What is the best method to conduct an OnBase Group/User Security Audit?

[kliebeng]

Hi there,

I am tasked with conducting an audit of the security/access of OnBase users and groups.   I've ran the User Group / Right Configuration Report and find it to be a little more busy and cumbersome that I would like.  Its fine for ME as a technical person, but I don't think  distributing it to a department or group would be helpful unless I flatten it out. 

My intent is to reach out to each a functional owner(s) of each group to have them verify the user memberships and accesses within each group.  

The current solution is holding a meeting(s) where we will look at the Users Groups and Rights menu in Config and get feedback from the content owners on what their setup should be.   This is conceptually pretty easy but I feel that it could be difficult to schedule these meetings or track down a user in every group with the authority to make these decision while on a Zoom meeting.   I would like to find a way to accomplish the same thing a little less real time and give them some liberty to really consider their content permissions and report back asynchronously.

I could be making a mountain out of a mole hill and idea of going through the UI together could prove to be the least painless-but I was just wondering if there is another way to accomplish this that I am not privy to.   I do not have database access or anything like that, so I can have a dev generate anything custom like another report or a dashboard to help me.

I would love to hear any thoughts or considerations the community has to offer.  Thanks so much!

[kliebeng]

If you could share that SQL, I would greatly appreciate it.


We have devs in house but none are appointed to assist with my task or have anything to do with this service.  As far as what the sysadmin of our OnBase infrastructure has told me at passing, we may not have authorization to do direct database queries outside of the client functionality.  I can get clarification from him and our sales rep to see if maybe I misunderstood something. 

[Larissa_Armand]

Here's for details for a particular user (though you could modify this as needed) to show what user groups they are in and what basic permissions that group has. Written for Oracle DB so you'd need to modify for SQL Server. 

select rtrim(U.REALNAME) as UserName, rtrim(G.USERGROUPNAME) as GroupName, rtrim(D.ITEMTYPENAME) as DocumentType,
case
when BITAND(GXD.FLAGS, 2) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 512) > 0 then 'X'
end as RetrieveDoc,
case
when BITAND(GXD.FLAGS, 1) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 4) > 0 then 'X'
end as CreateDoc,
case
when BITAND(GXD.FLAGS, 😎 > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 8388608) > 0 then 'X'
end as ModifyDoc,
case
when BITAND(GXD.FLAGS, 16) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 33554432) > 0 then 'X'
end as SaveRotation,
case
when BITAND(GXD.FLAGS, 4) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 65536) > 0 then 'X'
end as DeleteDoc,
case
when BITAND(GXD.FLAGS, 262144) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE6, 131072) > 0 then 'X'
end as DeleteUncommitted,
case
when BITAND(GXD.FLAGS, 64) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE1, 32) > 0 then 'X'
end as PrintDoc,
case
when BITAND(GXD.FLAGS, 128) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE1, 64) > 0 then 'X'
end as ExternalMail,
case
when BITAND(GXD.FLAGS, 256) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE1, 1048576) > 0 then 'X'
end as InternalMail,
case
when BITAND(GXD.FLAGS, 32) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 32) > 0 then 'X'
end as ReIndex,
case
when BITAND(GXD.FLAGS, 512) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 256) > 0 then 'X'
end as ViewRevision,
case
when BITAND(GXD.FLAGS, 1024) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 2048) > 0 then 'X'
end as CreateRevision,
case
when BITAND(GXD.FLAGS, 8192) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 32768) > 0 then 'X'
end as ViewVersion,
case
when BITAND(GXD.FLAGS, 16384) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 16384) > 0 then 'X'
end as CreateVersion,
case
when BITAND(GXD.FLAGS, 2048) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 4096) > 0 then 'X'
end as ModifyKeywords,
case
when BITAND(GXD.FLAGS, 4096) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 8192) > 0 then 'X'
end as ViewKeywords,
case
when BITAND(GXD.FLAGS, 32768) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 262144) > 0 then 'X'
end as RestrictedKeywords,
case
when BITAND(GXD.FLAGS, 131072) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 67108864) > 0 then 'X'
end as ViewHistory,
case
when BITAND(GXD.FLAGS, 1048576) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE2, 64) > 0 then 'X'
end as CopyToClipboard,
case
when BITAND(GXD.FLAGS, 65536) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE0, 524288) > 0 then 'X'
end as CreateReference,
case
when BITAND(GXD.FLAGS, 2097152) > 0 then 'X' --override
when BITAND(G.USERPRIVILEGE5, 1024) > 0 then 'X'
end as SeparateDoc
from HSI.USERGROUP G
left outer join HSI.USERGROUPCONFIG GXD ON GXD.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.DOCTYPE D ON D.ITEMTYPENUM = GXD.ITEMTYPENUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where
U.USERNUM = @{pUserNum}
order by 3, 2
;

