Click or drag to resize
Row-Level Security

This topic contains the following sections:

This document describes the Row-Level Security in PACKflow.

Overview

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.

Database & Data Access Layer Architecture

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.

RLS Schemas Overview
User experience

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.

Id

Title

RLS - READ

RLS - UPDATE

RLS - DELETE

1

Berlin

Users: 'Jack', 'SystemAdmin'

Groups: 'CustomGroup1'

Users: 'Jack', 'SystemAdmin'

Users: 'SystemAdmin';

2

Rome

Users: 'SystemAdmin'

Groups: 'CustomGroup2'

Users: 'SystemAdmin'

Users: 'SystemAdmin'

3

Brussels

Users: 'SystemAdmin'

Groups: 'CustomGroup1'

Users: 'SystemAdmin'

Users: 'SystemAdmin'

4

Paris

Users: -

Groups: -

Users: 'Jack', 'SystemAdmin'

Users: 'Jack', 'SystemAdmin'

Note 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.

Id

Title

RLS - UPDATE

RLS - DELETE

1

Berlin

AuthorizedDenied

4

Paris

AuthorizedAuthorized

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

Id

Title

RLS - UPDATE

RLS - DELETE

1

Berlin

DeniedDenied

3

Brussels

DeniedDenied

4

Paris

DeniedDenied
Note 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 operation through permissions

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

EveryonePF_CreatedBy property value PF_CreatedBy property value -
User notifications

PFUserNotification

EveryoneParentUser property value ParentUser property value ParentUser property value
Object Locks

PFObjectLock

EveryoneEveryonePF_User property value PF_User property value
Note 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.
RLS Confidential Survey Workflow

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

RLS Confidential Survey Fields

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

PermissionGlobalstate: To be investigatedstate: To be approvedstate: Publishedstate: Rejected
Create Members of 'Directors' group * NANANANA
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 note 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.

See Also

Reference

Role
PFField_User

Other Resources