The haccess command line utility provides a form of this, but not for all 3 categories in one report.  Also, in the Administrator Tool, there is a "User Groups" report that will provide a list of all users for each user group.

Is there any kind of a SQL query that will give us a list of users, what user groups they are in and the Harvest projects that those user groups have access to?

CA Harvest SCM - all versions.  These queries should work for both Oracle and SQL Server

For a listing of all access permissions by user group across all projects, states and processes, this SQL should provide the answer:

SELECT * FROM (
SELECT 'Project' AS "LEVEL",
  HARENVIRONMENT.ENVIRONMENTNAME,
  ' ' AS STATENAME,
  ' ' AS PROCESSNAME,
  ' ' AS PROCESSTYPE,
  HARUSERGROUP.USERGROUPNAME,
  HARENVIRONMENTACCESS.SECUREACCESS AS "PROJECT SECURE",
  HARENVIRONMENTACCESS.UPDATEACCESS AS "PROJECT UPDATE",
  HARENVIRONMENTACCESS.EXECUTEACCESS AS "PROJECT USE",
  HARENVIRONMENTACCESS.VIEWACCESS AS "PROJECT VIEW",
  ' ' AS "STATE UPDATE",
  ' ' AS "STATE UPDATEPACKAGE",
  ' ' AS "PROCESS EXECUTE"
FROM HARENVIRONMENT
INNER JOIN HARENVIRONMENTACCESS
ON HARENVIRONMENT.ENVOBJID = HARENVIRONMENTACCESS.ENVOBJID
INNER JOIN HARUSERGROUP
ON HARENVIRONMENTACCESS.USRGRPOBJID = HARUSERGROUP.USRGRPOBJID
INNER JOIN HARUSERSINGROUP
ON HARUSERGROUP.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID
UNION
SELECT 'State' AS "LEVEL",
  HARENVIRONMENT.ENVIRONMENTNAME,
  HARSTATE.STATENAME,
  ' ' AS PROCESSNAME,
  ' ' AS PROCESSTYPE,
  HARUSERGROUP.USERGROUPNAME,
  ' ' AS "PROJECT SECURE",
  ' ' AS "PROJECT UPDATE",
  ' ' AS "PROJECT USE",
  ' ' AS "PROJECT VIEW",
  HARSTATEACCESS.UPDATEACCESS AS "STATE UPDATE",
  HARSTATEACCESS.UPDATEPKGACCESS AS "STATE UPDATEPACKAGE", 
  ' ' AS "PROCESS EXECUTE"
FROM HARENVIRONMENT
INNER JOIN HARSTATE
ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID
INNER JOIN HARSTATEACCESS
ON HARSTATE.STATEOBJID = HARSTATEACCESS.STATEOBJID
INNER JOIN HARUSERGROUP
ON HARSTATEACCESS.USRGRPOBJID = HARUSERGROUP.USRGRPOBJID
INNER JOIN HARUSERSINGROUP
ON HARUSERGROUP.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID
WHERE ((UPDATEACCESS = 'Y') OR (UPDATEPKGACCESS = 'Y'))
UNION
SELECT 'Process' AS "LEVEL",
  HARENVIRONMENT.ENVIRONMENTNAME,
  HARSTATE.STATENAME,
  HARSTATEPROCESS.PROCESSNAME,
  HARSTATEPROCESS.PROCESSTYPE,
  HARUSERGROUP.USERGROUPNAME,
  ' ' AS "PROJECT SECURE",
  ' ' AS "PROJECT UPDATE",
  ' ' AS "PROJECT USE",
  ' ' AS "PROJECT VIEW",
  ' ' AS "STATE UPDATE",
  ' ' AS "STATE UPDATEPACKAGE",
  HARSTATEPROCESSACCESS.EXECUTEACCESS AS "PROCESS EXECUTE"
FROM HARENVIRONMENT
INNER JOIN HARSTATE
ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID
INNER JOIN HARSTATEPROCESS
ON HARSTATE.STATEOBJID = HARSTATEPROCESS.STATEOBJID
INNER JOIN HARSTATEPROCESSACCESS
ON HARSTATEPROCESS.STATEOBJID    = HARSTATEPROCESSACCESS.STATEOBJID
AND HARSTATEPROCESS.PROCESSOBJID = HARSTATEPROCESSACCESS.PROCESSOBJID
INNER JOIN HARUSERGROUP
ON HARUSERGROUP.USRGRPOBJID = HARSTATEPROCESSACCESS.USRGRPOBJID
INNER JOIN HARUSERSINGROUP
ON HARUSERGROUP.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID
WHERE HARSTATEPROCESSACCESS.EXECUTEACCESS = 'Y'
) ALLRESULTS
ORDER BY USERGROUPNAME,
ENVIRONMENTNAME,
STATENAME,
PROCESSNAME

