Redshift Integration

Integrate with AWS Redshift to handle Privacy requests and perform automated content classification on your data

About the Amazon Redshift Integration 

What it does: 

  • Performs content scanning on clusters in AWS Redshift to detect and map the types of data stored inside it.
  • Redshift integration will scan all authorized clusters for the pre-created specific db user and perform data classification on all accessible tables.
  • Automate Copy and Delete requests for your Redshift tables.

Before setting up this integration:

  • Be sure to add Amazon Redshift to your Inventory. To learn how to add systems to your Inventory, click here.
  • Make sure your MineOS plan supports automatic integrations.
  • Make sure your provided DB User for scanning already exists as part of Redshift.
  • For Privacy Requests handling support - make sure the user has permissions for SELECT and DELETE on the desired Redshift database, and tables in it.

How to set up

On AWS - first, create a role for MineOS to assume

  1. Log into your AWS account
  2. Go to IAM -> Roles -> Create Role
  3. Under Trusted entity type  choose AWS account
  4. Choose Another AWS account and enter the account ID 595379680669
  5. Under Options, mark Require External ID and choose your external ID secret.
    Make sure to remember your external ID for connecting the integration in MineOS later on.

  6. Click Next
  7. Skip the Add Permissions page as we will attach them to the role later 
  8. Click Next
  9. Add your Role Name, Description
  10. Under IAM -> Roles choose the create Role
  11. Under Permissions Policy click Add Permissions -> Create Inline Policy 
  12. Click Json on the upper right side and paste the following policy
  13. Click Next
  14. Name the policy and Create Policy

Make sure to fill in your AccountID and DbUser name inside the JSON.
The current JSON uses a wild flag for regions and clusters, please make sure to keep those for us to scan all clusters.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "DescribeClusters",
            "Effect": "Allow",
            "Action": [
                "redshift:DescribeClusters"
            ],
            "Resource": [
                "*"
            ]
        },
        {
            "Sid": "GetClusterCredentials",
            "Effect": "Allow",
            "Action": [
                "redshift:GetClusterCredentials"
            ],
            "Resource": [
                "arn:aws:redshift:*:{YouAccountID}:dbuser:*/{YouDbUser}"
            ]
        }
    ]
}

Make sure MineOS has access to your Redshift clusters

MineOS IPs should be whitelisted in your VPC security group of choice inbound rules. Connecting via VPN tunneling is also a MineOS available feature if your clusters aren't publicly accessible

MineOS IPs
- 34.77.7.236
- 35.187.97.141
- 34.76.247.90
- 34.140.71.114

On MineOS: 

  1. Head to your Data Inventory and select Redshift
  2. Scroll down to the component titled Request handling
  3. Select Scan this source using Data Classifier
  4. Select Integration as the handling style.
  5. Paste your External ID, Account ID, Role Name, DB User, and Region of choice.
  6.  If successful, click Test & save to enable the integration. 

A connection is considered successful when at least one database has been discovered and successfully accessed.


DSR Queries

To manage your DSR handling actions, you need to define the queries that will run on the Database for each possible action:

Note: The queries you use won't be validated! You should run them in your Redshift account to make sure they are running as expected


Preview Query

The Preview query will be used when opening the ticket processing screen and is responsible for showing how many records were found as well as showing a sample (preview) of the data.

Example Query:

SELECT name,phone,address FROM "dbName"."schema"."table" WHERE email =  '{{endUserEmail}}';

* Preview will return the count of the number of records returned in the query response

* Preview will show the first 3 values from the query response, it supports strings and long types

* The 'endUserEmail' variable is mandatory

 

Copy Query

The Copy query will be used for the Copy action. Copy action is running on the ticket processing page in ticket of type Copy when clicking on Generate Copy

Example Query:

SELECT * FROM "dbName"."schema"."table" WHERE email = {% raw %} '{{endUserEmail}}';

* Copy will show all records returned in the query response

* The 'endUserEmail' variable is mandatory

 

Delete Query

The Delete query will be used for the Delete action. Delete action is running on the ticket processing page in ticket of type Deletion when clicking on Delete from X sources

Example Query:

DELETE FROM "dbName"."schema"."table" WHERE email = '{{endUserEmail}}';

* The 'endUserEmail' variable is mandatory.

Paste the details and queries in the Request handling tab in the correct inputs and click Save.

 

 

Talk to us if you need any help with integrations via our chat or at portal@saymine.com, and we'll be happy to assist!🙂