Security Setup in Hyperion Planning

Now that I have TestApp named application, let’s see how to setup security and what are the changes we see !

To login to application and access forms or retrieve data we need to have a user id.  Firstly when we create an application we will not have any id’s in hsp_users table and no hsp_group table they will created only when we create groups and provision on TestApp application only Hyperion Planning creates hsp_group table with entries of group names that have been given provision on TestApp application.

Lets see how it happens.!

Hyperion Planning creates HSP_Group table in the application schema only when you assign security in application on any group created in HSS. So at this point as we have not assigned security to any group we will not be able to see HSP_Group table in RDBMS.

You can check provisioning information/Role details from below table in RDBMS which details the groups or user id on which application has been provisioned.

select * from user_segments

where segment_type='TABLE' and segment_name like '%GROUP%'

This gives the provisioning information on application TestApp in HSS

I see 3 records now 2 records with identity id native://DN=cn=911,ou=People,dc=css,dc=hyperion,dc=com?USER and 1 record with identity id native://nvid=xxxxxxxx:-432432432ofdsaOfdsfs:-632a?USER

admin identity will always be : native://DN=cn=911,ou=People,dc=css,dc=hyperion,dc=com?USER

you can check this for confirmation with below query

When I provision on user id there will be an entry into CSS_PROVISIONING_INFO for that user  and it will have identitiy_id(native object id – ldap and msad object id) to cross check whose id is that you can run below query to get the username 

and when you replace the identity_id with 3 record’s id

native://nvid=xxxxxxxx:-432432432ofdsaOfdsfs:-632a?USER you will get the username for that identity_id if you provisioned on a group with TestApp provision then you see corresponding record in CSS_PROVISIONING_INFO table with a record named ?GROUP at the end of member_identity.

Now that we have created a test native id (test1) and provisioned on TestApp, it created an entry in CSS_PROVISIONING_INFO table with identity id of that native user test1 in RDBMS and as well in Essbase.

EAS

No any forms or access on dimensions has not been given for now.

When I clicked on Connect it took me into Hyperion Planning, when I tried to expand the application node.

We need to run ProvisionUsers script to synchronize the provisioning info.

Now  getting below error

We need to  change the mode to allow all users to login which can changed under Application Settings.

Finally managed to login to application but not able to see any data forms,

Yes as you have not been given access on any data forms you will not be able to see any forms under application.

Now I have provided access on a data form called Testing.

What happens when I try opening the data form !! no surprises..!! will get error..!!! because I have not given access to members on data form to that user test1.

For now you have not set security, enabled for any of the dimensions, by default planning enforces/enables security for Standard Dimensions Scenario,Version,Entity,Account. You can check this  by running below query or you can find this through Planning by checking dimension properties on which dimensions security is enabled, you can even disable the security if you dont wish to have on any dimension, you can also apply/enable security on custom dimension members.

Select * from hsp_dimension

You can find the dimension id’s which have been enabled for security (Standard & Custom) which says with ENFORCE_SECURITY attribute against dimension id’s. You can make a note of dimension id’s which has got enforce_Security as 1 and query in hsp_object table for that dimension name.(By Default Scenario,Account,Entity,Version will have dimension id as 31,32,33,35) Now we see ENFORCE_Security has 1 , lets confirm this what are those dimensions which has this value 1.

You can run below query in your planning application schema to get the dimensions names for those dimension id's you see where Enforce_Security as 1 , to get the dimension names.

Data Form Design

Data Form Members
Row	Account	Headcount WL1	Headcount WL2	Headcount WL3
Page	Entity	Beijing	China	Hong Kong
POV	Scenario	Current
POV	Version	BU Version_1

Looks like below

To assign security to members or dimensions, select the member and click on Assign Access(You will get assign access button only if you have enabled security for that dimension)

You will see users & groups in this window only if you have provisioned(roles) on this application

You can select type & level of access (Read/Write/None & Member/IDescendants/IChildren/Descendants etc..)

 And click Add. Done you have provided access on Headcount WL2 member to test1 user.

You can see View hyperlink beside Headcount WL1 & Headcount WL2 members which states that there is security setup against those members.

Similarly need to provid access on other secure dimensions.(Entity,Scenario,Version)

Now I have provided access as below

Dimension	Member	Access
Account	Headcount WL1	Read
Account	Headcount WL2	Read
Account	Headcount WL3	None
Entity	Beijing	Read
Entity	China	Write
Entity	Hong Kong	None
Scenario	Current	Write
Version	BU Version_1	Write

Let’s login and see what happens with this level of access.

