How to obtain a list of QM Query user profiles and allow SQL statements each can run

Resolving The Problem

IBM i has a view that can be used to gather this information.

This SQL View includes internal logic to retrieve the various authorizations that are kept within an internal structure that is not accessible to any IBM i *USRPRF.

A simple query of QSYS2/SQLQMPROF will provide the following information:

DEFAULT_LIBRARY,
DEFAULT_CRTAUT,
NAMING,
QUERY_MODE,
RUN_QUERY_MODE,
USER_LEVEL,
CONFIRMATION_MESSAGE,
RDB_CONNECTION,
SAMPLE_SIZE,
MAXIMUM_ROWS,
DEFAULT_COLLECTION,
QUERY_OUTPUT_TYPE,
DISPLAY_OPTION,
OUTPUT_TABLE,
OUTPUT_TABLE_SCHEMA,
JOB_DESCRIPTION,
JOB_DESCRIPTION_SCHEMA,
ISOLATION_LEVEL,
DEFAULT_PRINTER,
PROFILE_IN_USE,
ALLOW_ACCESS,
DEFAULT_CREATE_MODE,
ALLOW_CREATE_MODE_CHANGE,
ALLOW_SAMPLE_REPORT,
ALLOW_SORT_SEQUENCE,
DEFAULT_DISPLAY_COLUMN,
CONNECTION_METHOD,
ALTER_SEQUENCE,
ALTER_TABLE,
CALL,
COMMENT,
COMMIT,
CREATE_ALIAS,
CREATE_COLLECTION,
CREATE_DISTINCT_TYPE,
CREATE_FUNCTION,
CREATE_INDEX,
CREATE_PROCEDURE,
CREATE_SEQUENCE,
CREATE_TABLE,
CREATE_TRIGGER,
CREATE_VIEW,
DECLARE_GLOBAL_TEMPORARY,
DELETE,
DROP_ALIAS,
DROP_COLLECTION,
DROP_DISTINCT_TYPE,
DROP_FUNCTION,
DROP_INDEX,
DROP_PACKAGE,
DROP_PROCEDURE,
DROP_ROUTINE,
DROP_TABLE,
DROP_TRIGGER,
DROP_VIEW,
GRANT,
INSERT,
LABEL,
LOCK_TABLE,
REFRESH_TABLE,
RELEASE_SAVEPOINT,
RENAME,
REVOKE,  
ROLLBACK,
SAVEPOINT,
SELECT,
SET_CURRENT_DEGREE,
SET_ENCRYPTION_PASSWORD,
SET_PATH,
SET_SCHEMA,
SET_TRANSACTION,
UPDATE,
ALTER_FUNCTION,
ALTER_PROCEDURE,
CREATE_VARIABLE,
DROP_VARIABLE,
MERGE 


For example, you could use this query to see which users can use SELECT, UPDATE, or DELETE

select substr(PROFILE,1,10) as USRPRF,
       SELECT, UPDATE, DELETE  
  from QSYS2/SQLQMPROF                                        
 where SELECT = 'Y'                                            
    or UPDATE = 'Y'                                            
    or DELETE = 'Y'                                        


Your next question may be how to turn on 'logging' to keep track of when changes were made.

As the information is stored within an internal structure, a user cannot 'query' it.
Or add a 'SQL TRIGGER' to it, etc.

But IBM i Development did add audit support as well - audit entry T X2 in QAUDJRN which tells you if someone has changed this information.
- and we built a view over the top of that you could use.

select *                          
  from SQLQMProfilesAudit