0

Is it possible grant a PRIVATE Synonym from an the Table Owner schema to an User schema?

Background: We are doing microservice development and there is an sole owner schema and many different service accounts (schemas) that have been granted access to the tables created by the owner. At the moment, it's a nuisance to have to log into each service account to create synonyms. Is there a way to run a script to do it from the owner schema? We're trying to automate this process.

  • unless owner has create any synonym privilege, it is not possible to create private synonym in someone else's schema. – Raj Sep 11 '17 at 19:49
  • 1
    Or just use a public synonym for each table? – Philᵀᴹ Sep 11 '17 at 19:50
0

It can be done as SYSTEM (or a user with DBA privileges) or if 'create any synonym' is granted to the user.

SQL> create user test1 identified by test1;    
User created.

SQL> create user test2 identified by test2;    
User created.

SQL> grant create session to test2;    
Grant succeeded.

SQL> create table test1.xtable (x number);    
Table created.

SQL> create synonym test2.xtable for test1.xtable;    
Synonym created.

SQL> drop synonym test2.xtable;    
Synonym dropped.

SQL> conn test2/test2
Connected.
SQL> create synonym xtable for test1.xtable;
create synonym xtable for test1.xtable
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>  conn system/manager
Connected.

SQL> grant create any synonym to test2;    
Grant succeeded.

SQL>  conn test2/test2
Connected.
SQL> create synonym xtable for test1.xtable;    
Synonym created.
  • "grant create synonym to test2;" is sufficient so that user test2 can create a synonym in his schema. No "any" is needed. The private synonym that is created here is the synonym test2.xtable by the user test2. What the OP wants is to create a synonym in another user's schema. So user test1 should create the synonym test2.xtable. So you have to "grant create any synonym to test1", then test1 can successfully "create synonym test2.xtable for test1.xtable; " – miracle173 Aug 13 at 5:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.