Revoke Access to Multiple System Tables from Multiple Users with a New Role

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 users can query them. But what If I want to revoke the SELECT privilege on several system tables from just one or more users? To do that I first have to revoke the SELECT privilege from PUBLIC, create a new Role which has the SELECT privilege, then grant the new Role to all users except the users that I wish to remove the access. Example: I don’t want users JIM and DAVE to be able to read from the V_CATALOG.DATABASES nor the V_CATALOG.LICENSE_AUDITS system table. Currently, they 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) dbadmin=> \c - dave You are now connected as user "dave". 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, v_catalog.license_audits FROM public; REVOKE PRIVILEGE dbadmin=> CREATE ROLE system_tables_role; CREATE ROLE dbadmin=> GRANT SELECT ON v_catalog.databases, v_catalog.license_audits TO system_tables_role; GRANT PRIVILEGE 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 | system_tables_role (1 row) dbadmin=> \! vsql -Atc "SELECT 'GRANT system_tables_role TO ' || user_name || '; ALTER USER ' || user_name || ' DEFAULT ROLE ' || CASE WHEN default_roles = '' THEN 'system_tables_role' ELSE REPLACE(default_roles, '*', '') || ', system_tables_role' END || ';' FROM users WHERE NOT is_super_user AND user_name NOT IN ('jim', 'dave');" | vsql -e GRANT system_tables_role TO user1; GRANT ROLE ALTER USER user1 DEFAULT ROLE user_role1, user_role2, user_role3, system_tables_role; ALTER USER GRANT system_tables_role TO user2; GRANT ROLE ALTER USER user2 DEFAULT ROLE system_tables_role; ALTER USER GRANT system_tables_role TO monitor; GRANT ROLE ALTER USER monitor DEFAULT ROLE sysmonitor, system_tables_role; ALTER USER GRANT system_tables_role TO role_user1; GRANT ROLE ALTER USER role_user1 DEFAULT ROLE r1, system_tables_role; ALTER USER GRANT system_tables_role TO role_user2; GRANT ROLE ALTER USER role_user2 DEFAULT ROLE r1, r2, system_tables_role; ALTER USER GRANT system_tables_role TO dave; GRANT ROLE ALTER USER dave DEFAULT ROLE system_tables_role; ALTER USER Now users JIM and DAVE can no longer read from the V_CATALOG.DATABASES and V_CATALOG.LICENSE_AUDITS system tables! 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 dbadmin=> SELECT database_size_bytes FROM v_catalog.license_audits LIMIT 1; ERROR 4367: Permission denied for relation license_audits dbadmin=> SHOW ENABLED ROLES; name | setting ---------------+--------- enabled roles | (1 row) dbadmin=> \c - dave You are now connected as user "dave". dbadmin=> SELECT database_name FROM v_catalog.databases; ERROR 4367: Permission denied for relation databases dbadmin=> SELECT database_size_bytes FROM v_catalog.license_audits LIMIT 1; ERROR 4367: Permission denied for relation license_audits dbadmin=> SHOW ENABLED ROLES; name | setting ---------------+--------- enabled roles | (1 row) 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) dbadmin=> SELECT database_size_bytes FROM v_catalog.license_audits LIMIT 1; database_size_bytes --------------------- 37462123 (1 row) dbadmin=> SHOW ENABLED ROLES; name | setting ---------------+---------------------------------------------------------- enabled roles | user_role1*, user_role2, user_role3*, system_tables_role (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 https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEROLE.htm