Can someone please verify this for me?

From: Nuno Souto <nsouto_at_optusnet.com.au>
Date: Thu, 09 Oct 2003 07:09:25 -0800
Message-ID: <F001.005D2903.20031009070925@fatcity.com>

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).

Received on Thu Oct 09 2003 - 10:09:25 CDT