Privileges and Privilege Subjects in SQL

Privileges and Privilege Subjects in SQL

Determining Privileges in the SQL Language

Five special predicates are implemented in 602SQL for determining privileges of the specified privilege subject to the specified table or table records. These predicates are has_select_privil (for the read privilege), has_update_privil (for the update privilege), has_delete_privil (for the delete privilege), has_insert_privil (for the insert privilege) and has_grant_privil (for the grant privilege).

Read and update privileges are determined for each column separately, but other privileges are determined for the entire table.

The predicates are used as a part of the SELECT query phrase. Table selection in the FROM clause specifies which table the privileges are determined and the WHERE condition specifies which records (if the table has record privileges enabled).

These predicates have the following form:

privilege_predicates ::= column_predicates | table_predicates
column_predicates ::= { has_select_privil | has_update_privil } 
	( column, sort [, subject_name, subject_category ] )
table_predicates ::= { has_delete_privil | has_insert_privil | has_grant_privil } 
	( sort [, subject_name, subject_category ] )
sort ::= {0 | 1 | 2}
subject_category ::= { CATEG_USER | CATEG_GROUP | CATEG_ROLE }

Description

The column_predicates must always have one column specified in the SELECT query phrase. The other predicates (delete, insert and grant) do not need to have any column specified, since these privileges are defined for the entire table.

The sort value specifies which privilege is being determined:

You can specify the subject_name and subject_category parameters for predicates. If both parameters are specified, this subject's privilege will be determined. If not specified, the user asking the query is assumed. The existence of the specified subject is not checked while evaluating this query, if the subject does not exist, it has no privileges.

Effective privileges are a union of privileges granted directly to the user and privileges granted for being part of a certain group or role within an application. Special privileges granted to the user when executing procedures and evaluating queries that have the admin mode set on objects are not included.

Examples:

Use a SELECT to determine privileges.

SELECT has_select_privil(a,2,'anonymous',CATEG_USER, has_select_privil(b,2,'anonymous',CATEG_USER)
FROM PrivTab

Display only those records that the JUNIOR_USER role can read column B.

SELECT *
FROM PrivTab
WHERE has_select_privil(b,2,'junior_user',CATEG_ROLE)

Editing privileges

The GRANT and REVOKE statements are used to set privileges for the specified subject to a table or table records.

See

• Checking privileges when executing SQL statements

Privileges and Privilege Subjects in SQL

Privileges and Privilege Subjects in SQL