Non-DBADMIN Access to System Tables (Table by Table)

The DBADMIN can assign a delegate the SYSMONITOR role to grant full access to system tables without granting full DBADMIN access. The SYSMONITOR role applies to all tables that have the flag IS_MONITORABLE set to true in the SYSTEM_TABLES system table.

What if I want a non-DBADMIN user to have full access to only a few system tables, not all of them? This can be achieved by maintaining a copy of the system tables and granting access to them instead!

Example:

I would like for the user LUCY to be able to view all data in the TABLES, VIEWS, GRANTS and PROJECTION_STORAGE system tables. dbadmin=> dbadmin=> CREATE ROLE my_system_tables_role; CREATE ROLE CREATE SCHEMA my_system_tables DEFAULT INCLUDE PRIVILEGES; CREATE SCHEMA dbadmin=> GRANT USAGE, SELECT ON SCHEMA my_system_tables TO my_system_tables_role; GRANT PRIVILEGE dbadmin=> CREATE TABLE my_system_tables.tables AS SELECT * FROM tables; WARNING 6978: Table "tables" will include privileges from schema "my_system_tables" CREATE TABLE dbadmin=> CREATE TABLE my_system_tables.views AS SELECT * FROM views; WARNING 6978: Table "views" will include privileges from schema "my_system_tables" CREATE TABLE dbadmin=> CREATE TABLE my_system_tables.grants AS SELECT * FROM grants; WARNING 6978: Table "grants" will include privileges from schema "my_system_tables" CREATE TABLE dbadmin=> CREATE TABLE my_system_tables.projection_storage AS SELECT * FROM projection_storage; WARNING 6978: Table "projection_storage" will include privileges from schema "my_system_tables" CREATE TABLE dbadmin=> GRANT my_system_tables_role TO lucy; GRANT ROLE dbadmin=> ALTER USER lucy DEFAULT ROLE my_system_tables_role; ALTER USER dbadmin=> \c - lucy You are now connected as user "lucy". dbadmin=> SELECT COUNT(*) FROM tables; COUNT ------- 4 (1 row) dbadmin=> SELECT COUNT(*) FROM my_system_tables.tables; COUNT ------- 72 (1 row) You can create an external procedure that the user can run to update the data in the MY_SYSTEM_TABLES tables: dbadmin=> \! cat /home/dbadmin/test_db/procedures/update_my_system_table.sh #!/bin/bash source ~/.bashrc /opt/vertica/bin/vsql -c "TRUNCATE TABLE my_system_tables.tables; INSERT /*+ DIRECT */ INTO my_system_tables.tables SELECT * FROM v_catalog.tables;" /opt/vertica/bin/vsql -c "TRUNCATE TABLE my_system_tables.views; INSERT /*+ DIRECT */ INTO my_system_tables.views SELECT * FROM v_catalog.views;" /opt/vertica/bin/vsql -c "TRUNCATE TABLE my_system_tables.grants; INSERT /*+ DIRECT */ INTO my_system_tables.grants SELECT * FROM v_catalog.grants;" /opt/vertica/bin/vsql -c "TRUNCATE TABLE my_system_tables.projection_storage; INSERT /*+ DIRECT */ INTO my_system_tables.projection_storage SELECT * FROM v_monitor.projection_storage;" exit 0 dbadmin=> CREATE PROCEDURE my_system_tables.update_my_system_tables_pr() AS 'update_my_system_tables.sh' LANGUAGE 'external' USER 'dbadmin'; CREATE PROCEDURE dbadmin=> GRANT EXECUTE ON PROCEDURE my_system_tables.update_my_system_tables_pr() TO my_system_tables_role; GRANT PRIVILEGE dbadmin=> \c - lucy You are now connected as user "lucy". dbadmin=> SELECT COUNT(*) FROM my_system_tables.tables; COUNT ------- 75 (1 row) dbadmin=> \c - dbadmin You are now connected as user "dbadmin". dbadmin=> CREATE TABLE some_table (c INT); CREATE TABLE dbadmin=> \c - lucy You are now connected as user "lucy". dbadmin=> SELECT my_system_tables.update_my_system_tables_pr(); INFO 4427: Procedure reported: COMMIT COMMIT COMMIT COMMIT update_my_system_tables_pr ---------------------------- 0 (1 row) dbadmin=> SELECT COUNT(*) FROM my_system_tables.tables; COUNT ------- 76 (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/AdministratorsGuide/DBUsersAndPrivileges/Roles/SYSMONITORROLE.htm

Have fun!