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



  • Options


    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.






    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
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file