Oracle supports Virtual Private Databases (VPDs) by combining session-level metadata from connections and security policies. You have two options when you configure a VPD. One option lets you build the VPD on a schema and the other option lets you build the VPD based on striped views. This article will show you how to stripe a view and manage access to rows and row sets.

A striped view may be a subset of a single table or a subset of a set of joined tables. You stripe a table by including one or more columns that act as a filter. For example, you can filter rows on a group identifier, a cost center, or a reporting currency. A striped view lets multiple users see only certain rows or sets of rows in the view, which are like slices of a pie.

 

Striped View

 

The article shows you how to:

  • Set and read the client_info column
  • Stripe a view based on the client_info column
  • Leverage the client_info column value for authentication

You typically set up VPDs with the security_admin and dbms_rls packages. It’s also possible to mimic VPD behavior by striping views and setting up Oracle’s metadata. Oracle manages its connection metadata in the v$session view. While there are several columns that you can set, this article focuses on only the client_info column of the v$session view.

You can set metadata when you authenticate against an Access Control List (ACL). In December 2015, I wrote an article about how you can write and deploy a PL/SQL authentication function. This article uses the authorize function from that article.

 The dbms_application_info package lets you set and read the client_info column’s value. The client_info column is 64 characters in length, and you can set multiple values inside the string. The Oracle E-Business Suite actually stores values for organization, currency, and language in the client_info column for each connection. They use the fnd_profile package to set and read the client_info and other information specific to the Oracle E-Business Suite.

Set and Read client_info Column

You set the client_info value with the dbms_application_info package’s set_client_info procedure. After setting the value, you can read the client_info value with the read_client_info procedure.

The following set_login function sets a user_id and user_group_id value in the client_info column of the v$session view:

SQL> CREATE OR REPLACE FUNCTION set_login
  2  ( pv_login_name VARCHAR2 ) RETURN VARCHAR2 IS
  3
  4    /* Declare a success flag to false. */
  5    lv_success_flag NUMBER := 0;
  6
  7    /* Declare a common name for a return variable. */
  8    client_info VARCHAR2(64) := NULL;
  9
 10    /* Declare variables to hold cursor return values. */
 11    lv_login_id NUMBER;
 12    lv_group_id NUMBER;
 13
 14    /* Declare a cursor to return an authorized user id. */
 15    CURSOR authorize_cursor
 16    ( cv_login_name VARCHAR2 ) IS
 17      SELECT   a.user_id
 18      ,        a.user_group_id
 19      FROM     application_user a
 20      WHERE    a.user_name = cv_login_name;
 21
 22  BEGIN
 23
 24    /* Check for not null login name. */
 25    IF pv_login_name IS NOT NULL THEN
 26      /* Open, fetch, and close cursor. */
 27      OPEN authorize_cursor(pv_login_name);
 28      FETCH authorize_cursor INTO lv_login_id, lv_group_id;
 29      CLOSE authorize_cursor;
 30
 31      /* Set the CLIENT_INFO flag. */
 32      dbms_application_info.set_client_info(
 33           LPAD(lv_login_id,5,' ')
 34        || LPAD(lv_group_id,5,' '));
 35      dbms_application_info.read_client_info(client_info);
 36
 37      /* Set success flag to true. */
 38      IF client_info IS NOT NULL THEN
 39        lv_success_flag := 1;
 40      END IF;
 41    END IF;
 42
 43    /* Return the success flag. */
 44    RETURN lv_success_flag;
 45  END;
 46  /

The authorize_cursor cursor on lines 15 through 20 queries the application_user table for a valid user’s user_id and user_group_id values. The call to the set_client_info procedure breaks across lines 32 through 34 to ensure the display doesn’t wrap. The login_id value is a number and the LPAD converts it to a five-character string by left padding whitespace. The LPAD function also converts the group_id value to a five-character string. This example allows only five digit values for both the login_id and group_id columns, but a real model should probably allocate space for up to ten digits.

Line 35 calls the read_client_info procedure, which is a call-by-reference procedure. The read_client_info returns the client_info variable into the local scope of the function. The IF-block verifies the client_info column value is not null and assigns a one to the success_flag variable. The function returns 1 when the client_info column is set and 0 when it isn’t set.

You can call the set_login function like this:

SQL> SELECT set_login('potterhj') AS success FROM dual;

It should return 1 as successful. You can then query the values of the client_info column, like this:

SQL> SELECT   SYS_CONTEXT('userenv','client_info') AS client_info
  2  FROM     dual;

The sys_context function returns the entire client_info string, like:

CLIENT_INFO
--------------
    1    2

You also can implement an anonymous PL/SQL block, like:

