Why can't I edit CID DataSets

Question & Answer
, , ,
1

I recently installed the CID dashboards in our environment. The Datasets are refreshing and the dashboards are showing the data properly. I made the change to add a tag to the summary_view view in Athena based on this video… https://youtu.be/Yc64XsDo30M?si=kwflEDoxdfte8Zvj However I am NOT able to edit or view the summary_view DataSet in QuickSight. FIrst few times I got an Access Denied error for athena:ListDatabases. THe QuickSIght Role specifically gives the role the athena:ListDatabases to the cid_cur databases.
Now I am getting a different error saying “Can’t perform this action
Query table not found. Verify the table in the dataset or custom SQL and try again.” Which is garbage since I can clearly see the table in Athena and the dashboards are displaying the data. I cannot verify the table in the dataset because it won’t display it.

Anyone know what is going on here and how to fix this?

Brian

2

So I decided to delete the CFN Stack and redeploy again. First off is the QuickSIghtRole does not even include the CID Bucket in the S3 policy so right off the bat the Datasets fail to refresh. I had to manaully add the CID bucket to the policy. THe only bucket it listed was the CostOptimization bucket.

However I still cannot edit of preview any data in the CID QuickSight data sets. I get these messages…

Can’t perform this action

You don’t have sufficient permissions to connect to this dataset or run this query. Contact your administrator for assistance.

Error details

region: us-east-1
timestamp: 1697576968294
requestId: 42410e65-ceee-4951-8f21-812b9ead7dca
sourceErrorCode: 100071
sourceErrorMessage: [Simba]AthenaJDBC An error has been thrown from the AWS Athena client. You are not authorized to perform: athena:ListDatabases on the resource. After your AWS administrator or you have updated your permissions, please try again. [Execution ID not available]
sourceErrorState: HY000
sourceException: java.sql.SQLException
sourceType: ATHENA
3

hi @Brian_P_Schwind

would you be able to confirm if QuickSightRole has Athena access permitted from QS Management console?

image
image1184×1148 112 KB

kind regards,
Wakana

1 Like
4

Sure can…

image
image769×712 34.8 KB

ALso here is the policy on the CidQuickSightDataSourceRole role…

{
“Version”: “2012-10-17”,
“Statement”: [
{
“Action”: [
“athena:ListDataCatalogs”
],
“Resource”: “",
“Effect”: “Allow”
},
{
“Action”: [
“athena:ListDatabases”
],
“Resource”: [
"arn:aws:athena:us-east-1:12:database/cid_cur"
],
“Effect”: “Allow”
},
{
“Action”: [
“glue:GetDatabases”
],
“Resource”: [
"arn:aws:glue:us-east-1:12:catalog"
],
“Effect”: “Allow”
},
{
“Action”: [
“glue:GetTable”,
“glue:GetPartitions”
],
“Resource”: [
"arn:aws:glue:us-east-1:12:catalog",
"arn:aws:glue:us-east-1:12:database/cid_cur",
"arn:aws:glue:us-east-1:12:table/cid_cur/"
],
“Effect”: “Allow”
},
{
“Action”: [
“athena:GetQueryExecution”,
“athena:StartQueryExecution”,
“athena:GetQueryResultsStream”,
“athena:GetQueryResults”
],
“Resource”: "arn:aws:athena:us-east-1:12:workgroup/CID",
“Effect”: “Allow”
},
{
“Action”: [
“s3:GetBucketLocation”,
“s3:ListBucket”
],
“Resource”: "arn:aws:s3:::aws-athena-query-results-cid-12-us-east-1",
“Effect”: “Allow”
},
{
“Action”: [
“s3:GetObject”,
“s3:PutObject”
],
“Resource”: "arn:aws:s3:::aws-athena-query-results-cid-12-us-east-1/",
“Effect”: “Allow”
},
{
“Action”: [
“lakeformation:GetDataAccess”
],
“Resource”: "”,
“Effect”: “Allow”
}
]
}

5

Hello @Brian_P_Schwind, is this an issue you are still facing in QuickSight? When you go to create a dataset from the Athena source you mentioned, are you attempting to import a table/view directly, or are you using SQL to query the Athena view and build out your dataset manually?

Otherwise, if you were able to find a solution, do you mind replying with the steps you took to correct this?

1 Like
6

Well…I had opened an AWS Support Case(we have enterprise support agreement) for this and AWS still could not find a resolution to this issue. :frowning:
I finally gave up and gave the role we were using full admin(allow, :) privs. That allowed me to do what I needed to do granted it is not the most secure way. My thought was I would cirlce back someday and try to figure which actions it was missing. AWS wasn’t able to figure it out though LOL!

Brian

7

Hello @Brian_P_Schwind, I completely understand the hesitancy to want to use full admin privileges. I have looked through the policy that you posted above and also cannot find what is missing. For now, I will mark this ticket as a bug since you have already communicated with AWS support and archive this topic.

I will research this a bit further and see if I can find any information. If I have any luck finding a solution, I will message you directly so we can get this resolved. Thank you for your patience and I am glad you at least found a work-around for the time being.

10

Hello @Brian_P_Schwind, I was able to figure out the problem and I think this could be helpful for other users so I am going to post my response here instead.

In this section of your policy:

{
“Action”: [
“athena:ListDatabases”
],
“Resource”: [
"arn:aws:athena:us-east-1:12:database/cid_cur"
],
“Effect”: “Allow”
},

You are using the action ListDatabases but only given access to a single database so it is throwing an error. You need to give access to all databases here in the Resource section so that it will run without Full Admin Allow Privileges. That should give you the solution you are looking for.

1 Like
11

Awesome…thanks a bunch!

1 Like