Re: roles and stored procedure

>I'm making a few stored procedures with oracle to access a users table.
>to allow other users to access that table I have to give them rights to
>do that. My idea was to grant them those rights by creating a Role for
>it. That role contains the rights to access the table.
>I thought (how stupid of me) that a privilige granted through a role was
>the same as a direct granted privilige (for example the SELECT
>privilige). NOT!!
>After trying a few options I lost all hope and I called Oracle to give
>me an answer, it was right, a privilige granted by a role doesn't work
>inside a stored procedure.
>Ok for that, I have to give all users directly those rights (why use
>roles anyway)
>

I think you might have missed one point about stored procedures - the user who uses the procedure has the access of the CREATOR of the procedure.

If the owner of a table creates a package that provides access to a table owned by him, then any user provided EXECUTE privilege to that package has the same access as the owner, without any additional role granting to any of the tables involved. This privilege can be granted directly, or through ROLES. In the package, the owner of the package can use any table,column etc that he can access. A user of the package, when they use it, will have the same access to these objects as the creator of the package. The user of the package does not even need to know the really name of the objects involved.

Dan Hekimian-Williams Received on Fri Nov 27 1998 - 16:32:31 CET