This documentation supports the 9.1 version of Remedy IT Service Management Suite.

To view the latest version, select the version from the Product version menu.

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

  1. Log on to the BMC Remedy Mid-Tier as a Reporting Administrator.
  2. Choose Applications > Smart Reporting > Smart Reporting console.
  3. On the BMC Remedy Smart Reporting console, choose Administration > Admin Console, and from the Data Sources option, click AR System.
  4. On the Data Source details, under Security, select Yes for Source Filters. An additional step is added to at the top of the page.
  5. Click Step 2.
  6. In the Filters section, click Add.
  7. Select Scheduled SQL Query Data Entry option and then click Next.
  8. Click Edit in the Available Filter Types box.
  9. On the Filter Type dialog box, click Add and enter a unique code and description. Click Add again, and then click Save.
  10. 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:

    1. Add the filter query

      Notes

      - 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 Group with an available filter type or the new one added in Step 9.

      SELECT 'User ID',
      `CTM:SupportGroupAssocPeopleLookUp`.`Login ID`+'@bmc',

      'Support Group',

      `CTM:Support Group`.`Support Group Name`

      FROM `AR System Schema`.`CTM:SupportGroupAssocPeopleLookUp`

      INNER JOIN `AR System Schema`.`CTM:Support Group`

      ON

      ( `CTM:SupportGroupAssocPeopleLookUp`.`Support Group ID` = `CTM:Support Group`.`Support Group ID`

      )

    2. Set the Frequency for the query as required and click Save.

  11. Click the Filter name to open the details.
  12. Click the Refresh this filter now link to view filtered data.
  13. 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.
  14. 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.
    1. On the BMC Remedy Smart Reporting console, go to Administration > Admin Console.
    2. Under views select AR System > IncidentManagement, and click Edit.
    3. For Edit Type, select Clone and click Continue.
    4. Click Step 2 on the top of the page.
    5. Under Available Fields, expand the Incident detail folder and search for Assignee Group field.
      Double click the field to open the field properties.
    6. On the Access tab, from the Access Filter drop down box, select the Support Group filter you created and click Save.
    7. 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.

Note

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.
Was this page helpful? Yes No Submitting... Thank you

Comments

  1. Michiel Heijmans

    The access filters are a powerful feature to restrict access to data in Smart Reporting. I struggled to configure this following the above instructions.I found it difficult to understand how the SQL query had to be built.

    The following Yellowfin link helped me understand how to build the correct SQL query: http://www.yellowfinbi.com/YFForum-Passing-a-User-ID-to-a-stored-procedure-?thread=102596.

    Carefully note the following:

    Enter a SQL query that will return all User IDs from your database. Yellowfin uses this query to keep track of how to filter data for each user. The result set must return four columns: Identifier Type, Identifier ID, Reference Type, Reference Value. The first two columns are used by Yellowfin to identify a Yellowfin user. The last two columns specify the value that Yellowfin will use to filter data for that user (the value returned in the third column must match the Code you gave to the filter type in step 3).

    May 10, 2016 09:07
    1. Sirisha Dabiru

       Hi Michiel Heijmans,

      Thanks for letting us know about the SQL Query. I will check with the SMEs and update the documentation.

       

      Regards,

      Sirisha

       

      Abhijeet Teli

      Aug 25, 2016 04:48
    1. Sirisha Dabiru

       Hi Michiel Heijmans,

       

      Please see the snapshot below for the explanation of the query and let me know if it helps:

       

       

      Regards,

      Sirisha

       

      Sep 13, 2016 03:39
  2. Adam Newhall

    I need help editing the SQL query- I've tested the support group version of this filter and would like to change it to the company- taking into account whether or not a user's profile has Unrestricted Access before restricting the access to just the company. FYI- we have Smart Reporting v 7.1, on ITSM 9.1 (no SP's or SP1, not sure which) and we are multi-tenancy in ITSM in the cloud, not local.

    Jan 23, 2018 12:48