In this blog, I would be talking about Hyperion Shared Services and messing around with the backend tables that make the brains of HSS. Today, we are going to get the users and the groups they belong to using SQL and by checking the backend tables that store this information.
The below snapshot shows the users that exist in my Hyperion EPM system. I have got three users whose name begins with testuser and admin who is the Shared Services administrator.
The information about the users is stored in the table called CSS_USERS which is present in the Hyperion Shared Services schema.
A select statement on this table gives an output as shown below.
The fields of the CSS_USERS table is as shown in the below snapshot.
The important fields are IDENTITY_ID which is the internal ID for the user name, NAME which is the user name, PASSWORD which is the password field.
Observe the last two columns in the above snapshot.
LOWER_IDENTITY_ID and LOWER_NAME which is basically the lowercase of the IDENTITY_ID and the NAME fields in the above table. This is the reason why you can enter your user name in either uppercase or lowercase and it still works. The comparison happens on the LOWER_IDENTITY_ID and LOWER_NAME.
The below snapshot shows the CSS_USERS table that is pivoted. Observe the data in the password field. The password field is encoded using SSH2 algorithm is my guess.
The next snapshot shows two groups that I have created for the REVPLAN application and WORLD group which is the default group in Hyperion.
The next couple of snapshot shows me adding the test users to the groups.
The information of the groups is stored in a table called as CSS_GROUPS as shown in the below snapshot.
The metadata of the CSS_GROUPS table is as shown in the below table.
The pivoted CSS_GROUPS table is as shown in the below snapshot.
Now, while the CSS_USERS table has information of the users in Hyperion EPM and the CSS_GROUPS table has information on the groups created in Hyperion EPM, the association of users to groups is stored in a table called as CSS_GROUP_MEMBERS which is as shown in the below snapshot.
The metadata of the CSS_GROUP_MEMBERS table is as shown in the below snapshot.
In the above snapshot, GROUP_IDENTITY matches the IDENTITY_ID field of the CSS_GROUPS table and the MEMBER_IDENTITY matches the IDENTITY_ID field of the CSS_USERS table.
Thus, by having a SQL join, we can get the information of users and which groups they belong to.
A simple SQL query to get this information is as shown in the above snapshot.
The output of the query is as shown in the below snapshot.
The below snapshot shows the users that exist in my Hyperion EPM system. I have got three users whose name begins with testuser and admin who is the Shared Services administrator.
The information about the users is stored in the table called CSS_USERS which is present in the Hyperion Shared Services schema.
A select statement on this table gives an output as shown below.
Observe the last two columns in the above snapshot.
LOWER_IDENTITY_ID and LOWER_NAME which is basically the lowercase of the IDENTITY_ID and the NAME fields in the above table. This is the reason why you can enter your user name in either uppercase or lowercase and it still works. The comparison happens on the LOWER_IDENTITY_ID and LOWER_NAME.
The below snapshot shows the CSS_USERS table that is pivoted. Observe the data in the password field. The password field is encoded using SSH2 algorithm is my guess.
The next couple of snapshot shows me adding the test users to the groups.
The information of the groups is stored in a table called as CSS_GROUPS as shown in the below snapshot.
In the above snapshot, GROUP_IDENTITY matches the IDENTITY_ID field of the CSS_GROUPS table and the MEMBER_IDENTITY matches the IDENTITY_ID field of the CSS_USERS table.
Thus, by having a SQL join, we can get the information of users and which groups they belong to.
The output of the query is as shown in the below snapshot.
Hi! wonderfull information...do you know if Oracle has an official documentation explaininig all these tables? We need to schedule provisioning reports and we think the only way is through sql querys! Thanks and regards!
ReplyDelete