Database-Based Authentication for PHP Apps, Part 2


by Michael McLaughlin

Learn how to secure PHP-based Web applications via database-based authentication; in this installment, fine grained access is examined.

Published May 2007

In Part 1 of this article, you learned how database-based authentication works and how to implement it in PHP-based Web applications.

In Part 2, you will expand on the example authentication and user entry solution constructed previously. Whereas the current solution treats all users as equals, the idea of users accessing all things equally is unusual in real applications. Rather, your users probably have roles and privileges limiting their access to data based on their position of responsibility in a company.

Since Oracle8i, Oracle Database has included a security feature known as Virtual Private Database (VPD). A VPD lets you define, build and apply security policies. These security policies can restrict user access to specific tables or parts of tables. The policies let you manage access privileges for scheme by examining session level data, which the database creates when a user connects to an Oracle instance. VPD implementation requires a common repository schema that grants privileges to distinct scheme. Your user-defined database design must also provide a striping column in common repository tables. The access to these tables is then managed by the VPD policies.

VPD technology relies on the connection data stored in the database catalog. The connection data identifies and tracks activities of a schema (user). All but one field of this connection metadata is defined and managed by the Oracle database. The CLIENT_INFO column is available for developers to use when building user-defined applications. You can combine the session CLIENT_INFO column with a database column to stripe data at a granularity below schema and support multiple users in a single schema. Oracle E-Business 11i Suite stripes multiple organizations and currencies through views using the CLIENT_INFO column.

The combination of these technologies let you implement fine grained access privileges and roles through security policies. Alternatively, you can manage fine grained access by bundling the CLIENT_INFO column into your authentication model.

Oracle Database Connection Architecture

Sample Code Manifest

Program Name Language Description

create_identity_db2.sql

SQL

Script that builds the database and seeds necessary rows to initialize the web application

credentials2.inc

PHP

Include file that defines the three global constants you use in all oci_connect() function calls

SignOnUserAdmin.php

PHP

Program file that resets the PHP session ID before calling the UserAdmin.php program

UserAdmin.php

PHP

Program file that (a) authenticates new users and returning active sessions; (b) renders a form to enter new users into an ACL when a user enjoys permissions; and (c) acts as a portal to view users based on access permissions.

UserView.php

PHP

Program file that authenticates new users and returning active sessions; and renders a form to view users based on access permissions

The Oracle Database connection architecture has two facets that you should understand as a PHP programmer. First, the actual database connection is known as a database event and has a unique internally maintained numeric value. Every database connection creates a row of metadata in the system catalog. The row exists for the length of the connection and can be queried by any user with DBA role privileges from the V$SESSION view. The value of the SESSIONID in the database is unique and unrelated to the PHP session_id() function return value. Second, you have only one database SESSIONID value when you open a persistent connection with the oci_pconnect() function, while you can have more than one SESSIONID value when opening non-persistent connections.

The oci_connect() and oci_new_connect() functions create new SESSIONID values each time they run. Only the first oci_pconnect() function call creates a SESSIONID value but subsequent oci_pconnect() function calls reuse the open session. This reduces the dynamic marshalling expense required to build a new connection and enables preservation of state information between web pages and the database server. It is also possible for you to have two or more open non-persistent connections in the scope of a single PHP session or response-acknowledgment request cycle.

Virtual Private Database Architecture

The VPD architecture works by combining the connection metadata and user-defined security policies in a separate security schema. Security policies are procedural programming instructions written in PL/SQL stored functions. The policy functions have two formal parameters by default — one is a string representing an Oracle schema (also known as a user account), and the other is a table name. These policy functions return a variable length string that is appended to a WHERE clause for any SQL statement against the table. The WHERE clause lets you restrict the rows your users can query and transact against. You restrict access by filtering data on a qualifying column like an organization unit or security group role. Filtered tables are known as striped tables or views.

You implement a VPD by creating a schema/user, like SECURITY_ADMIN, and granting the schema access to the SYS.DBMS_RLS stored package. The DBMS_RLS package lets you add, maintain, and remove security policies. Then you can define policy stored functions in the SECURITY_ADMIN schema and add them as policies using the DBMS_RLS package.

