Row level security

Some organisations have a need to restrict access to data at the level of the rows in the database. For instance a common scenario is that compounds are assigned to a particular project and only people belonging to that particular project should be allowed to see those compounds. So when a user is looking at compounds in the database they should only be able to see the compounds assigned to the project(s) he/she belongs to. The other compounds are in the database but are invisible to the user.

The main problem to overcome is that every individual scenario will be different, as each organisation will have its own requirements and arrangement of user and project information, or may have completely different needs.

The approach described here, is based on mechanisms implemented on the Instant JChem level. If you are looking for a solution based on the database level, please see here.

Adding a SQL Filter

There is a mechanism for achieving this by allowing a custom row filter for any IJC Entity to be defined as a SQL fragment that will be appended to all SELECT statements (e.g. query results). To do this:

  1. Open the Schema Editor and find the Entity that you want to restrict access to.

  2. Go to the 'Extra Attributes' tab and click on the 'Add' button. The Extra attributes editor will open.

  3. Select the 'SQL Filter' property, enter the SQL filter you need and click the 'Add button'.

  4. Click the 'Apply' button in the Entity editor to apply the changes.

  5. Disconnect from the schema and reconnect so that you can test the results.

images/download/attachments/1805554/EdittingSQLlevel.png

The process for editing an existing filter is almost identical. Just select the 'SQL Filter' property in the 'Extra Attributes' tab and click 'Edit'. Similarly to remove a filter click 'Remove'.

Simple example

So how do you know what filter to enter? Of course the exact filter will vary in each case, but some guiding principles and an example will help you work out what is needed. As an example we will start with a very simple filter that restricts the compounds you can see in the Pubchem Demo sample data (included in the sample project in IJC) to those with a molecular weight of less than 500. Of course your filter will be more complex and we cover that below, but this simple example lets you experiment with SQL Filters using the sample data in IJC to get experience.

Data is retrieved from the database using a SQL SELECT statement that might also include a query (e.g. Mol Formula starts with 'C12'). This would look something like this as a SQL filter (structure queries don't operate quite like this, but the principle is the same and the SQL filter is also applied to structure searches for JChemBase and JChem cartridge tables):

SELECT cd_id FROM pubchem_demo WHERE cd_formula like 'C12%'

When you specify a SQL Filter you should think of it being applied like this:

SELECT cd_id FROM pubchem_demo WHERE cd_formula like 'C12%' AND __SQL_FILTER__

where

__SQL_FILTER__

is replaced by your specified SQL filter. In our example we want rows where the molweight is less than 500 so our SQL filter (what you enter into the Extra attributes editor) need to look like this:

cd_molweight < 500

so when substituted into the full SELECT statement will look like this:

SELECT cd_id FROM pubchem_demo WHERE cd_formula like C12% AND cd_molweight < 500

We suggest you try this with the PubChem demo data to get started on using this feature. If you do so, when you open the Pubchem grid view you will only see the 863 compounds whose mol weight is less than 500.

Specifying the IJC username

In a real world example you will almost certainly want to be able to specify the IJC username of the current user. You do this by using the _IJC_USERNAME_ (that's two underscores at each end) value in your SQL filter and the current user's username will be substituted into the SQL Filter before it is added to the SELECT statement. The username is the username the user logs in with and values will be found in the USERNAME column of the IJC_USER table.

So let's extend our example and assume that the username is also stored in the data table (e.g. lets assume that the same pubchem_demo table now has an extra column with the username of the user who entered the compound. The SQL Filter would now look like this:

username = '__IJC_USERNAME__'

note the quotes around the

__IJC_USERNAME__

Including data from other tables

Of course in a real scenario you will not just be using data from a single table, and your select statement will need to use data from other tables. For instance, lets assume that your compounds table had a column (proj) that defined the project id that the compound belonged to and you had a project table that defined your projects and a user_project table that asociated each user to one or more projects. In this case your SQL Filter would look something like this:

proj IS NULL or proj IN
(SELECT project_id FROM user_project
WHERE user_project.username = '__IJC_USERNAME__')

Note the check for proj being null (we assume that not all compounds are assigned to projects and we want all users to see those). Also note the use of a sub-select as part of filter. Your 'real world' scenario will of course be different, and may well be more complex, but the same principles apply. You are recommended to test this using a SQL editor to establish the correct filter before applying it to IJC. Its up to you to work out what is the right filter for your particular needs.

When applying row level filters to more than one table in a schema and in particular if those tables are linked by a relationship then one must be aware to use fully qualified SQL statements, else it is possible to encounter an error. Below are examples of a SQL filter statements for a parent and child table found in the demo data of IJC:

WOMBAT.proj IS NULL or WOMBAT.proj IN
(SELECT p.proj
FROM PROJECTS p WHERE p.Project_name
IN (SELECT up.Project
FROM USER_PROJECTS up
WHERE up.user_name = '__IJC_USERNAME__'))
WOMBAT_ACT_LIST.proj IS NULL or WOMBAT_ACT_LIST.proj IN
(SELECT p.proj
FROM PROJECTS p WHERE p.Project_name
IN (SELECT up.Project
FROM USER_PROJECTS up
WHERE up.user_name = '__IJC_USERNAME__'))

We welcome feedback on this feature. Please provide feedback via the Instant JChem forum .