Tony Marston's Blog About software development, PHP and OOP

RADICORE for PHP - Implementing Virtual Private Databases

Posted on 1st October 2007 by Tony Marston

Amended on 1st April 2016

Introduction
Implementation
- To add or not to add RDCACCOUNT_ID to a unique key
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
7. Alter the code for database updates
8. Alter the code for database deletes
Application Requirements
Amendment History
Comments

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). This principle may also be referred to as Multi-Tenancy where each "tenant" has his own customer/subscriber account.

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:

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.

To add or not to add RDCACCOUNT_ID to a unique key

When adding the rdcaccount_id column to a table you have the option to make it part of the primary key or to keep it separate, so what are the differences? The major implication is if the table is a parent in a parent-to-child (one-to-many) relationship and the parent table contains records with rdcaccount_id = 1 (the shared account) and you attempt to add records to the child table with a non-sharing account.

Framework Changes

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

  1. Create a new table to hold subscriber account details (MNU_ACCOUNT).
  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.
  7. Alter the code for database updates.
  8. Alter the code for database deletes.

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.

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 may be necessary to ensure that the WHERE clause contains a reference to this column. This is achieved by adding the following code to the _sqlAssembleWhere() method:

    if (isset($this->fieldspec['rdcaccount_id'])) {
        if (!empty($where_array['rdcaccount_id'])) {
            // value has already been supplied, so continue
        } else {
            $account_id = $_SESSION['rdcaccount_id'];
            if (empty($account_id)) {
                $account_id_string = null;  // read all accounts
            } elseif ($account_id == 1) {
                // read only the shared account
                $account_id_string = "$this->tablename.rdcaccount_id='1'";
            } else {
                // read the user's account and the shared account
                $account_id_string = "$this->tablename.rdcaccount_id IN ('1', '$account_id')";
            } // if
            if (!empty($account_id_string)) {
                if (empty($this->sql_search)) {
                    $this->sql_search = $account_id_string;
                } else {
                    if (substr_count($this->sql_search, $account_id_string) == 0) {
                        $this->sql_search .= " AND $account_id_string";
                    } // if
                } // if
            } // if
        } // if
    } // if

Note the following:

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 = RDCsingleton::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 every insert a check is made inside the getInitialData() method to check that the record's foreign key is compatible with the user's account:

if (isset($this->fieldspec['rdcaccount_id'])) {
    $account_id = $_SESSION['rdcaccount_id'];
    if (empty($account_id)) {
        $account_id = 1;  // change from NULL to the shared account
    } // if
    if (isset($fieldarray['rdcaccount_id']) AND $fieldarray['rdcaccount_id'] != $account_id) {
        // "User's account (X) is not compatible with record's account (Y)"
        $this->errors['rdcaccount_id'] = getLanguageText('sys0232', $account_id, $fieldarray['rdcaccount_id']);
        return $fieldarray;
    } else {
        // always use this user's account_id
        $fieldarray['rdcaccount_id'] = $account_id;
    } // if
} // if

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:

7. Alter the code for database updates

Users who belong to a private account (where rdcaccount_id > 1) can only update records which belong to the same account, so cannot update a shared record (where rdcaccount_id = 1). This is achieved with code similar to the following in the updateRecord() method:

    if (isset($this->fieldspec['rdcaccount_id'])) {
        $account_id = $_SESSION['rdcaccount_id'];
        if (empty($account_id) OR $account_id == 1) {
            if ($fieldarray['rdcaccount_id'] == 1) {
                // this user can update a record in the shared account
            } elseif ($fieldarray['rdcaccount_id'] > 1) {
                // "Record belongs to a non-shared account, so can only be modified by a user in the same account"
                $this->errors[] = getLanguageText('sys0235');
            } // if
        } elseif ($account_id > 1) {
            if ($fieldarray['rdcaccount_id'] == 1) {
                // "Cannot update a record in the shared account"
                $this->errors[] = getLanguageText('sys0189');
            } elseif ($fieldarray['rdcaccount_id'] != $account_id) {
                // "Record belongs to a non-shared account, so can only be modified by a user in the same account"
                $this->errors[] = getLanguageText('sys0235');
            } // if
        } // if
    } // if

8. Alter the code for database deletes

Users who belong to a private account (where rdcaccount_id > 1) can only delete records which belong to the same account, so cannot delete a shared record (where rdcaccount_id = 1). This is achieved with code similar to the following in the validateDelete() method:

    if (isset($this->fieldspec['rdcaccount_id'])) {
        if (!empty($_SESSION['rdcaccount_id'])) {
            if ($fieldarray['rdcaccount_id'] != $_SESSION['rdcaccount_id']) {
                // not allowed to delete a shared record
                $this->errors['rdcaccount_id'] = getLanguageText('sys0188');
            } // if
        } // if
    } // if

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:

    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 where rdcaccount_id is NULL or 1) rather than an Account Administrator (a user with account access where rdcaccount_id > 1).
  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.


Amendment history:

01 Apr 2016 Amended the MNU_USER table, reading from the database and Application Requirements so that users with rdcaccount_id = NULL have read access to all data, but write access only to data where rdcaccount_id = 1 (the shared account).
05 Jun 2014 Amended Alter the code for inserting into the database.
Added To add or not to add RDCACCOUNT_ID to a unique key
01 Aug 2009 Added Alter the code for database updates and Alter the code for database deletes.

counter