Role Provisioning

 SELECT    ue.name,    ue.acctKey,    ue.userkey,    re.rolekey,    re.rolename,    'Provision Role' as 'Default_Action_For_Analytics'FROM    (        SELECT            U.USERNAME AS name,            A.ACCOUNTKEY AS acctKey,            U.USERKEY AS userkey,            GROUP_CONCAT(DISTINCT E.ENTITLEMENT_VALUEKEY) AS MATCHING_ENTITLEMENT_KEY        FROM USERS U                 JOIN USER_ACCOUNTS UA ON U.USERKEY = UA.USERKEY                 JOIN ACCOUNTS A ON UA.ACCOUNTKEY = A.ACCOUNTKEY                 JOIN ACCOUNT_ENTITLEMENTS1 AE ON A.ACCOUNTKEY = AE.ACCOUNTKEY                 JOIN ENTITLEMENT_VALUES E ON AE.ENTITLEMENT_VALUEKEY = E.ENTITLEMENT_VALUEKEY        WHERE A.ENDPOINTKEY = 178        GROUP BY U.USERKEY, A.ACCOUNTKEY, U.USERNAME    ) ue        JOIN    (        SELECT            R.ROLEKEY,            R.ROLE_NAME AS rolename,            GROUP_CONCAT(DISTINCT RE.ENTITLEMENT_VALUEKEY) AS MATCHING_ENTITLEMENT_KEY        FROM ROLES R                 JOIN ROLE_ENTITLEMENTS RE ON R.ROLEKEY = RE.ROLEKEY        WHERE R.ENDPOINTKEY = 178        GROUP BY R.ROLEKEY, R.ROLE_NAME    ) re    ON NOT EXISTS (        SELECT 1        FROM (                 SELECT                     TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(re.MATCHING_ENTITLEMENT_KEY, ',', n.n), ',', -1)) AS val                 FROM (                          SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5                          UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10                          UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15                          UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20                      ) n                 WHERE n.n <= LENGTH(re.MATCHING_ENTITLEMENT_KEY) - LENGTH(REPLACE(re.MATCHING_ENTITLEMENT_KEY, ',', '')) + 1             ) role_val        WHERE FIND_IN_SET(role_val.val, ue.MATCHING_ENTITLEMENT_KEY) = 0    )    LEFT JOIN ROLE_USER_ACCOUNT RUA ON RUA.USERKEY = ue.userkey AND RUA.ROLEKEY = re.rolekey WHERE RUA.ROLEKEY IS NULL;

Comments

Post a Comment

Popular posts from this blog

Enhanced Query - To Update Account Status and Name

select accountkey as accounts__primarykey, 'SUSPENDED FROM IMPORT SERVICE' as accounts__status, CONCAT(name,'-Deleted on-', DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'))as accounts__name from accounts where endpointkey=527 and name='CN=INRVCH,OU=User Accounts Vista,OU=User Accounts,OU=User Directory,DC=asia-pac,DC=xx,DC=com'  and status not in ('SUSPENDED FROM IMPORT SERVICE') limit 1;
Read more

Connection Config - STATUS_THRESHOLD_CONFIG

 {   "statusAndThresholdConfig": {    "inactivateAccountsNotInFile": true    "statusColumn":"userlock",    "activeStatus":["0","128","192"],     "accountThresholdValue": 500,     "deleteLinks": false,     "correlateInactiveAccounts": true,        } } ----------------------------------------------------------------------------- When account import run above STATUS_THRESHOLD_CONFIG is used to check. ----------------------------------- inactivateAccountsNotInFile  ==> True/false true ---> Account status 1/2 ( Active/Inactive) Day 1 --> 100 Accounts import Day 2 --> 90 Diff= 10  ( 90= Active , 10 Inactive)  ------------------------------------------------------------------------------ ----------------------------------- inactivateAccountsNotInFile  ==> True/false false ---> Account status 1/SFIS ( Active/SUSPENDED FROM IMPORT SERVICE.) Day 1 --> 100 Ac...
Read more

Email variables _ Register User

ServiceAccountOwnerMap ------- [:] entitlements ------- [] accessapprovers ------- com.saviynt.ecm.workflow.Access_Approvers : 812 jbpm_activity_name ------- Third Party Registration Approvers allApproversComments ------- null fullrowhtmltablerows ------- listofallrolesinrequest ------- [] approvedEntitlementsOwners ------- [] entitlementshtmltablerowsv2 ------- entitlementshtmltablerowsv3 ------- ServiceAccountType ------- requestlink ------- null/jbpmworkflowmanagement/showrequestdetails/TP Registration Approvals.553050?reqid=433 allRejectorsComments ------- null rolesApprovedOrRejected ------- [] rolename ------- request_access ------- com.saviynt.ecm.workflow.Request_Access : 456 users ------- XXXX allRejectors ------- [] requestid ------- 553050 rolemap ------- [:] baseUrlForEmail ------- https://dev.saviyntcloud.com/ECM assignee ------- XXXX activityname ------- Third Party Registration Approvers ServiceAccountFlag ------- false request ------- com.saviynt.ecm.workflow.ARS_Reques...
Read more