sp_objectpermission System Procedure

Generates a report on object privileges granted to the specified role, or user name, or the object privileges granted on the specified object or dbspace.

Syntax

sp_objectpermission ( [object_name], [object_owner], [object_type] )

Parameters

Privileges

You must have EXECUTE privilege on the system procedure.. Any user can execute sp_objectpermission to obtain all the object privileges granted to him- or herself. Object owners can also execute this procedure to obtain the object privileges for self-owned objects. Additional system privileges are needed to obtain object privileges for the following:
  • Object privileges granted to other users or granted on objects owned by other users – You must also have the MANAGE ANY OBJECT PRIVILEGE system privilege
  • Object privileges that are granted on objects owned by a role or granted to a role – You must also have the MANAGE ANY OBJECT PRIVILEGE system privilege or be a role administrator on the role
  • Object privileges of a dbspace – You must have the MANAGE ANY DBSPACE system privilege

Remarks

Column Name Data Type Description
grantor char(128) The user ID of the grantor
grantee char(128) The user ID of the grantee
object_name char(128) The name of the object
owner char(128) The name of the object owner
object_type char(20) The type of object
column_name char(128) The name of the column
permission char(20) The name of the privilege
grantable char(1) Whether or not the privilege is grantable
All arguments are optional and can generate these reports:
  • If input is an object (table, view, procedure, function, sequence, and so on), procedure displays list of all roles and user that have different object privilege on the object.
  • If input is a role or user, procedure displays list of all object privileges granted to the role or input. When executing sp_objectpermission to display object privileges of a user or a role, the object privileges that are inherited through role grants also.
  • If input is a dbspace name, procedure displays list of all user or roles that have CREATE privilege on the specified dbspace.
  • By default, object type is NULL and the object privileges for all existing object types matching the specified object name appear.

Example

The following GRANT statements are executed:

GRANT SERVER OPERATOR TO r4;
GRANT BACKUP DATABASE TO r3 WITH ADMIN OPTION;
GRANT DROP CONNECTION TO r3 WITH ADMIN ONLY OPTION;
GRANT MONITOR TO r2;GRANT CHECKPOINT TO r1;
GRANT ROLE r2 TO r1 WITH ADMIN OPTION;
GRANT ROLE r3 TO r2 WITH NO ADMIN OPTION;
GRANT ROLE r4 TO r3 WITH ADMIN ONLY OPTION;
Consider these object privileges:
  • r5 owns a table named test_tab and a procedure named test_proc in the database.
  • u5, which has administrative rights over r5, grants the following privileges:
    • GRANT SELECT ON r5.test_tab TO r2 WITH GRANT OPTION;
    • GRANT SELECT (c1), UPDATE (c1) ON r5.test_tab TO r6 WITH GRANT OPTION;
    • GRANT EXECUTE ON r5.test_proc TO r3;
  • u6, which has administrative rights over r6, grants the following privileges:
    • GRANT SELECT (c1), REFERENCES (c1) ON r5.test_tab TO r3;
If sp_objectpermission( ‘r1’ ) is executed, output is similar to:
Example sp_objectpermission( ‘r1’ ) Output
grantor grantee object_name
u5 r2 test_tab
u6 r3 test_tab
u6 r3 test_tab
u6 r3 test_proc
(Continued)

owner

 object_type grantor
r5 TABLE u5
r5 COLUMN u6
r5 COLUMN u6
r5 PROCEDURE u6
(Continued)

grantable

 column_name privilege
Y NULL SELECT
N c1 SELECT
Y c1 REFERENCES
N NULL EXECUTE

If sp_objectpermission( ‘test_tab’, ‘r5’, ‘table’ ) is executed, output is similar to:

Example sp_objectpermission( ‘test_tab’, ‘r5’, ‘table’ ) Output
grantor grantee object_name
u5 r2 test_tab
u5 r6 test_tab
u5 r6 test_tab
u6 r3 test_tab
u6 r3 test_tab
(Continued)

owner

 object_type grantor
r5 TABLE u5
r5 COLUMN u5
r5 COLUMN u5
r5 COLUMN u6
r5 COLUMN u6
(Continued)

column_name

 privilege grantable
NULL SELECT Y
c1 SELECT Y
c1 UPDATE Y
c1 SELECT N
c1 REFERENCES N
Parent topic: Procedures and Functions