1. Using Oracle Essbase
  2. Understand Your Access Permissions in Essbase

4 Understand Your Access Permissions in Essbase

How you work with Essbase depends on your user role and application-level permissions.

In Essbase, there are three user roles:

The majority of Essbase users have User role. Power User and Service Administrator roles are reserved for those who require permission to author and maintain applications. Users with User role are granted application-level permissions that distinguish their access to data and permissions in each application.

Depending on your security provider, user roles and permissions may be different, and you may manage them outside of Essbase. Likewise, you may manage users and groups outside of Essbase.

See About Identity Providers.

Security Provider Add, remove, and manage users and groups Provision and deprovision roles
EPM Shared Services security mode In the Shared Services Console In the Shared Services Console
External security configured in WebLogic In the external provider In the Essbase web interface or REST API
WebLogic Embedded LDAP In the Essbase web interface or REST API In the Essbase web interface or REST API

Note:

WebLogic Embedded LDAP is not recommended for production environments.

EPM Shared Services security mode

The following Essbase web interface items are disabled in EPM Shared Services security mode:

  • The Security page (there is no Security icon in the Essbase web interface)

    Essbase users and groups are stored directly in EPM Shared Services and are not added or managed in the Essbase web interface.

  • The Permissions tab in the application inspector
  • The Reset Password option on the Admin menu

External security configured in WebLogic

If you are using an external security provider configured in WebLogic, Essbase users and groups are stored directly in the external provider and are not added or managed in the Essbase web interface. However, you provision and deprovision roles in the Essbase web interface or through the REST API.

The following Essbase web interface items are enabled when using external security configured in WebLogic:

  • The Security page (there is a Security icon in the Essbase web interface)
  • The Roles tab on the Security page (the Users and Groups tab is disabled)
  • The Permissions tab in the application inspector
  • The Reset Password option on the Admin menu

Note:

If you need to clean up inactive users/groups from Essbase after they have been removed or renamed on the external provider, use the MaxL Drop User and Drop Group statements.

WebLogic Embedded LDAP (an internal LDAP that is part of WebLogic, and is not recommended for production use):

Use the Security page (the Security icon on the Applications page) in the Essbase web interface or use the REST API to manage users and groups and to provision and deprovision roles.

User Role

If your user role in Essbase is User with no application permissions, you can use the Files catalog (specifically, the shared, users, and gallery folders), download desktop tools from the Console, and explore the Academy to learn more about Essbase.

You must be granted additional access to applications by Power Users or Service Administrators. Applications are structures that contain one or more cubes, also known as databases. You can see only applications and cubes for which you have been granted application permissions.

You can have a unique application permission for each application on the server. Application permissions, from least privileged to highest, are:

Database Access Permission

If your user role in Essbase is User and you have Database Access permission for a particular application, you can view data and metadata in the cubes within the application.

Your ability to view data and metadata may be limited in areas that are restricted by filters. You may be able to update values in some or all areas of the cube, if someone has granted you write access using a filter. You can use drill through reports, if any exist, to access sources of data outside the cube, as long as a filter does not restrict your access to the cells within the drillable region.

With Database Access permission, you can also view the cube outline, and download files and artifacts from the application and cube directories. Job types you can run include building aggregations (if the cube is an aggregate storage cube), and running MDX scripts. Using the Console, you can view database size and monitor your own sessions.

If you are a scenario participant, you can view base data as well as scenario changes, and if you are a scenario approver, you can approve or reject the scenario.

Database Update Permission

If your user role in Essbase is User and you have Database Update permission for a particular application, you can make updates to the cubes within the application.

With Database Update permission for a particular application, you can do everything that a user with Database Access permission can do. Jobs you can run include loading, updating, and clearing data in the cube. You can export the cube data to tabular format. You can run any calculation scripts that you have been granted permission to execute. You can create, manage, and delete your own scenarios in block storage cubes that are enabled for scenario management.

Database Manager Permission

If your user role in Essbase is User and you have Database Manager permission for a particular application, you can manage the cubes within the application.

With Database Manager permission for an application, you can do everything that a user with Database Update permission can do. Additionally, you can upload files to the cube directory, edit the cube outline, export the cube to an application workbook, and start/stop the cube using the web interface. Job types you can run include building dimensions, exporting data, and exporting the cube to a workbook.

As a Database Manager, you also have access to the database inspector, which gives you control of even more cube operations. To open the database inspector from the web interface, start with the Applications page, and expand the application. From the Actions menu to the right of the name of the cube you want to manage, click Inspect to launch the inspector.

