Unable to grant any privileges to user in oracle 11gR2 except create session

1.5k views Asked by At

I am using oracle database 11g Release 2 I am able to create user and give it create session privileges, but I am unable to give it select privilege.

create user user1 identified by pass;
User created.
grant create session to user1;
Grant succeeded.
grant select on emp to user1;
Grant succeeded.

After this I connect as user1 Now when I run this statement it say

select * from emp;

oracle reply=

ERROR at line 1:
ORA-00942: table or view does not exist

Than I checked privileges to user1 using

select * from session_privs;
PRIVILEGE
---------------------------------
CREATE SESSION

Which means only create session privilege is available to user1. How can I give select privileges to user1?

1

There are 1 answers

1
Mureinik On BEST ANSWER

Giving a use the SELECT privilege (or any other privilege for that matter) does not create a synonym. As user1, who is not the table's owner, you should still reference the table by its fully qualified name, with the owner. Assuming the owner is called owner1, user1's query should be:

SELECT * FROM owner1.emp

As for the data dictionary query, this is also to be expected. These privileges are recorded in the [DBA|ALL|USER]_TAB_PRIVS views:

SELECT * FROM all_tab_privs WHERE grantee = 'USER1'