Hyperion Planning Security – Import, Export Utilities and More

Today, I’d like to review how we can quickly and easily setup Security in a Planning application using the Import & Export Utilities, as well as a custom Excel VBA template.

Before anything else, I should probably explain or remind some basics about Planning security.

Why do we need to setup security and how is security defined in Hyperion?

Security whether created for a Planning or Essbase application, enables a company to control access to the Hyperion solution. Setting up security lets you protect data and prevent unauthorized users from changing or viewing data.

Security in Hyperion Planning is defined at three levels:

1. Authentication: you need to have a username whether it is a native Hyperion username, or your Microsoft Active Directory single sign on.
2. Provisioning Users and Groups: Hyperion application security determines user access to Hyperion products using the concept of roles. Each role possesses a different set of permissions determining the level of access the user will get. In most cases, predefined roles will be sufficient. But if needed you can create custom roles.
3. Assigning access to users and groups in Hyperion Planning.

Security is maintained in Shared Services by administrators. For more information about setting up security, you can refer to the User Security Administration Guide.

If you have set up security for a Planning application before, you know you can manually assign access to groups or users to dimension members, forms, Business rules, task lists but that’s a very time consuming process. The other way is to import it all at once using a text file that will need to be named SecFile.txt and the ImportSecurity utility.

Now the trick is to setup that SecFile.txt the right way.

To give you some pointers about the required syntax for the SecFile.txt, you can set up security manually for a few dimension members, a few forms, form folders, task lists, business rules and use the ExportSecurity utility to generate the SecFile.txt.

ExportSecurity.cmd

The ExportSecurity.cmd is available on the Planning server. Open a command prompt, and go to the Planning utilities location (In my case C:\Oracle\Middleware\user_projects\epmsystem1\Planning\planning1)

To simply Export Security, type the below command, replace ApplicationName and UserName as desired and press enter:

ExportSecurity /A=ApplicationName,/U=UserName

You will then be asked to type in your password, then press enter.

Export process is done. In the same folder as the ExportSecurity utility, you should see a file named SecFile.txt. This text file contains all the security for the application you defined in the ExportSecurity command.

Parameters other than application name and username are available for the ExportSecurity.cmd. Just search for “Exporting Access Permissions“.

Now that you have a valid SecFile.txt, you can manually edit it or you can use a tool like the one I’m going to show you and that you can download here.

Use an Excel VBA template to set up the SecFile.txt and more.

This tool is a simple excel file with some vba behind it.

Couple notes:

• all tabs are protected to avoid users from deleting cell formulas. Password to remove the protection is: password
• Don’t delete rows or columns. if you need to delete cells, use clear cells instead.

The Hyperion Security Excel VBA template can be downloaded here. Once the file is open, use the first tab to:

• specify on which dimensions you wish to apply security
• list of users that will need to access the application (optional)
  

Then go to the MemberSecurity tab and start defining the dimension security you need. On each row, specify:

• the dimension
• a Member Name from that dimension
• the group Name for which you want to apply security to
• the Access level which gives you a list of options:
  • None which gives no access to selected member (depends on the Relationship selected)
  • READWRITE which gives write access to selected member (depends on the Relationship selected)
  • READ which gives read access to selected member (depends on the Relationship selected)
• Relationship which gives you a list of options: MEMBER, @DESCENDANTS, @IDESCENDANTS, @CHILDREN or @ICHILDREN

Make sure MemberNames and Group Names are spelled right. If the groups haven’t been created yet, that is not a problem.
One note, I always set up security using groups and pretty much never assign form, rules or dimension access per user so the template is laid out for Groups, but you can enter User Name in any of the “Group Name” columns.

Then go to the FormSecurity tab and start defining the Form security you need. On each row, specify:

• the group Name
• the form or form folder name
• the Access level which gives you a list of options:
  • None which gives no access to selected form or forms within the selected form folder
  • READ which gives the ability to open the selected form or forms within the selected form folder
  • WRITE which gives the ability to edit the selected form or forms within the selected form folder and by edit, I mean modify the layout of the form, menus and business rules attached to it. Simple users usually never have WRITE access to forms.
