Lab 7.5: an analyst who sees only GBP rows and no free-text columns
Register the curated bucket with Lake Formation, switch the transactions table from IAM-only access to Lake Formation permissions, create a data cells filter, and prove from the analyst's own session that the row and column restrictions hold.
Assumes: AWS Lake Formation · AWS Glue Data Catalog · Amazon Athena engine v3 · AWS CLI v2 · Region us-east-1 · profile learn-sandbox · labs/m07-transaction-pipeline/infra/cli/lab75-lake-formation.sh. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lab you can:
- Make yourself a data lake administrator and explain why the setup script refuses to do it for you.
- Register an SSE-KMS S3 location with Lake Formation and give its service-linked role the key permission it needs.
- Remove
IAMAllowedPrincipalsfrom a table and describe exactly what changes for whom. - Create a data cells filter (
currency = 'GBP', excluding three free-text columns) and grantSELECTon it to a role. - Assume the analyst role in a separate CLI profile and show, with three queries, that rows and columns are restricted.
- Name the ways the restriction can still be bypassed and verify the analyst has no direct S3 path.
- Tear the whole configuration down and give the table back to IAM-only access.
Prerequisites
- Governance, lineage and cataloguing with Lake Formation, which explains the concepts used here. Read Secure pipeline design for the key and role background.
- Data in the curated table. This lab filters
hl_m07_curated.transactions, which the project pipeline fills. Do the module project up to and including "Make it queryable in Athena" first, or the analyst will correctly see zero rows of nothing. - The stack
HlM07-DataLakedeployed, AWS CLI v2,aws sso login --profile learn-sandbox, Git Bash (the script isbash).
Cost label
🟢 Free. Lake Formation permissions, filters and tags have no charge. You pay only for the services that read data: a handful of Athena queries over a few hundred kilobytes, which round up to the 10 MB minimum (a fraction of a cent). The stack's standing cost is the one from Lab 7.3 and the project.
Scenario
Harbourline's UK retail analysts study card spend. They may see GBP transactions only, and they have no business reason to see counterparty names or payment references, which often contain people's names. Today there is one table and one S3 location, and everyone with S3 access sees every row and column. You will enforce the restriction in the catalogue layer, once, instead of copying the table three times.
Architecture
Security-boundary view. The analyst's IAM policy allows Athena on one workgroup, catalogue reads, lakeformation:GetDataAccess, and read/write on the results bucket only. It deliberately has no s3:GetObject on the curated bucket. Lake Formation hands Athena temporary credentials for the data, and Athena applies the filter before it returns a row.
The files
| File | Role in the lab |
|---|---|
infra/cli/lab75-lake-formation.sh | setup and teardown (about 80 lines of AWS CLI) |
sql/athena/02_project_tables.sql | Creates hl_m07_curated.transactions (run by the project) |
src/.../AccessReview.cs (from the governance lesson) | Optional: lists every grant for review |
Part A: before you start
Become a data lake administrator (one-off, in the console)
Lake Formation → Administration → Administrative roles and tasks → Data lake administrators → Add, and choose the IAM role behind your SSO session. The script never changes data lake settings because the setting replaces the whole administrator list: a script that "adds you" can remove everyone else. If you are using a shared sandbox, add to the list, do not replace it.
To find the role, remove the session suffix from your caller identity:
export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1 MSYS_NO_PATHCONV=1
aws sts get-caller-identity --query Arn --output text
# arn:aws:sts::111122223333:assumed-role/AWSReservedSSO_<PermissionSet>_<hash>/<you>
aws iam list-roles --path-prefix /aws-reserved/sso.amazonaws.com/ --query "Roles[].Arn" --output textConfirm the table exists and has rows
ACCOUNT=$(aws sts get-caller-identity --query Account --output text)
aws glue get-table --database-name hl_m07_curated --name transactions --query "Table.StorageDescriptor.Location"Then run SELECT currency, count(*) FROM hl_m07_curated.transactions GROUP BY currency in workgroup hl-m07-analysts as yourself. You should see both GBP and EUR. This is your before picture.
Part B: set up
Run the setup script
cd labs/m07-transaction-pipeline
./infra/cli/lab75-lake-formation.sh setupWhat it does, in order (read the script, do not run it blind):
- Creates the role
hl-m07-analyst-ukwith a trust policy for the account (arn:aws:iam::<account>:root) so your SSO session can assume it, taggedProject=cea-lab. Its inline policy allows: Athena start/get/stop on workgrouphl-m07-analysts; Glue read of the catalogue;lakeformation:GetDataAccess; S3 get/put/list on the results bucket only; andkms:Decrypt/GenerateDataKeyconditioned on the aliasalias/hl-m07-data. - Registers the curated bucket with Lake Formation using the service-linked role, so Lake Formation can vend credentials for it.
- Revokes
IAMAllowedPrincipalsonhl_m07_curated.transactions. That default grant means "IAM decides"; once it is gone, Lake Formation permissions decide for integrated engines. - Creates the data cells filter
uk_gbp_no_free_text: row filtercurrency = 'GBP', excluded columnscounterparty_name,reference,source_file. - Grants the role
DESCRIBEon the database andSELECTon the filter, and nothing else.
Let the service-linked role decrypt the data key
The curated bucket is SSE-KMS, and Lake Formation reads it through its service-linked role (it exists now, because registration created it). That role needs kms:Decrypt on the key, or every analyst query fails with a KMS error. AWS documents adding the role to the key policy; a grant achieves the same without editing the policy:
KEY_ARN=$(aws kms describe-key --key-id alias/hl-m07-data --query KeyMetadata.Arn --output text)
aws kms create-grant --key-id "$KEY_ARN" \
--grantee-principal "arn:aws:iam::${ACCOUNT}:role/aws-service-role/lakeformation.amazonaws.com/AWSServiceRoleForLakeFormationDataAccess" \
--operations Decrypt DescribeKey GenerateDataKeyThe exact operations and the role ARN path should be checked against the Lake Formation documentation for your account ⚠ VERIFY. If the call is rejected, add the role to the key policy instead. The CDK stack's key policy does not include this statement, which is why the lab shows it separately.
Give yourself back access to the table
By removing IAMAllowedPrincipals you also removed the default way you query the table in Athena. Data lake administrators can grant, but do not automatically read. Grant your SSO role SELECT on the table so you can compare views:
SSO_ROLE_ARN=arn:aws:iam::${ACCOUNT}:role/aws-reserved/sso.amazonaws.com/AWSReservedSSO_<PermissionSet>_<hash>
aws lakeformation grant-permissions --principal DataLakePrincipalIdentifier="$SSO_ROLE_ARN" \
--permissions SELECT DESCRIBE --resource '{"Table":{"DatabaseName":"hl_m07_curated","Name":"transactions"}}'
aws lakeformation grant-permissions --principal DataLakePrincipalIdentifier="$SSO_ROLE_ARN" \
--permissions DESCRIBE --resource '{"Database":{"Name":"hl_m07_curated"}}'Whether Lake Formation needs the path in /aws-reserved/sso.amazonaws.com/ in the principal ARN should be tested in your account ⚠ VERIFY. This grant is yours only: teardown revokes it.
Create a profile for the analyst
Add this to ~/.aws/config (replace the account id):
[profile hl-analyst-uk]
role_arn = arn:aws:iam::111122223333:role/hl-m07-analyst-uk
source_profile = learn-sandbox
region = us-east-1Check who you are: aws sts get-caller-identity --profile hl-analyst-uk should show assumed-role/hl-m07-analyst-uk/....
Part C: prove it from the analyst's session
Define the helper once (it takes a profile as the first argument):
q() {
local p=$1 id state
id=$(aws athena start-query-execution --profile "$p" --work-group hl-m07-analysts --query-string "$2" --query QueryExecutionId --output text) || return
while :; do
state=$(aws athena get-query-execution --profile "$p" --query-execution-id "$id" --query QueryExecution.Status.State --output text)
[ "$state" = QUEUED ] || [ "$state" = RUNNING ] || break
sleep 1
done
echo "[$p] $state"
if [ "$state" = SUCCEEDED ]; then
aws athena get-query-results --profile "$p" --query-execution-id "$id" --output text --query "ResultSet.Rows[].Data[].VarCharValue" | head -20
else
aws athena get-query-execution --profile "$p" --query-execution-id "$id" --query QueryExecution.Status.StateChangeReason --output text
fi
}Rows: the analyst sees only GBP
q learn-sandbox "SELECT currency, count(*) AS n FROM hl_m07_curated.transactions GROUP BY currency"
q hl-analyst-uk "SELECT currency, count(*) AS n FROM hl_m07_curated.transactions GROUP BY currency"You (admin, with your own grant) see both currencies. The analyst sees GBP only, with the same GBP count as yours. The filter is applied inside Athena before the result is formed, so there is no way to GROUP BY or WHERE around it.
Columns: the free-text fields are not there
q hl-analyst-uk "SELECT * FROM hl_m07_curated.transactions LIMIT 3"
q hl-analyst-uk "SELECT reference FROM hl_m07_curated.transactions LIMIT 3"The first query succeeds and returns every column except counterparty_name, reference and source_file. The second fails, because the column does not exist as far as this principal is concerned. The exact message depends on the engine version (a column-not-found or a permission style message), so compare the behaviour, not the wording. The columns the analyst can see include counterparty_iban_token and counterparty_iban_masked: tokens for counting, masks for recognition, never the raw IBAN, which the Glue job removed before the data reached curated.
The bypass check: no direct S3 path
aws s3 ls "s3://hl-m07-curated-${ACCOUNT}/transactions/" --profile hl-analyst-uk
aws iam simulate-principal-policy --policy-source-arn "arn:aws:iam::${ACCOUNT}:role/hl-m07-analyst-uk" \
--action-names s3:GetObject --resource-arns "arn:aws:s3:::hl-m07-curated-${ACCOUNT}/transactions/*" \
--query "EvaluationResults[].EvalDecision" --output textThe ls fails with AccessDenied, and the simulation returns implicitDeny. If the analyst could list and read the Parquet files, the filter would be decoration: they could download the files and read every row and column. This check is the most important one in the lab.
Look at the grants
aws lakeformation list-permissions --resource '{"Table":{"DatabaseName":"hl_m07_curated","Name":"transactions"}}' \
--query "PrincipalResourcePermissions[].{who:Principal.DataLakePrincipalIdentifier,what:Permissions}" --output table
aws lakeformation list-permissions --resource '{"DataCellsFilter":{"TableCatalogId":"'"$ACCOUNT"'","DatabaseName":"hl_m07_curated","TableName":"transactions","Name":"uk_gbp_no_free_text"}}'The table should show your own grant but no IAM_ALLOWED_PRINCIPALS; the filter listing shows the analyst role with SELECT. The governance lesson's C# access-review tool prints the same list in a form an owner can sign.
Part D: console view
- Lake Formation → Data catalog → Tables → transactions → Actions → View permissions. Confirm
IAMAllowedPrincipalsis absent. - Data catalog → Data filters. Open
uk_gbp_no_free_text: row filter, excluded columns. - Administration → Data lake locations. The curated bucket shows registered with the service-linked role.
- Permissions → Data lake permissions, filter by principal
hl-m07-analyst-uk. - CloudTrail → Event history → Event name
GetDataAccess. Each analyst query leaves an event: that is your audit evidence.
Security considerations
⚠ Learning shortcut — not for production
The analyst role trusts the whole account, so any principal there with sts:AssumeRole can use it, and "UK" is approximated by currency = 'GBP'. A real bank grants roles through IAM Identity Center permission sets tied to groups, uses a business attribute (booking entity or region) for the row filter, gives access through an approval workflow with expiry, and keeps data lake administrators in a separate, MFA-protected role.
IAMAllowedPrincipalsis the classic hole. If it is still on the table, IAM alone grants access and your filter does nothing. The setup script revokes it, and the access-review tool warns when it reappears.- No direct S3 for analysts. Lake Formation protects access through integrated engines; it does not stop
s3:GetObjectif IAM allows it. Prefer a bucket policy that denies reads except for the pipeline role and the Lake Formation role. - The results bucket is a side door. Athena writes result sets to S3 and Lake Formation does not govern that location. The analyst role can read results of its own workgroup queries; do not share the workgroup with people who should not see those results, and keep results short-lived (seven days here).
- Views do not inherit cell filters. AWS documents that cell filters defined on a table do not apply to a view over that table. Do not give analysts a view and assume the filter follows.
- The filter is only as good as its column list.
ExcludedColumnNamesis a deny-list: a new free-text column added next month is visible by default. Prefer an include-list or tag-based control (LF-Tags) for columns that matter. - Registration role and KMS. The service-linked role can decrypt the data key, so it is part of the key's "who can use it" review.
Compliance review required
Hiding names and references reduces exposure, but tokens and masks may still be personal data under UK GDPR if they can be linked back to a person. Which analysts may see which columns is a decision for your data-protection officer; Lake Formation enforces the decision.
Common mistakes
- Running setup before the table exists → the filter command fails. Finish the project's Athena step first.
- Forgetting to grant yourself
SELECTafter revokingIAMAllowedPrincipals→ you cannot see the unfiltered table and think the lab is broken. - Skipping the KMS permission for the service-linked role → every analyst query fails with a KMS error.
- Testing as yourself and not as the analyst → administrators are not restricted by the analyst's filter.
- Granting
SELECTon the table instead of on the filter → the analyst sees everything. - Leaving the table in Lake Formation mode after the lab → later labs and
cdk destroybehave differently. Run teardown.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
AccessDeniedException creating the filter or granting | You are not a data lake administrator | Add your SSO role in Lake Formation → Administrative roles and tasks |
Analyst query: Insufficient Lake Formation permission(s) | Missing DESCRIBE on the database, or SELECT not on the filter | Check list-permissions; the principal ARN must match exactly |
Analyst query fails with a KMS AccessDenied | The Lake Formation service-linked role cannot decrypt the key | Create the grant (or key policy statement) in Part B step 2 |
| Analyst gets zero rows although you see GBP rows | Wrong principal, or partition projection and the filter interact badly | Test with a table without projection; check the principal ARN; ⚠ VERIFY behaviour of projected partitions with filters in your account |
| Analyst sees both currencies | IAMAllowedPrincipals still granted, or the analyst has direct S3 access | Re-run the revoke; remove S3 permissions |
Athena asks for DESCRIBE on default | Documented requirement when using Athena with Lake Formation | Grant DESCRIBE on the default database to the analyst role |
assume-role fails | Trust policy or profile misconfigured | Check source_profile and that your SSO session is current |
| Setup says the role already exists | Earlier partial run | Run teardown first (its ` |
Cost and cleanup
./infra/cli/lab75-lake-formation.sh teardownThe script revokes the analyst's grants, deletes the filter, re-grants IAMAllowedPrincipals ALL on the table, deregisters the location and deletes the role and its inline policy. Then remove what the script does not know about:
# Your own grants and the KMS grant (list first, then revoke by id)
aws lakeformation revoke-permissions --principal DataLakePrincipalIdentifier="$SSO_ROLE_ARN" --permissions SELECT DESCRIBE \
--resource '{"Table":{"DatabaseName":"hl_m07_curated","Name":"transactions"}}'
aws lakeformation revoke-permissions --principal DataLakePrincipalIdentifier="$SSO_ROLE_ARN" --permissions DESCRIBE \
--resource '{"Database":{"Name":"hl_m07_curated"}}'
aws kms list-grants --key-id alias/hl-m07-data --query "Grants[].{id:GrantId,to:GranteePrincipal}"
aws kms revoke-grant --key-id alias/hl-m07-data --grant-id <GrantId-from-the-list>
# Remove the [profile hl-analyst-uk] block from ~/.aws/config.
# Check nothing tagged remains
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab \
--query "ResourceTagMappingList[?contains(ResourceARN, 'hl-m07')].ResourceARN"Remove yourself from the data lake administrators only if you added yourself for this lab and no one else relies on it. Do it in the console.
Exercises
- 🟢 Guided: Create
eu_eur_no_free_textand a rolehl-m07-analyst-eu, extend a copy of the script, and prove with one query per role that each sees only its own currency and noreferencecolumn. - 🟡 Challenge: Replace the deny-list of excluded columns with an LF-Tag design: tag
counterparty_name,referenceandsource_fileasclassification=confidential, everything elseinternal, and grant the analyst roleSELECTonclassification = internal. Show that a column you add next month is not visible until someone classifies it. - 🔴 Live problem: An audit finding says "UK analysts can download full transaction files from S3". You see the Lake Formation filter is correct and Athena behaves. List the possible causes in the order you would check them, the evidence for each, and the remediation plan.
Assignment
Add a docs/analyst-access.md to your portfolio repository: your access matrix (audiences, tables, columns, rows), the filter definitions, the five verification queries with their expected results and the teardown evidence (the tag query output).
Solutions
Copy the script's role, policy and grant blocks with hl-m07-analyst-eu and FILTER=eu_eur_no_free_text, a row filter of currency = 'EUR' and the same excluded columns. Create a second CLI profile that assumes the EU role. In Athena, SELECT currency, count(*) FROM ... GROUP BY currency returns only EUR for the EU profile and only GBP for the UK profile, and SELECT reference fails for both. The two filters are separate resources, so granting one never leaks the other.
Create the tag with aws lakeformation create-lf-tag --tag-key classification --tag-values internal confidential, attach confidential to the three columns with add-lf-tags-to-resource on a TableWithColumns resource and internal at table level, then grant SELECT to the role on an LFTagPolicy for classification = internal. Columns with no tag, such as one added next month, inherit the table-level tag in the usual pattern, so classify new sensitive columns at creation time and add a CI check that fails when a column has no explicit classification. A row restriction by currency is not a tag feature, so keep a data cells filter for rows, or separate the entities physically.
Check, in order: (1) IAMAllowedPrincipals still granted on the table; (2) the analyst role or its group has s3:GetObject via a broad managed policy such as read-only S3; (3) a bucket policy with no deny for non-pipeline principals; (4) files copied elsewhere (another bucket, a laptop, an e-mailed export, the Athena results bucket); (5) a view or CTAS that wrote unfiltered data to a location the analysts can read. Evidence: simulate-principal-policy, IAM Access Analyzer findings, CloudTrail data events for the bucket, and the list of principals with access. Remediation: remove direct S3 access, add the deny-except-pipeline bucket policy, revoke IAMAllowedPrincipals, restrict the results bucket, and ask the DPO whether the past exposure needs assessing.
Knowledge check
Answer: It stops IAM permissions alone from granting access, so Lake Formation grants and filters decide who can query it through integrated engines. Everyone, including administrators, then needs an explicit grant to read it.
Answer: With it, the analyst could read the Parquet files directly and bypass every filter. Lake Formation vends temporary credentials to Athena instead.
Answer: It is a deny-list: a column added later is visible by default. An include-list or LF-Tag classification fails closed instead.
Answer: The Lake Formation service-linked role that reads the registered location (and, for results, the analyst role or Athena for the results bucket). The analyst needs no key permission on the curated data itself.
Interview questions
- "How do you stop analysts reading raw files from S3?" — Remove direct S3 permissions from their roles, add a bucket policy denying reads to anyone but the pipeline and Lake Formation roles, and give access only through Lake Formation and Athena.
- "Row filter or separate tables?" — A data cells filter on one table avoids duplicating data and its personal-data footprint; separate tables only when physical separation (jurisdiction, encryption keys) is required.
- "What evidence would an auditor want?" — The grants list, the filter definitions, proof that
IAMAllowedPrincipalsis gone, CloudTrailGetDataAccessevents, and the periodic access review.
Certification practice
A company stores customer transactions in S3 and queries them with Athena. Regional analysts must see only their region's rows and must not see the customer_name column. Which approach meets this with the least duplication?
A. A copy of the table per region in separate buckets.
B. Register the S3 location with Lake Formation, revoke IAMAllowedPrincipals, and grant SELECT on a data cells filter with a row filter and excluded column per region.
C. S3 bucket policies with prefix conditions.
D. Athena views and IAM permissions on the views.
Answer: B. One table, enforced by Lake Formation. A duplicates data, C cannot hide columns inside files, D cannot stop direct table access without Lake Formation.
After enabling Lake Formation data filters, an analyst still reads all columns through Athena. Which two checks should the security engineer make first?
A. Whether IAMAllowedPrincipals still has permissions on the table.
B. Whether the analyst role has s3:GetObject on the data location.
C. Whether the Glue crawler ran today.
D. Whether the bucket is in the same Region as the workgroup.
Answer: A and B. Either leaves an IAM-only path around Lake Formation. C and D do not affect authorisation.
Completion checklist
- I am (temporarily) a data lake administrator and the curated table has data
- I ran setup, granted the Lake Formation role key access and granted myself
SELECT - The analyst sees only GBP rows and no
referencecolumn; I saw the failingSELECT reference - The analyst's
aws s3 lson curated failed and the simulation saysimplicitDeny - I ran teardown, revoked my own grants and the KMS grant, and ran the tag query
Key takeaways
- Filters only work if
IAMAllowedPrincipalsand direct S3 access are gone. - One table and one filter per audience beats copies of the data.
- The Lake Formation service-linked role needs key permission on an SSE-KMS location.
- Test as the restricted principal, and test the bypass paths, not only the happy path.
- A deny-list of columns fails open; classify with tags or use an include-list.
Further reading
Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.
Lab 7.4: a Glue Spark job with job bookmarks and a data-quality quarantine
Run a Glue 5.0 job that reads only new raw files through a job bookmark, evaluates a DQDL ruleset row by row, writes passing rows to partitioned Parquet and failing rows with their broken rules to quarantine, and compare its verdict with the Python validator.
Lab 7.6: near-real-time fraud signals with Kinesis, a .NET Lambda and SNS
Run the velocity and high-amount rules locally first, then deploy a one-shard Kinesis stream, a .NET 10 Lambda and an SNS topic, send a burst of transactions, receive exactly one velocity signal, and destroy everything inside the hour.