"create protected multi dialect VIEW .... security definer" fails with "Create Table Default Permissions should be empty, either in the database or settings"

Question

Hello

I am trying to create a protected view following https://docs.aws.amazon.com/athena/latest/ug/views-glue.html

I created a simple Athena table backed by an s3 stored csv file.

The table looks fine but when I try e.g.

`CREATE PROTECTED MULTI DIALECT VIEW tenanta.tenantA SECURITY DEFINER 
AS SELECT deviceid,devicename 
FROM basetable
WHERE tenant = 'tenA'`

I get an error:

`Create Table Default Permissions should be empty, either in the database or settings. (Service: AmazonDataCatalog; Status Code: 400; Error Code: AccessDeniedException; Request ID: 88621925-db0f-46bd-a1da-0781a2142fdf; Proxy: null)
This query ran against the ‘default’ database, unless qualified by the query. Please post the error message on our forum  or contact customer support  with Query ID: efcf61b9-70ea-4bd7-8d55-020fba0ba1cd`

I don't know what this means nor to address.

Does anyone have tips?

Tx!!

Peter

Answer

This error states that the database has "Use only IAM access control for new tables in this database" enabled. 
Having this setting enabled, it grants permission to 'IAM_ALLOWED_PRINCIPALS' for any new tables created in the database. Permission granted to 'IAM_ALLOWED_PRINCIPALS' means it uses only IAM permissions (and not LF) to determine access to the resource. When this setting is disabled, this permission is not added and therefore access to the table is determined by both IAM and LF.

For data catalog views to work this setting must be disabled (so that access is determined by LF permissions) and underlying tables referred in view must be registered in lakeformation.

This setting can be found under database in lakeformation.  
On lakeformation console navigate to Databases > Select the database > Edit > uncheck 'Use only IAM access control for new tables in this database'.

"create protected multi dialect VIEW .... security definer" fails with "Create Table Default Permissions should be empty, either in the database or settings"

Relevant content