Linked server SQL <->Oracle and a revelation about using Oracle Synonyms in Four part query

I was working on one the Enterprise customer Incident. The issue at hand was that we were unable to use Oracle Synonyms in a Four part naming query against a SQL-Oracle Linked Server. To probe the scenario, I had to set up a repro locally.

Step 1 : To Create a test user on Oracle

Login using a SYSTEM account ( This would preferably be a SYSADMIN account ). In this scenario, Oracle10g host name was ORCL.

Create user TEST identified by TEST ;

Grant connect, resource to TEST ;

Grant create synonym to TEST ;

Alter user SYSTEM account unlock;

Connect SYSTEM / ******* @ ORCL

Grant select on EMP to TEST ;

Connect TEST / TEST @ ORCL

Create synonym EMP FOR SYSTEM.EMP ;

 

Step 2 : Creating Linked Server in SQL Server 2005

Here in my example , I'm calling the Linked Server as TIGER. Specify the product name as Oracle and Data source as ORCL.

 

In the security page of the linked server, choose the option "Be made using this security context" and specify the login of the Test user created previously.

 

To test the issue , I created a table EMP in Oracle with the following contents:

 

Once this done, we start querying using four part query:

 

Further continuing, when we use Oracle Synonym in our four part query we observe the following:

 Full Error message is:

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "TIGER" does not contain the table ""Test"."EMP"". The table either does not exist or the current user does not have permissions on that table.

After spending some quality time doing research on this issue, found that this is a limitation in the Linked Server infrastructure when using Oracle Synonym in four part query.

Found that open query works fine using the Synonym. The other method to execute a four part query would be to use Oracle views instead of Synonyms.

Hence dropped the synonym which I had created earlier by running the following query :

 Drop synonym EMP ;

Next created a view by running the following query :

Create view EMP as select * from SYSTEM.EMP;

 

Next when i tried to run the four part query again but instead of oracle synonym , I used the Oracle View and the result was as below:

Conclusion :  

Oracle synonyms will not work with four part queries in a SQL-Oracle linked server.  Workaround is to use Open query or to use Oracle views instead of synonyms. A revelation after a long overhaul of trials and errors.