|
Implementing Security for Reports based on the Data-Warehouse
Data-security for reports based on the data-warehouse will ALWAYS be based on the "User-Person-Link".
|
Prerequisites
- If you intend to filter on a certain attribute, the system needs to be able to establish a link from the User
 to Person <attribute>. - In other words: the attribute used for a filter must be part of the Person record.
|
|
NOTE:
- Once security has been applied, you may no longer be able to 'preview' the report in the Report Designer.
- Therefore it is important to only add the security after you have finished designing the report.
- To verify the filter, you will need to run the report from within SAI360.
|
Report Security - generic Information
To implement security, you will need to include a WHERE condition in your SQL script which will then work as the security filter for the report.
A key element for this filter is a Pentaho Environment Variable: env::username
If you want to ...
|
Perform these steps ...
|
Apply data security to a record, so that a report only shows records to which a user has access.
|
To apply data security to a report, you will have to
- Define and save a filter, which makes use of the ID of the current user. To do so, you can apply the Pentaho Environmental Variable by using the syntax:
${env::username}
The following example retrieves the current user's Department Details:
SELECT
"dbo"."rt_person"."dwk_department",
"dbo"."rt_person"."user_name"
FROM
"dbo"."rt_person"
WHERE
"dbo"."rt_person"."user_name" = ${env::username}
In this example, this filter was saved as "Get Department from User Person Link"
- Apply and save a Report Parameter, where the above filter is being applied. To allow this parameter to work as a security filter, please make that the following settings are applied:
- Mandatory = Yes
- Hidden = Yes
- Display Type = <blank>
- Use first value if default value formula results in NA = Yes
|
Link Parameter
|
Like with any other parameter, you can link this parameter to other parameters.
- Sample Requirement
A report "Incident by Responsible Supervisor" needs to be created where
- a user can select from a list or persons
- but this list must only show persons who work in the user's department.
- Solution:
You apply a filter to the SQL script which is used to retrieve the 'Users', which in turn is used to feed the parameter:
SELECT distinct
Persons.last_name,
Persons.dwi_person,
Persons.dwk_department,
(Persons.last_name + ', '
+ Persons.first_name) as FullName
FROM "dbo"."rt_incident" as Incidents
LEFT OUTER JOIN "dbo"."rt_person" as Persons ON Incidents.dwk_responsible_supervisor = Persons.dwi_person
INNER JOIN "dbo"."rt_department" as Departments ON Persons.dwk_department = Departments.dwi_department
INNER JOIN "dbo"."dn_department_parent_department_cl" as Rollups ON Departments.dwi_department = Rollups.dwk_department
INNER JOIN "dbo"."rt_department" as ParentDepartment ON ParentDepartment.dwi_department = Rollups.dwk_parent_department
WHERE Rollups.dwk_parent_department = ${UserPersonLink}
-- This value is returned from the hidden "security" parameter in the report, as shown above.
ORDER BY
Persons."last_name" ASC
|
|