SQL> SET SERVEROUTPUT ON SIZE UNLIMITED
SQL> DECLARE
  2    /* Declare local variable. */
  3    client_info VARCHAR2(64);
  4  BEGIN
  5    /* Read client info. */
  6    dbms_application_info.read_client_info(client_info);
  7
  8    /* Print output. */
  9    dbms_output.put_line(
 10        'User ID [ '||SUBSTR(client_info,1,5)||']');
 11    dbms_output.put_line(
 12        'Group ID ['||SUBSTR(client_info,6,5)||']');
 13  END;
 14  /

Line 6 reads the client_info value, and lines 10 and 12 parse the text into two values. It prints:

User ID  [    1]
Group ID [    2]

This part of the article taught you how to set and read the client_info column value from the v$session view.

Stripe a View based on the client_info Column

You can stripe the application_user table because we have two columns that let us filter the data. They are the user_id and user_group_idcolumns, as you can see below:

SQL> desc application_user

Name Null? Type
----------------- -------- --------------
USER_ID           NOT NULL NUMBER
USER_NAME         NOT NULL VARCHAR2(20)
USER_PASSWORD     NOT NULL VARCHAR2(40)
USER_ROLE         NOT NULL VARCHAR2(20)
USER_GROUP_ID     NOT NULL NUMBER
USER_TYPE         NOT NULL NUMBER
START_DATE        NOT NULL DATE
END_DATE                   DATE
FIRST_NAME        NOT NULL VARCHAR2(20)
MIDDLE_NAME                VARCHAR2(20)
LAST_NAME         NOT NULL VARCHAR2(20)
CREATED_BY        NOT NULL NUMBER
CREATION_DATE     NOT NULL DATE
LAST_UPDATED_BY   NOT NULL NUMBER
LAST_UPDATE_DATE  NOT NULL DATE

There are many ways to stripe tables because there are many types of use cases. The use case for this example is to grant three types of privilege. They are: the privilege to view all users in the authorized_user table, the privilege to view all users in the same group, and the privilege to view only your user record.

While hardcoding values for a simple use case is tempting, it’s always a bad choice. Creating an authorized_grouptable appears as the best way to keep the design flexible to add new groups and simple for use. The table is defined as follows:

SQL> CREATE TABLE authorized_group
  2  ( authorized_group_id    NUMBER
  3  , authorized_group_type  VARCHAR2(20)
  4  , authorized_group_desc  VARCHAR2(20));

There is an inherent sequencing dependency for the data in the authorized_group table. The dependency means the administrator privilege is the first row, the individual privilege is the second row, and all groups are rows three and above. This approach recognizes that the use case only has one administrator and one individual privilege. It also recognizes that you need to define an indefinite number of new groups.

The following creates the striped authorized_userview:

SQL> CREATE OR REPLACE VIEW authorized_user AS
  2    SELECT   au.user_id
  3    ,        au.user_name
  4    ,        au.user_role
  5    ,        au.last_name || ', ' || au.first_name || ' '
  6    ||       NVL(au.middle_name,'') AS full_name
  7    FROM     application_user au CROSS JOIN
  8            (SELECT
  9               TO_NUMBER(
 10                 SUBSTR(
 11                   SYS_CONTEXT('USERENV','CLIENT_INFO'),1,5)) AS login_id
 12             , TO_NUMBER(
 13                 SUBSTR(
 14                   SYS_CONTEXT('USERENV','CLIENT_INFO'),6,5)) AS group_id
 15             FROM dual) fq INNER JOIN authorized_group ag
 16    ON      (fq.group_id = ag.authorized_group_id
 17    AND      ag.authorized_group_type = 'ADMINISTRATOR')
 18    OR      (au.user_group_id = ag.authorized_group_id
 19    AND      ag.authorized_group_type = 'INDIVIDUAL'
 20    AND      au.user_group_id = fq.group_id
 21    AND      au.user_id = fq.login_id)
 22    OR      (fq.group_id > ag.authorized_group_id
 23    AND      ag.authorized_group_type = 'INDIVIDUAL'
 24    AND      au.user_group_id = fq.group_id);

Lines 8 through 15 define a runtime view that queries the session metadata from the client_info column of the v$session view. A cross join lets you add these two session metadata elements as columns to each row in the application_user table because they’re the SELECT-list of a runtime view with only one row.

There are three alternative joins in this view, which is clearly an advanced SQL solution. The first join condition on lines 16 and 17 resolves for users that are administrators. The second join condition on lines 18 through 21 resolves for those individuals that have restricted access to their own account. The third join condition on lines 22 through 24 resolves for those individuals with group access privileges.

  1. All of the conditional joins use the authorized_group_type to identify the authorized_group_id column, as you can see on lines 17, 19, and 23. You copy the authorized_group_id column into the user_group_id column when inserting a new user into the application_user table or changing an existing user from one privilege to another.

