Public Synonyms
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Public Synonyms

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    Hi:
    I just created a schema with few tables and granted public synonyms on those tables.. when i log in as other users and try to access the tables I cannot.. but, when i log in as system user, I can.. what could be the problem?? Urgent..

  2. #2
    Join Date
    Jan 2000
    Location
    *
    Posts
    37
    You have created the synonyms but did you grant privileges? You either need to grant privileges such as select to public or to to the indivildual users. Otherwise they can only describe the table.

    magskn

  3. #3
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    you need to "create" public synonyms and then "grant" permissions on those synonyms to public (or specific users)

    - Rajeev

  4. #4
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    rsuri -
    No, you don't grant privileges on Public synonyms. By definition, they are accessible by all users in the database (hence the term 'Public'). However, in order to be of any use, a user DOES have to have privileges granted on the underlying object.

    Example:
    I create table carp.emp.
    If I want to let you have access to the table, I grant the privilege:
    GRANT SELECT ON emp TO rsuri;

    You can now select from the table, but must fully qualify the table name:
    SELECT * FROM carp.emp;

    Now I create a public synonym:
    CREATE PUBLIC SYNONYM employee FOR carp.emp;

    Without any further granting, you can now successfully execute:
    SELECT * FROM employee;
    and see the data in carp.emp.

    Finally, I revoke the privilege on the table:
    REVOKE SELECT ON emp FROM rsuri;

    Even though you can still "see" the synonym, when you try
    SELECT * FROM employee;
    you will get a "Table or View Does Not Exist" error.

    Contrary to a previous post, you will get a similar error if you try to do a DESC on a public synonym where you have no privileges on the underlying object:
    DESC employee
    ERROR:
    ORA-04043: object carp.emp does not exist

  5. #5
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    oops.
    You are right Carp; Thanks for correcting.

    - Rajeev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width