Controlling Access to Database Cells

When the security levels defined for applications, databases, users, and groups are not enough, the Hyperion Essbase Database Filter layer gives you control over security at the most detailed level. Filters let you 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 the role of Supervisor, you can define and assign any filters to any users or groups. Filters do not affect you.

If you are a user with Create/Delete Applications privilege, you can assign and define filters for applications you created.

If you have the role of Application Designer or Database Designer, you can define and assign filters within your applications or databases. For more information about privilege levels, see Managing Security at Global and User Levels.

This chapter contains the following sections:

• Privileges at the Database Filter Layer
• Defining Filters
• Assigning Filters

Privileges at the Database Filter Layer

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 are designating a set of restrictions upon 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 Hyperion Essbase 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 particular 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, while they have no access at all to Profit data values.

Filters are composed of 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 an individual cell.

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. This is because the filter definition, being more data-specific, indicates a greater level of detail than the more general database access level.

Calculation access is controlled by minimum global permissions or by privilege levels 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.

Defining Filters

To define a filter means to do any of the following things:

• Create a new filter
• Edit an existing filter
• Copy an existing filter into a new but identical filter
• Rename an existing filter
• Delete an existing filter

Before defining a filter, you must connect to the server and select the database associated with the filter.

To define a filter for the selected database, choose Security > Filters. Hyperion Essbase displays the Filters dialog box. Begin with this dialog box for all filter definitions, whether you are creating, deleting, editing, copying, or renaming a filter.

If you want only to view a list of filters for the selected database, this dialog box shows a list of filters.

You can also use display filter in MaxL or the LISTFILTERS command in ESSCMD to perform this task. See the online Technical Reference in the DOCS directory for information.

Creating a New Filter

You can create a new filter for each set of access restrictions you need to place on database values. There is no need to create separate filters for users with the same access needs--once 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.

1. To create a filter, select your current application and database in the Application Desktop window (if they are not already selected).

   To practice creating a filter using a sample, see Mini-Tutorial.

2. Choose Security > Filters.

   Hyperion Essbase displays the Filters dialog box.

   Figure 18-1: Filters Dialog Box

   

3. Click New.

   Hyperion Essbase displays the following confirmation box.

   Figure 18-2: Associate Outline Confirmation Box

   

4. Click Yes to confirm that you want to associate the current outline with the filter. This will enable the member selection tool so that you don't have to type in all member specifications.

   Hyperion Essbase displays the Define Filter dialog box.

   Figure 18-3: Define Filter Dialog Box

   

   Click Help for information on each option.

5. Type a name for the new filter in the Filter Name text box.
6. To define an access level for whatever object you intend to specify in the corresponding row of the Member Specification column:
   1. Make sure that Row 1 under the Access column is selected, as in Figure 18-3.
   2. Click the down arrow next to the cell in the Access column to choose an access level of None, Read, or Write.
7. To select a dimension to which you want to specify the access levels:
   1. Choose a dimension from the Dimensions list box.
   2. In the Members list box, double-click on the down-arrow next to a dimension name to see it expand into an outline of its members.
   3. Make sure the cursor is in the first row of the filter sheet, labeled Member Specification.
   4. Paste a dimension name or member name into the row by selecting the word from the outline in the Members list box.
8. To apply a function to dimensions and members:
   1. Position the cursor in the Member Specification row and select the appropriate function from the Functions list box.
   2. Specify a member for the function by placing the cursor within the parentheses and selecting the member name from the outline in the Members list box.

      The member name should appear within the parentheses.

9. To delete a row, place the insertion point in the row and click Delete Row.
10. To verify that your syntax is correct for the entire filter sheet, click Verify.

    Note:

    For more information about functions and syntax, consult the online Technical Reference in the DOCS directory.

11. Click OK to save the filter.

Mini-Tutorial

1. To create an example filter, make sure your current application is Sample and your current database is Basic. (Specify these in the Application Desktop window.)
2. Choose Security > Filters. Hyperion Essbase displays the Filters dialog box (Figure 18-1).
3. Click New.

   Hyperion Essbase displays the following confirmation box.

   Figure 18-4: Associate Outline Confirmation Box

   

4. Click Yes to confirm that you want to associate the current outline with the filter.

   Hyperion Essbase displays the Define Filter dialog box (see Figure 18-3).

5. Type the name Finances in the Filter Name text box.
6. Fill in the filter sheet for the sample filter, Finances, to match the following example:

   Figure 18-5: Sample Member Specifications in the Define Filter Dialog Box

   

   See the instructions for creating a filter (see Creating a New Filter).

   This filter defines the following access plan:

   • No retrieval or update access to any data for members of the Total Expenses branch or the Ratios branch.
   • Read-only access to all members of the Inventory branch below Actual.
   • Read-only access to all members of the Inventory branch below Budget for the months of Jan through Jun.
   • Write access to all members of the Inventory branch below Budget data for months of Jul through Dec.

Filtering Whole Members vs. Filtering Member Combinations

The following examples illustrate different ways to control access to database cells. Data can be protected by filtering entire members, or by filtering member combinations.

