Where does EAS store user information

I have been meaning to write up this blog for a long time but never got around to it, recently there was a post on the Oracle forums which kick started me into finally addressing the topic.

In the pre 11.1.2 world of EAS it was simple to find out what users, server, profiles were being used in EAS as the information was all stored in XML files within the EAS storage directory.

User information was stored in a file called users.xml, opening the file provides all the users that had logged into EAS and some of their credentials.

For each of the users there will be a directory which contains server information and profile information.

Opening the servers.xml file will display the Essbase server information which has been added by the user in the EAS console.

So nice and simple to understand what is happening with user information but as version 11 was quickly evolving and maturing the way the information was being stored changed from version 11.1.2.0

It is sensible to assume that the credentials were moved into relational repository such as the EAS or Shared Services databases, many of the old style properties files were heading into the Shared Services database so maybe this is the location of where the information is being held.

Searching through the database tables you will not find any of the user details and only configuration type information.

A clue to where the information is being held occurs if you happen to be hit with users disappearing from EAS or there are problems suddenly starting up the web application server.

Researching these problems and looking through Oracle Support they both point to a problem with the credential store cwallet.sso file which is held within the application server domain.

“A credential store is a repository of security data (credentials). A credential can hold user name and password combinations, tickets, or public key certificates. Credentials are used during authentication, when principals are populated in subjects, and, further, during authorization, when determining what actions the subject can perform.”

A good example of proving this file is linked to EAS is try logging in with a new user in the EAS console or say add a new Essbase server and you will see the modified date update as the changes are applied.

The question to why the wallet file was chosen over storing the credentials in the relational database I am not sure on and the only reason I can think of is because of the standalone options available with Essbase and EAS, though to be honest after all the years of pain with the Essbase security file it wouldn’t be my first choice to go down a binary file route.

Well that is all well and good knowing the details might be kept in the file but what is more important is accessing this information and understanding how it is stored.

There are multiple ways of accessing the internals of the file and I will go through a few of the options as some methods are better than others.

The first stop for me was the orapki utility:

“The orapki utility is provided to manage public key infrastructure (PKI) elements, such as wallets and certificate revocation lists, from the command line.”

The utility is available in:
<MIDDLEWARE_HOME>\oracle_common\bin\

Displaying the information can be achieved by running the following from command line

orapki wallet display -wallet <path_to_wallet>\cwallet.sso

The output confirms there is information in the wallet which relates to EAS users, servers and profiles but does not provide much more than that.

Just for reference there are also credentials for ODI (which are used by FDMEE) and oracle web services manager stored in the file.

So how about accessing the credentials through Enterprise Manager Fusion Middleware Control which is installed and deployed by default from 11.1.2.2, for previous 11.1.2 versions it is possible to deploy it which I covered in a past blog.

The credential wallet can be accessed in EM by right clicking the EPMsystem domain and selecting Security > Credentails.

Viewing the wallet using this method provides a much clearer vision and understanding on how the information is being stored.

The information is held in a structure which is based on maps and keys and each key can be a generic or password type.

“A credential is uniquely identified by a map name and a key name. Typically, the map name corresponds with the name of an application and all credentials with the same map name define a logical group of credentials, such as the credentials used by the application. All map names in a credential store must be distinct.”

All the EAS related keys are stored under the map CSF_EAS_MAP and all keys are of the generic type.

At the moment we only really have the same information as when using the orapki utility but the added advantage is using EM it is possible to edit the keys so let’s see what is in the EPM_EAS_USER key.

[<EASUser  id="1" username="admin" password="2l0fKnpc78AIKpmB/I08qA==" supervisor="true" fullName="" email="epmadmin@epmmail.com" roles="" external="true" isMigrated="true" identity="native://DN=cn=911,ou=People,dc=css,dc=hyperion,dc=com?USER" />]

Nice, the user is contained in the credential and looks to be in a similar format to the way it was stored in pre 11.1.2 versions

If there are multiple users then these will all be stored in the one credential for example:

[<EASUser id=”1”……/><EASUser id=2”…./>]

How about the server information, well this is slightly different and the key name relates to the EAS user id, so id=”1” would match to the key CSF_EAS_MAP_EPM_EAS_USER_SERVERS_1

