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.