You can also rewrite the authorized_user view into a more modern syntax with the WITHclause on lines 2 through 10, like this:

SQL> CREATE OR REPLACE VIEW authorized_user AS
  2    WITH fq AS
  3     (SELECT
  4        TO_NUMBER(
  5          SUBSTR(
  6            SYS_CONTEXT('USERENV','CLIENT_INFO'),1,5)) AS login_id
  7      , TO_NUMBER(
  8          SUBSTR(
  9            SYS_CONTEXT('USERENV','CLIENT_INFO'),6,5)) AS group_id
 10      FROM dual)
 11    SELECT   au.user_id
 12    ,        au.user_name
 13    ,        au.user_role
 14    ,        au.last_name || ', ' || au.first_name || ' '
 15    ||       NVL(au.middle_name,'') AS full_name
 16    FROM     application_user au CROSS JOIN fq INNER JOIN
 17               authorized_group ag
 18    ON      (fq.group_id = ag.authorized_group_id
 19    AND      ag.authorized_group_type = 'ADMINISTRATOR')
 20    OR      (au.user_group_id = ag.authorized_group_id
 21    AND      ag.authorized_group_type = 'INDIVIDUAL'
 22    AND      au.user_group_id = fq.group_id
 23    AND      au.user_id = fq.login_id)
 24    OR      (fq.group_id > ag.authorized_group_id
 25    AND      ag.authorized_group_type = 'INDIVIDUAL'
 26    AND      au.user_group_id = fq.group_id);

Every use case requires a test case to confirm the value of the solution. The test case for this solution requires that you examine the unfiltered test data in the application_usertable. The sample data contains five users, which you can find with the following query:

SQL> COLUMN user_name FORMAT A14
SQL> COLUMN authorized_group_id FORMAT A21
SQL> SELECT   au.user_name
  2  ,        au.user_id
  3  ,        au.user_group_id
  4  ,        ag.authorized_group_type
  5  FROM     application_user au INNER JOIN authorized_group ag
  6  ON       au.user_group_id = ag.authorized_group_id;

It displays the following data:

USER_NAME      USER_ID    USER_GROUP_ID AUTHORIZED_GROUP_TYPE
-------------- ---------- ------------- ---------------------
potterhj                1             1 ADMINISTRATOR
weasilyr                2             2 INDIVIDUAL
longbottomn             3             2 INDIVIDUAL
holmess                 4             3 GROUP
watsonj                 5             3 GROUP

Now, you can test the striped view with the following three test cases. The first examines whether the user holds the administrator’s responsibility. The second examines whether the user holds the individual’s responsibility. The third examines whether the user holds the group’s responsibility.

This test the administrator’s privilege and returns all users:

SQL> SELECT set_login('potterhj') AS output FROM dual;

This test the individual’s privilege and returns only those user with the individual’s privilege:

SQL> SELECT set_login('longtbottomn') AS output FROM dual;

This test the group’s privilege and returns all user in the same group:

SQL> SELECT set_login('holmess') AS output FROM dual;

The following query tests the results from the authorized_user table:

SQL> SELECT   au.user_name
  2  ,        au.user_id
  3  ,        au.user_role
  4  FROM     authorized_user au;

It returns the following from the sample data:

USER_NAME      USER_ID    USER_ROLE
-------------- ---------- --------------------
holmess                 4 DBA
watsonj                 5 DBA

This segment of the article has shown that the striping view works for three different types of privileges. The next segment shows you how to incorporate it in an authentication function.

Leverage the client_info column value for authentication

