RADICORE for PHP - Implementing Virtual Private Databases

By Tony Marston

1st October 2007

Introduction

Implementation

Framework Changes

1. Create the MNU_ACCOUNT table

2. Amend the MNU_USER table

3. Amend the WF_CASE, WF_TOKEN and WF_WORKITEM tables

4. Change the logon procedure

5. Alter the code for reading from the database

6. Alter the code for inserting into the database

Application Requirements

Introduction

Most web applications are constructed and distributed on the principle that each customer runs a separate instance of the application and its underlying database(s). This means that all users who access the application can potentially access all of the data. However, there are some applications which have a single instance yet deal with data for multiple customer/subscriber accounts where each customer/subscriber has its own set of private data. In theses circumstances it is vitally important that the data which belongs to one account must remain private to that account and that this private data cannot be accessed or modified by users of a different account. This is implemented using a feature known as a Virtual Private Database (VPD) or Row Level Security (RLS).

Although some database engines (e.g. Oracle) may have methods of implementing VPD, some may not, in which case it will require code within the application. The purpose of this article is to document how Virtual Private Databases can be implemented with the Radicore framework.

Implementation

In order for records within the same database table to be separated by subscriber account it is necessary for each record within that table to contain a column which provides the account identity. In order for the framework to detect that such a column exists so that it may take the appropriate action it is necessary for this column to have a particular name. A convention within the Radicore framework is that any database column which requires particular processing has a name which is reserved for that purpose, and that name is prefixed with 'rdc', which is short for Radicore. The column which holds this account identity is therefore called rdcaccount_id and is an unsigned integer.

Although some data must be kept private by account, it may also be possible for different accounts to share the same data. For example, a single set of lookup tables could be shared by all accounts instead of forcing each account to maintain its own copy. This leads to the following set of possible options:

• Data within a table is totally shared - this does not contain the rdcaccount_id column.
• Data within a table is totally private - this contains the rdcaccount_id column with a value which is greater than 1.
• Data within a table is part shared and part private - this contains the rdcaccount_id column with a value equal to 1 for shared data and greater than 1 for private data.

If a database table is required to contain private data then it must contain the rdcaccount_id column in one of the following ways:

1. As part of a compound Primary key

   CREATE TABLE IF NOT EXISTS `foobar` (
     `rdcaccount_id` int(11) unsigned NOT NULL default '1',
     `foo_id` varchar(8) NOT NULL default '',
     ......
     PRIMARY KEY  (`rdcaccount_id`,`foo_id`)
   )
   

   In this example a compound key is created by adding rdcaccount_id to the existing primary key which is not a technical key, therefore may have meaning to the user.
2. As part of a compound Candidate (unique) key

   CREATE TABLE IF NOT EXISTS `foobar` (
     `foo_id` int(11) unsigned NOT NULL auto_increment,
     `rdcaccount_id` int(11) unsigned NOT NULL default '1',
     `foo_code` varchar(8) NOT NULL default '',
     ......
     PRIMARY KEY  (`foo_id`),
     UNIQUE KEY `rdcaccount_id` (`rdcaccount_id`,`foo_code`)
   )
   

   In this example there is a technical (or surrogate) primary key whose value is supplied from an auto-incrementing sequence, and a separate candidate key which also contains a code which may have meaning to the user. Note that it is the primary key which is used in any relationships with subordinate tables.
3. As an Index (non-unique key)

   CREATE TABLE IF NOT EXISTS `foobar` (
     `foo_id` int(11) unsigned NOT NULL auto_increment,
     `rdcaccount_id` int(11) unsigned NOT NULL default '1',
     ......
     PRIMARY KEY  (`foo_id`),
     KEY `rdcaccount_id` (`rdcaccount_id`)
   )
   

   In this example there is no candidate key or non-technical primary key with which rdcaccount_id can be combined, so it is implemented as a separate non-unique index.

Note that in the above examples the rdcaccount_id column has a default value of '1' so that if no value is supplied (see below for users with Shared Access) then any new records will automatically be linked with the shared account instead of a specific private account.

Framework Changes

In order for the rdcaccount_id column to be detected and dealt with in the appropriate manner it is necessary for some changes to be made to the framework. These changes can be summarised as follows:

