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

    Hi,

    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:

    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <CDADescriptor>
       <DataSources>
          <Connection id="test" type="sql.jndi">
             <Jndi>DW</Jndi>
          </Connection>
       </DataSources>
       <DataAccess access="public" connection="test" id="test" type="sql">
          <Name>test</Name>
          <Cache duration="3600" enabled="true"/>
          <Columns/>
          <Parameters>
             <Parameter default="" name="filterParam" type="String"/>
             <Parameter access="private"
                        default="${[system:data-access/settings.xml{data-access-roles}]}"
                        name="roleParam"
                        type="String"/>
          </Parameters>
          <Query><![CDATA[EXEC [dbo].[rep_get_filter_values] '2663693', ${filterParam}, ${roleParam}]]></Query>
       </DataAccess>
    </CDADescriptor>
    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

    Default

    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!

    Thanks,

  3. #3
    Join Date
    Nov 2011
    Posts
    1,229

    Default

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

    You're right about the private parameters.
    Pedro Vale
    --
    CTools Product Development
    http://www.webdetails.pt

  4. #4

    Default

    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
    Code:
    ${[security:principalRoles]}
    And you can also get the username with
    Code:
    ${[security:principalName]}
    Cheers!


  5. #5
    Join Date
    Nov 2011
    Posts
    1,229

    Default

    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
    http://www.webdetails.pt

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.