We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Granting select to a role — Vertica Forum

Granting select to a role

I'm having an issue, where I want to create a role and grant select on all the tables in my database to that role.

 

So far as an example:

 

I've created the role,   "create role tmp_role;"

 

granted usage on that role to all the schemas,    "grant usage on schema tmp_schema to tmp_role;"

 

granted select on all tables to that role, "used a sql generated sql"

 

granted that role to the a user,  "grant tmp_role to tmp_user;"

 

granted usage on schema to the user, "grant usage on schema tmp_schema to tmp_user;"

 

alter that user to have that as a default role, "alter user tmp_user default role tmp_role"

 

 

 

Seems I'm still not seeing any of the tables in the database.. is there something I'm missing when it comes to using roles?

 

 

Thanks for the help in advance

 

Comments

  •  

    Steps looks correct.

    It all statements were successful it should work.

     

     

    Here are some checks you can do

     

     

    LOGIN as tmp_user

     

    1. Verify that role is granted to user. Run :     show available_roles;

    2. Verify that role is enabled in session. Run :     show enabled_roles;

     

    You should see tmp_role in both cases.

     

     

     

    LOGIN as DBADMIN

     

    1. Verify that role granted to user. Run: select * from grants where grantee = 'tmp_user';

    You should see line for ROLE grant to USER

     

    2. Verify that needed permissions granted to role. Run: select * from grants where grantee = 'tmp_role';

     

    You should see a lot of lines for each SELECT/USAGE grant to ROLE

     

     

     

Leave a Comment

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