Security filters for hierarchy members

Secure members in a hierarchy by using a security filter. A security filter specifies whether you are granting or denying users access to one or more members.

You can add security rules after modeling hierarchies in a project. They are independent of any dynamic cube.

Every hierarchy in IBM® Cognos® Cube Designer contains a default security filter that is named All Members Granted. This option explicitly grants access to all hierarchy members. You can define further security filters as required.

There are two types of security filter you can create:

  • Lookup table-based security filter

    If security rules for users are stored in a relational database table, you can import the data source and use the lookup table in a security filter.

  • Role-based security filter

    You can also manually define security rules, for example, where no suitable lookup tables exist.

You can also combine lookup table-based and role-based security filters. For example, you can restrict access to sales data to the Sales Employees user group by using a security view, and then use IBM Cognos Administration to further restrict access for each salesperson in the lookup table.

For each filter, you must specify the scope to indicate whether you are explicitly granting or denying access to hierarchy members. You then complete the filter as follows:

  • If you are defining a role-based security filter, you use a dynamic query mode expression to specify the required hierarchy members to include in the filter.
  • If you are defining a lookup table-based filter, you specify which lookup table columns contain keys for hierarchy members from each level. You then use an expression to select the rows from the lookup table that are relevant to the user executing the query.

    You can include macro expressions to match the user information in the lookup table to the current user information. An example is shown here:

    ( User Name = #sq($account.personalInfo.userName)#) and ( Security Type = 'grant') and ( Security Scope = 'self_and_descendant')

Important: A valid expression must return a set of hierarchy members.

In IBM Cognos Dynamic Cubes security, the deny scope has precedence over the grant scope. After a member is explicitly denied, it cannot be accessed. You can use a combination of deny filters to further restrict user access to the members in a hierarchy.

The following table describes the scope options that you can use when you are defining a security filter.

Table 1. Security filter scope options
Scope Description
Grant Members Explicitly grant access to specified hierarchy members. Report users can see only the specified hierarchy members and associated values. Using a grant scope without the Ancestors option can lead to visible ancestors.
Grant Members and Descendants Explicitly grant access to hierarchy members and all their descendants. Report users can see only the specified hierarchy members and associated values. Using a grant scope without the Ancestors option can lead to visible ancestors.
Grant Members and Ancestors Explicitly grant access to hierarchy members and all their ancestors. Report users can see only the specified hierarchy members and associated values.
Grant Members, Descendants and Ancestors Explicitly grant access to hierarchy members together with all their descendants and ancestors. Report users can see only the specified hierarchy members and associated values.
Deny Members and Descendants Explicitly deny access to hierarchy members and all their descendants. Report users cannot see the specified hierarchy members and associated values.
When you set up a security filter, you must consider the following points:
  • When you explicitly grant access to a hierarchy member, report users can see only that member and its associated values. Users are denied access to all other hierarchy members.

    For example, the Geography hierarchy contains the following members: All, Canada, and Europe. If you grant access to the All member only, users cannot see Canada or Europe.

  • When you explicitly grant access to a hierarchy member by using the Grant Members option or the Grant Members and Descendants option, report users can also see the ancestor members, but not their values.

    The values of these visible ancestor members are shown as ERR to differentiate them from a true Null value. The use of visible ancestors ensures that there is a path from a root member of the hierarchy to any granted members. Without a path from a root member to granted members, the IBM Cognos Studios cannot properly display members. Because Cognos Dynamic Cubes does not support visual totals, visible ancestors ensure that rollup values do not reveal information about secured descendants.

  • When you explicitly deny access to a hierarchy member, access to all other members in the hierarchy, except descendant members, is implicitly granted.
  • When you explicitly deny access to a hierarchy member, access to all descendant members is also denied.

    If the result of this option is an unbalanced or ragged hierarchy, padding members are used to balance the hierarchy. For more information, see Padding members.

  • If a security filter is set up with a grant or deny scope option, but does not contain an expression, then no members are granted or denied.
  • If a security filter contains references to a member that cannot be resolved, the member reference is ignored.

    If a member reference cannot be resolved because the member does not exist in a hierarchy, the security filter is still valid.

    If it cannot be resolved because the filter contains an invalid expression, an error occurs and access to the full hierarchy is denied.

  • If an error occurs as a result of applying a security filter, when a user opens a package or runs a report, an error message is shown because access to the whole hierarchy is automatically denied.

Secured padding members

The use of secured padding members ensures that the hierarchies remain balanced. Balanced, non-ragged hierarchies have better performance in the studios. Secured padding members are inserted into a secured hierarchy member tree when a granted member has all its child members restricted. This scenario is most common with the Grant Members option, when descendants are not included in the scope. However, it can also occur with deny filters or with a combination of grant and deny filters.

Consider the following points:

  • If all descendants of a non-leaf member are restricted, then secured padding members are inserted into all the levels below the non-leaf member.
  • If all leaf members are restricted, padding members are inserted; the leaf level is not removed.
  • The caption of secured padding members is either empty or blank or has the name of the parent. This is the same configuration setting for caption of padding member in ragged and unbalanced hierarchies.
  • Secured padding members are secured similar to visible ancestors.
  • Intrinsic properties of secured padding members are accurate, however member properties are null.
  • There is at most one secured padding member for each level under a parent member.

Aggregated data in a secured dynamic cube

When you grant access to hierarchy members, it is possible that report users might inadvertently infer member values to which they are denied.

For example, suppose that you have a Geography hierarchy with these members and values: All (100), Canada (30), Europe (70). Using the Grant Members and Ancestors option, access is explicitly granted to Canada and its parent (All). Report users can see All (100), and Canada (30). If report users are aware that Europe is the only one other hierarchy member, they can infer that its value is 70.