Examples of Ad-Hoc SQL Queries › List Users Connected to a Role

List Users Connected to a Role

This SQL query is the foundation of the CAS4CR02 report. This query returns the userids and user names for all users who are connected to a specific role on a specific image.

The ROLEXREF table contains the cross-reference information matching a role with each userid connected to the role. The USERINFO table is used to return the user name for the userid.

To list users connected to a role, run the following SQL commands:

SELECT CIADB01.ROLEXREF.SYSID, CIADB01.ROLEXREF.USERID, NAME
FROM   CIADB01.ROLEXREF, CIADB01.USERINFO                   
WHERE ROLEID  = 'PROFILE1' AND              
      CIADB01.ROLEXREF.SYSID = 'SY59' AND                   
      CIADB01.ROLEXREF.SYSID = CIADB01.USERINFO.SYSID AND   
      CIADB01.ROLEXREF.USERID = CIADB01.USERINFO.USERID     
      ORDER BY SYSID, USERID; 

A report with users connected to the specified role is created.