The policies maintain a barrier around the data, which is typically located in another schema. The schema that owns the tables then grants access and transaction privileges to one or more other schemas. This configuration structure is similar to the default definer rights model for stored procedures in an Oracle database. Stored procedures act by default with the privileges of the schema where they are created.

In a classic definer rights model, the owning schema does not typically grant access and transaction rights to tables and views to other users. The schema grants only execute privileges on their stored procedures. The stored procedures guarantee that users access and transact against the data in a planned way. Unfortunately, the definer rights model does not enable slicing the data into roles based on the accessing source — user or schema.

Combining the policies of VPDs with the definer rights model lets you slice the data into pieces based on assigned roles and privileges. It does this because the tables are now accessed like subsets, by effectively creating views.

Figure 1 illustrates the idea that four users see different slices or views data.



Figure 1 Definer Rights Model

Dividing data into views is done by adding a striping column to a table. You can map striping columns against a schema name in the VPD security policy function. You can also stripe the table on a column value other than database schema names.

Figure 2 illustrates how a numeric pseudo key column, SYSTEM_USER_GROUP_ID in the SYSTEM_USER table, lets you group users by a common value.



Figure 2 A Striped Table

The 0 or 1 allows you to distinguish between and group by super and end users respectively. The SYSTEM_USER_GROUP_ID column values are pseudo keys, or numeric sequence values that typically map to descriptions found in another table. This striping model works well when users are not database scheme.

Schema-level security is a common approach that is consistent with traditional approaches to definer rights data models. This user level security approach lets you map non-scheme user accounts as striping column values. You can implement a VPD either way. The next two subsections describe the basic mechanics for both security approaches.

Schema Level Security Model

The schema level security model is straightforward to implement. It requires that all users are scheme. This is typically an undesirable business model and poses significant administrative burdens because of the expense associated with grants and synonym maintenance activities.

In this model, your policy functions secure the schema by querying the context information from the stored session metadata. Only the SYS_CONTEXT() function can secure the connection schema name. You call the SYS_CONTEXT() function inside your policy function as shown in the following sample pseudo code:

CREATE OR REPLACE FUNCTION policy_user_maintenance
( schema           VARCHAR2
, tab              VARCHAR2 )
RETURN VARCHAR2 IS

  -- Define local variables.
  schema_name      VARCHAR2(30);
  where_clause     VARCHAR2(100);

BEGIN
  -- Get schema name.
  schema_name := SELECT SYS_CONTEXT('userenv ','session_user ') FROM dual;

  CASE schema_name
    WHEN user_a THEN
       ... qualifying logic ...
    WHEN user_b THEN
       ... qualifying logic ...
    WHEN user_c THEN
       ... qualifying logic ...
    ELSE
       ... qualifying logic ...
  END CASE;

  -- Return where clause.
  RETURN where_clause;

END policy_user_maintenance;
/

An implementation of a real POLICY_USER_MAINTENANCE() function like the example shell works only when users are scheme. Your PHP programs would require authentication, then each connect statement would become dynamic. This means that each PHP connection function call would need to have access to various scheme credentials. While this is an ugly solution, it is doable--albeit not likely a production solution that qualifies you for a bonus.

A better solution is possible. You achieve it by supplementing the context information stored in the session metadata. This is done by writing information to the CLIENT_INFO column in the V$SESSION view as discussed in the next subsection. The CLIENT_INFO column is a 64 character user-defined string in the session metadata.

User Level Security Model

The user level security model requires more effort to implement than the schema level model. You implement user level security by a three step process. You (a) authenticate credentials against your ACL, (b) write your authenticated credentials to the context information stored in the session metadata, and (c) write your profile stored functions to validate against the user defined context information stored in the session metadata.

Authenticating Credentials. While you have discretion as to how you implement your authentication, you should keep it simple. Assuming that you have adopted a similar SYSTEM_USER table as your ACL, you can authenticate against the user name and password in the table. Then, you can get a copy of the primary key column value, and use the primary key value as your context identifier in the session metadata. This eliminates upper, lower and mixed case validations but it can also appear as a magic number solution.

Reading and Writing Credentials to Session Metadata

The process of writing to and reading from the session CLIENT_INFO column requires you to use the DBMS_APPLICATION_INFO package. You use the SET_CLIENT_INFO procedure in the DBMS_APPLICATION_INFO package to write data into the 64 character CLIENT_INFO column found in the V$SESSION view. The following anonymous PL/SQL block assumes that the SYSTEM_USER_ID column value is 1:

