CODEWITHSUNDEEP
oracle-databaseoracle11goracle10gprivilegessynonym
Kenny
Kenny

Reputation: 1982

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 ?

Upvotes: 5

Views: 58633

Answers (3)

Ashish Mishra
Ashish Mishra

Reputation: 83

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.

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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.

Upvotes: 11

ArtBajji
ArtBajji

Reputation: 960

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

Upvotes: 3

Related Questions