Oracle have rightly gone down the route of replacing OpenLdap with using a RDBMS source, it is simple but an effective replacement and has been incorporated into the Foundation relational storage piece. This now fits in line with a wide range of the Oracle EPM product set using a RDBMS engine to store its information.
You may wonder why it was not done sooner.
If you have a look in your Foundation (Shared Services) database you will see 13 tables prefixed with CSS
These tables are now the core to provisioning with Shared Services. I will cover some of the tables in more detail shortly.
Another big change in this release with regards to provisioning is that there is no need to refresh essbase security once a new user/group is provisioned with a role against essbase in Shared Services.
This means the user/group information that was stored in the essbase.sec file is now being pushed into Shared Services tables and essbase has been updated to communicate directly with Shared Services.
The security refresh of essbase from Shared Services has always been an irritant and the removal of it is a welcome change.
So let me prove it does work and at the same time show you what is happening in the Shared Services relational tables.
If you are not interested in the tables and the SQL generated (and there is plenty of it) for provisioning then now is a good time to leave and do something far more interesting.
First a native user is created in Shared Services.
The creation of a native user will generate 2 records being written to 2 tables, the first table is CSS_IDENTIFY where it stores the native user value and a lower case version of it.
The SQL that is generated is :-
insert into "CSS_IDENTITY"("IDENTITY_ID","LOWER_IDENTITY_ID") values ('native://nvid=871ba2ae9678192d:-6132c2e4:1287e41a072:-7ed3?USER',
'native://nvid=871ba2ae9678192d:-6132c2e4:1287e41a072:-7ed3?user');
If you ever took a look at previous releases and checked out the code generated for a native OpenLdap user/group it would look something like :-
native://DN=cn=4e3bfafb605e4946:-7f90ab6e:11ff233f82d:-7b85,ou=People,dc=css,dc=hyperion,dc=com?USER
So the format of the value has changed, I am not yet sure how this will affect migrations from earlier releases to 11.1.2, I know there is no upgrade path from previous versions though I am not sure if that is meant to include migrations, it is something I will testing in the near future.
The second record that is inserted is into the table CSS_USERS, this table holds user information such as username, password, email etc..
The SQL generated is a long the lines of :-
insert into “CSS_USERS"("IDENTITY_ID","NAME","PASSWORD","FIRSTNAME","LASTNAME",
"DESCRIPTION","EMAIL","ACCOUNTSTATUS","CREATEDBY","UPDATEDBY",
"CREATEDTIMESTAMP","UPDATEDTIMESTAMP","LOWER_IDENTITY_ID",
"LOWER_NAME") values ('native://nvid=871ba2ae9678192d:-6132c2e4:1287e41a072:-7ed3?USER',
'essuser','{SSHA}G/iurdcY1Aoe24xnt4m8yQqqLuJgd1Bb',NULL,NULL,NULL,
NULL,'1','admin','admin',TO_DATE('09-MAY-10', 'DD-MON-RR'),
TO_DATE('09-MAY-10', 'DD-MON-RR'),
'native://nvid=871ba2ae9678192d:-6132c2e4:1287e41a072:-7ed3?user',
'essuser');
Ok, so that is the sequence when a native user is created so now let’s provision the user with an essbase role.
This time one record is written to table CSS_PROVISIONING_INFO
The table holds the provisioning information for a user/group and links back to the CSS_USERS table by column MEMBER_IDENTITY to IDENTITY_ID
The SQL generated follows the format of :-
insert into “CSS_PROVISIONING_INFO"("APPLICATION_ID","MEMBER_IDENTITY","ROLE_IDENTITY","MEMBER_TYPE",
"CREATEDBY","CREATEDTIMESTAMP","LOWER_APPLICATION_ID",
"LOWER_MEMBER_IDENTITY","LOWER_ROLE_IDENTITY")
values ('ESB:Analytic Servers:win2008x64:1',
'native://nvid=871ba2ae9678192d:-6132c2e4:1287e41a072:-7ed3?USER',
'native://DN=cn=ESB:0009,ou=ESB,ou=Roles,dc=css,dc=hyperion,dc=com?ROLE',
'1','admin',TO_DATE('09-MAY-10', 'DD-MON-RR'),
'esb:analyticservers:win2008x64:1',
'native://nvid=871ba2ae9678192d:-6132c2e4:1287e41a072:-7ed3?user',
'native://dn=cn=esb:0009,ou=esb,ou=roles,dc=css,dc=hyperion,dc=com?role');
The application and role information are contained in columns APPLICATION_ID and ROLE_IDENTITY, in this case it is describing the Essbase Server name and the role of “Server Access”, I will go into more detail shortly on how this table links with another table to get more meaningful role information from the ROLE_IDENTITY column.
Look no refresh from Shared Services
Anyway in EAS expand security and take a look at the users you will see that the user is instantly added, no need to refresh security.
And you will be able to log straight into essbase without any issues.
If you check the essbase log output you will notice it is slightly different from previous versions, when logging in the directory that the user belongs to e.g. @Native Directory is logged and when logging out the native id is also logged.
This is how it gets logged in previous version.
If you export the security file then you will see the user information still gets written to it, it is just that it does not used in the same way anyway.
I was also interested in seeing if the provisioning of user for planning has changed in this release, previously if you provisioned an existing essbase user for a planning application and then had a look at the security in EAS it would not show the user as an “Essbase and Planning” access type until you either ran a planning refresh, executed the provisionusers utility or the user logged into the planning application.
The existing essbase user was provisioned for the PLANSAMP planning application. This would create a new record in the CSS_PROVISONING_INFO table and the SQL generated is similar to the one shown earlier.
insert into “CSS_PROVISIONING_INFO"("APPLICATION_ID","MEMBER_IDENTITY","ROLE_IDENTITY","MEMBER_TYPE",
"CREATEDBY","CREATEDTIMESTAMP","LOWER_APPLICATION_ID",
"LOWER_MEMBER_IDENTITY",
"LOWER_ROLE_IDENTITY") values ('HP:PLANSAMP',
'native://nvid=871ba2ae9678192d:-6132c2e4:1287e41a072:-7ed3?USER',
'native://DN=cn=HP:0001,ou=HP,ou=Roles,dc=css,dc=hyperion,dc=com?ROLE',
'1','admin',TO_DATE('09-MAY-10', 'DD-MON-RR'),
'hp:plansamp',
'native://nvid=871ba2ae9678192d:-6132c2e4:1287e41a072:-7ed3?user',
'native://dn=cn=hp:0001,ou=hp,ou=roles,dc=css,dc=hyperion,dc=com?role');
This time the Application id is “HP:PLANSAMP”.
The users information is instantly updated in EAS so it does prove that essbase is now talking directly with Shared Services.
There is no change in regards to the user information being written to the planning applications HSP_USERS table when a user is provisioned against a planning application, it still requires the user to log in or a refresh to be run to populate the table.
The only difference is the new style native SID is used.
So what about an Active Directory user, what happens when they are provisioned in Shared Services.
There is no real difference in the way Active Directory information is accessed in Shared Services.
The active directory user is provisioned for two roles, an essbase calc role and a planner role.
A record is inserted into the CSS_IDENTITY table with the OBJECTGUID that relates to the user.
insert into “CSS_IDENTITY"("IDENTITY_ID","LOWER_IDENTITY_ID") values ('msad://OBJECTGUID=\a9\1a\6a\81\fc\64\d3\42\a9\69\43\11\6a\96\a5\a4?USER','msad://objectguid=\a9\1a\6a\81\fc\64\d3\42\a9\69\43\11\6a\96\a5\a4?user');
No record is inserted into the CSS_USERS table as the user already exists in the AD and the user information can be queried directly from the AD using the OBJECTGUID.
Like with a native user a record is inserted into the CSS_PROVISIONING_INFO table for each role that has been applied.
Essbase Sample – Calc Role
insert into "CSS_PROVISIONING_INFO"("APPLICATION_ID","MEMBER_IDENTITY","ROLE_IDENTITY","MEMBER_TYPE",
"CREATEDBY","CREATEDTIMESTAMP","LOWER_APPLICATION_ID",
"LOWER_MEMBER_IDENTITY","LOWER_ROLE_IDENTITY") values ('ESBAPP:Sample_win2008x64_1',
'msad://OBJECTGUID=\a9\1a\6a\81\fc\64\d3\42\a9\69\43\11\6a\96\a5\a4?USER',
'native://DN=cn=ESBAPP:0005,ou=ESBAPP,ou=Roles,dc=css,dc=hyperion,dc=com?ROLE',
'1','admin',TO_DATE('15-MAY-10', 'DD-MON-RR'),
'esbapp:sample_win2008x64_1',
'msad://objectguid=\a9\1a\6a\81\fc\64\d3\42\a9\69\43\11\6a\96\a5\a4?user',
'native://dn=cn=esbapp:0005,ou=esbapp,ou=roles,dc=css,dc=hyperion,dc=com?role');
Planning – PlanSamp – Planner role
insert into "CSS_PROVISIONING_INFO"("APPLICATION_ID","MEMBER_IDENTITY","ROLE_IDENTITY","MEMBER_TYPE",
"CREATEDBY","CREATEDTIMESTAMP","LOWER_APPLICATION_ID",
"LOWER_MEMBER_IDENTITY","LOWER_ROLE_IDENTITY") values ('HP:PLANSAMP',
'msad://OBJECTGUID=\a9\1a\6a\81\fc\64\d3\42\a9\69\43\11\6a\96\a5\a4?USER',
'native://DN=cn=HP:0001,ou=HP,ou=Roles,dc=css,dc=hyperion,dc=com?ROLE',
'1','admin',TO_DATE('15-MAY-10', 'DD-MON-RR'),
'hp:plansamp',
'msad://objectguid=\a9\1a\6a\81\fc\64\d3\42\a9\69\43\11\6a\96\a5\a4?user',
'native://dn=cn=hp:0001,ou=hp,ou=roles,dc=css,dc=hyperion,dc=com?role');
And if you check security in EAS you will see the AD user has been instantly provisioned with Essbase and Planning Roles.
Once again no need for a refresh and logging in as the AD user will produce the same sort of logging information in essbase as with a Native user.
So what about Groups?
A new native group was created.
And the user essuser was added to the group, once this was applied the following SQL is generated and executed.
insert into "CSS_IDENTITY"("IDENTITY_ID","LOWER_IDENTITY_ID") values ('native://nvid=2c571170edfbf9c6:-6429c4d1:128cb73e13b:-7ed2?GROUP',
'native://nvid=2c571170edfbf9c6:-6429c4d1:128cb73e13b:-7ed2?group');
This creates the native identity of the group.
insert into “CSS_GROUPS"("IDENTITY_ID","NAME","DESCRIPTION","CREATEDBY","UPDATEDBY",
"CREATEDTIMESTAMP","UPDATEDTIMESTAMP","LOWER_IDENTITY_ID",
"LOWER_NAME") values ('native://nvid=2c571170edfbf9c6:-6429c4d1:128cb73e13b:-7ed2?GROUP',
'essgroup',
NULL,'admin','admin',TO_DATE('24-MAY-10', 'DD-MON-RR'),
TO_DATE('24-MAY-10', 'DD-MON RR'),
'native://nvid=2c571170edfbf9c6:-6429c4d1:128cb73e13b:-7ed2?group',
'essgroup');
This creates the information about the group e.g. name, description and identity.
insert into “CSS_GROUP_MEMBERS"("GROUP_IDENTITY","MEMBER_IDENTITY","MEMBER_TYPE",
"LOWER_GROUP_IDENTITY",
"LOWER_MEMBER_IDENTITY") values ('native://nvid=2c571170edfbf9c6:-6429c4d1:128cb73e13b:-7ed2?GROUP',
'native://nvid=871ba2ae9678192d:-6132c2e4:1287e41a072:-7ed3?USER','1',
'native://nvid=2c571170edfbf9c6:-6429c4d1:128cb73e13b:-7ed2?group',
'native://nvid=871ba2ae9678192d:-6132c2e4:1287e41a072:-7ed3?user');
This assigns the user (essuser) by identity to the group (essgroup) by identity.
There are another couple of insert to the table CSS_GROUP_CACHE_DELTA, I am not quite sure what this table is used for, obviously some sort of caching but I didn’t think important to show the SQL generated.
So how do you find out a role name and description from the role identity written to the provisioning table.
The two records that were written above the role identities were:-
Essbase Calc - native://dn=cn=esbapp:0005,ou=esbapp,ou=roles,dc=css,dc=hyperion,dc=com?role
Planning planner role -
native://dn=cn=esbapp:0005,ou=esbapp,ou=roles,dc=css,dc=hyperion,dc=com?role
To do this the CSS_ROLE_LOCALES table can be queried.
The NAME and DESCRIPTION columns are the fields you will be interested in, you will also need to filter on the Locale otherwise records will be returned for each locale (n different locales)
If you combine the provisioning info and locale table you can return role information by user.
So if you want to start generating provisioning reports then it is relatively simple to query the tables directly.
The burning question that was niggling at me was do the products now communicate directly with the relational tables or do they go via the Shared Services web app in anyway. The easy was to find out was to stop the Foundation Managed Server.
Once stopped, I fired up essbase.
No problems there, looks to have started.
A quick check of the SharedServices_Security_Client.log (in \Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\essbase\essbase) and you can see essbase directly communicating with the shared services relational source using JDBC and a message indicates the CSS system is fully initialized.
Another test logging in as an AD user, so it does look like essbase communicates directly with the relational information and does not go through the HSS web app.
I also tested EAS, Studio and Planning and they both followed the same communication method, obviously anything that uses workspace would need the Foundation managed server to be started.
I must stress that I am not saying to start using the system like this; it was just to prove a point.
It also indicates that if you are having problems logging in to the system then the first place to check is that the rdbms database is up and running and you can communicate with it across the network.
Well there we have it a quick look at life after OpenLdap, definitely the way forward.