Chapter 4. Planning Application Permissions

Microsoft Excel tables connect directly to a Microsoft SQL Server database using user credentials.

Microsoft SQL Server controls user permissions.

So, a database administrator must add application users to a database and assign application roles.

The planning application has four built-in roles:

1. planning_app_users - Business users
2. planning_app_analysts - Business analysts
3. planning_app_administrators - Application administrators
4. planning_app_developers - Application developers

Each role has specific predefined permissions for application objects.

In addition to Microsoft SQL Server permissions, the planning application checks additional internal permissions on the forms and dimension members for business users (members of the planning_app_users role).

Application administrators manage such permissions using the planning-app-admin.xlsx workbook.

It is easy. So, business managers can manage application permissions with no requests to IT.

Database Permissions

This topic is for database administrators.

To add an application user:

1. Create a login.
2. Create a user in the application database.
3. Assign an application role to the user.

For example, you can use the following code to create an SQL Server login (in the master database):

CREATE LOGIN pa_user_01 WITH PASSWORD=N'Dev_2011#_Xls4168'

You can use the following code to create a user (in the application database):

CREATE USER pa_user_01 FOR LOGIN pa_user_01

You can use the sp_addrolemember procedure to assign a role:

EXEC sp_addrolemember 'planning_app_users',  'pa_user_01'

As talked above, you can add a user to one of the application roles:

planning_app_users, planning_app_analysts, planning_app_administrators, and planning_app_developers.

Form Permissions

To manage form permissions, open the planning-app-admin.xlsx workbook and the form_permissions worksheet:

You see a form list configured by business analysts in rows and usernames in columns.

Just set 1 to allow executing the form and 0 to deny executing.

Member Permissions

To manage member permissions, open the member_permissions sheet and select a dimension on the ribbon:

Here is a legend of used symbols:

R - select (read), specified for this member;

r - select (read), inherited from a parent member;

W - select and update (read and write), specified for this member;

w - select and update (read and write), inherited from a parent member;

D - deny, specified for this member;

d - deny, inherited from a parent member.

In the screenshot above, we can see:

The administrator denies read and write operations for Company Total directly and for its children by inheritance.

However, he allows reading and writing for KAM (Germany) and reading for KAM (Austria).

As a result, the user can see only two entities and can edit data for the first one only.

Type "r", "w", or "d" to change permissions.

The application applies for new permissions immediately after changes.

Base Settings

The application verifies member permissions in accordance with the base dimension settings:

In this example, we see five active dimensions.

The first four are protected. The application checks permissions for these dimensions only.

The Times and Categories dimensions allow selecting and updating any member by default.

So, you need to deny access to such dimension members if required.

The Accounts and Entities dimensions deny access by default.

So, you need to allow access for such dimension members.

You can find this form in the dimensions worksheet in the planning-app-analyst.xlsx workbook.

Account Permissions

Here is an example of account permissions:

Typically, you set permissions for upper-level members, and the application applies permissions to child members.

You can tune permission for any child member. Just change its permissions.

Entity Permissions

Here is an example of entity permissions:

Typically, this is a protected dimension, and you have to allow access directly.

Time Permissions

Here is an example of time dimension permissions:

Typically, this is a protected dimension that allows reading and writing by default. So, you can leave this as is.

Category Permissions

Here is an example of category dimension permissions:

Typically, this is a protected dimension that allows reading and writing by default. So, you can leave this as is.

Note that you see dimension members that can contain input data. You do see calculated members.

Region Permissions

Here is an example of region dimension permissions:

In this example, the region dimension is not protected. So, the application does not control access, and you do not need to change its permissions.

Closing Periods

Use the closed_periods worksheet to prevent changes in closed periods:

For example, in this case, we closed all periods for all categories except the November and December forecast.

The application checks these settings for all users including business analysts and application developers.

The planning-app-analyst.xlsx workbook also contains this form, and business analysts can close periods too.