Authorization for queries

The same authorization rules for apply to the SELECT statement, the fullselect, select-statement, and subselect query forms.

For any form of a query, the privilege set that is defined below must include one of the following authorities or privilege sets:

For each table or view identified in the statement, the privilege set must include one of the following:
- Ownership of the table or view
- The SELECT privilege on the table or view
- DBADM authority for the database (tables only)
If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.
SYSADM authority
SYSCTRL authority (catalog tables only)
DATAACCESS authority
Installation SYSOPR authority (catalog tables and system tables only)

If a query includes a user-defined function, the privileges that are held by the authorization ID of the statement must include at least one of the following:

For each user-defined function that is identified in the statement, one of the following:
- The EXECUTE privilege on the function
- Ownership of the function
SYSADM authority
DATAACCESS authority

If a query includes a sequence object, the privileges that are held by the authorization ID of the statement must include at least one of the following:

For each sequence object that is identified in the statement, one of the following:
- The USAGE privilege on the sequence object
- Ownership of the sequence object
DATAACCESS authority

If the select-statement is part of a DECLARE CURSOR statement, the privilege set is the privileges that are held by the authorization ID of the owner of the plan or package.

If the select-statement contains an SQL data change statement, the privilege set must include the SELECT privilege and the appropriate privileges for the SQL data change statement (insert, update, or delete privileges) on the target table or view.

If the select-statement references a table that contain an active row or column access control, and row permissions or column masks are defined for the table, the authorization ID or role of the statement does not need authority to reference objects that are specified in the definitions of those row permissions or column masks.

Privilege sets for queries

Start of change If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. End of change

Start of change If the statement is dynamically prepared, and the application is bound in a trusted context, the privilege set is the set of privileges that are held by that role. Otherwise, the privilege set depends on the dynamic SQL statement behavior, which is specified by option DYNAMICRULES: End of change

Run behavior: The privilege set is the union of the privilege sets that are held by each authorization ID of the process.
Bind behavior: The privilege set is the privileges that are held by the authorization ID of the owner of the plan or package.
Define behavior: The privilege set is the privileges that are held by the authorization ID of the owner of the stored procedure or user-defined function.
Invoke behavior: The privilege set is the privileges that are held by the authorization ID of the invoker of the stored procedure or user-defined function.

For a list of the DYNAMICRULES values that specify run, bind, define, or invoke behavior, see Table 1.

When any form of a query is used as a component of another statement, the authorization rules that apply to the query are specified in the description of that statement. For example, see the authorization rules that apply to the subselect in a CREATE VIEW statement in CREATE VIEW statement.

If your installation uses the access control authorization exit (DSNX@XAC), that exit might be controlling the authorization rules instead of the rules that are listed here.