Sunday, February 2, 2014

Microsoft Dynamics AX 2012 Excel Add-in – Security Roles (Add Tables, Add Data)

Microsoft Dynamics AX 2012 Excel Add-in – Security Roles (Add Tables, Add Data)
 
Purpose: The purpose of this document is to explain security settings required by Microsoft Dynamics AX 2012 Excel Add-in for data import/export using "Add Tables" and "Add Data" methods.
 
Challenge: When using Microsoft Dynamics AX 2012 Excel Add-in you have 2 methods to access data using "Add Tables" and "Add Data" functions. "Add Tables" function allows you to access raw tables (as they are listed in AOT). "Add Data" function allows you to access Services, standard Queries (Query references) and Custom Queries exposed through Document Data Sources in Organization Administration > Setup > Document Management. The challenge is how to set up security settings for Microsoft Dynamics AX 2012 Excel Add-in following required minimum privilege principle to allow users import/export data. 
 
Solution: When using "Add Tables" function the system will access raw tables (as they are listed in AOT) and thus the user should be assigned to System Administrator role in order to import/export data. When using "Add Data" function the system will access the list of Document Data Sources to provide the user with the access to business data in accordance with his/her permissions. In order to use "Add Data" function the user does NOT need to be assigned to System Administrator role, instead you can leverage a very granular security setup to allow the user to import/export business data exposed through Document Data Sources, for example, only using certain operations preventing business data damage which may occur when using raw tables approach.  
 
Walkthrough
 
When using "Add Tables" function the system will access raw tables (as they are listed in AOT).
 
Select Tables
 
 
In Microsoft Dynamics AX 2012 by default and by design only System Administrators have access to AOT.
 
Recommendation
Description
Always assign the least permissions when you set up and configure the user security features in Microsoft Dynamics AX.
Before you set up and configure the least permissions in Microsoft Dynamics AX, consider the following recommendations:
  • By default, and by design, only Microsoft Dynamics AX system administrators have access to the Application Object Tree (AOT). Do not grant users access to the AOT, unless the users are members of a development role who must access the AOT as part of their job requirements. If you grant regular users access to the AOT, the users may intentionally or unintentionally compile the application, synchronize the application, change license files, or change module configurations. All of these actions can cause problems in your business or organization.
  • Do not make users members of the System administrators role, or grant these users access to System administration in Microsoft Dynamics AX, unless the users are responsible for setting up and configuring Microsoft Dynamics AX in your business or organization. If you grant regular users access to this group and module, the users may intentionally or unintentionally cause problems in the Microsoft Dynamics AX application.
  • Do not assign users to the Windows Administrators group or Power Users group on their local computers, unless the users are explicitly required to perform the job functions of an administrator or power user. Members of these groups can add applications to their local computers and remove applications from their local computers, and these actions can introduce security risks. Instead, assign users to the Windows User group. Click Start > Administrative Tools > Server Manager > Local Users and Groups.
 
Please find more info about Security Best Practices in AX 2012 here:  http://technet.microsoft.com/en-us/library/hh202063.aspx
 
When the user invokes "Add Tables" or "Add Data" functions in Microsoft Dynamics AX 2012 Excel Add-in the system will use AifUserSessionService AIF Web Service to retrieve info about current user session
 
[SysEntryPointAttribute]
public AifUserSessionInfo getUserSessionInfo()
{
    AifUserSessionInfo info = new AifUserSessionInfo();
 
    info.parmAXLanguage(this.getLanguage());
    info.parmCurrencyInfo(this.getAifCurrencyInfo());
    info.parmCompany(this.getCompany());
    info.parmCompanyTimeZone(this.getCompanyTimeZone());
    info.parmUserPreferredTimeZone(this.getUserPreferredTimeZone());
    info.parmUserPreferredCalendar(this.getUserPreferredCalendar());
    info.parmUserId(this.getUserId());
    info.parmIsSysAdmin(Global::isSystemAdministrator());
    info.parmAOSLocaleName(AifUserSessionService::getAosLocaleName());
 
    return info;
}
 
