Security Filter in Business Central (Customized Solution)

Greetings Readers,

Two years ago, I completed one implementation project of Business Central in one of Client's Business where there was a business requirement that a user from one Location should not see data or should not be able to process any transaction from other locations.

In addition Business had crucial raw materials and vendors data which they do not want to give access to anyone in the company except for few individuals. So to maintain the integrity of data there was need to filter all the records in business central based on Location, Inventory Posting group (for raw material) and Vendor posting group (for Vendor).

I tried "Security Filter" in Permission sets feature of Business Central and identified that it had many limitations mentioned in my previous blog here. In that blog I've explained the use of Security Filter and its limitations. I was not satisfied with the solution as a Consultant and as there was limitation on Cost, Time, and many other factors not in my control I could not devise an optimum solution for this.

Now when I decided to create a solution by myself, to mitigate these limitations and provide an easy to setup functionality and I've created a Customized functionality named "Security Filter" (As I couldn't come up with any other better name) in Business Central which I've described in detail in this blog.

So first let's have a quick walkthrough.

Security Filter Setup

This is a single page to access all the objects of this functionality and for quick setup.

Here with a single click you can enable or disable the Security Filter functionality. You can also specify if user with restricted right will be notified about the same or not with a custom message. Users will be notified on accessing Business Central.

User Setup

You need to specify at least one user as Super User for Security Filter. This is to ensure that atleast one user has all the rights to the business central and this functionality will not work for Super Users specified on User Setup. (I am thinking to make this optional based on setup)

Security Filters

So now you need to create Security Filters which will be applied to the Users. For this you need to access Security Filter List which will list down all the Filters created in Business Central.

On creating a new Security Filter a card page will open where you need to specify below details as per the business requirement.

First let's take the example where we need to apply the filter based on Customer No. on all the tables where Customer data is shown i.e. we want to hide certain Customer records from different tables of Business central. Now let's see how you can do it.

Security Filter Code: Specify a unique code for identification of this Security Filter.

Description: Specify the meaningful description to this security filter, you can specify its purpose too.

Main Table No.: Here you need to select the Table no., the records of which you want to filter. Select the table no. from the list. In our example it will be Customer table.

Main Table PK No.: This is important to specify which Primary key to use for applying the filter. This functionality supports filter of any table with up to 5 primary keys(This covers almost all the tables in use). For e.g. in case of "Dimension values", If you want to apply filter based on Dimension value in that case "Dimension value" is 2nd primary key of Dimension values tables, so you can also select 2nd primary key to apply filters. This will be used to suggest tables and fields as mentioned further in the blog.

In out example for Customer we need to use Customer No. to create the filter value so PK for it is 1. You can select the same from lookup list.

Main Table Field: This specifies additional field you want to see in order for you to identify the record if Primary key is not enough to identify the record. This field will be shown while creating filter value from system as shown further in the blog.

Main Table Filter value: Here you need to specify the filter value that you want to apply on tables. So let's say we want to see data of only Customer no. 10000 and 30000. So you can specify the value as "10000|30000". You can also use any operators for filtering the records like Pipe, double dot for range etc.

If you do not want to specify it manually, there is an easy way. Click on Update Temp Table records in actions. After that drill down on Filter value field and you can select one or more records to create the filter value automatically. This way it is easier for Users and Admin to generate Filter Value.

Filter Line: In filter lines you can directly select any table or any field on which you want to apply the filter mentioned in the Filter value field in header. I've kept it open for so that if the filter needs to be applied on any field which may or may not be linked to the Customer no. then that will also be possible.

You need to select Table no. and Field No. Filter value will auto copy from the Header.

What if I want to apply some different filter for any one single field of table ? Well you can always change the Filter value on line level too and it will do the job.

Still do I need to manually identify all the tables where Customer No. is a field ? Well I've solution for that, click on "Suggest Line Tables" in action and it will list down all the tables and fields where Customer table is related with Customer No. field.

The fields are linked with the "Main table PK no" field, so if you specify another primary key system will suggest lines based on that primary key. For e.g. in case of Dimension values, If you want to apply filter based on Dimension value in that case Dimension value is 2nd primary key of Dimension values tables, so system is capable to suggest Table and field based on Dimension value key and not the Dimension code (First PK of Dimension values).

System will also suggest lines from any customized tables.

If let's say you do not want to apply filter on any of the suggested lines you can simply edit it, modify the filter value or add lines as per your requirement.

If you want to see if this setup is proper and it will work on the runtime or not, then you can use "Test Security Filter" action. It will give you result, time taken and errors details if failed.

Ok, So we have created Security Filter for Customer. Let's attach it to the users. Search for User Security Filters page and here you can link Security Filter Code with User ID and select if to enable the Security Filter or disable it with a single click.

