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.