PL/SQL and Roles (was Re: PL/SQL problem.... (Rebuild Indexes))

On Wed, 19 Aug 1998 18:52:29 +0200, Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote:

>Regrettably, Madhu is absolutely right
>PL/SQL ignores roles. All grants to dba_views are either to a role, or to
>PUBLIC, which is again indirect.
>This will probably get resolved in the next version, though the feature already
>exists quite some time.
>NB: Sql*plus doesn't suffer from thies problem
>

<signature and previous messages snipped>

OK, no-one else has taken this up for months, so I will. If I believed in HTML postings, the next line would be in VERY large letters:

The fact that stored PL/SQL objects ignore privileges obtained through a role is NOT a bug and should never be "fixed"!

The reasons for this are as follows:

A role is a set of privileges which a user has because the role is active at the present time in the users present session.

Roles may be activated and de-activated at will throughout the life of a session.

When a stored PL/SQL object is executed, all privileges are checked as if the owner was executing it, BUT the execution is not taking place in the owner's session, so no roles can be active, nor is it possible to work out which roles might be active.

Consequently the only privileges which will work are those the owner has been given directly.

There is no reason why the owner should not be given the necessary privileges, it does not protect anything to try to use a role in this situation. A role in this situation is not even an administrative convenience, since no-one else will need exactly that set of privileges.

If you don't agree, please tell us exactly why you need it to be different,

Regards,

Eric Received on Fri Sep 04 1998 - 16:22:30 CDT