Controlling Access to Database Cells

In This Section:

Introduction

Understanding How Filters Define Permissions

Creating Filters

Managing Filters

Assigning Filters

Introduction

When security levels defined for applications, databases, users, and groups are insufficient, Essbase security filters give you more specific control. Filters enable you to control access to individual data within a database by defining what kind of access is allowed to which parts of the database, and to whom these settings apply.

If you have Administrator permissions, you can define and assign any filters to any users or groups. Filters do not affect you.

If you have Create/Delete Applications permissions, you can assign and define filters for applications that you created.

If you have Application Manager or Database Manager permissions, you can define and assign filters within your applications or databases.

Understanding How Filters Define Permissions

Filters control security access to data values, or cells. You create filters to accommodate security needs for specific parts of a database. When you define a filter, you designate restrictions on particular database cells. When you save the filter, you give it a unique name to distinguish it from other filters, and the server stores it in essbase.sec, the security file. You can then assign the filters to any users or groups on the server.

For example, a manager designs a filter named RED and associates it with a database to limit access to cells containing profit information. The filter is assigned to a visiting group called REVIEWERS, so that they can read, but cannot alter, most of the database; they have no access to Profit data values.

Filters comprise one or more access settings for database members. You can specify the following access levels and apply them to data ranging from a list of members to one cell.

Access Level

Description

None

No data can be retrieved or updated for the specified member list.

Read

Data can be retrieved but not updated for the specified member list.

Write

Data can be retrieved and updated for the specified member list.

Metaread

Metadata (dimension and member names) can be retrieved and updated for the corresponding member specification.

Note:

The metaread access level overrides all other access levels. If additional filters for data are defined, they are enforced within any defined metaread filters.If you have assigned a metaread filter on a substitution variable and then try to retrieve the substitution variable, an unknown member error occurs, but the value of the substitution variable gets displayed. This is expected behavior.Metadata security cannot be completely turned off in partitions. Therefore, do not set metadata security at the source database; otherwise, incorrect data may result at the target partition.When drilling up or retrieving on a member that has metadata security turned on and has shared members in the children, an unknown member error occurs because the original members of the shared members have been filtered. To avoid this error, give the original members of the shared members metadata security access.

Any cells that are not specified in the filter definition inherit the database access level. Filters can, however, add or remove access assigned at the database level, because the filter definition, being more data-specific, indicates a greater level of detail than the more general database access level.

Data values not covered by filter definitions default first to the access levels defined for users and, when Essbase is in native security mode, second to the global database access levels.

Calculation access is controlled by permissions granted to users and groups. Users who have calculate access to the database are not blocked by filters—they can affect all data elements that the execution of their calculations would update. When Essbase is in native security mode, calculation access is also controlled by minimum global permissions for the application or database.

Creating Filters

You can create a filter for each set of access restrictions you need to place on database values. You need not create separate filters for users with the same access needs. After you have created a filter, you can assign it to multiple users or groups of users. However, only one filter per database can be assigned to a user or group.

Note:

If you use a calculation function that returns a set of members, such as children or descendants, and it evaluates to an empty set, the security filter is not created. An error is written to the application log stating that the region definition evaluated to an empty set.

Before creating a filter, perform the following actions:

  • Connect to the server and select the database associated with the filter.

  • Check the naming rules for filters in Limits.

*  To create a filter, use a tool:

Tool

Topic

Location

Administration Services

Creating or Editing Filters

Oracle Essbase Administration Services Online Help

MaxL

create filter

