Sure... there you go! SQL> create role new_role identified by password;
Role created. SQL> 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 6 'set role new_role'; <<---- 7* end; SQL> / Procedure created. SQL> set role none; Role set. SQL> select * from session_roles; no rows selected SQL> exec turn_on_role; BEGIN turn_on_role; END; * ERROR at line 1: ORA-01979: missing or invalid password for role 'NEW_ROLE' ORA-06512: at "SYSMAN.TURN_ON_ROLE", line 5 ORA-06512: at line 1 SQL> ed Wrote file afiedt.buf 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 6 'set role new_role identified by password'; <<- 7* end; SQL> / Procedure created. SQL> exec turn_on_role; PL/SQL procedure successfully completed. SQL> --- Nuno Souto <[EMAIL PROTECTED]> wrote: > I have a problem with the new procedure based roles, > "Secure Application Roles". > The following is taken from an example in ASKTOM. > Basically, I'm trying to setup a role that is > enabled or not by a procedure. The original code > from Tom: > > [EMAIL PROTECTED]> l > 1 create or replace procedure turn_on_role > 2 authid current_user > 3 as > 4 begin > 5 execute immediate > 'set role new_role identified by > password'; > 6* end; > [EMAIL PROTECTED]> create role new_role identified by > password; > Role created. > [EMAIL PROTECTED]> set role none; > Role set. > [EMAIL PROTECTED]> select * from session_roles; > no rows selected > [EMAIL PROTECTED]> set role new_role; > set role new_role > * > ERROR at line 1: > ORA-01979: missing or invalid password for role > 'NEW_ROLE' > [EMAIL PROTECTED]> exec turn_on_role; > PL/SQL procedure successfully completed. > [EMAIL PROTECTED]> select * from session_roles; > ROLE > ------------------------------ > NEW_ROLE > [EMAIL PROTECTED]> > > > Now, if I try this using what I need: > 1 create or replace procedure turn_on_role > 2 authid current_user > 3 as > 4 begin > 5 execute immediate > 'set role new_role'; > 6* end; > > and then try to run it: > > > exec turn_on_role; > > I get a ORA-6565 error: > "Cannot execute SET ROLE from within stored > procedure" > > Any ideas what am I missing here? > 9.2.0.1, Win2K. > Did the usual searches everywhere including > Metaclick, > nothing that I can relate to... > > TIA for any help. > Cheers > Nuno Souto > [EMAIL PROTECTED] > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Nuno Souto > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
