A Three Tier Schema: Adding the Code and User Tiers

This is the last in a series of three blogs on creating a three tier schema in Oracle. The previous two blogs introduced the concept of the three tier schema, and creating the data layer. This blog covers the final two concepts: creating the code layer and giving the user access to the code.

As previously stated, the code layer is the only schema that has access to the data and the user schema is the only schema allowed to access the code layer. The code schema is a schema that only contains a set of stored procedures, which have limited access to the data schema.

The package below contains two simple stored procedures for reading and writing events to our purchase events example; remember that these have been simplified for clarity.

The Package Specification

CREATE OR REPLACE PACKAGE ORDERS_CODE.purchase_events
AS

PROCEDURE update_purchase_events(p_purchase_code IN VARCHAR2,p_event_code IN NUMBER,p_event_date TIMESTAMP);

FUNCTION read_order_events(p_purchase_code IN VARCHAR2) RETURN SYS_REFCURSOR;

END purchase_events;

/
show errors;

The Package Body

Having defined the package specification, the package body follows:

CREATE OR REPLACE PACKAGE BODY ORDERS_CODE.purchase_events
IS
 
--
-- Simple proc that inserts an event into the event table.
-- Error handling removed for simplicity
--
PROCEDURE update_purchase_events(p_purchase_code IN VARCHAR2,p_event_code IN NUMBER,p_event_date TIMESTAMP)
IS

BEGIN

   INSERT INTO orders_purchase_events (pk,purchase_code,event_code,event_date) 
    VALUES(ORDERS_CODE.ORDERS_EVENT_SEQUENCE.nextVal,p_purchase_code,p_event_code,p_event_date);

END update_purchase_events;

--
-- Function that reads data from the PURCHASE events tables.
-- Returning all the events for a given purchase code.
-- Error handling removed for simplicity
--
FUNCTION read_order_events(p_purchase_code IN VARCHAR2)
   RETURN SYS_REFCURSOR
AS

   v_cursor SYS_REFCURSOR;

BEGIN

   OPEN v_cursor FOR SELECT
     e.purchase_code,c.event_type,e.event_date
     FROM orders_purchase_events e, orders_purchase_eventcodes c, 
       WHERE c.event_code = e.event_code AND
       e.purchase_code = p_purchase_code;
 
   RETURN v_cursor;

END read_order_events;

END purchase_events;
/

show errors

Example of the Three Tier Schema - Completing the User Tier

The last step in completing our three tier database is to allow the user schema to access our code.

GRANT EXECUTE ON ORDERS_CODE.purchase_events TO ORDERS_USER;

CREATE OR REPLACE PUBLIC SYNONYM purchase_events for orders_code.purchase_events;

/

The very last step is giving our application access to the user schema. This is usually by creating a data source in our web or application server, which is used by our application.

Some Organisation

One of the negative aspects of this arrangement is its complexity. One way to negate this is to employ a strict system of structuring your database files.
The system suggested below shares the data, code and user schemas between six separate files for clarity. These are:

1. <package name>.sql - which contains the data layer construction
2. <package name>.pks - which contains the package specification
3. <package name>.pkb - which contains the package code body
4. <package name>.grants - which contains the privileges granted to other users
5. <package name>.synonyms - which contains the appropriate PUBLIC synonym.
6. <package name>.order

The .order file is a list of the order in which the previous files should be executed. This will generally look like this (note that SQL is missing as you may not want to recreate your tables each time your update the database):

@@<package name>.pks
@@<package name>.pkb
@@<package name>.grants
@@<package name>.synonyms

And Finally...

In a well defined and correctly architected system, you can see how splitting your database into three separate schemas can aid system security: your webserver only knows about the user schema, the user schema is only allowed to access the stored procedures of the code schema, and finally, the code schema has limited access to the data that’s buried at the heart of your system.

Posts in this series:

1. Introduction to 3 Tier Schemas in Oracle
2. An Example 3 Tier Schema
3. A Three Tier Schema: Adding the Code and User Tiers