BEGIN
   -- Write value to V$SESSION.CLIENT_INFO column.
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO('1 ');
END;
/

You can now read this value by calling the READ_CLIENT_INFO() procedure. You should enable SERVEROUTPUT using SQL*Plus to see the rendered output when you run the following program:

DECLARE
  client_info      VARCHAR2(64);
BEGIN
   -- Read value from V$SESSION.CLIENT_INTO column.
   DBMS_APPLICATION_INFO.READ_CLIENT_INFO(client_info);

   -- Print it to console.
   DBMS_OUTPUT.PUT_LINE('[ '||client_info||']');
END;
/

User-defined session columns let you store unique information related to user credentials from your ACL. You assign a session column value during user authentication. Then, the session CLIENT_INFO column allows you to manage multiple user interactions in a single schema. Authenticated users can access whole or partial rows from tables when their session CLIENT_INFO column value matches a striping column values in the table.

Writing a Security Profile Stored Procedure. Your policy functions secure the user-defined CLIENT_INFO column value by querying the context information from the stored session metadata. Both the USERENV() and SYS_CONTEXT() functions can secure the user-defined column value.

You can use the USERENV() or SYS_CONTEXT() function to query database session metadata. You can also use these functions when you are unsure if your user account has the DBA role privilege. The SYS_CONTEXT() function is more flexible and provides greater access to the session metadata, and you should use it in lieu of the older USERENV() function. The following demonstrates how you check whether you have the DBA role by using the SYS_CONTEXT() function:

SELECT   sys_context('userenv ', 'isdba ')
FROM     dual;

The query will return a Boolean true when you have the DBA role and false when you do not have the role. The SYS_CONTEXT() function provides an alternative to querying the V$SESSION view for connection information, and is available to restricted permission scheme.

You change the previously discussed policy function by replacing the SCHEMA_NAME variable with the CLIENT_INFO variable. Then, you use the SYS_CONTEXT function to select the current CLIENT_INFO column value from the session metadata. All remaining logic in the security policy procedure would also require changes from the schema name to the user-defined CLIENT_INFO column value. The following shows a revised pseudo-code security policy function for a user-level security management procedure:

CREATE OR REPLACE FUNCTION policy_user_maintenance
( client_info      VARCHAR2
, tab              VARCHAR2 )
RETURN VARCHAR2 IS

  -- Define local variables.
  client_info_name VARCHAR2(64);
  where_clause     VARCHAR2(100);

BEGIN
  -- Get schema name.
  client_info_name := SELECT SYS_CONTEXT('userenv ','client_info ') FROM dual;

  CASE client_info_name
    WHEN user_a THEN
       ... qualifying logic ...
    WHEN user_b THEN
       ... qualifying logic ...
    WHEN user_c THEN
       ... qualifying logic ...
    ELSE
       ... qualifying logic ...
  END CASE;

  -- Return where clause.
  RETURN where_clause;

END policy_user_maintenance;
/

Authentication Process Model

This section provides an authentication solution that uses the CLIENT_INFO session column and striped tables. Rather then introduce the complexity of setting up a security administration schema and policy function, the solution uses a striped view. Striped views contain WHERE clauses that join user session data values to the striping column values. This approach mimics the dynamic WHERE clauses built by VPD security functions. Oracle E-Business Suite uses this technique to access dynamic views containing user specific organization and reporting currency data.

The authentication process model depends on the same two tables introduced in Part 1. These tables are shown in Figure 3.



Figure 3 Basic Authentication Data Model

While there is no change in the definition of the tables, this authentication model implements programming logic that takes advantage of data striping provided by the SYSTEM_USER_GROUP_ID column in the SYSTEM_USER table.

The authentication model stripes only for super and end users, which are the administration and employee groups respectively. You can identify super users by querying the database and checking the SYSTEM_USER_GROUP_ID column for a value of one. End users will have a value greater than or equal to 1. The PHP scripts read the SYSTEM_USER_GROUP_ID to set the form session as a super or end user; and they set the CLIENT_INFO column values using the DBMS_APPLICTION_INFO package to read data from striped views.

