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?
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.
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