Secure your database credentials with AWS Secrets Manager and encrypt data with AWS KMS in Amazon QuickSight

Amazon QuickSight is a fully managed, cloud-native business intelligence (BI) service that makes it easy to connect to your data, create interactive dashboards, and share them with tens of thousands of users, either directly within a QuickSight application, or embedded in web apps and portals.

Let’s consider AnyCompany, which owns healthcare facilities across the country. The central IT team of AnyCompany is responsible for setting up and maintaining IT infrastructure and services for all the facilities in each state. Because AnyCompany is in the healthcare industry and holds sensitive data, they want to store their database credentials safely and don’t want to share them with individuals in the reporting or BI teams. Additionally, they need to encrypt their data at rest using their own encryption key instead of service-managed keys to satisfy their regulatory requirements. AnyCompany is able to audit access of their SPICE (the QuickSight robust in-memory calculation engine) datasets. In an unlikely case of a security incident, AnyCompany is in full control to immediately lock down access to their data by universally revoking access to their AWS Key Management Service (AWS KMS) keys. QuickSight is one of the services used by AnyCompany, and central IT needs to be able to set up these security measures.

QuickSight Enterprise Edition now supports storing database credentials in AWS Secrets Manager, a feature that allows you to put these credentials in Secrets Manager and not share with every BI user for data source creation. Secrets Manager is a secret storage service that you can use to protect database credentials, API keys, and other secret information. Using a key helps you ensure that the secret can’t be compromised by someone examining your code, because the secret isn’t stored in the code.

Additionally, QuickSight supports account administrators to use their own customer managed key (CMK) to encrypt and manage datasets in SPICE, through integration with AWS KMS. AWS KMS lets you create, manage, and control cryptographic keys across your applications and more than 100 AWS services. With AWS KMS, you can encrypt data across your AWS workloads, digitally sign data, encrypt within your applications using the AWS Encryption SDK, and generate and verify message authentication codes (MACs). Using a QuickSight SPICE CMK enables QuickSight users to revoke access to SPICE datasets with one click, and maintain an auditable log that tracks how SPICE datasets are accessed.

Both features help increase the level of security and transparency, give you more control over QuickSight, and help satisfy security requirements by company and government agency policies.

In this post, we walk you through the steps to use these features.

Solution overview

To enable both features (storing of database credentials in Secrets Manager and using KMS keys for encryption), we require an administrator of the QuickSight account. In the following sections, we walk you through the high-level steps to implement this solution:

1. Enable Secrets Manager integration from the QuickSight management console.
2. Create or update a data source with secret credentials using the QuickSight API.
3. Create a dataset using the data source you created.
4. Enable KMS keys from the QuickSight management console.
5. Audit CMK usage and dataset access in AWS CloudTrail.
6. Revoke access to CMK-encrypted datasets.

Prerequisites

Make sure you have the following prerequisites:

• A QuickSight subscription with Enterprise Edition
• A secret in Secrets Manager with your database credentials
• KMS keys to encrypt data in SPICE

Enable Secrets Manager integration

With this integration, you no longer need to manually enter data source credentials; you can store them in Secrets Manager and manage access via Secrets Manager. You can also rotate the keys and credentials in one place instead of updating all the data sources. Complete the following steps to enable the integration:

1. Sign in to your QuickSight account.
2. On the user name drop-down menu, choose Manage QuickSight.
   
3. Choose Security & permissions in the navigation pane.
4. Under QuickSight access to AWS services, choose Manage.
   
5. From the list of services, choose Select secrets under AWS Secrets Manager.
   
6. Select the appropriate secret from the list of secrets and choose Finish.
   

QuickSight creates an AWS Identity and Access Management (IAM) role called aws-quicksight-secretsmanager-role-v0 in your account. It grants users in the account read-only access to the specified secrets and looks similar to the following code:

Create a data source with secret credentials using the QuickSight API

At the time of this writing, creation of data sources using the stored secret in Secrets Manager is only available through the CreateDatasource public API.

The following code is an example API call to create a data source in QuickSight. This example uses the create-data-source API operation. You can also use the update-data-source operation to update an existing data source. For more information, see CreateDataSource and UpdateDataSource.

aws quicksight create-data-source --aws-account-id <AccountID> \ --data-source-id <DATASOURCEID> \ --name NAME \ --type MYSQL \ --permissions '[{"Principal": "arn:aws:quicksight:region:accountID:user/namespace/username", "Actions": ["quicksight:DeleteDataSource", "quicksight:DescribeDataSource", "quicksight:DescribeDataSourcePermissions", "quicksight:PassDataSource", "quicksight:UpdateDataSource", "quicksight:UpdateDataSourcePermissions"]}]' \    --data-source-parameters='{"MySQLParameters":{"Database": "database", "Host":"hostURL", "Port":"port"}}' \ --credentials='{"SecretArn":"arn:aws:secretsmanager:region:accountID:secret:secretname"}' \ --region us-west-2

