Setting row-level security for Reports
Depending on the user's group, row level security can be implemented for each report.
To apply row level security
- Log on to the BMC Remedy Mid-Tier as a Reporting Administrator.
- Choose Applications > Smart Reporting > Smart Reporting console.
- On the BMC Remedy Smart Reporting console, choose Administration > Admin Console, and from the Data Sources option, click AR System.
- On the Data Source details, under Security, select Yes for Source Filters. An additional step is added to at the top of the page.
- Click Step 2.
- In the Filters section, click Add.
- Select Scheduled SQL Query Data Entry option and then click Next.
- Click Edit in the Available Filter Types box.
- On the Filter Type dialog box, click Add and enter a unique code and description. Click Add again, and then click Save.
On the Filters page, from the Refresh Type options, select Updates will overwrite current entries. All previous records will be deleted.
Define a filter query:
Add the filter query
- The Login ID field defined in this sample query requires that you concatenate the Smart Reporting customer name with the login ID that the user used when onboarding the customer. In the example, we have used bmc. Replace it with the login ID and customer name as used during onboarding.
- In the filter query defined below, replace
Support Groupwith an available filter type or the new one added in Step 9.
SELECT 'User ID',
`CTM:Support Group`.`Support Group Name`
FROM `AR System Schema`.`CTM:SupportGroupAssocPeopleLookUp`
INNER JOIN `AR System Schema`.`CTM:Support Group`
( `CTM:SupportGroupAssocPeopleLookUp`.`Support Group ID` = `CTM:Support Group`.`Support Group ID`
Set the Frequency for the query as required and click Save.
- Click the Filter name to open the details.
- Click the Refresh this filter now link to view filtered data.
- Click Step 1 on the top of the page, and then click Save.
The defined query fetches the user IDs and associated Support Groups. Once we have this data, we need to assign this source filter to the appropriate field in the View to implement row level security in the reports.
- Let's take the example of assigning the source filter to the Assignee Group field in the Incident Management module. It allows you to select the filter when you create a report using the Incident Management view. The report data will be restricted to the associated support group of the logged in user.
- On the BMC Remedy Smart Reporting console, go to Administration > Admin Console.
- Under views select AR System > IncidentManagement, and click Edit.
- For Edit Type, select Clone and click Continue.
- Click Step 2 on the top of the page.
- Under Available Fields, expand the Incident detail folder and search for Assignee Group field.
Double click the field to open the field properties.
- On the Access tab, from the Access Filter drop down box, select the Support Group filter you created and click Save.
- Click Step 4 and click Activate. Make sure you remove (Clone) from the view name before you click Activate again to activate the view.
Now when you create a report using the Incident Management view, in the Data section of the report, the Source Filters section is displayed under the Report Data section. Select this option to apply the filter values. Logged on users will now see report data based on the support group that they belong to.
The process described in this example describes how to implement row level security based on Support Groups. You can use the same procedure to define row level security based on other fields. For this:
- Update the Select statement in the SQL query provided in step 10 and replace it with the appropriate field and table names.
- Update the corresponding objects in steps 14b and 14e.