Granting select privileges on synonym
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Granting select privileges on synonym

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    Granting select privileges on synonym

    Dear all,

    Here is my situation. I am creating synonym for the table which is in Remote database. Synonym creation is successful. After that, i am granting select privileges on synonym to other schema's in the same database. How do i resolve this.

    I can create the same synonym in all other schema's. But i do not want that.

    SQL> CREATE SYNONYM gt_ausp FOR infsapi.AUSP@odsprd_i_box;

    Synonym created.

    SQL> GRANT SELECT ON gt_ausp TO raventst
    2 ;
    GRANT SELECT ON gt_ausp TO raventst
    *
    ERROR at line 1:
    ORA-02021: DDL operations are not allowed on a remote database

    SQL>

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    This is some what the similar set up we carry out our site. The way to do this is to create the synonym to the remote table with a different name. Then create a view with the same name of the remote table. then grant that view to the users. This way the users need not know what the remote table nor the synonym. If you do any refresh all you need would be to rebuild the synonym.

    Hope this would help you.
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Dec 2005
    Posts
    195
    Sam, Thanks for your response. It helped now. But what i did here is,

    Create a view for remote table, then grant select privileges to all other schemas in the same database. It solved my problem.

    From your statement, why would we need to create the synonym to the remote table with a different name. Then create a view with the same name of the remote table. then grant that view to the users.

  4. #4
    Join Date
    Feb 2007
    Posts
    212
    yes dear...i found similar situation with the apps express (htmldb). Its web
    page forms can not be based on synonyms but on views or tables only.
    I wonder whats the logic behind the views

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by jennifer2007
    I wonder whats the logic behind the views
    mmhhh... a select statement?... just extract the view code and look at it
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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