Issue with roles and grants
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?