Select nor working in new user
I have created 'emp' table in oracle 9.2 schema 'scm1' as a user 'scm1' and inserted few records. The below 'select' command works fine.
I have created a new user 'user1' and granted 'select' privilege on the above table to this new user.
After I login to user 'user1', If I say
'select * from emp;' this says 'table or view not found'. But If I say 'select * from scm1.emp;' it works fine.
I want to use 'select * from emp;' under the new user. Please help. Thanks.
# 3 Re: Select nor working in new user
There is no alternative method. You need to create a public synonym in order for users, other than the schema owner, to reference the table without a qualifying schema name.
# 4 Re: Select nor working in new user
I did login to the new user 'user1' and created synonym using 'create synonym emp for scm1.emp;' I am able to use all DML commands. How can I prevent this synonym from using INSERT,UPDATE, DELETE. I mean only SELECT should work.
# 5 Re: Select nor working in new user
Use the Grant/Revoke commands to specify privileges.
http://www.techonthenet.com/oracle/grant_revoke.php
