
- V_CATALOG — information about persistent objects in the catalog
- V_MONITOR — information about transient system state
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