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

Jim_Knicely · July 2019

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=> CREATE ROLE my_system_tables_role;
CREATE ROLE

dbadmin=> 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/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Monitoring/Vertica/UsingSystemTables.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Roles/SYSMONITORROLE.htm

Have fun!