Amazon Redshift Module 7: Security, Data Sharing, and Integrations

Step 1: Manage users and roles

Create database users or, better, use IAM identities or your identity provider through federation so people do not share passwords. Group permissions into roles, grant roles to users, and grant the minimum on schemas and tables. Avoid using the admin user for daily work.

CREATE ROLE analyst;
GRANT USAGE ON SCHEMA public TO ROLE analyst;
GRANT SELECT ON TABLE fact_sales, dim_customer, dim_product, dim_date TO ROLE analyst;

CREATE USER priya PASSWORD 'Use-A-Strong-Password-1';
GRANT ROLE analyst TO priya;

Step 2: Restrict columns and rows

Column-level grants hide sensitive columns, and row-level security policies limit which rows a role can see. Dynamic data masking can hide or partially reveal values for some roles. Use these to give analysts access without exposing personal data.

-- column-level: grant everything except a sensitive column
GRANT SELECT (customer_id, city, signup_date) ON dim_customer TO ROLE analyst;

-- row-level security example
CREATE RLS POLICY region_policy
WITH (city varchar(60))
USING (city = 'Pune');
ATTACH RLS POLICY region_policy ON dim_customer TO ROLE analyst;
ALTER TABLE dim_customer ROW LEVEL SECURITY ON;

Step 3: Encrypt and isolate the network

Redshift encrypts data at rest with AWS KMS keys and supports SSL for connections in transit. Run the warehouse in a VPC, keep it private unless you need public access, restrict security groups to known addresses, and turn on audit logging to S3 or CloudWatch so you can see who ran what.

Step 4: Share live data with datashares

A datashare lets a producer warehouse give consumers read access to live schemas, tables, and views without copying the data, even across accounts. Consumers query it as if it were local, and the producer stays the single source of truth. It is a clean way to give another team or department the data without building a pipeline.

-- on the producer
CREATE DATASHARE sales_share;
ALTER DATASHARE sales_share ADD SCHEMA public;
ALTER DATASHARE sales_share ADD TABLE public.fact_sales;
GRANT USAGE ON DATASHARE sales_share TO NAMESPACE '<consumer-namespace-id>';

-- on the consumer
CREATE DATABASE sales_shared FROM DATASHARE sales_share OF NAMESPACE '<producer-namespace-id>';
SELECT count(*) FROM sales_shared.public.fact_sales;

Step 5: Know the zero-ETL and other integrations

Zero-ETL integrations replicate data continuously from supported sources, such as Amazon Aurora and Amazon RDS, into Redshift so you can analyze operational data almost in real time without building an ETL pipeline. Redshift also integrates with Amazon SageMaker for machine learning, streaming ingestion from Kinesis and MSK, and BI tools such as Amazon QuickSight, Tableau, and Power BI. Check the current list of supported sources and Regions.