How to report users (using Analytics Control) whose Access is deleted directly in Target System

[nimitdave]

Use Case

This article describes configuration of the analytics that report the users whose access is deleted directly on the target application.

Pre-requisites

1. Full data reconciliation from target system to EIC.
2. Post full reconciliation , application data should be baselined using Baselining feature in endpoint details (refer Saviynt document on how to baseline application)

Applicable Version(s)

All

Solution

Once a baseline has been done, the below query can be used to identify all the data deleted directly in the target application. You can also provide actions:

• Provision access: This will create an add task that can be marked as completed in case access needs to be added back.
• De-provision Access: This will create a revoke task in case access needs to remain removed.

The control has below logic:

1. If records are either baselined (has an entry in account_entitlements1 table) or have a completed add access task then the record is not picked up in the control.
2. Baseline date (in case access is baselined) or task creation date (in case access is assigned via EIC) should be greater than any remove task created for the same access. This means if access is baselined, but a remove task was created later, but access is still assigned then access is rogue access. The same goes for if the remove access task was created after add the access task but access is still assigned to the user.

3. If access was baselined: There is no remove access task created for the user after baseline date but access is not present in the the accounts_entitlements1 table.

4. If access was provided by EIC  task: There is no remove access task created for the user which taskupdate date greater than add task update date but the access is not present in accounts_entitlements1 table.

   
   

   SELECT *

   FROM

   (SELECT

         t.TASKKEY AS tasks,

         e.DISPLAYNAME AS 'Application Name',

         u.username AS 'User Name',

         a.name AS 'Application User ID',

                   a.usergroup AS 'User Group',

                   max(t.UPDATEDATE) AS 'Provisioning Task Completion Date',

          v.entitlement_value 'Entitlement Name',

         IFNULL((CASE

                      WHEN v.risk = 0 THEN 'None'

                      WHEN v.risk = 1 THEN 'Very Low'

                      WHEN v.risk = 2 THEN 'Low'

                      WHEN v.risk = 3 THEN 'Medium'

                      WHEN v.risk = 4 THEN 'High'

                      WHEN v.risk = 5 THEN 'Very High'

                 END),

                  'None') AS 'Entitlement Risk',

         a.accountkey AS acctKey,

         v.ENTITLEMENT_VALUEKEY AS entvaluekey,

         'In SSM' AS 'Entitlement Status',

          'ProvisionAccess' AS 'Action to be Provisioned'

     FROM

         endpoints e,

         accounts a,

          entitlement_values v,

         arstasks t,

         users u,

         user_accounts ua

     WHERE

         t.accountkey = a.accountkey

             AND u.userkey = ua.userkey

             AND ua.accountkey = a.accountkey

             AND e.ENDPOINTKEY = a.ENDPOINTKEY

             AND a.STATUS IN ('1' , 'Active','Manually Provisioned')

             AND t.entitlement_valuekey = v.entitlement_valuekey

             AND t.status = 3

             AND t.tasktype = 1

                                     AND u.statuskey = 1

             AND NOT EXISTS( SELECT

                  accountkey, entitlement_valuekey

             FROM

                  arstasks

             WHERE

                  TASKTYPE = 2

                      AND accountkey = t.accountkey

                      AND entitlement_valuekey = t.entitlement_valuekey

                      AND status = 3)

             AND NOT EXISTS( SELECT

                  accountkey, entitlement_valuekey

             FROM

                  account_entitlements1 e

             WHERE

                  e.accountkey = t.accountkey

                      AND e.entitlement_valuekey = t.entitlement_valuekey)

             AND e.customproperty2 = 'control’

                                     and v.status = 1

             group by `Application Name`,`User Name`,`Application User ID`,`User Group`,

              `Entitlement Name`,`Entitlement Risk`,acctKey,entvaluekey,`Entitlement Status`,`Action to be Provisioned`

     UNION  SELECT

         NULL AS tasks,

         e.DISPLAYNAME AS 'Application Name',

         u.username AS 'User Name',

         a.name AS 'Application User ID',

                   a.usergroup AS 'User Group',

         '' AS 'Provisioning Task Completion Date',

          v.entitlement_value 'Entitlement Name',

         IFNULL((CASE

                      WHEN v.risk = 0 THEN 'None'

                      WHEN v.risk = 1 THEN 'Very Low'

                      WHEN v.risk = 2 THEN 'Low'

                      WHEN v.risk = 3 THEN 'Medium'

                      WHEN v.risk = 4 THEN 'High'

                     WHEN v.risk = 5 THEN 'Very High'

                 END),

                  'None') AS 'Entitlement Risk',

         a.accountkey AS acctKey,

          v.ENTITLEMENT_VALUEKEY AS entvaluekey,

         'Onboarded Account Entitlement' AS 'Entitlement Status',

          'ProvisionAccess' AS 'Action to be Provisioned'

     FROM

         endpoints e,

         accounts a,

          entitlement_values v,

         users u,

         user_accounts ua,

          account_entitlements1_onboarded aeo

     WHERE

         u.userkey = ua.userkey

             AND ua.accountkey = a.accountkey

             AND e.ENDPOINTKEY = a.ENDPOINTKEY

             AND a.STATUS IN ('1' , 'Active','Manually Provisioned')

             AND e.customproperty2 = 'control'

             AND a.accountkey = aeo.accountkey

             AND aeo.entitlement_valuekey = v.ENTITLEMENT_VALUEKEY

                                   AND u.statuskey = 1

             AND (aeo.ACCOUNTKEY , aeo.ENTITLEMENT_VALUEKEY) NOT IN (SELECT

                  k.accountkey, k.entitlement_valuekey

             FROM

                  account_entitlements1 k

             WHERE

                  k.accountkey = a.accountkey)

             AND NOT EXISTS( SELECT

                  taskkey

             FROM

                  arstasks

             WHERE

                  accountkey = aeo.accountkey

                      AND entitlement_valuekey = v.ENTITLEMENT_VALUEKEY

                      AND status = 3)

                                   AND NOT EXISTS( SELECT

                  taskkey

             FROM

                  arstasks

             WHERE

                  accountkey = aeo.accountkey

                      AND entitlement_valuekey = v.ENTITLEMENT_VALUEKEY

                      AND status = 3)

                                     AND NOT EXISTS

                                                   (

                SELECT

                 a11.accountkey

                FROM

                 arstasks a11

                                                    WHERE

                                                                   a11.accountkey = a.accountkey

                   AND a11.status = 3

                   AND 2 = (

                                                                   select TASKTYPE from arstasks ar23

                                                                   where ar23.accountkey = a11.accountkey

                                                                   and ar23.status = 3

                   and ar23.UPDATEDATE > aeo.BASELINEDATE

                                                                   order by ar23.updatedate desc limit 1) )

                                                   and v.status = 1) as T1