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

oracleschemasynonyms

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.

Best Answer

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.
Related Question