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
Page 1 / 1
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
Reply
Log in to the Mark43 Guardian Hub
Mark43 Customers log in through SSO. Prospective customers may create an account.
Customer SSO Login
Mark43 Customer SSO Loginor
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.