Security for SQL objects
All objects on the system, including SQL objects, are managed by the system security function.
Users can authorize SQL objects through either the SQL GRANT and REVOKE statements or the Edit Object Authority (EDTOBJAUT), Grant Object Authority (GRTOBJAUT), and Revoke Object Authority (RVKOBJAUT) CL commands.
The SQL GRANT and REVOKE statements
operate on SQL functions, SQL packages, SQL procedures, distinct types,
array types, sequences, variables, tables, views, XSR objects, and
the individual columns of tables and views. Furthermore, SQL GRANT
and REVOKE statements only grant private and public authorities. In
some cases, it is necessary to use EDTOBJAUT, GRTOBJAUT, and RVKOBJAUT
to authorize users to other objects, such as commands and programs.
The authority checked for SQL statements depends on whether the statement is static, dynamic, or being run interactively.
For static SQL statements:
- If the USRPRF value is *USER, the authority to run the SQL statement locally is checked using the user profile of the user running the program. The authority to run the SQL statement remotely is checked using the user profile at the application server. *USER is the default for system (*SYS) naming.
- If the USRPRF value is *OWNER, the authority to run the SQL statement locally is checked using the user profiles of the user running the program and of the owner of the program. The authority to run the SQL statement remotely is checked using the user profiles of the application server job and the owner of the SQL package. The higher authority is the authority that is used. *OWNER is the default for SQL (*SQL) naming.
For dynamic SQL statements:
- If the USRPRF value is *USER, the authority to run the SQL statement locally is checked using the user profile of the person running the program. The authority to run the SQL statement remotely is checked using the user profile of the application server job.
- If the USRPRF value is *OWNER and DYNUSRPRF is *USER, the authority to run the SQL statement locally is checked using the user profile of the person running the program. The authority to run the SQL statement remotely is checked using the user profile of the application server job.
- If the USRPRF value is *OWNER and DYNUSRPRF is *OWNER, the authority to run the SQL statement locally is checked using the user profiles of the user running the program and the owner of the program. The authority to run the SQL statement remotely is checked using the user profiles of the application server job and the owner of the SQL package. The highest authority is the authority that is used. Because of security concerns, you should use the *OWNER parameter value for DYNUSRPRF carefully. This option gives the access authority of the owner program or package to those who run the program.
For interactive SQL statements, authority is checked against the authority of the person processing the statement. Adopted authority is not used for interactive SQL statements.