🚀 Add database management to the Control Panel via Lake Formation

[michaeljcollinsuk]

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 #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

See related feature request #4352

Definition of Done

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

[jamesstottmoj]

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]

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]

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

[jamesstottmoj]

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]

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

[michaeljcollinsuk]

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]

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.