Re: Urgent: SET ROLE in a stored procedure

From: <chris_at_sky_at_my-dejanews.com>
Date: Fri, 30 Oct 1998 10:45:52 GMT
Message-ID: <71c5cu$271$1@nnrp1.dejanews.com>

Unfortunately this is what I've also found - I had wanted to add some extra security by having a stored procedure which could do other validation before a role was actually set.

This is a real pain as the (explicitly) stated purpose of the dbms_session package is to allow us to do certain actions from within stored procedures:

CREATE OR REPLACE
package dbms_session is

OVERVIEW --
This package provides access to SQL "alter session" statements, and
other session information from, stored procedures.

PROCEDURES AND FUNCTIONS -- procedure set_role(role_cmd varchar2);
Equivalent to SQL "SET ROLE ...".
Input arguments:
role_cmd
This text is appended to "set role " and then executed as SQL.

In article <36372791.3745355_at_192.86.155.100>, tkyte_at_us.oracle.com wrote:
> A copy of this was sent to Dmitri Blinov <Dmitri_Blinov_at_dialogbank.com>
> (if that email address didn't require changing)
> On Wed, 28 Oct 1998 13:19:33 +0300, you wrote:

>


> >> Unfortunately, you can't. Oracle will tell you :


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


> >


> >And what about DBMS_SESSION.SET_ROLE procedure ?


> >


> >PLSQL has syntax limitation and for that reason there are exist


> >several packages, which allows you to use the same functionality by


> >calling their functions. One of them is DBMS_SESSION.

> >
> >Dmitry.

> The dbms_session.set_role procedure can be used in an anonymous block (not a
> stored, compiled object but rather an anonymous, execute one time, pl/sql
> block)..

>
<snip>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Oct 30 1998 - 04:45:52 CST