Security is a very important topic in the world of business intelligence (BI). In part 1 of this three-part series of articles, I introduced the different security levels in the SAP BusinessObjects BI platform: object level, application level, and Universe level.

Part 2 of this series explained the two approaches to implementing security inside Universes—using enterprise database security tables and the security editor—and provided details for how to use the security editor to do this. As discussed previously, there are two main approaches for implementing security in an SAP BusinessObjects Universe:

• Using the SAP BusinessObjects Information Design Tool (IDT) security editor (discussed in detail in part 2 of this series)
• Using enterprise database security tables (the main topic of this article)

The first approach (using a security editor) is a built-in feature and it can be used to create different categories of security profiles that can then be assigned to the corresponding user or group. This option provides a graphical user interface (GUI) for creating profiles and assigning users. I recommend that you use this method if you want to implement customized security profiles tailored per Universe and you are not planning to use them in other Universes.

In this final article of this three-part master-guide series, the focus is on how to use the second option for applying security inside SAP BusinessObjects Universes—using enterprise database security tables. 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 (discussed in more detail in my other BI Expert article, “Part 2: Implementing Security is SAP BusinessObjects Universe 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.

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.

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.

Taha M. Mahmoud

Taha M. Mahmoud is a PMP, TOGAF, ITIL, and CSM, and a senior BI consultant, BI project manager, and solution architect. He has more than seven years of experience consulting and deploying successful BusinessObjects projects in the banking and telecom industries. Taha is the author of the book, Creating Universes with SAP BusinessObjects.

You may contact the author at tabouelfootooh@ejada.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.