In the preceding call, QuickSight authorizes secretsmanager:GetSecretValue access to the secret based on the API caller’s IAM policy, not the IAM service role’s policy. The IAM service role acts on the account level and is used when an analysis or dashboard is viewed by a user. It can’t be used to authorize secret access when a user creates or updates the data source.

We get the following response:

{
   "Arn": "string",
   "CreationStatus": "string",
   "DataSourceId": "string",
   "RequestId": "string"
}

In the initial response, the creation status is CREATION_IN_PROGRESS. To check if the data source was successfully created, use the DescribeDatasource API to receive a description of the data source:

aws quicksight describe-data-source --aws-account-id <AccountID> \ --data-source-id <DATASOURCEID>

A successful API call returns the data source object that includes status and data source details:

{
   "Status": integer,
   "DataSource":{
      "Arn": "string",
 "DataSourceId": "string",
   	 "Name": "string"
   	 "Type": "string"
   	 "Status": "string"
   	 "CreatedTime": "string"
   	 "LastUpdatedTime": "string"
   	 "DataSourceParameters": {
       }
   	 "VpcConnectionProperties": {
 "VpcConnectionArn":"string"
  }
	 "SslProperties": {
            "DisableSsl": boolean
        },
       "SecretArn": "string"
}
     "RequestId": "string"
}

Create a dataset using the new data source

For instructions on creating a new SPICE dataset using the data source you just created, refer to Creating a dataset using an existing data source.

Enable KMS keys

To enable KMS keys, complete the following steps:

1. On the QuickSight start page, choose Manage QuickSight.
   
2. Choose KMS keys in the navigation pane.
3. Choose Manage.
   
4. On the KMS Keys page, choose Select key.
   
5. In the Select key pop-up box, on the Key menu, choose the key that you want to add.
   

If your key isn’t on the list, you can manually enter the key’s ARN.

6. Choose Use as default encryption key for all new SPICE datasets in this QuickSight account to set the selected key as your default key.

A blue badge appears next to the default key to indicate its status.

When you choose a default key, all new SPICE datasets that are created in the Region that hosts your QuickSight account are encrypted with the default key.

7. Optionally, add more keys by repeating the previous steps.

Although you can add as many keys as you want, you can only have one default key at one time.

8. Optionally, change or remove CMKs by changing or deleting the default key for all new SPICE datasets.

For existing datasets, you need to perform a full refresh after changing or deleting the default key to take effect.

Audit CMK usage and dataset access in CloudTrail

When a key is used (for example, when a CMK-encrypted SPICE dataset is accessed), an audit log is created in CloudTrail. You can use the log to track the key’s usage. For more information, see Logging operations with AWS CloudTrail. If you need to know which key a SPICE dataset is encrypted by, you can find this information in CloudTrail. Complete the following steps:

1. On the CloudTrail console, navigate to your CloudTrail log.
2. Locate the CMK usage (CMK-encrypted SPICE dataset access), using the following search arguments:
   1. The event name (eventName) is GenerateDataKey or Decrypt.
   2. The eventTime denotes when the CMK is used (a CMK-encrypted SPICE dataset is accessed).
   3. The request parameters (requestParameters) contain the QuickSight ARN for the dataset.
   4. The request parameters (requestParameters) contain the KMS ARN (keyId) of the CMK.

See the following code:

{
    "eventVersion": "1.08",
    "userIdentity": {
        "type": "AWSService",
        "invokedBy": "quicksight.amazonaws.com"
    },
    "eventTime": "2022-10-26T00:06:06Z",
    "eventSource": "kms.amazonaws.com",
    "eventName": "Decrypt",
    "awsRegion": "us-west-2",
    "sourceIPAddress": "quicksight.amazonaws.com",
    "userAgent": "quicksight.amazonaws.com",
    "requestParameters": {
        "constraints": {
            "encryptionContextSubset": {
                "aws:quicksight:arn": "arn:aws:quicksight:us-west-2:111122223333:dataset/12345678-1234-1234-1234-123456789012"
            }
        },
        "keyId": "arn:aws:kms:us-west-2:111122223333:key/87654321-4321-4321-4321-210987654321",
        "encryptionAlgorithm": "SYMMETRIC_DEFAULT"
    },
....
}

Now we can verify the CMK that’s currently used by a SPICE dataset.

1. In your CloudTrail log, locate the most recent grant events for the SPICE dataset using the following search arguments:
   1. The event name (eventName) contains Grant.
   2. The request parameters (requestParameters) contain the QuickSight ARN for the dataset.

See the following code:

