view all query log - v_monitor.query_profiles

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)

Thanks!