Revoke Access From Just One System Table

Vertica provides an API (application programming interface) for monitoring various features and functions within a database in the form of system tables. These tables provide a robust, stable set of views that let you monitor information about your system’s resources, background processes, workload, and performance, allowing you to more efficiently profile, diagnose, and view historical data equivalent to load streams, query profiles, tuple mover operations, and more.

System tables are grouped into the following schemas:

• V_CATALOG — information about persistent objects in the catalog
• V_MONITOR — information about transient system state

The SELECT privilege on all system tables is granted to PUBLIC. Therefore, by default all user can query them.

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!