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 them. 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.
I do hope you liked this post and will add value for you in your daily work as a professional. If you have related questions or feedback, don’t hesitate to use the Comment feature below.
That’s all for now. Till next time!
How can i resolve the ERROR: Variable WHSworker has not been declared in AOT-> Table -> Myinvent location
Thanks for reading my blog and asking a question. From the context you provided, I don’t know exactly what you have done. If you tried to create a similar solution in your environment, ensure that you review the naming of variables and if you referenced the model (which contains the WHSWorker object) to your own extension model.
Hi Andre,thank you
After importing the .XPO i couldn’t find the following tables in AOT
These tables are part of the Application Suite model. Do you get errors during compilation?
I got the error during importing the .xpo and compilation. the issue is under
data dictionary \ tables \ MyInventLocations
Have you checked if you have the Application Suite model referenced in your model? This might be causing your issue.
Same scenario i faced in D365 FO. How can i used this article as ref in D365 Finance Operations.
When you follow the link to my Onedrive, you will find the objects for the XDS example for both Dynamics 365 and AX 2012. One of the forms is differently created, but the XDS policy itself and the data model is equal for both.
This solution is exactly what we need. However, after loading it into our test environments, we discovered that no users can see anything in “Released Products” regardless of their warehouse security. Do you know what might be causing this?
I loaded the code with no changes – straight from your OneDrive – and the issue occured. I cannot find any relation to InventTable anywhere in the solution so we’re not sure what’s going on
Sorry, to confirm, this is in D365 for the Warehouse Security
Thanks for the interest in the provided example. There is no reference in the security policy to tables part of the released products. Can you check if you have a customization or ISV solution which extended the released products form with one of the tables that are constrained in this policy (e.g. InventLocation)? What exact version of Dynamics 365 F&O are you currently running?
Thanks for your quick reply! I’ll check with our devs about the customizations. We experienced the issue in product versions 10.0.20 and 10.0.24
I did a check on my environment which runs on 10.0.25. There is no issue with the released product for users restricted to warehouses. Please update me once you know more about the details in your environment. Let me know if I can be of any assistance.
After some trial and error, I’ve identified that the issue occurs when InventItemLocation is added as a constrained table to the InventLocationSecurity_Worksheet security policy
Would the solution still work if I removed this?
Great you found the culprit. The table you mentioned is also a datasource on the released products form. If you restrict this table, then indeed you are also restricting the total query on this form.
Have you noticed the difference between the two security policies in the downloadable file? I created a policy InventLocationSecurity_Group and a second called InventLocationSecurity_Worksheet. The “group” policy has a restriction on all operations. That means that you can’t add records which are not meeting the query criteria, but you also can’t see the related records. This is in my example restricting the warehouses and sites on the forms and in the look-ups. The “worksheet” policy is only restricting the database operations; this is not hiding any records. Did you change the Operation property? In that case, it could indeed cause the behavior you had encountered. If the Operations is set to InsertUpdateDelete, then it would not restrict data on the released products form if you add the table InventItemLocation.
This makes a lot of sense – thank you. Yes, I had changed it to AllOperations because we don’t want users to be able to see the records at all, rather than just not being able to edit them
I’ve tested it and it seems to be working fine without that constrained table
Thank you for all of your help
Thank you for downloading and starting a really good discussion. I liked the conversation and think that it would be beneficial for many other readers of this post.
Me again! We have now put this into live in D365, but a user is now trying to create a new warehouse and is receiving the error
Cannot create a record in Warehouses (InventLocation). Security policy permission denied.
The user has “All” selected in Security Type in Warehouse Security.
I have tested this and received the same issue with my test account. I also noticed that if I tried to create the same record a second time, it said the record already exists – even though it’s not showing in the list. So it looks like it may have been partially created
Any ideas how to fix this?
The example policy has currently the setting AllOperations for the Operations property. This implies that the users will see a list, but can’t create anything apart from the list. When you look at the XDS() method, also for users which do have the setting ‘All’ selected, it will build a list with current existing warehouses. This would indeed restrict the creation of new warehouses. You can modify the policy or add additional customizations to make it possible for these users to create warehouses. With the current example, only the system administrator would be able to create the warehouse. One option would be changing the Operations property to Select. Then a new record can be created, but they are not directly visible. The user will need a new session as the temporary table with available warehouses will be created once per session. You can also consider changing the Contect type to restrict the warehouses only for particular roles. You can then use the RoleName or ContextString properties. Then other roles will not be restricted anymore, giving them the option to create new warehouses without limitations.
Hi Andre, thanks for this and sorry for the very late reply. I’m having the same issue now but with the BOM table when trying to create formula lines.
I was under the impression that users would be able to create new formulas/warehouses etc if they had “All” selected. Is this not the aim of this customisation? That users can only modify/create warehouses/SOs/POs/formulas for which they are selected against in the Warehouse Security form?
I tried selecting “list” and choosing all the available locations instead of “All” and they still cannot create a warehouse, or create formula lines.
I am fairly new to this. Can you please advise how I can get this working? Happy for you to email me directly.
With XDS the system is restricting records. In my example, if a user has “All” or all warehouses are added, it will build a temporary table with all warehouses. It is then restricted to this list. If a user will add, e.g. warehouse ‘117’, this is not in the list and an error is raised when the policy has the property Operations set to AllOperations. You can prevent the error when you set this property to Select.
If the requirement in your organization is about restricting to a certain site, then you create a different policy and not restrict the warehouses themselves.