Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

Security

By default, users can view Excel reports and data connections only if they have administrative credentials on the server that is running SQL Server and if they have access to the network share. Since this isn't a normal setup, users typically need reporting privileges in SQL Server before they can view the Microsoft Dynamics GP data that is displayed in data connections and Excel reports.

There are three areas of security around Excel reports deployed to a network share or local drive:

  • Security to the network share/local folder
  • Security at the database level
  • Security around Excel

We'll spend a few minutes with each one.

Network share security

Realistically, network share security is normally going to be set by a network administrator. To shortcut this for administrators, the minimum required security on the shared folder is:

  • Change for the share tab
  • Read for the security tab

Now, for those of you who want the version that is longer than a Latvian wiener dog:

  1. In Windows Explorer, right-click on the folder where you deployed the Excel reports and then click Sharing and Security.
  2. On the Sharing tab, click Advanced Sharing and check Share this folder.
  3. Click Permissions.
  4. If the user or group already exists in this window, you can skip to the next step, otherwise:
    • Click Add
    • In the Select Users, Computers, or Groups window, enter the group or the users that you want to have access to the shared reports
    • Click OK
  5. Select the user or group to apply permission to in the Group or user names area.
  6. Select the Allow checkbox for the Change permission and then click OK. The Change permission is the minimum required permission.
    Network share security
  7. Click on the Security tab.
  8. In the Group or user names area, click Add.
  9. If the user or group already exists in this window you can skip to the next step, otherwise:
    • In the Select Users, Computers, or Groups window, enter the group or the users that you want to have access to the shared reports
    • Click OK
  10. In the Group or user names area, select each group or user, and then click the permission that you want the group or the user to have. The minimum required permission is Read.
    Network share security
  11. Click OK.

    Tip

    These instructions may vary slightly depending on the version of Windows Server used on the network or the user's version of Windows on a local drive.

By default, Dynamics GP 2013 deploys reports related to each company and each functional area in their own network folder. This makes it easy to apply different permission levels to sensitive areas such as payroll.

Database-level security

Access to information in the Dynamics GP 2013 database is handled a little differently. A set of fixed security roles is created automatically in SQL Server when Excel reports are deployed. All of these roles start with rpt_. These roles provide access to the underlying tables and views. The process to assign security is to add a user or group to SQL Server and give them access to the appropriate roles. The users that get added are not Dynamics GP users but either SQL Server users (different from the GP login IDs) or active directory users and groups.

This feels different from other aspects of Dynamics GP 2013 security in that setting security and assigning views to roles is all done from within SQL Server. The idea was to tie a SQL role to a role performed by a GP user, but in the end it feels a little clumsy.

To connect the SQL role with an Excel report to ensure that a user has appropriate access, you really need the spreadsheet from Microsoft that links the two together. You can find it at https://mbs.microsoft.com/fileexchange/?fileID=e4bb6958-0f07-4451-b72c-f02784e484df. (short link: http://bit.ly/15C3XN2)

In our example, we need access to the Account Summary Default Excel sheet. This sheet uses the Account Summary view. From the spreadsheet we see a number of roles that include the appropriate access.

Database-level security

For our example, we'll give a user access to the rpt_accounting manager role. In practice, it's not unusual to add all GP users to a single active directory group and give that group access to all the fixed reporting roles. This is particularly true for companies that don't use payroll and that don't have other sensitive reporting requirements.

To grant database permission using the built-in roles, we have to add the user or group to SQL Server and then assign the appropriate role(s).

To add a user to SQL Server, follow these steps:

  1. Open SQL Server Management Studio and log in using either Windows Authentication or SQL Server authentication.
  2. Expand Security | Logins.
  3. Right-click on Logins and select New Login.
  4. Click Search.
  5. Enter the domain and user you want to add or enter the group that you want to add to SQL Server. For my example, I'm entering my domain and user name—ibisinc\mpolino. This could also be a group of users such as GPUSERS, for example.
    Database-level security
  6. Click on Check Names to validate the entry and twice click OK to finish.

The user has now been added to SQL Server. Our example used a domain user but you can also set up an SQL user. In general, a domain user is preferred because it eliminates the need for the user to manage multiple logins and passwords for reporting. Using a domain login also provides additional control to administrators. If an employee leaves, for example, removing them from the domain removes both their network access and their reporting access in one step.

To grant access to the reporting roles:

  1. In the Security | Logins section, double-click the user or group that you just created.
  2. Select User Mapping on the left.
  3. In the upper-center section labeled Users mapped to this login:, check the box next to the company that you want to grant report access to. For our example, select TWO.
  4. In the lower-center section named Database role membership for: TWO, check the box next to rpt_Accounting Manager:
    Database-level security
  5. Click OK to continue.

The user now has rights to access the TWO AccountSummary default report that we've been working with and any other reports available as part of the rpt_Accounting Manager role.

Excel 2013 security

As you connect with database connections in Excel, a security bar may pop up with the message SECURITY WARNING External Data Connections have been disabled:

Excel 2013 security

This is an Excel security feature designed to prevent malicious code from running without the user's knowledge. In our case, however, we deployed the reports, we are running them on our network, and controlling access. This is about as secure as it's going to get and the message is really annoying for users. Let's turn it off.

To disable the Excel security message for these files:

  1. Open Microsoft Excel 2013 and select File | Options | Trust Center.
  2. Select Trusted Locations.
  3. Click Add new location.
  4. Browse to the location where you deployed the Excel reports. In my example, I used C:\GP2013XL. Click OK.
  5. Check the box marked Subfolders of this location are also trusted and click OK:
    Excel 2013 security
  6. Click OK twice to exit.

Now, when you run the Excel reports in the next section, the reports will open in Excel 2013 without the security warning.

Note

Microsoft offers a great Knowledge Base article on Excel reports and security at http://support.microsoft.com/kb/949524.

上一章目录下一章