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, sequences, tables, views, 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.

• Authorization ID
  An authorization ID is a user profile object that identifies a unique user. You can use the Create User Profile (CRTUSRPRF) command to create an authorization ID.

• Views
  Views can prevent unauthorized users from having access to sensitive data.

• Auditing
  DB2 UDB for iSeries is designed to comply with the U.S. government C2 security level. A key feature of the C2 level is the ability to perform auditing on the system.

 

Parent topic:

Data protection

 

Related reference

Security Reference PDF
GRANT (Table or View Privileges)
REVOKE (Table or View Privileges)