Follow these step-by-step instructions to learn how to implement security in SAP Universes using the security editor feature of the Information Design Tool. The security editor is the out-of-the box solution provided by SAP to handle security in Universes. I explain how to create data security profiles related to connection, table, SQL parameters, and row-level security. Then, I explain how to create business security profiles related to folders, objects, filters, and so on. Finally, I discuss the importance of having a security matrix in place before implementing Universe security and I provide a sample that can be used as your template. There are two main approaches for implementing security in an SAP BusinessObjects Universe:

• Using the SAP BusinessObjects Information Design Tool security editor
• Using enterprise database security tables

Configuring security profiles in the Universe enforces the implemented security rules on all BI artifacts based on this Universe, such as Web Intelligence reports, Crystal Reports, dashboards, SAP BusinessObjects Lumia visualizations, SAP BusinessObjects Design Studio dashboards, and mobile BI documents (reports and dashboards). In this article, I focus on the first approach, using the Information Design Tool security editor to implement Universe-level security.

Configuring Security in SAP BusinessObjects Universes Using the Security Editor

Before I go into detail about using the security editor inside the Information Design Tool to implement security in Universes, you need to know that there are two options for adopting security models, as follows:

• Allow everyone by default to see everything, and just restrict access to a set of specific users by applying restrictions or security rules that affect only them.
• Deny everyone access by default, and give access to only specific sets of users based on the type of data that they can access.

The first security model is the one used by default in SAP BusinessObjects Universes. This means that, by default, all users have access to all objects (e.g., dimensions, measures, attributes, and filters) inside a Universe. In addition, everyone can see all the data generated from report queries based on this Universe, unless a security rule or restriction is created and assigned to those users. These security rules or restrictions are known as security profiles and can be assigned to a specific user or a set of users (user group) to restrict their access. Security profiles are a set of rules or restrictions related to data, connections, objects, or SQL limitations. There are two types of security profiles: data security and business security profiles.

• Data security profiles are designed to secure data-connection or data-foundation resources inside a Universe. Universes normally contain three layers:  Data Connection, Data Foundation, and Business. The Data Connection layer allows the Universe to connect to data sources (external files such as Text, Excel, and CSV), database systems (e.g., Oracle, Microsoft SQL, Teradata, and so on), and Big Data sources (Hadoop). The Data Foundation Layer is the Universe layer that is used to design a physical model. This is done by adding database tables related to the Universe’s subject area, and configuring joins between them. Data security profiles aim to restrict access to database-related entities (schemas, tables, views, or data) that are maintained inside the Data Foundation Layer.
• The Business Layer of the Universe is where the physical model is translated into business terms. The main goal of this layer is to hide any technical details like database table names, columns, joins, and so on, and to provide information in business terms that business people can understand and deal with. The Business Layer, at the end, is a list of dimensions, measures, and attributes as well as some pre-defined filters and common business rules. Business security profiles are used to secure Business Layer resources in a Universe. They restrict access based on specific business rules, conditions, and filters.

Applying Security to Universes

There are five key steps to applying security to a Universe:

1. Log in to the repository.
2. Select a Universe.
3. Create a new security profile.
4. Assign the new security profile to users (or groups of users).
5. Test the restriction rules inside the security profile.

Security profiles are stored with Universes on the SAP BusinessObjects repository. The first step for creating a new security profile is to log in to the repository and establish a new SAP BusinessObjects server session. Select a Universe and then you can start creating your new security profile. Security profiles can be assigned to SAP BusinessObjects users as well as to groups of users. Note that one security profile can have many restriction rules from the same type of restriction—one type—but not from any other type. This means, for example, if you create a new data security profile, you can define a set of data security rules related to data, connection, and SQL limitations inside that profile, but you can’t mix business and data restrictions. If you want to create restrictions for a business rule, you need to create a separate profile for storing business rules. (Note: Make sure that SAP BusinessObjects client tools are installed on your PC and you have access to the SAP BusinessObjects server to follow the steps described in the remaining part of this article. (If you don’t have it installed, you should watch the companion demonstration video: click here to view the video.) In this example scenario, I use a NorthWind Universe but you can apply the same to an eFashion Universe as well. NorthWind and eFashion are two sample Universes provided with any SAP BusinessObjects installation.)

