Skip to main content

In any report, you can view the “Managed Permissions” key lock icon within each report.  In the USER SETTINGS section only, it lists all officer that can “Can Edit” permission.  I need to isolate this area and add to an existing report, but I’m having trouble finding the exact table or view.  Any clues?  TIA

It's in rms.entity_permissions

  • entity_id would be the report (or other item). For reports, the entity_type is the value of id in ref.entity_type_new where name = 'REPORT'
  • role_id is taken from rms.roles
  • operation_type is the actual permission depending on the bit where
    00001 (1) = Can View
    00100 (4) = Can Manage
    01000 (8) = Can Edit
    10000 (16) = Can Find

Here's the query I use to determine all the reports that are secured (where the department role is "Can Find" or is missing)

DECLARE @DepartmentRole bigint = (SELECT id FROM rms.roles WHERE name = 'Department - elkgrove-ca')
DECLARE @report_type bigint = (SELECT id FROM ref.entity_type_new WHERE name = 'REPORT')

DECLARE @permission_can_find int = 16
/*
00001 (1) = Can View
00100 (4) = Can Manage
01000 (8) = Can Edit
10000 (16) = Can Find
*/


-- Where the department role has "Can Find" permission
SELECT entity_id AS report_id
FROM RMS.entity_permissions
WHERE
role_id = @DepartmentRole
AND entity_type_id = @report_type
AND operation_type = @permission_can_find

UNION

-- Where the department role doesn't exist
SELECT r.id AS report_id
FROM
RMS.entity_permissions ep
RIGHT JOIN RMS.reports r ON ep.entity_id = r.id
WHERE
ep.role_id = @DepartmentRole
AND ep.entity_type_id = @report_type
AND ep.entity_id IS NULL

 


@Kevin M. Thanks for the quick response.  I don’t see RMS.entity_permissions in my Data Lake, but I’ll definitely ask M43 to add it, which makes me wonder what other tables and/or views are not visible to me.  Once I get M43 to add that table, I’ll check out the code.


Reply