Home » SQL & PL/SQL » SQL & PL/SQL » Synonyms, Execute Immediate, ***on another user for another user***
Synonyms, Execute Immediate, ***on another user for another user*** [message #353716] Tue, 14 October 2008 17:14 Go to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
I know the first answer that is going to be given it that privileges granted via roles do no operate in a procedure, so let's just get that out of the way. I know this is the problem, what I do not know is what privilege I need. I know that the 'create synonym' privilege isn't it Sad.

I have a table BOB on users SCOTT_A and SCOTT_B with public accessibility. I have another 2 other users: SCOTT_LOADER, and SCOTT.

I have a procedure on SCOTT_LOADER that runs:
EXECUTE IMMEDIATE 'create or replace synonym SCOTT.BOB for SCOTT_A.BOB';

This procedure fails with ORA-01031, ORA-06512 errors.

All of SCOTT, SCOTT_A, SCOTT_B, SCOTT_LOADER have the create synonym privilege.

Thanks!
Re: Synonyms, Execute Immediate, ***on another user for another user*** [message #353722 is a reply to message #353716] Tue, 14 October 2008 19:04 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
see "create ANY synonym". You can't just create synonyms another schema without extra permissions.
Re: Synonyms, Execute Immediate, ***on another user for another user*** [message #353920 is a reply to message #353716] Wed, 15 October 2008 11:06 Go to previous message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Excellent, that was exactly it. Thanks!
Previous Topic: Finding empty strings with LIKE
Next Topic: SQl Server 2005 to Oracle 10g Proc conversion
Goto Forum:
  


Current Time: Fri Dec 09 19:33:24 CST 2016

Total time taken to generate the page: 0.24489 seconds