Using the SAP Information Design Tool Security Editor to Implement Security

Open the Information Design Tool by navigating to the SAP BusinessObjects Start menu and then following menu path SAP BusinessObjects Client Tools > Information Design Tool. In the screen that opens (Figure 1) click the security editor icon to open it. This opens the security editor screen shown in Figure 2, which has two main sections, the Universes/Profiles and the Users/Groups panes. As you can see in the left of the figure, Universes/Profiles is the default view.  

The Universes/Profiles View

In the Universes/Profiles section of the screen (Figure 2), you can select a Universe and insert a new data or business profile into that Universe (in the left panel). Then you can assign this profile to users or groups of users in the right panel. Finally, you can change the Data Security Profile Options in the bottom. The main purpose of this screen is to show which users and groups of users are assigned to which specific security profiles.

The Users/Groups View

The Users/Groups view (accessed from the left of Figure 2) opens the User/Groups screen, shown in detail in Figure 3. This is used to display a list of Security Profiles assigned to a specific user or groups of users. First, select a user or a group in the left pane. Then the list of assigned security profiles is displayed on the right. You can manage (add or remove) security profiles in this view in the same way you did in the Universes/Profiles section. To manage the security profiles, follow these steps:

1. Select a user or group of users.
2. Select a Universe.
3. Lists of the selected users or groups of users who are assigned security profiles (data as well as business) related to the selected Universe are displayed in the two bottom panels.

In the next section, I show how to create data and business security profiles.  

Creating Data Security Profiles 

Data security profiles are sets of rules and restrictions related to data. Usually they are based on data foundation resources. The Data Foundation Layer is one of the main three Universe resources that mainly focus on the physical data model represented by tables and joins. To create a data security profile, click the security editor icon in the Information Design Tool, and select the Universes/Profiles view option from the bottom left panel in Figure 3. This opens the screen shown in Figure 4. As you can see, there are five main categories (tabs) for defining data-restriction rules: Connections, Controls, SQL, Rows, and Tables.

Defining Connection Rules

In the Connections tab you can override the default connection used by the assigned user to connect to the Universe. In this example, let’s assume that there are two database connections, production and testing, and you want Universe testers to see only the testing environment and business users to see only the production database. To achieve this, you need to create a security profile and assign it to the Universe tester user to restrict the access to the user acceptance testing (UAT) database. Make the following entries:

Data Security Profile Name: Enter NorthWind UAT Database

Replacement Connections:  Select NorthWind.cnx.

Then click the Edit button.

This opens the Define Replacement Connection window (Figure 5). Select NorthWind UAT from the list of available connections and click the OK button. You should receive a pop-up message that states that the new NorthWind UAT Database data security profile is successfully created (not shown).

(Tip! Only the list of secured published connections is displayed in the Define Replacement Connection window, and this is only applicable for relational connections.) Once you receive the success message, the new definition for the NorthWind UAT data security profile is added to the Connections tab options, as shown in Figure 6. The next step is to assign this new security profile to the Administrator user. First, select the NorthWind UAT Database (Figure 7) and take the following steps.

1. Select the NorthWind UAT Database data security profile from the left panel.
2. Select the Administrator user from the right panel.
3. Click the left arrow to assign this security profile for this user.

After taking these steps, you see the new NorthWind UAT Database security profile is created under NorthWind.unx, as shown on the left of Figure 7. After assigning this security profile to the Administrator user, he or she can display data retrieved from the UAT database instead of the production database when opening any report or BI document based on this Universe. This happens at run time and it only affects users or groups who are assigned to this security profile. (Note: In this example, the data security profile is assigned to the administrator user because (in this case), there isn’t a tester user. You can define a replacement connection for each connection specified in a Multi-Source Universe.)

Defining Control Rules

For this scenario, let’s assume that you have a specific user group that has access to many detailed operational reports that are built on the top of your NorthWind Universe. In this example, you want to give a priority to BI and analytics reports, and limit the operational data inquires to a maximum of 10,000 rows or a 15-minute-limited execution time. To apply these restrictions, you can use a controls data security profile. Simply follow these steps and make these entries. 1. Create a new data security profile under the NorthWind Universe by following the same steps outlined previously in the Defining Connections Rules section, but this time navigate to the Controls tab (instead of Connections). 2. In the screen that opens (Figure 8), enter Limited operational reports in the Data Security Profile Name field. 3. Make sure the Controls tab is selected, and in the screen at the bottom of Figure 8, make the following settings:

