Edited at

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

taro, 20190101, 101, 00001

jiro, 20190202, 102, 00001
ojiro, 20190303, 101, 00002
hanako, 20190404, 103, 00004

スクリーンショット 0001-08-09 10.06.07.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

スクリーンショット 0001-08-09 10.07.34.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.

スクリーンショット 0001-08-10 8.27.51.png

The database was ready.

スクリーンショット 0001-08-09 10.10.35.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)

スクリーンショット 0001-08-09 10.11.22.png

Enter the following (following)

Name: "test1"

Database: "lf01"

スクリーンショット 0001-08-09 10.13.57.png

Include path to s3://lf01/in00/

* It seems that you cannot select a bucket in another region

Check CSV

スクリーンショット 0001-08-09 10.14.22.png

Click [Add column] in the upper right.

スクリーンショット 0001-08-09 10.16.48.png

Add columns. The column to add↓

name(string)

date(string)

storeid(string)

accountnumber(string)

スクリーンショット 0001-08-09 10.16.59.png

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

スクリーンショット 0001-08-09 10.17.45.png

The table was ready.

スクリーンショット 0001-08-09 10.18.11.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)

スクリーンショット 0001-08-10 8.40.08.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.

スクリーンショット 0001-08-10 8.41.02.png


Check with Athena

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

スクリーンショット 0001-08-09 10.23.03.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

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.

スクリーンショット 0001-08-10 8.36.15.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.

スクリーンショット 0001-08-09 10.56.00.png

Enter the following (following)

IAM user add roles : test_user1

Database: lf01

Table: test1

スクリーンショット 0001-08-09 10.58.20.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

スクリーンショット 0001-08-09 12.43.35.png

The result looks something like this

スクリーンショット 0001-08-09 12.45.32.png


Check with Athena

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

スクリーンショット 0001-08-10 8.48.50.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):...

スクリーンショット 0001-08-10 8.50.55.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

スクリーンショット 0001-08-10 9.39.47.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.

スクリーンショット 0001-08-10 16.35.02.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