Many developers prefer to write APEX applications today, but there are developers who build standalone web applications. This article describes how you can create an Access Control List (ACL), and a PL/SQL authentication function.

An ACL lists your user names, passwords, and system privileges. Along with the ACL you need to create tables to track valid and invalid sessions. This article describes sample ACL tables and teaches you how to build an authentication function. It’s divided into two parts defining ACL tables and developing a PL/SQL authentication function.

Defining ACL Tables

There are four basic ACL tables. They store the user names and passwords, qualify user privileges, and store valid and invalid sessions. This paper implements the following tables:

  • The application_user table, which stores user names and passwords.
  • The system_session table, which stores valid sessions or records of user logins.
  • The invalid_session table, which stores invalid attempts to connect to the database by unauthorized users.

This article doesn’t cover how to manage user privileges because its focus is how you authenticate or repudiate access to a multiple user database. An application_privilege table would be a subordinate table to the application_user table.

You define the application_user table and application_user_s sequence with the following code:

SQL> CREATE TABLE application_user
  2 ( user_id NUMBER CONSTRAINT pk_application_user1 PRIMARY KEY
  3 , user_name         VARCHAR2(20)  CONSTRAINT nn_application_user1 NOT NULL
  4 , user_password     VARCHAR2(40)  CONSTRAINT nn_application_user2 NOT NULL
  5 , user_role         VARCHAR2(20)  CONSTRAINT nn_application_user3 NOT NULL
  6 , user_group_id     NUMBER        CONSTRAINT nn_application_user4 NOT NULL
  7 , user_type         NUMBER        CONSTRAINT nn_application_user5 NOT NULL
  8 , start_date        DATE          CONSTRAINT nn_application_user6 NOT NULL
  9 , end_date          DATE
 10 , first_name        VARCHAR2(20)  CONSTRAINT nn_application_user7 NOT NULL
 11 , middle_name       VARCHAR2(20)
 12 , last_name         VARCHAR2(20)  CONSTRAINT nn_application_user8 NOT NULL
 13 , created_by        NUMBER        CONSTRAINT nn_application_user9 NOT NULL
 14 , creation_date     DATE          CONSTRAINT nn_application_user10 NOT NULL
 15 , last_updated_by   NUMBER        CONSTRAINT nn_application_user11 NOT NULL
 16 , last_update_date  DATE          CONSTRAINT nn_application_user12 NOT NULL
 17 , CONSTRAINT un_application_user1 UNIQUE(user_name));

There are seven columns in the natural key for the application_user table. The natural key uses the user_name, user_password, user_role, user_group_id, user_type, start_date, and end_date columns. The user_id column on line 2 is the surrogate key.  The end_date column is always a null value for currently authorized users. It should be noted that there’s always a one-to-one match between surrogate and natural keys. The created_by, creation_date, last_update_date, and last_updated_by columns are often called the “who done it” or “who-audit” columns. That's because they document who creates the row and who last updates the row.

You need to create an application_user_s sequence for the application_user table:

SQL> CREATE SEQUENCE application_user_s;

At this point, lets insert some test records into the application_user table. The data will help us test the PL/SQL authentication function in the next section. The following inserts five rows in the application_user table:

SQL> INSERT INTO application_user VALUES
  2  ( application_user_s.nextval
  3  ,'potterhj','c0b137fe2d792459f26ff763cce44574a5b5ab03'
  4  ,'System Admin', 2, 1, SYSDATE, null, 'Harry', 'James', 'Potter'
  5  , 1, SYSDATE, 1, SYSDATE);

SQL> INSERT INTO application_user VALUES
  2  ( application_user_s.nextval
  3  ,'weasilyr','35675e68f4b5af7b995d9205ad0fc43842f16450'
  4  ,'Guest', 1, 1, SYSDATE, null, 'Ronald', null, 'Weasily'
  5  , 1, SYSDATE, 1, SYSDATE);

