Submission Notes: This report was submitted on 2019-10-04 by Dan Scott of Laurentian University. It was created by Dan Scott.

Version: Used on Evergreen 3.1

Data Sources Used: actor.org_unit, actor.usr, permission.grp_tree, permission.usr_work_ou_map

Purpose: – List all people who have permissions, and their associated permissions, based on permission groups at one or more libraries.

Running Tips/Guidelines: There a few parameters you will want to set. Where it says 'ORG_UNIT_ID' inside the "WHERE work_ou_in ()" clause, put the org unit ID of the library that represents the working location where those staff have permissions. You can have one or more comma-delimited ORG_UNIT_ID parameters.

WITH x AS (
  SELECT au.id AS usr, family_name, first_given_name, au.email, expire_date, aou.name
  FROM actor.usr au
    INNER JOIN permission.usr_work_ou_map ON usr = au.id
    INNER JOIN actor.org_unit aou ON aou.id = work_ou
  WHERE work_ou IN (ORG_UNIT_ID, ORG_UNIT_ID, ORG_UNIT_ID))
SELECT x.*, pgt.name AS group_name
FROM x
  INNER JOIN permission.usr_grp_map pugm ON x.usr = pugm.usr
  INNER JOIN permission.grp_tree pgt ON pgt.id = pugm.grp
ORDER BY family_name, first_given_name, x.name, group_name
;

Submission Notes: This report was submitted on 2019-10-04 by Dan Scott of Laurentian University. It was created by Dan Scott.

Version: Used on Evergreen 3.1

Data Sources Used: actor.org_unit, actor.usr, permission.grp_tree, permission.usr_work_ou_map

Purpose: – List all people who have individually assigned permissions at one or more libraries.

Running Tips/Guidelines: There a few parameters you will want to set. Where it says 'ORG_UNIT_ID' inside the "WHERE work_ou_in ()" clause, put the org unit ID of the library that represents the working location where those staff have permissions. You can have one or more comma-delimited ORG_UNIT_ID parameters.

WITH x AS (
  SELECT au.id AS usr, family_name, first_given_name, au.email, expire_date, aou.name
  FROM actor.usr au
    INNER JOIN permission.usr_work_ou_map ON usr = au.id
    INNER JOIN actor.org_unit aou ON aou.id = work_ou
  WHERE work_ou IN (ORG_UNIT_ID, ORG_UNIT_ID, ORG_UNIT_ID))
SELECT x.*, ppl.code AS perm_name
FROM x
  INNER JOIN permission.usr_perm_map pupm ON x.usr = pupm.usr
  INNER JOIN permission.perm_list ppl ON ppl.id = pupm.perm
ORDER BY family_name, first_given_name, x.name, ppl.code
;