It only requires adding a single line of code to the authorize authentication function my earlier Creating a PL/SQL Authentication Functionarticle. The complete modified function follows for the convenience of the reader.

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_match BOOLEAN := FALSE;
  9    lv_session VARCHAR2(30);
 10    lv_user_id NUMBER := -1;
 11
 12    /* Declare authentication_t instance. */
 13    lv_authentication_t AUTHENTICATION_T := authentication_t(null,null,null);
 14
 15    /* Define an authentication cursor. */
 16    CURSOR authenticate
 17    ( cv_username VARCHAR2
 18    , cv_password VARCHAR2 ) IS
 19      SELECT   user_id
 20      ,        user_group_id
 21      FROM     application_user
 22      WHERE    user_name = cv_username
 23      AND      user_password = cv_password
 24      AND      SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
 25
 26    /* Declare a cursor for existing sessions. */
 27    CURSOR valid_session
 28    ( cv_session VARCHAR2
 29    , cv_raddress VARCHAR2 ) IS
 30      SELECT   ss.session_id
 31      ,        ss.session_number
 32      FROM     system_session ss
 33      WHERE    ss.session_number = cv_session
 34      AND      ss.remote_address = cv_raddress
 35      AND     (SYSDATE - ss.last_update_date) <= .003472222;
 36
 37    /* Declare a local exception. */
 38    e EXCEPTION;
 39    PRAGMA EXCEPTION_INIT(e,-20001);
 40
 41    /* Create an autonomous transaction. */
 42    PRAGMA AUTONOMOUS_TRANSACTION;
 43  BEGIN
 44
 45    /* Write authentication records. */
 46    FOR i IN authenticate(pv_username, pv_password) LOOP
 47      /* Set control variable for a match. */
 48      lv_match := TRUE;
 49      lv_user_id := i.user_id;
 50
 51      /* Set the CLIENT_INFO flag or raise an exception. */
 52      IF NOT set_login(pv_username) = 1 THEN
 53        RAISE e;
 54      END IF;
 55
 56      /* Assign existing session ID values. */
 57      FOR j IN valid_session(pv_session, pv_raddress) LOOP
 58        lv_session := j.session_number;
 59      END LOOP;
 60
 61      /* Write new session or update existing session. */
 62      IF NOT lv_session IS NULL THEN
 63        /* Update existing session in system_session table. */
 64        UPDATE   system_session
 65        SET      last_update_date = SYSDATE
 66        WHERE    session_number = pv_session
 67        AND      remote_address = pv_raddress;
 68      ELSE
 69        /* Insert new session into invalid_session table. */
 70        INSERT INTO system_session
 71        VALUES
 72        ( system_session_s.nextval
 73        , pv_session
 74        , pv_raddress
 75        , i.user_id
 76        , i.user_id
 77        , SYSDATE
 78        , i.user_id
 79        , SYSDATE );
 80
 81        /* Assign session ID number. */
 82        lv_session := pv_session;
 83      END IF;
 84      /* Commit the independent transaction. */
 85      COMMIT;
 86    END LOOP;
 87
 88    /* Write an invalid session row. */
 89    IF NOT lv_match THEN
 90      /* Insert new session into invalid_session table. */
 91      INSERT INTO invalid_session
 92      VALUES
 93      ( invalid_session_s.nextval
 94      , pv_session
 95      , pv_raddress
 96      , lv_user_id
 97      , SYSDATE
 98      , lv_user_id
 99      , SYSDATE );
100      /* Commit the independent transaction. */
101      COMMIT;
102    ELSE
103      /* Allocate space, assign instance, and return session ID value. */
104      lv_authentication_t := authentication_t( username => pv_username
105                                             , password => pv_password
106                                             , sessionid => pv_session );
107    END IF;
108
109    /* Return the authentication set. */
110    RETURN lv_authentication_t;
111  EXCEPTION
112    WHEN OTHERS THEN
113      RETURN lv_authentication_t;
114  END;
115  /

Line 38 declares an exception variable and line 39 issues a precompiler instruction that maps the user-defined error number to the exception variable.

Line 52 through 54 is an IF-block that sets the login striping, or it raises an exception when it fails to set the login striping. Lines 111 through 113 handles the exception and returns an empty set of values in the authentication_t type.

The test case is straight forward. You call the

SQL> COLUMN username FORMAT A10
SQL> COLUMN password FORMAT A10
SQL> COLUMN sessionid FORMAT A12
SQL> SELECT   username
  2  ,        SUBSTR(password,1,3)||'...'||SUBSTR(password,-3,3) AS password
  3  ,        sessionid
  4  FROM TABLE(
  5         SELECT   CAST(
  6                    COLLECT(
  7                        authorize( pv_username => 'watsonj'
  8                      , pv_password => 'c0b137fe2d792459f26ff763cce44574a5b5ab03'
  9                      , pv_session => 'session_test'
 10                      , pv_raddress => '127.0.0.1')) AS authentication_tab)
 11         FROM dual);

It prints the following:

USERNAME   PASSWORD   SESSIONID
---------- ---------- ------------
watsonj    c0b...b03  session_test

After having set the striping values inside the authorize function, you can check whether they’re correct with the following query:

SQL> SELECT   au.user_name
  2  ,        au.user_id
  3  ,        au.user_role
  4  FROM     authorized_user au;

It returns the following values:

USER_NAME      USER_ID    USER_ROLE
-------------- ---------- --------------------
holmess                 4 DBA
watsonj                 5 DBA

This article has shown you how to stripe views and how to stripe them inside an authorization function.