Issue with roles and grants

sameerdurrani

Hi  

 

 

I created a user Fred.

then I created a role reporter and assigned Fred to it using following:

 

GRANT reporter to Fred

 

Then i gave the following rights to the role reporter

 

GRANT SELECT ON <schema_name> to reporter
GRANT ALL ON ALL TABLES IN SCHEMA <schema_name>  TO reporter

 

Then when I login as user Fred , I set the role

 

SET role reporter

 

but as Fred , I cant see the tables in the SCHEMA <schema_name>.  I can see the SCHEMA_NAME in the SQL editor navigation but I cant see the tables.

 

What am i doing wrong here? Is there any thing wrong here? Am i missing something.

 

It works when i grant the access on the schema directly to the uers i.e 

 

GRANT SELECT ON <schema_name> to Fred

GRANT ALL ON ALL TABLES IN SCHEMA <schema_name>  TO Fred

 

It works fine in that case and the user Fred can see the tables in the Sql editor navigator.

 

 

What am i doing wrong here? Why is the role not working properly?

 

-Sameer

 

ckotsidimos

Try this as Fred and send the outcome

 => SET ROLE ALL;SET

=> SHOW ENABLED_ROLES;

Sharon_Cutter

Hi Sameer,

 

Try granting USAGE on the schema to the role as well.

 

  --Sharon

 

sameerdurrani

I actually tried this

 

GRANT ALL ON <schema_name> to Fred

GRANT ALL ON ALL TABLES IN SCHEMA <schema_name>  TO Fred

 

and then again checked Fred, and it couldnt see all the tables. 

I am granting all that i can to Fred. Is there something else that i need to check as well.

 

Regards, 

Sameer Durrani

ckotsidimos

Grant the Role to Fred after you give the permissions to the Role! 

Sharon_Cutter

> GRANT ALL ON <schema_name> to Fred

 

The syntax for granting to a schema is GRANT ... ON SCHEMA <schema_name>.