InterSystems IRIS provides extensive configurable security options, yet many developers primarily use roles and resources to secure entire tables or routines. Today, we will delve deeper. We can also secure individual columns and rows separately, but these two mechanisms operate very differently. Let's begin with the columns.

Column Security

For testing and demonstration, we will keep our table structure concise and straightforward. We have a table called "Person" in the USER namespace that contains an ID column, a date of birth column (DOB), first name, and last name. 

Class User.Person Extends %Persistent
{
    Property FirstName As %String;
    Property LastName As %String;
    Property DOB As %Date;
    Property User As %String;
}

We will create a role called limited_access which will implement some column security, but the first thing we must remember to do is make sure to add the %DB_User resource to this role so that the user has access to the database. Once we’ve done that, we can begin to think about columns. We will go to the SQL Tables tab in the role setup and ensure that the USER namespace is selected. Then we will click the Add Columns button, which is to the right of the Add Tables button. This will open the dialog where we will control column permissions.

This interface closely resembles the screen used for adding permissions for tables. The notable exclusion is the DELETE option, which cannot be controlled at the column level. It makes sense since we cannot delete individual columns from a record in a table. However, we retain control over the SELECT, INSERT, UPDATE, and REFERENCES privileges. We can also use the Grant Admin checkboxes to permit users with this role to delegate the same privilege to other users or roles. For today's example, we will grant the role all permissions for the ID, FirstName, and LastName columns. Yet, we will assign only the SELECT privilege for the DOB column. After making these changes, we will save the role. You will then observe that the entry for this table in the role configuration displays a hyphen (-) under the privileges column. Yet, it includes an Edit Column option.

If we click the Edit Columns link on the right, we can review the exact permissions defined for each column individually.

This is where we can modify or revoke specific column permissions. If we check the Add Columns box, the initial configuration form for adding columns will reappear below, allowing us to make further additions. If you ever encounter a table listed in a role that appears to lack defined privileges, you should click the Edit Columns link to check for column-specific permissions.

Io test this configuration, we need to set up a user with this role. We will name this user testuser. It will possess our limited_access role, and we will also grant it the %Developer role to access the SQL portion of the Management Portal and execute some queries. For example, consider the following SQL query.

INSERT INTO Person(DOB,FirstName,LastName) VALUES(%ODBCIN('1900-09-03'),'David','Hockenbroch')

If we execute this query while logged in as a superuser with %All privileges, the query will be successful. However, if we log out of the Management Portal and sign back in as our test user, an error will occur.

The query has failed because the user lacks permission to insert into the DOB column. If we adjust the query to omit the restricted column, it will succeed. The following query will still work for this user:

INSERT INTO Person(FirstName,LastName) VALUES('David','Hockenbroch')

Similarly, if the user did not have permission to update or select a column, any query violating those restrictions would fail. For instance, if I removed the DOB column entirely from the column privileges, I would be unable to execute the SELECT * FROM Person command. Instead, I would be required to use SELECT ID, FirstName, LastName FROM Person. Note that if there are computed columns that a user lacks access to, the calculations will still be executed appropriately.

If we slightly modify our class definition, we can introduce a whole new issue. Suppose we decided to change our class definition to include the Required keyword in the DOB property. If we then attempt to run the query stated above, it will cause an error instead of succeeding.

You must keep this in mind when establishing column privileges: if you deny a user INSERT permission for a certain column that is not automatically assigned via compute code, an initial expression, or a similar method, the user will be unable to insert any rows at all!

Row-Level Security

Now we will explore row-level security. Our objective will be to ensure that only the user who created a row can access it.

Row-level security functions very differently from column security. We cannot configure it through the Management Portal. Instead, we must modify our class definition. First, we should add a property:

Property Creator As %String [ SqlComputeCode = {set {*} = $USERNAME}, SqlComputed, SqlComputeOnChange = %%INSERT];

This is an SQL computed property that automatically sets its value to the current username when a row is inserted. We will insert one row per user. Then we should observe the following records:

Next, we will need to override the ROWLEVELSECURITY parameter in the class definition. Setting it to 1 will automatically direct IRIS to store the reader list in a property named %READERLIST. However, in our case, since we created a property to store the username that should have access to the row, we will override this parameter with the name of that column (Creator). Remember that since row-level security can be handled by role or username, we could use a column containing a role name instead if desired.

If we attempt to select any data from this table at this point, the query will return empty. This happens because we have added row-level security to an existing table, and row-level security relies on building an index on the field being used for security. We must now rebuild the table’s indices. We will accomplish this through the Management Portal. In the SQL area, we will select the table on the left, then click Actions, and finally click Rebuild Table’s Indices.

Now that the appropriate index is present, when users select from the table, they will see only their own rows. It is essential to recognize that this restriction applies even to superusers! In most security contexts within IRIS, having the %All role grants access to virtually everything, but row-level security is a notable exception. Even if we run a DELETE * FROM Person query as a superuser, it will only eliminate the rows that the superuser is authorized to access.

We could achieve the same outcome in an alternative way. This time, let’s set the ROWLEVELSECURITY parameter to 1, but add a %SecurityPolicy method to our class.

ClassMethod %SecurityPolicy(Creator) As %String [ SqlProc ]
{
    return Creator
}

This method is defined as a class method that returns a string and uses the SqlProc keyword. It can accept any number of arguments, but they must be column names from the class itself, and the names of the arguments must precisely match the column names. In this case, since we are working with a column called "Creator", the method argument must also be named "Creator". We simply return that column's value to fulfill our goal here. This method can be as intricate as your needs demand, provided it ultimately returns a username or role name. It can also return a comma-separated list of names if necessary.

When the ROWLEVELSECURITY parameter is set to 1, the property %READERLIST is utilized to store who is authorized to access the row. If you wish to view this list in a query, you can execute something similar to the following.

SELECT %READERLIST, * FROM Person

Row-level security is enforced in addition to table security. A user cannot access, update, or delete a row unless they have permission to do so for both the table and the row. Importantly, row-level security is only applied when using SQL, not when employing object access or directly manipulating globals. Be extremely cautious if you access your data in a way that bypasses this security layer!

With the careful implementation of these two powerful tools, you can substantially elevate your data security!