Access right on synonym and underlying table

Question

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 ?

Answer 1

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.

Answer 2

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