Oracle and PL/SQL Recipes: A Problem-Solution Approach

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 ...