How to use Lake Formation ② (Data Catalog Authority Control)

# Control permissions to data catalog

Lake Formation allows fine-grained authority control in the data catalog.
For each user, you can prohibit adding tables to the database of the data catalog, hide certain columns, and limit the SQL that can be executed. I'll try.

#### S3 pass
s3://test-lf01/in00

#### Database name
lf01

#### table name
test1

#### Test IAM User

test_user1

# Upload CSV file to S3

File name: person.csv
Upload the following CSV file to s3://test-lf01/in00

```csv:
taro, 20190101, 101, 00001
jiro, 20190202, 102, 00001
ojiro, 20190303, 101, 00002
hanako, 20190404, 103, 00004
```
<img width="1125" alt="スクリーンショット 0001-08-09 10.06.07.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/efdf275d-31e7-fe7a-3e72-e309b45b64a8.png">

Schema assumes the following
name (string)
date (string)
storeid (string)
accountnumber (string)

# Create data catalog

### Create database

Click "Database" from the left menu on the Lake Formation screen, and click [Create database] in the upper right

<img width="1222" alt="スクリーンショット 0001-08-09 10.07.34.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/4a99c528-7c80-8453-b6e8-623ee7fe59e7.png">

Enter "lf01" in Name, uncheck "Grant All to Everyone for new tables in this database", and click [Create database] at the bottom right
※"Everyone" is an element of the Lake Formation user group. "Everyone" includes all principals with IAM permissions on Lake Formation, not the general public.

"Grant All to Everyone for new tables in this database" is a setting that grants all privileges to "Everyone" for new tables created in this database. The default is checked.

<img width="748" alt="スクリーンショット 0001-08-10 8.27.51.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/a8dcac1b-c54b-e687-566c-0ae927c252d9.png">

The database was ready.

<img width="1249" alt="スクリーンショット 0001-08-09 10.10.35.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/1b4d34f2-eb07-ed82-e9a0-9f6c98344fde.png">

### Create Table

Click "Tables" from the menu on the left side of the Lake Formation screen, and click [Create table] in the upper right (this time manually create the table)

<img width="1215" alt="スクリーンショット 0001-08-09 10.11.22.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/37b462e5-1c0b-4b5a-6898-7486e2ba478b.png">

Enter the following (following)

Name: "test1"
Database: "lf01"

<img width="747" alt="スクリーンショット 0001-08-09 10.13.57.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/4beb5ee7-e06f-a579-c08f-5d003282416c.png">

Include path to s3://lf01/in00/
* It seems that you cannot select a bucket in another region
Check CSV

<img width="721" alt="スクリーンショット 0001-08-09 10.14.22.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/e5b00a58-fa35-6b56-321e-02fb72c6cc6a.png">

Click [Add column] in the upper right.

<img width="749" alt="スクリーンショット 0001-08-09 10.16.48.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/7356968f-4a65-a565-4d47-e2548b11ef03.png">

Add columns. The column to add↓
name(string)
date(string)
storeid(string)
accountnumber(string)

<img width="541" alt="スクリーンショット 0001-08-09 10.16.59.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/26c502c7-57c9-c42b-eb89-ff98f88f896b.png">

Click [Submit] at the bottom right when you finish adding.

<img width="747" alt="スクリーンショット 0001-08-09 10.17.45.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/9db56e58-c486-feb2-3367-67bfc9cdfbf6.png">

The table was ready.

<img width="1233" alt="スクリーンショット 0001-08-09 10.18.11.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/9ebf0333-01f9-ae70-f946-e89cedfdffb3.png">

### Authority check

For the database, the administrator has full privileges and "Everyone" has full privileges (by default, full privileges are given to "Everyone" when creating a database)

<img width="978" alt="スクリーンショット 0001-08-10 8.40.08.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/4fe34e01-8d1c-f81b-e3e1-b12bf6de88c6.png">

For the table, the administrator has full authority and no "Everyone" (as described above, this time when creating a database, the newly created table is set not to give full authority to "Everyone"). So only the administrator can operate it.

<img width="988" alt="スクリーンショット 0001-08-10 8.41.02.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/60b26092-80ab-faec-5f29-df7945335b34.png">

