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:

SQL Server Setup

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.

Join Tables in Crystal Reports

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.

Show formula

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:

Schedule the report in BOE

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:

Administrator View (All Records)

Here is what the restricted user ron sees:

Ron's view of the same instance

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.

Live Office View

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.

9 replies on “Row-level Security Trick with Crystal Reports”

  1. Hi,

    My requirement is that I need to apply restrictions at the DB level. So please let me know if there is any way to pass the CurrentCEUser as the parameter to a stored procedure which sets the session at the DB level before fetching the data. Finally, my goal is to retrieve data that pertains to that user only.

    Please let me know a solution to this.

    Advanced Thanks,
    Nachiketa

  2. No, not directly. You can do it through the Viewer SDK by modifying the input parameters within the report and supplying the users name. That would be my recommendation. In fact you might want to create a standard prompt such as _USER and change your viewer code so that you cycle through the parameter and if you see this, you replace it.

    If you want it out of the box, the only option is to make your report a subreport and then use the “master” report to pass in the CurrentCEUser.

  3. If you want to get your feet wet with a free trial download – Look at Crystal Report Distribution (The merging of Business Process Automation and Crystal Reports Distribution software).

  4. I am using a SQL command based crystal report to gather the summaries. The summaries are performed at database level. In this case how can I acheive row level security based on the user who logs in.

  5. I was thinking in the same line to implement row level security in crystal reports. But in my case I will need to provide access to a certain row to multiple users. To match it with the above post, the user to country relationship would be many to many.

    If we use this approach, we will end up creating a bigger dataset as the data gets duplicated for each user based on his access to countries.

Comments are closed.