Creating and Assigning Universe Security Profiles

Objectives

After completing this lesson, you will be able to:

  • Create a Data Security Profile.
  • Create a Business Security Profile.
  • Assign and Test Security Profiles.

Data Security Profiles

Data Security Profiles are a group of settings that define security on the data foundation and the data connections. Data Security Profiles can be defined for relational universes only.

Data Security Profile Connection Settings

Use the Data Security Profile Connections setting to define replacement connections that can override the connections defined in the universe. When a user with a profile containing a replacement connection runs a query on the universe, the replacement connection is used. Only secured connections can be defined as replacement connections. The replacement connection must be of the same type as the original connection. Relational connections fall into one of the following three types:

  • SAP BW relational databases

  • SAS relational databases

  • Other relational databases

Data Security Profile Controls Settings

Use the Data Security Profile Controls settings to define replacement query limits that override the default limits when retrieving data from the database. Default query limits are set by the universe designer in the business layer. When a user with a profile with replacement Controls settings runs a query, the replacement limits are used instead of the limits defined in the business layer properties.

In the editor for Data Security Profiles, the limits selected and the limit values defined in the business layer are displayed. When you select or deselect a limit, or enter a new value for a limit, the label appearance changes to bold. This bolding shows that the limit is an override and not the default limit defined in the universe.

The following table lists Data Security Profile Controls settings:

Data Security Profile Controls Settings

Query LimitPossible values
Limit size of result set to
  • A numerical value between 0 and 2,147,483,647 rows
  • False
Limit execution time to
  • A numerical value between 0 and 2,147,483,647 minutes
  • False
Warn if cost estimate exceeds
  • A numerical value between 0 and 10,000 minutes
  • False

Data Security Profile SQL Settings

Use the Data Security Profile SQL settings to define replacement query options. When a user with an assigned or inherited profile with SQL settings uses the query panel, the replacement options are used instead of the query options defined in the universe.

Data Security Profile Controls Settings

Query OptionPossible values
Allow use of subqueries
  • True
  • False
Allow use of union, intersect, and minus operators
  • True
  • False
Allow complex operands in Query Panel
  • True
  • False
Multiple SQL statements for each context
  • True
  • False
Multiple SQL statements for each measure
  • True
  • False
Allow Cartesian products
  • Warn
  • Prevent

When you make any changes from the default settings, the label appearance changes to bold. This bolding shows that the limit is an override and not the default limit defined in the universe.

Data Security Profile Rows Settings

Use Data Security Profile Rows settings to restrict the rows returned in a query. You restrict the rows by defining an SQL WHERE clause for a specified table. When a user with an assigned or inherited profile with a Rows setting runs a query that selects from the table defined in the WHERE clause, the defined WHERE clause is added to the SQL generated.

Note

A user who has the right to edit the generated SQL in the reporting tool, can change the WHERE clause generated by the Rows setting. Remember to manage the rights of the user in the reporting tool, to prevent the user from modifying the SQL.

The SQL for the WHERE clause cannot include:

  • Calculated columns

  • Derived tables

Data Security Profile Tables Settings

Use the Data Security Profile Tables setting to define replacement tables. When a user with an assigned or inherited profile that has a Tables setting runs a query that references the original table, the replacement table is used instead.

Note

A user who has the right to edit the generated SQL in the reporting tool can change the replacement table name. Remember to manage the rights of the user in the reporting tool to prevent the user from modifying the SQL.

You can replace a standard table in the data foundation with a database table in the connection defined for the universe or another standard table in the data foundation.

Note

You can specify an owner and qualifier for the replacement table by entering the details in the fields provided. Do not enter the details as part of the table name.

Business Security Profiles

Business Security Profiles are a group of settings that define security on a published universe using objects in the business layer.

In the business layer, designers set the status of objects to Active, Hidden, or Deprecated. When defining profile settings, you have access to all active objects in the business layer. Objects that are hidden or deprecated in the business layer never appear in the query panel or on reports.

Business Security Profile Query Data Settings

This setting defines the universe views and business layer objects available to the user in the query panel. By default, a user granted access to a universe published in the repository can see all universe objects in the query panel. Once the user is assigned or inherits a profile with a Create Query setting, only the views and objects granted by the setting are displayed and can be selected for a query.

If an object is not granted and not denied explicitly, it is denied by default. Unlike objects that are explicitly denied, objects that are denied by default could be granted by inheritance after aggregating Business Security Profiles to determine the net profile for a user.

Methods to Grant and Deny Objects

  • By business layer view: grant or deny all views defined for the universe. If most views are allowed, it is easiest to grant all views, and then deny the ones that are not allowed. Using the All business layers view, and All objects option means that any new view or object, defined in the business layer, is automatically included in the Create Query setting when the universe is published.

  • By object: grant or deny all objects in the business layer.

    • Dimensions

    • Attributes

    • Measures

    • Calculated members

    • Filters

    • Prompts

    • Named sets

    • Folders: Grants or denies all objects in the folder.

    • Analysis dimensions: Grants or denies all objects in the dimension.

    • Hierarchies: Grants or denies all objects in the hierarchy.

The objects in a granted view are granted in that view only. If the same object is contained in another view, it is not automatically granted.

Whether or not a user sees a particular object in the query panel is determined after aggregating the Create Query settings in all profiles assigned to the user, and considering the access level.

Business Security Profile Display Data Settings

Use the Business Security Profile Display Data settings to determine what data a user can view in a generated report. Using the All objects option means that any new object defined in the business layer is automatically included in the display data setting when the universe is published.

When a Display Data setting denies a user access to an object, they can refresh a report containing the denied object. The user can specify the refresh options in this case by setting the SQL generation parameter AUTO_UPDATE_QUERY in the business layer.

  • Setting the parameter to No refreshes the report and generates an error message.

  • Setting the parameter to Yes removes the denied objects from the query and from any filters defined in the business layer. Data for other granted objects is retrieved and displayed to the user in a partial report.

Whether or not a user sees data for a particular object is determined after aggregating the Display Data settings in all profiles assigned to the user, and considering the object access level.

Business Security Profile Filters Settings

Use the Business Security Profile Filters setting to define a filter using objects in the business layer or named member sets. You create and edit filters explicitly for the Business Security Profile using the Security Editor. Filters in the Business Security Profile are not accessible in the business layer. If the Business Security Profile is deleted, the filter or named set is also deleted.

Once the user is assigned or inherits a profile with a Filters setting, the filter is added to the query script (and combined with any filters defined in the business layer). This restricts the data displayed.

Relational Universes

For relational universes, you define filters on dimensions and measures in the business layer. You can define compound filters that are linked by the AND and OR operators. You can also define multiple filters to apply to the query.

When a user runs a query, the filters are always applied to the query and to the returned data.

OLAP Universes

For OLAP universes, you define a named set of members. You can include or exclude members for any dimension in the business layer. This process removes excluded members from the query when data is retrieved from the cube.

Note

The filter does not impact the aggregation of values in the report. Only the display of members is filtered. You can include or exclude members from multiple dimensions.

You can also define multiple named sets to apply to the query.

Security Profile Assignment and Testing

Once you have created the necessary security profiles, assign them to specific users or groups of users.

Create Security and Apply Security Profiles

In this short video, you will see how to create and apply security profiles in a universe.

Create, Assign, and Test Security Profiles

To limit user access to certain data, create and assign data and business security profiles.

ExerciseStart Exercise

Log in to track your progress & complete quizzes