From: petlars@pip.dknet.dk (Peter H. Larsen)
Subject: Re: SET ROLE in PL/SQL
Date: 1997/12/12
Message-ID: <34919d03.25676941@news2.dknet.dk>#1/1
References: <348E1587.76DE803@ican.ca> <34907138.713C@ramavista.com>
Newsgroups: comp.databases.oracle.server



When are people gonna learn that ROLES (dynamic authentication) does
NOT apply to stored procedures or PL/SQL ?

Authentication in PL/SQL has to be static - has to be granted
directly. It's not possible to disable/enable.

The reason is rather complex to explain, but the essens is that when a
stored procedure is created, the procedure freezes the rights that the
owner had at the creation point. Anyone who gets EXECUTE right to that
procedure enherrits the authentication. That means if a procedure
like:

CREATE OR REPLACE PROCEDURE DEL_ALL_DATA IS
BEGIN
  DELETE FROM SECRET_TABLE;
END;

is created under the schema SECRETS and granted to user DUMMY who has
no access to the table SECRET_TABLE what so ever, the DUMMY user will
STILL be able to delete the table.

Now ROLES works like this: When you log on the system looks in your
profile and enables the roles given in there. By default ALL granted
roles are enabled. But this can easaly be changed.  

Regards
Peter H. Larsen  (petlars@pip.dknet.dk)
Oracle Consultant (not affiliated with Oracle)

On Fri, 12 Dec 1997 10:03:20 +1100, raymond allo
<raymond@ramavista.com> wrote:

>Jerry Apfelbaum wrote:
>> 
>> We have not had any success with trying to execute the SQL command:
>>           SET ROLE ThisRole IDENTIFIED BY ThisPassword;
>> using dynamic SQL in either an anonymous PL/SQL block or in a stored
>> procedure.
>> 
>> Can this be done?  If not, is there an explanation as to why this is not
>> permitted?
>I had the same problem and contacted Oracle about it. It doesn't work
>unfortunately. i have put in a change/enhancement request though. But
>for the mean time it is a no brainer.
>
>Raymond Allo
>Ramavista Pty Ltd
>Sydney, Australia