Row-Level Security

Row level security is a data protection practice where every row in database may have dedicated CRUD restrictions. Microsoft SQL Server prior to 2016 didn't feature RLS natively.

Packflow implements RLS protection on every persisted PFBaseObject. This system works with any version of Microst SQL Server from 2008 R2 to 2016.

Packflow automatically picks users and groups to fill CRUD columns during save operations. The principal set is calculated using different sources, like fields or system groups, and is open to customization. See this section for further details: RLS Permissions.

In a Packflow Database, data and system tables exist in the 'DBADMIN' schema.

RLS protection of these tables is implemented within the 'DBUSER' schema. It provides protected views, functions and procedures used by the data access layer for CRUD operations. Members of this schema cannot execute arbitrary SQL on the database.

Public site application pool user belongs to DBUSER only. This provides great security on the public Packflow site, which is made completely immune to SQL injection.

DBADMIN schema is normally used by the web administration app pool for database ensuring. It might be used by the Timer Jobs engine, Ie. to generate reports based on raw SQL queries.

One visible impact of RLS is the pre-filtering of data retrieved by Packflow queries. This applies for every request on Packflow data access layer, from standard GUI operations to deployed custom codes.

Here is an example of how it works. The table below represents a set of persisted objects in a PACkflow database, along with their respective CRUD users and groups previously computed by the engine.

Note
An empty RLS principal set means everyone has the right to perform the target operation.

Jack retrieves Berlin and Paris when selecting data on this content. He may edit them, and has the right to delete Paris.

CustomGroup1 members see Berlin, Brussels and Paris. They can't edit or delete any of these items.

Note
SystemAdmin is automatically added to every RLS column with principals. Codes within a PFRunAsAdmin call will use this principal to query the Database. As a system account, it is protected from direct login.

RLS permissions are automatically calculated within Packflow. The engine uses principal sources like properties or fields on the target object, or specific groups and system users. These sources' runtime values are used to fill RLS principal sets, according to predefined rules.

Objects like Views or User notifications use simple rules to compute the CRUD principals. More customizable objects like Content-Types' PFItem may have very granular, workflow-sensitive permissions customized in the application model.

Simple examples

Here are some examples of Packflow objects explaining how CRUD permissions are computed.

• Private Views

  Private views are expected to be seen only by their creator. He also must be able to edit them. The engine uses the PF_CreatedBy user field on the PFView instance to grant the view creator these rights.

• User notifications

  While anyone may create user notifications, only the target user may see, update or delete his own notifications. The engine uses the ParentUser property on the notification instance representing the owner user to set this behavior.

• Object Locks

  Objects locks are set by users to flag items they lock for edition.

  Everyone can see the locks for information purposes, but only the lock owner can update or delete it.

Object type	Create	Read principal source	Update principal source	Delete principal source
Private Views PFView	Everyone	PF_CreatedBy property value	PF_CreatedBy property value	-
User notifications PFUserNotification	Everyone	ParentUser property value	ParentUser property value	ParentUser property value
Object Locks PFObjectLock	Everyone	Everyone	PF_User property value	PF_User property value

Note
SystemAccount is ommited there for simplicity. It is automatically added to every column not set to 'Everyone'

Permissions customization in content-types

On each content-type, RLS permissions can be customized with model-defined sources. Permissions can be set globally and by state.

Let's take a simple use case as an exemple. An organisation has to make surveys on different topics, with the following requirements:

• Directors only may create a survey and name a single supervisor for it.
• A dedicated expert team completes the requested surveys.
• Once survey complete, the supervisor chooses to publish or reject it.
• If published, survey is made visible to everyone in the organisation.

The content-type itself has minimal data: Title, Conclusions, and Approver user field. It has four possible states, as required in the state diagram above

Here is how this content-type behavior can be modeled in Packflow:

Permission	Global	state: To be investigated	state: To be approved	state: Published	state: Rejected
Create	Members of 'Directors' group *	NA	NA	NA	NA
Read	Members of 'Directors' and 'Experts' groups * 'Approver' field value	-	-	Everyone	-
Update	-	'PF_CreatedBy' field value Members of 'Experts' group *	'Approver' field value	-	-
Delete	Members of 'Directors' group *	-	-	-	-

Important
* Groups are not directly modeled in Packflow. Groups are instanciated runtime from modeled roles, for each application context in the application. See this topic for more details. Groups, Roles and Application Contexts

When an item is updated, the engine computes the RLS permissions based on the 'Global' + state-specific modeled permissions.

Default visibility is set to 'Directors', 'Experts' groups and 'Approver' field value.

As can be seen above, during 'Investigation' phase, only Experts and Directors can modify a survey.

Once in approval phase, only the 'Approver' can edit it.

When published, it can't be updated anymore, and everyone can see it.

When rejected, it can't be updated anymore.

Reference

Other Resources