[Larissa_Armand]

Here's another for configuration items besides document types: 

select rtrim(G.USERGROUPNAME) as GrantedToGroupUser, rtrim(I.ITEMTYPEGROUPNAME) as ConfigurationItemName, 'Document Type Group' as ConfigItemType, '' as UsedIn
from HSI.USERITEMTYPEGRP GXI
left outer join HSI.USERGROUP G ON GXI.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.ITEMTYPEGROUP I ON I.ITEMTYPEGROUPNUM = GXI.ITEMTYPEGROUPNUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where
rtrim(U.USERNUM) = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(D.ITEMTYPENAME) as DocTypeGroupName, 'Document Type' as ConfigItem, (select trim(ITEMTYPEGROUPNAME) from HSI.ITEMTYPEGROUP where ITEMTYPEGROUPNUM = D.ITEMTYPEGROUPNUM) as UsedIn
from HSI.USERGROUPCONFIG X
left outer join HSI.USERGROUP G ON X.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.DOCTYPE D ON D.ITEMTYPENUM = X.ITEMTYPENUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where
rtrim(U.USERNUM) = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(Q.QUEUENAME) as ScanQueue, 'Scan Queue', '' as UsedIn
from HSI.USERGSCANQUEUE GXQ
left outer join HSI.USERGROUP G ON GXQ.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.SCANQUEUE Q ON Q.QUEUENUM = GXQ.QUEUENUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where
rtrim(U.USERNUM) = @{pUserNum}
and Q.QUEUENAME IS NOT NULL
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(CQ.CQNAME) as CustomQuery, 'Custom Query', '' as UsedIn
from HSI.USERGCUSTOMQUERY GXCQ
left outer join HSI.USERGROUP G ON GXCQ.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.CUSTOMQUERY CQ ON CQ.CQNUM = GXCQ.CQNUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where
rtrim(U.USERNUM) = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(F.FORMNAME) as FormTemplate, 'Unity Form Template', (select trim(ITEMTYPENAME) from HSI.DOCTYPE where ITEMTYPENUM = F.ITEMTYPENUM) as UsedIn
from HSI.UFFORMXUSERGROUP GXF
left outer join HSI.USERGROUP G ON GXF.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.UFFORM F ON F.UFFORMNUM = GXF.UFFORMNUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where
rtrim(U.USERNUM) = @{pUserNum}
and F.FORMSTATUS != '1'
and bitand(F.FLAGS,1) > 0
and F.UFFORMNUM != '356' --specified user group box unchecked
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(F.FORMNAME) as FormTemplate, 'Unity Form Template', (select trim(ITEMTYPENAME) from HSI.DOCTYPE where ITEMTYPENUM = F.ITEMTYPENUM) as UsedIn
from HSI.USERGROUPCONFIG X
left outer join HSI.USERGROUP G ON X.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.UFFORM F ON F.ITEMTYPENUM = X.ITEMTYPENUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where rtrim(U.USERNUM) = @{pUserNum}
and F.FORMSTATUS != '1'
and bitand(F.FLAGS,1) = 0
and F.DSTYPE != '2' --based document type box checked
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(N.NOTETYPENAME) as NoteType, 'Note Type', '' as UsedIn
from HSI.USERGNOTETYPE GXN
left outer join HSI.USERGROUP G ON GXN.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.NOTETYPE N ON N.NOTETYPENUM = GXN.NOTETYPENUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where
rtrim(U.USERNUM) = @{pUserNum}
and GXN.NOTETYPEPRIVS != '0'
UNION
select case when DPU.USERTYPE = '1' then (select rtrim(G.USERGROUPNAME) from HSI.USERGROUP G where G.USERGROUPNUM = DPU.USERNUM)
when DPU.USERTYPE = '0' then (select rtrim(U.USERNAME) from HSI.USERACCOUNT U where U.USERNUM = DPU.USERNUM)
end as AccessGrantedToDesc, DP.RPTDATAPROVIDERNAME, 'Data Provider', '' as UsedIn
from HSI.RPTDATAPROVIDERXUSER DPU
left outer join HSI.RPTDATAPROVIDER DP on DPU.RPTPROVIDERNUM = DP.RPTPROVIDERNUM
left outer join HSI.USERACCOUNT U on U.USERNUM = DPU.USERNUM
left outer join HSI.USERGROUP G on G.USERGROUPNUM = DPU.USERNUM
where
(DPU.USERTYPE = '0' and DPU.USERNUM = @{pUserNum})
or
(DPU.USERTYPE = '1' and DPU.USERNUM IN (select USERGROUPNUM from HSI.USERXUSERGROUP where USERNUM = @{pUserNum}))
UNION
select case when DBU.USERTYPE = '1' then (select rtrim(G.USERGROUPNAME) from HSI.USERGROUP G where G.USERGROUPNUM = DBU.USERNUM)
when DBU.USERTYPE = '0' then (select rtrim(U.USERNAME) from HSI.USERACCOUNT U where U.USERNUM = DBU.USERNUM)
end as AccessGrantedToDesc, DB.DASHBOARDNAME, 'Dashboard', '' as UsedIn
from HSI.DASHBOARDXUSER DBU
left outer join HSI.DASHBOARDINFO DB on DBU.DASHBOARDNUM = DB.DASHBOARDNUM
left outer join HSI.USERACCOUNT U on U.USERNUM = DBU.USERNUM
left outer join HSI.USERGROUP G on G.USERGROUPNUM = DBU.USERNUM
where
(DBU.USERTYPE = '0' and DBU.USERNUM = @{pUserNum})
or
(DBU.USERTYPE = '1' and DBU.USERNUM IN (select USERGROUPNUM from HSI.USERXUSERGROUP where USERNUM = @{pUserNum}))
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(L.LIFECYCLENAME) as LifeCycleName, 'Workflow Life Cycle', '' as UsedIn
from HSI.USERGLC GXL
left outer join HSI.USERGROUP G ON GXL.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.LIFECYCLE L ON L.LCNUM = GXL.LCNUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where
rtrim(U.USERNUM) = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(S.STATENAME) as QueueName, 'Workflow Queue', trim(L.LIFECYCLENAME) as UsedIn
from HSI.USERGLCSTATE GXS
left outer join HSI.USERGROUP G ON GXS.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.LCSTATE S ON S.STATENUM = GXS.STATENUM
left outer join HSI.LIFECYCLE L ON S.SCOPE = L.LCNUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where rtrim(U.USERNUM) = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(T.TASKLISTNAME) as TaskListName, 'Workflow Ad Hoc Task', trim(L.LIFECYCLENAME) as UsedIn
from HSI.USERGXTASKLIST GXT
left outer join HSI.USERGROUP G ON GXT.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.TASKLIST T ON GXT.TASKLISTNUM = T.TASKLISTNUM
left outer join HSI.LIFECYCLE L ON T.SCOPE = L.LCNUM
left outer join HSI.LCSTATE S ON S.SCOPE = L.LCNUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where rtrim(U.USERNUM) = @{pUserNum}
and bitand(T.FLAGS,8192) >0
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(T.TASKLISTNAME) as TaskListName, 'System Tasks', '' as UsedIn
from HSI.USERGXTASKLIST GXT
left outer join HSI.USERGROUP G ON GXT.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.TASKLIST T ON GXT.TASKLISTNUM = T.TASKLISTNUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where rtrim(U.USERNUM) = @{pUserNum}
and bitand(T.FLAGS,64) >0
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(F.FOLDERTYPENAME) as FolderTypeName, 'Folder Types', '' as UsedIn
from HSI.USERGFOLDERTYPE GXF
left outer join HSI.USERGROUP G ON GXF.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.FOLDERTYPE F ON F.FOLDERTYPENUM = GXF.FOLDERTYPENUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where
rtrim(U.USERNUM) = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(T.DOCPACKETTMPLNAME) as DocPacketTemplateName, 'Document Packet Template', '' as UsedIn
from HSI.DOCPACKETTMPLXUSERGROUP GXT
left outer join HSI.USERGROUP G ON GXT.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.DOCPACKETTEMPLATE T ON T.DOCPACKETTMPLNUM = GXT.DOCPACKETTMPLNUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where
rtrim(U.USERNUM) = @{pUserNum}
UNION
select rtrim(U.USERNAME) as UserName, R.ROLENAME as RoleName, 'Role', '' as UsedIn
from HSI.USERXROLE X
left outer join HSI.USERACCOUNT U ON X.USERNUM = U.USERNUM
left outer join HSI.ROLESTABLE R ON X.ROLENUM = R.ROLENUM
where rtrim(U.USERNUM) = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(T.DCTEMPLATENAME) as DocCompTemplate, 'Document Composition Template', (select trim(ITEMTYPENAME) from HSI.DOCTYPE where ITEMTYPENUM = T.DESTDOCTYPENUM) as UsedIn
from HSI.USERGDCTEMPLATE X
left outer join HSI.USERGROUP G ON X.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.DCTEMPLATE T ON T.DCTEMPLATENUM = X.DCTEMPLATENUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where rtrim(U.USERNUM) = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(C.DCCATEGORYNAME) as DocCompCategory, 'Document Composition Category', '' as UsedIn
from HSI.USERGDCCATEGORY X
left outer join HSI.USERGROUP G ON X.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.DCCATEGORY C ON C.DCCATEGORYNUM = X.DCCATEGORYNUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where rtrim(U.USERNUM) = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(K.KEYSETNAME) as AutoFillKeywordSet, 'AutoFill Keyword Set' as ConfigItem, '' as UsedIn
from HSI.USERGROUPKEYSET X
left outer join HSI.USERGROUP G ON X.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.KEYWORDSET K ON K.KEYSETTABLENUM = X.KEYSETTABLENUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where rtrim(U.USERNUM) = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(S.STATENAME) as QueueName, 'Workflow Queue Override - Queue Administration', (select trim(LIFECYCLENAME) from HSI.LIFECYCLE where LCNUM = S.SCOPE) as UsedIn
from HSI.LCSTATE S, HSI.LCSTATEUSERGPRIVS X, HSI.USERGROUP G, HSI.USERXUSERGROUP UXG, HSI.USERACCOUNT U
where S.STATENUM = X.STATENUM and X.USERGROUPNUM = G.USERGROUPNUM and G.USERGROUPNUM = UXG.USERGROUPNUM and UXG.USERNUM = U.USERNUM
and bitand(X.USERPRIVILEGE0,1) >0
and U.USERNUM = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(S.STATENAME) as QueueName, 'Workflow Queue Override - See Other Users Documents', (select trim(LIFECYCLENAME) from HSI.LIFECYCLE where LCNUM = S.SCOPE) as UsedIn
from HSI.LCSTATE S, HSI.LCSTATEUSERGPRIVS X, HSI.USERGROUP G, HSI.USERXUSERGROUP UXG, HSI.USERACCOUNT U
where S.STATENUM = X.STATENUM and X.USERGROUPNUM = G.USERGROUPNUM and G.USERGROUPNUM = UXG.USERGROUPNUM and UXG.USERNUM = U.USERNUM
and bitand(X.USERPRIVILEGE0,2) >0
and U.USERNUM = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(S.STATENAME) as QueueName, 'Workflow Queue Override - Execute System Work', (select trim(LIFECYCLENAME) from HSI.LIFECYCLE where LCNUM = S.SCOPE) as UsedIn
from HSI.LCSTATE S, HSI.LCSTATEUSERGPRIVS X, HSI.USERGROUP G, HSI.USERXUSERGROUP UXG, HSI.USERACCOUNT U
where S.STATENUM = X.STATENUM and X.USERGROUPNUM = G.USERGROUPNUM and G.USERGROUPNUM = UXG.USERGROUPNUM and UXG.USERNUM = U.USERNUM
and bitand(X.USERPRIVILEGE0,4) >0
and U.USERNUM = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(S.STATENAME) as QueueName, 'Workflow Queue Override - Execute Timer', (select trim(LIFECYCLENAME) from HSI.LIFECYCLE where LCNUM = S.SCOPE) as UsedIn
from HSI.LCSTATE S, HSI.LCSTATEUSERGPRIVS X, HSI.USERGROUP G, HSI.USERXUSERGROUP UXG, HSI.USERACCOUNT U
where S.STATENUM = X.STATENUM and X.USERGROUPNUM = G.USERGROUPNUM and G.USERGROUPNUM = UXG.USERGROUPNUM and UXG.USERNUM = U.USERNUM
and bitand(X.USERPRIVILEGE0,8) >0
and U.USERNUM = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(S.STATENAME) as QueueName, 'Workflow Queue Override - Ad Hoc Routing', (select trim(LIFECYCLENAME) from HSI.LIFECYCLE where LCNUM = S.SCOPE) as UsedIn
from HSI.LCSTATE S, HSI.LCSTATEUSERGPRIVS X, HSI.USERGROUP G, HSI.USERXUSERGROUP UXG, HSI.USERACCOUNT U
where S.STATENUM = X.STATENUM and X.USERGROUPNUM = G.USERGROUPNUM and G.USERGROUPNUM = UXG.USERGROUPNUM and UXG.USERNUM = U.USERNUM
and bitand(X.USERPRIVILEGE0,16) >0
and U.USERNUM = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(S.STATENAME) as QueueName, 'Workflow Queue Override - Override Auto Feed', (select trim(LIFECYCLENAME) from HSI.LIFECYCLE where LCNUM = S.SCOPE) as UsedIn
from HSI.LCSTATE S, HSI.LCSTATEUSERGPRIVS X, HSI.USERGROUP G, HSI.USERXUSERGROUP UXG, HSI.USERACCOUNT U
where S.STATENUM = X.STATENUM and X.USERGROUPNUM = G.USERGROUPNUM and G.USERGROUPNUM = UXG.USERGROUPNUM and UXG.USERNUM = U.USERNUM
and bitand(X.USERPRIVILEGE0,32) >0
and U.USERNUM = @{pUserNum}
UNION
select rtrim(G.USERGROUPNAME) as GroupName, rtrim(S.STATENAME) as QueueName, 'Workflow Queue Override - Ownership Administration', (select trim(LIFECYCLENAME) from HSI.LIFECYCLE where LCNUM = S.SCOPE) as UsedIn
from HSI.LCSTATE S, HSI.LCSTATEUSERGPRIVS X, HSI.USERGROUP G, HSI.USERXUSERGROUP UXG, HSI.USERACCOUNT U
where S.STATENUM = X.STATENUM and X.USERGROUPNUM = G.USERGROUPNUM and G.USERGROUPNUM = UXG.USERGROUPNUM and UXG.USERNUM = U.USERNUM
and bitand(X.USERPRIVILEGE0,64) >0
and U.USERNUM = @{pUserNum}
order by 3, 2
;

[kliebeng]

Thank you for the help!  I'll take a look at this and circle back if I have any questions.