Secure by warehouse

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.

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.

Secure by warehouse

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.

Customized option

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

Secure by warehouse
  • 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.

Constrained tables

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
  • BOM
  • Agreement lines
  • Inventory transfer lines (only from warehouse)
  • Warehouse items

Depending on your needs, you can go ahead and expand or change it.

Functional results

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.

Secure by warehouse

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:

Secure by warehouse

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.

Technical details

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.

Secure by warehouse
Secure by warehouse

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!

28 replies
  1. Semay
    Semay says:

    How can i resolve the ERROR: Variable WHSworker has not been declared in AOT-> Table -> Myinvent location

    Reply
    • André Arnaud de Calavon
      André Arnaud de Calavon says:

      Hi Semay,

      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.

      Reply
  2. Shaikh Muhammad Atif
    Shaikh Muhammad Atif says:

    Hi,

    Same scenario i faced in D365 FO. How can i used this article as ref in D365 Finance Operations.

    Regards,

    Reply
    • André Arnaud de Calavon
      André Arnaud de Calavon says:

      Hi Shaikh,

      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.

      Reply
  3. Asher Francis
    Asher Francis says:

    Hi Andre

    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

    Thanks
    Asher

    Reply
    • André Arnaud de Calavon
      André Arnaud de Calavon says:

      Hi Asher,

      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?

      Reply
        • André Arnaud de Calavon
          André Arnaud de Calavon says:

          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.

          Reply
          • Asher
            Asher says:

            Hi Andre

            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?

            Thanks

          • André Arnaud de Calavon
            André Arnaud de Calavon says:

            Hi Asher,

            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.

          • Asher Francis
            Asher Francis says:

            Hi Andre

            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

            Asher

  4. Asher Francis
    Asher Francis says:

    Hi Andre

    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?

    Thanks

    Reply
    • André Arnaud de Calavon
      André Arnaud de Calavon says:

      Hi Asher,

      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.

      Reply
      • Asher Francis
        Asher Francis says:

        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.

        Thanks

        Reply
  5. André Arnaud de Calavon
    André Arnaud de Calavon says:

    Hi Asher,

    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.

    Reply
  6. Sophia Retief
    Sophia Retief says:

    Good day Andre. First of all thank you so much for this article.
    I have used this solution for different clients and it works great. I have also used a different version using the Default dimension and that works great. But I have a different problem now where a client wants a similar solution for the LedgerDimension on LedgerJournalTrans.
    I created a tmp table where the Dimension Values are defined for the user and the Policy using a role. But when I open any of the LedgerJournal forms are opened no records are shown. Is there a way that I can see what the query is when the form is run. I have checked select * from ModelSecPolRuntimeEx where name like ‘%Ledger%’ and the ModeledQueryDebugInfo looks good. Do you know if there is some additional query or something happening on th LedgerJournalTrans forms that makes this Policy not to work. Kind regards

    Reply
  7. Mahmoud Mohyi
    Mahmoud Mohyi says:

    Thanks André,
    it works fine except the transfer order “To warehouse” I need to show all warehouses

    Reply
    • André Arnaud de Calavon
      André Arnaud de Calavon says:

      Hi Mahmoud,

      Thanks for your comment. I got this question before and understand the business case.
      You can create a view of the warehouses and use that view for an override of the standard lookup. In case the view is not set as a constrained table, it will be treated as a different table, being not restricted by the XDS policy. Let me know if you can continue with this suggestion.

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.