Using the database inspector, you can:

  • Enable scenarios or change the number of scenarios allowed
  • Manage dimensions, including generation and level names
  • Access and manage files related to the database
  • Create and edit calculation scripts, drill through reports, MaxL scripts, MDX scripts, report scripts, and rules files for dimension building and data loading
  • Assign users permissions to execute calculation scripts
  • Create and assign filters to grant or restrict data access for specific users and groups. You can assign filters, for your cube, to any users or groups who are already provisioned to use the application (an Application Manager or higher must provision users).
  • Manage cube-level substitution variables
  • View locked cube objects and data blocks
  • View and change database settings
  • View database statistics
  • View and export audit records from the web interface

Application Manager Permission

If your user role in Essbase is User and you have Application Manager permission for a particular application, you can manage the application and cubes.

With Application Manager permission for a particular application, you can do everything that a user with Database Manager permission can do, for all cubes in the application. Additionally, you can make copies of any cubes within the application. You can copy or delete the application if you are the owner (the power user who created it), and you can delete any of the cubes in the application, if you are the cube owner (the power user who created it). You can start/stop the application using the web interface, and you can view and terminate user sessions in the Console. Job types you can run include running MaxL scripts, and using Export LCM to back up cube artifacts to a zip file.

Using the database inspector, you can manage cubes in your application the same way that a Database Manager can, and additionally, you can purge audit records for cubes.

As an Application Manager, you also have access to the application inspector, which gives you control of even more operations. To open the application inspector from the web interface, start with the Applications page. From the Actions menu to the right of the name of the application that you manage, click Inspect to launch the inspector.

Using the application inspector, you can:

  • Access and manage files related to the application
  • Manage application-level connections and Datasources for access to external sources of data
  • Change application configuration settings
  • Provision and manage user and group permissions for the application and its cubes
  • Add and remove application-level substitution variables
  • Change general application settings
  • View application statistics
  • Download application logs

Power User Role

Power User is a special user role that enables you to create applications on an Essbase service.

If you are a power user, you are automatically granted Application Manager privilege for applications you created. Your options for creating applications and cubes include creating them from scratch in the Applications page of the web interface, importing from an application workbook, building from Cube Designer, and using the LCM Import job (or the lcmimport CLI command).

You can delete and copy applications that you created.

As a power user, you can be assigned permission to work on applications that you did not create. If your assigned permission is lower than Application Manager, then your actions are restricted to the actions permitted for the application permission you were assigned. For example, if you are assigned Database Manager permission to an application created by another power user, then your access is restricted to what a User with Database Manager permission can do.

Service Administrator Role

A Service Administrator has unlimited access to Essbase.

If you are a service administrator, you can do everything that power users and Application Managers can do, for all applications and cubes. Additionally, you can manage users and groups, using the Security page in the web interface. From the Analyze view for any cube, you can execute MDX reports impersonating other users (using Execute As) to test their access.

From the Console, you can manage connections and Datasources at the server level, configure e-mail settings for scenario management, and manage logs, the antivirus scanner, all user sessions, and system configuration. You can also view statistics for all databases, add and remove global substitution variables, access Performance Analyzer to monitor service usage and performance, and view/change any service-level settings.

Unlike Power User, the Service Administrator role cannot be restricted. Service administrators always have full access to all applications and cubes on the Essbase server.

About Filters

Filters control security access to data values in a cube. Filters are the most granular form of security available.

When you create a filter, you designate a set of restrictions on particular cube cells or on a range of cells. You can then assign the filter to users or groups.

Your own security role determines if you can create, assign, edit, copy, rename, or delete filters:

  • If you have the Application Manager role, then you can manage any filter for any user or group. Filters do not affect you.
  • If you have the Database Update role, then you can manage filters for the applications that you created.
  • If you have the Database Manager role, then you can manage filters within your applications or cubes.
  • If you have the Database Access role (default), then you have read access to data values in all cells, unless your access is further restricted by filters.

Create Filters

You can create multiple filters for a cube. If you edit a filter, modifications made to its definition are inherited by all users of that filter.

See Controlling Access to Database Cells Using Security Filters.

  1. On the Applications home page, expand the application.
  2. From the Actions menu, to the right of the cube name, launch the inspector.
  3. Select the Filters tab.
  4. Click Add Image of the add icon..
  5. Enter a filter name in the Filter Name text box.
  6. In the Filter Editor, click Add Image of the add icon.
  7. Under Access, click and use the drop-down menu to select an access level.

    • None: No data can be retrieved or updated

    • Read: Data can be retrieved but not updated

    • Write: Data can be retrieved and updated

    • MetaRead: Metadata (dimension and member names) can be retrieved and updated

      The MetaRead access level overrides all other access levels. Additional data filters are enforced within existing MetaRead filters. Filtering on member combinations (using AND relationships) does not apply to MetaRead. MetaRead filters each member separately (using an OR relationship).

  8. Select the row under Member Specification and enter member names.

    You can filter members separately, or you can filter member combinations. Specify dimension or member names, alias names, member combinations, member sets that are defined by functions, or substitution variable names, which are preceded by an ampersand (&). Separate multiple entries with commas.

  9. Create additional rows for the filter as needed.

    If filter rows overlap or conflict, more detailed cube area specifications apply over less detailed, and more permissive access rights apply over less permissive. For example, if you give a user Read access to Actual and Write access to Jan, then the user would have Write access to Jan Actual.

  10. Click Validate to ensure that the filter is valid.
  11. Click Save.

