Row-level Security Trick with Crystal Reports

Row-level Security on Scheduled Instances Without the Use of Business Views

When I realized that I could get row-level security on scheduled instances without using Business Views I thought, I have to share this with everyone. When you schedule a report, the engine retrieves all the data based on the credentials of the user running the report. If I now want to get row-level security, I must apply security within the report at “format time” or view time. In other words, I must apply the security to the formatting of the report.

The great thing about this is that it not only applies to viewing reports, but also to accessing those view reports through other tools such as Live Office.
Situation: Allow an administrative user to run a report for all users and then apply row-level security at view time but do not leverage Business Views.

1. Make sure you have some type of method for determining row level security. In my case I created a Customer_Security table and added some restrictions so that certain users were allowed to see the data for certain countries. In my case I’m using SQL Server together with some Xtreme Customer data. Here you can see some sample data and the contents of the Customer_Security table:

2. Next, I need to create a Crystal Report which links the customer table to the customer security table and delivers back the expected results. When I created the report I used an outer join since no ever country in the customer table has an associated security setting for it.

3. Next I want to create a suppression formula on the row that contains data that restricted users can’t see. In this case I used the following suppression formula:

NOT (CurrentCEUserName = ‘ADMINISTRATOR’ OR
CurrentCEUserName = Uppercase({Customer_Security.Username}))

This TRUE if the user viewing the report is NOT the administrator or NOT a matching security value. This formula will cause the country records they do not have access to see to be hidden from view.

WARNING: In this example, I am not doing any special calculations, but if you do need to do summaries or groupings, I recommend that you set each value to a formula and within the formula you set values equal to blank (”) or 0 if the row should not be seen by the end user.

4. Now I can publish this report to BusinessObjects Enterprise and schedule the report to run as administrator and then view the report as ron. When I do this, I will expect to see all the records when logged in as administrator and only England, France and Germany records when logged in as ron.

Let’s schedule the report:

Crystal Reports applies formatting a view time, therefore it will evaluate the suppression formula for the individual running the report and apply it to the report instance. In this case the countries that ron is not allowed to see will be suppressed, even though they were retrieve with the original schedule instance.

Here is a view of what the administrator sees:

Here is what the restricted user ron sees:

We did not have to reschedule the report for ron, yet we were still able to achieve row level security from a scheduled instance! Do you know what happens now when I view this report from within Live Office? The suppression formulas are still applied at run-time and therefore the view security remains!

Here is what I will see from within Excel.

You can see here that I am accessing the Latest Instance and yet, even though the latest instance contains ALL RECORDS for the report, Live Office observes that suppression and resticts what the restricted user ron can see.

CONCLUSION: You can obtain row-level security from within Business Objects by using CEUserName and an external security table together with suppression formulas that are evaluated at run-time.