Safe your database credentials with AWS Secrets and techniques Supervisor and encrypt knowledge with AWS KMS in Amazon QuickSight

Amazon QuickSight is a totally managed, cloud-native enterprise intelligence (BI) service that makes it simple to connect with your knowledge, create interactive dashboards, and share them with tens of 1000’s of customers, both instantly inside a QuickSight software, or embedded in net apps and portals.

Let’s think about AnyCompany, which owns healthcare amenities throughout the nation. The central IT crew of AnyCompany is liable for establishing and sustaining IT infrastructure and providers for all of the amenities in every state. As a result of AnyCompany is within the healthcare trade and holds delicate knowledge, they need to retailer their database credentials safely and don’t need to share them with people within the reporting or BI groups. Moreover, they should encrypt their knowledge at relaxation utilizing their very own encryption key as a substitute of service-managed keys to fulfill their regulatory necessities. AnyCompany is ready to audit entry of their SPICE (the QuickSight strong in-memory calculation engine) datasets. In an unlikely case of a safety incident, AnyCompany is in full management to right away lock down entry to their knowledge by universally revoking entry to their AWS Key Administration Service (AWS KMS) keys. QuickSight is among the providers utilized by AnyCompany, and central IT wants to have the ability to arrange these safety measures.

QuickSight Enterprise Version now helps storing database credentials in AWS Secrets and techniques Supervisor, a function that means that you can put these credentials in Secrets and techniques Supervisor and never share with each BI person for knowledge supply creation. Secrets and techniques Supervisor is a secret storage service that you should utilize to guard database credentials, API keys, and different secret data. Utilizing a key helps you make sure that the key can’t be compromised by somebody inspecting your code, as a result of the key isn’t saved within the code.

Moreover, QuickSight helps account directors to make use of their very own buyer managed key (CMK) to encrypt and handle datasets in SPICE, by way of integration with AWS KMS. AWS KMS allows you to create, handle, and management cryptographic keys throughout your functions and greater than 100 AWS providers. With AWS KMS, you possibly can encrypt knowledge throughout your AWS workloads, digitally signal knowledge, encrypt inside your functions utilizing the AWS Encryption SDK, and generate and confirm message authentication codes (MACs). Utilizing a QuickSight SPICE CMK allows QuickSight customers to revoke entry to SPICE datasets with one click on, and preserve an auditable log that tracks how SPICE datasets are accessed.

Each options assist enhance the extent of safety and transparency, offer you extra management over QuickSight, and assist fulfill safety necessities by firm and authorities company insurance policies.

On this publish, we stroll you thru the steps to make use of these options.

Resolution overview

To allow each options (storing of database credentials in Secrets and techniques Supervisor and utilizing KMS keys for encryption), we require an administrator of the QuickSight account. Within the following sections, we stroll you thru the high-level steps to implement this resolution:

Allow Secrets and techniques Supervisor integration from the QuickSight administration console.
Create or replace an information supply with secret credentials utilizing the QuickSight API.
Create a dataset utilizing the information supply you created.
Allow KMS keys from the QuickSight administration console.
Audit CMK utilization and dataset entry in AWS CloudTrail.
Revoke entry to CMK-encrypted datasets.

Conditions

Be sure you have the next stipulations:

A QuickSight subscription with Enterprise Version
A secret in Secrets and techniques Supervisor along with your database credentials
KMS keys to encrypt knowledge in SPICE

Allow Secrets and techniques Supervisor integration

With this integration, you not have to manually enter knowledge supply credentials; you possibly can retailer them in Secrets and techniques Supervisor and handle entry through Secrets and techniques Supervisor. You can too rotate the keys and credentials in a single place as a substitute of updating all the information sources. Full the next steps to allow the mixing:

Register to your QuickSight account.
On the person title drop-down menu, select Handle QuickSight.
Select Safety & permissions within the navigation pane.
Beneath QuickSight entry to AWS providers, select Handle.
From the checklist of providers, select Choose secrets and techniques below AWS Secrets and techniques Supervisor.
Choose the suitable secret from the checklist of secrets and techniques and select End.