• Limit size of result set to: Select the check box and change the number to 10,000 (rows)
• Limit execution time to: Select the check box and set the number to 10 (minutes)
• Warn if cost estimate exceeds: Select the check box and set the number to 5 minutes

Note that the control settings can be configured for the Universe under the Data Foundation Layer. The values that are input here (data security profile controls) override the default settings that have been configured on the Universe (Data Foundation Layer) level. In this case, the new settings are displayed in bold (as Limit size of result set to 10000 rows is in Figure 8), while the other options (Limit Execution Time to and Warn if cost estimates exceeds) are not. This is because they are not changed—they are the same value as configured on the Universe data foundation level.

Defining SQL Rules

Under the SQL tab (Figure 9), you can make the settings to allow or disallow the end user to use some SQL features such as:

• Subqueries
• Union, intersect, and minus operators
• Complex operands in the query panel

These SQL features are divided into three main categories:

1. Query: Here you can control the query options, such as subqueries, set operations (union, intersect, and minus operators), and complex operands.
2. Multiple Paths: Here you can control the multiple paths’ options to overcome design traps (discussed in more detail later in the Data Foundation Advanced Topics section).
3. Cartesian Products: Here you can allow or disallow Cartesian product. (Cartesian product takes place if you are trying to query two un-joined tables.) In this case, the database engine produces all the possible row combinations that impact performance. This usually happens because the Universe designer forgets to join tables or when a business user tries to build an unrealistic query to retrieve data from two different, unrelated tables.

The rules settings that you make here also override the default SQL options defined in the Universe for the assigned user only.

Defining Row Rules

The Rows tab is used to restrict the data retrieved as per as the assigned user. In this example, you want to restrict the Chicago office manager’s view so he or she can only see orders shipped to Chicago. To do this, follow these steps:

1. Create a new data security profile under the NorthWind Universe by following the same steps outlined previously.

2. In the screen that opens (Figure 10), enter Shipped to Chicago in the Data Security Profile Name field and go to the Rows tab.

3. Click the Insert button, which opens the screen in Figure 11 (the Define Row Restriction pop-up).

4. In the Table field, select Orders from the drop-down options.

5. In the WHERE Clause box, enter Orders."Ship City"='Chicago' and click the OK button.

This action opens the screen in Figure 12, where you can see the restricted table in the Rows tab. Now, this profile adds the defined WHERE Clause to any query generated by the assigned user, but only if the user selects from the Orders table. Click the OK button to save your settings. (Tips!

• Multiple row restrictions can be defined in the same data security profile.
• The WHERE Clause restrictions are only used when the corresponding tables are queried by the assigned users.
• Don’t allow end-users to customize SQL queries in Web Intelligence reports. This is because then they can edit the generated query and remove the WHERE clause line defined in this restriction from the SQL query generated by the report.
• Build a security matrix first to avoid assigning two contradicting profiles to the same user or user group. For example, you can build two profiles, one to filter on Ship City = Chicago and the other one on Ship City = New York. If you applied both of them to the same user or user group, then the end user will not get any records. [The security-matrix-building step is discussed in more detail later in this article.])

Defining Table Rules

Use the Tables tab to replace one table or view at run time with another one. You can select a table from one of the connections defined with the Universe or another table from the data foundation resource. In this example, let’s assume that there are two finance tables. The first one contains the publicly published figures while the second one contains the actual (proprietary), confidential figures. You want all users to have view access to the published figures, but you want only users in the executive user group to see the confidential information as well. In this case, you want to use the publicly published finance data table in your Universe. In addition, you need to create a data profile to switch this table at run time if any user from the executive group tries to access this table (so that they can see the confidential data as well). Here are the steps for creating a data security profile that replaces the table at run time based on the logged-on user’s profile. 1. Create a new data security profile under the NorthWind Universe by following the same steps outlined previously. 2. In the screen that opens (Figure 13), enter Replace Table in the Data Security Profile Name field and go to the Tables tab. 3. Click the Insert button and in the screen that opens (Figure 14) define the original table (Customers) that you want to replace in the Original Table field. Then define the replacement Qualifier and Table for the original table in the replacement area. Finally, click the OK button to save and close this window.