Please note that an instance of AifUserSessionInfo class will have UserId and IsSysAdmin properties assigned to be consumed in External code (Excel Add-in). IsSysAdmin property signifies that the current user is assigned to System Administrator role, and in case IsSysAdmin property is set to "true" the user will have access "Add Tables" – "Select Tables" screen regardless of what actual UserId value is passed ("Admin" or not). And vice versa even if UserId = "Admin" value is passed, but IsSysAdmin property is not set the user will not have access to "Add Tables" – "Select Tables" screen and "The role you are assigned does not have permissions to access the selected data" error will appear.
 
When using "Add Data" function the system will access the list of Document Data Sources to provide the user with the access to business data in accordance with his/her permissions. Please consider using "Add Data" function for business data import/export which does NOT require your user to be assigned to System Administrator role.
 
To illustrate a necessary security setup for Microsoft Dynamics AX 2012 Excel Add-in when using "Add Data" approach I'll use User KEN for import of Ledger transactions via LedgerGeneralJournalService AIF Web Service
 
Please note that LedgerGeneralJournalService AIF Web Service is exposed and activated in Document Data Sources as shown below
 
Document Data Sources
 
 
This is security settings in Microsoft Dynamics AX 2012 for User KEN. Please note that User KEN is NOT assigned to System Administrator role
 
User KEN (Financial controller)
 
 
Now if I try to invoke "Add Tables" function the following error pops up
 
Dynamics AX Error
 
 
Please note that User KEN is NOT assigned to System Administrator role
 
Now instead of "Add Tables" I'm going to invoke "Add Data" function and select LedgerGeneralJournalService AIF Web Service
 
In the case your User doesn't have access to the business data based on tables in the query used by AIF Web Service you could see "User is not authorized to select a record in table" error
 
Microsoft Dynamics AX Office Addin  
 
 
In this particular case I used User TONY who doesn't have access to the required business data. You can easily fix it by assigning TONY to the role which has access to the required business data
In fact User KEN already has access to the required business data, so I'm going to invoke "Add Data" function now
 
Add Data
 
 
LedgerGeneralJournalService AIF Web Service shows up in the list of available Document Data Sources now. Please make sure you deploy appropriate Service group in order for Services to show up in the list
 
For example, on Microsoft Dynamics AX 2012 Demo VM I had to deploy LedgerServices Services group
 
Service group
 
 
Now I can compose a data set for data import/export using Field Chooser. The next step will be to retrieve the info about Ledger journals from Microsoft Dynamics AX 2012 using Refresh function
 
Refresh
 
 
Now it's time to use Publish function and create a brand-new Ledger journal in Microsoft Dynamics AX 2012 using Excel Add-in
 
Publish
 
 
When I try to publish the following error may occur if the user doesn't have permissions to execute operation
 
Publishing Details
 
 
This is because User KEN doesn't have access to the Server method which is one of possible securable objects in Microsoft Dynamics AX 2012 
 
In order to resolve this issue and grant appropriate access for User KEN I will create my own Role and assign User KEN to it
 
Security Role "Alex"
 
 
Duty
 
 
Privilege
 
 
Please note that I explicitly provided access to Server method (LedgerGeneralJournalService.create), so User KEN will be able to create Ledger journals
 
Add permissions to privilege
 
 
Add permissions to privilege
 
 
Now I'll assign User KEN to my new Security Role "Alex"
 
User KEN
 
 
Now I'll be able to successfully create Ledger journal in Microsoft Dynamics AX 2012
 
Publish
 
 
Publishing details
 
 
Here's the resulting Ledger journal in Microsoft Dynamics AX 2012
 
General journal
 
 
Please note that in terms of access to the business data you can also assign permissions to particular tables through Override permissions as shown below
 
 
This approach allows to granularly set up permissions for users to use Microsoft Dynamics AX 2012 Excel Add-in without assigning them to System Administrator role.
 
Summary: This document describes how to set up security settings required by Microsoft Dynamics AX 2012 Excel Add-in for data import/export following required minimum privilege principle.
Author: Alex Anikiev, PhD, MCP
 
