Designing Row-Based Security into Standard Reports

The most robust way to handle row and column level data security in JasperReports Server is to utilize a Domain and a security file. This is covered in Chapter 6 of the JasperReports Ultimate Guide (SECURING DATA IN A DOMAIN). The how-to in this page utilizes some of the same techniques but at the SQL level without the need for a domain. Again, the recommended way to achieve data security is via a domain, this is another less-robust method. That said, let's get started with the example.

Creating Users

Let's create our two users: Max and Joe. Log in as the jasperadmin and create the two users. Once the users are created, add some attributes to the JiProfileAttribute table. This is covered in depth in the JasperReports Server Ultimate Guide, Section 6.2.5.5 "Inserting Attributes into the Profile Attribute Table in SQL". Note that in version 5.0 and above, you can now manage these in the UI, as in the example below:

Note: the example above is for PostgresSQL syntax

For older version of the product or if you prefer to do it programatically (or through API):

For Max (in my case user id #10):

INSERT INTO JIProfileAttribute( attrName
                               ,attrValue
                               ,principalobjectclass
                               ,principalobjectid)
                        values( "Cities"
                               ,"'Alameda','Beverly Hills','Los Angeles','San Francisco','San Diego'"
                               ,"com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoUser"
                               ,10);

For Joe (in my case user id #11):

INSERT INTO JIProfileAttribute( attrName
                               ,attrValue
                               ,principalobjectclass
                               ,principalobjectid)
                        values( "Cities"
                               ,"'Los Angeles'" 
                               ,"com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoUser"
                               ,11);

NOTE: this is for MySQL syntax

You should see the Profile Attributes in JasperReports Server's User Interface now:

Creating the Report

Now a report can be created. There's a few special things needed to take advantage of the Profile Attributes.

Query

In this example the query is structured something like this:

 
SELECT sum(store_sales) store_city
  FROM stores
 WHERE store_city
    IN ($P!{LoggedInUserAttribute_Cities})

So for example, what JasperReports Server will do when Max runs this report is to expand the WHERE clause to:

 ... WHERE store_city 
        IN ( 'Alameda&'
            ,'Beverly Hills&'
            ,'Los Angeles'
            ,'San Francisco'
            ,'San Diego&')

Parameter

In order for the report to be aware of the Logged In User Attributes you will need to declare them in the form of Parameters in your report.

NOTE: Make sure there is no default value for the parameter, sometimes iReport will fill in a blank default value, clear it out in the XML file

Results

So when Joe runs the report he only sees the cities he's allowed to see:

And when Max runs the report he sees the cities he's allowed to see:

Other Considerations

Input Controls

The same logic can be applied to Query based Input Controls so that the user only sees applicable fiels. An example Query for an Input Control might look like this:

SELECT store_city
  FROM stores
 WHERE store_city IN ($P!{LoggedInUserAttribute_Cities})

Limitations

Not every user has the Profile Attribute of Cities, for example running this report as jasperadmin caused errors. An additional parameter was added to the example report. The parameter LoggedInCheck was added to generate valid SQL even when $P{LoggedInUserAttribute_Cities} is empty.

This was acheived via the default value expression of LoggedInCheck, like so:

$P{ LoggedInUserAttribute_Cities}.isEmpty()
    ? "'dummy'"
    : $P{LoggedInUserAttribute_Cities
   }

In addition the chart in the example report is dependant on getting data back from SQL so there's a Print When Expression on the chart to only print if the $P{LoggedInUserAttribute_Cities} is not empty, here is that expression:

$P{LoggedInUserAttribute_Cities}.length() > 0

Example Report

• Sample.zip - this file contains the JRXML necessary to deploy to Jasperserver. It is not necessary to create input controls on the JasperReports Server side.
• Choose /analysis/datasources/FoodmartDataSourceJNDI as this report's data source

See Also

• Designing_Column-Based_Security_into_Standard_Reports - similar concept but using Roles vs Attributes and Columns vs Rows.
• Built-in_Parameters_for_Logged_In_User - A list of all built-in paramters for Logged in Users
• Accessing_the_Logged_In_User_within_a_Query-based_Input_Control - Query Based input controls with LoggedInUser
• JasperReports Server Administration Guide: Built-in Parameters for Query-based Input Controls