Data Source based on Logged In User?

[abarnes]

Hi all,

I am interested in implementing row-level security on our database and to provide easy custom reporting to our users.

 

I have done some searching and although this topic comes up often I couldn't find any answers to my planned solution to this problem.

 

My plan is to define MySQL views that implement row level security based on the MySQL user logged in.  I have tested this and it appears to work perfectly (or as well as can be expected from MySQL implementation of views).

 

My current stumbling block is being able to dynamically define the data source that a report will use.

 

The simplest (possibly dirtiest) way would be if I could define the Jasper JDBC data source username to be the username of the person logged in, I could then match it up with a correctly privileged user in MySQL.  Does anyone know if this is possible?

 

My second and preferred alternative was to create a custom data source.  However does anyone know if its possible to get the username of the person logged in from inside the custom data source class?  If it was I could then do a lookup on username vs allowed access to return a JDBC connection limited to their data area.

 

The above two methods seem to have a great advantage over placing the logged in user in the WHERE clause as you can now allow users to define their own SQL queries safe in the knowledge they can only get at the data in defined VIEW.

 

If anyone has ideas on how else I could achieve the above I would be very interested to hear them.

 

Thanks for any help / advice.

 

Alex

Post Edited by abarnes at 05/11/2009 15:39

[kenyoungsolutionary.com]

I think this will depend on you application architecture. In our case, we are using Oracle VPD and the Springframework. In our case, using Spring AOP, we intercept the call to java.sql.Datasource.getConnection(), and replace the connection (or in our case, add additional variables to the connection) prior to having it delivered back to the application, or to Jasper.

[abarnes]

Sorry I dont think I understand what you mean by "depends on your application architecture".

 

The application is a web based CRM written in PHP but this has no impact on how JasperServer presents the data from MySQL.

 

*Edit on my initial post, it would appear that the community version cannot allow standard users to define new reports (I assume the commercial version can).

Post Edited by abarnes at 05/11/2009 15:43

[abarnes]

Does anyone know if a custom data source can read report parameters?

 

For example if on all our global reports we put a hidden parameter of $P{LoggedInUser}.getFullName() could the data source read those and use to select the correct database connection?
 

 

Thanks