SQL> INSERT INTO application_user VALUES
  2  ( application_user_s.nextval
  3  ,'longbottomn','35675e68f4b5af7b995d9205ad0fc43842f16450'
  4  ,'Guest', 1, 1, SYSDATE, null, 'Neville', null, 'Longbottom'
  5  , 1, SYSDATE, 1, SYSDATE);

SQL> INSERT INTO application_user VALUES
  2  ( application_user_s.nextval
  3  ,'holmess','c0b137fe2d792459f26ff763cce44574a5b5ab03'
  4  ,'DBA', 3, 1, SYSDATE, null, 'Sherlock', null, 'Holmes'
  5  , 1, SYSDATE, 1, SYSDATE);

SQL> INSERT INTO application_user VALUES
  2  ( application_user_s.nextval
  3  ,'watsonj','c0b137fe2d792459f26ff763cce44574a5b5ab03'
  4  ,'DBA', 3, 1, SYSDATE, null, 'John', 'H', 'Watson'
  5  , 1, SYSDATE, 1, SYSDATE);

The INSERT statements use encrypted values for the user_password value but you can encrypt them with the encrypt function discussed in this other article of mine. It did not seem necessary here to show encryption.

The system_session table holds the valid session information. You define the system_session table with the following code:

SQL> CREATE TABLE system_session
  2  ( session_id        NUMBER        CONSTRAINT pk_ss1 PRIMARY KEY
  3  , session_number    VARCHAR2(30)  CONSTRAINT nn_ss1 NOT NULL
  4  , remote_address    VARCHAR2(15)  CONSTRAINT nn_ss2 NOT NULL
  5  , user_id           NUMBER        CONSTRAINT nn_ss3 NOT NULL
  6  , created_by        NUMBER        CONSTRAINT nn_ss4 NOT NULL
  7  , creation_date     DATE          CONSTRAINT nn_ss5 NOT NULL
  8  , last_updated_by   NUMBER        CONSTRAINT nn_ss6 NOT NULL
  9  , last_update_date  DATE          CONSTRAINT nn_ss7 NOT NULL);

You create the system_session_s sequence for the system_session table, like this:

SQL> CREATE SEQUENCE system_session_s;

There are three columns in the natural key of the system_session table. The natural key uses the session_number, remote_address, and user_id columns. The external programming language, like Perl, PHP, or Ruby, establish the session number. Like the prior table, the session_id column is a surrogate key and the primary key for the table. Like, the earlier table, the other columns are the who-audit columns. A fully qualified session table would include additional columns. For example, you would define columns to store all of the HTML headers.

The following defines the invalid_session table:

SQL> CREATE TABLE invalid_session
  2  ( session_id        NUMBER        CONSTRAINT pk_invalid_session1 PRIMARY KEY
  3  , session_number    VARCHAR2(30)  CONSTRAINT nn_invalid_session1 NOT NULL
  4  , remote_address    VARCHAR2(15)  CONSTRAINT nn_invalid_session2 NOT NULL
  5  , created_by        NUMBER        CONSTRAINT nn_invalid_session3 NOT NULL
  6  , creation_date     DATE          CONSTRAINT nn_invalid_session4 NOT NULL
  7  , last_updated_by   NUMBER        CONSTRAINT nn_invalid_session5 NOT NULL
  8  , last_update_date  DATE          CONSTRAINT nn_invalid_session6 NOT NULL);

You create the invalid_session_s sequence for the invalid_session table, like this:

SQL> CREATE SEQUENCE invalid_session_s;

The invalid_session table mirrors all but one of the columns from the system_session table. The missing column in the invalid_session table is the user_id column. That’s because you only write to the invalid_session table when the user isn’t valid.

The natural key of the invalid_session table has only two columns. They are the session_number and remote_address columns. The session_id is the surrogate key for the invalid_session table.

After defining the tables, you can now develop the PL/SQL authentication function in the next section.

Developing a PL/SQL Authentication Function

This section shows you how to write a PL/SQL authentication function. The function will take four key arguments. The arguments are the user name, password, session number, and remote address. The function also returns three values, and they are the user name, password, and session number.

