Access right on synonym and underlying table

1/ How are privileges on synonyms and underlying objects related ? If one has rights on synonym, would he automatically has rights on the table and vice versa ?

Oracle says

When you grant object privileges on a synonym, you are really granting privileges on the underlying object, and the synonym is acting only as an alias for the object in the GRANT statement

which means privilege on synonym is enough. That will bypass table privilege.

Another source says that access right on table is enough and synonym privilege has no meaning.

Does it mean either privilege on the synonym or the underlying table is enough ?

2/ Is the behavior the same for private and public synonym. I haven't really seen an example of granting privileges on synonyms for a user to "see/access". How to grant privilege on private synonyms to a user ?

3 Answers

Both the Oracle docs and the message you referred to say exactly the same thing. Privileges are not granted on a synonym. When you attempt to grant privileges on a synonym the database actually performs the grant on the object referred to by the synonym. Thus, it makes no difference if the synonym is public or private because the actual grant is made on the object referred to by the synonym.

Best of luck.

EDIT

Let's demonstrate what happens:

-- Logged in as user BOB2 CREATE TABLE RPJ_TEST (N NUMBER); SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME = 'RPJ_TEST'; -- the above statement returns no rows CREATE SYNONYM RPJ_TEST_SYN -- create synonym FOR RPJ_TEST; SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME = 'RPJ_TEST'; -- the above statement returns no rows GRANT SELECT ON RPJ_TEST TO BOB; -- grant on table SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME = 'RPJ_TEST'; -- the above statement returns GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY BOB BOB2 RPJ_TEST BOB2 SELECT NO NO GRANT UPDATE ON RPJ_TEST_SYN TO BOB2; -- grant "on synonym" actually performs grant on table SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME = 'RPJ_TEST'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY BOB BOB2 RPJ_TEST BOB2 SELECT NO NO BOB BOB2 RPJ_TEST BOB2 UPDATE NO NO 

Note that after the grant on the synonym RPJ_TEST_SYN the privileges granted on the table referred to by the synonym had been changed.

From Oracle Doc "A public synonym is owned by the special user group named PUBLIC and is accessible to every user in a database. A private synonym is contained in the schema of a specific user and available only to the user and to grantees for the underlying object."

With a public synonym PUBS on TABLE X of Schema B, User A can access User B's table X. With a private synonym PVTS on TABLE Y of Schema B, User A cannot access User B's table Y unless access is granted explicitly as mentioned above.

Check OracleDoc

My two cents:- Suppose there is a table tab1 defined in abc_owner schema and its synonym is created in abc_user schema, then:-

Running a grant like this in the abc_user schema:-

GRANT SELECT ON tab1 TO def_owner; 

might succeed or fail depending on the grants that abc_user has over the objects in abc_owner. If it has only select grants, the above query will fail. And then you will have to do it in the owner schema itself.