{
    "eventVersion": "1.08",
    "userIdentity": {
        "type": "AWSService",
        "invokedBy": "quicksight.amazonaws.com"
    },
    "eventTime": "2022-10-26T00:11:08Z",
    "eventSource": "kms.amazonaws.com",
    "eventName": "CreateGrant",
    "awsRegion": "us-west-2",
    "sourceIPAddress": "quicksight.amazonaws.com",
    "userAgent": "quicksight.amazonaws.com",
    "requestParameters": {
        "constraints": {
            "encryptionContextSubset": {
                "aws:quicksight:arn": "arn:aws:quicksight:us-west-2:111122223333:dataset/12345678-1234-1234-1234-123456789012"
            }
        },
        "retiringPrincipal": "quicksight.amazonaws.com",
        "keyId": "arn:aws:kms:us-west-2:111122223333:key/87654321-4321-4321-4321-210987654321",
        "granteePrincipal": "quicksight.amazonaws.com",
        "operations": [
            "Encrypt",
            "Decrypt",
            "DescribeKey",
            "GenerateDataKey"
        ]
    },
....
}

Depending on the event type, one of the following applies:

• CreateGrant – You can find the most recently used CMK in the key ID (keyID) for the last CreateGrant event for the SPICE dataset
• RetireGrant – If latest CloudTrail event of the SPICE dataset is RetireGrant, there is no key ID and the SPICE dataset is no longer CMK encrypted

Revoke access to CMK-encrypted datasets

You can revoke access to your CMK-encrypted SPICE datasets. When you revoke access to a key that is used to encrypt a dataset, access to the dataset is denied until you undo the revoke. The following method is one example of how you can revoke access:

1. On the AWS KMS console, choose Customer managed keys in the navigation pane.
2. Select the key that you want to turn off.
3. On the Key actions menu, choose Disable.

After you revoke access by using any method, it can take up to 15 minutes for the SPICE dataset to become inaccessible.

Sample implementation

The following code shows a sample CreateDatasource API call for creating a QuickSight data source:

aws quicksight create-data-source --aws-account-id <AccountID> --data-source-id hospitaldataASM --name hospipataldataASM --type POSTGRESQL --credentials={\"SecretArn\":\"arn:aws:secretsmanager:us-east-1:<AccountiD>:secret:<SecretID>\"} --data-source-parameters={\"PostgreSqlParameters\":{\"Database\":\"postgres\",\"Host\":\"xxxx.xxxxxx.us-east-1.rds.amazonaws.com\",\"Port\":5432}} --vpc-connection-properties={\"VpcConnectionArn\":\"arn:aws:quicksight:us-east-1:<AccountID>:vpcConnection/<vpcConnectionName>\"} --permissions="Principal=arn:aws:quicksight:us-east-1:380249061054:user/default/<username>,Actions=quicksight:DescribeDataSource,quicksight:DescribeDataSourcePermissions,quicksight:PassDataSource,quicksight:UpdateDataSourcePermissions,quicksight:DeleteDataSource,quicksight:UpdateDataSource"

We get the following response:

To monitor the status of the new data source, run the DescribeDataSource API:

aws quicksight describe-data-source --aws-account-id <AccountID> \ --data-source-id hospitaldataASM

We get the following response:

To validate the KMS keys used, navigate to CloudTrail logs, as shown in the following code:

Finally, audit the CMK usage (dataset access) via CloudTrail logs. And in the unlikely case of a security incident, access to data can be locked down universally by revoking access to the KMS keys.

Clean up

Clean up the resources created as part of this post with the following steps:

1. To remove the Secrets Manager integration, update the data source with regular service-level credentials.
2. Remove the secret from the QuickSight admin console.
3. On the QuickSight start page, choose Manage QuickSight.
4. Choose KMS keys in the navigation pane.
5. Choose Manage.
6. Choose the Actions menu (three dots) on the row of the default key, then choose Delete.
7. In the pop-up box that appears, choose Remove.

Conclusion

This post showcased the new features released in QuickSight to secure database credentials through integration with Secrets Manager and AWS KMS. We also demonstrated how to set up customer managed keys to enable encryption of data at rest in QuickSight SPICE, track key usage history using CloudTrail, and lock down access to data by revoking access to KMS keys.

Try out QuickSight support for Secrets Manager and AWS KMS integration to secure your credentials and data with QuickSight, and share your feedback and questions in the comments. For more information, refer to Key management and Using AWS Secrets Manager secrets instead of database credentials in Amazon QuickSight.

Join the Quicksight Community to ask, answer and learn with others and explore additional resources.

About the authors

Srikanth Baheti is a Specialized World Wide Sr. Solution Architect for Amazon QuickSight. He started his career as a consultant and worked for multiple private and government organizations. Later he worked for PerkinElmer Health and Sciences & eResearch Technology Inc, where he was responsible for designing and developing high traffic web applications, highly scalable and maintainable data pipelines for reporting platforms using AWS services and Serverless computing.

Raji Sivasubramaniam is a Sr. Solutions Architect at AWS, focusing on Analytics. Raji is specialized in architecting end-to-end Enterprise Data Management, Business Intelligence and Analytics solutions for Fortune 500 and Fortune 100 companies across the globe. She has in-depth experience in integrated healthcare data and analytics with wide variety of healthcare datasets including managed market, physician targeting and patient analytics.