As discussed in other articles, there are many developers who use Oracle APEX to develop robust web applications. They choose Oracle APEX because it’s simply a great tool. While APEX gets better with each new release, there are developers who want more control. They would like to know how to build native PL/SQL web applications.

Oracle APEX uses Oracle XML Database (XDB) Server as its engine. The XDB Server is a multiprotocol web server. It lets you configure settings with the dbms_xdb and dbms_epg PL/SQL Built-in packages. You also have the ability to configure the XDB Server to concurrently run standalone native PL/SQL web applications.

 This article builds on my earlier Configure Oracle’s XDB Server for Web Applications by demonstrating how you can encrypt a user’s password and verify a user’s password with the dbms_crypto built-in package. You learn how to create your own password function in this article and how to create a verification function.

It would be convenient to call the encryption function the password function, but you can’t call it password. That’s because SQL*Plus already uses password to reset a user’s password. This article calls the password function the encrypt function. Fortunately, there’s no verification function in SQL*Plus. The verify function shows you how to verify an encrypted password.

This article has three parts. They are:

  • Configuring dbms_crypto built-in package
  • Writing a custom encrypt function
  • Writing a custom verify function

You should read through how to setup dbms_crypto before you try developing the custom encrypt and verify functions.  The encrypt and verify functions should not be stored in plain text. That means you need to wrap them or deploy them in Java. A Java solution isn’t available in the Oracle Database 11g Express Edition, so this article presents a clear text and wrapped solution in PL/SQL

Configuring DBMS_CRYPTO built-in function

The internal Oracle schema should own the dbms_crypto built-in package. You connect as the system user to check ownership with the following query:

SQL> COL package FORMAT A25
SQL> SELECT DISTINCT owner || '.' || name AS package
  2  FROM   dba_source
  3  WHERE  name = 'DBMS_CRYPTO';

The query should return the following result:

PACKAGE
-------------------------
SYS.DBMS_CRYPTO

If the query doesn’t return the preceding value, you need to install the dbms_crypto built-in package. You can run the following two scripts from the $ORACLE_HOME/rdbms/admin directory by using the ?. The ? at the SQL*Plus prompt substitutes for the $ORACLE_HOME environment variable.

SQL> @?/rdbms/admin/dbmsobtk.sql
SQL> @?/rdbms/admin/prvtobtk.sql

After you verify the installation of the dbms_crypto built-in package, you need to make sure your schema has access to the package. You can grant execute privilege with this syntax:

SQL> GRANT EXECUTE ON dbms_crypto to student;

You should now be able to call the dbms_crypto built-in package from the student schema. The next section shows you how to create and wrap the encrypt function.

Writing a custom encrypt function

A custom password function should take a plain text variable length string, and the function should return an encrypted variable length string. The following encrypt password function does exactly that:

SQL> CREATE OR REPLACE
  2    FUNCTION encrypt( password VARCHAR2 ) RETURN RAW IS
  3      /* Declare local variables for encryption. */
  4      lv_key_string VARCHAR2(40) := 'EncryptKey';
  5      lv_key RAW(64);
  6      lv_raw RAW(64);
  7      lv_encrypted_data RAW(64);
  8    BEGIN
  9      /* Dynamic assignment. */
 10      IF password IS NOT NULL THEN
 11        /* Cast the password to a raw type. */
 12        lv_raw := utl_raw.cast_to_raw(password);
 13
 14        /* Convert to a RAW 64-character key. */
 15        lv_key := utl_raw.cast_to_raw(lv_key_string);
 16        lv_key := RPAD(lv_key,64,'0');
 17
 18        /* Encrypt the salary before assigning it to the object type attribute */
 19        lv_encrypted_data := dbms_crypto.encrypt( lv_raw
 20                                                , dbms_crypto.encrypt_aes256
 21                                                  + dbms_crypto.chain_cbc
 22                                                  + dbms_crypto.pad_pkcs5
 23                                                , lv_key);
 24    ELSE
 25      /* Raise an application error. */
 26      RAISE_APPLICATION_ERROR(-20001,'An empty string does not encrypt.');
 27    END IF;
 28
 29    /* Return a value from the function. */
 30    RETURN lv_encrypted_data;
 31  END encrypt;
 32  /

