Row-Level Security

Introduction

Typically, SQL security is controlled by granting SELECT privilege on a table or on a view; the privilege is granted to a role or user. In most cases, view-level security provides adequate control over which rows each user can select; however, when the number of views required becomes very large, another alternative for fine-grained access control is needed.

For example, a hospital may make patient-specific data available online to each patient, and creating a separate view for each patient is not desirable. In such a scenario, row-level security is the practical way to secure the data.

The following are constraints on the use of row-level security:

• Row-level security is only available for persistent classes.

• Row-level security is only available for tables on the InterSystems IRIS server. It is not available for linked or foreign tables.

• Row-level security is only enforced when accessing rows from SQL. It is not enforced when directly accessing globals or when accessing globals via the object interface.

To implement row-level security for a table, you add a special field that holds a list of usernames or roles such that only those usernames or roles can access a given row. This special field can be the generated %READERLIST field or it can be a custom field. In either case, the data in this field is for internal use and your code cannot access it.

Setting Up with the %READERLIST Field

For a given table, to enable row-level security using the %READERLIST field, make the following changes to the class from which the table is projected.

1. In the class definition, set the value of the ROWLEVELSECURITY parameter:

   Parameter ROWLEVELSECURITY = 1;

   

   In this case, once you compile the class, the %RLI index is automatically added to the class, although it is not visible in the class definition. This is a a collection index on the %READERLIST property. The query optimizer uses it to minimize the performance impact when row-level security is enabled.

2. If you want to store the values in another property in the same class, then set the parameter equal to the name of that property.

   Parameter ROWLEVELSECURITY = "rlsprop";

   

   Where rlsprop is the name of a property in the same class.

3. If you used a custom property name (that is, ROWLEVELSECURITY is not 1), add an index named %RLI index on the property.

   If you expect to never have more than one username or role in this property, then this index can be an ordinary (non-collection) bitmap index. This generally provides optimal performance.

4. In the same class, also define a %SecurityPolicy() class method, which specifies the role and usernames that are permitted to select the row, subject to view and table SELECT privileges.

   This class method must return an empty string, a username, a role, or a comma-separated list of usernames or roles. If the method returns an empty string, there is no specific restriction for this row; the row is visible to all users who hold the SELECT privilege on the table.

   Important:

   The %All role does not automatically have access to rows in a table that are protected with row-level security. If %All is to have access to such a row, the %SecurityPolicy() method must explicitly return the %All role, along with others as needed.

   The structure of the %SecurityPolicy() method is:

   ClassMethod %SecurityPolicy() As %String [ SqlProc ]
   {
       RETURN ""
   }
   

   

   Its characteristics are:

   • It is a class method with the required name %SecurityPolicy.

   • It returns a string (type %String).

   • It takes zero or more arguments. If this method takes any arguments, each argument name must match a property name in the class. If any property names do not exactly match their SQL field names, instead use the SQL field names as argument names.

   • The SqlProc keyword specifies that the method can be invoked as a stored procedure.

5. Compile the class and any dependent classes.

When you compile this class, two changes are made to it automatically (but are not visible in the class definition):

• It has a new property named %READERLIST, which is a calculated field. The value of this field is determined by the %SecurityPolicy() method. Whenever an INSERT or UPDATE occurs, %SecurityPolicy() is invoked for that row and populates the value of %READERLIST.

• It has an index named %RLI index, which is a collection index on the %READERLIST property.

Setting Up with a Custom Field

For a given table, to enable row-level security using a custom field, make the following changes to the class from which the table is projected.

1. Identify a property in the same class that contains data that can be used for row-level security. This property can be an empty string or can contain a role, a username, or a comma-separated list of roles or usernames. If the property is an empty string, there is no specific restriction for this row; the row is visible to all users who hold the SELECT privilege on the table.

2. Add a suitable index (with any index name) to this class for this property. If you expect to never have more than one username or role in this property, then this can be an ordinary (non-collection) bitmap index. This generally provides optimal performance.

3. In the class definition, set the value of the ROWLEVELSECURITY parameter:

   Parameter ROWLEVELSECURITY = "rlsprop";

   

   Where rlsprop is the name of the property identified in step 1.

4. Compile the class and any dependent classes.

After Adding Row-Level Security to a Table with Existing Data

If you have added row-level security to a table that already contained data, you need to make updates to account for the fact that you have added a new index (as well as possibly a new field) to the table:

1. Rebuild the indexes for the table.

2. Update the value of the property that lists the users and roles who can view each row.

Security Tips

Keep in mind the following security factors when using row-level security:

• Row-level security operates in addition to table-level security. To execute a SELECT, INSERT, UPDATE, or DELETE statement, a user must have been granted both table-level access and row-level access for the relevant row.

• User privileges are checked dynamically at runtime — when there is an attempt to execute an SQL command.

• If you create an updateable view and specify WITH CHECK OPTION, then an INSERT operation on that view checks if the row to be inserted passes the WHERE clause that is specified in the view. Further, if you are creating the view from a table with row-level security, then the INSERT fails if either the WHERE clause of the view or the implicit row-level security predicate would cause that row to not be visible if you were to issue a command of SELECT * FROM on the view.

• If you have access to a row, it is possible to change the value of the %READERLIST field (or whatever field holds the list of users and roles who can view the row). This means that you can perform an action that, directly or indirectly, removes your access to the row.

• If you attempt to insert a row that would have violated a UNIQUE constraint if row-level security had not been defined, then it will still violate the constraint if row-level security is defined, regardless of whether the row causing the constraint failure is visible to the updating transaction.

See Also

• Users and Roles

• Defining Indexes