Tags: Dynamics ERP, Dynamics AX 2012, Excel, Dynamics AX 2012 Excel Add-in, Data Import, Data Conversion, Data Migration, Application Integration Framework, Add Tables, Add Data, Security Role, System Administrator.
 
Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the concepts and describe the examples.
 

27 comments:

  1. Excellent post, very clear and useful !

    ReplyDelete
  2. I have found success utilizing Arbela's Security Manager: http://lnkd.in/bWbFJR2

    ReplyDelete
  3. Just the info I needed -- concise and to the point. Thanks!

    ReplyDelete
  4. If I need to use this functionality (ADD-DATA), but to publish a Price Discount Journal, can I do it?

    I try whit the service "PricePriceDiscJournalService" but I can't see it in the add-in to select. Is this service for the add-in?

    Thanks

    ReplyDelete
    Replies
    1. Hi Melvin!

      Please make sure you deploy the Service first: you can add it into a Services Group (existing or new) and then right click and Deploy in AOT. Then please add an entry in Document Data Sources form in Org administration > Setup > Document management > Document data sources with Data source type = Service. After that you may want to refresh data cache or restart AOS before a newly created data sources will show up in the list

      Good luck!

      Best Regards,
      /Alex

      Delete
    2. Excellent Mr Alex.

      Thank you so much, it works!!

      Delete
    3. Hi Alex, me again :(

      I create a journal in AX, then I use the ADD-IN to get the lines with the option ADD-DATA and the service "PriceDiscountJournalService", then I try to update the amount and publish back to AX, but I am getting this message:

      "The PriceDiscountJournalService Service does not support Update"

      What can I do to solve this? Do you know about other alternative to do this?

      Thanks again.

      Delete
  5. I think the things you covered through the post are quiet impressive, good job and great efforts. I found it very interesting and enjoyed reading all of it..
    Microsoft Dynamics CRM Training | Microsoft Dynamics GP Training

    ReplyDelete
  6. Hello Alex and everyone else,

    Thank you for this highly helpful post. One question I have is if this method can be used to update master tables as well? I am trying to this with the HcmWorkerImportService and I cannot get the service to update an existing record.

    I keep getting the following error:
    "The document hash does not match the hash in the XML. The document may have been changed since last readDocument Worker could not be updated. Error details: The document hash does not match the hash in the XML. The document may have been changed since last read"

    any ideas?

    Thank you

    ReplyDelete
  7. Nice one! Thanks for the detailed post.

    ReplyDelete
  8. Good article! I think that microsoft ax is the best solution for companies! have a nice day :)

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. Chung cư An Bình City thành phố giao lưu
    chung cư cao cấp Phạm Hùng FLC Green Home
    chung cư Vũ Trọng Phụng Rivera Park
    bán đợt cuối chung cư Xuân mai Sparks Tower Hà Đông
    dự án cc eco green city nguyễn xiển
    bán chung cư riverside garden vũ tông phan
    căn hộ rivera park hà nội

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. Hello!!!
    Excellent and very cool idea and the subject at the top of magnificence and I am happy to this post..Interesting post! Thanks for writing it. click

    ReplyDelete
  13. What if I want to use a query reference instead of a service ?
    How do I have to proceed?
    For example I have to use ProjTable which is as type of Query reference.

    ReplyDelete
  14. https://www.danhbachungcu.net/2018/11/chung-cu-samsora-premier-105-chu-van-an.html

    ReplyDelete
  15. A very interesting article. Now I'm interested in such technological novelties. I decided it was time to move forward and start doing something with the company. I think a step ahead is a good thing for me and my employees. I will need some development and more qualifications :) I found the Internet of Things recently (on the site click ) which seems quite interesting . I did not know that before. Do you think it is a step in the right direction? :)

    ReplyDelete
  16. Hi, Is it documented anywhere in Microsoft Docs that to use "Add Tables" option we need Sys Admin access?

    ReplyDelete
  17. importer database Really I enjoy your site with effective and useful information. It is included very nice post with a lot of our resources.thanks for share. i enjoy this post.

    ReplyDelete
  18. I'm so excited to learn this stuff. Looking forward to my excel course.

    ReplyDelete