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.
sp_objectpermission ( [object_name], [object_owner], [object_type] )
If no value is specified, privileges on all object types are returned. Default value is NULL.
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 |
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;
grantor | grantee | object_name | owner | object_type |
---|---|---|---|---|
u5 | r2 | test_tab | r5 | TABLE |
u6 | r3 | test_tab | r5 | COLUMN |
u6 | r3 | test_tab | r5 | COLUMN |
u6 | r3 | test_proc | r5 | PROCEDURE |
(Continued) grantor |
grantable | column_name | privilege |
---|---|---|---|
u5 | Y | NULL | SELECT |
u6 | N | c1 | SELECT |
u6 | Y | c1 | REFERENCES |
u6 | N | NULL | EXECUTE |
If sp_objectpermission( ‘test_tab’, ‘r5’, ‘table’ ) is executed, output is similar to:
grantor | grantee | object_name | owner | object_type |
---|---|---|---|---|
u5 | r2 | test_tab | r5 | TABLE |
u5 | r6 | test_tab | r5 | COLUMN |
u5 | r6 | test_tab | r5 | COLUMN |
u6 | r3 | test_tab | r5 | COLUMN |
u6 | r3 | test_tab | r5 | COLUMN |
(Continued) grantor |
column_name | privilege | grantable |
---|---|---|---|
u5 | NULL | SELECT | Y |
u5 | c1 | SELECT | Y |
u5 | c1 | UPDATE | Y |
u6 | c1 | SELECT | N |
u6 | c1 | REFERENCES | N |