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
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 theCREATE 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.