GET_PRIVILEGES_DESCRIPTION
Returns the effective privileges the current user has on an object, including explicit, implicit, inherited, and role-based privileges.
Because this meta-function only returns effective privileges, GET_PRIVILEGES_DESCRIPTION only returns privileges with fully-satisfied prerequisites. For a list of prerequisites for common operations, see Privileges Required for Common Database Operations.
For example, a user must have the following privileges to query a table:
- Schema: USAGE
- Table: SELECT
If user Brooke has SELECT privileges on table s1.t1
but lacks USAGE privileges on schema s1
, Brooke cannot query the table, and GET_PRIVILEGES_DESCRIPTION does not return SELECT as a privilege for the table.
Inherited privileges are not displayed if privilege inheritance is disabled at the database level.
Syntax
GET_PRIVILEGES_DESCRIPTION( 'type', '[[database.]schema.]name' );
Parameters
type |
Specifies an object type, one of the following:
|
[database.]schema |
Specifies a database and schema, by default the current database and |
name |
Name of the target object |
Privileges
None
Example
In the following example, user Glenn has set the REPORTER role and wants to check his effective privileges on schema s1
and table s1.articles
.
- Table
s1.articles
inherits privileges from its schema (s1
). - The REPORTER role has the following privileges:
- SELECT on schema
s1
- INSERT WITH GRANT OPTION on table
s1.articles
- SELECT on schema
- User Glenn has the following privileges:
- UPDATE and USAGE on schema
s1
. - DELETE on table
s1.articles
.
- UPDATE and USAGE on schema
GET_PRIVILEGES_DESCRIPTION returns the following effective privileges for Glenn on schema s1
:
=> SELECT GET_PRIVILEGES_DESCRIPTION('schema', 's1'); GET_PRIVILEGES_DESCRIPTION -------------------------------- SELECT, UPDATE, USAGE (1 row)
GET_PRIVILEGES_DESCRIPTION returns the following effective privileges for Glenn on table s1.articles
:
=> SELECT GET_PRIVILEGES_DESCRIPTION('table', 's1.articles'); GET_PRIVILEGES_DESCRIPTION -------------------------------- INSERT*, SELECT, UPDATE, DELETE (1 row)