| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Can someone please verify this for me?
I haven't tried using these before, but I do notice that your
'create role' syntax appears to be incorrect for this usage.
Assuming the creating user is scott , it appears it should be:
create role new_role identified using scott.turn_on_role
Here are the results from my modified version of this: 9.2.0.4 on RH 7.3
13:19:54 dv03>@s 13:19:55 dv03> 13:19:55 dv03>drop role new_role;
Role dropped.
13:19:55 dv03>
13:19:55 dv03>create or replace procedure turn_on_role
13:19:55 2 authid current_user
13:19:55 3 as
13:19:55 4 begin
13:19:55 5 dbms_session.set_role('NEW_ROLE');
13:19:55 6 end;
13:19:55 7 /
Procedure created.
13:19:55 dv03>
13:19:55 dv03>show errors procedure turn_on_role
No errors.
13:19:55 dv03>
13:19:55 dv03>create role new_role identified using jkstill.turn_on_role;
Role created.
13:19:55 dv03>
13:19:55 dv03>set role none;
Role set.
13:19:55 dv03>
13:19:55 dv03>select * from session_roles;
no rows selected
13:19:55 dv03> 13:19:55 dv03>set role new_role 13:19:55 2 13:19:55 dv03>exec turn_on_role
PL/SQL procedure successfully completed.
13:19:55 dv03> 13:19:55 dv03> 13:19:55 dv03>select * from session_roles;
ROLE
13:19:55 dv03>
Jared
"Nuno Souto" <nsouto_at_optusnet.com.au>
Sent by: ml-errors_at_fatcity.com
10/09/2003 08:09 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Can someone please verify this for me?
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;
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';
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: INET: Jared.Still_at_radisys.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 - 16:24:25 CDT
 |
 |