1

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
  • 3
    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. – Dilini Sep 8 '13 at 20:18
  • 1
    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 – Dilini Sep 8 '13 at 20:31
  • 1
    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
2

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

2

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

  • This is more of a comment than an answer. Could you please flag it for moderators to change it into a comment. You can comment on all posts when you have enough reputation. P. S. Welcome to the forum! – Vérace Aug 13 '15 at 8:09
1

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;
  • 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. – Dilini Sep 8 '13 at 19:55
1

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

  • 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. – Dilini Sep 8 '13 at 20:10
  • Actually, in this case it is privs. The role was granted however it is not being set (and it isn't a default role either). – gsiems Feb 3 '17 at 20:06
0

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

0

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.

-1

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;
-4

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.

  • 1
    There isn't much point to using roles if you're going to grant "select any table" to the user (neither "select any table" or "select any dictionary" should be handed out like candy) and your other grants are rather orthogonal to the question asked. All the user should need is "create session" and the role-- provided they actually set the role. – gsiems Feb 3 '17 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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