Multiple EAS users mean multiple server keys and the same goes for profiles.

The key is stored differently to that of the users as it stores a property name and value.

Editing the Key provides the next stumbling block as it does not visibly display the server information though in reality you wouldn’t really want to have to go into EM and into each key to extract the details as it would be time consuming and is too manual for my liking.

Are there any other options available?  Well reading through the Oracle security (OPSS) documentation there is the following useful bit of information:

“Oracle Platform Security Services includes the Credential Store Framework (CSF), a set of APIs that applications can use to create, read, update, and manage credentials securely.”

So maybe by putting together a little bit of code it could help in displaying the EAS credentials.

Before I attempt this I thought it would be wise to configure the EAS web application to use a separate wallet file so there is no chance of screwing up the file which is shared by other products, this method is usually suggested when experiencing issues with users being lost from EAS because the wallet is being overwritten by other applications accessing and updating it.

To do this there are a couple of configuration files which should be copied from the within the domain to a new location for use with the new wallet file:

jps-config,xml  (JPS=Java Platform Security)

“This file can be seen as the lookup services registry for OPSS. Among these services are login modules, authentication providers, authorization policy providers, credential stores and auditing services.”

system-jazn-data.xml

“This is the default configuration file for file-based identity and policy stores in Oracle Platform Security.”

Next the new location of the jps-config.xml file has to be updated in the property which is passed into the EAS java web application.

If it is a Windows environment then registry is updated with the new value and for Unix the setCustomParamsEssbaseAdminServices.sh script.

Starting the EAS web application should automatically create a new wallet file.

Analysing the EAS application log (based on 11.1.2.3) shows that because the MAP and keys don’t yet exist they are created.

This can also be verified using the orapki utility

There will be no keys created for servers until a user logs into EAS and add an Essbase server.

On to the Java code to output the EAS credential information, now I am not going to go into depth about how it works as if you spend a little time researching it is not that difficult to do.

Please note I can’t confirm whether any of the following is supported and don’t hold me responsible for corrupting the wallet.

To be able to access the wallet a JPS configuration file is required providing the path to the wallet, I created a simple file which only contained details for the credential store.

The required security Java classes are all available under:
<MIDDLEWARE_HOME>\oracle_common\modules

Basically the path and filename for the JPS configuration file are passed in as an argument at runtime, the wallet file is then read and then all keys and credentials that are part of the EAS map are outputted.

As the wallet has just been created and no users have accessed EAS the following information is extracted.

Not very interesting yet so let’s login into EAS.

Run the code again:

This time the user credentials have been extracted from the wallet.

Now to add an Essbase server using "Single Sign On"

Run again:

Interesting by using the API method the server information is fully displayed.

Once the user has logged out of EAS the profile key is either updated or created.

How about adding an Essbase server without using "Single Sign On"

This time the server information is added to the same key.

The password doesn’t look to be encrypted either when adding a server in this way.

Extracting the information is great but I wanted to know if it was possible to add an Essbase server directly to the wallet, I dug around a bit and found the required Java classes then modified the code so that the user and server to be added are passed in as an argument.

The server looked to be successfully added to the wallet but the ultimate test was to log back into EAS and check.

Well there we have it the server is now available to the user and it could have easily been added for multiple users or if required a server could have been removed from specified users.

Hopefully this post has provided an insight to how EAS stores user information and gives you the power to report and manage this.

LCM 11.1.2.3 now supports planning essbase data (after patching)

A quick update from me as I noticed an 11.1.2.3 patch was recently released for Shared Services:

Patch 17307819: PSU 11.1.2.3.050

The patch mainly addresses the long awaited LCM backward compatibility to 11.1.1.4 but requires patch 11.1.1.4.500 to be applied which was also released at the same time.

I was looking through the list of defect fixes and the following caught my attention:

17348625 - Support is needed for the migration of Essbase data for Planning application using Lifecycle Management.

Over time LCM has matured into a well-established tool which does simplify the whole migration piece and is streets ahead of the world we used to live in, yes it does have some drawbacks and bugs but with each release it has improved and now supports the majority of EPM products.

LCM has supported Essbase data migration since 11.1.2.0 but this was for pure Essbase applications and the functionality has been missing for planning applications until now.

