oracle-databaseoracle11goracle10gprivilegessynonym

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 ?


Solution

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