The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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