How to provide Vertica user with read-only access to certain specified system tables?

Question

We're looking to set up a user in our Vertica database that can see certain system tables, (projections, projection_storage and views), but we don't want this user to be a dbadmin, because we don't want them to have write privileges on these tables. I've tried using GRANT statements to give a regular user access to these tables, but that doesn't seem to work. Each user can only see their own own records in those tables. Is there a way to set up a user as I describe, or do we need to have this user be a dbadmin?

Our use case is that we need a user that can get a list of the schemas that exist in our database and iterate through each schema, gathering information to store in one central location. If our user is granted usage on the individual schemas, then they can get a list of those schemas, but they aren't able to access the necessary records in the projection_storage and views tables.

Thank you!

Settera

Answer 1

Granting USAGE on the schema to the user or role is not enough for users to see its projections in projection_storage table. If the user or the role has SELECT access on the table, then projections for those tables can be viewed in projection_storage. I am in Vertica 7.1, and I was able to view projection records by granting SELECT permission just to the role instead of granting to individual user ID.

If the user does not need to access tables but needs to list out tables in the schema for some reporting purpose, one option would be to periodically dump the content of projection_storage to a different table and grant proper privileges on this table to the user.

Cautionary

Answer 2

Just for the sake of maintaince you should create database roles !! and then give acces to those roles to your users . Other-wise the maintainance will be hell to you !!

Carleencarlen

I totally agree that setting up a role, rather than a user, will make maintenance easier. However, my question still applies at the role level: how do I set up a role such that it will have the access I described in my initial question? – Settera

Answer 3

Normally, I just give a user USAGE on a schema. And then "GRANT SELECT on to ;"

Do they have INSERT permissions on those tables?

Eighteenmo

I don't want them to have INSERT permissions on the tables, just SELECT. And it looks like granting usage on a schema, then granting select on particular tables does provide me access to certain parts of the metadata, (i.e. I can see that the schema exists and that it contains the tables I'm checking for), but it doesn't allow access to other pieces of metadata, such as the projection_storage table. – Settera

Answer 4

Granting select access to the role on the table , does not grant complete access to metadata tables like projection_storage . This seems to be a bug. In order to get complete access select needs to be granted to individual user id.

Amarillo

Answer 5

You can follow the below steps to create a user with select privileges to a schema . I ll follow this with a example ,In my test database I have a schema 'sid' with a table 'student_table'.

1) Login as a admin on your database .
    [dbadmin@localhost bin]$  vsql -u
    User name: dbadmin
    Password: 

2) Create the user with a password 

    dbadmin=> create user test identified by 'R';
    CREATE USER


3) Give the newly created user a Grant for the usage on the database.
    dbadmin=> Grant ALL on database vertica to test;
    GRANT PRIVILEGE

4) You can then grant the user the Usage to the schema 
    dbadmin=> Grant Usage on Schema sid to test;
    GRANT PRIVILEGE

5) Finally provide the select grant to the user on the table .
    dbadmin=> Grant select on sid.student_table to test ;
    GRANT PRIVILEGE
    dbadmin=> \q
6) Login with the new user 'test' , You will be able to access both projection storage and 
    your table sid.student_table

    [dbadmin@localhost bin]$ vsql -u
    vsql: Warning: The -u option is deprecated. Use -U.
    User name: test
    Password: 
    Welcome to vsql, the Vertica Analytic Database interactive terminal.

    test=> select * From sid.student_table;
     Student_ID | Last_name | First_Name | Class_Code |      Grade_pt      
    ------------+-----------+------------+------------+--------------------
           9999 | T_        | S%         | PG         | 98.700000000000000
    (1 row)

    test=> select * From projection_storage;
    -[ RECORD 1 ]-----------+-----------------------------------------
    node_name               | v_vertica_node0001
    projection_id           | 45035996273836526
    projection_name         | Student_Table_DBD_1_rep_tet1_v1_node0001
    projection_schema       | sid
    projection_column_count | 6
    row_count               | 9
    used_bytes              | 375
    wos_row_count           | 0
    wos_used_bytes          | 0
    ros_row_count           | 9
    ros_used_bytes          | 375
    ros_count               | 1
    anchor_table_name       | Student_Table
    anchor_table_schema     | sid
    anchor_table_id         | 45035996273756612

Cohlier