Informatica

October 3, 2023 · 28 min read

Creator

Informatica is a leading data integration and management software, empowering organizations to extract, transform, and load data for efficient analysis and decision-making. It facilitates seamless integration of disparate data sources, ensuring data quality and governance throughout the process.

Identities ❌ | Accounts ✔️ | Groups ✔️ | App Roles ❌ | Entitlements ❌

Application

Steps

Login to RSA G&L console as System Administrator.
Navigate to Resources > Application.
Click on Create Application and select Other Application.
Enter the details as shown below.
Click Finish.

Account Collector

Steps

Login to RSA G&L console as System Administrator.
Navigate to Resources > Applications.
Click on Informatica.
Navigate to Collectors tab and click on Create Account Collector. Enter the details as shown below.
Click Next and enter the Oracle database details. Use the Test Connection to make sure the connection details are accurate.
Click Next. Select the appropriate data that you intend to collect.
Click Next. On the Account Data page, enter the following query to collect account information.

SELECT
  a.pos_name accountID,
  b.pou_fullname fullName,
  a.groupName groupName,
  a.poi_securitydomain securityDomain
FROM
  (
    SELECT
      poi3.poi_userinfo,
      poi2.psi_discriminator,
      poi2.pos_name,
      poi1.pos_name groupName,
      poi2.poi_securitydomain
    FROM
      PO_IDOBJ poi1,
      PO_IDOBJ poi2,
      PO_IDOBJ poi3
    WHERE
      poi1.poi_securitydomain = 'Native'
      AND poi1.psi_discriminator LIKE 'GROUP'
      AND poi2.psi_container = poi1.psi_container
      AND poi3.pos_name = poi2.pos_name
      AND poi3.psi_discriminator = 'LDAPUSER'
      AND poi3.poi_securitydomain = poi2.poi_securitydomain
    ORDER BY
      poi1.pos_name,
      poi2.pos_name
  ) a,
  PO_USERINFO b
WHERE
  a.poi_userinfo = b.psu_opid
  AND a.groupName <> 'Everyone'
ORDER BY
  a.pos_name‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The following are the important attributes

Click Next. On the User Account Mappings Data configuration page enter the following query to map the Informatica account to user identity.

SELECT
  DISTINCT(accountID),
  accountID AS userID,
  fullName
FROM
  (
    SELECT
      a.pos_name accountID,
      B.POU_FULLNAME fullName,
      a.groupName groupName,
      a.poi_securitydomain securityDomain
    FROM
      (
        SELECT
          poi3.poi_userinfo,
          poi2.psi_discriminator,
          poi2.pos_name,
          poi1.pos_name groupName,
          poi2.poi_securitydomain
        FROM
          PO_IDOBJ poi1,
          PO_IDOBJ poi2,
          PO_IDOBJ poi3
        WHERE
          poi1.poi_securitydomain = 'Native'
          AND poi1.psi_discriminator LIKE 'GROUP'
          AND poi2.psi_container = poi1.psi_container
          AND poi3.pos_name = poi2.pos_name
          AND poi3.psi_discriminator = 'LDAPUSER'
          AND poi3.poi_securitydomain = poi2.poi_securitydomain
        ORDER BY
          poi1.pos_name,
          poi2.pos_name
      ) a,
      po_userinfo b
    WHERE
      a.poi_userinfo = b.psu_opid
      AND a.groupName <> 'Everyone'
    ORDER BY
      pos_name
  )

The following are the important attributes

Click Next. In the Group Data configuration section enter the following query to collect groups from Informatica.

