---
policy: /ce/ca/aws/iam/delete-user-inline-or-directly-attached-policies
logic: /ce/ca/aws/iam/delete-user-inline-or-directly-attached-policies/prod.logic.yaml
executionTime: 2025-04-24T23:44:51.835762856Z
rows:
  - id: test1
    match: true
    status:
      expected: DISAPPEARED
      actual: DISAPPEARED
    conditionIndex:
      expected: 99
      actual: 99
    conditionText:
      expected: isDisappeared(CA10__disappearanceTime__c)
      actual: isDisappeared(CA10__disappearanceTime__c)
    runtimeError: {}
  - id: test2
    match: true
    status:
      expected: INCOMPLIANT
      actual: INCOMPLIANT
    conditionIndex:
      expected: 199
      actual: 199
    conditionText:
      expected: CA10__AWS_IAM_Policy_User_Links__r.has(COMPLIANT)
      actual: CA10__AWS_IAM_Policy_User_Links__r.has(COMPLIANT)
    runtimeError: {}
  - id: test3
    match: true
    status:
      expected: INCOMPLIANT
      actual: INCOMPLIANT
    conditionIndex:
      expected: 299
      actual: 299
    conditionText:
      expected: CA10__userPolicies__r.has(COMPLIANT)
      actual: CA10__userPolicies__r.has(COMPLIANT)
    runtimeError: {}
  - id: test4
    match: true
    status:
      expected: COMPLIANT
      actual: COMPLIANT
    conditionIndex:
      expected: 300
      actual: 300
    conditionText:
      expected: otherwise
      actual: otherwise
    runtimeError: {}
usedFiles:
  - path: /ce/ca/aws/iam/delete-user-inline-or-directly-attached-policies/policy.yaml
    md5Hash: 1F459CEAE4522E4F5040CFC8D8812F11
    content: |
      ---
      names:
        full: AWS IAM User has inline or directly attached policies
        contextual: User has inline or directly attached policies
      description: |-
        IAM users are granted access to services, functions, and data through IAM policies. There are four ways to define policies for a user:
         1) Edit the user policy directly, aka an inline, or user, policy.
         2) attach a policy directly to a user.
         3) add the user to an IAM group that has an attached policy.
         4) add the user to an IAM group that has an inline policy.
         Only the third implementation is recommended.
      type: COMPLIANCE_POLICY
      categories:
        - SECURITY
      frameworkMappings:
        - "/frameworks/cis-aws-v5.0.0/01/14"
        - "/frameworks/cloudaware/identity-and-access-governance/user-account-management"
        - "/frameworks/aws-fsbp-v1.0.0/iam/02"
      similarPolicies:
        internal:
          - dec-x-4157c58a
        cloudConformity:
          - url: https://www.trendmicro.com/cloudoneconformity-staging/knowledge-base/aws/IAM/receive-permissions-via-groups-only.html
            name: Receive Permissions via IAM Groups Only
  - path: /ce/ca/aws/iam/delete-user-inline-or-directly-attached-policies/prod.logic.yaml
    md5Hash: A6FDBA788A21E785B7CC040DDB4F52D5
    content: |-
      ---
      inputType: "CA10__CaAwsUser__c"
      testData:
        - file: test-data.json
      conditions:
        - status: "INCOMPLIANT"
          currentStateMessage: "User has directly attached policies."
          remediationMessage: "Add a user to an IAM group"
          check:
            RELATED_LIST_HAS:
              status: "COMPLIANT"
              relationshipName: "CA10__AWS_IAM_Policy_User_Links__r"
        - status: "INCOMPLIANT"
          currentStateMessage: "User has inline policies."
          remediationMessage: "Add a user to an IAM group."
          check:
            RELATED_LIST_HAS:
              status: "COMPLIANT"
              relationshipName: "CA10__userPolicies__r"
      otherwise:
        status: "COMPLIANT"
        currentStateMessage: "User doesn't have inline or directly attached policies."
      relatedLists:
        - relationshipName: "CA10__userPolicies__r"
          conditions: []
          otherwise:
            status: "COMPLIANT"
            currentStateMessage: "This is an inline policy."
        - relationshipName: "CA10__AWS_IAM_Policy_User_Links__r"
          conditions: []
          otherwise:
            status: "COMPLIANT"
            currentStateMessage: "This is a directly attached policy."
  - path: /ce/ca/aws/iam/delete-user-inline-or-directly-attached-policies/test-data.json
    md5Hash: A6D58BF7BB733C8EF1156C10C89834D6
    content: |-
      [
        {
          "expectedResult": {
            "status": "DISAPPEARED",
            "conditionIndex": "99",
            "conditionText": "isDisappeared(CA10__disappearanceTime__c)",
            "runtimeError": null
          },
          "context": {
            "snapshotTime": "2024-07-09T20:48:18Z"
          },
          "Id": "test1",
          "CA10__disappearanceTime__c": "2024-07-09T19:51:32Z",
          "CA10__userPolicies__r": [],
          "CA10__AWS_IAM_Policy_User_Links__r": []
        },
        {
          "expectedResult": {
            "status": "INCOMPLIANT",
            "conditionIndex": "199",
            "conditionText": "CA10__AWS_IAM_Policy_User_Links__r.has(COMPLIANT)",
            "runtimeError": null
          },
          "context": {
            "snapshotTime": "2024-07-09T20:48:18Z"
          },
          "Id": "test2",
          "CA10__disappearanceTime__c": null,
          "CA10__userPolicies__r": [],
          "CA10__AWS_IAM_Policy_User_Links__r": [
            {
              "Id": "test2_1",
              "CA10__disappearanceTime__c": null,
              "CA10__user__c": "test2"
            }
          ]
        },
        {
          "expectedResult": {
            "status": "INCOMPLIANT",
            "conditionIndex": "299",
            "conditionText": "CA10__userPolicies__r.has(COMPLIANT)",
            "runtimeError": null
          },
          "context": {
            "snapshotTime": "2024-07-09T20:48:18Z"
          },
          "Id": "test3",
          "CA10__disappearanceTime__c": null,
          "CA10__userPolicies__r": [
            {
              "Id": "test3_1",
              "CA10__disappearanceTime__c": null,
              "CA10__user__c": "test3"
            }
          ],
          "CA10__AWS_IAM_Policy_User_Links__r": []
        },
        {
          "expectedResult": {
            "status": "COMPLIANT",
            "conditionIndex": "300",
            "conditionText": "otherwise",
            "runtimeError": null
          },
          "context": {
            "snapshotTime": "2024-07-09T20:48:18Z"
          },
          "Id": "test4",
          "CA10__disappearanceTime__c": null,
          "CA10__userPolicies__r": [],
          "CA10__AWS_IAM_Policy_User_Links__r": []
        }
      ]
