UPDATE 7/31/19
I would look to use the REST API to load and extract security now. This method will still work but has been improved by Oracle.
When I first started working on Hyperion Planning, clients and consultants would call the hotline and ask how they could see their existing security because back in the 1.0 days it was not available. So I dove into the database and wrote a Microsoft Access database that enabled consultants and clients the ability to see their security. We distributed the database and instructions if people asked nicely or loudly until the feature was written into the tool.
The Oracle EPBCS/FCCS platform does not come with a nice way of exporting and importing object security the way the on-prem version of Hyperion Planning had with its command line utilities. I decided to take a stab at hopefully making it easier.
EXPORTING SECURITY
This entry will focus on exporting the current security of an application and formatting it into a csv file so you can easily edit the security or view its contents.
I am going to focus on group security since it is a best practice to apply security at a group level. You can follow this process for users as well.
The first step is to extract the current application security using the migration component of EPBCS. You can use a complete application extract or just extract the security. Below I am just extracting the group security.
Once the migration is executed download it to your machine. Unzip the file and you will find your currently assigned security in super fun XML files for each group.
Here is what it looks like in XML.
Its pretty simple XML but not great to edit. Now that you have the files I have written a Powershell script that will convert all of the files in a directory to a combined csv file. By no means am I a Powershell master but I like it because its extremely quick, and handles XML, JSON, and CSV formats extremely well. Generally you will find it installed on most new servers or clients. Here is some documentation to enable it on windows.
It can also run on your mac or linux environment.
You can download the script here. Once you download it place it in a directory where you plan to execute it from.
The script accepts two parameters. The first is the directory where the XML files are stored and the second is the name of the file you want to create. If you dont put a path the file will be saved in the same directory as where you execute the script. The parameters should be separated with a space.
Once you launch the shell navigate to the directory you have saved the script in.
Here is the command :
./sec2csv.ps1 “V:POWERSHELL/BLOG/HP-TOYS/resource/Security/Access Permissions” security.csv
Place quotes around the directory.
UPDATE
I have updated the script to search the whole directory and create a report combining your user access and group access.
It will look like this in the shell.
Once the script runs it will generate your export.
It has now combined all of your group files into one csv file that will look like this.
The information in this file will be very similar to what was required for the import and export utility in planning. The definition of these fields can be found here. The only one that is really different is the IsUser field. For group security this will always be N.
CONCLUSION
I hope you find this first part of this conversion helpful. I am testing the CSV to XML script now, it has been working great and I should have that up soon. I hope this helps you manage security in the cloud!!
You da man! Great utility
Thanks Larry! Get ready for May 4th!!
I see you don’t monetize your page, don’t waste your traffic, you can earn additional
cash every month because you’ve got high quality content.
If you want to know how to make extra $$$, search for:
Boorfe’s tips best adsense alternative
I currently have a client that requires explicit security for more than a thousand members. Thank you for both this and the sec2xml utility, Anthony. You’re a lifesaver!
Hi Anthony,
I hope all is well! I am looking to devise a strategy for a sound ODI 12c-centric integration solution for automating Security with PBCS. There is some literature on this (noted at the end of this abstract) but it is very high-level and perhaps overly simplistic – I am in need of a little more information/details to get me confident to move forward with an elegant and robust solution.
We will be aiming to integrate an On-Premise ODI 12c solution with EPBCS (current is an on-premise Planning application).
The Current State
1. The current state (on-premise ODI with on-premise Planning) makes use of the ImportSecurity utility to load the SecFile.txt. Shared Services in Planning is updated when the ImportSecurity.cmd file is run on the server with the latest security.
2. Contract is a dimension, and we have security enabled for the contracts (Dimension members) each contract (each has its own COR (person tied to ). Users are assigned to security groups that have designated READ, WRITE etc access to certain members, descendants of members.
3. ODI Load Plan steps are initiated in sequence to get the load to the final target load state. A series of MDX scripts called by Windows bat and SQL procedures/packages are invoked to get the security and its users/CORs to be loaded and reflected properly in Shared Services.
4. New Contracts/Users to Security Groups process is controlled via LDAP and Active Directory. COR user security for the Planning application is generated automatically with a connection through Microsoft Active Directory. An LDAP query is run to pull all user first and last names and corresponding network IDs from Active Directory (AD). The COR Contract Mapping step is used to populate the SPENDPLN_COR_Contract_Mapping table, where CORs are matched to their corresponding contracts. This step also creates the SPENDPLN_COR_Security_Group_Import.mxl file on SD1 which contains the code to add users to contract groups. The second step, Execute SPENDPLN_COR_Security_Group_Import.mxl executes the MAXL file to add the new security in Shared Services.
Risks/Considerations In Need Of Triage
1. I am aware that many would suggest to move everything over to LCM in EPM Automate on the application side (non-ODI) – however a lot of the current functionality is controlled in ODI. Given the SQL transformations that occur to manipulate flat files and data for final transformation and load into EPBCS via the load utility – I would hate to have to re-develop this entire solution on the application side.
2. There is no LDAP for EPBCS – so how would an ODI-centric design authenticate the user when importing entries from ODI into EPBCS?
3. I am aware that EPM Automate replaces the use of MaxL for these purposes – would this require the use of EPM Automate solely to automate this process? I am going to assume custom Groovy scripts will have to be devised in JSON format to physically load the data in .json to EPBCS from ODI?
4. Is this design going to require sole RESTful API design – not just EPM Automate wrapper – for this functionality to work? What RESTful calls will I need to replace what the Load Utility does?
5. Performance-wise – can RESTful handle in just one API call a load file with multiple records for each load – or would this require several asynchronous RESTful calls for every record in a flat file?
6. What Groovy scripts will I need to devise likely for this to work?
7. Are there max security groups an instance of EPBCS can handle?
Conclusion
Overall, just looking for some sound guidance to determine how this can work as I’d hate just to maintain all security in EPBCS-alone with EPM Automate . Any help to set the guideline from a high-level abstract would be appreciated!
Are you trying to automate the adding of users to the system and groups or the metadata security in the application?
Hi Anthony, it was nice explanation. Can you please share the Powershell script for .xml to .csv conversion. When i am trying to down load it was not happening.
This link should work. https://www.dropbox.com/s/1g72rhniwfnffte/sec2csv.ps1?dl=0
I think that REST may offer better ways of doing this now.