Authority to distributed relational database objects

 

You can use either the SQL GRANT and REVOKE statements or the control language (CL) Grant Object Authority (GRTOBJAUT) and Revoke Object Authority (RVKOBJAUT) commands to grant and revoke a user's authority to relational database objects.

The SQL GRANT and REVOKE statements only operate on packages, tables, and views. In some cases, it is necessary to use 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 interactive SQL statements, authority is checked against the authority of the person processing the statement. Adopted authority is not used for interactive SQL statements.

Users running a distributed relational database application need authority to run the SQL package on the application server (AS). The GRANT EXECUTE ON PACKAGE statement allows the owner of an SQL package, or any user with administrative privileges to it, to grant specified users the privilege to run the statements in an SQL package. You can use this statement to give all users authorized to the AS, or a list of one or more user profiles on the AS, the privilege to run statements in an SQL package.

Normally, users have processing privileges on a package if they are authorized to the distributed application program created using the CRTSQLxxx command. If the package is created using the Create Structured Query Language Package (CRTSQLPKG) command, you might have to grant processing privileges on the package to users. You can issue this statement in an SQL program or using interactive SQL. A sample statement is as follows:

GRANT EXECUTE ON PACKAGE  SPIFFY.PARTS1
TO PUBLIC

The REVOKE EXECUTE ON PACKAGE statement allows the owner of an SQL package, or any user with administrative privileges to it, to remove the privilege to run statements in an SQL package from specified users. You can remove the EXECUTE privilege to all users authorized to the AS or to a list of one or more user profiles on the AS.

If you granted the same privilege to the same user more than once, revoking that privilege from that user nullifies all those grants. If you revoke an EXECUTE privilege on an SQL package you previously granted to a user, it nullifies any grant of the EXECUTE privilege on that SQL package, regardless of who granted it. A sample statement is as follows:

REVOKE EXECUTE ON PACKAGE  SPIFFY.PARTS1
FROM PUBLIC

You can also grant authority to an SQL package using the Grant Object Authority (GRTOBJAUT) command or revoke authority to an SQL package using the Revoke Object Authority (RVKOBJAUT) command.

 

Parent topic:

Security

 

Related reference

Create Structured Query Language Package (CRTSQLPKG) command
Grant Object Authority (GRTOBJAUT) command
Revoke Object Authority (RVKOBJAUT) command
Security for SQL objects
Distributed relational database statements