script: |-
  CREATE TEMP FUNCTION mock_ExpectedResult()
  RETURNS ARRAY<STRUCT<
          Id STRING,
          expectedResult STRUCT<status STRING, conditionIndex DECIMAL, conditionText STRING, runtimeError STRING>
      >>
  DETERMINISTIC
  LANGUAGE js
  AS r"""
    return [ {
    "Id" : "test1",
    "expectedResult" : {
      "status" : "DISAPPEARED",
      "conditionIndex" : "99",
      "conditionText" : "isDisappeared(CA10__disappearanceTime__c)",
      "runtimeError" : null
    }
  }, {
    "Id" : "test2",
    "expectedResult" : {
      "status" : "INCOMPLIANT",
      "conditionIndex" : "199",
      "conditionText" : "CA10__AWS_IAM_Policy_User_Links__r.has(COMPLIANT)",
      "runtimeError" : null
    }
  }, {
    "Id" : "test3",
    "expectedResult" : {
      "status" : "INCOMPLIANT",
      "conditionIndex" : "299",
      "conditionText" : "CA10__userPolicies__r.has(COMPLIANT)",
      "runtimeError" : null
    }
  }, {
    "Id" : "test4",
    "expectedResult" : {
      "status" : "COMPLIANT",
      "conditionIndex" : "300",
      "conditionText" : "otherwise",
      "runtimeError" : null
    }
  } ];
  """;

  CREATE TEMP FUNCTION mock_CA10__CaAwsUser__c()
  RETURNS ARRAY<STRUCT<
      Id STRING,
      CA10__disappearanceTime__c TIMESTAMP,
      context STRUCT<
          snapshotTime TIMESTAMP
      >
  >>
  DETERMINISTIC
  LANGUAGE js
  AS r"""
    return [ {
    "context" : {
      "snapshotTime" : new Date("2024-07-09T20:48:18Z")
    },
    "Id" : "test1",
    "CA10__disappearanceTime__c" : new Date("2024-07-09T19:51:32Z")
  }, {
    "context" : {
      "snapshotTime" : new Date("2024-07-09T20:48:18Z")
    },
    "Id" : "test2"
  }, {
    "context" : {
      "snapshotTime" : new Date("2024-07-09T20:48:18Z")
    },
    "Id" : "test3"
  }, {
    "context" : {
      "snapshotTime" : new Date("2024-07-09T20:48:18Z")
    },
    "Id" : "test4"
  } ];
  """;

  CREATE TEMP FUNCTION mock_CA10__CaAwsUserPolicy__c()
  RETURNS ARRAY<STRUCT<
      Id STRING,
      CA10__disappearanceTime__c TIMESTAMP,
      CA10__user__c STRING,
      context STRUCT<
          snapshotTime TIMESTAMP
      >
  >>
  DETERMINISTIC
  LANGUAGE js
  AS r"""
    return [ {
    "context" : {
      "snapshotTime" : new Date("2024-07-09T20:48:18Z")
    },
    "Id" : "test3_1",
    "CA10__user__c" : "test3"
  } ];
  """;

  CREATE TEMP FUNCTION mock_CA10__CaAwsIamUserPolicyAttachment__c()
  RETURNS ARRAY<STRUCT<
      Id STRING,
      CA10__disappearanceTime__c TIMESTAMP,
      CA10__user__c STRING,
      context STRUCT<
          snapshotTime TIMESTAMP
      >
  >>
  DETERMINISTIC
  LANGUAGE js
  AS r"""
    return [ {
    "context" : {
      "snapshotTime" : new Date("2024-07-09T20:48:18Z")
    },
    "Id" : "test2_1",
    "CA10__user__c" : "test2"
  } ];
  """;

  CREATE TEMP FUNCTION process_CA10__CaAwsUser__c(
      obj STRUCT<
          Id STRING,
          CA10__disappearanceTime__c TIMESTAMP,
          CA10__userPolicies__r ARRAY<STRUCT<
              Id STRING,
              CA10__disappearanceTime__c TIMESTAMP,
              CA10__user__c STRING,
              result STRUCT<status STRING, conditionIndex DECIMAL, conditionText STRING, currentStateMessage STRING, currentStateReferences STRING, remediation STRING, runtimeError STRING>
          >>,
          CA10__AWS_IAM_Policy_User_Links__r ARRAY<STRUCT<
              Id STRING,
              CA10__disappearanceTime__c TIMESTAMP,
              CA10__user__c STRING,
              result STRUCT<status STRING, conditionIndex DECIMAL, conditionText STRING, currentStateMessage STRING, currentStateReferences STRING, remediation STRING, runtimeError STRING>
          >>
      >,
      snapshotTime TIMESTAMP
  )
  RETURNS STRUCT<status STRING, conditionIndex DECIMAL, conditionText STRING, currentStateMessage STRING, currentStateReferences STRING, remediation STRING, runtimeError STRING>
  DETERMINISTIC
  LANGUAGE js
  AS r"""
      var references1 = [];
      // condition[0], conditionIndex:[0..99]
      references1.push('Deleted From AWS [CA10__disappearanceTime__c]: ' + obj.CA10__disappearanceTime__c);
      if (obj.CA10__disappearanceTime__c != null) {
          return {status: 'DISAPPEARED', conditionIndex: 99, conditionText: "isDisappeared(CA10__disappearanceTime__c)", currentStateMessage: "Object is deleted in the source", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null};
      }
      var count_CA10__AWS_IAM_Policy_User_Links__r_COMPLIANT2 = 0;
      if (obj.CA10__AWS_IAM_Policy_User_Links__r != null) {
          for (var i3 = 0; i3 < obj.CA10__AWS_IAM_Policy_User_Links__r.length; i3++) {
              if (obj.CA10__AWS_IAM_Policy_User_Links__r[i3].result.status == 'COMPLIANT') {
                  count_CA10__AWS_IAM_Policy_User_Links__r_COMPLIANT2 += 1;
              }
          }
      }
      // condition[1], conditionIndex:[100..199]
      references1.push('Related list [CA10__AWS_IAM_Policy_User_Links__r] ' + (count_CA10__AWS_IAM_Policy_User_Links__r_COMPLIANT2 > 0 ? 'has' : 'does not have') + ' objects in COMPLIANT status');
      if (count_CA10__AWS_IAM_Policy_User_Links__r_COMPLIANT2 > 0) {
          return {status: 'INCOMPLIANT', conditionIndex: 199, conditionText: "CA10__AWS_IAM_Policy_User_Links__r.has(COMPLIANT)", currentStateMessage: "User has directly attached policies.", currentStateReferences: references1.join('\n'), remediation: "Add a user to an IAM group", runtimeError: null};
      }
      var count_CA10__userPolicies__r_COMPLIANT4 = 0;
      if (obj.CA10__userPolicies__r != null) {
          for (var i5 = 0; i5 < obj.CA10__userPolicies__r.length; i5++) {
              if (obj.CA10__userPolicies__r[i5].result.status == 'COMPLIANT') {
                  count_CA10__userPolicies__r_COMPLIANT4 += 1;
              }
          }
      }
      // condition[2], conditionIndex:[200..299]
      references1.push('Related list [CA10__userPolicies__r] ' + (count_CA10__userPolicies__r_COMPLIANT4 > 0 ? 'has' : 'does not have') + ' objects in COMPLIANT status');
      if (count_CA10__userPolicies__r_COMPLIANT4 > 0) {
          return {status: 'INCOMPLIANT', conditionIndex: 299, conditionText: "CA10__userPolicies__r.has(COMPLIANT)", currentStateMessage: "User has inline policies.", currentStateReferences: references1.join('\n'), remediation: "Add a user to an IAM group.", runtimeError: null};
      }
      return {status: 'COMPLIANT', conditionIndex: 300, conditionText: "otherwise", currentStateMessage: "User doesn't have inline or directly attached policies.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null};
  """;

  CREATE TEMP FUNCTION process_CA10__AWS_IAM_Policy_User_Links__r(
      obj STRUCT<
          Id STRING,
          CA10__disappearanceTime__c TIMESTAMP,
          CA10__user__c STRING
      >,
      snapshotTime TIMESTAMP
  )
  RETURNS STRUCT<status STRING, conditionIndex DECIMAL, conditionText STRING, currentStateMessage STRING, currentStateReferences STRING, remediation STRING, runtimeError STRING>
  DETERMINISTIC
  LANGUAGE js
  AS r"""
      var references1 = [];
      // condition[0], conditionIndex:[0..99]
      references1.push('Deleted From AWS [CA10__disappearanceTime__c]: ' + obj.CA10__disappearanceTime__c);
      if (obj.CA10__disappearanceTime__c != null) {
          return {status: 'DISAPPEARED', conditionIndex: 99, conditionText: "isDisappeared(CA10__disappearanceTime__c)", currentStateMessage: "Object is deleted in the source", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null};
      }
      return {status: 'COMPLIANT', conditionIndex: 100, conditionText: "otherwise", currentStateMessage: "This is a directly attached policy.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null};
  """;

  CREATE TEMP FUNCTION process_CA10__userPolicies__r(
      obj STRUCT<
          Id STRING,
          CA10__disappearanceTime__c TIMESTAMP,
          CA10__user__c STRING
      >,
      snapshotTime TIMESTAMP
  )
  RETURNS STRUCT<status STRING, conditionIndex DECIMAL, conditionText STRING, currentStateMessage STRING, currentStateReferences STRING, remediation STRING, runtimeError STRING>
  DETERMINISTIC
  LANGUAGE js
  AS r"""
      var references1 = [];
      // condition[0], conditionIndex:[0..99]
      references1.push('Deleted From AWS [CA10__disappearanceTime__c]: ' + obj.CA10__disappearanceTime__c);
      if (obj.CA10__disappearanceTime__c != null) {
          return {status: 'DISAPPEARED', conditionIndex: 99, conditionText: "isDisappeared(CA10__disappearanceTime__c)", currentStateMessage: "Object is deleted in the source", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null};
      }
      return {status: 'COMPLIANT', conditionIndex: 100, conditionText: "otherwise", currentStateMessage: "This is an inline policy.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null};
  """;


  SELECT
    expectedResult.Id as Id,
    IF (
      IFNULL(expectedResult.expectedResult.status, '') = IFNULL(sObject.result.status, '')
      AND IFNULL(expectedResult.expectedResult.conditionIndex, -1) = IFNULL(sObject.result.conditionIndex, -1)
      AND IFNULL(expectedResult.expectedResult.conditionText, '') = IFNULL(sObject.result.conditionText, '')
      AND IFNULL(expectedResult.expectedResult.runtimeError, '') = IFNULL(sObject.result.runtimeError, ''),
      "MATCH",
      "FAIL"
    ) as match,
    expectedResult.expectedResult.status as expectedStatus, sObject.result.status as actualStatus,
    expectedResult.expectedResult.conditionIndex as expectedConditionIndex, sObject.result.conditionIndex as actualConditionIndex,
    expectedResult.expectedResult.conditionText as expectedConditionText, sObject.result.conditionText as actualConditionText,
    expectedResult.expectedResult.runtimeError as expectedRuntimeError, sObject.result.runtimeError as actualRuntimeError
  FROM UNNEST(mock_ExpectedResult()) expectedResult
  LEFT JOIN (
      SELECT
          sObject.Id AS Id,
          sObject.CA10__disappearanceTime__c AS CA10__disappearanceTime__c,
          `CA10__userPolicies__r`.arr AS CA10__userPolicies__r,
          `CA10__AWS_IAM_Policy_User_Links__r`.arr AS CA10__AWS_IAM_Policy_User_Links__r,
          process_CA10__CaAwsUser__c(
              STRUCT(
                  sObject.Id AS Id,
                  sObject.CA10__disappearanceTime__c AS CA10__disappearanceTime__c,
                  `CA10__userPolicies__r`.arr AS CA10__userPolicies__r,
                  `CA10__AWS_IAM_Policy_User_Links__r`.arr AS CA10__AWS_IAM_Policy_User_Links__r
              ),
              sObject.context.snapshotTime
          ) as result
      FROM UNNEST(mock_CA10__CaAwsUser__c()) AS sObject
      LEFT JOIN (
          SELECT
              sObject.CA10__user__c,
              ARRAY_AGG(
                  STRUCT(
                      sObject.Id AS Id,
                      sObject.CA10__disappearanceTime__c AS CA10__disappearanceTime__c,
                      sObject.CA10__user__c AS CA10__user__c,
                      process_CA10__userPolicies__r(
                          STRUCT(
                              sObject.Id AS Id,
                              sObject.CA10__disappearanceTime__c AS CA10__disappearanceTime__c,
                              sObject.CA10__user__c AS CA10__user__c
                          ),
                          sObject.context.snapshotTime
                      ) as result
                  )
              ) AS arr
          FROM UNNEST(mock_CA10__CaAwsUserPolicy__c()) AS sObject
          GROUP BY sObject.CA10__user__c
      ) AS `CA10__userPolicies__r`
          ON sObject.Id = `CA10__userPolicies__r`.CA10__user__c
      LEFT JOIN (
          SELECT
              sObject.CA10__user__c,
              ARRAY_AGG(
                  STRUCT(
                      sObject.Id AS Id,
                      sObject.CA10__disappearanceTime__c AS CA10__disappearanceTime__c,
                      sObject.CA10__user__c AS CA10__user__c,
                      process_CA10__AWS_IAM_Policy_User_Links__r(
                          STRUCT(
                              sObject.Id AS Id,
                              sObject.CA10__disappearanceTime__c AS CA10__disappearanceTime__c,
                              sObject.CA10__user__c AS CA10__user__c
                          ),
                          sObject.context.snapshotTime
                      ) as result
                  )
              ) AS arr
          FROM UNNEST(mock_CA10__CaAwsIamUserPolicyAttachment__c()) AS sObject
          GROUP BY sObject.CA10__user__c
      ) AS `CA10__AWS_IAM_Policy_User_Links__r`
          ON sObject.Id = `CA10__AWS_IAM_Policy_User_Links__r`.CA10__user__c
  ) sObject ON sObject.Id = expectedResult.Id;
