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:

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:

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.

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.

Part of the primary key

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.

Not part of the primary key

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.

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 the user has $_SESSION['rdcaccount_id'] = NULL then that user will be able to read the data for all accounts on that table.

If the user has $_SESSION['rdcaccount_id'] = 1 then that user will only be able to read the data for the shared account on that table.

If the user has $_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

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:

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:

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.

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';

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.

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).

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.