The patch requires applying on all servers in an EPM environment and updates not only Shared Services but EPMA and Planning as well.

Once applied new Essbase data artifacts are available for planning application in Shared Services.

The data migration supports both BSO and ASO planning applications.

The BSO export is a full text data export in column format, the ASO export is of course level 0 (not in column format)

 The export is the same as using LCM with Essbase applications except with planning the txt file extension is included while with essbase there is no file extension though it is still text format.

If the LCM planning export includes the Essbaase data artifact then the output content will not be zipped and be in exploded format as a folder.

If the planning essbase databases are large in size then be aware the export could take a while to complete and the database will be in read-only mode while the export is active.

It is worth understanding how the export is processed as it could potentially cause problems.

The export looks to be executed from the planning server and at first is exported to the
<EPM_ORACLE_INSTANCE>\tmp directory

 The export file is then renamed and moved to the LCM import/export directory.

If the export is large then this could impact the available space on the epm instance drive and add additional network traffic as the file is moved around.

It is possible to change the location of the tmp directory by updating java temporary directory for the planning web application server.

On windows this can be achieved by updating the registry.

For Unix type deployments then setCustomParamsPlanning.sh can be updated.

I did also notice a slight issue if the account running the planning managed server does not have access to the LCM import/export directory (in my case the directory was on a share) then the LCM process will complete successfully but the LCM Essbase Data folder will not exist, the data export will still run to the temporay directory but that is as far as it goes even though it does not fail, this does not happen for any of the other planning artifacts and did throw me for a while but updating the permissions resolved the problem.

When importing the data using LCM the data is not cleared out and it is the equivalent of importing with overwrite values.

There is no option to clear the data which would be nice and I have always thought it would be great if there was an option to completely clear out the target planning application before running an LCM import, maybe one for the future?

If importing to a planning ASO database then any aggregations will need to be run as this is not handled by LCM.

I did test using the EPM clone utility which I blogged about here and can confirm once patched it does automatically export/import the planning data.

EPM 11.1.2.3 clone utility

Whenever I complete a build of an environment I test out basic functionality of the installed products with a set of sample applications, reports and integrations, if there are a number of environments to build this process can sometimes be a little tedious so I was looking for a way to speed up and simplify the process.

In 11.1.2.3 a new command line utility appeared which allows the cloning of environments and it basically uses LCM (Lifecycle Management) as the engine to achieve this.

I have known about the utility since it was released but never got round to testing it out and was wondering whether it could help with my situation.

In theory the utility could be used to clone any environment and possibly take scheduled snapshots.

There are some points before using the utility worth highlighting:

• It only operates against LCM enabled products. (if you are not sure which are enabled then have a read here)
  
  
• It does export Essbase and HFM data and as it operates in serial this can not only be an extremely slow process the size of the export could be huge. It doesn’t look like there is a parameter to exclude the data.
  
  
• LCM does not yet export Planning data so this needs to be addressed separately.
  (Shared Services Patch 11.1.2.3.050+ supports planning data)
  
  
• You can only import in an environment where there are no applications for Planning, Financial Management, and Profitability and Cost Management.
  
  
• It does not export deployment metadata which makes sense as you wouldn’t want to transfer that across environments unless you wanted to corrupt it.

Before I put the utility to the test I created two like for like 11.1.2.3 environments and in the first environment I created the following:

• Shared Services provisioned users.
• Essbase applications (BSO/ASO) with data, rules, scripts, report, variables.
• Default sample planning application which was initialized and refreshed.
• Calculation Manager rules, rulesets and variables.
• Full statutory HFM sample application with data.
• Financial Reporting reports for Essbase, Planning and HFM.
• FDMEE integrations for Essbase, Planning and HFM

The Planning and HFM application were both classic and not EPMA which I will explain the reason for later.

These sample applications should give a good representation of a standard EPM deployment and provide a simple test for the cloning utility.

The utility is broken into export – epm_cloneexport.bat/sh and import - epm_cloneimport.bat/sh and these can be found in

<MIDDLEWARE_HOME>\user_projects\<instancename>\bin

To run the utility there is only additional piece that is required and that is a properties file which contains the admin username and password.
 

To run the export from command line the following syntax is required.

epm_cloneexport.bat properties_filename

