Linked Server

I have an oracel linked server that I use openqueryset statements to read the oracle tables.

However, I want to update some data in a couple of these oracle tables. The linked server is setup using a readonly user. I'd like to be able to call an Oracle Set Role to grant me update capability and then perform the update.

Can anyone help me out with some possibilities?

See SQL Server 2005 Books Online

OPENQUERY (Transact-SQL)

This is a KB error article but it has useful information on using OPENQUERY

http://support.microsoft.com/kb/q270119/

|||

Well... does not really apply. What I am thinking about doing is calling an oracle stored procedure that grants the linked server account update privledges on the table. Then submit an openquery statement to do the update.

However, I am not sure if the privledge granted from the prcale stored procedure will still be in effect when the second openquery statement is issued.

The reason why I am doing this is for security reasons to not allow a linked server "default" update privledges on the Oracle database.

|||

In case anyone needs to do this... or something similar... I have figured it out:

On Oracle server, create the Oracle Package and Procedure:

create or replace
PACKAGE BODY Call_SEC_ROLE_Package
AS
PROCEDURE sec_roles
(ReturnVal OUT SqlReturnTbl)
is
begin
dbms_session.set_role('apply_security identified by secure_pass');
ReturnVal(1) := '1';
end sec_roles;
end Call_SEC_ROLE_Package;

On SQL Server create your linked server and then call the Oracle stored procedure by:

SELECT *
FROM OPENQUERY
(LinkServerName, '{CALL Call_SEC_ROLE_Package.sec_roles({resultset 1, ReturnVal})}')
go