Viewing Privileges Granted on Objects
Vertica logs information about privileges granted on various objects, including the grantor and grantee, in the V_CATALOG.GRANTS system table. The order of columns in the table corresponds to the order in which they appear in the GRANT command. An asterisk in the output means the privilege was granted WITH GRANT OPTION.
The following command queries the GRANTS system table:
=> SELECT * FROM grants ORDER BY grantor, grantee; grantor | privileges_description | object_schema | object_name | grantee ---------+-------------------------------------------------+---------------+-------------+----------- Bob | | | commentor | Alice dbadmin | CREATE | | schema2 | Bob dbadmin | | | commentor | Bob dbadmin | | | commentor | Bob dbadmin | | | logadmin | Bob dbadmin | USAGE | | general | Bob dbadmin | INSERT, UPDATE, DELETE, REFERENCES | public | applog | Bob dbadmin | | | logadmin | Ted dbadmin | USAGE | | general | Ted dbadmin | USAGE | | general | Sue dbadmin | CREATE, CREATE TEMP | | vmart | Sue dbadmin | USAGE | | public | Sue dbadmin | SELECT* | public | applog | Sue dbadmin | USAGE | | general | Alice dbadmin | INSERT, SELECT | public | comments | commentor dbadmin | INSERT, SELECT | public | applog | commentor dbadmin | | | logwriter | logadmin dbadmin | | | logreader | logadmin dbadmin | DELETE | public | applog | logadmin dbadmin | SELECT | public | applog | logreader dbadmin | INSERT | public | applog | logwriter dbadmin | USAGE | | v_internal | public dbadmin | CREATE TEMP | | vmart | public dbadmin | USAGE | | public | public dbadmin | USAGE | | v_catalog | public dbadmin | USAGE | | v_monitor | public dbadmin | CREATE*, CREATE TEMP* | | vmart | dbadmin dbadmin | USAGE*, CREATE* | | schema2 | dbadmin dbadmin | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES* | public | comments | dbadmin dbadmin | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES* | public | applog | dbadmin (30 rows)
To quickly find all of the privileges that have been granted to all users on the schema named myschema, run the following statement:
=> SELECT grantee, privileges_description FROM GRANTS WHERE object_name='myschema'; grantee | privileges_description ---------+------------------------ Bob | USAGE, CREATE Alice | CREATE (2 rows)
Note that the vsql commands, \dp and \z, both return similar information to GRANTS:
=> \dp
Access privileges for database "apps" Grantee | Grantor | Privileges | Schema | Name
-----------+---------+-------------------------------------------------+--------+------------
public | dbadmin | USAGE | | v_internal
public | dbadmin | USAGE | | v_catalog
public | dbadmin | USAGE | | v_monitor
logadmin | dbadmin | | | logreader
logadmin | dbadmin | | | logwriter
Fred | dbadmin | USAGE | | general
Fred | dbadmin | | | logadmin
Bob | dbadmin | USAGE | | general
dbadmin | dbadmin | USAGE*, CREATE* | | schema2
Bob | dbadmin | CREATE | | schema2
Sue | dbadmin | USAGE | | general
public | dbadmin | USAGE | | public
Sue | dbadmin | USAGE | | public
public | dbadmin | CREATE TEMP | | appdat
dbadmin | dbadmin | CREATE*, CREATE TEMP* | | appdat
Sue | dbadmin | CREATE, CREATE TEMP | | appdat
dbadmin | dbadmin | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES* | public | applog
logreader | dbadmin | SELECT | public | applog
logwriter | dbadmin | INSERT | public | applog
logadmin | dbadmin | DELETE | public | applog
Sue | dbadmin | SELECT* | public | applog
(22 rows)
See GRANT Statements in the SQL Reference Manual.