The balance of this section discusses how to setup and experiment with the test application before analyzing the code and identify management solution. The sample schema and code differ from that in Part 1 and they use a different database schema. This enables you to have both sample code trees up and running on your system for comparison purposes.

Setup Test Application. The demonstration application uses an Oracle schema named IDMGMT2, and the password is the same as the schema. You can create the user and environment by following these steps:

1. Log into the database as the privileged SYSTEM user and run the following commands. You must explicitly grant the CREATE ALL VIEWS permission when using Oracle 10g due to changes in how the RESOURCE role works:

SQL> CREATE USER IDMGMT2 IDENTIFIED BY IDMGMT2;
SQL> GRANT CONNECT, RESOURCE TO IDMGMT2;
SQL> GRANT CREATE ALL VIEWS TO IDMGMT2;

2. Connect to the new user schema:

SQL> CONNECT IDMGMT2/IDMGMT2@XE

3. Run the create_identity_db2.sql script in the IDMGMT2 schema to create all necessary objects and seed the SYSTEM_USER table:

SQL> @create_identity_db2.sql

4. Place the following files in your htdocs directory or a subdirectory of the htdocs directory:

 • SignOnUserAdmin.php
 • UserAdmin.php
 • UserView.php

These steps complete the setup necessary for our purposes here. You can now experiment with either the realm or session identification management examples.

Experiment with Session Authentication. You can experiment with the realm identity management by following these steps:

1. Open the realm identification management examples by using the following URL:

http://localhost/SignOnUserAdmin.php

2. You can use either of the following seeded accounts as valid credentials using Basic HTTP/HTTPS authentication. Make sure you don't have the cap lock enabled because they are case sensitive.

User Name Password
administrator welcome
guest guest



Figure 4 Cookie and Session Login page

You will be presented with the following page, where you should login as a privileged user in this test system, using the "administrator" user name and "welcome" password credentials, and not the restricted privilege "guest" user.

After authenticating your credentials, the program transfers you to the New User (UserAdmin.php) form. You should notice that there are changes from the prior New User (AddDBUser.php) form. The form now supports entering new users as belonging to either the administration or employee group, and the form provides a view to see entered users. The administration group members have privileges to add new users while the employee group's privileges are restricted and unable to add new users.

3. You can now enter a user in both the administration or employee groups. The administration group is a super user group and the employee group is for end users. You should enter both of the following users:

  Administration Group Employee Group

User ID

jonesi

ravenwoodm

Password

jonesi

ravenwoodm

First Name

Indiana

Marion

Last Name

Jones

Ravenwood

Make sure you don't have the cap lock enabled because entries are case sensitive. If the Administration Group radio button is not clicked, you should click it when entering Indiana Jones. Click the Employee Group radio button before entering Marion Ravenwood. You will use both accounts in subsequent navigation steps.



Figure 5 Cookie and Session Login Page

The administrative group is the default for the New User (UserAdmin.php) form. When you create an administrative group user, the SYSTEM_USER_GROUP_ID column gets a 0 value. Creating a new user in the employee group inserts a value of 1 in the same column. Users who are defined in the administration group have the privilege to create other users, while those in the employee group cannot create new users.

After you enter Indiana Jones and Marion Ravenwood, you will see the New User form, as shown in Figure 6. This form acknowledges that you've entered a new user. If your credentials fail to meet set conditions the same line would explain why.



Figure 6 Main Administration page with a confirmation message

4. After you enter both Indiana Jones and Marion Ravenwood as the "administrator" user, you can view your user ACL members by clicking the View User button.



Figure 7 User Account View from an Administration Group user

4. After you have confirmed entry Indiana Jones as a system administrator and Marion Ravenwood as a system user, you should click the Log Out button. Then, you should log in as a system user by using the guest or Marion Ravenwood credentials. Figure 8 shows how the New User form looks to an employee group user.



Figure 8 Main Administration page for a Restricted User Account

5. When you click the View User button as Marion Ravenwood, only your account is displayed. This behavior works because the form returns a striped view of data.



Figure 9 User Account View from a Employee Group User

You have now experimented with the striped user authentication forms. The next section analyzes how identity management works to support these forms.