QuickSight creates an AWS Identification and Entry Administration (IAM) function known as aws-quicksight-secretsmanager-role-v0 in your account. It grants customers within the account read-only entry to the required secrets and techniques and appears much like the next code:

Create an information supply with secret credentials utilizing the QuickSight API

On the time of this writing, creation of knowledge sources utilizing the saved secret in Secrets and techniques Supervisor is barely out there by way of the CreateDatasource public API.

The next code is an instance API name to create an information supply in QuickSight. This instance makes use of the create-data-source API operation. You can too use the update-data-source operation to replace an present knowledge supply. For extra data, see CreateDataSource and UpdateDataSource.

aws quicksight create-data-source --aws-account-id AWSACCOUNTID  --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:area:accountID:secret:secretname"}"  --region us-west-2

Within the previous name, QuickSight authorizes secretsmanager:GetSecretValue entry to the key based mostly on the API caller’s IAM coverage, not the IAM service function’s coverage. The IAM service function acts on the account stage and is used when an evaluation or dashboard is seen by a person. It may’t be used to authorize secret entry when a person creates or updates the information supply.

We get the next response:

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

Within the preliminary response, the creation standing is CREATION_IN_PROGRESS. To test if the information supply was efficiently created, use the DescribeDatasource API to obtain an outline of the information supply:

aws quicksight describe-data-source --aws-account-id AWSACCOUNTID  --data-source-id DATASOURCEID

A profitable API name returns the information supply object that features standing and knowledge supply particulars:

{
   "Standing": integer,
   "DataSource":{
      "Arn": "string",
 "DataSourceId": "string",
   	 "Title": "string"
   	 "Sort": "string"
   	 "Standing": "string"
   	 "CreatedTime": "string"
   	 "LastUpdatedTime": "string"
   	 "DataSourceParameters": {
       }
   	 "VpcConnectionProperties": {
 "VpcConnectionArn":"string"
  }
	 "SslProperties": {
            "DisableSsl": boolean
        },
       "SecretArn": "string"
}
     "RequestId": "string"
}

Create a dataset utilizing the brand new knowledge supply

For directions on creating a brand new SPICE dataset utilizing the information supply you simply created, consult with Making a dataset utilizing an present knowledge supply.

Allow KMS keys

To allow KMS keys, full the next steps:

On the QuickSight begin web page, select Handle QuickSight.
Select KMS keys within the navigation pane.
Select Handle.
On the KMS Keys web page, select Choose key.
Within the Choose key pop-up field, on the Key menu, select the important thing that you just need to add.

In case your key isn’t on the checklist, you possibly can manually enter the important thing’s ARN.

Select Use as default encryption key for all new SPICE datasets on this QuickSight account to set the chosen key as your default key.

A blue badge seems subsequent to the default key to point its standing.

Whenever you select a default key, all new SPICE datasets which might be created within the Area that hosts your QuickSight account are encrypted with the default key.

Optionally, add extra keys by repeating the earlier steps.

Though you possibly can add as many keys as you need, you possibly can solely have one default key at one time.

Optionally, change or take away CMKs by altering or deleting the default key for all new SPICE datasets.

For present datasets, you must carry out a full refresh after altering or deleting the default key to take impact.

Audit CMK utilization and dataset entry in CloudTrail

When a key’s used (for instance, when a CMK-encrypted SPICE dataset is accessed), an audit log is created in CloudTrail. You should utilize the log to trace the important thing’s utilization. For extra data, see Logging operations with AWS CloudTrail. If you must know which key a SPICE dataset is encrypted by, you’ll find this data in CloudTrail. Full the next steps:

On the CloudTrail console, navigate to your CloudTrail log.
Find the CMK utilization (CMK-encrypted SPICE dataset entry), utilizing the next search arguments:
1. The occasion title (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) comprise the QuickSight ARN for the dataset.
4. The request parameters (requestParameters) comprise the KMS ARN (keyId) of the CMK.

See the next code:

