Oracle-L: RE: Can someone please verify this for me?

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Thu, 09 Oct 2003 10:54:43 -0800
Message-ID: <F001.005D2949.20031009105443@fatcity.com>

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

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
ineyman_at_perceptron.com

-----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:

ops$tkyte_at_DEV816> 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;
ops$tkyte_at_DEV816> create role new_role identified by password; Role created.
ops$tkyte_at_DEV816> set role none;
Role set.
ops$tkyte_at_DEV816> select * from session_roles; no rows selected
ops$tkyte_at_DEV816> set role new_role;
set role new_role
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'NEW_ROLE' ops$tkyte_at_DEV816> exec turn_on_role;
PL/SQL procedure successfully completed. ops$tkyte_at_DEV816> select * from session_roles; ROLE

NEW_ROLE
ops$tkyte_at_DEV816>

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
nsouto_at_optusnet.com.au

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nuno Souto
  INET: nsouto_at_optusnet.com.au

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: ListGuru_at_fatcity.com (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: ineyman_at_perceptron.com

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: ListGuru_at_fatcity.com (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).

Received on Thu Oct 09 2003 - 13:54:43 CDT