Unlike most Database services. Redshift has a three tier collection setup:
- The top most tier is called a Database, a Database contains multiple Schemas
- The second tier is called a Schema, a Schema contains multiple Tables
- The final tier is called a Table, a Table contains multiple rows of data or references a location in S3
Database -> Schema -> Table
To create a new user in Redshift, run the following command:
Redshift Spectrum is able to access data in S3 that has been cataloged via AWS Glue by creating an external schema. An external schema acts like a database but instead of holding the data within the redshift cluster it uses an attached IAM role to read the data from S3.
To create an External Schema you will need the name of the AWS Glue Database to be added. An IAM role that can be used to read the data from S3 (A role has been included in the Data Platform) and a schema name that the database will be displayed as.
Execute the following SQL against the Redshift Cluster:
For a user to be able to access an external schema, they must first be granted the temp permission on the containing database. Execute the following SQL against the Redshift Cluster:
Once a user has the temp permission of a specific database, they then need to be granted permissions on each External Schema that they need access to. Run the following commands for each Schema: