4-16. Forcing Data Access to Go Through Packages

Problem

You have defined all subprograms and packages for a particular application, and you want to allow other users to access these constructs and execute the program but not have access to any data tables directly.

Solution

Define all the packages, procedures, and functions for your program within a single schema that has access to all the data. All user access should be made from separate schemas, and they should be granted execute privileges on the PL/SQL objects but not access to the tables themselves.

For instance, if you want to control access to a package named PROCESS_EMPLOYEE_TIME, that package along with all required tables, types, and sequences should be loaded into an application schema that has the appropriate permissions required to access the data. For the purposes of this recipe, the application schema name is EMP.

Next, create a role by which to manage the privileges needed to invoke the package's procedures and functions. Grant EXECUTE privileges to that role. Grant that role to application users.

Your application users will now be able to execute the procedures and functions within the package. Those procedures and functions can in turn update the database tables in the package's schema. However, users will not have direct access to those tables. All updates must flow through the package.

How It Works

To control an application's data, it is important to restrict access to the tables. The solution in this recipe shows how to create a package in the same schema that contains the application tables. The package thus has access to those tables. Users, however, do not have table-level access.

After creating the package, you can grant EXECUTE access on the package to application users. Users can then invoke packaged procedures and functions, and those procedures and functions in turn can modify the data in the tables. However, users have no direct access to the tables.

By forcing users to go through packaged procedures and functions, you limit users to using a defined interface that remains under your control. You now have some amount of freedom to modify the underlying tables. So long as you do not change the package interface, you can make changes to the underlying tables without disrupting the application.