Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

Is it possible to use/log in to a different account in pl/sql. For example, I would like to write to a file in a folder only a specific account has access to. Here's an example of what I mean:

Other code...
LOG_IN_OTHER_USER('SPECIALUSER', 'SECRETPASSWORD');
UTL_FILE.PUT_LINE(OutputFile,the_outfile);
UTL_FILE.FFLUSH(OutputFile);
LOG_OFF_OTHER_USER('SPECIALUSER');
Other code...
share|improve this question

2 Answers

up vote 4 down vote accepted

Assuming 11g. There are a few ways to do this. Easiest is to use the Oracle Job Scheduler with the Remote Job Agent. That agent can run using credentials for a qualified OS account. You could run a job that selects data from the database and spools it in the required output directory.

You could also copy a file to the Remote Job agent. This is closest to the code flow you described. For docu and examples see the book I wrote about the Oracle Scheduler.

There are no ways to switch OS accounts directly in pl/sql because whatever you do in the database, you are using the credentials of the Oracle Database Instance. The Oracle Remote Job Agent can use own credentials and is accessible from within pl/sql by using the dbms_scheduler package.

share|improve this answer
Yep, in Oracle SPs execute as their owner, not as the calling user, all that is needed is for that user to have execute priv granted – Gaius Apr 27 '11 at 15:47
Definer and Invoker rights relate to Oracle users not Windows users. Kaze indicates that the PL/SQL needs to run as a different Windows user not a different Oracle user. – Leigh Riffel Apr 27 '11 at 17:04
1  
@Leigh Riffel that's why I mentioned the Remote Job Agent first. That can use the credentials of an OS account. The question is about 'an other user in pl/sql' so I interpreted that as an Oracle account. The Remote Job Agent can handle both situations; it is called Remote but there is nothing against it running locally to the database. – ik_zelf Apr 27 '11 at 19:48
@ik_zelf - The title saying “Use different windows account” and the limitation that only SPECIALUSER has write access to a particular folder indicate that the OP is trying to deal with windows permissions. The method you describe while a feasible workaround does not do this. It does use a different windows account which can call PL/SQL, but your workaround requires SQL to be run by the job. If the other user calls PL/SQL, that PL/SQL will run with the same windows permissions as the oracle service. – Leigh Riffel Apr 27 '11 at 21:32
@Leigh yes, you are right, re-reading the title helps. However, the copy to the job agent does exactly what the OP wants. You can copy a blob from pl/sql to a file that is written by the job agent, using the credentials of the special user. No seperate job needed. I think my blog has a post on it, same for the book. Oracle Scheduler is real powerful. Often underestimated. – ik_zelf Apr 27 '11 at 22:46
show 4 more comments
up vote 3 down vote

No, but there are ways to do what you are trying to do.

PL/SQL runs with the same OS context and permissions as the Oracle service, so even if you run a procedure as the other windows user, the OS permissions in the PL/SQL will still match the Oracle services. In order to get around this you will have to in some way get the data to where the other user can pick it up or have the other user get the data directly from the database. Here are two ideas, but there are probably many more.

You could create the file in a location that can be reached and then have a job running in the context of the other user that moves it to the protected folder.

Another way would be to have a client application build the file itself logged in as the other user. ik_zelf's answer has an excellent way to do this.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.