Re: Granting roles from stored procedure

Heather van der Zweep wrote:
>
> Anyone know if it's possible to execute a grant role command from within a
> stored procedure? I have tried using dbms_sql.parse to issue the command
> but am getting an invalid privilege error. I am able to grant the role
> from the sql prompt.
>
> Can I assume that grant/revoke is illegal from within a stored procedure?
>
> If you have a solution, please e-mail me at hvanderz_at_ch2m.com as well as
> posting to this newsgroup. Our news server has been acting up.
>
> Thanks in advance.
>
> Heather van der Zweep
> CH2M HILL Inc

Heather:

My first guess is that you have the privilege to "grant" via a ROLE instead of having is granted directly to your account. Have the privilege granted directly to you ie

grant select on v_$session to heather;

and see if that helps. When procedures are compiling and running they only look at privileges that the user has been granted directly.

Hope this helps,

John Hough Received on Fri Feb 28 1997 - 00:00:00 CST