view all query log - v_monitor.query_profiles

nirka

Hi,

When I query table v_monitor.query_profiles I see only the queries that I ran.
Is it possible to grant permissions to view queries that ran by all users? (without adding my user as a super user).

Thanks

Jim_Knicely

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)

nirka

Thanks!