Analyze the UserAdmin.php Session Authentication Code. Several changes to prior version of the AddDBUser.php program presented in the earlier paper make this striped behavior possible. The AddDBUser.php program has been changed to the UserAdmin.php script with the following changes:

  • Modify the get_session() function to get and set proper user-defined session metadata variable values.
  • Modify the create_new_db_user() function to verify whether the current authenticated user is a privileged user or not.
  • Modify the get_message() function and the base form logic to provide confirmation of user entry.
  • Disable the text entry fields, Add User button and Administration/Employee group radio buttons unless the user is a privileged user.
  • The base code of the verify_db_login() function now captures the group value and assigns it to a $_SESSION variable.

You will build striped web applications by implementing similar coding changes in your applications. Each of the five business logic changes are examined individually.

Modify the get_session() Function. The get_session() function has changed between the AddDbUser.php and UserAdmin.php versions to enable separate request handling based on a user's group assignment. The select statement now returns two additional columns — they are the SYSTEM_USER_ID and SYSTEM_USER_GROUP_ID values. The get_session() function evaluates whether the SYSTEM_USER_GROUP_ID value is 0, which is the administration group in this application. When the user has administration privileges, the function assigns the group value to the $_SESSION['client_info'] value. When the user does not have administration privileges, the function assigns the individual's record identifier as the $_SESSION['client_info'] value.

You can then use the $_SESSION['client_info'] value to control future connections in the scope of the currently running script file. You maintain a $_SESSION['client_info'] value in your PHP program scope when you're using non-persistent connections to the Oracle database. You can avoid this by simply setting the CLIENT_INFO column value in the session metadata, provided you made all persistent connections — using oci_pconnect() function.

The modified get_session() function is shown below with the changes highlighted by bold text:

