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

Using roles/grants vs. public synonym

I created two users, U1 and U2, then created role R. I created tables in user U1 and gave grants to select, insert, update and delete to role R. I assigned role R to U2. Now if I log in through user U2 then I am able to see all objects of user U1. I can perform a similar operation by creating a public synonym in U1. What is the difference between these two approaches? Is there any question of efficiency? If yes, then which one is better?

I have the following query. I created two users, U1 and U2, then created role R. I created tables in user U1 and gave grants to select, insert, update and delete to role R. I assigned role R to U2. Now if I log in through user U2 then I am able to see all objects of user U1. I can perform a similar operation by creating a public synonym in U1, i.e., I create a public synonym in user U1 and see all objects of user U2.

What is the difference between these two approaches? Is there any question of efficiency? If yes, then which one is better?

Granting to PUBLIC is easier, but much more dangerous. It is a rule for database security that you only grant privileges to the users that need them. Furthermore, you only give the user the ability to do the bare minimum things they need to do. In today's security-conscious IT environments, granting more than they need and to more than just the people that need it is a way to open up your database with wide security holes. And it could cost you your job.

Use roles to help you administer security. That is what they were designed to do. I never grant any privilege to PUBLIC and I've never had a reason to do so.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

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