:rocket: Add database management to the Control Panel via Lake Formation

michaeljcollinsuk commented 4 months ago

User Story

As a data engineer I want to grant other users access to my databases in the Control Panel So that access is visible and easy to maintain

Value / Purpose

We are currently working with the DPR team to share their databases with the AP. Once shared, we need to grant AP users access. Doing this in Lake Formation has benefits to the existing method of managing IAM roles via data-engineering-database-access.

We have also had a feature request for this from our users https://github.com/ministryofjustice/analytical-platform/issues/4352

Useful Contacts

@michaeljcollinsuk, @jamesstottmoj

User Types

No response

Hypothesis

If we... add Lake Formation to Control Panel Then... database access management will be improved

Proposal

Integrate Lake Formation with the Control Panel so that users can manage database access themselves.

Initial scope should be limited to:

Only allow access management of databases that are already registered with Lake Formation
Use the named resource method for granting access
Only enable the feature for privileged users - AP superusers and select number of test users
Use the work with DPR as an initial test case (ADD LINK TO RELATED TICKET)

Additional Information

Definition of Done

[x] Privileged users can see databases and tables in the AP via the Control Panel
[x] Privileged users can grant access to databases/tables registered in Lake Formation via the Control Panel
[x] Privileged users can revoke access to databases/tables registered in Lake Formation via the Control Panel
[x] Superusers can grant other users access to the database management feature
[x] The feature has been tested with the databases that are being shared with the AP as part of DPR work

jamesstottmoj commented 4 months ago

Implementation made

Super user can apply toggle to user that will allow them database management access
User can apply/revoke permissions on a table that has been registered with lake formation (tables that are not registered with lake formation cannot have user permissions assigned via control panel)

Unit tests written

jamesstottmoj commented 4 months ago

This ticket needs testing in development. The plan is to take the PR build and put it in dev and see what permissions are missing.

jamesstottmoj commented 4 months ago

Merged to main. Going to put into development to test.

jamesstottmoj commented 3 months ago

Changes now in development. Experimented to find appropriate permissions to apply to the control panel role in order to apply permissions on users. They've been applied via AP Terraform

jamesstottmoj commented 3 months ago

UI needs some tweaks to make information presentation clearer. Blocked as we need a database and shared tables from DPR into AP account.

michaeljcollinsuk commented 2 months ago

Deployed to prod for testing with pre-prod data. It is using the curated_prisons_history_preprod_dbt database and tables, via a resource link.

michaeljcollinsuk commented 1 month ago

Follow on tickets may need to be raised once further testing has been completed, but the DOD for this ticket is now complete and has been deployed to production.

ministryofjustice / analytical-platform