How to generate a BDM Permissions Audit

To assist data owners in keeping permissions to their data up to date, it is often desirable to provide them with a listing of who currently has membership to their BDM permissions groups.

Select spriden_last_name "Last Name", spriden_first_name "First Name", 
Otgmgr.Ae_Login.Usrnam "Username", Otgmgr.Ae_Als.Aliasname "Permissions Group", 
nvl((select distinct('Y') from pebempl where pebempl_pidm = gobtpac_pidm and ( pebempl_term_date is null or pebempl_term_date > sysdate +15 )),'N') "Employment Check"
From Otgmgr.Ae_Login
join
  Otgmgr.Ae_Amap
on
  Otgmgr.Ae_Login.Usrid = Otgmgr.Ae_Amap.Usrid  
join
  OTGMGR.ae_als
on
  Otgmgr.Ae_Amap.Aliasid = Otgmgr.Ae_Als.Aliasid
join
  gobtpac
on
  lower(gobtpac_external_user) = lower(Otgmgr.Ae_Login.Usrnam)
join
  spriden
on
  spriden_pidm = gobtpac_pidm and spriden_change_ind is null
Where 
Otgmgr.Ae_Als.Aliasid In
(...)
order by spriden_last_name, spriden_first_name, aliasname
 
 
 
--Then finish off the statement with one of the below as for what module's access you want to pull. Plug these in to the (...) section of the above query.
 
--Fin Aid
(5,2,4,6)
 
--Finance
(16,17,18,19,20,21,22,23,42,63,64,70,71,79,88,107,150)
 
-- HR/Payroll
(24,25,26,27,28,29,30,78)
 
--Admisisons
(99,100,101,102,84,85,103,104)
 
--Registrar
(32,40,39,36,34,35,83,89,90,91,108)
 
--Bursars
(80,81,82)
 
--Criminal Justice
(54,55,56,74,75,76,77,87,98)
 
--COBA
(66,67)
 
--Sam Center
(45,46,47)
 
--Res Life
(43,44,72)
 
--Academic Affairs
(48,49,50)
 
--MAHP
(52,53,73)
 
--Honors
(92,93,94)
 
--Health Center
(51,65)
 
--International
(68,69)
 
--Honors
(92,93,94)
 
--Testing Center
(57,58,59)
 
---IT
(31,33,41,1,110)
 
 
You can then right-click on the query results, and select Export. In the Export Screen, set the Format to "excel 2003+ (xlsx)" and choose where you would like the file saved, it is a good idea to include the date you are pulling this audit in the filename, then click Next, then click Finish. I will generally then open the file to verify it looks valid, resize the columns to the data widths, freeze the top row that has the headers in it, and turn on filtering. You can then save and provide to the client.
 
 
To check if this knowledge article needs to be updated, you can run the below query to find any groups that aren't included in the ones listed above:
Select * From Otgmgr.Ae_Als Where Aliasid Not In
--Fin Aid
(5,2,4,6)
and aliasid not in
--Finance
(16,17,18,19,20,21,22,23,42,63,64,70,71,79,88,107,150)
and aliasid not in
-- IT
(31,33,41,1,110)
and aliasid not in
-- HR/Payroll
(24,25,26,27,28,29,30,78)
and aliasid not in
--Admisisons
(99,100,101,102,84,85,103,104)
and aliasid not in
--Registrar
(32,40,39,36,34,35,83,89,90,91,108)
and aliasid not in
--Bursars
(80,81,82)
and aliasid not in
--Criminal Justice
(54,55,56,74,75,76,77,87,98)
and aliasid not in
--COBA
(66,67)
and aliasid not in
--Sam Center
(45,46,47)
and aliasid not in
--Res Life
(43,44,72)
and aliasid not in
--Academic Affairs
(48,49,50)
and aliasid not in
--MAHP
(52,53,73)
and aliasid not in
--Honors
(92,93,94)
and aliasid not in
--Health Center
(51,65)
and aliasid not in
--International
(68,69)
and aliasid not in
--Honors
(92,93,94)
and aliasid not in
--Testing Center
(57,58,59)
and aliasid not in
-- AX Report Manager
(86)
and aliasid not in
-- Global group
(3);