• the Form Security Type:
  • SL_FORM if object selected is a single form
  • SL_COMPOSITE if object is a composite form
  • SL_FORMFOLDER if object is a form folder
• Finally, Relationship is derived from the Form Security Type of object, if the object is a folder then the Relationship is always @IDESCENDANTS, if it is a form or composite form then it is always MEMBER.

Make sure Form, Form folders and Group Names are spelled right. If the groups haven’t been created yet, that is not a problem.

Then go to the BusinessRuleSecurity tab and start defining the BR security you need. On each row, specify:

• the group Name
• the Business rule or Business rule folder name (for Business rules, I personally use Business rules folder only to set up security)
• the Access level which gives you a list of options:
  • NONE which gives no access to selected rule or rules within a rule folder
  • LAUNCH which gives the ability to execute the selected rule or rules within a rule folder
• Relationship which gives you a list of options: MEMBER, @DESCENDANTS or @IDESCENDANTS
• the Business Rule Security Type:
  • SL_RULE if object selected is a single rule
  • SL_CALCFOLDER if object selected is a rule folder

Make sure BR, BR folders and Group Names are spelled right. If the groups haven’t been created yet, that is not a problem.

Then go to the TaskListSecurity tab and start defining the Task List security you need if any. On each row, specify:

• the group Name
• the Task List Name
• the Access level which gives you a list of options:
  • READWRITE which gives the ability to assign a task list
  • READ which gives the ability to view a task list
• Relationship : MEMBER only
• the Task List Security Type: SL_TASKLIST

Make sure Task List names and Group Names are spelled right. If the groups haven’t been created yet, that is not a problem.

Once done, go back to the first tab: Manage and click on the Create SecFile.txt button. Once done, a SecFile text file should have been created at the same location as the Excel VBA template and should have been populated based on your inputs:

This file is ready to be imported into a Planning application. Before we go through the ImportSecurity utility, you can also use the Excel VBA template to:

• Create groups that are being used in the SecFile by clicking on Create Security groups file in the Manage tab. A SecGroupsFile.txt will be created at the same location as the Excel VBA template. This will give you a unique list of groups. You can copy its content into the MaxL Editor in EAS and run it to create the groups.
• Add users to groups by filling the AddUsersToGroup tab and clicking on Create User Access List in the Manage tab. A UserAccessList.txt will be created at the same location as the Excel VBA template. You can copy its content into the MaxL Editor in EAS and run it to assign users to groups.

Note: if you use those methods to create groups and assign users to those groups, you will still need:

• to provision those groups with the appropriate roles manually. These groups should not be provisioned with administrative roles.
• to make sure Security filters have been refreshed

ImportSecurity.cmd

The ImportSecurity.cmd is available on the Planning server at the same location as the ExportUtility. Before running the utility, make sure the SecFile.txt that needs to be imported is in the same directory as the ImportSecurity utility.

To Import Security, type the below command, replace ApplicationName and UserName as desired, and press enter:

ImportSecurity.cmd “Application,username,SL_COMMA,,,”
ImportSecurity.cmd “Application,username,SL_COMMA,,,SL_CLEARALL” 

SL_COMMA represents the delimiter used in the SecFile.txt. The default delimited is SL_COMMA.
You can use SL_CLEARALL to clear all previously set security before importing.

You will then be asked to type in your password, then press enter. If everything was done correctly, all rows should have been added to the application successfully as shown below.

Other parameters are available for the ImportSecurity.cmd. Just search for “Importing Access Permissions“.

ExportSecurity and ImportSecurity logs can be found on the Planning server under X:\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\planning.

These Planning utilities (ExportSecurity and ImportSecurity) can also be very useful to:

• make quick massive changes to an existing Hyperion Planning application
• migrate Security between environments
• or just view what security is setup in an existing Hyperion Planning application. Vijay will show you in an upcoming post an even faster method for this)