Once executed the utility will cycle through all the available artifacts in the environment and export them.

The properties file is updated and the password is encrypted after the first use so there are no security implications.

The full export log is available at:

<MIDDLEWARE_HOME>\user_projects\<instancename>\diagnostics\logs\migration

The utility outputs one folder named EPM_CloneExport which is in the environments LCM import/export location.

Beneath the EPM_CloneExport folder the output is broken into each product area just like with any LCM export.

 In Shared Services you can access the full export under the File System.

The options available are exactly the same as standard with LCM and it is possible to use them at full export level or by individual product.

In Shared Services viewing the Migration Status Report will also display the output from running the utility.

Right so that is the export taken care of the next step is to copy the EPM_CloneExport folder over to LCM import/export directory on the target environment, as usual I hit the windows path limit on some of the folders so used robocopy to move them over to the other environment.

Before running the import using the clone utility I created the planning applications data source with the same name as the source as the utility will not be able to create the planning application without this in place.

I then edited the Users.csv file which contains the native users and removed the admin user as otherwise it will update the password for the admin account with the same password as the source environment

 To run the import from command line the properties file needs recreating and then the following syntax is required:

epm_cloneimport.bat properties_filename

Once executed the utility will cycle through all the tasks defined in the import.xml file (automatically generated from the export) and due to the different artifact dependencies it should hopefully import them in the correct order. 

 
 It is possible to edit the xml file and remove tasks if they are not required for the import.

The import acts in the same way as the export and produces a log in the same location and the results are viewable in the Migration Status Report within Shared Services.

The report is indicating that the import failed and by selecting the failed status it provides further information.

The import failed on just two Essbase custom defined functions which are used by Calculation Manager, the comment field can only be a maximum of 256 characters and both these functions seem to have comments which are longer.

I am not sure how Calculation Manager manages to register them in the first place in the source environment if they exceed the limit.

Even though the CDF LCM files don’t have a file extension they are all in XML format and can be opened with any text editor.

As a workaround the comment can be updated in the file to shorten the length to less than 256 characters and then imported through the standard LCM mechanism through Shared Services.

If you get hit with the following error then in most cases it can be ignored.

The reason the error is generated is if the source environment does not have any associated artifacts available then it will not create a resource directory, when importing the utility will try to locate and directory and as it does not exist generate an error.

Once the import has successfully completed then there may be a few manual updates to carry out for example if the Financial Reporting database connections are different between source and target such as HFM cluster name.

For FDMEE you would need to copy application data files and update the root folder values

Now I have a cloned environment without too much hassle but remember this was with classic applications and not EPMA ones, the reason I left the applications as classic is that usually with LCM and EPMA the applications have be created through EPMA by deploying before importing the remaining applications artifacts, maybe the utility had somehow got around this but I would be shocked if it had.

I ran another clone export from the source environment but this time I had converted the planning application to EPMA before running the utility.

I cleared down the target environment by reverting to a previous snapshot, copied the EPM_CloneExport folder across, fixed the Essbase CDF issue and then ran the clone import utility again.

As I expected the import failed with the following errors:

Basically because the planning application is now EPMA enabled the application definition file will be at the EPMA level and not the planning application level, as the application does not yet exist LCM tries to create the application as classic but fails because the definition file is not there.

The FDMEE artifacts then fail to import because the planning application does not exist.

How about if I try deploying the application from EPMA after the failure?

This creates the planning application so I can try running the clone import again.

The import will not run because one of the utility rules:

• You can only import in an environment where there are no applications for Planning, Financial Management, and Profitability and Cost Management.

It is interesting that the error states “Please run the epm environment reset script”, I am not sure which script this is referring to but if anybody does know then please let me know.

This is where the clone utility exposes a major flaw as how it is possible to clone an environment which has EPMA enabled applications if it can’t create the applications and the applications can’t already exist.

Once again when working with EPMA you feel the pain.

I suppose the workaround would be after the import fails to import the application and any other artifacts that failed through the standard LCM route.

Using this method is not idea but at least it should be successful.

Alternatively for my testing strategy I could create the applications as classic in the source environment then run the clone export and finally convert them to EPMA.

Well there we have it if there are only classic applications in the environment the utility works quite well but if EPMA is in the mix then it adds in additional complications.

