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>

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.

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:)

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 ;)