How to organize entity-level, row-level and state-based security in a work-flow-alike scenario. (part 1)


Let’s start from the following real-live enterprise scenario.

Image, I’m builing an app for managing purchase requests. I have an PurchaseOrder which is initially requested by a company employee. Before being sent eventually to the vendor, the purchase order first needs to be approved by several other parties (the direct management of the requestor, some advising parties, a procurement officer, the board of directors, etc. …)  in the company.  All parties involved will enrich the purchase order with additional information in such a way we can speak of some kind of workflow. From a security perspective, the data which is enriching the original purchase order may not be edited by the original requestor, or may even not be seen by the original requestor of other “enriching parties”.

How can we make this doable in LightSwitch and how can we make this really secure.

The first part only covers the table-level security. The row-level security and the state-driven workflow/security aspects are covered in  later episodes.

The scenario for a small proof of concept

Data involved

Let’s stick, for the small proof of concept w’ll working out here, to the scenario where we have a purchase request which is handed in by a “requestor” en enriched by a procurement officer and finally enriched, approved or rejected by the board of directors.

So, we have 3 parties, all enriching the Purchase request entity. Let’s start with a “naive” representation of this:

In order to make the data model not too heavy, I’m using the field SeveralOtherFieldsOfProcurementOfficer and SeveralOtherFieldsBoardOfDirectors as a “placeholder” for a whole bunch of fields. This keeps our example manageable.

The State property makes that we speak of a kind of workflow:

  • when the requester finished her request, she can put to state to “ready for PO validation“, in such a way the request can be picked up by the Procurement Officer (PO). The requestor can also leave the request in state “Draft“. As long as it is in Draft mode, other parties can’t see the request.
  • The Procurement Officer can validate the request and put in state “ready for board of directors approval” or can put it back in state “Draft”.
  • Finally, the board of directors can approve or decline the request.

The requestor has following rights with respect to the purchase order:

  • creating a purchase order
  • may not see (nor edit)  the fields added by the procurement officer
  • may read (but not edit) the fields added by the procurement officer.
The Procurement officer has following rights:
  • may view (but not edit) the original purchase request from the requester
  • may create/update/read the procurement specific fields
  • may read (but not edit), the specific fields that the board of directors will add
The board of directors has following rights:
  • may view (but not edit) the original purchase request from the requester
  • may view (but not edit) the specific fields from the procurement officer
  • may edit/read the specific fields that the board of directors add.
It’s clear that we will end up with 3 roles in the application, which is handled inside the application with 3 permissions on the request entity. Furthermore the notion of the request state is also deeply involved in the application security.

Why is the above “naive” datamodel not workable

There are several reasons why the above datamodel will sooner are later give trouble:

  • the request entity is never created as a whole: the requester provides only a subset of fields. The trouble is that the requester will not be able to save the record because there are mandatory fields which can, given our requirements, not be provided at requester-create-time. Ok, we could make the PO and board of director specific fields optional, but that would make our data model weaker and would require to implement the mandatory field logic in code, rather than on data level.
  • How will be enable the security? Since everything is in one table, we can only leverage field level security and even only in a partial way. We could use the _isReadOnly method to ensure that given a users role and request state, certain fields are read-only, but it will not be possible in an easy way to make sure that certain fields can not be read and that is is ensured in a secure way.
  • Please note that hiding fields client side has nothing to do with security. Security happens server side, where you have to protect your assets. Making controls read-only or invisible client side has more to do with cosmetics and application comfort but nothing with data security.
  • Even if we could elaborate a way to implement this field-level security, it would be very tedious to maintain this application because adding a new field (due to evolving requirements) would mean going through the application logic and add the logic for the newly added field.
It’s clear that we want to have the security implemented on the right level of granularity: table-level.

What type of UI do we have in mind?

The tabs layout, is the most applicable way that LightSwitch offers out-of-the-box for handling the above scenario.

The generic purchase request fields are in the upper part of the screen. The lower part contains the tabs with the PO and board of directors specific fields. When our server side logic is in place, the tabs can be very elegantly shown or hidden to ensure further user comfort.

Towards a more normalized data model.

The following datamodel is closer to our goal of a more manageable security implementation. Since the procurement officer and the board of directors data related to a purchase request, should be secured differently they are taken out of the purchase request.

These sub entities get a 0..1 to 1 relationship with the purchase request entity. This means that they can exist and if they exist only one instance can exist. That fits perfectly our goal.

Since the Procurement Officer and the board of directors fields are isolated now in separate tables, we can manage in a much better way the security.

Some Definitions

Table level security

The subject of this first part on lightswitch security. Entity level security handles the authorization aspects of a table in isolation, so only related to a related permission. In LightSwitch this type of authorization security is handled in the domain model by following access control methods:

Since the information carried in these methods is on domain level, the client can re-use this information, by automatically enabling/disabling buttons related to these CRUD (Create, read, update and delete) methods. This type of security is coarse-grained.

Table level security is involed in both the query (CanRead) and the save pipeline  (CanDelete, CanUpdate, CanInsert).

