Re: Can someone please verify this for me?
- Original Message - I haven't tried using these before, but I do notice that your 'create role' syntax appears to be incorrect for this usage. Sorry for the late reply, folks. Rugby World Cup got in the way... :D Yeah, found out what the problem was after all. I assumed that authid definer was redundant (which was the case I was trying to get to work), so I left it out of the statement. That caused the 6565 error. Once I put authid current_user or authid definer back in the statement, all was well. It appears that a SET ROLE only works in a procedure, IF one explicitly indicates the authid clause in the procedure (or package) creation. Without that, it's 6565. With it, all works fine. Go figure... Thanks a lot for all the help from all the replies, too many for me to thank individually. Now, to make this work with a login trigger... 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).
Re: Can someone please verify this for me?
Maybe you should try with DBMS_SESSION.SET_ROLE? On Thu, 2003-10-09 at 11:09, Nuno Souto wrote: 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 -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
RE: Can someone please verify this for me?
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).
RE: Can someone please verify this for me?
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).
Re: Can someone please verify this for me?
Nope. Tried with that one as well, same result. Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - Maybe you should try with DBMS_SESSION.SET_ROLE? -- 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).
Re: Can someone please verify this for me?
Sure... there you go! SQL create role new_role identified by password; Role created. SQL 1 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; SQL / Procedure created. SQL set role none; Role set. SQL select * from session_roles; no rows selected SQL exec turn_on_role; BEGIN turn_on_role; END; * ERROR at line 1: ORA-01979: missing or invalid password for role 'NEW_ROLE' ORA-06512: at SYSMAN.TURN_ON_ROLE, line 5 ORA-06512: at line 1 SQL ed Wrote file afiedt.buf 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 6 'set role new_role identified by password'; - 7* end; SQL / Procedure created. SQL exec turn_on_role; PL/SQL procedure successfully completed. SQL --- Nuno Souto [EMAIL PROTECTED] wrote: 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). __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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).
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 dv03drop role new_role;
Role dropped.
13:19:55 dv03
13:19:55 dv03create 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 dv03show errors procedure turn_on_role
No errors.
13:19:55 dv03
13:19:55 dv03create role new_role identified using jkstill.turn_on_role;
Role created.
13:19:55 dv03
13:19:55 dv03set role none;
Role set.
13:19:55 dv03
13:19:55 dv03select * from session_roles;
no rows selected
13:19:55 dv03
13:19:55 dv03set role new_role
13:19:55 2
13:19:55 dv03exec turn_on_role
PL/SQL procedure successfully completed.
13:19:55 dv03
13:19:55 dv03
13:19:55 dv03select * from session_roles;
ROLE
--
NEW_ROLE
1 row selected.
13:19:55 dv03
Jared
Nuno Souto [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/09/2003 08:09 AM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
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:
[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).
