Re: Role Privilege Question/Peculiarity

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Tue, 31 Aug 1999 18:56:46 +0800
Message-ID: <37CBB4EE.2A8C@yahoo.com>

Martin Douglas wrote:
>
> Put your thinking hats on...
>
> My application requires the CREATE ANY PROCEDURE system privilege so
> that it can perform procedure creation via dynamic sql. The schema used
> is granted this privilege and works fine. However, our DBA got the
> brilliant idea (or not) to stick this privilege under a role and then
> grant the role to the above schema instead. Viola... the application no
> longer works and complains about invalid privileges in the dbms_sql and
> dbms_sys_sql packages.
>
> Any explanations as to why this would happen? There obviously is a
> reason for not passing on the privilege... I just want to know why oh
> why :)
>
> Thanks!

Roles are not enabled during procedure execution - privs must be directly granted...

(fictitious but) valid example:

SQL> grant select on blah to my_role;

SQL> grant my_role to mike;

SQL> connect mike/mike

SQL> select * from blah;

will be fine...

SQL> exec mikes_proc_that_reads_blah;

will fail...

HTH
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Tue Aug 31 1999 - 05:56:46 CDT