Re: A role identified by a package - ORA-06565

I think I would assume that the version was 9, and the 'identified by' was a typing error for 'identified using'.

If this is the case, then the problem is that you can't set a role from within a procedure unless the authid is 'current_user'.

You'll have to change the package declaration to:

    create or replace package test_role_auth     authid current_user as

        procedure set_role;
    end test_role_auth;

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Howard J. Rogers wrote in message
<3c3e9363$0$32235$afc38c87_at_news.optusnet.com.au>...


>How many more times do people have to be told: WE NEED A VERSION!!

>

>OK.  Rant off.

>

>I'm going to assume you probably don't have Oracle 9i, because protecting a

>role using a procedure or package was not possible before then (at least,

>this ability is listed as a new feature in 9i...).

>

>The syntax for doing so is subtly different from what you have here, too:

>

>create role test_role identified USING test_pkg

>

>(and I don't actually know whether you can use a package... development's

>not my strong point, and I've only done it with a procedure, but I guess it

>can't be that different).

>

>All your example does is create a role protected by the password

"test_pkg",


>which is not what you were after at all!

>

>Regards

>HJR

>--

>----------------------------------------------

>Resources for Oracle: http://www.hjrdba.com

>===============================

>

>

>"Vsevolod Afanassiev" <vafanassiev_at_aapt.com.au> wrote in message

>news:4f7d504c.0201101559.55bffee2_at_posting.google.com...

>> I am trying to create a role identified by a package, the

>> creation part works, but when I try to use it, I get

>> ORA-06565: cannot execute SET ROLE from withing stored procedure

>>

>> SQL> create role test_role identified by test_pkg;

>>

>> Role created.

>>

>> SQL> create or replace package test_role_auth as

>>   2     procedure set_role;

>>   3  end test_role_auth;

>>   4  /

>>

>> Package created;

>>

>> SQL> create or replace package body test_roleauth as

>>   2  procedure set_role is

>>   3  begin

>>   4     dbms_session.set_role('TEST_ROLE');

>>   5  end set_role;

>>   6  end test_role_auth;

>>   7 /

>>

>> Package body created;

>>

>> SQL> connect /

>> Connected.

>> SQL> begin

>>   2     test_role_auth.set_role;

>>   3  end;

>>   4  /

>> begin

>> *

>> ERROR at line 1

>> ORA-06565: cannot execute SET ROLE from within stored procedure

>> ORA-06512: at "SYS.DBMS_SESSION", line 120

>> ORA-06512: at "OPS$ORACLE.TEST_ROLE_AUTH", line 4

>> ORA-06512: at line 2

>>

>> Any ideas?

>>

>> Thanks,

>> Sev

>

>