On the Filters tab in the inspector, you can edit a filter by clicking the filter name and making your changes in the Filter Editor.

You can copy, rename, or delete a filter by clicking the Actions menu to the right of the filter name and choosing an option.

After creating filters, assign them to users or groups.

Create Efficient Dynamic Filters

You can create dynamic filters based on external source data to reduce the number of filter definitions needed.

Instead of managing a set of hard-coded data-access filters for many users, you can filter access to cube cells from external source data, based on member and user names.
You do this using dynamic filter definition syntax, including the method @datasourceLookup and the variables $LoginUser and $LoginGroup. Your external source data is a csv file or a relational table. For relational source data, you can load the .csv to a relational table.
Dynamic Filter Syntax

Use dynamic filter syntax to create flexible filters you can assign to multiple users and groups.

Filter rows can contain the following elements as part of their definition, in addition to member expressions.

$loginuser

This variable stores the value of the current logged in user at runtime. It can be used in conjunction with the @datasourcelookup method.

$logingroup

This variable stores the value of all the groups that current logged-in user belongs to. It includes both direct and indirect groups. When used in conjunction with the @datasourcelookup method, each group is individually looked up against the Datasource.

@datasourcelookup

This method fetches records from a Datasource.

Syntax

@datasourcelookup (dataSourceName, columnName, columnValue, returnColumnName)
Parameter Description
dataSourceName

The name of the external Datasource defined in Essbase. For an application-level Datasource, prefix the name with the application name and a period.

columnName

The name of the Datasource column to search for a given columnValue.

columnValue

The value to search for in columnName.

returnColumnName

The name of the Datasource column from which to return a list of values.

Description

A @datasourcelookup call is equivalent to the following SQL query:

select returnColumnName from dataSourceName where columnName=columnValue

@datasourcelookup looks up the given Datasource and searches for records where columnName contains columnValue. If you specify columnValue as $loginuser, this method will search for records where columnName contains the name of the currently logged in user.

Essbase forms the filter definition row by combining the list elements as a comma-separated string. If any record contains special characters, spaces, or only numbers, they are enclosed in quotation marks.

Examples

Enclose the parameters within quotation marks.

The following call looks up a global Datasource, and returns a list of store names where Mary is the store manager.

@datasourceLookup("StoreManagersDS","STOREMANAGER","Mary","STORE")

The following call looks up an application-level Datasource, and returns a list of store names where the currently logged in user is the store manager.

@datasourceLookup("Sample.StoreManagersDS","STOREMANAGER","$loginuser","STORE")

The following call looks up an application-level Datasource, and returns a list of store names where the store department matches any of the groups to which the logged in user belongs.

@datasourceLookup("Sample.StoreManagersDS","STORE_DEPARTMENT","$logingroup","STORE")

If the logged in user belongs to 3 groups, then the above @datasourcelookup method returns all the matching column values for each group.

Workflow to Create Dynamic Filters

Use the following general workflow to create dynamic filters.

This dynamic filters workflow assumes you already have a cube, and have provisioned users and groups.

  1. Identify a source of data, whether it is a file or a relational source.
  2. Define the connection and the Datasource in Essbase, either globally or at the application level.
  3. Create filters at the cube level, using the Filters section of the database inspector.
  4. Define filter rows for each filter, using the dynamic filter syntax to employ the $loginuser variable, the $logingroup variable, and the @datasourcelookup method as needed.
  5. Assign the filters to users or groups.
  6. If you assigned the filter to a group, assign the group to the application to be filtered, using the Permissions section of the application inspector.
Example of a Dynamic Filter

The following dynamic filter works with the cube called Efficient.UserFilters, available in the gallery as a sample template.


Dynamic filter with three rows granting metaread access to the logged in user.

To learn how to create and apply this dynamic filter, download the workbook template, Efficient_Filters.xlsx, from the Technical section of the gallery, and follow the README instructions in the workbook. The gallery is available in the Files section of the Essbase web interface.