GRANTS
Provides information about privileges granted on various objects, the granting user, and grantee user. The order of columns in the table corresponds to the order in which they appear in the GRANT command. The GRANTS table does not retain the role grantor.
| Column Name | Datan Type | Description |
|---|---|---|
GRANTEE
|
VARCHAR | The user being granted permission. |
GRANTEE_ID
|
INTEGER |
A unique numeric ID assigned by the Vertica catalog that identifies the user granted permissions. |
GRANT_ID
|
INTEGER |
A unique numeric ID assigned by the Vertica catalog that identifies the |
GRANTOR
|
VARCHAR |
The user granting the permission. |
GRANTOR_ID
|
INTEGER |
A unique numeric ID assigned by the Vertica catalog that identifies the user who performed the grant operation. |
OBJECT_ID
|
INTEGER |
A unique numeric ID assigned by the Vertica catalog that identifies the object granted. |
OBJECT_NAME
|
VARCHAR |
The name of the object that is being granted privileges. Note that for schema privileges, the schemaname appears in the |
OBJECT_SCHEMA
|
VARCHAR |
The name of the schema that is being granted privileges. |
OBJECT_TYPE
|
VARCHAR |
The object type on which the grant was applied; for example, ROLE, SCHEMA, DATABASE, RESOURCEPOOL. Output from this column is useful in cases where a schema, resource pool, or user share the same name. |
PRIVILEGES_DESCRIPTION
|
VARCHAR |
A readable description of the privileges being granted; for example INSERT, SELECT. An asterisk in PRIVILEGES_DESCRIPTION output indicates a privilege WITH GRANT OPTION. |
Notes
The vsql commands \dp and \z both include the schema name in the output. For example:
=> \dp
Access privileges for database "vmartdb"
Grantee | Grantor | Privileges | Schema | Name
---------+---------+------------+--------+-----------------
| dbadmin | USAGE | | public
| dbadmin | USAGE | | v_internal
| dbadmin | USAGE | | v_catalog
| dbadmin | USAGE | | v_monitor
| dbadmin | USAGE | | v_internal
| dbadmin | USAGE | | v_catalog
| dbadmin | USAGE | | v_monitor
| dbadmin | USAGE | | v_internal
| dbadmin | USAGE | | designer_system
(9 rows)
The vsql command \dp *.tablename displays table names in all schemas. This command lets you distinguish grants for same-named tables in different schemas:
=> \dp *.events
Access privileges for database "dbadmin"
Grantee | Grantor | Privileges | Schema | Name
---------+---------+--------------------------------------------+---------+--------
user2 | dbadmin | INSERT, SELECT, UPDATE, DELETE, REFERENCES | schema1 | events
user1 | dbadmin | SELECT | schema1 | events
user2 | dbadmin | INSERT, SELECT, UPDATE, DELETE, REFERENCES | schema2 | events
user1 | dbadmin | INSERT, SELECT | schema2 | events
(4 rows)
The vsql command \dp schemaname.* displays all tables in the named schema:
=> \dp schema1.*
Access privileges for database "dbadmin"
Grantee | Grantor | Privileges | Schema | Name
---------+---------+--------------------------------------------+--------------+------------
user2 | dbadmin | INSERT, SELECT, UPDATE, DELETE, REFERENCES | schema1 | events
user1 | dbadmin | SELECT | schema1 | events
(2 rows)
Examples
This example shows CREATE and USAGE privileges granted to Bob in the fictitious apps database:
=> SELECT grantor, privileges_description, object_schema, object_name, grantee FROM grants; grantor | privileges_description | object_schema | object_name | grantee ---------+------------------------+---------------+-------------+--------- dbadmin | USAGE | | general | Bob dbadmin | CREATE | | schema2 | Bob (2 rows)
This next query looks for privileges granted to a particular set of grantees. The asterisk in privileges_description column for User1 means that user has WITH GRANT OPTION privileges.
=> SELECT grantor, privileges_description, object_schema, object_name, grantee FROM grants WHERE grantee ILIKE 'User%'; grantor | privileges_description | object_schema | object_name | grantee ---------+---------------------------+---------------+-------------+--------- release | USAGE | | general | User1 release | USAGE | | general | User2 release | USAGE | | general | User3 release | USAGE | | s1 | User1 release | USAGE | | s1 | User2 release | USAGE | | s1 | User3 User1 | INSERT*, SELECT*, UPDATE* | s1 | t1 | User1 (7 rows)
In the following example, online_sales is the schema that first gets privileges, and then inside that schema the anchor table gets SELECT privileges:
=> SELECT grantee, grantor, privileges_description, object_schema, object_name FROM grants WHERE grantee='u1' ORDER BY object_name;
grantee | grantor | privileges_description | object_schema | object_name ---------+---------+------------------------+-------- ------+------------------ u1 | dbadmin | CREATE | | online_sales u1 | dbadmin | SELECT | online_sales | online_sales_fact
The following statement shows all grants for user Bob:
-> SELECT * FROM GRANTS WHERE grantee = 'Bob'; -[ RECORD 1 ]----------+-------------------- grant_id | 45035996273749244 grantor_id | 45035996273704962 grantor | dbadmin privileges_description | USAGE object_schema | object_name | general object_id | 45035996273718666 object_type | RESOURCEPOOL grantee_id | 45035996273749242 grantee | Bob -[ RECORD 2 ]----------+-------------------- grant_id | 45035996273749598 grantor_id | 45035996273704962 grantor | dbadmin privileges_description | object_schema | object_name | dbadmin object_id | 45035996273704968 object_type | ROLE grantee_id | 45035996273749242 grantee | Bob -[ RECORD 3 ]----------+-------------------- grant_id | 45035996273749716 grantor_id | 45035996273704962 grantor | dbadmin privileges_description | object_schema | object_name | dbadmin object_id | 45035996273704968 object_type | ROLE grantee_id | 45035996273749242 grantee | Bob -[ RECORD 4 ]----------+-------------------- grant_id | 45035996273755986 grantor_id | 45035996273704962 grantor | dbadmin privileges_description | object_schema | object_name | pseudosuperuser object_id | 45035996273704970 object_type | ROLE grantee_id | 45035996273749242 grantee | Bob -[ RECORD 5 ]----------+-------------------- grant_id | 45035996273756986 grantor_id | 45035996273704962 grantor | dbadmin privileges_description | CREATE, CREATE TEMP object_schema | object_name | mcdb object_id | 45035996273704974 object_type | DATABASE grantee_id | 45035996273749242 grantee | Bob -[ RECORD 5 ]----------+-------------------- ...