function get_session($sessionid,$userid = null,$passwd = null)
{
  // Attempt connection and evaluate password.
  if ($c = @oci_connect(SCHEMA,PASSWD,TNS_ID))
  {
    // Assign metadata to local variable.
    $remote_address = $_SERVER['REMOTE_ADDR'];

    // Return database UID within 5 minutes of session registration.
    $s = oci_parse($c,"SELECT   su.system_user_id
                       ,        su.system_user_name
                       ,        su.system_user_group_id
                       ,        ss.system_remote_address
                       ,        ss.system_session_id
                       FROM     system_user su JOIN system_session ss
                       ON       su.system_user_id = ss.system_user_id
                       WHERE    ss.system_session_number = :sessionid
                       AND     (SYSDATE - ss.last_update_date) <=
                                  .003472222");

    // Bind the variables as strings.
    oci_bind_by_name($s,":sessionid",$sessionid);

    // Execute the query and raise missing table message on failure.
    if (@oci_execute($s,OCI_DEFAULT))
    {
      // Check for a validated user, also known as a fetched row.
      if (oci_fetch($s))
      {
        // Assign unqualified values.
        $_SESSION['userid'] = oci_result($s,'SYSTEM_USER_NAME');

        // Assign the privileged group or user primary key column value.
        if (oci_result($s,'SYSTEM_USER_GROUP_ID') == 0)
          $_SESSION['client_info'] = oci_result($s,'SYSTEM_USER_GROUP_ID');
        else
          $_SESSION['client_info'] = oci_result($s,'SYSTEM_USER_ID');

        // Check for same remote address.
        if ($remote_address == oci_result($s,'SYSTEM_REMOTE_ADDRESS'))
        {
          // Refresh last update timestamp of session.
          update_session($c,$sessionid,$remote_address);
          return (int) oci_result($s,'SYSTEM_SESSION_ID');
        }
        else
        {
          // Log attempted entry.
          record_session($c,$sessionid);
          return 0;
        }
      }
      else
      {
        // Record when not first login.
        if (!isset($userid) && !isset($passwd))
          record_session($c,$sessionid);
        return 0;
      }
    }
    else
    {
      // Set error message.
      set_error(__FUNCTION__,array('SYSTEM_USER','SYSTEM_SESSION'));
    }

    // Close the connection.
    oci_close($c);
  }
  else
  {
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }
}

Modify the create_new_db_user() Function. The create_new_db_user() function now returns a Boolean variable as opposed to a void. This change lets the UserAdmin.php program call the function inside a control structure, as shown below:

if (create_new_db_user($_SESSION['db_userid']
                      ,$newuserid
                      ,$newpasswd
                      ,$fname
                      ,$lname
                      ,$usergroup))
{
  // Set code to successful.
  $code = USER_VALID;

  // Render new form with successful acknowledgement.
  userAdminForm(array("code"=>$code
                     ,"form"=>"UserAdmin.php"
                     ,"userid"=>$newuserid));
}

The change lets you append a success message to the next rendered copy of the New User form. The function also guards against an unauthorized user adding a new user. While the functionality to add a new user is restricted by conditional rendering of the form based on the privileges of authenticated users, it should also be protected in the function. The precautions create a good design model that does not exclusively rely on tightly coupled behaviors. Functional coupling can get lost during maintenance coding and you should ensure independence of action for all functions. Functions can then be used by different user interfaces (UIs).

This application UI takes the approach to prevent an unauthorized attempt to create a new user, but another may enable the attempt but report back to the user that they are unauthorized. Functions supporting UIs should be flexible enough to handle both approaches, like this one.

The create_new_db_user() function now sets a $written control variable to false on entry. Then, it checks whether the active user is authorized to add a new user. It does this by reading the $_SESSION['client_info'] value set by the get_session() function. The control variable is reset to true when a new user is successfully added to the database. The complete function follows below:

// Add a new user to the authorized control list.
function create_new_db_user($userid,$nuserid,$npasswd,$fname,$lname
                           ,$ugroup)
{
  // Set control variable.
  $written = false;

  // Attempt connection and evaluate password.
  if ($c = @oci_connect(SCHEMA,PASSWD,TNS_ID))
  {
    // Check for prior insert, possible on web page refresh.
    if (!is_inserted($c,$nuserid) && ($_SESSION['client_info'] == 0))
    {
      // Return database UID.
      $s = oci_parse($c,"INSERT INTO system_user
                         ( system_user_id
                         , system_user_name
                         , system_user_password
                         , first_name
                         , last_name
                         , system_user_group_id
                         , system_user_type
                         , start_date
                         , created_by
                         , creation_date
                         , last_updated_by
                         , last_update_date )
                         VALUES
                         ( system_user_s1.nextval
                         , :newuserid
                         , :newpasswd
                         , :firstname
                         , :lastname
                         , :usergroup
                         , 1
                         , SYSDATE
                         , :userid1
                         , SYSDATE
                         , :userid2
                         , SYSDATE)");

      // Bind the variables as strings.
      oci_bind_by_name($s,":newuserid",$nuserid);
      oci_bind_by_name($s,":newpasswd",sha1($npasswd));
      oci_bind_by_name($s,":firstname",$fname);
      oci_bind_by_name($s,":lastname",$lname);
      oci_bind_by_name($s,":usergroup",$ugroup);
      oci_bind_by_name($s,":userid1",$userid);
      oci_bind_by_name($s,":userid2",$userid);

      // Execute the query print error handling for missing table.
      if (@oci_execute($s,OCI_COMMIT_ON_SUCCESS))
      {
        // Update control variable for insert.
        $written = true;
      }
      else
      {
        // Set error message.
        set_error(__FUNCTION__,array('SYSTEM_USER'));
      }
    }

    // Close the connection.
    oci_close($c);

    // Return control variable.
    return $written;
  }
  else
  {
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }
}

Modify the get_message() Function. The only change to the get_message() function is the addition of two criteria. One acknowledges successful entry of a new user, and the other supports an unauthorized access message. You should also note that the previous magic numbers have been converted to constants, which improves the readability of the code.

Disable Conditionally the Add User and Group Radio Buttons. The userAdminForm() function now conditionally renders the text entry fields, Add User and Administration/Employee group radio buttons. This change implements the UI discussed earlier and prevents unauthorized attempts to add new users. While the text entry fields and Administration/Employee group radio buttons have no utility when the submission button is disabled, they are likewise disabled to avoid end-user confusion. They are disabled when the following conditional statement is not met:

if ((!@$_SESSION['client_info']) && (@$_SESSION['client_info'] == 0))

The statement checks whether the session level variable is both set and equal to 0, which is the only authorized or privileged account in the application. In a real implementation, you would probably have a list of authorized account privileges in an array. Assuming that you initialize a privileged ACL in a $authorized_list array variable, you would most likely use a conditional expression like this:

if ((!@$_SESSION['client_info']) &&
    (array_search(@$_SESSION['client_info'],$authorized_list)))

Modify the verify_db_login() Function. The application design enables a session to be updated when a user log out and back in using the same credentials within a timeout window. This design requires you to update the behavior of the verify_db_login() function. You change the function by enabling it to capture and set the $_SESSION['client_info'] value, as shown below in the function implementation:

function verify_db_login($userid,$passwd)
{
  // Attempt connection and evaluate password.
  if ($c = @oci_connect(SCHEMA,PASSWD,TNS_ID))
  {
    // Return database UID.
    $s = oci_parse($c,"SELECT   system_user_id
                       ,        system_user_group_id
                       FROM     system_user
                       WHERE    system_user_name = :userid
                       AND      system_user_password = :passwd
                       AND      SYSDATE BETWEEN start_date
                                        AND NVL(end_date,SYSDATE)");

    // Assign encryted value to variable, avoiding E_STRICT error.
    $newpassword = sha1($passwd);

    // Bind the variables as strings.
    oci_bind_by_name($s,":userid",$userid);
    oci_bind_by_name($s,":passwd",$newpassword);

    // Execute the query and raise missing table message on failure.
    if (@oci_execute($s,OCI_DEFAULT))
    {
      // Check for a validated user, also known as a fetched row.
      if (oci_fetch($s))
      {
        // Confirm session and collect foreign key reference column.
        if ((!isset($_SESSION['session_id'])) ||
            (!isset_sessionid($c,$_SESSION['sessionid'])))
        {
          $_SESSION['db_userid'] = oci_result($s,'SYSTEM_USER_ID');
          $_SESSION['client_info'] = oci_result($s,'SYSTEM_USER_GROUP_ID');
          register_session($c,(int) $_SESSION['db_userid']
                          ,$_SESSION['sessionid']);
        }

        // User verified.
        return true;
      }
      else
      {
        // User not verified.
        return false;
      }
    }
    else
    {
      // Set error message.
      set_error(__FUNCTION__,array('SYSTEM_USER'));
    }

    // Close the connection.
    oci_close($c);
  }
  else
  {
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }
}

The five analyses have reviewed the changes to UserAdmin.php script. The next section will examine the new UserView.php program.

Analyze the UserView.php Session Authentication Code. The UserView.php program is new but is an outgrowth of the business logic found in the UserAdmin.php program. The development of the new form required three new functions and allowed the removal of several functions. It also required the development of a striped AUTHORIZED_USER view in the database.

By synchronizing the functions, you are positioned to remove them from the individual script files and place them in a single shared library to support the application. This is the next recommended step in building your own identity management solution.

The three new functions added are:

  • The return_users() function that gets the results from a striped view.
  • The set_client_info() function that sets a user-defined session metadata value in the database.
  • The strip_special_characters() function that lets you use otherwise unsupported formatting characters in your PL/SQL program statements, like line returns.

Before examining how the PHP code in the UserView.php form implements the architecture and design, you should first understand how the striped view works. The following is the view definition provided in the create_identity_db2.sql script:

CREATE OR REPLACE VIEW authorized_user AS
  SELECT   su.system_user_id AS user_id
  ,        su.system_user_name AS user_name
  ,        cl.common_lookup_meaning AS user_privilege
  ,        CASE
             WHEN su.first_name IS NOT NULL AND  su.last_name IS NOT NULL
             THEN su.first_name||' '|| su.last_name
             ELSE NULL
           END AS employee_name
  ,        su.system_user_group_id AS group_id
  FROM     system_user su JOIN common_lookup cl
  ON       su.system_user_group_id = cl.common_lookup_id
  WHERE    TO_NUMBER(NVL(SYS_CONTEXT('userenv','client_info'),-1)) = 0
  OR       su.system_user_id = 
             TO_NUMBER(NVL(SYS_CONTEXT('userenv','client_info'),-1))
  ORDER BY CASE
             WHEN first_name IS NULL
             THEN 0
             ELSE 1
           END
  ,        su.last_name
  ,        su.first_name
  ,        su.system_user_id;

The WHERE clause requires resolution of the call to the SYS_CONTEXT() function. When the function fails to return a value, the null value substitution is -1, which should return no rows by design. Therefore, this striped view only works when the user-defined database session metadata CLIENT_INFO column value is set to a matching value by the web application.

The return_users() Function.The return_users() function gets the results from a striped view and formats them for display. The function calls the set_client_info() function before querying the data. The set_client_info() function sets the server session CLIENT_INFO value by calling the DBMS_APPLICATION_INFO() procedure. This limits the view to seeing only those records consistent with the end-user permissions.

The view returns information about all ACL users when the active user is in the Administration group, and only information about the active user when the active user is in the Employee group. This is clearly a limitation of trying to keep the example short and an opportunity for improvement when you implement your own identity management system. The complete return_user() function follows for your reference:

function return_users($userid)
{
  // Attempt connection and evaluate password.
  if ($c = @oci_connect(SCHEMA,PASSWD,TNS_ID))
  {
    // Set the connection striping.
    set_client_info($c,$userid);

    // Return database UID.
    $s = oci_parse($c,"SELECT   user_id
                       ,        user_name
                       ,        user_privilege
                       ,        employee_name
                       FROM     authorized_user");

    // Execute the query, error handling should be added.
    if (@oci_execute($s,OCI_DEFAULT))
    {
      // Set a first row control variable.
      $no_row_fetched = true;

      // Initialize the return variable in case no rows are found.
      $out = '';

      // Check for a validated user, also known as a fetched row.
      while (oci_fetch($s))
      {
        if ($no_row_fetched)
        {
          $out .= '<tr>';
          $out .= '<td align="center"><b>User ID</b></td>';
          $out .= '<td align="center"><b>User Name</b></td>';
          $out .= '<td align="center"><b>User Privilege</b></td>';
          $out .= '<td align="center"><b>Employee Name</b></td>';
          $out .= '</tr>';
          $no_row_fetched = false;
        }

        $out .= '<tr>';

        for ($i = 1;$i <= oci_num_fields($s);$i++)
          if (!is_null(oci_result($s,$i)))
          {
            if ($i == 1)
              $out .= '<td align="right">'.oci_result($s,$i).'</td>';
            else
              $out .= '<td>'.oci_result($s,$i).'</td>';
          }
          else
            $out .= '<td> </td>';
        $out .= '</tr>';
      }
    }
    else
    {
      // Set error message.
      set_error(__FUNCTION__,array('AUTHORIZED_USER'));
    }

    // Close the connection.
    oci_close($c);

    // Return formatted string.
    return $out;
  }
  else
  {
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }
}

The set_client_info() Function.The set_client_info() function calls a PL/SQL procedure inside an anonymous PL/SQL block.

// Strip special characters, like carriage or line returns and tabs.
function set_client_info($c,$userid)
{
  // Declare a PL/SQL execution command.
  $stmt = "BEGIN
             dbms_application_info.set_client_info(:userid);
           END;";

  // Strip special characters to avoid ORA-06550 and PLS-00103 errors.
  $stmt = strip_special_characters($stmt);

  // Parse a query through the connection.
  $s = oci_parse($c,$stmt);

  // Map the local variable to a bind variable.
  oci_bind_by_name($s,':userid',$userid);

  // Run the procedure.
  if (oci_execute($s))
    return true;
  else
    return false;
}

The strip_special_characters() Function. The strip_special_characters() function removes tabs and line returns that cause problems with the PL/SQL parser. This enables you use whitespace and line returns to make your code more readable. The function is:

function strip_special_characters($str)
{
  $out = "";
  for ($i = 0;$i < strlen($str);$i++)
    if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) &&
        (ord($str[$i]) != 13))
      $out .= $str[$i];
  // Return pre-parsed SQL statement.
  return $out;
}

Conclusion

You have now learned how identity management works in the context of virtual private databases for scheme, and how to configure the application for individual users found in an ACL within a single schema. The code examples and analyses show how you can operate selectively based striping values of authenticated users. The users can be stored in an ACL table that resides in a single schema. You can now extend the provided solution into a VPD model by simply building the policy functions against the session CLIENT_INFO column value.


Michael McLaughlin is the author of Oracle Database 10g Express Edition PHP Web Programming, and co-author of Oracle Database 10g PL/SQL Programming and Expert Oracle PL/SQL by Oracle Press, and a professor of Computer Information Technology at Brigham Young University — Idaho.