Oracle – create synonym ora-01031 insufficient privileges

grantoracleprivilegesschemasynonym

I need help understanding what grants/privileges a user needs to CREATE a SYNONYM when it points to another (different) schema object.

When I try the below, I get ora-01031 insufficient privileges, so obviously I am missing and failing to apply other needed privileges. I did search as well as I could but couldn't find anything specific to cross-schema synonyms.

CREATE USER test IDENTIFIED BY pw DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
ALTER USER test IDENTIFIED BY pw;
GRANT CONNECT, RESOURCE TO test;

-- ... create a bunch of stuff in test...

CREATE USER READWRITE IDENTIFIED BY pw DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE    TEMP;
ALTER USER READWRITE IDENTIFIED BY pw;
GRANT CONNECT, RESOURCE TO READWRITE;

GRANT SELECT ON GDACS.FIXALARMS TO PUBLIC;
GRANT UPDATE, INSERT ON GDACS.FIXALARMS TO READWRITE; 

CONNECT READWRITE/pw;

CREATE SYNONYM FIXALARMS for test.FIXALARMS;
ORA-01031 insufficient privileges

Best Answer

The documentation for the CREATE SYNONYM command includes:

Prerequisites

To create a private synonym in your own schema, you must have the CREATE SYNONYM system privilege.

To create a private synonym in another user's schema, you must have the CREATE ANY SYNONYM system privilege.

To create a PUBLIC synonym, you must have the CREATE PUBLIC SYNONYM system privilege.

You're trying to create a private synonym in READWRITE's own schema, so you have to have to do:

GRANT CREATE SYNONYM TO READWRITE;

The object the synonym is pointing to is in a different schema, but that isn't relevant here.

If your new account is only going to access objects in the GDACS schema, and particularly if you have a lot of objects you want to grant access to, then as an alternative to having to create synonyms for everything you could alter the new user's current_schema in each session - possibly via a logon trigger.

Related Topic