249 lines
6.8 KiB
SQL
249 lines
6.8 KiB
SQL
SET PATH *LIBL ;
|
|
|
|
CREATE OR REPLACE FUNCTION SAILPOINT.ES_GET_ALL_USERS (
|
|
INENVLIST VARCHAR(1000) DEFAULT NULL )
|
|
RETURNS CLOB(2147483647)
|
|
LANGUAGE SQL
|
|
SPECIFIC SAILPOINT.ESALLUSERS
|
|
NOT DETERMINISTIC
|
|
MODIFIES SQL DATA
|
|
CALLED ON NULL INPUT
|
|
SET OPTION ALWBLK = *ALLREAD ,
|
|
ALWCPYDTA = *OPTIMIZE ,
|
|
COMMIT = *NONE ,
|
|
DBGVIEW = *SOURCE ,
|
|
DECRESULT = (31, 31, 00) ,
|
|
DYNDFTCOL = *NO ,
|
|
DYNUSRPRF = *OWNER ,
|
|
SRTSEQ = *HEX
|
|
BEGIN
|
|
DECLARE ENVLIST VARCHAR ( 500 ) DEFAULT NULL ;
|
|
DECLARE LIBL VARCHAR ( 1000 ) DEFAULT NULL ;
|
|
DECLARE MAPICS_USERS CLOB ( 1 G ) DEFAULT NULL ;
|
|
DECLARE PAD VARCHAR ( 3 ) DEFAULT ' ' ;
|
|
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
|
|
BEGIN
|
|
DECLARE V_SQLSTATE CHAR ( 5 ) ;
|
|
DECLARE V_DB2_RETURN_CODE INT ;
|
|
DECLARE V_DB2_MESSAGE_ID CHAR ( 7 ) ;
|
|
DECLARE V_ERROR_MESSAGE CLOB ( 2048 ) ;
|
|
-- Capture diagnostic info
|
|
GET DIAGNOSTICS CONDITION 1
|
|
V_DB2_RETURN_CODE = DB2_RETURNED_SQLCODE ,
|
|
V_SQLSTATE = RETURNED_SQLSTATE ,
|
|
V_ERROR_MESSAGE = MESSAGE_TEXT ,
|
|
V_DB2_MESSAGE_ID = DB2_MESSAGE_ID ;
|
|
RETURN
|
|
(
|
|
VALUES JSON_OBJECT (
|
|
'success' VALUE 'false' , 'SQLInfo' VALUE JSON_OBJECT (
|
|
'SQLState' VALUE V_SQLSTATE ,
|
|
'SQLCode' VALUE V_DB2_RETURN_CODE ,
|
|
'messageID' VALUE V_DB2_MESSAGE_ID ,
|
|
'message' VALUE V_ERROR_MESSAGE ,
|
|
'currentTimestamp' VALUE CURRENT_TIMESTAMP
|
|
)
|
|
) ) ;
|
|
END ;
|
|
DECLARE GLOBAL TEMPORARY TABLE SESSION . MAPICS_USERS FOR SYSTEM NAME
|
|
MAPICUSERS (
|
|
ENVID VARCHAR ( 10 ) CCSID 37 DEFAULT NULL ,
|
|
F0BUCD VARCHAR ( 10 ) CCSID 37 DEFAULT NULL ,
|
|
F0H1TX VARCHAR ( 60 ) CCSID 37 DEFAULT NULL ,
|
|
GUAAHY VARCHAR ( 5 ) CCSID 37 DEFAULT NULL ,
|
|
EXTYPE VARCHAR ( 50 ) CCSID 37 DEFAULT NULL ,
|
|
EXDEPT VARCHAR ( 10 ) CCSID 37 DEFAULT NULL ,
|
|
EDDESC VARCHAR ( 60 ) CCSID 37 DEFAULT NULL ,
|
|
EUROLE VARCHAR ( 10 ) CCSID 37 DEFAULT NULL ,
|
|
ERDESC VARCHAR ( 60 ) CCSID 37 DEFAULT NULL ,
|
|
EXU10A VARCHAR ( 10 ) CCSID 37 DEFAULT NULL ,
|
|
EXSTAT VARCHAR ( 15 ) CCSID 37 DEFAULT NULL ,
|
|
GUADMW VARCHAR ( 15 ) CCSID 37 DEFAULT NULL ,
|
|
F0UGRP VARCHAR ( 5 ) CCSID 37 DEFAULT NULL )
|
|
RCDFMT MAPICUSERS
|
|
WITH REPLACE ;
|
|
FOR SELECT DISTINCT A . ENVID
|
|
FROM TABLE (
|
|
SELECT ROW_NUMBER ( ) OVER (
|
|
) AS RN , ENVID
|
|
FROM JSON_TABLE (
|
|
SAILPOINT . ES_GET_ALL_MAPICS_ENVIRONMENTS ( ) ,
|
|
'$.data[*]'
|
|
COLUMNS (
|
|
ENVID VARCHAR ( 2 ) CCSID 37 PATH '$.envId'
|
|
)
|
|
)
|
|
) A
|
|
JOIN TABLE (
|
|
SELECT COUNT ( NULLIF ( FRAGMENT , '' ) ) OVER (
|
|
) AS FRAG_COUNT , RN ,
|
|
NULLIF ( FRAGMENT , '' ) AS FRAGMENT
|
|
FROM TABLE (
|
|
CISTOOLS . ROWS_COUNTER ( ( SELECT COUNT ( * )
|
|
FROM MMLIST ) )
|
|
)
|
|
LEFT JOIN TABLE (
|
|
CISTOOLS . SPLIT_STRING (
|
|
REGEXP_REPLACE (
|
|
CAST (
|
|
COALESCE ( INENVLIST , '' ) AS
|
|
VARCHAR ( 1000 ) CCSID 37 ) ,
|
|
'[\\[\\]\\{\\}\\"\\,\\s]+' , ' ' ) ,
|
|
' ' )
|
|
)
|
|
ON RN = ROW_NUM
|
|
) S
|
|
ON ENVID =
|
|
CASE
|
|
WHEN FRAG_COUNT > 0 THEN FRAGMENT
|
|
ELSE ENVID
|
|
END
|
|
DO
|
|
SET CISTOOLS . LIBL = CISTOOLS . SET_LIBRARY_LIST ( ENVID ) ;
|
|
INSERT INTO SESSION . MAPICS_USERS
|
|
SELECT
|
|
CAST ( CISTOOLS . MAPICS_ENV AS VARCHAR ( 10 ) CCSID 37 )
|
|
AS ENVID ,
|
|
CAST ( TRIM ( F0BUCD ) AS VARCHAR ( 10 ) CCSID 37 ) AS F0BUCD ,
|
|
CAST ( TRIM ( F0H1TX ) AS VARCHAR ( 60 ) CCSID 37 ) AS F0H1TX ,
|
|
CAST ( TRIM ( GUAAHY ) AS VARCHAR ( 5 ) CCSID 37 ) AS GUAAHY ,
|
|
CAST (
|
|
CASE EXTYPE
|
|
WHEN ' ' THEN 'General User'
|
|
WHEN 'A' THEN 'Administrator'
|
|
WHEN 'S' THEN 'System/Application User'
|
|
WHEN 'G' THEN 'Generic User'
|
|
ELSE '(Unknown)'
|
|
END AS VARCHAR ( 50 ) CCSID 37 ) AS EXTYPE ,
|
|
CAST ( TRIM ( EXDEPT ) AS VARCHAR ( 10 ) CCSID 37 ) AS EXDEPT ,
|
|
CAST (
|
|
IFNULL ( TRIM ( D . EUDESC ) , '' ) AS VARCHAR ( 60 ) CCSID 37 )
|
|
AS EDDESC ,
|
|
CAST ( TRIM ( EUROLE ) AS VARCHAR ( 10 ) CCSID 37 ) AS EUROLE ,
|
|
CAST (
|
|
IFNULL ( TRIM ( R . EUDESC ) , '' ) AS VARCHAR ( 60 ) CCSID 37 )
|
|
AS ERDESC ,
|
|
CAST ( TRIM ( EXU10A ) AS VARCHAR ( 10 ) CCSID 37 ) AS EXU10A ,
|
|
CAST (
|
|
CASE EXSTAT
|
|
WHEN 'A' THEN 'Active'
|
|
WHEN ' ' THEN 'Unknown'
|
|
WHEN 'D' THEN 'Disabled'
|
|
END AS VARCHAR ( 15 ) CCSID 37 ) AS EXSTAT ,
|
|
CAST ( TRIM ( GUADMW ) AS VARCHAR ( 15 ) CCSID 37 ) AS GUADMW ,
|
|
CAST (
|
|
CASE F0UGRP
|
|
WHEN 1 THEN 'true'
|
|
WHEN 0 THEN 'false'
|
|
END AS VARCHAR ( 5 ) CCSID 37 ) AS F0UGRP
|
|
FROM MZF0REP MZ
|
|
LEFT JOIN ESUSRP
|
|
JOIN ESROLP R
|
|
USING ( EUROLE )
|
|
ON EUUSER = MZ . F0BUCD
|
|
LEFT JOIN ESUEXP
|
|
ON MZ . F0BUCD = EXUSER
|
|
LEFT JOIN ESDPTP D
|
|
ON EXDEPT = EUDEPT
|
|
LEFT JOIN MZGUCPS0
|
|
ON GUBUCD = MZ . F0BUCD
|
|
WHERE EXTYPE <> 'R' ;
|
|
END FOR ;
|
|
RETURN
|
|
COALESCE (
|
|
JSON_OBJECT (
|
|
'status' : 'success' , 'data' VALUE (
|
|
SELECT JSON_ARRAYAGG (
|
|
JSON_OBJECT (
|
|
'userId' VALUE A . AUTHORIZATION_NAME ,
|
|
'status' VALUE A . STATUS ,
|
|
'previousSignOn' VALUE A . PREVIOUS_SIGNON ,
|
|
'signonInvalidAttempts' VALUE
|
|
A . SIGN_ON_ATTEMPTS_NOT_VALID ,
|
|
'userClass' VALUE A . USER_CLASS_NAME ,
|
|
'specialAuths' VALUE JSON_ARRAY ( SELECT NULLIF (
|
|
TRIM ( FRAGMENT ) , '' )
|
|
FROM TABLE (
|
|
CISTOOLS . SPLIT_STRING (
|
|
A . SPECIAL_AUTHORITIES , ' ' )
|
|
)
|
|
WHERE TRIM ( FRAGMENT ) <> '' ) ,
|
|
'groupProfile' VALUE A . GROUP_PROFILE_NAME ,
|
|
'daysUntilPwExpires' VALUE
|
|
A . DAYS_UNTIL_PASSWORD_EXPIRES ,
|
|
'text' VALUE A . TEXT_DESCRIPTION ,
|
|
'homeDir' VALUE A . HOME_DIRECTORY ,
|
|
'locale' VALUE A . LOCALE_PATH_NAME ,
|
|
'localPwMgmt' VALUE A . LOCAL_PASSWORD_MANAGEMENT ,
|
|
'mapicsAttributes' VALUE (
|
|
SELECT JSON_ARRAYAGG (
|
|
JSON_OBJECT (
|
|
MU . ENVID VALUE JSON_ARRAY (
|
|
JSON_OBJECT (
|
|
'userId' VALUE MU . F0BUCD ,
|
|
'userName' VALUE MU . F0H1TX ,
|
|
'operatorId' VALUE MU . GUAAHY ,
|
|
'userType' VALUE MU . EXTYPE ,
|
|
'userDept' VALUE JSON_OBJECT (
|
|
'deptCode' VALUE NULLIF (
|
|
MU . EXDEPT , '' ) ,
|
|
'deptDescription' VALUE NULLIF (
|
|
MU . EDDESC , '' )
|
|
ABSENT ON NULL
|
|
) , 'userEmpNo' VALUE MU . EXU10A ,
|
|
'status' VALUE MU . EXSTAT ,
|
|
'startMenu' VALUE NULLIF (
|
|
MU . GUADMW , '' ) , 'roles' VALUE (
|
|
SELECT JSON_ARRAYAGG (
|
|
JSON_OBJECT (
|
|
'roleCode' VALUE
|
|
NULLIF ( R . EUROLE , '' ) ,
|
|
'roleDescription'
|
|
VALUE NULLIF (
|
|
R . ERDESC , '' )
|
|
ABSENT ON NULL
|
|
) FORMAT JSON
|
|
)
|
|
FROM TABLE (
|
|
SELECT DISTINCT EUROLE , ERDESC
|
|
FROM SESSION . MAPICS_USERS C
|
|
WHERE C . F0BUCD = MU . F0BUCD
|
|
AND C . ENVID = MU . ENVID
|
|
) R ) FORMAT JSON ,
|
|
'isGroupId' VALUE MU . F0UGRP FORMAT
|
|
JSON ABSENT ON NULL
|
|
) )
|
|
) FORMAT JSON
|
|
)
|
|
FROM TABLE (
|
|
SELECT DISTINCT F0BUCD , ENVID ,
|
|
F0H1TX , GUAAHY ,
|
|
EXTYPE , EXDEPT ,
|
|
EDDESC , EXU10A ,
|
|
EXSTAT , GUADMW ,
|
|
F0UGRP
|
|
FROM SESSION . MAPICS_USERS
|
|
WHERE F0BUCD =
|
|
A . AUTHORIZATION_NAME
|
|
) MU ) FORMAT JSON
|
|
)
|
|
)
|
|
FROM QSYS2 . USER_INFO_BASIC A ) FORMAT JSON
|
|
) , JSON_OBJECT (
|
|
'success' VALUE 'false' , 'SQLInfo' VALUE JSON_OBJECT (
|
|
'messageID' VALUE 'SQL02000' ,
|
|
'message' VALUE 'Requested user not found ' ,
|
|
'currentTimestamp' : CURRENT_TIMESTAMP
|
|
)
|
|
) ) ;
|
|
END ;
|
|
|
|
GRANT ALTER , EXECUTE
|
|
ON SPECIFIC FUNCTION SAILPOINT.ESALLUSERS
|
|
TO AMAPICS WITH GRANT OPTION ;
|
|
|
|
GRANT EXECUTE
|
|
ON SPECIFIC FUNCTION SAILPOINT.ESALLUSERS
|
|
TO PUBLIC ;
|
|
|