My first blog on 11.1.2!!!!! (feels really interesting…..)
I wanted to experiment on this new release for a longtime but couldn’t get a chance to do that (busy schedule :)). But here I’m writing the 1st blog on 11.1.2 Hyperion Shared Services changes…..
The first noticeable change on all Hyperion Products is the look and feel…..(blue and white color of fusion products is clearly visible) then a lot of changes in the folder structure (I think it’ll be difficult for the admins to relate to the previous versions)…..and then the introduction of OHS as HTTP server….(hope this will get changed in the future release).
As you all know 11.1.2 Shared Services is using relational database as the native user directory (openLDAP is history now). Hope that this adds more reliability to the services.
I was interested in knowing what changes are introduced into relational databases (to replicate ….(user information and preferences)…..so I did some experiments on user addition, group addition….so these are the results…..
To capture the changes I enabled auditing on Oracle (with the help of my dear friend Arun Pillai).
Enable Auditing on Oracle
Using SQLPLUS connect to your Oracle database as sysdba. Issue the following command to check the status of Audit.
SQL> SHOW PARAMETER AUDIT
NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string F:ORACLEPRODUCT10.2.0ADMIN
CERAHYPADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
If the audit_trail is set to none, issue the following SQLPLUS command to enable it.
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.
Now we need to define auditing for the Shared Services database user. My Shared Services user is called as HSSADMIN.
AUDIT ALL BY HSSADMIN BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY HSSADMIN BY ACCESS;
AUDIT EXECUTE PROCEDURE BY HSSADMIN BY ACCESS;
So now we have all the setup to capture the changes made by Shared Services (when a user is added)
Restart the database to start auditing…. (remember to turn this feature off after the experiment)
Now I’m going to add a user in Shared Services.
before
CSS_USERS is the table that stores the Native user information
before
CSS_USERS table after the addition
A closer look reveals that it still follows many of the openLDAP conventions
?USER at the end for the user…
ou=People,dc=css,dc=hyperion,dc=com structure.
I was interested in knowing whether any other table got updated. So I ran the below query to check the changes.
SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = ‘HSSADMIN’ and (ACTION_NAME =’INSERT’ or ACTION_NAME =’UPDATE’)
ORDER BY timestamp;
and found that CSS_IDENTITY table will also get updated when you add a user.
This table contains the IDENTITY_ID and the LOWER_IDENTITY_ID of all users and groups.
For a group
When you add group there are three tables that gets changed.
- CSS_GROUPS
- CSS_IDENTITY
- CSS_GROUP_CACHE_DELTA
When you add a group to a group or a user to a group then in addition to the above 3 tables CSS_GROUP_MEMBERS table also gets updated.
There are many new tables added to Shared Services repository (Essbase clustering information and Smart View Shared Connection information comes from here)
Will post more details on 11.1.2 as I progress with my experiments with the new software.
Good Post looks like you got a good DBA Arun now..
can you put the 11.1.2 installation document also..awaiting for your experiment results…
Hi Celvin,
I appreciate and follow your blog, thanks a lot.
I have a small question. In 11.1.1.3, there is CSSImport utility. In 11.1.2, how can we import User and Group definitions from file system using flat files ?
Kind regards,
İlhan.
Looks good Celvin 🙂
Thanks,
Bakul.
Thanks a lot…Bakul and Maddy….
Ilhan Bey i'm working on something related to this…hopefully will post that by next month….
Good post…..Will be upgrading our environment to system 11.1.2.2 soon. By the way you can mail me the installation doc, if you have any[:)]….Thanks in advance.
–Amit Dutta Roy.
Hi Celvin,
I am not able to get full list of Active directory users ( which are assigned to Native Groups) from CSS_Identity table – as (Name and Provide) col of said table is showing null, What could be other table i should look into it.
PK
It'll be under CSS_GROUPS However you'll not get the name of AD users. It just stores GUID.
You can perform an LCM and get a list. Or you can download NUMSys (link on top of this blog)
Hi Celvin,
We are trying to create audit dashboards and need information on when a user got added to a CSS_Groups from and audit perspective. We have the sql which tell s us who is in what group but missing the audit piece whn a user was added or removed from a particular group.
We are on 11.1.2.3.501, and I was wondering if you have an update on this piece and whether this setting allows for such reporting and if it does whether it can be kept turned on for live reporting.
I appreciate your feedback.
Thanks
Amit
Since you are only dealing with Native Groups, you can query the table CSS_GROUPS for createdtimestamp and updatedtimestamp columns
The groups are native, however the users are on AD, and the what we want to know is when a user is added or removed from a particular group ( I hope I am making sense) 🙂
Thanks Celvin
Nope, there is no such option available.
Hi,
Where can i find the table on the db? i want to back-it up and check the size from time to time.
Thanks