Row-Level Security

Introduction

Typically, SQL security is controlled by granting SELECT privilege on a table or view to a user or role. The use of roles simplifies access control when the number of security roles is substantially fewer than the number of users. 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 practical.

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.

Setting Up Row-Level Security

To enable row-level security for a table, edit the definition of the class from which the table is projected.

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

   Parameter ROWLEVELSECURITY = 1;

   

   This definition for the parameter means that row-level security is active and that the class uses the generated %READERLIST property to store information about users and roles with authorized access to the row.

   Alternatively, you can define the parameter as follows:

   Parameter ROWLEVELSECURITY = rlsprop;
   

   

   Where rlsprop is the name of a property in the same class. This alternative means that row-level security is active and that the class uses the given property to store information about users and roles with authorized access to the row. In this case, also add an index to the class as follows:

   Index %RLI On rlsprop;

   

2. Define a %SecurityPolicy() class method, which determines and specifies the role and usernames that are permitted to select the row, subject to view and table SELECT privileges.

   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 must match a property name in the class.

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

   • The RETURN statement of the method returns the users or roles that may view the row. If there is more than one user or role, RETURN must return a comma-separated list of their names. Returning the null string (as in the example) specifies that the row is visible to all users who hold the SELECT privilege on the table.

   Important:

   A user who is assigned to 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.

3. Compile the class and any dependent classes.

Adding Row-Level Security to a Table with Existing Data

To add row-level security to a table with existing data, first follow the procedure described in Setting Up Row-Level Security. Then:

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.

Performance Tips

The %READERLIST property is a calculated field and its value is determined by the %SecurityPolicy() method. Whenever an INSERT or UPDATE occurs, %SecurityPolicy() is invoked for that row and populates the value of %READERLIST.

A collection index on the %READERLIST property is defined, and can be exploited by the query optimizer to minimize the performance impact when row-level security is enabled.

By default, when you set ROWLEVELSECURITY equal to 1, a collection index is defined for the %READERLIST property (column) because the security policy can, in general, return more than one comma-separated role or username. If your security policy never returns more than one user or role name, then you can override the ROWLEVELSECURITY parameter and explicitly define the %RLI index as an ordinary (non-collection) bitmap index. This generally provides optimal performance.

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