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

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