Record Level Security for Entity Backed Records

Record level security allows you to control who can and cannot access individual records. For example, in a list of medical records, each doctor should only be able to view records that belong to their own patients. 

Security will be configured differently based on the record source (entity, process, service backed, etc.), and whether the record is synced or unsynced.

Record Level Security for SYNCED Records

If data sync has been enabled on your records, then security can be configured directly on the record. Security can be inherited from related record types, so security can be maintained in a single place and configured via guided experience.

Security can be based on a users’ groups, whether their username is aligned to a particular record field, or whether the user has access to related records.

Download a working version of this example.

In the example application, there is a record type which contains "cases" with sensitive data that should only be accessible to specific users. For example, a specific analyst may have access to cases #250 and #256, but not case #475. To accomplish this, the following are required:

Synced Record Security Implementation

Data fabric makes it easy to set up record security. In this example, we will have a synced Case record and synced CaseUserAssociation record. These will be related to one another by the foreign key - caseId.

Now, you can leverage the data in the related record to properly secure the Case record data.

In the “Record-Level Security” section, you will need two rules:

The first rule enables all users in the Case Global Viewers group to see all Cases.

The second rule says that users whose name is found in the related record field “username” will have access to see those Cases.

Once you have configured both rules, and you have properly created test users and added them to the correct groups, you can use the testing tool to ensure the Case records have been properly secured.

Unsynced Record Security Implementation

Use the design listed below in this document if your record security use case fits both criteria below:

• The record is not a process backed record (use process instance security for process backed records)
• Different users have access to different records within the same record type (to control access to the record type as a whole, configure security for the record type)

The solution in this document particularly suits, but is not limited to, the following situations:

• Users could potentially have access to more than 100 records
• The record type data could exceed 10,000 rows
• Some users (like managers or administrators) have access to all records while other users have access to only certain records

Design Description:

1. A new table, CaseUserAssociation, which stores a mapping of Appian usernames to Case IDs.
2. A database view joining the Case table and the CaseUserAssociation table which is used as the source of data for the record. The database view contains multiple rows for each case, each storing a username of the user who can access the record row.
3. A default filter that compares the usernames to the currently logged in user to return the right list of cases.

Managing Global Viewers

There is a common use case in which some users need to have access to all records. Explicitly adding these users to each record in the mapping table is potentially cumbersome and hurts performance. Instead, use this strategy:

1. Update the default filter expression to check whether the logged in user is a member of the global viewer's group (e.g. Case Global Viewers) 
2. If so, create a blank a!queryfilter() or a!queryLogicalExpression() instead of the logged in user's actual username.
3. If not, create a a!queryfilter() or a!queryLogicalExpression() to equal the logged in user's actual username.
4. Since there is no filter if the user is a member of the global viewers group, this results in all cases being returned.

This design is used in the example application.

Managing Records and User Access

• Adding and removing user access
  • Each time a user is given access to an individual case, i.e. record row, the CaseUserAssociation table should have a new row storing that record row primary key ID and the user’s username.
  • To remove a user’s access to a case, remove the mapping row from CaseUserAssociation that contains the Case ID and that user’s username.
  • Adding and removing user access can be accomplished using related actions or as part of an independent process.
• Adding and removing records
  • When removing a record all related rows (e.g. all rows with the referenced Case ID) should be removed from the association table including the global viewers row (if present).

Example Application Instructions

1. Download the following files (supported in Appian 7.7+):
   • View_Based_Record_Level_Security_DDL.sql
   • View_Based_Record_Level_Security_Sample_Data.sql
   • View_Based_Record_Level_Security.zip
2. Run the SQL scripts in View_Based_Record_Level_Security_DDL.sql in a MySQL database. The script may require slight alterations for other database vendors.
3. Run the data load SQL scripts in View_Based_Record_Level_Security_Sample_Data.sql in a MySQL database. The script may require slight alterations for other database vendors.
4. In Appian, import the application View_Based_Record_Level_Security.zip.
5. Select the Cases data store and set the data source to be the correct JNDI name for your environment’s database. Verify and publish the data store.
6. Create users with the following usernames and add them to group Case All Business Users:
   • testUser1
   • testUser2
   • testUser3
7. Create testUser4 to be a global viewer and add the user to the group Case Global Viewers.
8. Log in as any of the four users listed in steps 6 or 7 and view the Cases record type. Note that the users only have access based on the mapping from table CaseUserAssociation.