Re: Stored Procedure Use vs. Role Use

Sorry for the late reply.

We too came across this situation. So, we created a new schema with direct grants to all tables. This schema was the owner of all our packages/procedure/functions. Then we granted excute permissions on the packages to the roles that we created. In this way, only people with the correct role can access the procedure.

The only problem with this is that if the role granted to the user does not allow him/her to delete records from a table, but they can do so via the package/procedure.

If u have come across a better way to implement the above, please let me know.

Regards,

VJ.

Larry Jones wrote:
>
> I have a big problem. I have a major bit of PL/SQL, I want to put into a
> stored procedure. It works fine, without being in a stored procedure
> (just executing from SQL*PLUS command line). But when I put it into a
> stored procedure for actual use, it bombs. It has to do with grants on
> some of the tables I am accessing.
>
> Evidently who ever executes the stored procedure, has to be granted
> access to the tables directly, not through roles. It's not the owner of
> the stored procedure, but the person executing it.
>
> This will totally blow away our role/security structure and is not going
> to happen here.
>
> Is there anyway around this, that anyone knows of? I can't believe
> something like this is causing such a problem. Roles are a great way to
> handle access, and for Oracle not to handle them properly, is a joke.
>
> BTW... I will be calling the procedure from PowerBuilder, to an Oracle
> 7.3.3 database.
>
> Thanks
>
> --
> **********************
> Larry Jones
> lljo_at_chevron.com
 

-- 
Vijay Damodaran
vijayd_at_nortel.com
Work: (972) 685-8150