
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!