{
    "eventVersion": "1.08",
    "userIdentity": {
        "sort": "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 will confirm the CMK that’s at present utilized by a SPICE dataset.

In your CloudTrail log, find the latest grant occasions for the SPICE dataset utilizing the next search arguments:
1. The occasion title (eventName) accommodates Grant.
2. The request parameters (requestParameters) comprise the QuickSight ARN for the dataset.

See the next code:

{
    "eventVersion": "1.08",
    "userIdentity": {
        "sort": "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"
        ]
    },
....
}

Relying on the occasion sort, one of many following applies:

CreateGrant – You will discover essentially the most lately used CMK in the important thing ID (keyID) for the final CreateGrant occasion for the SPICE dataset
RetireGrant – If newest CloudTrail occasion of the SPICE dataset is RetireGrant, there isn’t any key ID and the SPICE dataset is not CMK encrypted

Revoke entry to CMK-encrypted datasets

You’ll be able to revoke entry to your CMK-encrypted SPICE datasets. Whenever you revoke entry to a key that’s used to encrypt a dataset, entry to the dataset is denied till you undo the revoke. The next technique is one instance of how one can revoke entry:

On the AWS KMS console, select Buyer managed keys within the navigation pane.
Choose the important thing that you just need to flip off.
On the Key actions menu, select Disable.

After you revoke entry through the use of any technique, it could possibly take as much as quarter-hour for the SPICE dataset to change into inaccessible.

Pattern implementation

The next code exhibits a pattern CreateDatasource API name for making a QuickSight knowledge supply:

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:person/default/<username>,Actions=quicksight:DescribeDataSource,quicksight:DescribeDataSourcePermissions,quicksight:PassDataSource,quicksight:UpdateDataSourcePermissions,quicksight:DeleteDataSource,quicksight:UpdateDataSource"

We get the next response:

To watch the standing of the brand new knowledge supply, run the DescribeDataSource API:

aws quicksight describe-data-source --aws-account-id <AccountId>      --data-source-id hospitaldataASM

aws quicksight describe-data-source –aws-account-id <AccountId> –data-source-id hospitaldataASM

We get the next response:

To validate the KMS keys used, navigate to CloudTrail logs, as proven within the following code:

Lastly, audit the CMK utilization (dataset entry) through CloudTrail logs. And within the unlikely case of a safety incident, entry to knowledge could be locked down universally by revoking entry to the KMS keys.

Clear up

Clear up the sources created as a part of this publish with the next steps:

To take away the Secrets and techniques Supervisor integration, replace the information supply with common service-level credentials.
Take away the key from the QuickSight admin console.
On the QuickSight begin web page, select Handle QuickSight.
Select KMS keys within the navigation pane.
Select Handle.
Select the Actions menu (three dots) on the row of the default key, then select Delete.
Within the pop-up field that seems, select Take away.

Conclusion

This publish showcased the brand new options launched in QuickSight to safe database credentials by way of integration with Secrets and techniques Supervisor and AWS KMS. We additionally demonstrated the way to arrange buyer managed keys to allow encryption of knowledge at relaxation in QuickSight SPICE, observe key utilization historical past utilizing CloudTrail, and lock down entry to knowledge by revoking entry to KMS keys.

Check out QuickSight assist for Secrets and techniques Supervisor and AWS KMS integration to safe your credentials and knowledge with QuickSight, and share your suggestions and questions within the feedback. For extra data, consult with Key administration and Utilizing AWS Secrets and techniques Supervisor secrets and techniques as a substitute of database credentials in Amazon QuickSight.

Concerning the authors

Srikanth Baheti is a Specialised World Large Sr. Resolution Architect for Amazon QuickSight. He began his profession as a advisor and labored for a number of non-public and authorities organizations. Later he labored for PerkinElmer Well being and Sciences & eResearch Know-how Inc, the place he was liable for designing and creating excessive site visitors net functions, extremely scalable and maintainable knowledge pipelines for reporting platforms utilizing AWS providers and Serverless computing.

Raji Sivasubramaniam is a Sr. Options Architect at AWS, specializing in Analytics. Raji is specialised in architecting end-to-end Enterprise Information Administration, Enterprise Intelligence and Analytics options for Fortune 500 and Fortune 100 firms throughout the globe. She has in-depth expertise in built-in healthcare knowledge and analytics with extensive number of healthcare datasets together with managed market, doctor focusing on and affected person analytics.