Extracting user based security from Hyperion Planning

The Post takes me through my experience in providing solution to my client for they were very much in need of this as to analysis exact number of users and security assigned to them which helped in putting together the details interms of licenses and managing application security.

To Extract User Ids from Application Schema who belongs to application groups


Run Below Query in Planning Application Schema (Ex: PlnApp is my application schema)
-------------------------------------------------------------------------------------------------------------
create table unameingrp as
select g2.sid,o1.object_name from hsp_usersingroup g1,hsp_object o1,hsp_group g2
where o1.object_id=g1.user_id and g2.group_id=g1.group_id
order by 2



GRANT SELECT ON UNAMEINGRP TO HSS

Run Below Query in Shared Services Schema
-----------------------------------------
create table GrpUsrNames as
select c1.name,c2.object_name from css_groups c1,PlnApp.unameingrp c2
where c2.sid=c1.identity_id
order by c2.object_name

With this you have users in each application group.

Load Your Application Secfile into a RDBMS table I used sql loader with a control file(PlnApp.ctl)
-----------------------------------------------------------------------------
Your secfile can be obtained by running ExportSecuirty Utility available in Planning under below path.

user_projects/Dev/Planning/planning1

 ./ExportSecurity.sh -f:password.txt /A=PlnApp,/U=admin,/TO_FILE=/dev01/PlnAppSec

You should have your table columns in CAPS or else it gives column doesn't exist

Before you start loading data make sure you have created table in HSS schema as "PlnAppSecfile" as i am loading secfile data into that table through sql loader.

Control File Code (I named this control file as PlnApp.ctl)








LOAD DATA
APPEND INTO TABLE PlnAppSecfile
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
("GROUPNAME","DIMENSIONAME","ACCESSTYPE","ACCESSLEVEL","UNKNOWN")

if you have tab delimited fields then you need to use fields terminated by as below

LOAD DATA
APPEND INTO TABLE PlnAppSecfile
FIELDS TERMINATED BY x'09'
TRAILING NULLCOLS
("GRPS","NAM")

Batch File Code
rename PlnAppSec.txt Sec.dat
sqlldr username/password@DatabaseName DATA=Sec.dat control=PlnApp.ctl log=PlnApp.log errors=10000 rows=1000000 DIRECT=TRUE

Make Sure your PlnAppSec and batch file and ctl file are in same path, if not use absolute path.
Now Just double click the batch file your secfile would have got loaded into HSS schema table PlnAppSecfile, incase of any error refer to logfile.

Now you have Users under each group and access assigned on each group & username, so joining these 2 should give us the final result.

incase if you want more details in terms of dimension name after every artefact then you need to extract dimension details as well, like A12100987- Account,E1213232 - Entity...etc


Run Below Query in PlnApp Schema

1.create table test2 as select * from hsp_object;

2.update test2
set object_id=object_id-1;

create table dimdetails as
select h1.object_id,h2.type_name,h1.object_name actname,t2.object_name as aliasname from hsp_object h1,hsp_object_type h2,test2 t2
where h1.object_type in h2.object_type and h2.type_name='Account' and t2.object_type=10 and h1.object_id=t2.object_id
union all
select h1.object_id,h2.type_name,h1.object_name actname,t2.object_name as aliasname from hsp_object h1,hsp_object_type h2,test2 t2
where h1.object_type in h2.object_type and h2.type_name='Scenario' and t2.object_type=10 and h1.object_id=t2.object_id
union all
select h1.object_id,h2.type_name,h1.object_name actname,t2.object_name as aliasname from hsp_object h1,hsp_object_type h2,test2 t2
where h1.object_type in h2.object_type and h2.type_name='Version' and t2.object_type=10 and h1.object_id=t2.object_id
union all
select h1.object_id,h2.type_name,h1.object_name actname,t2.object_name as aliasname from hsp_object h1,hsp_object_type h2,test2 t2
where h1.object_type in h2.object_type and h2.type_name='Entity' and t2.object_type=10 and h1.object_id=t2.object_id
union all
select h1.object_id,h2.type_name,h1.object_name actname,t2.object_name as aliasname from hsp_object h1,hsp_object_type h2,test2 t2
where h1.object_type in h2.object_type and h2.type_name='User Defined Dimension Member' and t2.object_type=10 and h1.object_id=t2.object_id

GRANT SELECT ON DIMDETAILS TO HSS

come back to HSS schema and join your 3 tables:GrpUsrNames,PlnAppSecfile,DimDetails which is your Final result like,

user belongs to A,B,C groups and has access to Account,Entity,Scenario,CustomDimenion,Forms,BRs etc.

Hope this is useful.