Re: Permissions with Packages

>I have a package PACKAGE_ONE compiled by USER_1 with their own
>permissions (synonyms, etc).
>
>I have multiple other users which wish to use PACKAGE_ONE but they all
>have different permissions than USER_1 (i.e. different GRANTS).
>
>My problem is that, when the other users execute functions within the
>PACKAGE_ONE package, the functions have the same permissions as USER_1
>rather than the actual user (this is true).
>

That is one of the features of packages - the ability to control the interface to a table (through a package) rather than just controling the table.

But to answer your question, keep your package the way it is; and do not grant execute to it to the other users. Create separate FUNCTIONS (or PROCEDURES) that just call the package functions or procedures. Grant access to these secondary functions/procedures based on the users. You may need to add additional code to you package so they know what to do.

Also, I you can use the USER keyword and queries against SESSION_ROLE(S?) to see what roles the user has, and have your package act appropriately.

Dan Hekimian-Williams Received on Mon Nov 09 1998 - 21:15:44 CST