AWS Lake Formationを有効にした時の既存リソース(Glue,S3)へのアクセスについて整理してみる

employee_id,first_name,last_name,department,salary,hire_date
1001,John,Smith,Sales,50000,2022-01-15
1002,Mary,Johnson,IT,60000,2021-06-20
1003,Robert,Williams,HR,45000,2023-03-10
1004,Sarah,Brown,Sales,52000,2022-08-01
1005,Michael,Davis,IT,65000,2021-04-15
1006,Lisa,Miller,HR,47000,2023-01-20
1007,David,Wilson,Sales,51000,2022-11-30
1008,Jennifer,Jones,IT,63000,2021-09-10
1009,James,Taylor,HR,46000,2023-02-28
1010,Emily,Anderson,Sales,53000,2022-05-15

s3://<任意のBucket名>/cm_kasama_hr_employee/personal_info/personal_info.csv

aws s3 ls s3://<任意のBucket名>/
                           PRE admin/
                           PRE hr-restrict/
                           PRE hr/

AWSTemplateFormatVersion: '2010-09-09'
Description: IAM Roles and Policies for HR Reader

Parameters:
  UserArn:
    Type: String
    Description: IAM User ARN who can assume this role
  AthenaQueryBucketName:
    Type: String
    Description: S3 bucket name for Athena query results
  LakeformationBucketName:
    Type: String
    Description: S3 bucket name for Lake Formation data
  RolePrefixName:
    Type: String  
    Description: Role prefix name

Resources:
  HRReaderRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: !Sub ${RolePrefixName}-hr-data-access-with-iam-policy
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Effect: Allow
            Principal:
              AWS: !Ref UserArn
            Action: sts:AssumeRole
            Condition:
              Bool:
                aws:MultiFactorAuthPresent: 'true'
      Policies:
        - PolicyName: HRReaderPolicy
          PolicyDocument:
            Version: '2012-10-17'
            Statement:
              - Effect: Allow
                Action:
                  - glue:GetDatabases
                  - glue:GetTables
                  - glue:GetTable
                  - glue:GetPartitions
                  - glue:GetDatabase
                Resource: '*'
              - Effect: Allow
                Action:
                  - athena:StartQueryExecution
                  - athena:GetQueryExecution
                  - athena:GetQueryResults
                  - athena:GetWorkGroup
                  - athena:ListWorkGroups
                Resource: '*'
              - Effect: Allow
                Action:
                  - s3:PutObject
                  - s3:GetObject
                  - s3:ListBucket
                  - s3:GetBucketLocation
                Resource:
                  - !Sub arn:aws:s3:::${AthenaQueryBucketName}
                  - !Sub arn:aws:s3:::${AthenaQueryBucketName}/hr/*
              - Effect: Allow
                Action:
                  - s3:ListBucket
                  - s3:GetObject
                Resource:
                  - !Sub arn:aws:s3:::${LakeformationBucketName}
                  - !Sub arn:aws:s3:::${LakeformationBucketName}/*
              - Effect: Allow
                Action:
                  - s3:ListAllMyBuckets
                Resource: '*'

  HRReaderRestrictRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: !Sub ${RolePrefixName}-hr-data-access-with-lakeformation
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Effect: Allow
            Principal:
              AWS: !Ref UserArn
            Action: sts:AssumeRole
            Condition:
              Bool:
                aws:MultiFactorAuthPresent: 'true'
      Policies:
        - PolicyName: HRReaderRestrictPolicy
          PolicyDocument:
            Version: '2012-10-17'
            Statement:
              - Effect: Allow
                Action:
                  - glue:GetDatabases
                  - glue:GetTables
                  - glue:GetTable
                  - glue:GetPartitions
                  - glue:GetDatabase
                Resource: '*'
              - Effect: Allow
                Action:
                  - athena:StartQueryExecution
                  - athena:GetQueryExecution
                  - athena:GetQueryResults
                  - athena:GetWorkGroup
                  - athena:ListWorkGroups
                Resource: '*'
              - Effect: Allow
                Action:
                  - s3:PutObject
                  - s3:GetObject
                  - s3:ListBucket
                  - s3:GetBucketLocation
                Resource:
                  - !Sub arn:aws:s3:::${AthenaQueryBucketName}
                  - !Sub arn:aws:s3:::${AthenaQueryBucketName}/hr-restrict/*
              - Effect: Allow
                Action:
                  - lakeformation:GetDataAccess
                Resource: '*'
              - Effect: Allow
                Action:
                  - s3:ListAllMyBuckets
                Resource: '*'

-- データベース作成
CREATE DATABASE IF NOT EXISTS cm_kasama_hr_employee;
-- テーブル作成
CREATE EXTERNAL TABLE cm_kasama_hr_employee.personal_info ( 
	employee_id INT, 
	first_name STRING, 
	last_name STRING, 
	department STRING, 
	salary INT, 
	hire_date DATE
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://<任意のBucket名>/cm_kasama_hr_employee/personal_info/'
TBLPROPERTIES ('skip.header.line.count'='1');