PL/SQL functions can return one thing, like most programming languages. The one thing a PL/SQL function can return may be a scalar variable or a composite data type. A composite data type can be a data structure or a collection type. A data structure is a set of elements organized by their data type in position order. They’re defined as object types in SQL and record types in PL/SQL. A collection type holds a set of a scalar data type or a set of a data structure.

The PL/SQL authentication function requires you to define an object type before you can implement an authentication function. The next two subsections show you how to create the object type and function. The third subsection shows you how to perform a unit test.

Define a PL/SQL Object Type

A PL/SQL object type defines a data structure with an implicit constructor. However, PL/SQL supports positional and named notation. It also supports a default constructor, which can be three arguments in positional order or by named order.

The definition of the authentication_t type is:

SQL> CREATE OR REPLACE
  2    TYPE authentication_t IS OBJECT
  3    ( username VARCHAR2(20)
  4    , password VARCHAR2(40)
  5    , sessionid VARCHAR2(30));
  6  /

While the scope of the PL/SQL authentication function is capable of running in SQL or PL/SQL scope, PL/SQL requires a collection of the object type when you want to call it from a query in SQL scope.

The following defines a collection of the authentication_t type:

SQL> CREATE OR REPLACE
  2    TYPE authentication_tab IS TABLE OF authentication_t;
  3  /

After creating the function in the schema, you can create the PL/SQL authentication function.

Define a PL/SQL Authentication Function

The PL/SQL authentication function lets you check whether the user name and password match before creating a connection to the database. After verifying a user’s credentials, the user name and password, the function writes a record to the system_session table. A failure to update the user’s credentials writes to the invalid_session table. The function returns an empty authentication_t instance when the credentials don’t validate, and a populated instance when they do validate.

The function also lets a user preserve a connection when the user posts a reply within five minutes. It does that by updating the last_update_date column of the system_session table.

The following provides the authorize function code:

SQL> CREATE OR REPLACE FUNCTION authorize
  2  ( pv_username VARCHAR2
  3  , pv_password VARCHAR2
  4  , pv_session VARCHAR2
  5  , pv_raddress VARCHAR2 ) RETURN authentication_t IS
  6
  7    /* Declare session variable. */
  8    lv_session VARCHAR2(30);
  9
 10    /* Declare authentication_t instance. */
 11    lv_authentication_t AUTHENTICATION_T := authentication_t(null,null,null);
 12
 13    /* Define an authentication cursor. */
 14    CURSOR authenticate
 15    ( cv_username VARCHAR2
 16    , cv_password VARCHAR2 ) IS
 17      SELECT   user_id
 18      ,        user_group_id
 19      FROM     application_user
 20      WHERE    user_name = cv_username
 21      AND      user_password = cv_password
 22      AND      SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
 23
 24    /* Declare a cursor for existing sessions. */
 25    CURSOR valid_session
 26    ( cv_session VARCHAR2
 27    , cv_raddress VARCHAR2 ) IS
 28      SELECT   ss.session_id
 29      ,        ss.session_number
 30      FROM     system_session ss
 31      WHERE    ss.session_number = cv_session
 32      AND      ss.remote_address = cv_raddress
 33      AND     (SYSDATE - ss.last_update_date) <= .003472222;
 34
 35    /* Create an autonomous transaction. */
 36    PRAGMA AUTONOMOUS_TRANSACTION;
 37  BEGIN
 38
 39    /* Write authentication records. */
 40    FOR i IN authenticate(pv_username, pv_password) LOOP
 41      /* Assign existing session ID values. */
 42      FOR j IN valid_session(pv_session, pv_raddress) LOOP
 43        lv_session := j.session_number;
 44      END LOOP;
 45
 46      /* Write new session or update existing session. */
 47      IF NOT lv_session IS NULL THEN
 48        /* Update existing session in system_session table. */
 49        UPDATE   system_session
 50        SET      last_update_date = SYSDATE
 51        WHERE    session_number = pv_session
 52        AND      remote_address = pv_raddress;
 53      ELSE
 54        /* Insert new session into invalid_session table. */
 55        INSERT INTO system_session
 56        VALUES
 57        ( system_session_s.nextval
 58        , pv_session
 59        , pv_raddress
 60        , i.user_id
 61        , i.user_id
 62        , SYSDATE
 63        , i.user_id
 64        , SYSDATE );
 65
 66        /* Assign session ID number. */
 67        lv_session := pv_session;
 68      END IF;
 69    END LOOP;
 70    /* Allocate space, assign instance, and return session ID value. */
 71    lv_authentication_t := authentication_t( username => pv_username
 72                                           , password => pv_password
 73                                           , sessionid => pv_session );
 74    /* Commit the records. */
 75    COMMIT;
 76    /* Return a record structure. */
 77    RETURN lv_authentication_t;
 78  END;
 79  /

