Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 helpful 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
share|improve this question
1  
Do NOT create tables as the user SYSTEM, SYS or any other Oracle "internal" user. Create a user for your application and use its schema as a base. –  Phil Sep 8 '13 at 20:01
    
@Phil: Thanks for pointing that out. Edited the question by replacing SYSTEM.Table_1 with ADMIN.Table_1. –  bird234 Sep 8 '13 at 20:18
    
First off, are you certain that the table was created in the SYSTEM schema? As @Phil points out, that is a bad practice. Talking about a generic "database administrator" doesn't help us much. Second, if you are going to create a private synonym, Alice would need to create that. Creating a private synonym in the SYSTEM schema won't help. –  Justin Cave Sep 8 '13 at 20:19
    
@JustinCave Alice executed CREATE SYNONYM Syn_Table_1 FOR ADMIN.Table_1;, however got the error insufficient privileges –  bird234 Sep 8 '13 at 20:31
    
In addition to ensuring you're in the right schema, try this: once logged in as Alice, do: SET ROLE Viewer IDENTIFIED BY pwdviewer; For more see here –  r.m Sep 8 '13 at 22:08

3 Answers 3

up vote 1 down vote accepted

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

share|improve this answer

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;
share|improve this answer
    
Thank you very much for the helpful reply. Following your suggestion, TABLE_1_OWNER was added. However, the error does not go away. I updated the question. Thanks again. –  bird234 Sep 8 '13 at 19:55

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

share|improve this answer
    
Thank you very much for the clear reply. I tried 1) giving fully-qualified names, 2) Using synonyms and 3) Altering sessions. Unfortunately, none of those methods work. –  bird234 Sep 8 '13 at 20:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.