1. Create a new table to hold subscriber account details.
2. Add the rdcaccount_id column to the MNU_USER table.
3. Add the rdcaccount_id column to the WF_CASE, WF_TOKEN and WF_WORKITEM tables.
4. Change the logon procedure.
5. Alter the code for reading from the database.
6. Alter the code for inserting into the database

1. Create the MNU_ACCOUNT table

This table is required to hold the details of all subscriber account, and was constructed as follows:

CREATE TABLE IF NOT EXISTS `mnu_account` (
  `rdcaccount_id` int(10) unsigned NOT NULL auto_increment,
  `account_name` varchar(255) NOT NULL default '',
  `rdcversion` int(10) unsigned NOT NULL default '1',
  `created_date` datetime NOT NULL default '2000-01-01 00:00:00',
  `created_user` varchar(16) default 'UNKNOWN',
  `revised_date` datetime default NULL,
  `revised_user` varchar(16) default NULL,
  PRIMARY KEY  (`rdcaccount_id`)
) TYPE=MyISAM;

New entries on this table can only be created by users with Shared Access (see below). Those with Account Access can only work within their designated account.

The shared account has an ID of '1' while all private accounts have an ID which is greater than '1'.

2. Amend the MNU_USER table

The rdcaccount_id column is added to the MNU_USER table so that each user can be assigned to an account. Note this this is the only table where rdcaccount_id is optional and without a default value.

• A user without a value for rdcaccount_id has Shared Access:
  • Can create new entries on the MNU_ACCOUNT table.
  • Can create/read/amend/delete users for any account, and change a user's account.
  • Can only access shared data on all other database tables. Any new records will have rdcaccount_id set to '1'.
• A user with a value for rdcaccount_id has Account Access:
  • Cannot create new entries on the MNU_ACCOUNT table.
  • Cannot create/read/amend/delete users for any account other than his designated account.
  • Can access shared data on all other database tables, and private data within his designated account. Any new records will have rdcaccount_id set to his account.

3. Amend the WF_CASE, WF_TOKEN and WF_WORKITEM tables

The rdcaccount_id column is added to the WF_CASE, WF_TOKEN and WF_WORKITEM tables as a non-unique index so that the details of workflow cases remain private to each account.

Those Radicore installations which do not use subscriber accounts will automatically have the rdcaccount_id column set to '1' to denote shared access.

4. Change the logon procedure

When a user passes through the LOGON screen the value for rdcaccount_id will be added to the $_SESSION data so that it is available in all subsequent pages. This informs the framework whether the user has Shared Access or Account Access.

5. Alter the code for reading from the database

When reading data from database tables which contain the rdcaccount_id column it is necessary to ensure that the WHERE clause contains a reference to this column. This is achieved by adding the following code to the getData() method:

    if (isset($this->fieldspec['rdcaccount_id'])) {
        // this table is split by account, so account_id must be supplied in WHERE string
        if (!isset($where_array['rdcaccount_id'])) {
      	    $account_id =& $_SESSION['rdcaccount_id'];
      	    if (empty($account_id) AND eregi('mnu_user|mnu_account', $this->tablename)) {
      	        // no account id supplied, so read everything on these tables only
      	    } else {
      	        if (empty($account_id)) {
      	            $account_id_string = "rdcaccount_id='1'";
      	        } else {
      	            $account_id_string = "rdcaccount_id IN ('1', '$account_id')";
      	        } // if
                if (empty($this->sql_where)) {
                    $this->sql_where = $account_id_string;
                } else {
                    if (substr_count($this->sql_where, $account_id_string) == 0) {
                        $this->sql_where .= " AND $account_id_string";
                    } // if
                } // if
      	    } // if
        } // if
    } // if

Note the following:

• If the user has Shared Access (no value for $_SESSION['rdcaccount_id']) then the string rdcaccount_id='1' will be appended to the sql WHERE clause so that the user can read shared data only. This excludes the MNU_USER and MNU_ACCOUNT tables where all records, both shared and private to any account, can be viewed.
• If the user has Account Access then the string rdcaccount_id IN ('1','?') will be appended to the sql WHERE clause so that the user can read any shared data plus any data which is private to his account.

When dealing with a LINK 1 pattern it is necessary to determine whether the INNER table contains the rdcaccount_id column so that the generated SQL can be changed from:

SELECT x_person.person_id, x_option.option_id, x_option.option_desc, 
       CASE WHEN x_pers_opt_xref.person_id IS NULL THEN 'F' ELSE 'T' END AS selected