Am not able to see Hong Kong(Entity) member in Page and Headcount WL3(Account) in Rows, and I don’t have write access to Headcount WL1,Headcount WL2 , this is because though I have write access on China entity, but I don’t have write access on Headcount WL1,Headcount WL2 accounts and am not able to see Hong Kong & Headcount WL3 members as I don’t have any access on those members. So if you don’t have access to some members on data form , you will not be able to see those members on data form. To write into Database you need to have write access on all members in the combination, only then you get write access, even if 1 member in the combination has read access you will not write access against that combination.

Lets provide write access on Headcount WL1,Headcount WL2 and read access on Headcount WL3 and entity access not being changed.

Ta Da!!  You have got write access on Headcount WL1, Headcount WL2 members and able to see Headcount WL3 member now on data form.

For below selection you see cells are greyed out because you have been given read access on Beijing entity.

Let’s provide write access to Beijing & Read access to Hong Kong entities and see what happens.!!

You have got write access on Beijing entity & able to see Hong Kong entity in Page.

In general most cases if the security assignment/Provisions or change is smaller then Hyperion Planning synchronizes the changes to Essbase. But incase if the synchronization does not happen or you are not able to see members on data forms thought you assign access then in that case Refresh Security from Planning to Essbase which creates Security filters at Essbase end. (Essbase has the data residing in, so it would be essbase which would be giving the data when you retrieve or storing the data when you submit/write).

For provision synchronization you need to run ProvisionUsers script and for security synchronization you need to refresh security from Planning as below.

 

This then writes the security information in Planning RDBMS into Essbase and essbase create security filters.

Another and last layer of security is workflow. Workflow contains Entity in combination with any secondary dimension (account or any other custom dimension) and you will define the list of entities & opting secondary dimension members to be part of workflow. If you don’t have entity & secondary dimension part of workflow then security assignment is the last step. Once you setup workflow for Entity & secondary dimension combination then you need to assign scenario and version for the for the workflow to work/effective. Once you assign scenario & version to the workflow you need to start the workflow for those scenario & version combination , then it means workflow is started. And only owners who have been made as the owners of entity & secondary dimension combination only can submit/enter the data. (you need to put the id or number of employees to make him as the owner of that entity & secondary dimension combination, planning will automatically get his name when you put id- this works only if that user has already been part of group provisioned on that application and has access on that combination.) For assigning scenarios & Versions to workflow you should have enabled process management for those members in their member properties only then you can assign them to the workflow.

There might be scenario like you have provisioned,assigned security,made owner in workflow, still the user is not able to enter data then it might be a problem with security filters or scenario dimension (For Scenario dimension we define start year & end year, start period & end period, so if your year & period is out of the boundary of scenario dimension member then the system will not allow to enter data as you have restricted Planning only for set of years & periods, to get write access, edit the properties of that scenario member and change start year/period & end year/period accordingly.

When you assign security to group on any dimension or member then Hyperion Planning creates hsp_group table and creates an entry with the group id application RDBMS user.

When you de-provision the id/group from provisions against the TestApp application then corresponding records will be deleted from Foundation services RDBMS(CSS_PROVISIONING_INFO) but the details would be existing in Planning application RDBMS hsp_group table with the group id.

IF you are not able to see some columns or rows then it might not be security constraint, probably you might have enabled suppress missing rows/columns enabled or hide data form option checked. Etc.

Without removing assigned security , if you de-provision the id, the security information would be still be there in database and incase if you are on a data form and try to refresh the data form after de-provisioning the user in Shared Services , you would still be able to see the members, but when you logout and try to login again , you will not be able to see the application as you don’t have access.

So the order of security setup is

1.       create user/group

2.       Provision on application

3.       Assign security

4.       Refresh security

5.       Planning unit hierarchy (workflow).

Incase of access swap between 2 users(A to B) below are the steps to be followed.

1.       Identity the groups that the earlier user A belonging to .

2.       Add the new user to those groups which user A was belonging to .

3.       Replace the user A with user B in workflow.

4.       Remove the user A from those groups.

5.       Stop & Start the workflow for user B.

Incase if the group that user A belong has access to 2 applications and there are other users who are part of that group and user A needs access swap only on 1 application then in that case its suggested to create a separate group.

Sometimes at the time of creating application through native admin id, it would take MSAD (admin) SID and updates in RDBMS against hsp_users table of that application schema db, ideally for native admin id the SID would be native one and user_id value would be 50001 for native admin id.

SID (native://DN=cn=911,ou=People,dc=css,dc=hyperion,dc=com?USER)

USER_ID=50001, you can change this and restart planning and it works !! you can restrict to pick admin id in MSAD by applying filter in your MSAD configuration.