In this post, I will continue explaining the examples created with eXtensible Data Security. In this part, I will explain how I did think of a solution for restricting warehouse access for users. There were a lot of questions on forums in the past about how to secure this. So, for a presentation on the Summit EMEA in Dublin this year, I decided to step into the challenges that comes with securing access to warehouses and related tables like inventory journals or purchase orders. I created a demo which will be covered in this part of the XDS blog series. In addition, this is a good example how to deal with different assignment of groupings per user without creating a policy per group or user.
Secure by warehouse
There might be several reasons for having the need to restrict access on warehouses. It might be related to type of products, different departments, sites or whatsoever. Reading all the questions, learns that people start telling about granting access to one or two warehouses only, and also having different persons responsible for different warehouses. What I notice, is that initially, people will try to create a policy per warehouse or set of warehouses. This idea will not work if one user should be assigned to multiple sets of warehouses. When you do create policies which contains the same constraint table, only one policy will be effective. So having multiple policies for different warehouses is not the correct solution. So, in general, when you are in the need to restrict groups of data, you should try to create a link between the user and the group. A, so called, MyConstruct table can be used to create a list of allowed groups per user. Technical details are provided below.
For this scenario, it is better to have a certain table where you can link users with warehouses. Preferably, I would like to use existing options for this.
Standard option link users with warehouses
While browsing the application, I could find one option where you can link users with warehouses. This is part of the warehouse management solution. Using the form Work users.
On this form there is an option to create logins with default warehouses for a worker. As by default, you can create user/worker relations, this could be an option for using it on security policies. With the button Warehouses, you can even link more warehouses to the same login User ID. However, it has some downsides…
It is part of the new warehouse management solution which is only available as of Microsoft Dynamics AX 2012 R3. If you are running older versions it is not available. Another disadvantage is the way you need to create the User IDs for the warehouse workers. Within the creation, you also have to mandatory provide a password. If you are not using warehouse management it doesn’t feel perfect to use this form.
If you are not running the correct version or wants to have another way of linking the warehouses, you can consider a new custom table where you can link users and warehouses. Also, in my example, I did include a customized option. You can explore this when you download the sources to test it for yourself. When you have imported and compiled the sources, you can open the form Warehouse security using the menu path: System administration > Setup > Security. Probably this is not the best place for this menu item, as all security related forms in this section are global setup where this warehouse security is data per legal entity. So, you can define per legal entity if all warehouses are visible or if and how they are constrained.
- All; this option is default and will result in showing all warehouses for the user
- List; use the warehouses selected for the user on this form
- Work user; will use the settings as defined for the worker on the Work users form.
- Both; this option combines the result for per user for the List and Work user.
If you have selected the option List or Both, you can actually link users by selecting a single warehouse, or multiple warehouses. With this new option, we do have some options to dynamically link warehouses with users for the security policies. Before explaining the technical part, it is good to know what is constrained in the demo and what not.
As this is a demo to show how you can use or implement XDS, not all tables and for sure not all your scenarios are supported. The warehouses table itself is constrained for all operations. That means that a user cannot read, create or maintain warehouses which are not part of his warehouse security. For worksheet type tables, I had chosen to only constrain insert, update and delete actions. This means that they can see all records, but can only change data belonging to their warehouses. One reason is to provide an example using the option to restrict only Create, Update and Delete actions. Another reason is to show users the complete document/journal lines for his reference. If e.g. a manager decided to create a purchase order with items for multiple warehouses, it would not show the complete records and interpretation errors might arise. It has been done for the next tables:
- Sales order lines
- Purchase order lines
- Inventory journal lines
- Agreement lines
- Inventory transfer lines (only from warehouse)
- Warehouse items
Depending on your needs, you can go ahead and expand or change it.
The security settings for warehouses for David is listed above. When he opens the Warehouses form, the next records are shown, so the policy works correct here.
On this form, using the policy with a restriction for all operations, the user will not see unauthorized warehouses. Also, the person is not able to create new warehouses. The functional result for a restriction on Create, Update and Delete is a bit different. When opening e.g. an inventory movement journal, the user can see all records. Initially there is no restriction to edit a certain record. The user can try to change e.g. the quantity on a line with a not assigned warehouse. Until the moment that the record will be saved, there is no blocking from the system. A lot of people then think that the policy is not working. However, the policy is enforced on the AOS, resulting in an error when the changes will be written to the database as shown in the next picture:
The error is very generic. It is just stating “Security policy permission denied”. Unfortunately, it is not possible to change this error based on the policy details to better explain to the user why he is receiving an error.
With help of a MyConstruct table, a list with warehouses per user will be created on the first call where the warehouses table will be used. As explained above, there are some variables how this list will be created. There is an additional complexity here as the warehouse security can be set per legal entity. The refresh frequency should be set per session; not per invocation. I tested the option with per invocation, but this had too much performance impact on the system. So, per session is the correct setting. However, in this setting, you need to loop through the legal entities and build the list for all legal entities at once as building the temporary data is done once per session. As solution for this, the XDS method on the MyInventLocations is only having the legal entity loop and will call a second method with the exact logic to build the data per legal entity.
Once you have the warehouses being compiled per user, the security policies can be created. In this case, I have used two policies because there is a difference between the setup tables and the worksheet type tables. The warehouses are fully restricted for all operations. The worksheet tables like sales order lines or purchase order lines do have another policy. All records are visible, but users can only update records belonging to their warehouses. As you can define only one Operation per policy, two policies were required to be able to differentiate in behavior.
In the policy, the table InventDim is used, but not constrained. If you do set this table or the table InventSum as constraint table, you will get runtime errors. E.g. creating a new inventory transaction is trying to link an InventDim record or update an InventSum record. If the tables are constrained, a record cannot be found while running the coding for a certain user and the system is then assuming it should create a new record. Actually when it already exists, a duplicate key exception will be thrown by the SQL database.
All objects for this warehouse security example can be found on my My OneDrive DynamicsShare. If you want to explore the examples, feel free to download and use it. The software is provided as-is and you cannot obtain any rights if something is not working correctly. You have to ensure you will install the examples in a separate environment first and test it carefully. If you have questions or feedback, feel free to add comments or send a message.
That’s all for now. Till next time!