Understanding the Business Security Profiles

Business security profiles are sets of rules and restrictions related to the Business Layer of your Universe. The main purpose of this type of profile is to restrict or grant access to folders and objects inside the Business Layer to the right users. There are three main categories of security rules inside this type of security profile:

1. Create query
2. Display data
3. Filters

I explain each of these types in the next sections.

Creating Query Rules

In this view, you can restrict the objects that the end user can use in the query editor when building a report query. You can grant or deny access to:

• Business Layer views
• Folders and business objects in the Business Layer

End users can only see and use the granted objects and Business Layer views, and are not able to see or access any denied objects for Business Layer views. In this example, let’s create a business security profile to assign the following permissions to the assigned users:

1. Grant access to the orders business view.
2. Deny access to customers’ contact information.

Create a new business security profile by clicking the create business security profile icon in the top section of the left panel (Universe/Profiles view), shown in Figure 2. Then, in the Define Business Security Profile pop-up that opens (Figure 15), enter Orders business view only in the Business Security Profile Name field. Then go to the Create Query tab. In the Business Layer Views section, click the top Insert Granted button. This action opens the screen in Figure 16 where you select the Orders business view from the list. (Select the All business layer views check box as a shortcut if you have many views to select instead of selecting them one by one.) Click the OK button. Back in the screen in Figure 15, this time you want to add a restriction to deny the assigned user access to the customer contact information folder. Click the Insert Denied button under the Objects box and the pop-up window in Figure 17 opens. Navigate to the Contact Info. folder and click the OK button. The new business security profile should look like the one in Figure 18. After applying this profile to business users, they can see objects under the Orders view only as they are granted. At the same time, they cannot see any objects in the Contact Info. folder because access to this folder is explicitly denied in this profile even if it is a part of the granted Orders view.

Creating Display Data Rules

The steps for creating a display data restriction are the same as for creating a Create Query restriction (but in the Display Data tab instead of the Create Query tab). The only difference is that the create query rule is used to manage assigned user permissions on the objects that are used in the query editor, while the display-data restriction manages the data that can be displayed by the end user. The result looks like the screen in Figure 19.

Creating Filter Rules

The steps for creating filter restrictions are like the steps for creating the row-level security rules (used in the Security Profiles data). The only difference is that row restriction is used only when the affected table is referred to in the assigned user query. In the filters restriction section (the Filters tab) of the Define Business Security Profiles, this condition is forced on (e.g., users cannot modify or change this filter) and applied to the assigned user (regardless of the selected objects and the referenced tables). The end result looks like the screen in Figure 20.

Best Practices

It is a good idea to get in the habit of creating a security matrix. A security matrix helps you to:

• Keep track of your groups and users and their assigned rules and profiles.
• Optimize security profiles and rules creation by identifying the privileges intersection between different users and groups.
• Identify the impact of changing or modifying a specific security profile.

Creating and Using a Security Matrix

A security matrix is a list (record) of the mapping between users and profiles. I use security matrices as my first step in defining what kinds of restriction rules will be created and to whom they should be assigned. This provides a single place to document and manage the security profiles. You can have just one security matrix mapping that shows the relationship between users and profiles, or you can create one with more details and break this relationship in two matrices. In this case, the first one could show the mapping between security profiles and corresponding user/groups (an example is shown in Table 1), while the second one could show the relationship between security settings and security profiles (an example is shown in Table 2).

User/Profile	Profile #1	Profile #2	Profile #3
User # 1	X
Group # 1		X	X
User # 2			X

Table 1 A simple security matrix that shows the relationship between users/groups and profiles

Security setting/Profile	Profile #1	Profile #2	Profile #3
Replace table TABLE1		TABLE2
Set maximum number of retrieved rows	1000		500
Allow use of inner join		Yes	Yes
Business Layer view	Master	Orders

Table 2 Sample security matrix that shows the relationship between security settings and profiles