For the same by individual user and group, this SQL should provide the answer:

SELECT * FROM (
SELECT HARENVIRONMENT.ENVIRONMENTNAME,
  ' ' AS STATENAME,
  ' ' AS PROCESSNAME,
  HARUSERGROUP.USERGROUPNAME,
  HARUSER.USERNAME,
  HARUSER.REALNAME,
  HARUSER.LASTLOGIN,
  HARUSERDATA.ACCOUNTDISABLED,
  HARUSERDATA.ACCOUNTLOCKED,
  CASE
    WHEN HARUSERDATA.ACCOUNTDISABLED = 'N'
    THEN 'A'
    WHEN HARUSERDATA.ACCOUNTDISABLED = 'A'
    THEN 'I'
  END AS STATUS_IND,
  'PROJECT' AS "TYPE",
  HARENVIRONMENTACCESS.SECUREACCESS AS "PROJECT SECURE",
  HARENVIRONMENTACCESS.UPDATEACCESS AS "PROJECT UPDATE",
  HARENVIRONMENTACCESS.EXECUTEACCESS AS "PROJECT USE",
  HARENVIRONMENTACCESS.VIEWACCESS AS "PROJECT VIEW",
  ' ' AS "STATE UPDATE",
  ' ' AS "STATE UPDATEPACKAGE",
  ' ' AS "PROCESS EXECUTE"
FROM HARENVIRONMENT
INNER JOIN HARENVIRONMENTACCESS
ON HARENVIRONMENT.ENVOBJID = HARENVIRONMENTACCESS.ENVOBJID
INNER JOIN HARUSERGROUP
ON HARENVIRONMENTACCESS.USRGRPOBJID = HARUSERGROUP.USRGRPOBJID
INNER JOIN HARUSERSINGROUP
ON HARUSERGROUP.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID
INNER JOIN HARUSER
ON HARUSER.USROBJID = HARUSERSINGROUP.USROBJID
INNER JOIN HARUSERDATA
ON HARUSER.USROBJID                      = HARUSERDATA.USROBJID
WHERE (HARENVIRONMENTACCESS.SECUREACCESS = 'Y')
OR (HARENVIRONMENTACCESS.UPDATEACCESS    = 'Y')
OR (HARENVIRONMENTACCESS.VIEWACCESS      = 'Y')
OR (HARENVIRONMENTACCESS.EXECUTEACCESS   = 'Y')
UNION
SELECT HARENVIRONMENT.ENVIRONMENTNAME,
  HARSTATE.STATENAME,
  ' ' AS PROCESSNAME,
  HARUSERGROUP.USERGROUPNAME,
  HARUSER.USERNAME,
  HARUSER.REALNAME,
  HARUSER.LASTLOGIN,
  HARUSERDATA.ACCOUNTDISABLED,
  HARUSERDATA.ACCOUNTLOCKED,
  CASE
    WHEN HARUSERDATA.ACCOUNTDISABLED = 'N'
    THEN 'A'
    WHEN HARUSERDATA.ACCOUNTDISABLED = 'A'
    THEN 'I'
  END AS STATUS_IND,
  'STATE' AS "TYPE",
  ' ' AS "PROJECT SECURE",
  ' ' AS "PROJECT UPDATE", 
  ' ' AS "PROJECT USE", 
  ' ' AS "PROJECT VIEW",
  HARSTATEACCESS.UPDATEACCESS AS "STATE UPDATE",
  HARSTATEACCESS.UPDATEPKGACCESS AS "STATE UPDATEPACKAGE", 
  ' ' AS "PROCESS EXECUTE"
FROM HARENVIRONMENT
INNER JOIN HARSTATE
ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID
INNER JOIN HARSTATEACCESS
ON HARSTATE.STATEOBJID = HARSTATEACCESS.STATEOBJID
INNER JOIN HARUSERGROUP
ON HARSTATEACCESS.USRGRPOBJID = HARUSERGROUP.USRGRPOBJID
INNER JOIN HARUSERSINGROUP
ON HARUSERGROUP.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID
INNER JOIN HARUSER
ON HARUSER.USROBJID = HARUSERSINGROUP.USROBJID
INNER JOIN HARUSERDATA
ON HARUSER.USROBJID = HARUSERDATA.USROBJID
WHERE ((UPDATEACCESS = 'Y') OR (UPDATEPKGACCESS = 'Y'))
UNION
SELECT HARENVIRONMENT.ENVIRONMENTNAME,
  HARSTATE.STATENAME,
  HARSTATEPROCESS.PROCESSNAME,
  HARUSERGROUP.USERGROUPNAME,
  HARUSER.USERNAME,
  HARUSER.REALNAME,
  HARUSER.LASTLOGIN,
  HARUSERDATA.ACCOUNTDISABLED,
  HARUSERDATA.ACCOUNTLOCKED,
  CASE
    WHEN HARUSERDATA.ACCOUNTDISABLED = 'N'
    THEN 'A'
    WHEN HARUSERDATA.ACCOUNTDISABLED = 'A'
    THEN 'I'
  END                                 AS STATUS_IND,
  'PROCESS' AS "TYPE",
  ' ' AS "PROJECT SECURE",
  ' ' AS "PROJECT UPDATE", 
  ' ' AS "PROJECT USE", 
  ' ' AS "PROJECT VIEW",
  ' ' AS "STATE UPDATE",
  ' ' AS "STATE UPDATEPACKAGE",
  HARSTATEPROCESSACCESS.EXECUTEACCESS AS "PROCESS EXECUTE"
FROM HARENVIRONMENT
INNER JOIN HARSTATE
ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID
INNER JOIN HARSTATEPROCESS
ON HARSTATE.STATEOBJID = HARSTATEPROCESS.STATEOBJID
INNER JOIN HARSTATEPROCESSACCESS
ON HARSTATEPROCESS.STATEOBJID    = HARSTATEPROCESSACCESS.STATEOBJID
AND HARSTATEPROCESS.PROCESSOBJID = HARSTATEPROCESSACCESS.PROCESSOBJID
INNER JOIN HARUSERGROUP
ON HARUSERGROUP.USRGRPOBJID = HARSTATEPROCESSACCESS.USRGRPOBJID
INNER JOIN HARUSERSINGROUP
ON HARUSERGROUP.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID
INNER JOIN HARUSER
ON HARUSER.USROBJID = HARUSERSINGROUP.USROBJID
INNER JOIN HARUSERDATA
ON HARUSER.USROBJID                       = HARUSERDATA.USROBJID
WHERE HARSTATEPROCESSACCESS.EXECUTEACCESS = 'Y'
) ALLRESULTS
ORDER BY USERNAME,
USERGROUPNAME,
ENVIRONMENTNAME,
STATENAME,
PROCESSNAME