🔐 Dynamically Creating Cell Level Security in Oracle EPM Using Groovy

In Oracle EPM, Cell Level Security (CLS) is a powerful feature that allows administrators to restrict access to specific data intersections. While CLS can be manually configured, automating this process using Groovy scripting can significantly streamline security management—especially in dynamic planning environments.

In this post, we’ll walk through a Groovy rule that dynamically generates an .xlsx file containing CLS rules based on user input in a form. This file is then zipped and uploaded via REST API to apply the security settings.

🧠 Use Case

We want to:

• Identify intersections in a form where users have marked entities as "lock".
• Generate a CLS rule in Excel format using the Workbook API.
• Upload and apply the rule using the REST API.

🛠️ Key Components

• Groovy Rule: Reads the grid and identifies locked entities.
• Workbook API: Creates an Excel file with CLS rules and sub-rules.
• REST API: Uploads and triggers the import of the CLS file.

📄 Step-by-Step Breakdown

1. Identify Locked Entities

We iterate through the form grid and collect all entities marked as "lock":

List entityList = []
operation.grid.dataCellIterator().each { cell ->
    if(cell.getFormattedValue() == "lock") {
        def entity = cell.getMemberName("Entity")
        entityList << entity
    }
}
def entityCSV = entityList.join(',')

2. Create the Workbook

Using the Workbook API, we create two sheets:

• Rules: Defines the CLS rule metadata.
• Sub Rules: Specifies the actual restrictions and members.

workbook().withCloseable { Workbook book ->
    book.createSheet('Rules').with { sheet ->
        addRow('Name', 'Position', 'Description', ..., 'Dim31 Required')
        addRow('Closed Entities', '1.0', 'Cell level security', ..., '')
    }
    book.createSheet('Sub Rules').with { sheet ->
        addRow('Name', 'Users', 'User Groups', ..., 'Dim31 Exclusion All')
        addRow('Closed Entities', '', 'ALL_PLANNER_GROUP', 'Deny Write', entityCSV, ..., '')
    }
    book.write(byteStream)
}

3. Zip and Upload the File

We zip the workbook and upload it using a REST API call:

zipOutputStream('CellLevelSecurity.zip').withCloseable { ZipOutputStream zipos ->
    def entry = new ZipEntry("CellLevelSecurity.xlsx")
    zipos.putNextEntry(entry)
    zipos.write(workbookBytes)
    zipos.closeEntry()
}

Then, we trigger the import job:

HttpResponse<String> jsonResponse = operation.application.getConnection("CONNECTIONNAME")
    .post("/rest/v3/applications/MYAPPNAME/jobs")
    .body("""
    {
        "jobType": "Import Cell-Level Security",
        "parameters": {
            "fileName": "CellLevelSecurity.zip"
        }
    }
    """)
    .header("Content-Type", "application/json")
    .asString()

4. Monitor the Job

We poll the job status until it completes:

def awaitCompletion(HttpResponse<String> jsonResponse, String connectionName, String operation) {
    ...
    for(long delay = 50; status == IN_PROGRESS; delay = Math.min(1000, delay * 2)) {
        sleep(delay)
        status = getJobStatus(connectionName, (String)ctx.read('$.jobId'))
    }
    ...
}

✅ Final Output

If successful, the script prints:

Cell Level Security Imported Successfully

📌 Conclusion

This Groovy-based approach to dynamically generating and applying Cell Level Security in Oracle EPM:

• Reduces manual effort
• Ensures consistency
• Enables real-time security updates based on user input

By leveraging the Workbook API and REST integration, you can build a robust and scalable CLS automation framework.