Re: Two simple questions...

From: Gama Franco <tiago_at_cern.ch>
Date: Thu, 07 Oct 2004 23:02:03 +0100
Message-ID: <2slsmsF1mimpoU1@uni-berlin.de>

Hi Rauf,

Perhaps I didn't explain myself.

The problem is that the stored procedure I am talking about creates a database link. Oracle executes procedures with the privileges conceded directly to the user (with role equals to none). So I can't execute the procedure without giving the user a create database link privilege explicitly (being in a role that has that privilege doesn't count).

What I would like to know is if there is a way to force oracle to use the privileges of the roles that the user is in, instead of the privileges conceded directly to him.

Best regards,

   Gama Franco

Rauf Sarwar wrote:
> Comments embedded.
>
>

>>1 - Is it possible to run a stored procedure using the privileges 
>>granted to a user through a role? I mean, is there any way to do it?

>
>
> Yes.
> SQL> grant execute on <procedure> to <role>;
> SQL> grant <role> to <user>;
> SQL> connect <user>/<password>
> SQL> execute <owner>.<procedure>;
>
>

>>2 - How do I inspect the roles of a user using SQL PLUS?

>
>
> look up description of dba_users and dba_role_privs.
>
> Regards
> /Rauf
Received on Thu Oct 07 2004 - 17:02:03 CDT