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