Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)

Figure 10-6 illustrates a subsystem, also called an application, that has visible and private package components. The contrast between the white and shaded part denotes a package specification part as distinct from a package body. The white, "exposed" slots represent components of an API. The API defines the interface and, in general, specifies what the package does. The body is the implementation. To see the implementation (i.e., how the code performs its task), one must look at the package code.

The design of a system begins with partitioning functionality into subcomponents, or subsystems. The process continues with partitioning the subsystem into packages. A single package within a subsystem satisfies a set of related requirements. A subsystem will likely include Oracle built-in packages (e.g., UTL_FILE for File IO). A package can logically reside in multiple subsystems, also called applications.

We can implement the concept of a subsystem using the existing database framework, specifically the database schema. To make a subsystem, we can encapsulate packages as objects within a schema and use database grants to make some packages visible and some not. The packages, for which we gave grants, become the API of the subsystem. A programmer might develop a useful and general purpose dates package that would exist in numerous subsystems.

Figure 10-7 illustrates a subsystem for a Student Registration Application. In contains a set of packages that support all the functionality for student registration. One package is exposed. This is the API. It provides ADD and GET operations on the private data stored in the tables of the schema and supports other application code that requires these services. For example, a student housing application might require the GET service component of the API.

Figure 10-7 is drawn to illustrate how applications within a single database can be partitioned and how the logic within partitions can interconnect through an API. This diagram shows a single database. Application can easily be distributed among several databases and achieve the same level of interconnectivity using database links.

The Student Registration Application (see Figure 10-7) shows three packages that are not part of the visible API. These are not callable from other applications. The application, as drawn literally, consists of four packages and four tables. One package is visible.

We grant execute privileges to users who need the API. Once the application becomes production, we can further lock-down the schema and revoke CREATE TABLE and similar roles from the registration production schema. From a database perspective, this includes the following:

• Create a database account for the student registration schema. We call this Student Registration Application (SRA).

• Build the application. Create all tables and packages in the SRA schema.

• GRANT EXECUTE ON API packages to other users such as the Student Housing Application owner.

• Revoke the Oracle CONNECT and RESOURCE roles from the SRA. This prevents accidental updates to production by development and test teams that work on separate development and test database instances.

• There is now a restricted one-way access to the SRA system. This is the package or set of packages that form the API.

• The application will undergo enhancements and new releases. This will require enabling the application roles, CONNECT and RESOURCE, and other roles to permit database table and PL/SQL enhancements.