Using Caché Objects
Adding Row-Level Security
[Home] [Back] 
InterSystems: The power behind what matters   
Class Reference   

Adding Row-Level Security
In addition to its general security, Caché offers SQL security with a granularity of a single row. This is called row-level security. With row-level security, each row holds a list of authorized viewers, which can be either users or roles. For more information on users and roles, see the Users and Roles chapters of the Caché Security Administration Guide.
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 to achieve the desired control 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. Creating a separate view for each patient is not a practical alternative; instead, fine-grained access control, in conjunction with the Caché role-based authentication model, enables this type of application to be created efficiently and securely through row-level security.
The following are constraints on the use of row-level security:
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 code, set the value of ROWLEVELSECURITY to 1, such as:
    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:
    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 ]
        QUIT ""
    Its characteristics are:
    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 specify this.
  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 the previous section, Setting Up Row-Level Security.” Then:
  1. Rebuild the indices for the table.
  2. Update the value of the property that lists the users and roles who can view each row.
Rebuilding the Indices
Do not rebuild indices while users are accessing the data for this table. Doing so may result in inaccurate query results.
The procedure to rebuild the indices for a table is:
  1. If the table has any views defined that have the WITH CHECK OPTION clause, remove these views with the DROP VIEW command. (You can re-create these views after updating who has access to each row).
  2. From the Management Portal home page, go to the SQL page (System Explorer > SQL) page.
  3. Select the namespace that contains the table.
  4. Under Tables, select the name of the table. This displays the Catalog Details for the table.
  5. On the Actions drop-down list, click Rebuild Table’s Indices.
For more information on rebuilding indices, see Defining Indices in the chapter Defining and Building Indices in Caché SQL Optimization Guide.
Updating Who Can View Each Row
The procedure to do this is:
  1. From the Management Portal home page, go to the SQL page (System Explorer > SQL) page.
  2. Select the namespace that contains the table.
  3. Click Execute Query.
  4. In the editable area, issue a statement to update the table. It should have the following form:
    UPDATE MySchema.MyClass SET rlsprop = 
                    MySchema.SecurityPolicy(MySQLColumnName1, ...)
  5. Click Execute.
  6. If desired, re-create any view that you initially removed.
Performance Tips and Information
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 and Information
Keep in mind the following security factors when using row-level security: