Exporting Security Out of Hyperion Planning Application via Oracle Database Repository

I found the below article after hours of searching for a solution for us. I hope this helps others.

EPM 11.1.2 – Exporting Security out of Hyperion Planning application
By Ruben Verghese on Sep 03, 2011
copied from https://blogs.oracle.com/pa/entry/exporting_security_out_of_hyperion

You can extract security information out planning application using the following script on Planning SQL tables,

SELECT A.OBJECT_ID AS GROUP_ID,
 A.OBJECT_NAME AS GROUP_NAME,
 D.USER_ID,
 E.OBJECT_NAME AS USER_NAME,
 D.ROLE,
 D.SYNC_PSWD,
 FROM
 HSP_OBJECT A,
 HSP_OBJECT_TYPE B,
 HSP_USERSINGROUP C,
 HSP_USERS D,
 HSP_OBJECT E

Where

A.OBJECT_TYPE=B.OBJECT_TYPE
 AND A.OBJECT_ID=C.GROUP_ID
 AND C.USER_ID=D.USER_ID
 AND D.USER_ID=E.OBJECT_id
 AND B.OBJECT_TYPE ='6'
 ORDER BY A.OBJECT_ID

[ad#ad-post]