Packages, Roles, Table Access and Security Question

Hello all.

I am developing a HTML app that uses Designer/2000 generated PL/SQL packages in conjunction with Oracle's Webserver. The problem is that seeing as the system is all package driven, I am wondering how I should implement table security.

Here is the situation.

        Owner A owns all of the tables and views.

        Owner B owns the packages for the application

        Owner A has to grant select, insert, update and delete to owner B on all tables as the packages

        need to be able to access the objects in that manner because the packages are responsible for

        inserting, updating, deleting and selecting data.

The problem.

        If another owner C is given privilege to execute the package owned by owner B, then owner C

        has full control of the table, when that person should only be able to work with it in a restricted

	fashion.  This is because when you run a package, you get the privileges
of the owner of the 	package.

The Solution?

        I have one solution, that is to create security functions, one for each function in the package,

        and then grant owner C privileges to the security functions. This would limit which functions

        the owner could execute inside the package, and thus limit access to the table.

        Is there a better solution out there??? I would like to be able to create roles, and grant access

        to the tables directly. But then the user still has privilege if granted a package directly???

        Is there a grant I can give to owner B so that other users use direct table privileges instead

        of the package owner's privileges??

Any help would be appreciated. Received on Thu Sep 04 1997 - 00:00:00 CDT