Oracle Essbase Technical Reference

    Filtering Members Versus Filtering Member Combinations

    Figure 147, How Filters Affect Data AND/OR Relationships illustrates different ways to control access to database cells. Data can be protected by filtering entire members or by filtering member combinations.

    • Filtering members separately affects whole regions of data for those members.

    • Filtering member combinations affects data at the member intersections.

      Figure 147.  How Filters Affect Data AND/OR Relationships

       How Filters Affect Data AND/OR Relationships

    Note:

    Filtering on member combinations (AND relationship) does not apply to metaread. Metaread filters each member separately (OR relationship).

    Filtering Members Separately

    To filter all the data for one or more members, define access for each member on its own row in Filter Editor. Filter definitions on separate rows of a filter are treated with an OR relationship.

    For example, to block access to Sales or Jan, assume that user KSmith is assigned this filter:

    Access

    Member Specification

    None

    Sales

    None

    Jan

    The next time user KSmith connects to Sample.Basic, she has no access to data values for the member Sales or for the member Jan. Her spreadsheet view of the profit margin for Qtr1:

    Figure 148. Results of Filter Blocking Access to Sales or Jan

    Results of Filter Blocking Access to Sales or Jan

    All data for Sales is blocked from view, as well as all data for January, inside and outside of the Sales member. Data for COGS (Cost of Goods Sold), a sibling of Sales and a child of Margin, is available, with the exception of COGS for January.

    Filtering Member Combinations

    To filter data for member combinations, define the access for each member combination using a row in Filter Editor. In filter definitions, two member sets separated by a comma are treated as union of those two member sets (an AND relationship).

    For example, assume that user RChinn is assigned this filter:

    Access

    Member Specification

    None

    Sales, Jan

    The next time user RChinn connects to Sample.Basic, she has no access to the data value at the intersection of members Sales and Jan. Her spreadsheet view of the profit margin for Qtr1:

    Figure 149. Results of Filter Blocking Access to Sales, Jan

    Results of Filter Blocking Access to Sales, Jan

    Sales data for January is blocked from view. However, Sales data for other months is available, and non-Sales data for January is available.

    Filtering Using Substitution Variables

    Substitution variables enable you to more easily manage information that changes regularly. Each substitution variable has an assigned name and value. The Database Manager can change the value anytime. Where a substitution variable is specified in a filter, the substitution variable value at that time is used.

    For example, if you want a group of users to see data only for the current month, you can set up a substitution variable named CurMonth and define a filter (MonthlyAccess) wherein you specify access, using &CurMonth for the member name. Using an ampersand (&) at the beginning of a specification identifies it as a substitution variable instead of a member name to Essbase. Assign the MonthlyAccess filter to the appropriate users.

    Each month, you need to change only the value of the CurMonth substitution variable to the member name for the current month, such as Jan, Feb, and so on. The new value will apply to all assigned users.

    See Using Substitution Variables.

    Filtering with Attribute Functions

    You can use filters to restrict access to data for base members sharing a particular attribute. To filter data for members with particular attributes defined in an attribute dimension, use the attribute member in combination with the @ATTRIBUTE function or the @WITHATTR function.

    Note:

    @ATTRIBUTE and @WITHATTR are member set functions. Most member set functions can be used in filter definitions.

    For example, assume that user PJones is assigned this filter:

    Access

    Member Specification

    None

    @ATTRIBUTE(“Caffeinated_False”)

    The next time user PJones connects to Sample.Basic, he has no access to the data values for any base dimension members associated with Caffeinated_False. His spreadsheet view of first-quarter cola sales in California:

    Figure 150. Results of Filter Blocking Access to Caffeine-free Products

    Results of Filter Blocking Access to Caffeine-free Products

    Sales data for Caffeine Free Cola is blocked from view. Note that Caffeine Free Cola is a base member, and Caffeinated_False is an associated member of the attribute dimension Caffeinated (not shown in the above spreadsheet view).

    Metadata Filtering

    Metadata filtering provides an additional layer of security in addition to data filtering. With metadata filtering, an administrator can remove outline members from a user's view, providing access only to those members that are of interest to the user.

    When a filter is used to apply MetaRead permission on a member,

    1. Data for all ancestors of that member are hidden from the filter user’s view.

    2. Data and metadata (member names) for all siblings of that member are hidden from the filter user’s view.

    Managing Filters

    You can perform the following actions on filters: viewing, editing, copying, renaming, and deleting.

    Viewing Filters

    *  To view a list of filters, use a tool:

    Tool

    Topic

    Location

    Administration Services

    Creating or Editing Filters

    Oracle Essbase Administration Services Online Help

    MaxL

    display filter

    Oracle Essbase Technical Reference

    ESSCMD

    LISTFILTERS

    Oracle Essbase Technical Reference

      Editing Filters

      *  To edit a filter, use a tool:

      Tool

      Topic

      Location

      Administration Services

      Creating or Editing Filters

      Oracle Essbase Administration Services Online Help

      MaxL

      create filter

      Oracle Essbase Technical Reference

        Copying Filters

        You can copy filters to applications and databases on any Essbase Server, according to your permissions. You can also copy filters across servers as part of application migration.

        *  To copy a filter, use a tool:

        Tool

        Topic

        Location

        Administration Services

        Copying Filters

        Oracle Essbase Administration Services Online Help

        MaxL

        create filter

        Oracle Essbase Technical Reference

        ESSCMD

        COPYFILTER

        Oracle Essbase Technical Reference

          Renaming Filters

          *  To rename a filter, use a tool:

          Tool

          Topic

          Location

          Administration Services

          Renaming Filters

          Oracle Essbase Administration Services Online Help

          MaxL

          create filter

          Oracle Essbase Technical Reference

          ESSCMD

          RENAMEFILTER

          Oracle Essbase Technical Reference

            Deleting Filters

            *  To delete a filter, use a tool:

            Tool

            Topic

            Location

            Administration Services

            Deleting Filters

            Oracle Essbase Administration Services Online Help

            MaxL

            drop filter

            Oracle Essbase Technical Reference

              Assigning Filters

              After you define filters, you can assign them to users or groups, which lets you manage multiple users who require the same filter settings. Modifications to the definition of a filter are automatically inherited by users of that filter.

              Filters do not affect users who have the Administrator role. Only one filter per database can be assigned to a user or group.

              Assigning Filters in Shared Services Security Mode

              In Oracle's Hyperion® Shared Services security mode, you assign filters through Oracle's Hyperion® Shared Services Console.

              *  To assign a filter to a user or group, see Assigning Database Calculation and Filter Access.

                Assigning Filters in Native Security Mode

                *  To assign a filter to a user or group, see “Assigning Filters” in the Oracle Essbase Administration Services Online Help.

                  Overlapping Filter Definitions

                  If a filter contains rows that have overlapping member specifications, the inherited access is set by the following rules, listed in order of precedence:

                  1. A filter that defines a more detailed dimension combination list takes precedence over a filter with less detail.

                  2. If the preceding rule does not resolve the overlap conflict, the highest access level among overlapping filter rows is applied.

                  For example, this filter contains overlap conflicts:

                  Access

                  Member Specification

                  Write

                  Actual

                  None

                  Actual

                  Read

                  Actual, @IDESCENDANTS(“New York”)

                  The third specification defines security at a greater level of detail than the other two. Therefore, read access is granted to all Actual data for members in the New York branch.

                  Because write access is a higher access level than none, the remaining data values in Actual are granted write access.

                  All other cells, such as Budget, are accessible according to the minimum database permissions.

                  If you have write access, you also have read access.

                  Note:

                  Changes to members in the database outline are not reflected automatically in filters. You must manually update member references that change.

                  Overlapping Metadata Filter Definitions

                  You should define a MetaRead filter using multiple rows only when the affected member set in any given row (the metaread members and their ancestors) has no overlap with MetaRead members in other rows. It is recommended that you specify one dimension per row in filters that contain MetaRead on multiple rows. However, as long as there is no overlap between the ancestors and MetaRead members, it is still valid to specify different member sets of one dimension into multiple MetaRead rows.

                  For example, in Sample Basic, the following filter definition has overlap conflicts:

                  AccessMember Specification
                  MetaReadCalifornia
                  MetaReadWest

                  In the first row, applying MetaRead to California has the effect of allowing access to California but blocking access to its ancestors. Therefore, the MetaRead access to West is ignored; users who are assigned this filter will have no access to West.

                  If you wish to assign MetaRead access to West as well as California, then the appropriate method is to combine them into one row:

                  AccessMember Specification
                  MetaReadCalifornia,West

                  Overlapping Access Definitions

                  When the access rights of user and group definitions overlap, the following rules, listed in order of precedence, apply:

                  1. An access level that defines a more detailed dimension combination list takes precedence over a level with less detail.

                  2. If the preceding rule does not resolve the overlap conflict, the highest access level is applied.

                  Example 1:

                  User Fred is defined with the following database access:

                  FINPLAN     R
                  CAPPLAN     W
                  PRODPLAN    N

                  He is assigned to Group Marketing, which has the following database access:

                  FINPLAN     N
                  CAPPLAN     N
                  PRODPLAN    W

                  His effective rights are set as:

                  FINPLAN     R
                  CAPPLAN     W
                  PRODPLAN    W
                  

                  Example 2:

                  User Mary is defined with the following database access:

                  FINPLAN     R
                  PRODPLAN    N

                  She is assigned to Group Marketing, which has the following database access:

                  FINPLAN     N
                  PRODPLAN    W

                  Her effective rights are set as:

                  FINPLAN     R
                  PRODPLAN    W

                  In addition, Mary uses the filter artifact RED (for the database FINPLAN). The filter has two filter rows:

                  Access

                  Member Specification

                  Read

                  Actual

                  Write

                  Budget, @IDESCENDANTS(“New York”)

                  The Group Marketing also uses a filter artifact BLUE (for the database FINPLAN). The filter has two filter rows:

                  Access

                  Member Specification

                  Read

                  Actual, Sales

                  Write

                  Budget, Sales

                  Mary’s effective rights from the overlapping filters, and the permissions assigned to her and her group:

                  R

                  Entire Finplan database

                  W

                  For all Budget data in the New York branch

                  W

                  For data values that relate to Budget and Sales