Vertica database System Tables Lock Down

When running a big Data Warehouse on top of HP Vertica the last thing you want is some smart BI analyst running queries on your system tables repositories. Sensitive information might be there for grabs by some bad intention-ed users. Also when running Multi-Tenant DW where you have multiple users speared by each project you dont really want to give this opportunity on seeing resource pools definition or any user specific definition.

So you wanna keep this access limited to only dbadmin role users.

By default the parameter RestrictSystemTables is set to 0,where 0 Allows all users to access system tables.
  • To see the actual value of the RestrictSystemTables parameter:
select * from configuration_parameters
   where
parameter_name ='RestrictSystemTables';
To restrict access to normal users the parameter RestrictSystemTables value needs to be 1 (Limits access to system tables to database administrator users).
  • To alter the value of the RestrictSystemTables parameter:
ALTER DATABASE db_name SET PARAMETER RestrictSystemTables=1;
Note: This requires a database restart. Ohh but what is my user needs to see this type of data ?
  • create views on this system tables limited to only each user scope.
Example : 
create view user_schema.vusers as
select * from v_catalog.users where user_name='user_name';

grant select on user_schema.users to user_name;
  •  the user will be able to see only his user definition.