Filtering members separately, by defining access for each member in a separate row of the Define Filter dialog box, affects whole regions of data for those members. Filtering member combinations, using one row in the Define Filter dialog box, affects data at the member intersections.

Figure 18-6: How Filters Affect Data: AND/OR Relationships

Figure 18-7: How Filters Affect Data: AND/OR Relationships

Filtering Members Separately

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

Example:

User KSmith is assigned the following filter preventing any access to the members Sales or Jan in the Sample Basic database:

Figure 18-8: Filter Blocking Access to Sales or 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 looks like the following:

Figure 18-9: 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 single row in the Define Filter dialog box. A filter definition using one row and a comma is treated as an AND relationship.

Example:

User RChinn is assigned the following filter which blocks only the intersection of the members Sales and Jan in the Sample Basic database:

Figure 18-10: Filter Blocking Access to Sales for 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 looks like the following:

Figure 18-11: 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 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.

Example

User PJones is assigned the following filter, which blocks access to data for caffeine-free products. "Caffeinated_False" is a Boolean-type attribute member in Sample Basic, in the Pkg Type attribute dimension. This attribute is associated with members in the base dimension Product.

Figure 18-12: Filter Blocking Access to Members with 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 looks like the following:

Figure 18-13: 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).

Editing a Filter

1. To edit an existing filter, choose Security > Filters. Hyperion Essbase displays the Filters dialog box (see Figure 18-1).
2. Select the filter you want to edit and click Edit. Hyperion Essbase displays the Associate Outline Confirmation box, as in Figure 18-2.
3. Click Yes to confirm that you want to associate the current outline with the filter. Hyperion Essbase displays the Define Filter dialog box, as in Figure 18-3.
4. Edit the filter as you would create one, by filling in the filter definition rows from items selected in the list boxes. See Creating a New Filter.
5. Click OK to save the filter.

Copying a Filter

1. To copy an existing filter, choose Security > Filters. The Filters dialog box appears (see Figure 18-1).
2. Select the filter you want to copy and click Copy. Hyperion Essbase displays the Copy Filter dialog box.
   • The labels in the From group display the name and location of the original filter.
   • The To group is where you enter the information for the new filter.

     Figure 18-14: Copy Filter Dialog Box

     

3. Select the application and database for the new filter from the list boxes in the To group.
4. Type the name of the new filter in the Filter text box, or if you decide not to create a new filter, but would rather update an existing one so that it becomes a copy of the original, select an existing filter name from the list box. Hyperion Essbase displays the following confirmation box:

   Figure 18-15: Copy Filter Confirmation Box

   

5. Click Yes to confirm that you want to replace the existing filter with the copy.
6. Click OK to save the filter.

You can also use create filter as in MaxL or the COPYFILTER command in ESSCMD to perform this task. See the online Technical Reference in the DOCS directory for information.

Renaming a Filter

1. Choose Security > Filters.

   Hyperion Essbase displays the Filters dialog box (see Figure 18-1).

2. Select the filter you want to rename and click Rename.

   Hyperion Essbase displays the Rename Filter dialog box.

   Figure 18-16: Rename Filter Dialog Box

   

3. Type in the new name and click OK to save.

You can also use the RENAMEFILTER command in ESSCMD to perform this task. See the online Technical Reference in the DOCS directory for information.

Deleting a Filter

1. Choose Security > Filters.

   Hyperion Essbase displays the Filters dialog box (see Figure 18-1).

2. Select the filter you want to delete and click Delete. Hyperion Essbase displays the following confirmation box:

   Figure 18-17: Delete Filter Confirmation Box

   

3. Click Yes to confirm that you want to delete the filter named in the confirmation box.

Assigning Filters

Once you have defined filters, you can assign them to users or groups. This lets you manage multiple users who require the same filter settings. Modifications to a filter's definition are automatically inherited by users of that filter.

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

1. Choose Security > Filters.

   The Filters dialog box appears as in Figure 18-1.

2. Select the filter name you want to assign and click Users/Groups.

   Hyperion Essbase displays the Assign Filters dialog box:

   Figure 18-18: Assign Filters Dialog Box

   

3. Select a name from the Users list box and click Add. Similarly, you can remove a user or group by selecting the name from the "Users/Groups using filter" list box and clicking Remove.
4. Click OK.

Overlapping Filter Definitions

If a filter contains rows that have overlapping member specifications, the inherited access is set by the following rules, which are 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, the following filter contains overlap conflicts:

Figure 18-19: Filter with Overlap Conflicts

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 data points, such as Budget, are accessible according to the minimum database permissions.

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

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 become:

   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 become:

   FINPLAN	R
   PRODPLAN	W

In addition, Mary uses the filter object RED (for the database FINPLAN), which has the following filter rows:

Figure 18-20: RED Filter for Database FINPLAN

The Group Marketing also uses a filter object BLUE (for the database FINPLAN) which has the following filter rows:

Figure 18-21: BLUE Filter for Database FINPLAN

The effective rights from the overlapping filters are:

The access level for unspecified members is the inherited access level of the database (in this case, Read).

For more sample scenarios, see Security Examples.

Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.