Adobe enterprise product blog | David McMahon

Requirement

 You may have a requirement to provide a full breakdown of all Users/Groups/Roles for your installation of Adobe LiveCycle ES. It is possible to find out this information using the AdminUI, however this would take a lot of effort and time to perform all the necessary searches and manually compile the information returned.  The other option would be to run an SQL query on the database to read the information directly from the DB tables.

Solution

 The following are 2 SQL queries to:

==========================================
Query 1: Identify all Users in each Group
==========================================

select epea.commonname as GROUP_NAME,
epeb.commonname as USER_NAME
from lcesprd.edcprincipalentity epea,
lcesprd.edcprincipalentity epeb,
lcesprd.edcprincipalgroupentity epg,
lcesprd.edcprincipalgrpctmntenti epgc
where epea.id = epg.refprincipalid
and epea.id = epgc.refparentprincipalid
and epeb.id = epgc.refchildprincipalid
and epea.status = 'CURRENT'
and epeb.status = 'CURRENT'
and epea.principaltype ='GROUP'
and epeb.principaltype ='USER'
order by GROUP_NAME

==================================================
Query 2: Identify all Roles assigned to each Group
==================================================

select epea.commonname as ROLE_NAME,
epeb.commonname as GROUP_NAME
from lcesprd.edcprincipalentity epea,
lcesprd.edcprincipalentity epeb,
lcesprd.edcprincipalroleentity epre
where epea.id = epre.refroleid
and epeb.id = epre.refprincipalid
and epeb.principaltype ='GROUP' -- (Note: can change this value to 'USER')
order by ROLE_NAME