If in future releases the utility can get around this issue and provide the option to export with or without data it could prove to be quite useful.

11.1.2.3 Planning – email configuration

To configure the email server in all planning releases prior to 11.1.2.3 it would be done through the system settings area in each application and this had to be done by the application owner.

Moving on to 11.1.2.3 and you will notice a subtle change in the system settings.

The email server option is not available anymore and if you are a planning administrator you may be wondering where it has gone.

From the release of version 11 it has been Oracle’s objective to get as much configuration information held centrally within the Shared Services registry which means all product components can share this information and there is no need for duplication which makes perfect sense.

At last planning has now moved a little step forward and can access the email configuration directly from the Shared Services registry so it only needs to be configured once and will be in sync with other EPM products.

So how is it configured, well if you have been involved with any of the EPM configuration then you will no doubt know the answer and that is it can be achieved using the EPM system configurator.

If you are not sure how to access the configurator then have a read here.

Once the configurator has started only select “Configure Common Settings”

You will notice there are more email configuration options available than you will previously be used to with planning, some of the major complaints in the past with the planning email configuration was the mail server port could not be selected and anonymous authentication was the only option so this is a welcome change at last.

The biggest question I had was does the authentication option work as just because it is there a common setting it doesn’t mean it would necessarily be available to planning, it is something that I will shortly test out.

Once the email properties have been applied these will be stored in the Shared Services registry.

You can verify this by running a registry report with the epmsys_registry command line utility

The property values can also be updated directly using the utility, an example to update the email server hostname would be:

epmsys_registry.bat updateproperty SHARED_SERVICES_PRODUCT/@SMTPHostName mailserverhostname

Though for some reason there are two properties which seem to be the same SMTPHostName and SMTPMailServer so both should be updated.

To update the authentication password then the addencryptedproperty can be used which encrypt the password before writing to the registry:

epmsys_registry.bat addencryptedproperty SHARED_SERVICES_PRODUCT/@SMTPServerPassword password

It is also true that it is possible to export/import registry information using LCM though the important caveat being it doesn’t look to encrypt the password on import.

Anyway time to test out whether the email functionality is successful in planning with the authentication option enabled.

Unfortunately it seems to be that email addresses still have to be manually configured in planning and there is no option to bring this through from Shared Services which personally I think should be fully integrated by now.

For the test I enabled the due date option for a task list entry and then monitored the email server.

The email server logs confirmed the email was sent and authentication was successful.

A check of the planners email account confirmed the email was delivered so at least I know the functionality is definitely working.

Patch available for 11.1.2.3 EAS web console bug with Java 7 update 45

Recently I posted a blog about an issue with 11.1.2.3 EAS web console and Java 7 update 45, if the update is applied then the web console is blank when opened and cannot be used.

I just noticed that there has been new patch set updates (11.1.2.3.003) released for Essbase related products which includes the following:

Patch 17609518: PATCH SET UPDATE: HYPERION ESSBASE ADMINISTRATION SERVICES SERVER 11.1.2.3.003

The one bug fix in the PSU is to address the Java issue:

17649604 - After Java 7 Update 45 has been updated, EAS console cannot start via Web Launcher.

I thought I would test out the patch and it has resolved the issue with the Java update.

The remaining Essbase related 11.1.2.3.003 patches are:

Patch 17609530: PATCH SET UPDATE: HYPERION ESSBASE RTC 11.1.2.3.003

Patch 17609535: PATCH SET UPDATE: HYPERION ESSBASE SERVER 11.1.2.3.003

Patch 17609493: PATCH SET UPDATE: HYPERION ANALYTIC PROVIDER SERVICES 11.1.2.3.003

Patch 17609497: PATCH SET UPDATE: HYPERION ESSBASE ADMIN SERVICES CONSOLE MSI 11.1.2.3.003

There are few interesting bug fixes for Essbase server:

17649547 - In some cases, after a dense restructure the size of the Essbase index files grows more than expected.

17649545 - During a dense restructure, the restructuring in some cases can fail and give the following error message: Corrupted Node Page in the B+tree.   [adIndPromoteSafeWrite] aborted

For Provider Services:

17275594 - In some cases, when retrieving with Smart View Client can result in getting an error: Binary Spreadsheet Table Token Error