Synonyms used in Oracle

I. BACKGROUND

There are two sid:asid,bsid, two user auser1,auser2 under Asid, and a user Buser1 under Bsid. Among them Auser2 and Buser1 established the Dblink. You now want to access the three tables Table1,table2,table3 information and function function1 on Auser1 by logging on to Buser1.

Second, the steps

① login Auser1 to Auser2 authorization (plus delete if you need to delete)

Grant Insert,select,update on Table1 to Auser2;

Grant Insert,select,update on table2 to Auser2;

Grant Insert,select,update on Table3 to Auser2;

Grant execute on function1 to Auser2;

② Login Auser2 Establish synonyms

Create synonym table1 for auser1.table1;

Create synonym table2 for auser1.table1;

Create synonym Table3 for auser1.table1;

Create synonym function1 for auser1.function1;

③ login Buser1 Find Db_link name (for example, Db_link named Mydblink1)

Select Username,db_link from User_db_links

④ establish a synonym for Buser1

Create synonym table1 for auser2.table1@mydblink1;

Create synonym table2 for auser2.table2@mydblink1;

Create synonym Table3 for auser2.table3@mydblink1;

Create synonym function1 for auser2.function1@mydblink1;

⑤ Test

At this point, the login Buser1 can access table1 through the SELECT * from table1.

Third, the attention point

You can also establish synonyms for procedure, but no commit is allowed during this execution. If you want to submit, commit () in JDBC.