Q
Problem solve Get help with specific problems with your technologies, process and projects.

Can't get data with synonym name

I created a public synonym with the same object name to allow me to refer to that object without prefixing the schema name, and I gave a select insert and update grant to public on that object, but I still can't get the data by using the synonym name.

I created a public synonym with the same object name to allow me to refer to that object without prefixing the schema name, and I gave a select insert and update grant to public on that object, but I still can't get the data by using the synonym name.

Assume I do the following:

CREATE PUBLIC SYNONYM emp FOR scott.emp;
GRANT insert ON scott.emp TO public;
GRANT select ON scott.emp TO public;
Then as another user, I can do the following without any problems:
SELECT * FROM emp;
INSERT INTO emp VALUES .....;
If you are still experiencing problems, you'll have to post your exact code along with complete error messages.

Also, it is typically a bad idea to grant privileges to PUBLIC. Grant privileges to only those users that need it. If granting privileges to multiple users is cumbersome, consider granting to a role you create and then grant that role to those users.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close