After having setup a new federated source Federated, I have created a virtual view on a federated query in a second database my_database where I use the data.
With my admin account, I was able to perform both a query on the federated database, and on the federated view.
(Admin: query-1 ✔️, query-2 ✔️)
query-1
select * from Federated.public.my_table;
query-2
create view my_database.my_view as
select * from Federated.public.my_table
select * my_database.from my_view
With an analyst AWS account with less permissions, I configured the IAM as recommended in the step 1 of Readme, but they are unable to query the federated view and get the following error, even though they can query the existing views (VIRTUAL_VIEW) and tables (EXTERNAL_TABLE) from the database and perform the direct query on the federated database.
(Analyst: query-1 ✔️, query-2 ❌)
https://docs.aws.amazon.com/athena/latest/ug/federated-query-iam-access.html
Insufficient permissions to execute the query.
This query ran against the `my_database` database, unless qualified by the query.
What's more, analysts can't recreate the view themselves,
create view my_database.analyst_view as
select * from Federated.public.my_table
-- Error: Schema 'public' does not exist
As I understand creating a federated view creates a new VIRTUAL_VIEW in the Glue catalog. In Lake Formation I already granted "Super" access the the whole my_database and tables.
After having setup a new federated source
Federated
, I have created a virtual view on a federated query in a second databasemy_database
where I use the data.With my admin account, I was able to perform both a query on the federated database, and on the federated view. (Admin: query-1 ✔️, query-2 ✔️)
query-1
query-2
With an analyst AWS account with less permissions, I configured the IAM as recommended in the step 1 of Readme, but they are unable to query the federated view and get the following error, even though they can query the existing views (
VIRTUAL_VIEW
) and tables (EXTERNAL_TABLE
) from the database and perform the direct query on the federated database. (Analyst: query-1 ✔️, query-2 ❌) https://docs.aws.amazon.com/athena/latest/ug/federated-query-iam-access.htmlWhat's more, analysts can't recreate the view themselves,
As I understand creating a federated view creates a new
VIRTUAL_VIEW
in the Glue catalog. In Lake Formation I already granted "Super" access the the wholemy_database
and tables.Do I have to set more permissions?
Thank you very much.