Re: User Password change privileges?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 31 Oct 2000 22:15:05 +0100
Message-ID: <01duvsseaocm27fs9u6f7j9s33ehl3l5jm@4ax.com>

On Tue, 31 Oct 2000 16:43:48 GMT, tariks_at_my-deja.com wrote:

>Sybrand
>
>I'm not sure I fully understand your explanation, but yes thanks that's
>working now!
>
>Tarik
>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

Just to remove any doubts, as this question is asked so often The problem here is privileges are checked at compilation time, not at execution time.

an anonymous pl/sql block (wrapped in begin end, *not* stored in the *database*) is compiled upon execution as it executes immediately. Roles are being used.
A stored procedure is stored in the database, to be executed later. Privileges are checked (as above) at *compilation* time, as opposed to *execution* time.
There is no guarantee your roles didn't change in the mean time. That's why Oracle ignores roles upon compiling a stored procedure.

In 8i this has been resolved by allowing a procedure to be run with definers rights (the default behavior) or invokers rights (new in Oracle 8i). This way the procedure will be checked at runtime against the privileges of the calling user.

Hth,

Sybrand Bakker, Oracle DBA Received on Tue Oct 31 2000 - 15:15:05 CST