Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Correct way to pass user roles privately in CDA query

  1. #1

    Default Correct way to pass user roles privately in CDA query


    I am switching some of my queries to SQL to improve performance however I need to pass the roles of the currently logged in user as this drives our security.

    I do not want any way for the user to hack the reports and pass the role themselves.

    So reading some of the other posts it seems that I can pass a private parameter in my query.

    I could set up a PRPT and then I can pass the parameter ${[env::roles]} and it works. But this is overkill as I have to make a PRPT each time and set up all the columns, etc.

    So I wanted to just use the SQL over JNDI connection within CDE and add a private parameter which passes the logged in users roles in the query.

    So it appears online that I can use the value ${[system:data-access/settings.xml{data-access-roles}]} to ascertain the users roles?

    I have a CDA test query as follows:

    <?xml version="1.0" encoding="UTF-8"?>
          <Connection id="test" type="sql.jndi">
       <DataAccess access="public" connection="test" id="test" type="sql">
          <Cache duration="3600" enabled="true"/>
             <Parameter default="" name="filterParam" type="String"/>
             <Parameter access="private"
          <Query><![CDATA[EXEC [dbo].[rep_get_filter_values] '2663693', ${filterParam}, ${roleParam}]]></Query>
    But I have two issues:

    1. This is not passing the user roles across, so I think I must be using the wrong value? I was going to use a custom parameter and do this.dashboard.context.role but not sure if this is the most secure approach as it appears otherwise in another forum thread.
    2. When I preview the query URL I can see the private parameter is added into the URL. Is this totally secure when the parameter is set to private? I tried changing the URL and it seems to disregard the parameter value in the URL and use the default one. So I think it is OK. Just want to be certain.

    Thank you!

  2. #2


    I was hoping somebody might have faced this problem before.

    From the looks of things I think I need to set up a custom action sequence to run at login to store the users roles as a session variable? Only then can I use it as a parameter in CDA such as ${[session:userRoles]}.

    Does anybody know if this is true? I feel the roles should already be a session variable the I can access as they are used all over the platform.

    Any help would be greatly appreciated!


  3. #3
    Join Date
    Nov 2011


    Should be ${[security: principalRoles]}. That gives you the roles.

    You're right about the private parameters.
    Pedro Vale
    CTools Product Development

  4. #4


    Thank you so much Pedro. That was easy wasn't it. FYI for anybody else out there it doesn't work with the space after security: . As I just found out Pedro wrote it like that because the text gets turned into a Smiley otherwise!

    Should be
    And you can also get the username with

  5. #5
    Join Date
    Nov 2011


    yeah, I was lazy and did not use the /code or /pre or whatever the tag is. sorry for that.
    Pedro Vale
    CTools Product Development

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.