Strange... works fine here (same environment 9.2.0.1 on Win2K server):
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 9 13:43:23 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> create role new_role
2 /
Role created.
SQL> 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;
8 /
Procedure created.
SQL> execute turn_on_role;
PL/SQL procedure successfully completed.
SQL>
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-----Original Message-----
Nuno Souto
Sent: Thursday, October 09, 2003 10: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: Igor Neyman
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).
