how to grant select on sessions to new user

I have created a new user which I want to use to check stuff but I don't want the user to have full dbadmin type privileges. I want to be able to grant select * on sessions for example to the new user, how do I do that without granting the dbadmin role to the user?


  • Options
    PUBLIC User
    Newly-created users do not have access to schema PUBLIC by default. Make sure to GRANT USAGE ON SCHEMA PUBLIC to all users you create.
    GRANT (Schema)
  • Options
    Hi Yes I did that to the newly created user but for whatever reason that only allows the new user to see his own sessions from what I can determine. I want to be able to see what other sessions are doing stuff  by doing select * from sessions or similar?

  • Options
    hmm... a sessions table? Did you looked what schema it belong?  V_MONITOR and not
    dbadmin=> select schema_name, table_name from all_tables where table_name = 'sessions';
    schema_name | table_name
    v_monitor | sessions
    (1 row)
    DBADMIN (select * from public.foo - success):
    dbadmin=> select * from foo limit 1;
    name | type | height | hike_safety
    Denali | mountain | 17000 | 12.2
    (1 row)

    dbadmin=> create user viewer SEARCH_PATH v_monitor;
    VIEWER(select * from public.foo - fail)
    dbadmin@twingo:~$ vsql -Uviewer
    Welcome to vsql, the Vertica Analytic Database interactive terminal.

    Type: \h or \? for help with vsql commands
    \g or terminate with semicolon to execute query
    \q to quit

    viewer=> select * from foo limit 1;
    ERROR 4566: Relation "foo" does not exist

    viewer=> select user_name, session_id from sessions;
    user_name | session_id
    viewer | twingo-7301:0x4890
    (1 row)
  • Options
    Hi Daniel, thanks for your post but you are slightly missing my point I think
    I need to have a user created that can issue select * from sessions and the results will show info from all sessions running on the database

    In your examples above your user only sees his own sessions ?

    I tried create user 'blah'  SEARCH_PATH v_monitor; (and alter user) statements
    but again this only seemed to give me info on the current user 'blah' session. I know I had another session running in another window as dbadmin and as dbadmin when I issue select * from session can see the dbadmin session as well as the 'blah' session.

    I need the 'blah' user to be able to see the sessions the same as dbadmin does, so far only if I grant the role of dbadmin to the user is the only way I found that works. I feel sure there has to be another way.

    I'll also do some experimenting, thanks.
  • Options
    >> you are slightly missing my point
    I don't missing nothing, its you actually do.
    But as you like: do you like to get direct answers/solutions and do not to use in logic and Vertica documentation? Ok. Its your choice. Wait for solution.

    Good Luck!
  • Options
    Hi Steve,

    Unfortunately, there is no other way to do this.  Sorry...

    "sessions" is a magic system table.  As you've noticed, it shows each user only the sessions that they are responsible for.

    There is currently no way to build a regular Vertica table with that property.  Unfortunately, there is also no way to use regular Vertica permissions on this flavor of magic system table.

    A workaround, admittedly ugly, would be to have a script that regularly INSERT .. SELECT's the contents of the sessions table into a regular table.  Then that table always has all session information in it, but you can control who can see it.

  • Options

    In Vertica version 7.2.x there is SYSMONITOR role which grants select on almost all system tables without grants full dbadmin control. See Vertica 7.2 Administration Guide.


    In earlier version you can use external procedure as workaround. You can see how it works here.

    As a solution try the algorithm:

    1. dbadmin creates table 't_sessions' (for example) which has the same structure as table 'sessions'.
    2. dbadmin creates external procedure which first truncates 't_sessions', then insert all rows from 'sessions' in 't_sessions'.
    3. dbadmin grants select on 't_sessions' to user and grants usage on created external procedure.

    As a result user must launch external procedure and then select from 't_sessions' to get information about all sessions.  

Leave a Comment

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