## Check with Athena

First of all, the administrator can confirm that the data catalog created by Lake Formation can be viewed on Athena.

<img width="1088" alt="スクリーンショット 0001-08-09 10.23.03.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/58646435-570d-dc96-edaf-42ced95c6744.png">

# Authority management by Lake Formation

Confirm authority control at the column level, which is one of the sales of Lake Formation

This time, I don't want to display the storeid and accountnumber columns for some users. I don't want to drop the table. Put the control.

### Create IAM user

The procedure is omitted, but test_user1 with Administrator policy was created like this.

test_user1 is administrator at IAM

![スクリーンショット 0001-08-09 10.28.09.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/57ca8aaa-d946-201f-9de2-55e40e76205e.png)

When you access Athena with test_user1, you can see lf01 of Database, but the table is not displayed because there is no authority to the table yet. Of course, queries are also rejected.
You could see it before Lake Formation appeared. You can think of this feature as a Lake Formation resource-based policy.

<img width="985" alt="スクリーンショット 0001-08-10 8.36.15.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/a8a292e9-7224-9874-476f-7f1dcb9e5cac.png">

### Set by Data Permissions of Lake Formation. Hide only the specific columns that you originally wanted to do

I don't want to display the storeid and accountnumber columns for the test_user1 user. I don't want to drop the table. Put the control.

Click "Data Permissions" in the left menu on the LakeFormation screen, then click [Grant] in the upper right.

<img width="1096" alt="スクリーンショット 0001-08-09 10.56.00.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/66879668-1895-7d9f-e1eb-644894cb48ff.png">

Enter the following (following)
IAM user add roles : test_user1
Database: lf01
Table: test1

<img width="453" alt="スクリーンショット 0001-08-09 10.58.20.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/90356778-4978-f6c4-fec6-446bfe4d9823.png">

Enter the following and click [Grant] at the bottom right

Select "Include columns" for Column
Select "name" and "date" for Include columns
Check "select" for Table permissions

<img width="446" alt="スクリーンショット 0001-08-09 12.43.35.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/828fbbc6-ac73-9991-340a-24fbd92352f1.png">

The result looks something like this

<img width="898" alt="スクリーンショット 0001-08-09 12.45.32.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/de8d4645-1487-f809-31ef-fb1f0327ba28.png">

### Check with Athena

For test_user1, only the name and date columns are visible.

<img width="991" alt="スクリーンショット 0001-08-10 8.48.50.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/40fb015d-9c4a-3d0f-7219-cdd390aec6b3.png">

DDL is rejected properly
You can see a message that looks like a permission error as follows.
MetaException(message:Insufficient Lake Formation permission(s):...

<img width="1158" alt="スクリーンショット 0001-08-10 8.50.55.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/b01da879-1467-e6b2-5087-5be1e9d2c084.png">

The Lake Access Recent Access Activity log looks something like this
* Since it is a CloudTrail log, it will be output after about 10 minutes. Also on this screen, the filter is quite weak ...

Click on View event in the upper right to check details

<img width="954" alt="スクリーンショット 0001-08-10 9.39.47.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/4bf0af9f-c7c7-8134-3fb9-52fbfdae117f.png">

### (Supplement) _DataCatalog default settings

You can enable or disable the following two default permissions for newly created databases and tables (default is enabled):

* Give All permission to "Everyone" for newly created database
* Grant ALL permission to "Everyone" on newly created table

If you don't need it, uncheck it and click [Save] at the bottom right. As a result, the newly created database or table is not automatically granted to "Everyone" (it can be operated with IAM authority), and operations that give explicit fine-grained permissions on Lake Formation can be performed. I can do it.

"Everyone" is an entity that exists for migration from GlueDataCatalog.

<img width="1037" alt="スクリーンショット 0001-08-10 16.35.02.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/5de331bf-9f0f-2fb3-bf41-c07f9da41037.png">

## Please come here

How to use lake formation
https://qiita.com/pioho07/items/093d971dcb5656730ea3

How to use Glue(Japanese)
https://qiita.com/pioho07/items/32f76a16cbf49f9f712f