Granting select to a role

larrimorem1

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

 

K_Krutiy

 

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