how to grant select on sessions to new user

Steve_Angell

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?

Daniel_Leybovic

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.
https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/DBUsersAndPr...
GRANT (Schema)
https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/GRANT/GRANT...

Steve_Angell

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?

 

Daniel_Leybovic

hmm... a sessions table? Did you looked what schema it belong?  V_MONITOR and not
PUBLIC.

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;
CREATE USER

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)

Steve_Angell

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.

Daniel_Leybovic

>> 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!

[Deleted User]

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.

Adam

duhovensky

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.