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.
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.
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.
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!!