Line 15 uses the utl_raw package to cast the string into a RAW data type. Line 16 pads the key to a length of 64 digits. The padding is required for the key value when you submit it as the fifth call parameter to the dbms_crypto.encrypt function. Line 19 through 23 demonstrates a call to the encrypt function of the dbms_crypto package. The encrypt function returns a RAW data type, which you can later store in a VARCHAR2 column.

Line 4 discloses the seed value, which shouldn’t happen. You should actually deploy a function like this with the dbms_ddl package’s create_wrapped procedure. It ensures that the seed value isn’t visible inside the data dictionary by querying the dba_source view.

It is possible to decrypt the encrypted values, but you don’t really need to do that. You should avoid providing a decrypting function unless you want to audit user passwords.

You can test the basic password function with a simple query like this:

SQL> COL encrypted_data FORMAT A64
SQL> COL encrypted_size FORMAT 999
SQL> SELECT encrypt('Kitty@Spencer!1234') AS encrypted_data
  2  ,      length(encrypt('Kitty@Spencer!1234')) AS encrypted_size
  3  FROM   dual;

It should display an unreadable string:

ENCRYPTED_DATA            ENCRYPTED_SIZE
------------------------- --------------
FEA2D3A25B ... CD3E115619             64

This test verifies that the encrypt function works.

Writing a custom verify function

The verify function takes the user’s name and an unencrypted password. The verify function returns a 1 if the unencrypted password encrypts to the same value found in the application’s Access Control List (ACL) table. The verify function returns a 0 when the unencrypted password fails to encrypt to a matching value in the ACL table.

You need to create a ACL table before you write the verify function. You can create a simple UAC app_user table with the following syntax:

SQL> CREATE TABLE app_user
  2  ( app_user_id    NUMBER CONSTRAINT pk_app_user PRIMARY KEY
  3  , app_user_name  VARCHAR2(30)
  4  , app_password   VARCHAR2(64));

Then, you create the app_user_s sequence for the app_user_id surrogate key column with the following sequence:

SQL> CREATE SEQUENCE app_user_s START WITH 1001;

After creating the app_user table, you insert a row for a user. The row has a plain text user name and an encrypted password. You use the following INSERT statement to add the row:

SQL> INSERT INTO app_user
  2  VALUES
  3  ( app_user_s.NEXTVAL
  4  ,'Johann Schmidt'
  5  , encrypt('Kitty@Spencer!1234'));

Line 5 calls the encrypt function with an unencrypted password, and stores the result of the verify function, which is the encrypted password. With an ACL table, you can create the verify function with the following code:

SQL> CREATE OR REPLACE
  2    FUNCTION verify
  3    ( user_name VARCHAR2
  4    , password VARCHAR2 ) RETURN NUMBER IS
  5
  6      /* Default return value. */
  7      lv_result NUMBER := 0;
  8
  9      /* Application user cursor. */
 10      CURSOR c (cv_user_name VARCHAR2) IS
 11        SELECT app_password
 12        FROM   app_user
 13        WHERE  app_user_name = cv_user_name;
 14    BEGIN
 15      /* Compare encrypted password. */
 16      FOR i IN c(user_name) LOOP
 17        IF encrypt(password) = i.app_password THEN
 18          lv_result := 1;
 19        END IF;
 20      END LOOP;
 21
 22      /* Return the value. */
 23      RETURN lv_result;
 24  END;
 25  /

The verify function sets a default return value of 0 for false. It uses a parameterized cursor on lines 10 through 13 to find the encrypted password. The for loop on lines 16 through 20 open the cursor, and on line 17 the if statement compares the encrypted password against the stored encrypted password in the app_user table.

You can test the verify function with the following anonymous block:

SQL> DECLARE
  2    /* Declare print variable. */
  3    lv_output VARCHAR2(64);
  4  BEGIN
  5    /* Test function returns:
  6    || ======================
  7    || - True returns 1
  8    || - False returns 0
  9    */
 10    IF verify('Johann Schmidt','Kitty@Spencer!1234') = 1 THEN
 11      dbms_output.put_line('Result [It worked!]');
 12    ELSE
 13      dbms_output.put_line('Result [It failed!]');
 14    END IF;
 15  END;
 16  /

Line 10 calls the verify function with the user's name and unencrypted password. It prints

Result [It worked!]

This article has shown you how to write, deploy, and test native PL/SQL encryption and verification functions. You can deploy these to support a PL/SQL web applications when using an unsecured DAD, as qualified in my prior Configure Oracle’s XDB Server for Web Applications.

You can find the complete test code for this article at this URL.