Tutorial: Creating and Using a Database Session-Based Application Context

This tutorial demonstrates how to create an application context that checks the ID of users who try to log in to the database.

Step 1: Create User Accounts and Ensure the User SCOTT Is Active

To begin this tutorial, you must create the necessary database accounts and endure that the SCOTT user account is active.

  1. Log in to a PDB as user SYS and connect using the SYSDBA administrative privilege.

    sqlplus sys@pdb_name as sysdba
Enter password: password

    To find the available PDBs in a CDB, log in to the CDB root container and then query the PDB_NAME column of the DBA_PDBS data dictionary view. To check the current container, run the show con_name command.

  2. Create the local user account sysadmin_ctx, who will administer the database session-based application context.

    CREATE USER sysadmin_ctx IDENTIFIED BY password;
GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO sysadmin_ctx;
GRANT READ ON HR.EMPLOYEES TO sysadmin_ctx;
GRANT EXECUTE ON DBMS_SESSION TO sysadmin_ctx;

    Replace password with a password that is secure.

  3. Create the following user account for Lisa Ozer, who is listed as having lozer for their email account in the HR.EMPLOYEES table.

    GRANT CREATE SESSION TO LOZER IDENTIFIED BY password;

    Replace password with a password that is secure.

  4. The sample user SCOTT will also be used in this tutorial, so query the DBA_USERS data dictionary view to ensure that the account status for SCOTT is OPEN.

    SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'SCOTT';

If the DBA_USERS view lists user SCOTT as locked and expired, then enter the following statement to unlock the SCOTT account and create a new password for him:

ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY password;

Enter a password that is secure. For greater security, do not give the SCOTT account the same password from previous releases of Oracle Database.

Related Topics

Step 2: Create the Database Session-Based Application Context

As the sysadmin_ctx user, you are ready to create the database session-based application context.

  1. Connect to the PDB as sysadmin_ctx.

    CONNECT sysadmin_ctx@pdb_name
Enter password: password

  2. Create the application context using the following statement:

    CREATE CONTEXT empno_ctx USING set_empno_ctx_pkg;

Remember that even though user sysadmin_ctx has created this application context, the SYS schema owns the context.

Step 3: Create a Package to Retrieve Session Data and Set the Application Context

Next, you must create a PL/SQL package that retrieves the session data and then sets the application context.

To create the package, use the CREATE OR REPLACE PACKAGE statement.

The following example shows how to create the package you need to retrieve the session data and set the application context. Before creating the package, ensure that you are still logged on as user sysadmin_ctx.

CREATE OR REPLACE PACKAGE set_empno_ctx_pkg IS
   PROCEDURE set_empno;
 END;
 /
 CREATE OR REPLACE PACKAGE BODY set_empno_ctx_pkg IS
   PROCEDURE set_empno
   IS
    emp_id HR.EMPLOYEES.EMPLOYEE_ID%TYPE;
   BEGIN
    SELECT EMPLOYEE_ID INTO emp_id FROM HR.EMPLOYEES
       WHERE email = SYS_CONTEXT('USERENV', 'SESSION_USER');
    DBMS_SESSION.SET_CONTEXT('empno_ctx', 'employee_id', emp_id);
   EXCEPTION
    WHEN NO_DATA_FOUND THEN NULL;
  END;
 END;
/

This package creates a procedure called set_empno that performs the following actions:

Step 4: Create a Logon Trigger for the Package

The logon trigger will run when the user logs in.

As user sysadmin_ctx, create a logon trigger for set_empno_ctx_pkg.set_empno package procedure.

CREATE TRIGGER set_empno_ctx_trig AFTER LOGON ON DATABASE
 BEGIN
  sysadmin_ctx.set_empno_ctx_pkg.set_empno;
 END;
/

Step 5: Test the Application Context

Now that the components are all in place, you are ready to test the application context.

  1. Connect as user lozer.

    CONNECT lozer@pdb_name
Enter password: password

    When user lozer logs on, the empno_ctx application context collects their employee ID. You can check it as follows:

    SELECT SYS_CONTEXT('empno_ctx', 'employee_id') emp_id FROM DUAL;

The following output should appear:

<pre class="copy"><code>EMP_ID  -------------------------------------------------------- 168</code></pre>

  1. Connect as user SCOTT.

    CONNECT SCOTT@pdb_name
Enter password: password

User SCOTT is not listed as an employee in the HR.EMPLOYEES table, so the empno_ctx application context cannot collect an employee ID for him.

SELECT SYS_CONTEXT('empno_ctx', 'employee_id') emp_id FROM DUAL;

The following output should appear:

EMP_ID

--------------------------------------------------------

From here, the application can use the user session information to determine how much access the user can have in the database. You can use Oracle Virtual Private Database to accomplish this. .

Related Topics

Step 6: Remove the Components of This Tutorial

If you no longer need the components of this tutorial, then you can remove them.

  1. Connect as SYS with the SYSDBA administrative privilege.

    CONNECT SYS@pdb_name AS SYSDBA
Enter password: password

  2. Drop the users sysadmin_ctx and lozer:

    DROP USER sysadmin_ctx CASCADE;
DROP USER lozer;

  3. Drop the application context.

    DROP CONTEXT empno_ctx;

    Remember that even though sysadmin_ctx created the application context, it is owned by the SYS schema.

  4. If you want, lock and expire SCOTT, unless other users want to use this account:

    ALTER USER SCOTT PASSWORD EXPIRE ACCOUNT LOCK;