Granting select privileges on synonym
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;
SQL> GRANT SELECT ON gt_ausp TO raventst
GRANT SELECT ON gt_ausp TO raventst
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database
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.
Life is a journey, not a destination!
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.
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
mmhhh... a select statement?... just extract the view code and look at it
Originally Posted by jennifer2007
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.
Click Here to Expand Forum to Full Width