sp_objectpermission System Procedure

Syntax

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

Parameters

object_name – The name of an object or dbspace or a user or a role. If not specified, object privileges of the current user are reported. Default value is NULL.
object_owner – The name of the object owner for the specified object name. The object privileges of the specified object owned by the specified object owner are displayed. This parameter must be specified to obtain the object privileges of an object owned by another user or role. Default value is NULL.
object_type – Valid values are:
- TABLE^*
- VIEW
- MATERIALIZED VIEW
- SEQUENCE
- PROCEDURE
- FUNCTION
- DBSPACE
- USER
Note: ^*Column-level object privileges also appear.

If no value is specified, privileges on all object types are returned. Default value is NULL.

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