Row level security - Authorization issue

[dogfuel]

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?

 

Thanks!

[dogfuel]

I'll rephrase ....

Is it possible to use the logged in JasperServer userid as a parameter in the report query (to constrain results to a subset of rows based on this id)?

 

Thanks!

[lucianc]

Yes.

 

Code:

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

 

HTH,

Lucian

[dogfuel]

Is there a way I can call the LoggedInUser.getuserID()

or .getUserEMAIL()?

I would like to retrieve the user keys and/or user emails.

 

Thanks.

[swood]

The User interface looks like:

 

Code:

public interface User extends AttributedObject {
	
	public String getUsername();
	
	public String getFullName();

	public String getEmailAddress();

	public boolean isExternallyDefined();

	public boolean isEnabled();
	
	public Set getRoles();

}

 

 

Sherman

JasperSoft

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

[dogfuel]

perfect - thank you

[jeffv]

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.

[jeffv]

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.

[mmflynn]

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?

 

Mary

[jeffv]

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 =

Code:

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

[swood]

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

 

Code:

<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:

 

Code:

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

 

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

 

 

 

Sherman

JasperSoft

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

[jeffv]

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:

Code:

<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>
	</parameter>

 

I am connecting to a Postgres database.

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

[swood]

Ahh.. I see the final SQL is wrong.

 

Try:

 

Code:

...WHERE user_name = '$P{LoggedInUsername}'

 

 

Sherman

JasperSoft

[swood]

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?

 

 

Sherman

JasperSoft

[jeffv]

Thanks, at least I'll quit trying to go that route. How can I extract the string from the collection/set of Roles return by the LoggedInUser?

 

Where do I log an enhancement request?

[zhu001]

Hi,

 

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'

 

Thanks

 

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

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

[shivaligandhi]

hi,

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

[janna_m]

 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">

<defaultValueExpression><![CDATA[$P{LoggedInUser}.getTenantId()]]></defaultValueExpression>

</parameter>

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)

atnet.sf.jasperreports.engine.query.JRAbstractQueryExecuter.appendParameterClauseChunk(JRAbstractQueryExecuter.java:347)

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..