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.
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:
rdcaccount_id column.rdcaccount_id column with a value which is greater than 1.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:
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.
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.
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.
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.
Using this structure as an example the same value for foo_id could exist with different values of rdcaccount_id.
An attempt to insert to a child table using a non-sharing account will be disallowed with the message: User's account (X) is not compatible with record's account (Y)
. This is because the child's foreign key must always match the parent's primary key, so it is not possible to change the value for rdcaccount_id in the child record.
If rdcaccount_id is not part of the primary key then a value for foo_id could exist only once, but would belong to the account which created it.
If rdcaccount_id is not part of the primary key on a parent table then it cannot be part of the foreign key on any child table. In this case it should not be defined on the child table at all as it has no purpose and could even cause errors. In those cases where the framework constructs a WHERE string from parent record before passing it to the getData() method on the child table it will exclude rdcaccount_id string as it is not part of the primary key, but the read of the child record will fail as the framework will detect that rdcaccount_id exists on the child table but no value has been supplied.
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:
rdcaccount_id column to the MNU_USER table.rdcaccount_id column to the WF_CASE, WF_TOKEN and WF_WORKITEM tables.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'.
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.
rdcaccount_id has Shared Access:
rdcaccount_id = 1.rdcaccount_id > 1 has Account Access:
rdcaccount_id set to his account.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.
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.
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:
$_SESSION['rdcaccount_id'] = NULL then that user will be able to read the data for all accounts on that table.$_SESSION['rdcaccount_id'] = 1 then that user will only be able to read the data for the shared account on that table.$_SESSION['rdcaccount_id'] > 1 then that user will be able to read the data for the shared account and that non-shared account on that table.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
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:
rdcaccount_id, or '1' if the user does not have one.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
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
In order to make use of this facility in your application you must do the following:
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.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';
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.rdcaccount_id is NULL or 1) rather than an Account Administrator (a user with account access where rdcaccount_id > 1).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.
| 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. |