Why does "GRANT SELECT ... TO Role;" not allow members of Role to SELECT?

Question

Please consider the following scenario on a Oracle 11g Database.

The user ADMIN performs the following:

CREATE USER Alice IDENTIFIED BY pwdalice;

GRANT CREATE SESSION TO Alice;

CREATE ROLE Viewer IDENTIFIED BY pwdviewer;

GRANT Viewer TO Alice;

GRANT SELECT ON Table_1 TO Viewer;

Then Alice logs into the database as 'Alice' and executes the following command:

SELECT * FROM Table_1;

Alice gets the following error:

SELECT * FROM Table_1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

I thought that granting privileges to a role would enable its member-users to get those privileges. However, this scenario shows that it is not so. What am I missing here? How can I grant SELECT to Alice using a role?

Update:

Following the answers, tried 3 fixes with no success

1) Using Fully-qualified Table Names

I missed to include the schema name in SELECT * FROM Table_1; command. However, even after adding the schema name as shown below, still the error comes.

Alice executes:

SELECT * FROM ADMIN.Table_1;

Gets the error:

SELECT * FROM ADMIN.Table_1
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

2) Using a synonym for the fully-qualified table name

Unfortunately, this does not seem to solve the problem either.

Alice executes the following:

CREATE SYNONYM Syn_Table_1 FOR ADMIN.Table_1;
CREATE SYNONYM Syn_Table_1 FOR ADMIN.Table_1
*
ERROR at line 1:
ORA-01031: insufficient privileges

3) Altering the session

ALTER SESSION SET current_schema = ADMIN;

Session altered.

SELECT * FROM Table_1;

SELECT * FROM Table_1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Answer 1

In addition to ensuring you're in the right schema, try this one;
Once logged in as Alice, do: SET ROLE Viewer IDENTIFIED BY pwdviewer;

For more see here

Answer 2

The role you created is password protected. Therefore it is not enabled by default, when alice logs in.

Answer 3

You need to use the fully-qualified name of the table, as Alice does not own the table.

For example, if the table is owned by TABLE_1_OWNER:

SELECT * FROM TABLE_1_OWNER.TABLE_1;

`Alice can also create a synonym in her own schema that points to the table, thus removing the need to fully qualify the name:

CREATE SYNONYM TABLE_1 FOR TABLE_1_OWNER.TABLE_1;

Answer 4

The problem is one of name resolution, not privileges.

The table table_1 exists in some schema other than the Alice schema. When Alice runs the query

SELECT * 
  FROM table_1

Oracle will look in the current schema to see if there is an object named table_1. Since there is no such object, it throws an error.

You can fix that by telling Oracle what schema the object resides in by qualiying the table name

SELECT * 
  FROM <<owner of table_1>>.table_1

Alternately, you can create a synonym (public or private) that provides the mapping. As Alice, for example, you can create a private synonym

CREATE SYNONYM table_1
   FOR <<owner of table_1>>.table_1

Once you do that, Alice can simply

SELECT * 
  FROM table_1

because there is an object in Alice's schema named table_1 (a private synonym for the actual table).

A third option would be to change the current schema for the session. Again, as Alice, if you run

ALTER SESSION SET current_schema = <<owner of table_1>>;

then you'll be able to run

SELECT * 
  FROM table_1

If you do that, however, then you would have to fully qualify table names if they exist in the Alice schema (unless, of course, you change the current_schema again).

Answer 5

if you still have this problem after applying the best practice approach of synonyms and privileges granted to roles, make sure that the grantee's user account has all roles enabled: ALTER USER DEFAULT ROLE ALL

Answer 6

In addition, I see that you need to make a new connection with the user to allow the grant via the role. A "simple" reconnect doesn't work.

Answer 7

By default Admin name is system.

So when Alice logs into the database as 'Alice' and execute the following command:

SELECT * FROM system.Table_1;

Note: check the user to whom the table belongs, say if table belongs to user shub write query as:

SELECT * FROM shub.Table_1;

Answer 8

If you haven't solved this issue, try to first create the role and add the corresponding permissions. Then create the user and assign the role to it.

In this order I think it should work. It worked for me and I was having the same issues you were experiencing.

Answer 9

In similar situation, following worked for me.

sqlplus / as sysdba

grant connect, resource, create view, select any table, create synonym, select any dictionary to [your user];

I know that this solution is not idle for all the cases but this was my last resort.