CODEWITHSUNDEEP
stored-proceduresoracle11gdatabase-schematoad
Victor
Victor

Reputation: 17097

Oracle stored procedure synonyms and grants not working as expected

In Oracle, when I create a stored procedure under user1, what is the difference between creating a public synonym on the procedure and granting execute rights on the procedure to the user 'PUBLIC'?
My objective is this: User 2 should login in TOAD, go to Schema Browser, and select the PUBLIC schema from the drop-down and be able to see and execute the procedure.

I tried both giving execute grants to user2 and PUBLIC and also create a PUBLIC synonym....but still no luck. user2 cannot see the procedure under the PUBLIC schema.

But funny thing is when I login as user2, open up an editor in TOAD (not schema browser), then I can execute the procedure using the :

BEGIN
PROC(arg1,arg2,arg3);
end;

Upvotes: 1

Views: 9985

Answers (1)

Gaurav Soni
Gaurav Soni

Reputation: 6336

An Oracle synonym basically allows you to create a pointer to an object that exists somewhere else. You need Oracle synonyms because when you are logged into Oracle, it looks for all objects you are querying in your schema (account). If they are not there, it will give you an error telling you that they do not exist or not declared.

In your case user1 create a proc and it will exist in his schema.For user2 ,its just a pointer to that .So you can't see his object in your schema or public schema.

Since user2 have execute grants ,so this means that when user2 try to execute proc from his schema then it will try to look proc in his schema ,if he dint find the proc then it will look to the pointer ,which he finds from the synonym .And finally he finds the proc and try to execute it ,since the proc have public execute grants(which means that any user can execute them) then it will able to execute the proc.

And if you go to schema browser in your toad,you'll find a tab for synonym ,where you'll find that you have synonym for proc in user2 schema and even public schema .Sorry for bad English :(

Upvotes: 2

Related Questions