Lines 2 through 5 qualify the four formal parameters, which are the user name, password, session ID, and remote address. Line 11 declares and instantiates an empty instance of the authentication_t type. The function returns this empty instance on line 101 when the user credentials aren’t validated.

Line 36 declares a precompiler instruction in the declaration block, which makes the authorize function an autonomous function. Autonomous functions may include DML statements. You can also call an autonomous function from a query because the transaction scope is independent of the query. However, you must put a COMMIT statement before the end of the execution block, which is on line 75.

Line 47 validates when the for-loop on lines 42 through 44 found an existing session.  After opening the cursor successfully on line 40, lines 42 through 44 validates whether an existing session exists. Line 53 is the ELSE block that inserts a value into the system_session table

Lines 71 through 73 populates the local lv_authentication_t instance. Line 75 commits the insert or update, and line 77 returns either the empty instance or a populated instance of the lv_authentication_t strucutre.

Unit Test a PL/SQL Authentication Function

There are two unit test cases in this section. One tests a call to the authorize function in a PL/SQL context and the other to the authorize function in a SQL context.

The following calls the authorize function in a PL/SQL context:

SQL> DECLARE
  2    /* Declare authentication_t instance. */
  3    lv_authentication_t AUTHENTICATION_T;
  4  BEGIN
  5    /* Create instance of authentication_t type. */
  6    lv_authentication_t := authorize('potterhj'
  7                                    ,'c0b137fe2d792459f26ff763cce44574a5b5ab03'
  8                                    ,'session_test'
  9                                    ,'127.0.0.1');
 10    /* Print object instance. */
 11    dbms_output.put_line('Username [' || lv_authentication_t.username || ']');
 12    dbms_output.put_line('Password [' || lv_authentication_t.password || ']');
 13    dbms_output.put_line('SessionID [' || lv_authentication_t.sessionid || ']');
 14  END;
 15  /

It prints:

Username  [potterhj]
Password  [c0b137fe2d792459f26ff763cce44574a5b5ab03]
SessionID [session_test]

The following calls the authorize function in a SQL context:

SQL> SELECT *
  2  FROM TABLE(
  3         SELECT CAST(
  4                  COLLECT(
  5                    authorize(
  6                        pv_username => 'potterhj'
  7                      , pv_password => 'c0b137fe2d792459f26ff763cce44574a5b5ab03'
  8                      , pv_session => 'session_test'
  9                      , pv_raddress => '127.0.0.1')) AS authentication_tab)
 10         FROM dual);

It prints:

USERNAME   PASSWORD             SESSIONID
---------- -------------------- ------------
potterhj c0b137fe2d792459 ...   session_test

The choice of which constructor you use is important. You should avoid positional notation where possible, and that’s especially true when the elements of the object type share the same scalar data type. Lines 6 through 9 in the anonymous PL/SQL block and lines 5 through 9 in the query show you how to use named notation.

This article has shown you how to create and test a PL/SQL authentication function for your standalone web applications. If you want to learn how to configure a secured or unsecured DAD in the XDB Server, please check this other post of mine. You can find the setup code for the authentication function with test cases at this URL.