Implementing Security in BusinessObjects Universes with Security Tables

Security is a very important topic in the world of business intelligence (BI).

The focus of this article is on how to use enterprise database security tables to apply security inside SAP BusinessObjects Universes. I recommend that you use this option if you want to re-use your security assignment in other BI tools or if you want to re-use it in more than one Universe (ergo the term enterprise). Using this method, you need to define security just one time and then you can use the same security tables with different Universes and subject areas.

In this article, you learn about:

• How to implement security in the Universe using enterprise security database tables
• The main differences between implementing security using the IDT security editor and using enterprise database security tables

Configuring security profiles in a Universe enforces the implemented security rules (e.g., applying some filters that can’t be changed by the end users on whom the filters are applied) on all BI artifacts based on this Universe, such as Web Intelligence reports, Crystal Reports, dashboards, SAP BusinessObjects Lumira visualizations, SAP BusinessObjects Design Studio dashboards, and mobile BI documents (reports and dashboards).

Configuring Security in Universes Using Database Tables

There are two options for adopting security models. You can:

• 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 in SAP BusinessObjects Universes. This means that, by default, all users have access to all business layer objects (e.g., dimensions, measures, attributes, and filters) inside a Universe. In addition, they 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. You can create restrictions to restrict those users using the security editor.

The second model is more restricted and assumes that no one has access until they are specifically granted privileges to access specific information to see. By default, all users do not have access—you need to assign the right permissions to the right people. This can be implemented in the Universe by storing security information in a database table (user name plus his or his permissions) and then embedding this table inside the Universe. The details for doing this are detailed in this article.

The point of this security mechanism is to have a database table that contains user IDs along with lists of values that each user allowed to see. In this scenario, a security table is required for each dimension. Then you need to join each security table with its corresponding dimension. Finally, you must use the @variable(‘BOUSER’) function to fetch the current logged-in user and do the proper filtration.

Table 1 shows an example of a database security table with regional dimensions.

Table 1
A sample security table for the region dimension

You can understand from this security table that Tmahmoud is only allowed to see information related to the central region, Maria can see the central and western regional information, and Pop can see all the regions’ information—central, western, and eastern. Now, let’s assume the following example star schema for sales information, shown in Figure 1.

Figure 1

Entity relationship diagram for a simple star schema

In this example, there is one fact table (a fact table in a star schema is the table that contains the ID referenced for other dimensions and all available metrics—in this example, Sales) and one dimension (Region). In the tables in Figure 2 you can see some example data to help you understand the star schema.

Figure 2

Sample data for region dimension (left) and sales-fact table (right)

The entity relationship diagram after adding the security table is displayed in the diagram in Figure 3.

Figure 3

An enterprise relationship diagram after the security table (RegionSEC) is added

Finally, you must use the @Variable(‘BO_USER’) function to get the current session (login) user and use it as an entry to the security table. Assume that you have the following condition (filter) on the Universe: (RegionSEC.UserID = @Variable(‘BO_USER’)).

Now let’s simulate what happens when Maria logs in to the SAP BusinessObjects Launchpad and opens the monthly sales reports (based on the Universe that is described above) that is based on the star schema example.

First, the @Variable(‘BO_USER’) function returns Maria as she is the logged-in user. The RegionSEC.UserID = @Variable(‘BO_USER’) function filters RegionSec rows and returns only the rows shown in Table 2, which are related to Maria.

Table 2
Filtered rows after applying security

As a result of the star join between RegionSec and region table, only the central and western regions are selected (filtered on). The same applies to the central and western region-related records (Figure 4). The eastern region is filtered out as Maria doesn’t have privileges to see this region (based on what stored in the RegionSec table.

Figure 4

Sample of the expected data to be displayed when Maria is logged in

You can apply the same concept and simulate what happens when Tmahmoud or Pop log in and use a report or dashboard based on the same Universe.

Now, let’s find out how to implement this in the Universe. This time I use another example based on the State dimension. To illustrate, look at the security table in Figure 5.

Figure 5

Example security table

Based on the settings in the table in Figure 5, the product manager can see all States’ information (Texas, California, and New York), but each State head can only see his or her own State’s information.

How to Join Security Tables with Dimensions

Here are step-by-step instructions for joining security tables with their corresponding dimensions in the IDT of SAP BusinessObjects.

1. In the IDT, navigate to Local Projects and expand the Test Project option (Figure 6).

Figure 6

eFashion.dfx (Data Foundation)

2. Double-click the eFashion data foundation (e.g., eFashion.dfx) to open it (2).

3. On any empty work space on the right, right-click and insert the STATE_SEC table option (3).

4. Navigate to the work space under eFashion.dfx data foundation and use the scroll bar to find the OUTLET_LOOKUP table. (There are many tables in the eFashion.dfx data foundation; you can also use the search function to search for the outlet_lookup table.)

5. Join the OUTLET_LOOKUP and STATE_SEC tables using the State column in both tables. Simply drag and drop the State column from one table to the next.

How to Create filters in Security Tables

Next you need to create a filter in the security table to display the relevant data only to the currently logged-in user.

1. Select the STATE_SEC table and create a new join (using the same steps as above).

2. Open the Edit Filter pop-up (Figure 7) by double-clicking the join line (the line between the OUTLET_LOOKUP and STATE_SEC tables shown in Figure 6) and type the following expression in the join-definition box (under Expression): STATE_SEC.USER_ID = @variable(‘BOUSER’). Then click the OK button.

Figure 7

Create a filter in the security table using a self-join to filter data related to the logged-in session user

The STATE_SEC.USER_ID = @variable(‘BOUSER’) variable retrieves the current logged-in user ID so that only data related to that user is retrieved.

How to Enforce Joins

Finally, you need to enforce this join so it is passed to the report-generated SQL query even if the end user doesn’t select any dimensions from the outlet table. First, select the dimension or measure that you want security to enforce when selected. Then follow these steps.

1. Open the eFashion business layer eFashion.blx in the left panel (Figure 6) to open the screen displayed in Figure 8. Then select any dimension or measure from the left panel (as shown in the figure). In this case, select State (1).

Figure 8

Select the measure or dimension to which to apply security

2. In the Dimension State screen that opens on the right of the figure, select the SQL Definition tab. Once in the SQL Definition tab (Figure 8), navigate to the Tables area (2).

3. Then click the ellipse icon (…) next to the Tables field (3) and the Associated SQL Tables pop-up opens (4).

4. Navigate to STATE_SEC table in the table list and tick the check box beside it. This means that whenever this dimension used in an SAP BusinessObjects report, dashboard, or query, the join that you created between the OUTLET_LOOKUP table and the STATE_SEC table becomes effective. This enforces the join with the security tables and ensures that returned results are filtered per the security assigned to the logged-in user.

If the logged-in user doesn’t have any entries in the security table that defines his or her security privileges, he or she is not able to see any results in the report or dashboard build based on this Universe. This means that, by default, all new users are restricted; therefore, they need to be added to the security table(s) and their security privileges defined to be able to see data.

A Comparison of the Two Approaches

Table 3 is a brief comparison on the two approaches (using the IDT security editor versus using enterprise database security tables). This comparison helps determine which approach to use to implement security in SAP Universes based on the supported features of each approach.

Table 3
A comparison of the security editor table and database security table