Comments

• Jim_Knicely - Select Field - Administrator

  June 2018 edited June 2018

  Hi,

  You can grant the SYSMONITOR role a user to give it access to system tables without granting full DBADMIN access.

  QUERY_PROFILES is a system table that is monitorable.

  dbadmin=> SELECT is_monitorable FROM system_tables WHERE table_name = 'query_profiles';
   is_monitorable
  ----------------
   t
  (1 row)
  

  See:
  https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/DBUsersAndPrivileges/SYSMONITORROLE.htm

  Example:

  dbadmin=> create user test_user;
  CREATE USER
  
  dbadmin=> \c - test_user
  You are now connected as user "test_user".
  
  dbadmin=> select distinct user_name from query_profiles;
   user_name
  -----------
   test_user
  (1 row)
  
  dbadmin=> \c - dbadmin
  
  dbadmin=> GRANT sysmonitor TO test_user;
  GRANT ROLE
  
  dbadmin=> ALTER USER test_user DEFAULT ROLE sysmonitor;
  ALTER USER
  
  dbadmin=> \c - test_user
  You are now connected as user "test_user".
  
  dbadmin=> select distinct user_name from query_profiles;
   user_name
  -----------
   test_user
   dbadmin
   my_user
   jim_test
  (4 rows)
  

  0
• nirka

  June 2018

  Thanks!

  0