You might be thinking why I’m writing about creating an access control report when Cloud has an inbuilt access control report.
If you are thinking about the access control card from Tools cluster, that is not the one I’m looking for. I’m after the “Access Control” report that On-Premises used to provide, which gives you information on dimension and object-level security.

It is buried under a menu (not evident if you don’t know where to look for).

From System Reports, you can run the access control report.

I took a CSV export and was not happy with the result.

It is a well-formatted report (Similar to the PDF). However, what I was looking for was more of a table structure.
What I genuinely want 🙂
Here is what I was looking for.
- Dimension
- The member that got security
- Group/User
- What type of access
- Which object type
You might be wondering that is similar to the one from Export Security REST API. Yes, you are right; the only extra information there is what dimension each member is coming from.
How can it be done
I can generate an LCM with all the secured dimensions, run the REST API for exporting security and stitch them together 🙂
Prerequisites
We need to create an LCM for exporting the secured dimensions.

Once that is done, you got to do is run the groovy script given below.
Groovy script
package com.oracle.ceal
@Grab('org.apache.derby:derby:10.9.1.0')
@Grab('commons-io:commons-io:2.6')
@GrabConfig(systemClassLoader=true)
import javax.net.ssl.HostnameVerifier
import javax.net.ssl.HttpsURLConnection
import javax.net.ssl.SSLContext
import javax.net.ssl.TrustManager
import javax.net.ssl.X509TrustManager
import java.net.HttpURLConnection
import java.util.zip.ZipFile
import static java.sql.ResultSet.*
import java.nio.file.Files
import java.nio.file.Paths
import groovy.json.*
import groovy.sql.Sql
import groovy.io.FileType
class PbcsRestClient {
private HttpURLConnection connection
private def planningUrl
private def interopUrl
private def proxyHost
private def proxyPort
private def user
private def pwd
private def appName
private def domain
private def ignoreSSLCertsErrors
public PbcsRestClient(planningServerUrl, interopServerUrl,httpProxyHost, httpProxyPort, identityDomain,username, password, applicationName, ignoreSSLCertificationPathErrors) {
planningUrl=planningServerUrl
interopUrl=interopServerUrl
proxyHost=httpProxyHost
proxyPort=httpProxyPort
domain=identityDomain
user=username
pwd=password
appName=applicationName
ignoreSSLCertsErrors=ignoreSSLCertificationPathErrors
}
def setProxyParams() {
Properties systemProperties = System.getProperties()
systemProperties.setProperty("http.proxyHost",proxyHost)
systemProperties.setProperty("http.proxyPort",proxyPort)
systemProperties.setProperty("https.proxyHost",proxyHost)
systemProperties.setProperty("https.proxyPort",proxyPort)
}
def setSSLParams() {
if (ignoreSSLCertsErrors !=null && ignoreSSLCertsErrors.toUpperCase()=="TRUE") {
println "Ignoring SSL certification path errors"
// Disable SSL cert validation
def hostnameVerifier = [
verify: { hostname, session -> true }
]
def trustManager = [
checkServerTrusted: { chain, authType -> },
checkClientTrusted: { chain, authType -> },
getAcceptedIssuers: { null }
]
HttpsURLConnection.setDefaultHostnameVerifier(hostnameVerifier as HostnameVerifier)
SSLContext context = SSLContext.getInstance("SSL")
context.init(null, [trustManager as X509TrustManager] as TrustManager[], null)
HttpsURLConnection.setDefaultSSLSocketFactory(context.getSocketFactory())
}
}
def openConnection(restUrl,method,localFileNameWithPathForStorage) {
println "Opening connection to $restUrl with method:$method"
int statusCode
setProxyParams()
setSSLParams()
URL newUrl
newUrl=new URL(restUrl)
connection = (HttpURLConnection) newUrl.openConnection()
connection.setDoOutput(true)
connection.setDoInput(true)
connection.setUseCaches(false)
if (method=="")
connection.setRequestMethod("GET")
else
connection.setRequestMethod(method)
connection.setRequestProperty("Content-Type","application/x-www-form-urlencoded")
String basicAuth = "Basic ${javax.xml.bind.DatatypeConverter.printBase64Binary("$domain.$user:$pwd".bytes)}"
connection.setRequestProperty("Authorization", basicAuth)
String response=""
try {
statusCode = connection.responseCode
println "Connection status code: $statusCode "
if (statusCode==401) {
println "Not authorized"
}
if (statusCode==200) {
println "Authentication succeeded"
println "Server response:"
println "-----"
response=displayServerResponse(connection,localFileNameWithPathForStorage)
println "-----"
}
if (statusCode==400) {
println "Bad request"
println "Server response:"
println "-----"
response=displayServerResponse(connection,"")
println "-----"
}
} catch (Exception e) {
println "Error connecting to the URL"
println e.getMessage()
} finally {
if (connection != null) {
connection.disconnect();
}
}
return response
}
def displayServerResponse(connection,localFileNameWithPathForStorage) {
InputStream is;
if (connection.getResponseCode()==200) {
is=connection.getInputStream();
} else {
is=connection.getErrorStream();
}
println "Response Content-Type:${connection.getContentType()}"
if (connection.getContentType().contains("application/json")) {
BufferedReader br = new BufferedReader(new InputStreamReader(is));
StringBuilder sb = new StringBuilder();
String line;
while ((line = br.readLine()) != null) {
sb.append(line+"\n");
}
br.close();
println sb
return sb.toString()
} else {
if (connection.getResponseCode()==200) {
//storing content
final int BUFFER_SIZE = 5 * 1024 * 1024;
def fileExt = connection.getHeaderField("fileExtension");
println "Downloading file with fileExtension header:$fileExt"
if (fileExt!=null) {
def saveFilePath = localFileNameWithPathForStorage;
File f = new File(saveFilePath);
is = connection.getInputStream();
FileOutputStream outputStream = new FileOutputStream(f);
int bytesRead = -1;
byte[] buffer = new byte[BUFFER_SIZE];
while ((bytesRead = is.read(buffer)) != -1) {
outputStream.write(buffer, 0, bytesRead);
}
println "Downloaded file to $localFileNameWithPathForStorage"
outputStream.close()
return localFileNameWithPathForStorage
} else {
println "Could not find fileExtension header"
}
}
}
is.close()
return ""
}
def downloadFile(serverFileName,localFolderForStorage) {
println "**Downloading file**"
def restUrl="$interopUrl/applicationsnapshots/$serverFileName/contents"
def response
response=openConnection(restUrl,"GET","$localFolderForStorage/$serverFileName")
println "****"
}
def runJob(payload) {
println "**Exporting security**"
def restUrl="$planningUrl/applications/$appName/jobs/"
def response
println "Payload is $payload"
response=openConnectionWithPayload(restUrl,"POST",payload as String,"application/json")
println "****"
return response
}
def getJobStatus(jobId) {
println "**get Job status**"
def restUrl="$planningUrl/applications/$appName/jobs/$jobId"
def response
response=openConnection(restUrl,"GET","")
println "****"
return response
}
def openConnectionWithPayload(restUrl,method,payload, contentType) {
println "Opening connection to $restUrl with method:$method"
int statusCode
setProxyParams()
setSSLParams()
URL newUrl
newUrl=new URL(restUrl)
connection = (HttpURLConnection) newUrl.openConnection()
connection.setDoOutput(true)
connection.setDoInput(true)
connection.setUseCaches(false)
if (method)
connection.setRequestMethod(method)
else
connection.setRequestMethod("GET")
connection.setRequestProperty("Content-Type",contentType)
String basicAuth = "Basic " + javax.xml.bind.DatatypeConverter.printBase64Binary("$domain.$user:$pwd".bytes)
connection.setRequestProperty("Authorization", basicAuth)
if (payload){
connection.getOutputStream().write(payload.getBytes("UTF-8"))
}
String response=""
try {
statusCode = connection.responseCode
println "Connection status code: $statusCode "
if (statusCode==401) {
println "Not authorized"
}
if (statusCode==200) {
println "Authentication succeeded"
println "Server response:"
println "-----"
response=displayServerResponse(connection,localFileNameWithPathForStorage)
println "-----"
}
if (statusCode==400) {
println "Bad request"
println "Server response:"
println "-----"
response=displayServerResponse(connection,"")
println "-----"
}
if (statusCode==201){
println "Authentication succeeded"
println "Server response:"
println "-----"
InputStream is=connection.getInputStream()
BufferedReader br = new BufferedReader(new InputStreamReader(is))
def object = new JsonSlurper().parseText(br.readLines())
br.close()
println "Processing job"
println "Checking status"
response=object.jobId
println "-----"
}
} catch (Exception e) {
println "Error connecting to the URL"
println e.getMessage()
} finally {
if (connection != null) {
connection.disconnect();
}
}
return response
}
def executeLCMExport(snapshotName) {
println "**Exporting snapshot**"
def typeExport="{type:export}"
def restUrl="$interopUrl/applicationsnapshots/$snapshotName/migration?q=$typeExport"
def response
response=openConnection(restUrl,"POST","")
println "****"
return response
}
def getLCMStatus(restUrl) {
println "**Getting snapshot status**"
def response
response=openConnection(restUrl,"GET","")
println "****"
return response
}
// Helper functions
def unZipWithFolders(fileName, destinationFolder,overWrite){
def ant = new AntBuilder()
def ovrWrite
if (overWrite) {
ovrWrite=overWrite
} else {
ovrWrite = "false"
}
ant.unzip( src:fileName,
dest:destinationFolder,
overwrite:ovrWrite )
}
}
class WaitForCode {
static retry( sleepTime, nbOfRetries, Closure logicToRun){
Throwable catched = null
for(int i=0; i<nbOfRetries; i++){
try {
return logicToRun.call()
} catch(Throwable t){
catched = t
println ("Retrying...")
Thread.sleep(sleepTime)
}
}
println ("Retry count limit exceeded. Stopping check.")
throw catched
}
}
// Check whether table exists, if not create it
def createDerbyTable(Sql sql,sqlCols, tableName){
def metadata = sql.connection.getMetaData()
def table = metadata.getTables(null, null, tableName.toUpperCase(), null)
if (!table.next()) {
sql.execute """CREATE TABLE APP."${tableName.toUpperCase()}" ( ${sqlCols.join(" ,")}) """ as String
} else {
sql.execute """TRUNCATE TABLE APP."${tableName.toUpperCase()}" """ as String
}
}
// derby bulk insert file
def derbyBulkInsert (Sql sql, String tableName, String fullFileName, String columnDelimiter) {
println """**Loading Derby database table $tableName with data from $fullFileName and $columnDelimiter as delimiter **"""
println "-----"
// runs in replace mode since the last argument is a non-zero
sql.execute("CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(null,'" + tableName.toUpperCase() + "','" + fullFileName + "','" + columnDelimiter + "',null,null,1)");
println "Load completed"
println "-----"
println "****"
}
def loadDimensionsAndCreateReport(sql,lcmFolder, reportPath,delimiter, filter) {
try {
// read the listing file for all the dimensions from the listing xml
plnDIMXML = new XmlParser().parse(new File("$lcmFolder/info/listing.xml").toURI().toString())
// find the names of the dimensions and their paths and add to a map
allDims=plnDIMXML.resource.findAll{it.@path.matches("(.*)Dimensions(.*)")}.collectEntries{[it.@name,it.@path]}
if (allDims){
allDims.each { dims ->
dimName = dims.key
dimPath = "$lcmFolder/resource/${dims.value}/${dims.key}.csv"
sqlCols=[]
def dimLine=0
def inputStream = new FileInputStream(dimPath)
def reader = new BufferedReader(new InputStreamReader(inputStream))
// All dimension files got XML code in it.
// The csv information starts after a specific pattern
// We are checking what line the pattern is in
new File(dimPath).eachLine(){line, lineNumber ->
if(line.contains("#--!")) {
dimLine=lineNumber
}
}
// Only going to use two columns for this (Child and Parent)
['Child','Parent'].each{
sqlCols << """"${it}" VARCHAR(2500)"""
}
// Create the table if it doesn't exist
// If it does then truncate the table
createDerbyTable(sql, sqlCols, dimName)
// Using SQL Perpared Statment to load data in bulk
def insertDIMStmt = """ INSERT INTO "${dimName.toUpperCase()}" ("Child","Parent") values (?,?)""" as String
// read each line from the CSV start line
// Planning adds formula to multiple lines, the trick I've done is
// To check for " in the the lines, if it is got a ", then don't use that line
reader.eachLine(){line,lineNumber ->
if (lineNumber > dimLine+1) {
def mbrLines=line.split(",")
if (mbrLines.size()>=2) {
if (!(mbrLines[0].matches("(.*)\"(.*)") || mbrLines[1].matches("(.*)\"(.*)"))){
sql.withBatch(500, insertDIMStmt) { stmt ->
stmt.addBatch(mbrLines[0], mbrLines[1])
}
}
}
}
}
reader.close()
inputStream.close()
// Create an INDEX on Child column
sql.execute("""CREATE INDEX "IDX_$dimName" ON "${dimName.toUpperCase()}" ("Child") """ as String)
}
createAccessReport(sql, reportPath, delimiter, filter)
} else {
println "No dimensions in the LCM"
}
} finally {
sql.close()
}
}
def createAccessReport(sql, reportPath,delimiter, filter) {
// if you are looking for dimension access info
// join with the respective table to find out which dimension it came from
def secRows
if (filter == "SL_DIMENSION"){
def secSQL = []
allDims.keySet().each{
secSQL << """
SELECT distinct '$it' as Dimension, a.* FROM SECACCESS a, "${it.toUpperCase()}" b
WHERE a."OBJECTNAME" = b."Child"
"""
}
writeReportFile = new File(reportPath)
secRows = sql.rows(secSQL.join("UNION ALL"))
} else {
secRows = sql.rows("""SELECT * FROM SECACCESS WHERE "OBJECTTYPE"='$filter' """ as String)
}
if (secRows){
writeReportFile.write(secRows[0].keySet().join(delimiter)) // keySet gives us the column names
secRows.each{result->
cols = result.size()
// Check whether the column type is CLOB, if it is then get the char stream's text
// Groovy ternary operator in action
vals = (0..<cols).collect{(result[it].getClass().name == "oracle.sql.CLOB" ? result[it].getCharacterStream().text.trim() : "${result[it].trim()}")}
writeReportFile.append(System.getProperty("line.separator") + vals.join(delimiter))
}
println "Access report generated"
} else {
println "No access defined"
}
}
// Read configuration script for the environment info.
def keyConfig = new ConfigSlurper().parse(new File('D:/groovy_scripts/Env_Info.config').toURL())
epbcsURL=keyConfig.serverEnvs.pbcs.epbcsURL
epbcsUserName=keyConfig.serverEnvs.pbcs.epbcsUserName
epbcsPassword=new String(keyConfig.serverEnvs.pbcs.epbcsPassword.decodeBase64())
epbcsDomain=keyConfig.serverEnvs.pbcs.epbcsDomain
appName=keyConfig.serverEnvs.pbcs.appName
apiVersion=keyConfig.serverEnvs.pbcs.apiVersion
lcmVersion=keyConfig.serverEnvs.pbcs.lcmVersion
PbcsRestClient pbcsClient=new PbcsRestClient("$epbcsURL/HyperionPlanning/rest/$apiVersion","$epbcsURL/interop/rest/$lcmVersion","","",epbcsDomain,epbcsUserName, epbcsPassword,appName,"TRUE")
// run export security job
// you can filter either users or groups
// "exportGroups": "group1,group2"
// "exportUsers": "user1,user2"
// you cannot sepcify them together
def secJobId=pbcsClient.runJob(JsonOutput.toJson(["jobType" : "Export Security", "jobName" : "ExportSecurity", "parameters":["fileName":"SecurityRecordsFile.csv"]]))
// run LCM export
def lcmJobResponse=pbcsClient.executeLCMExport("Export_SecDimensions")
def lcmObject = new JsonSlurper().parseText(lcmJobResponse)
// get the status of the lcm
// we cannot use job id to check the status as this is migration job
// only one link will have job status link, hence .find
def lcmJobLink = lcmObject.links.find{it["rel"] == "Job Status"}["href"]
// keep checking the status of sec export and dimension LCM file still it is complete
def secJobStatus="Processing"
while (secJobStatus=="Processing"){
def response = pbcsClient.getJobStatus(secJobId)
def object = new JsonSlurper().parseText(response)
secJobStatus=object.descriptiveStatus
sleep 10*1000 //sleep for 10 seconds
}
println "Export security Job $secJobStatus"
def jobStatus=-1
while (jobStatus == -1){
def object = new JsonSlurper().parseText(pbcsClient.getLCMStatus(lcmJobLink))
jobStatus=object.status
sleep 10*1000 //sleep for 10 seconds
}
println "LCM Export status $jobStatus"
if (secJobStatus == "Completed" && jobStatus == 0){
pbcsClient.downloadFile("SecurityRecordsFile.csv", "D:/groovy_scripts")
pbcsClient.downloadFile("Export_SecDimensions", "D:/groovy_scripts")
pbcsClient.unZipWithFolders("D:/groovy_scripts/Export_SecDimensions","D:/groovy_scripts/Dimension Security","true")
// create an in memory instance of derby database
def sql = Sql.newInstance(
url:'jdbc:derby:memory:epbcsAccessDB;create=true',
driver:'org.apache.derby.jdbc.EmbeddedDriver'
)
// Create the table to capture Access
// following are the columns that are present in Export security
def secSQLCols =[]
["OBJECTNAME","NAME","PARENT","ISUSER","OBJECTTYPE","ACCESSTYPE","ACCESSMODE","REMOVE"].each{
secSQLCols << """"${it.trim()}" VARCHAR(2500)"""
}
createDerbyTable(sql, secSQLCols, "SECACCESS")
derbyBulkInsert(sql, "SECACCESS","D:/groovy_scripts/SecurityRecordsFile.csv",",")
sql.execute("""CREATE INDEX "IDX_SECACCESS" ON "SECACCESS" ("OBJECTNAME") """ as String)
/* The following object types are supported in filter */
/* Object Type:
SL_FORM - Form
SL_COMPOSITE - Composite Form
SL_TASKLIST - Tasklist
SL_CALCRULE - Rule
SL_FORMFOLDER - Form Folder
SL_CALCFOLDER - Rule Folder
SL_DIMENSION - Dimension
SL_CALCTEMPLATE - Template
SL_REPORT - Management Report
SL_REPORTSSHOT - Management Report Snapshot
*/
loadDimensionsAndCreateReport(sql,"D:/groovy_scripts/Dimension Security/HP-$appName", 'D:/groovy_scripts/AccessReport.txt',",","SL_DIMENSION")
} else {
println "Export process failed"
}
The script runs the Export security REST API first (line 490), then executes the LCM process to extract secured dimensions (line 493).
It’ll then check whether both processes were successful. (lines 494-515)
If successful, it’ll then download the files (lines 519-520) and unzip the dimension LCM file(line 521).
It loads the files into an in-memory DERBY database.
I’m using a prepared Statement to load the dimensions into their table. (lines 406-422)
Once all the files are loaded, a report is generated with the specified filter.
Output

