How can I limit user to specific queries and prevent export in Athena?

Question

I want to use Athena to allow users to troubleshoot and find specific data. I'm storing sensitive information, so I'd like to limit the user's queries against the tables. For example, the user can search for a specific field value, but they can only return 100 records at a time. I also would like to prevent exporting the data, but I don't see an appropriate policy to prevent this.

Saved parameterized queries and prepared queries seem to offer a way to do this, but I don't see a way to *only* limit the user to these saved queries. I also don't want them to be able to write ad-hoc queries that can get at more data than the saved or prepared queries allow.

I see the "per query data usage control" setting in the Workgroup to limit the data per query, but the minimum I can set this to is 10 MB, which is larger than I'd like to set it.

In short, without creating a middle-layer API, is there a way to lock down the Athena console to prevent users from bulk-querying all the data and limit the user to pre-saved queries?

Answer

In short to my knowledge you cannot lockdown the console for returning only 100 records

You can definitely though use [LakeFormation](https://docs.aws.amazon.com/lake-formation/latest/dg/what-is-lake-formation.html) to do granular permissions on your data for certain users to access certain fields

A simple approach instead of a middle api could be to front athena with [step function](https://docs.aws.amazon.com/step-functions/latest/dg/connect-athena.html) console and remove users access to athena console but step functions console isn't exactly for non technical users

How can I limit user to specific queries and prevent export in Athena?

Relevant content