
System tables are grouped into the following schemas:
- V_CATALOG — information about persistent objects in the catalog
- V_MONITOR — information about transient system state
But what if I want to revoke the SELECT privilege from just one user? To do that I first have to revoke the SELECT privilege from PUBLIC then re-grant the SELECT privileges to all users expect the user that I wish to remove the access.
Example:
I don’t want user JIM to be able to read from the V_CATALOG.DATABASES system table.
Currently, he can.
dbadmin=> \c - jim
You are now connected as user "jim".
dbadmin=> SELECT database_name FROM v_catalog.databases;
database_name
---------------
test_db
(1 row)
Let’s change that!
dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".
dbadmin=> SELECT object_schema, object_name, privileges_description, grantee
dbadmin-> FROM v_catalog.grants
dbadmin-> WHERE object_name = 'databases';
object_schema | object_name | privileges_description | grantee
---------------+-------------+------------------------+---------
v_catalog | databases | SELECT | public
(1 row)
dbadmin=> REVOKE SELECT ON v_catalog.databases FROM PUBLIC;
REVOKE PRIVILEGE
dbadmin=> \! vsql -Atc "SELECT 'GRANT SELECT ON v_catalog.databases TO ' || user_name || ';' FROM users WHERE NOT is_super_user AND user_name <> 'jim';" | vsql -e
GRANT SELECT ON v_catalog.databases TO user1;
GRANT PRIVILEGE
GRANT SELECT ON v_catalog.databases TO user2;
GRANT PRIVILEGE
GRANT SELECT ON v_catalog.databases TO monitor;
GRANT PRIVILEGE
GRANT SELECT ON v_catalog.databases TO role_user1;
GRANT PRIVILEGE
GRANT SELECT ON v_catalog.databases TO role_user2;
GRANT PRIVILEGE
GRANT SELECT ON v_catalog.databases TO dave;
GRANT PRIVILEGE
Now user JIM can no longer read from the V_CATALOG.DATABASES system table!
dbadmin=> \c - jim
You are now connected as user "jim".
dbadmin=> SELECT database_name FROM v_catalog.databases;
ERROR 4367: Permission denied for relation databases
But other users can!
dbadmin=> \c - user1
You are now connected as user "user1".
dbadmin=> SELECT database_name FROM v_catalog.databases;
database_name
---------------
test_db
(1 row)
Helpful Links:https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Monitoring/Vertica/UsingSystemTables.htm
https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/GRANTS.htm
https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/GRANT/GRANTStatements.htm
https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/REVOKE/REVOKEStatements.htm
Have fun!