It worked for me 9.2.0.2 Solaris 2.8
But I'm surprised since I always thought that roles are disabled in stored
procs.
Even it worked for me but it's still disabled in the stored proc after the
execute immediate.
Waleed
-----Original Message-----
Sent: Thursday, October 09, 2003 11:09 AM
To: Multiple recipients of list ORACLE-L
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Khedr, Waleed
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).
