Home » SQL & PL/SQL » SQL & PL/SQL » Login as different user inside Procedure?
Login as different user inside Procedure? [message #217046] Wed, 31 January 2007 08:35 Go to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
I have written a procedure to automate some processes. There are execute immediate statements contained within and only one user has the ability to run these due to permmisions. Instead of logging into the schema(which is the user that has rights) I would like to be able to connect as that user within my pl/sql procedure. This will also enable others on my team to run this as well without having to log in as the schema once i complete this procedure.

So how does one do this?

Any help greatly appreciated.
Re: Login as different user inside Procedure? [message #217052 is a reply to message #217046] Wed, 31 January 2007 08:56 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You can use:
alter session set current_schema = <new_schema_name>;
Re: Login as different user inside Procedure? [message #217056 is a reply to message #217052] Wed, 31 January 2007 09:03 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
Ok a little new to this so lets say I have this...

create or replace procedure test_file(
table_name varchar2)
is
begin
execute immediate 'create table '||table_name||' (i integer)';
end;

where do I put that, and where do I put login info for the schema I am trying to login as?
Re: Login as different user inside Procedure? [message #217061 is a reply to message #217056] Wed, 31 January 2007 09:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Create the procedure as the privileged user and grant execute on it to everyone who needs it.
That is what stored procedures is all about!
Re: Login as different user inside Procedure? [message #217068 is a reply to message #217056] Wed, 31 January 2007 09:54 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Sorry, didn't catch that only the one schema owner has the appropriate privileges.

ALTER SCHEMA will not provide you the privileges of that ONE user, you'll still be restricted to the privileges assigned to the user you are actually signed in as.
Re: Login as different user inside Procedure? [message #217317 is a reply to message #217068] Thu, 01 February 2007 12:03 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
Thanks Frank... worked perfectly.
Re: Login as different user inside Procedure? [message #217361 is a reply to message #217317] Thu, 01 February 2007 17:40 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Biohazardbill, just curious, were you looking to create a table in the schema that you wanted all the other users to connect as?
Previous Topic: How to install sql*loader in ubuntu
Next Topic: Materialized view ""Refresh"" taking long time
Goto Forum:
  


Current Time: Sat Dec 03 07:53:27 CST 2016

Total time taken to generate the page: 0.09510 seconds