Jump to content
Changes to the Jaspersoft community edition download ×

Row level security - Authorization issue


Recommended Posts

I need to restrict users to a specfic organization_id (an integer key).


I do not mind joining from the fact table to a row level security table that associates user ids with an organization_id - is it simple to pickup up the JS user id as a parameter?



Link to comment
Share on other sites

  • Replies 17
  • Created
  • Last Reply

Top Posters In This Topic




<parameter name="LoggedInUser" class="com.jaspersoft.jasperserver.api.metadata.user.domain.User"/>
<parameter name="LoggedInUsername" isForPrompting="false">
<queryString>SELECT .. WHERE .. $P{LoggedInUsername} ..</queryString>




Link to comment
Share on other sites

  • 4 weeks later...

The User interface looks like:



public interface User extends AttributedObject {

public String getUsername();

public String getFullName();

public String getEmailAddress();

public boolean isExternallyDefined();

public boolean isEnabled();

public Set getRoles();






Post edited by: swood, at: 2008/02/14 07:25

Link to comment
Share on other sites

  • 2 months later...

I would like to use all/part of a special role as a parameter in a report. I would like to read through the Set of roles for a user, identify the 'special' role based on its format or a character and then return a string/integer to a parameter. Like getUsername.


What would be best; extend/modify the User Interface to include a function to do this, use a scriptlet, or create a stand alone java class? Extending or modifying the User interface is not ideal because I don't want to fix it each time I upgrade.


My goal is to reuse this parameter across many reports as a data source filter.

Link to comment
Share on other sites

I have a solution that works, but is not ideal because it requires maintaining a separate user name table in Postgres. I will take suggestions for improvement.


I created a 'client user' table in Postgres that contains the user name as it appears in Jasper, the client key and boolean for active users. This table is added to all 'client' reports with a where clause to filter 'user_name' = $P{LoggedInUsername}.


I would much rather parse out the client key from a user role.


I tried using an input control, but it wouldn't work due to a SQL syntax error at "= $". Probably didn't have a value for the parameter at that time. Just guess.

Link to comment
Share on other sites

I haven't quite followed everything you want to do, but I may be able to help with your current problem. Can you please post the definition (or a screen shot) for the parameter, the SQL for your query (the where clause should suffice), and a bit more info on the error?


Re: the error, did you run the report from iReport or JasperServer?



Link to comment
Share on other sites

I want to reuse reports across many different client users. Each client user must only see their subset of data so the reports need to be filtered to their key.


My plan is to assign a key specific role to client users. The text for these roles would contain the clients unique key (6 digit interger). All client specific reports would be created with this filter; many users, one report, specific client data.


Here are the details of the input control:

* Single Select Query

* Mandatory = yes

* Query =

SELECT * FROM tbl_jasper_users WHERE user_name = $P{LoggedInUsername}

* Parameter works; it currently displays in a text field in the report


Errors being returned:

com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : qrySelectKey


net.sf.jasperreports.engine.JRException: Error executing SQL statement for : qrySelectKey


org.postgresql.util.PSQLException: ERROR: syntax error at or near "= $"[/ol]



Full error is attached.


Post edited by: jeffv, at: 2008/04/29 18:55 [file name=inputcontrolError.txt size=30341]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/inputcontrolError.txt[/file]

Post edited by: jeffv, at: 2008/04/29 18:58

Link to comment
Share on other sites

What Mary was asking was: Did you declare the LoggedInUser parameter in the JRXML? Like:



<parameter name="LoggedInUser" class="com.jaspersoft.jasperserver.api.metadata.user.domain.User"/>


It has to be called "LoggedInUser".


To get the user name as a parameter, create another parameter:


<parameter name="LoggedInUsername" class="java.lang.String"/>
<defaultValueExpression ><![CDATA[$P{LoggedInUser}].getUsername()]></defaultValueExpression>


Then you can use $P(LoggedInUsername} in your query.






Post edited by: swood, at: 2008/04/29 19:42

Link to comment
Share on other sites

Yes I did. I have used $P{LoggedInUsername} in a text field in the same report to make sure I am receiving the user name. It works fine.


I now want to use that parameter in the WHERE clause of an input control that will filter the list of keys available for that user.


JRXML file contains:

<parameter name="LoggedInUser" isForPrompting="true" class="com.jaspersoft.jasperserver.api.metadata.user.domain.User"/>
<parameter name="LoggedInUsername" isForPrompting="false" class="java.lang.String">
<defaultValueExpression ><![CDATA[$P{LoggedInUser}.getUsername()]]></defaultValueExpression>


I am connecting to a Postgres database.

Post edited by: jeffv, at: 2008/04/29 20:02

Link to comment
Share on other sites

Teodor pointed out to me that you don't need the single quotes in the query.


And I did not read all your details properly, so I gave the wrong advice - sorry!


Looking at it again, you cannot filter the query input control in the way you are wanting. Could you log an enhancement?





Link to comment
Share on other sites

  • 2 months later...



Does anyone know if it is possible to reference custom attributes from ldap entry through the LoggedInUsername in a sql? If not, could it be done any other way?

I'm trying to reference attributes like Region, Location, Timezone any custom attribute....

Something like


select * from table where

region = '$P{LoggedInUsername}.region'




Post edited by: zhu001, at: 2008/07/01 18:37

Post edited by: zhu001, at: 2008/07/01 18:38

Link to comment
Share on other sites

  • 1 year later...


i have implemented the row level security in the same way, but when i use those reports in my dashboard

(using jasperserver pro 3.7.2)

i get the following error even after supplying all the default values:

The report is missing default values for atleast one mandatory parameter.


Please suggest the resolution

Link to comment
Share on other sites

  • 2 years later...

 I have similar problem in getting the logged in organization/tenant id & using them in the SQL query.

I used a parameter & captured the organization/tenant id as below, 

<parameter name="LoggedInUser" class="com.jaspersoft.jasperserver.api.metadata.user.domain.User"/>

<parameter name="LoggedInTenantId" class="java.lang.Integer" isForPrompting="false">



And printed this parameter in the main report - the proper value gets printed. 

I have a input control whose SQL Query is as below, 

select id, name from team_mst where TEAM_ID in (select team_id from access_mst where USER_ID = (Select USER_ID from user_mst where USER_NAME = '$P!{LoggedInUsername}' and ENTERPRISE_ID =  $P!{ LoggedInTenantId} ) and ENTERPRISE_ID =  $P!{ LoggedInTenantId} ) and VIR_TEAM_TYPE in (0,2) and DEACTIVATED = 0;

Before loading the main report the input control query itself fails with error as,

2012-07-24 17:00:37,881 ERROR errorPage_jsp,http-8080-1:487 - stack trace of exception that redirected to errorPage.jsp

net.sf.jasperreports.engine.JRRuntimeException: Parameter "LoggedInTenantId" does not exist.

at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.checkParameter(JRAbstractQueryExecuter.java:627)


at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.appendQueryChunk(JRAbstractQueryExecuter.java:262)

at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.parseQuery(JRAbstractQueryExecuter.java:247)


The parameter have been defined. I'm not sure what is required here... & how can we resolve this...PLease anyone can show some pointer or help ...

I have attached the JRXML that I'm using..

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...