Re: SET ROLE in PL/SQL

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_at_pip.dknet.dk)
Oracle Consultant (not affiliated with Oracle)

On Fri, 12 Dec 1997 10:03:20 +1100, raymond allo <raymond_at_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
Received on Fri Dec 12 1997 - 00:00:00 CST