Re: Set Role From a Stored Procedure

Just my 2 cents.

We've had a number of problems using roles. This was especially the case when we were trying to create views dynamically.

In the end, we have had to grant the
rights directly, not through a role.
For the view question:

Grant select on table to user

Otherwise, we kept getting an error that we couldn't do what we wanted from a role. A lot of wasted time.

Rodger Lepinsky

Chad M. Hegerty wrote:  

 We did this within a package, which is very similar.  

 We define ReturnCode as an integer  

 ReturnCode := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(ReturnCode, '<grant statement goes here>', dbms_sql.v7);  DBMS_SQL.CLOSE_CURSOR(ReturnCode);  

 The only problem that we had was that we had to grant the owner of the  package explicit GRANT ANY ROLE rights. Even with the owner having the DBA
 role it didn't work.  

 Hope it helps.  

 --
 Chad Hegerty (chad.hegerty_at_rossnutrition.com)  Ross Products Division - Abbott Laboratories  

 Bob England <rengland_at_apci.net> wrote in article  <5osl23$npq_at_queeg.apci.net>...
> > Is anyone familiar with a way to issue a Set Role from within a stored
> > procedure? Thanks.
> >
> >
Received on Wed Jul 02 1997 - 00:00:00 CDT