FROM x_person 
CROSS JOIN x_option 
LEFT JOIN x_pers_opt_xref ON (x_person.person_id=x_pers_opt_xref.person_id 
                          AND x_option.option_id=x_pers_opt_xref.option_id)
WHERE (x_person.person_id ='??') ORDER BY option_id  LIMIT 0,10

to:

SELECT x_person.person_id, x_option.option_id, x_option.option_desc, 
       CASE WHEN x_pers_opt_xref.option_id IS NULL THEN 'F' ELSE 'T' END AS selected
FROM x_person 
CROSS JOIN x_option ON (x_option.rdcaccount_id IN (1,3))
LEFT JOIN x_pers_opt_xref ON (x_person.person_id=x_pers_opt_xref.person_id 
                          AND x_option.option_id=x_pers_opt_xref.option_id)  
WHERE (x_person.person_id ='??') ORDER BY option_id  LIMIT 0,10

This is done using the following code in the _sqlAssembleWhereLink() method:

    $sql_from = $outer_table .' CROSS JOIN ';
    
    $dbobject =& singleton::getInstance($inner_table);
    if (isset($dbobject->fieldspec['rdcaccount_id'])) {
        $account_id =& $_SESSION['rdcaccount_id'];
        if (empty($account_id)) {
            $account_id_string .= "rdcaccount_id='1'";
        } else {
            $account_id_string .= "rdcaccount_id IN ('1', '$account_id')";
        } // if
        $sql_from .= " ON ($inner_table.$account_id_string)";
    } // if

6. Alter the code for inserting into the database

Before being added to the database all new data must pass through the validateInsert() method, so the following code has been added to deal with those circumstances where the rdcaccount_id column exists but does not yet have a value:

    if (strlen($fieldvalue) == 0) {
        // value is empty, but is there a default which can be inserted?
        if ($fieldname == 'rdcaccount_id') {
            // this table is split by account, so insert user's account_id
            if ($_SESSION['rdcaccount_id'] > 1) {
                $fieldvalue = $_SESSION['rdcaccount_id'];
            } elseif ($this->caller->tablename == 'mnu_account') {
                // value will be generated automatically
            } elseif ($this->caller->tablename == 'mnu_user') {
                // value is optional
            } else {
                // default to the shared account
                $fieldvalue = 1;
            } // if
        } elseif (....) {
            ....
        } // if
    } // if

Note the following:

• If the current table is MNU_ACCOUNT an empty value is allowed it will be provided by the database.
• If the current table is MNU_USER an empty value is allowed as the column is optional.
• For all other tables it is automatically set to the user's rdcaccount_id, or '1' if the user does not have one.

Application Requirements

In order to make use of this facility in your application you must do the following:

1. Add the rdcaccount_id column to the relevant database tables, either as part of the primary key, part of a candidate key, or as an index, as shown in Implementation.
2. After importing these tables in the Data Dictionary and before exporting them to the application you must modify all instances of the rdcaccount_id column so that:
   • No Edit/Display = NODISPLAY
   • No Search = NOSEARCH

   This can also be achieved by running the following SQL query:

   UPDATE dict.dict_column SET noedit_nodisplay='NDI', no_search='NSR' WHERE column_id='rdcaccount_id' 
                                                                         AND database_id!='MENU';
   

3. It may also be useful to create a relationship between the MENU.MNU_ACCOUNT table and each application table which contains the rdcaccount_id column. The relationship type must be set to RESTRICTED so that any rdcaccount_id which is currently in use cannot be deleted. You should then export the MENU.MNU_ACCOUNT table from the Data Dictionary so that these relationships become known to the application.
4. You must then create an entry on the MNU_ACCOUNT table for each subscriber account. This can only be done by a System Administrator (a user with shared access) rather than an Account Administrator (a user with account access).
5. After the MNU_ACCOUNT entry has been created it will then be possible to create users within this account. An Account Administrator must first be created by the System Administrator, then the Account Administrator can create as many users as necessary who will automatically be assigned to the same account.

That is all there is to it as the framework will automatically take care of the rest by amending any generated SQL statements as necessary. If you supply any manual SQL statements (which override any automatically generated statements) then you must ensure that these contain the relevant references to the rdcaccount_id column.

© Tony Marston
1st October 2007

http://www.tonymarston.net
http://www.radicore.org