Row level security

Row level security is only involved in the query pipeline. It will tell you which records (which rows) a user can see depending on her permission level. 

The _PreprocessQuery methods are key when it comes to row level security.

State based security

State based security happens on a more functional level and it related to actions that users apply on data. Let’s take the example of the PurchaseRequests. When a procurement officer decides that that a request is ok, she can put it in state ready for board of directors approval.  From then, the request is no longer editable for the procurement officer, neither for the original requestor. The change in authorization is due to the change of a particular field of the entity (in casu, the RequestState). The different states and the potential transitions between these states are typically represented in a Finite State Table.


Bringing the Purchase Request detail screen in line with a workable server-side security approach

Our final goal is that we can protect the RequestPOEnrichtment and the RequestBoardEnrichment entity via table level security:

 partial void RequestBoardEnrichments_CanInsert(ref bool result)
            result = this.Application.User.HasPermission(Permissions.BoardPermission);

        partial void RequestBoardEnrichments_CanRead(ref bool result)
            //result = ...

        partial void RequestBoardEnrichments_CanUpdate(ref bool result)
            result = this.Application.User.HasPermission(Permissions.BoardPermission);

We start with following layout of our Request detail screen. Note that the 2 tabs have an edit button, so that e.g. a procurement officer can decide to fill in the procurement officer specific fields. The databinding of the tabs is done as follows:   As you can see, the tab is bound to the “main” PurchaseRequest property. We’ll see in a minute or two that this will lead to trouble. We foresee following code for when a procurement officer wants to provide her specific fields:

partial void EditByProcurementOfficer_Execute()
            // Instantiate PO Enrichment entity
            if (PurchaseRequest.RequestPOEnrichment ==null)
                RequestPOEnrichment POEnrichment = new RequestPOEnrichment();
                PurchaseRequest.RequestPOEnrichment = POEnrichment;

So, the RequestPOEnrichment is simply instantiated if it doesn’t exist yet.

Now, just for testing, we insert following code in the ApplicationDataService partial class:

        partial void RequestPOEnrichments_CanRead(ref bool result)
            result = false;

What we expect now, is that we can still read the request entity, but that we would get an error (a red cross) in the PO tab. We don’t care right now getting errors, because we didn’t do anything specific client side to avoid, cosmetically, that the user would run into this error.

Unfortunately, we’ll run into this screen:


I can not really tell you why this error happens, but it is what it is. So, let’s try to find a solution.

Instead of  using a query for retrieving the current PurchaseRequest, we use a Property for storing the current PurchaseRequest and in the InitializeDataWorkspace method run an query and assign a value to the PurchaseRequest property. For one reason or another, by doing so, the problem solved.



    partial void PurchaseRequestDetail_InitializeDataWorkspace(List<IDataService> saveChangesTo)
            this.PurchaseRequestProperty =


Now, let’s test again the situation where the user has no rights to see the ProcurementOfficer specific data (so, the _CanRead method returns false:


That’s really what we want: the user can see the purchase request details but not the PO specific data. For the moment we don’t care about the red crosses, because that’s only client-side cosmetics. I love to see the red crosses, because it really means that no data which had to be protected went over the line.


Now, we can go in client-side cosmectic mode and make following update:
   partial void PurchaseRequestDetail_Created()
            this.FindControl("RequestPOEnrichment").IsVisible = this.DataWorkspace.ApplicationData.RequestPOEnrichments.CanRead;
            this.FindControl("RequestBoardEnrichment").IsVisible = this.DataWorkspace.ApplicationData.RequestBoardEnrichments.CanRead;


In order to start editing the PO specific fields we adapt the Execute code of the button as follows:

partial void EditBoard_Execute()
            if (PurchaseRequestProperty.RequestBoardEnrichment == null)
                PurchaseRequestProperty.RequestBoardEnrichment = new RequestBoardEnrichment();

        partial void EditProcurementOfficer_Execute()
            if (PurchaseRequestProperty.RequestPOEnrichment == null)
                PurchaseRequestProperty.RequestPOEnrichment = new RequestPOEnrichment();
The CanExecute methods becomes this:

 partial void EditProcurementOfficer_CanExecute(ref bool result)
            result = (this.DataWorkspace.ApplicationData.RequestPOEnrichments.CanInsert
                && this.PurchaseRequestProperty.RequestPOEnrichment == null);

        partial void EditBoard_CanExecute(ref bool result)
            result = (this.DataWorkspace.ApplicationData.RequestBoardEnrichments.CanInsert
                && this.PurchaseRequestProperty.RequestBoardEnrichment == null);


This leads to the result we want: in case I may not see the PO, i won’t see them. All security in place is server side and on table level.

Obviously, you can tune the UI experience, and maybe hide the tab if the user may not see the data.

In order to make the picture complete, we have to implement the requirements described initially:


Everything is now secured with respect to entity-level security.



Securing an application starts on the server side. Making use of 1 to 0..1 relationships is the basis for for a state driven workflow where the entity enrichment takes places in sub entities.

Part II will cover the state based and the row level security aspects.