If you are on the pre 19.05 version. I bet you are not. However, if you are, you can use the following groovy code to get the report.
You need to include the secured dimensions and security in the LCM.


The script is designed to parse the security XML files and load that to a table and create a similar report.
Groovy for Cloud versions pre 19.05
package com.oracle.ceal
@Grab('org.apache.derby:derby:10.9.1.0')
@Grab('commons-io:commons-io:2.6')
@GrabConfig(systemClassLoader=true)
import javax.net.ssl.HostnameVerifier
import javax.net.ssl.HttpsURLConnection
import javax.net.ssl.SSLContext
import javax.net.ssl.TrustManager
import javax.net.ssl.X509TrustManager
import java.net.HttpURLConnection
import java.util.zip.ZipFile
import static java.sql.ResultSet.*
import java.nio.file.Files
import java.nio.file.Paths
import groovy.json.JsonSlurper
import groovy.sql.Sql
import groovy.io.FileType
class PbcsRestClient {
private HttpURLConnection connection
private def planningUrl
private def interopUrl
private def proxyHost
private def proxyPort
private def user
private def pwd
private def domain
private def ignoreSSLCertsErrors
public PbcsRestClient(planningServerUrl, interopServerUrl,httpProxyHost, httpProxyPort, identityDomain,username, password, ignoreSSLCertificationPathErrors) {
planningUrl=planningServerUrl
interopUrl=interopServerUrl
proxyHost=httpProxyHost
proxyPort=httpProxyPort
domain=identityDomain
user=username
pwd=password
ignoreSSLCertsErrors=ignoreSSLCertificationPathErrors
}
def setProxyParams() {
Properties systemProperties = System.getProperties()
systemProperties.setProperty("http.proxyHost",proxyHost)
systemProperties.setProperty("http.proxyPort",proxyPort)
systemProperties.setProperty("https.proxyHost",proxyHost)
systemProperties.setProperty("https.proxyPort",proxyPort)
}
def setSSLParams() {
if (ignoreSSLCertsErrors !=null && ignoreSSLCertsErrors.toUpperCase()=="TRUE") {
println "Ignoring SSL certification path errors"
// Disable SSL cert validation
def hostnameVerifier = [
verify: { hostname, session -> true }
]
def trustManager = [
checkServerTrusted: { chain, authType -> },
checkClientTrusted: { chain, authType -> },
getAcceptedIssuers: { null }
]
HttpsURLConnection.setDefaultHostnameVerifier(hostnameVerifier as HostnameVerifier)
SSLContext context = SSLContext.getInstance("SSL")
context.init(null, [trustManager as X509TrustManager] as TrustManager[], null)
HttpsURLConnection.setDefaultSSLSocketFactory(context.getSocketFactory())
}
}
def openConnection(restUrl,method,localFileNameWithPathForStorage) {
println "Opening connection to $restUrl with method:$method"
int statusCode
setProxyParams()
setSSLParams()
URL newUrl
newUrl=new URL(restUrl)
connection = (HttpURLConnection) newUrl.openConnection()
connection.setDoOutput(true)
connection.setDoInput(true)
connection.setUseCaches(false)
if (method=="")
connection.setRequestMethod("GET")
else
connection.setRequestMethod(method)
connection.setRequestProperty("Content-Type","application/x-www-form-urlencoded")
String basicAuth = "Basic ${javax.xml.bind.DatatypeConverter.printBase64Binary("$domain.$user:$pwd".bytes)}"
connection.setRequestProperty("Authorization", basicAuth)
String response=""
try {
statusCode = connection.responseCode
println "Connection status code: $statusCode "
if (statusCode==401) {
println "Not authorized"
}
if (statusCode==200) {
println "Authentication succeeded"
println "Server response:"
println "-----"
response=displayServerResponse(connection,localFileNameWithPathForStorage)
println "-----"
}
if (statusCode==400) {
println "Bad request"
println "Server response:"
println "-----"
response=displayServerResponse(connection,"")
println "-----"
}
} catch (Exception e) {
println "Error connecting to the URL"
println e.getMessage()
} finally {
if (connection != null) {
connection.disconnect();
}
}
return response
}
def displayServerResponse(connection,localFileNameWithPathForStorage) {
InputStream is;
if (connection.getResponseCode()==200) {
is=connection.getInputStream();
} else {
is=connection.getErrorStream();
}
println "Response Content-Type: ${connection.getContentType()}"
if (connection.getContentType().contains("application/json")) {
BufferedReader br = new BufferedReader(new InputStreamReader(is));
StringBuilder sb = new StringBuilder();
String line;
while ((line = br.readLine()) != null) {
sb.append(line+"\n");
}
br.close();
println sb
return sb.toString()
} else {
if (connection.getResponseCode()==200) {
//storing content
final int BUFFER_SIZE = 5 * 1024 * 1024;
def fileExt = connection.getHeaderField("fileExtension");
println "Downloading file with fileExtension header:$fileExt"
if (fileExt!=null) {
def saveFilePath = localFileNameWithPathForStorage;
File f = new File(saveFilePath);
is = connection.getInputStream();
FileOutputStream outputStream = new FileOutputStream(f);
int bytesRead = -1;
byte[] buffer = new byte[BUFFER_SIZE];
while ((bytesRead = is.read(buffer)) != -1) {
outputStream.write(buffer, 0, bytesRead);
}
println "Downloaded file to $localFileNameWithPathForStorage"
outputStream.close()
return localFileNameWithPathForStorage
} else {
println "Could not find fileExtension header"
}
}
}
is.close()
return ""
}
def downloadFile(serverFileName,localFolderForStorage) {
println "**Downloading file**"
def restUrl="$interopUrl/applicationsnapshots/$serverFileName/contents"
def response
response=openConnection(restUrl,"GET","$localFolderForStorage/$serverFileName")
println "****"
}
def executeLCMExport(snapshotName) {
println "**Exporting snapshot**"
def typeExport="{type:export}"
def restUrl="$interopUrl/applicationsnapshots/$snapshotName/migration?q=$typeExport"
def response
response=openConnection(restUrl,"POST","")
println "****"
return response
}
def getLCMStatus(restUrl) {
println "**Getting snapshot status**"
def response
response=openConnection(restUrl,"GET","")
println "****"
return response
}
// Helper functions
def unZipWithFolders(fileName, destinationFolder,overWrite){
def ant = new AntBuilder()
def ovrWrite
if (overWrite) {
ovrWrite=overWrite
} else {
ovrWrite = "false"
}
ant.unzip( src:fileName,
dest:destinationFolder,
overwrite:ovrWrite )
}
}
class WaitForCode {
static retry( sleepTime, nbOfRetries, Closure logicToRun){
Throwable catched = null
for(int i=0; i<nbOfRetries; i++){
try {
return logicToRun.call()
} catch(Throwable t){
catched = t
println ("Retrying...")
Thread.sleep(sleepTime)
}
}
println ("Retry count limit exceeded. Stopping check.")
throw catched
}
}
// Check whether table exists, if not create it
def createDerbyTable(Sql sql,sqlCols, tableName){
def metadata = sql.connection.getMetaData()
def table = metadata.getTables(null, null, tableName.toUpperCase(), null)
if (!table.next()) {
sql.execute """CREATE TABLE APP."${tableName.toUpperCase()}" ( ${sqlCols.join(" ,")}) """ as String
} else {
sql.execute """TRUNCATE TABLE APP."${tableName.toUpperCase()}" """ as String
}
}
def createAccessReport(sql,lcmFolder,reportPath,delimiter, filter) {
try {
// read the listing file for all the dimensions from the listing xml
plnDIMXML = new XmlParser().parse(new File("$lcmFolder/info/listing.xml").toURI().toString())
// find the names of the dimensions and their paths and add to a map
allDims=plnDIMXML.resource.findAll{it.@path.matches("(.*)Dimensions(.*)")}.collectEntries{[it.@name,it.@path]}
if (allDims){
allDims.each { dims ->
dimName = dims.key
dimPath = "$lcmFolder/resource/${dims.value}/${dims.key}.csv"
sqlCols=[]
def dimLine=0
def inputStream = new FileInputStream(dimPath)
def reader = new BufferedReader(new InputStreamReader(inputStream))
// All dimension files got XML code in it.
// The csv information starts after a specific pattern
// We are checking what line the pattern is in
new File(dimPath).eachLine(){line, lineNumber ->
if(line.contains("#--!")) {
dimLine=lineNumber
}
}
// Only going to use two columns for this (Child and Parent)
['Child','Parent'].each{
sqlCols << """"${it}" VARCHAR(2500)"""
}
// Create the table if it doesn't exist
// If it does then truncate the table
createDerbyTable(sql, sqlCols, dimName)
// Using SQL Perpared Statment to load data in bulk
def insertDIMStmt = """ INSERT INTO "${dimName.toUpperCase()}" ("Child","Parent") values (?,?)""" as String
// read each line from the CSV start line
// Planning adds formula to multiple lines, the trick I've done is
// To check for " in the the lines, if it is got a ", then don't use that line
reader.eachLine(){line,lineNumber ->
if (lineNumber > dimLine+1) {
def mbrLines=line.split(",")
if (mbrLines.size()>=2) {
if (!(mbrLines[0].matches("(.*)\"(.*)") || mbrLines[1].matches("(.*)\"(.*)"))){
sql.withBatch(500, insertDIMStmt) { stmt ->
stmt.addBatch(mbrLines[0], mbrLines[1])
}
}
}
}
}
reader.close()
inputStream.close()
// Create an INDEX on Child column
sql.execute("""CREATE INDEX "IDX_$dimName" ON "${dimName.toUpperCase()}" ("Child") """ as String)
}
// Create the table to capture Access
def secSQLCols =[]
['NAME','OBJECTNAME','OBJECTTYPE','ACCESSMODE','FLAG','ISUSER'].each{
secSQLCols << """"${it.trim()}" VARCHAR(2500)"""
}
createDerbyTable(sql, secSQLCols, "SECACCESS")
// Read the access XML files
def insertSECStmt = 'INSERT INTO SECACCESS (NAME, OBJECTNAME, OBJECTTYPE, ACCESSMODE, FLAG, ISUSER) values (?,?,?,?,?,?)'
new File("$lcmFolder/resource/Security/Access Permissions").eachFileRecurse(FileType.FILES){
plnSECXML = new XmlParser().parse(it.toURI().toString())
sql.withBatch(500, insertSECStmt) { stmt ->
plnSECXML.acl.each{
stmt.addBatch(it.name.text(), it.objectName.text(), it.objectType.text(), it.accessMode.text(), it.flag.text(), it.isUser.text())
}
}
}
sql.execute("""CREATE INDEX "IDX_SECACCESS" ON "SECACCESS" ("OBJECTNAME") """ as String)
// if you are looking for dimension access info
// join with the respective table to find out which dimension it came from
def secRows
if (filter == "SL_DIMENSION"){
def secSQL = []
allDims.keySet().each{
secSQL << """
SELECT distinct '$it' as Dimension, a.* FROM SECACCESS a, "${it.toUpperCase()}" b
WHERE a."OBJECTNAME" = b."Child"
"""
}
writeReportFile = new File(reportPath)
secRows = sql.rows(secSQL.join("UNION ALL"))
} else {
secRows = sql.rows("""SELECT * FROM SECACCESS WHERE "OBJECTTYPE"='$filter' """ as String)
}
if (secRows){
writeReportFile.write(secRows[0].keySet().join(delimiter)) // keySet gives us the column names
secRows.each{result->
cols = result.size()
// Check whether the column type is CLOB, if it is then get the char stream's text
// Groovy ternary operator in action
vals = (0..<cols).collect{(result[it].getClass().name == "oracle.sql.CLOB" ? result[it].getCharacterStream().text.trim() : "${result[it].trim()}")}
writeReportFile.append(System.getProperty("line.separator") + vals.join(delimiter))
}
}
println "Access report generated"
} else {
println "No dimensions in the LCM"
}
} finally {
sql.close()
}
}
// Read configuration script for the environment info.
def keyConfig = new ConfigSlurper().parse(new File('D:/groovy_scripts/Env_Info.config').toURL())
epbcsURL=keyConfig.serverEnvs.pbcs.epbcsURL
epbcsUserName=keyConfig.serverEnvs.pbcs.epbcsUserName
epbcsPassword=new String(keyConfig.serverEnvs.pbcs.epbcsPassword.decodeBase64())
epbcsDomain=keyConfig.serverEnvs.pbcs.epbcsDomain
appName=keyConfig.serverEnvs.pbcs.appName
apiVersion=keyConfig.serverEnvs.pbcs.apiVersion
lcmVersion=keyConfig.serverEnvs.pbcs.lcmVersion
PbcsRestClient pbcsClient=new PbcsRestClient("$epbcsURL/HyperionPlanning/rest/$apiVersion","$epbcsURL/interop/rest/$lcmVersion","","",epbcsDomain,epbcsUserName, epbcsPassword, "TRUE")
// run LCM export
def lcmJobResponse=pbcsClient.executeLCMExport("Export_SecDimensions")
def lcmObject = new JsonSlurper().parseText(lcmJobResponse)
// get the status of the lcm
// we cannot use job id to check the status as this is migration job
// only one link will have job status link, hence .find
def lcmJobLink = lcmObject.links.find{it["rel"] == "Job Status"}["href"]
def jobStatus=-1
while (jobStatus == -1){
def object = new JsonSlurper().parseText(pbcsClient.getLCMStatus(lcmJobLink))
jobStatus=object.status
println jobStatus
sleep 10*1000 //sleep for 10 seconds
}
println "LCM Export status $jobStatus"
if(jobStatus == 0) {
pbcsClient.downloadFile("Export_SecDimensions", "D:/groovy_scripts")
pbcsClient.unZipWithFolders("D:/groovy_scripts/Export_SecDimensions","D:/groovy_scripts/Security Extract","true")
// create an in memory instance of derby database
def sql = Sql.newInstance(
url:'jdbc:derby:memory:epbcsAccessDB;create=true',
driver:'org.apache.derby.jdbc.EmbeddedDriver'
)
/* The following object types are supported in filter */
/* Object Type:
SL_FORM - Form
SL_COMPOSITE - Composite Form
SL_TASKLIST - Tasklist
SL_CALCRULE - Rule
SL_FORMFOLDER - Form Folder
SL_CALCFOLDER - Rule Folder
SL_DIMENSION - Dimension
SL_CALCTEMPLATE - Template
SL_REPORT - Management Report
SL_REPORTSSHOT - Management Report Snapshot
*/
createAccessReport(sql,"D:/groovy_scripts/Security Extract/HP-$appName",'D:/groovy_scripts/AccessReport.txt',",","SL_DIMENSION")
} else {
println "Export process failed"
}
Output

Why couldn’t we just use epmautomate exportappsecuroty command, we get the file in same format.
You can do that. I did mention that you can download the default security report from Planning UI. I didn’t cover the EPMAutomate part of it. Thank you for joining that out. However, this blog post talks about performing custom reporting if you need that extra filtering.