view all query log - v_monitor.query_profiles

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

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    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)
    
  • Thanks!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file