(
  SELECT
    groupName,
    REPLACE(
      '<p>The group gives access to folders:<br />' || RTRIM(
        XMLAGG(
          XMLELEMENT(E, PERMISSIONS, ';').EXTRACT('//text()')
          ORDER BY
            PERMISSIONS
        ).GetClobVal(),
        '<br />'
      ) || '</p>',
      ';',
      '<br />'
    ) AS description
  FROM
    (
      SELECT
        groupName,
        FOLDER_NAME || ': ' || PERMISSIONS AS PERMISSIONS
      FROM
        (
          SELECT
            'REP_ICOE_TEST' AS REPOSITORY,
            subj.subj_name folder_name,
            user_group.NAME groupName,
            CASE
              WHEN (
                (
                  obj_access.permissions - (obj_access.accountID + 1)
                ) IN (8, 16)
              ) THEN 'READ'
              WHEN (
                (
                  obj_access.permissions - (obj_access.accountID + 1)
                ) IN (10, 20)
              ) THEN 'READ,  EXECUTE'
              WHEN (
                (
                  obj_access.permissions - (obj_access.accountID + 1)
                ) IN (12, 24)
              ) THEN 'READ, WRITE'
              WHEN (
                (
                  obj_access.permissions - (obj_access.accountID + 1)
                ) IN (14, 28)
              ) THEN 'READ, WRITE, EXECUTE'
              ELSE 'NO PERMISSIONS'
            END permissions
          FROM
            ETLREPT.opb_object_access obj_access,
            ETLREPT.opb_subject subj,
            ETLREPT.opb_user_group user_group
          WHERE
            obj_access.object_type = 29
            AND obj_access.object_id = subj.subj_id
            AND obj_access.accountID = user_group.ID
            AND obj_access.user_type = user_group.TYPE
            AND obj_access.user_type = 2
            AND user_group.NAME LIKE 'GRP%'
          UNION
          SELECT
            'REP_ICOE_TEST2' AS REPOSITORY,
            subj.subj_name folder_name,
            user_group.NAME groupName,
            CASE
              WHEN (
                (
                  obj_access.permissions - (obj_access.accountID + 1)
                ) IN (8, 16)
              ) THEN 'READ'
              WHEN (
                (
                  obj_access.permissions - (obj_access.accountID + 1)
                ) IN (10, 20)
              ) THEN 'READ,  EXECUTE'
              WHEN (
                (
                  obj_access.permissions - (obj_access.accountID + 1)
                ) IN (12, 24)
              ) THEN 'READ, WRITE'
              WHEN (
                (
                  obj_access.permissions - (obj_access.accountID + 1)
                ) IN (14, 28)
              ) THEN 'READ, WRITE, EXECUTE'
              ELSE 'NO PERMISSIONS'
            END permissions
          FROM
            ETLREP2T.opb_object_access obj_access,
            ETLREP2T.opb_subject subj,
            ETLREP2T.opb_user_group user_group
          WHERE
            obj_access.object_type = 29
            AND obj_access.object_id = subj.subj_id
            AND obj_access.accountID = user_group.ID
            AND obj_access.user_type = user_group.TYPE
            AND obj_access.user_type = 2
            AND user_group.NAME LIKE 'GRP%'
        )
    )
  GROUP BY
    groupName
)

The following are the important attributes

In the Account Membership Data configuration section enter the following query to collect groups membership information from Informatica.

SELECT
  a.pos_name accountID,
  B.POU_FULLNAME fullName,
  a.groupName groupName,
  a.poi_securitydomain securityDomain
FROM
  (
    SELECT
      poi3.poi_userinfo,
      poi2.psi_discriminator,
      poi2.pos_name,
      poi1.pos_name groupName,
      poi2.poi_securitydomain
    FROM
      PO_IDOBJ poi1,
      PO_IDOBJ poi2,
      PO_IDOBJ poi3
    WHERE
      poi1.poi_securitydomain = 'Native'
      AND poi1.psi_discriminator LIKE 'GROUP'
      AND poi2.psi_container = poi1.psi_container
      AND poi3.pos_name = poi2.pos_name
      AND poi3.psi_discriminator = 'LDAPUSER'
      AND poi3.poi_securitydomain = poi2.poi_securitydomain
    ORDER BY
      poi1.pos_name,
      poi2.pos_name
  ) a,
  po_userinfo b
WHERE
  a.poi_userinfo = b.psu_opid
  AND a.groupName <> 'Everyone'
ORDER BY
  pos_name‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Click Next. In the User Resolution Rules, map the user to account.
Click Next. In the Member Account Resolution Rules, map the account collector.
Click Finish. Use the Test function to make sure the configurations are accurate.

COL017

Application​

Account Collector​

Application

Account Collector