So we are done with all the setup. Now let's see the result. Go to Customer List page and below you can see that we are not able to see any customers other than 10000 and 30000.

Based on our setup of Tables and Fields we can also check that Customer Ledger entry, Sales order, Sales Price or any other table is also showing the data related to this 2 customers only. Bingo !!

Will this work on reports too ? Well tried running the report Customer List and it will also show the details of only two customers. Any other standard or customized report will also give the same result.

Now you must be worrying what if there is a large number of Customers and we want to apply the filter of Customers based on Customer posting group. Do I need to make the filter manually for Customer No. and do I need to update the filter each time a new customer is added in that Posting group ?

The answer is no. Security Filter has feature called "Dynamic Filter". So let's see how we can use it.

First follow the steps to create new Security Filter with Code - Customer Posting Group and Description - Only Customers data with specific Customer Posting group. Fill in the main table details as done in Customer table just do not create filter value.

Dynamic Filter: On Security Filter you can enable Dynamic Filter. This will enable some more fields for Dynamic Filter setup.

Base Table No.: Base table is the table which will be used to find the Dynamic Filter value of main table. So in our example based on Customer Posting group we want to filter the Customer records and then generate Dynamic filter of Customer No. field. So Customer Posting group is the Base table used for filter main table and Customer is main table which will be used to create Filter value and on which filter will be applied.

Main Table Link Field: Which field of Main table specifies the link between Main table and Base table. So in our case it is "Customer Posting Group" field of Customer table.

Base Table PK No., Base Table Field and Base Table Filter value has same function as of main table field. So in our case we will select Filter Value as "Domestic" as we only want to see the Customer data of Customers with Posting group as "Domestic".

Now you need to run the Suggest Lines again as based on this setup system will suggest new setup.

As you can see in above screenshot, where in system Customer Posting group field is available the fixed filter is suggested while on other tables where Customer no. field is there the Dynamic filter is suggested.

Here also you can change the setup if you want to modify or delete some filters based on requirement.

There is one option to "Test Dynamic Filter" which will show you the Filter value that will be applied on the run time to make sure that everything is setup correctly.

So Let's see the results. Open Customer list and you can see that only Customers with Domestic posting group are shown. If you check Customer Ledger entry, Sales Order everywhere the same filters are applied based on Customer Posting group field or Customer No. field as applicable.

Customer no. 30000 does not have Customer posting group, so you will not be able to see any record with that Customer no.

So that is all about the walkthrough of functionality.

Why this solution is better than Security Filter provided in standard Business Central ?

In my previous blog I've mentioned all the limitation of Security Filter and now I'll list down a comparative pros of this Customized functionality.

One Click Enable/Disable: Using one click from Security Filter setup you can activate or deactivate this functionality. Which it takes lot of efforts in standard.

Easy Data Import/Export: You can use Excels or Config. Package to directly import or export data in Security Filter, Filter Lines and User Security Filters list. Also enable/disable the same through excel in one go.

Suggest Table Lines: Be it Fixed filter or Dynamic Filter, system will suggest you all the Tables and Fields which is linked with the Table and Primary key mentioned by Admin in the Security filter. This will reduce lot of burden on Admin to identify the tables where the filter is required. In standard you need to identify these tables manually.

Create Filter Value: Based on the table and fields selected, you can utilize lookup on Filter value fields to generate Filter value based on the records you have created and you do not need to create filter manually. In standard each time you need to create Filter value manually.

Dynamic Filter: Dynamic filter allows you to generate filter value on the runtime and saves lots of efforts to change Filter value each time a record is created in main table. Standard does not have feature of Dynamic Filter.

Test Filters: You can test the Security Filter and Dynamic Filter to make sure that system will not give any runtime errors. Also if any error is identified then troubleshooting becomes easy task with these features which takes long time in standard feature.

Synergy with Permissions: This functionality does not affect standard permission set, it will work in addition to standard Permission sets. However you can not override this functionality with Permission set. Why would you use standard Security Filter functionality in first place, Right ?

Works for Reports: Reports will also show filtered or restricted data based on the Security Filter configurations. In standard reports gives error if you try to access any record outside your permissions and blocks the process.

Copy Function: In few days I'll also add copy function to the Security Filter so Admin can easily copy and create new Security Filter and with minor modification create a new Security Filter as required. You can copy permission sets in standard.

User Notification: User will be notified through message if Admin wants to do so that they have limited access to data.

Easy User Interface: Well the best part of this functionality is easy to use, easy to setup user interface. Everything can be done through few clicks and you are good to go.

So that was all for this Security Filter functionality and I hope it will help ease the job of Admin to setup permissions and user data access. 

Let me know if there is any suggestions, questions or any improvements I can do in this functionality.

Also do suggest some good names for the functionality, pages and fields. I've kept them pretty easy to understand as of now.

Till then, Happy reading !!