Updated March 14, 2023
Introduction to RedShift GRANT
Redshift GRANT command is used to control the security and access to the database and its objects for users and groups of users in Amazon Redshift. We can specify the options inside the command as for reading or writing the data from and to the database, tables, columns, schema, procedures, functions or language. These privileges can also be given for access to the creation of tables or views, write the data or read the data from them, and even drop the tables. The opposite working of the GRANT command is the revoke command which can remove the assigned permissions from a group of users and user.
How does Grant Command Works?
The GRANT command can be used to assign any kind of privilege of operation on any of the objects of the current database. Other than this, it can also assign the permissions to the entities located externally to the database to users and user groups that have ON SCHEMA keywords specified in their syntax. Other than this, the GRANT can only assign the privilege of EXECUTE to the stored procedures. One more important thing to keep in mind is that GRANT privilege cannot be used for assigning the permissions to other external objects of the database and the transaction block, which start from BEGIN keyword and end with the END keyword.
Syntax:
The syntax of the GRANT command in Amazon Redshift is given below:
GRANT {{DELETE | UPDATE | SELECT | REFERENCES | INSERT | DROP} [, …] | ALL [ PRIVILEGES]}
ON {ALL TABLES IN SCHEM name of schema [, …] | [TABLE] name of table [, …]}
TO {GROUP name of the group | name of user [ WITH GRANT OPTION] | PUBLIC } [, …]
Or
GRANT {{TEMPORARY | CREATE | TEMP} [, …] | ALL [PRIVILEGES]}
ON DATABASE name of database [, …]
TO {GROUP name of group | PUBLIC | name of user [ WITH GRANT OPTION]}
In order to manipulate the privileges to the users or consumers for data shares, we can make the use of SHARE privilege and ALTER privilege. The consumers are assigned or removed the privileges by using the SHARE command, and for users, we can make the use of ALTER privilege.
The following syntax of GRANT is used for the same.
GRANT { SHARE | ALTER } ON DATASHARE name of the data share TO {GROUP name of the group | PUBLIC [, …] | name of the user [ WITH GRANT OPTION]}
And for data shares, you can use the below command:
GRANT USAGE ON DATASHARE name of data share TO ACCOUNT number of account [, …] | NAMESPACE GUID of name space [, …]
Other than the above-mentioned syntaxes, there are many versions of GRANT syntax depending on which operation you want to assign the privileges of and on which database object to which user. For a complete official reference of the GRANT syntaxes, you can refer to this link.
The terminologies used in the above syntax are given below:
- SELECT: This command assigns the privileges to the data which is selected from view and tables.
- INSERT: This command assigns the privilege to the table of inserting the data to the tables or views or even for the COPY statement.
- UPDATE: It is necessary to have the SELECT command privilege to the user before we go for assigning the privilege to update the data from tables or columns of those tables or views.
- DELETE: This command grants the privilege to delete any of the existing data from the table. This also requires SELECT privilege.
- REFERENCES: If any of the foreign key is present as the table’s constraint, then we need to assign the table’s permissions, which refers to the table that is being referenced. If not so, then the constraint cannot be created on any of the table.
- ALL [PRIVILEGES]: This clause assigns the permissions of all the operations to the group of user or user, whichever is specified in the statement. The use of the keyword PRIVILEGE is optional for specifying.
- ALTER: When we are using the Lake formation, we can use the alter command that enables the Amazon Web Service Glue Data Catalog for alteration.
- DROP: We can drop any of the tables if we assign the permission to drop any of the table in Amazon Redshift. And is enabled while using the lake formations in AWS Glue Data Catalog.
- ON [TABLE] name of the table: The permission is provided on the database object, which can be table or view and the specification of the keyword TABLE is optional in nature.
- WITH GRANT OPTION: This helps in specifying the user can be assigned the privilege to assign and grant the privileges to the other entities in a database. However, note that the GRANT OPTION cannot be used for the PUBLIC or group of users.
Example of RedShift GRANT
Given below are the example of RedShift GRANT:
Suppose that we have to grant the privilege to the user with the name payal of all the tables for the select operation of the schema educba_articles.
For this, we will make the use of the following command.
Code:
grant select on all tables in schema educba_articles to payal;
Output:
Let us consider one more example where we will try to assign the privileges of drop in the table of topics present in educba_articles schema for the group of users belonging to writer_group.
Code:
grant drop on table educba_articles.topics to group writer_group;
Output:
We can verify the privileges added by using the below command.
Code:
SELECT
u.usename,
t.schemaname||'.'||t.tablename,
has_table_privilege(u.usename,t.tablename,'select') AS "SELECT permission Assigned"
FROM
pg_user u
CROSS JOIN
pg_tables t
WHERE
u.usename = payal
AND t.tablename = "topics";
Output:
Conclusion
The use of the GRANT command can be done to provide the privileges and permissions of doing different operations on various entities of the database and can also be used other external objects of the database provided if certain conditions are accepted.
Recommended Articles
This is a guide to RedShift GRANT. Here we discuss the introduction, how grant command works? and example for better understanding. You may also have a look at the following articles to learn more –
