Grants are used to control access to the Amazon Redshift database. It allows users to read, select, insert, and drop rows and columns. Grants allow schema privileges on schema, grants help provide permissions and access to data. You can grant access to desired users and desired databases. By doing so, you can secure your confidential data. Amazon Redshift allows us to control who can access the data, whether it is an individual or a group.
We can view grants on the table, schema, database, and even on the column. This article delivers in-depth knowledge for viewing grants on Redshift.
Types of grants in Redshift:
There are many types of grants in Amazon Redshift
- SELECT
- INSERT
- UPDATE
- DELETE
- REFERENCES
- USAGE
- CREATE
- ALTER
- DROP
These grants can be assigned to individual users or groups providing full control over data.
View selected grants on the table in Amazon Redshift
In Amazon, we can view table grants. To view table grants select all the fields for which you should know the exact name of the field. Here, table_name, grantee, and privileges from the information schema where table privileges are defined are selected. In the output, three columns will be displayed with all the records. A hundred records match the query. To view grants our main focus should be on the privilege type.
Code:
# Viewing selected grants on the table in Amazon Redshift
SELECT
table_name,
grantee,
privilege_type
FROM information_schema.table_privileges;
Output:
View all grants on the table in Amazon Redshift.
To view all the fields, you need to select all from the information schema table privileges. In the result, you will see all the fields. It contains all the information regarding tables with the privilege type and grantee. When you don’t remember the field name you can simply run this query it will display all the fields and you can filter your desired fields.
Code:
# Viewing all grants on the table in Amazon Redshift
SELECT * FROM information_schema.table_privileges;
Output:
View grants from usage privileges in Amazon Redshift.
To view object schema with privilege type and other details execute the below statement. Select usage from the information schema. To view only grants, select privilege type and grantee in the query.
Code:
# Viewing grants from usage privileges in Amazon Redshift
SELECT * FROM information_schema.usage_privileges;
Output:
View grants from column privileges in Amazon Redshift.
We can view grants for columns, to view grants for column execute the below query. This will result in a list of records that have column privileges along with other data.
Code:
# Viewing grants from column privileges in Amazon Redshift
SELECT * FROM information_schema.column_privileges;
Output:
View grants on the schema in Amazon Redshift.
To view grants on schema in Redshift select from the namespace and set the namespace name as schema_name then convert the column based on the (,) from array to string. Then store the resulting output in the privileges column.
Code:
# Viewing grants on the schema in Amazon Redshift
SELECT
nspname AS schema_name,
array_to_string(nspacl, ',') AS privileges
FROM pg_namespace;
Output:
View grants on the database in Amazon Redshift.
We can also view grants on a database for that just change the above query and replace nspname with datnam which is the name of the database. Convert the array to a string based on (,) and store it in a column named privileges from pg_database.
Code:
# Viewing grants on the database in Amazon Redshift
SELECT
datname AS database_name,
array_to_string(datacl, ',') AS privileges
FROM pg_database;
Output:
Conclusion:
In a nutshell, we have studied how we can view grants in Amazon Redshift. Grants and permissions help in securing the data from unauthorized access. Looking into the detail of what these grants are and what are the type of grants. In Redshift we can define grants from individual users or groups. We can view grants on table, schema, and database in Redshift. By understanding grants, you can give access to authorized users which promotes the security of your data.