Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
# Examples
The following example grants the SELECT privilege on the SALES table to the user `fred`.
```
grant select on table sales to fred;
```
The following example grants the SELECT privilege on all tables in the QA\$1TICKIT schema to the user `fred`.
```
grant select on all tables in schema qa_tickit to fred;
```
The following example grants all schema privileges on the schema QA\$1TICKIT to the user group QA\$1USERS. Schema privileges are CREATE and USAGE. USAGE grants users access to the objects in the schema, but doesn't grant privileges such as INSERT or SELECT on those objects. Grant privileges on each object separately.
```
create group qa_users;
grant all on schema qa_tickit to group qa_users;
```
The following example grants all privileges on the SALES table in the QA\$1TICKIT schema to all users in the group QA\$1USERS.
```
grant all on table qa_tickit.sales to group qa_users;
```
The following example grants all privileges on the SALES table in the QA\$1TICKIT schema to all users in the groups QA\$1USERS and RO\$1USERS.
```
grant all on table qa_tickit.sales to group qa_users, group ro_users;
```
The following example grants the DROP privilege on the SALES table in the QA\$1TICKIT schema to all users in the group QA\$1USERS.
```
grant drop on table qa_tickit.sales to group qa_users;>
```
The following sequence of commands shows how access to a schema doesn't grant privileges on a table in the schema.
```
create user schema_user in group qa_users password 'Abcd1234';
create schema qa_tickit;
create table qa_tickit.test (col1 int);
grant all on schema qa_tickit to schema_user;
set session authorization schema_user;
select current_user;
current_user
--------------
schema_user
(1 row)
select count(*) from qa_tickit.test;
ERROR: permission denied for relation test [SQL State=42501]
set session authorization dw_user;
grant select on table qa_tickit.test to schema_user;
set session authorization schema_user;
select count(*) from qa_tickit.test;
count
-------
0
(1 row)
```
The following sequence of commands shows how access to a view doesn't imply access to its underlying tables. The user called VIEW\$1USER can't select from the DATE table, although this user has been granted all privileges on VIEW\$1DATE.
```
create user view_user password 'Abcd1234';
create view view_date as select * from date;
grant all on view_date to view_user;
set session authorization view_user;
select current_user;
current_user
--------------
view_user
(1 row)
select count(*) from view_date;
count
-------
365
(1 row)
select count(*) from date;
ERROR: permission denied for relation date
```
The following example grants SELECT privilege on the `cust_name` and `cust_phone` columns of the `cust_profile` table to the user `user1`.
```
grant select(cust_name, cust_phone) on cust_profile to user1;
```
The following example grants SELECT privilege on the `cust_name` and `cust_phone` columns and UPDATE privilege on the `cust_contact_preference` column of the `cust_profile` table to the `sales_group` group.
```
grant select(cust_name, cust_phone), update(cust_contact_preference) on cust_profile to group sales_group;
```
The following example shows the usage of the ALL keyword to grant both SELECT and UPDATE privileges on three columns of the table `cust_profile` to the `sales_admin` group.
```
grant ALL(cust_name, cust_phone,cust_contact_preference) on cust_profile to group sales_admin;
```
The following example grants the SELECT privilege on the `cust_name` column of the `cust_profile_vw` view to the `user2` user.
```
grant select(cust_name) on cust_profile_vw to user2;
```
## Examples of granting access to datashares
The following examples show GRANT datasharing usage permissions on a specific database or schema created from a datashare.
In the following example, a producer-side admin grants the USAGE permission on the `salesshare` datashare to the specified namespace.
```
GRANT USAGE ON DATASHARE salesshare TO NAMESPACE '13b8833d-17c6-4f16-8fe4-1a018f5ed00d';
```
In the following example, a consumer-side admin grants the USAGE permission on the `sales_db` to `Bob`.
```
GRANT USAGE ON DATABASE sales_db TO Bob;
```
In the following example, a consumer-side admin grants the GRANT USAGE permission on the `sales_schema` schema to the `Analyst_role` role. `sales_schema` is an external schema that points to sales\$1db.
```
GRANT USAGE ON SCHEMA sales_schema TO ROLE Analyst_role;
```
At this point, `Bob` and `Analyst_role` can access all database objects in `sales_schema` and `sales_db`.
The following example shows granting additional object-level permission for objects in a shared database. These extra permissions are only necessary if the CREATE DATABASE command that was used to create the shared database used the WITH PERMISSIONS clause. If the CREATE DATABASE command didn’t use WITH PERMISSIONS, granting USAGE on the shared database grants full access to all objects in that database.
```
GRANT SELECT ON sales_db.sales_schema.tickit_sales_redshift to Bob;
```
## Examples of granting scoped permissions
The following example grants usage for all current and future schemas in the `Sales_db` database to the `Sales` role.
```
GRANT USAGE FOR SCHEMAS IN DATABASE Sales_db TO ROLE Sales;
```
The following example grants the SELECT permission for all current and future tables in the `Sales_db` database to the user `alice`, and also gives `alice` the permission to grant scoped permissions on tables in `Sales_db` to other users.
```
GRANT SELECT FOR TABLES IN DATABASE Sales_db TO alice WITH GRANT OPTION;
```
The following example grants the EXECUTE permission for functions in the `Sales_schema` schema to the user `bob`.
```
GRANT EXECUTE FOR FUNCTIONS IN SCHEMA Sales_schema TO bob;
```
The following example grants all permissions for all tables in the `ShareDb` database’s `ShareSchema` schema to the `Sales` role. When specifying the schema, you can specify the schema’s database using the two-part format `database.schema`.
```
GRANT ALL FOR TABLES IN SCHEMA ShareDb.ShareSchema TO ROLE Sales;
```
The following example is the same as the preceding one. You can specify the database using the `DATABASE` keyword instead of using a two-part format.
```
GRANT ALL FOR TABLES IN SCHEMA ShareSchema DATABASE ShareDb TO ROLE Sales;
```
## Examples of granting the ASSUMEROLE privilege
The following are examples of granting the ASSUMEROLE privilege.
The following example shows the REVOKE statement that a superuser runs once on the cluster to enable the use of the ASSUMEROLE privilege for users and groups. Then, the superuser grants the ASSUMEROLE privilege to users and groups for the appropriate commands. For information on enabling the use of the ASSUMEROLE privilege for users and groups, see [Usage notes for granting the ASSUMEROLE permission](r_GRANT-usage-notes.md#r_GRANT-usage-notes-assumerole).
```
revoke assumerole on all from public for all;
```
The following example grants the ASSUMEROLE privilege to the user `reg_user1` for the IAM role `Redshift-S3-Read` to perform COPY operations.
```
grant assumerole on 'arn:aws:iam::123456789012:role/Redshift-S3-Read'
to reg_user1 for copy;
```
The following example grants the ASSUMEROLE privilege to the user `reg_user1` for the IAM role chain `RoleA`, `RoleB` to perform UNLOAD operations.
```
grant assumerole
on 'arn:aws:iam::123456789012:role/RoleA,arn:aws:iam::210987654321:role/RoleB'
to reg_user1
for unload;
```
The following is an example of the UNLOAD command using the IAM role chain `RoleA`, `RoleB`.
```
unload ('select * from venue limit 10')
to 's3://companyb/redshift/venue_pipe_'
iam_role 'arn:aws:iam::123456789012:role/RoleA,arn:aws:iam::210987654321:role/RoleB';
```
The following example grants the ASSUMEROLE privilege to the user `reg_user1` for the IAM role `Redshift-Exfunc` to create external functions.
```
grant assumerole on 'arn:aws:iam::123456789012:role/Redshift-Exfunc'
to reg_user1 for external function;
```
The following example grants the ASSUMEROLE privilege to the user `reg_user1` for the IAM role `Redshift-model` to create machine learning models.
```
grant assumerole on 'arn:aws:iam::123456789012:role/Redshift-ML'
to reg_user1 for create model;
```
## Examples of granting the ROLE privileges
The following example grants sample\$1role1 to user1.
```
CREATE ROLE sample_role1;
GRANT ROLE sample_role1 TO user1;
```
The following example grants sample\$1role1 to user1 with the WITH ADMIN OPTION, sets the current session for user1, and user1 grants sample\$1role1 to user2.
```
GRANT ROLE sample_role1 TO user1 WITH ADMIN OPTION;
SET SESSION AUTHORIZATION user1;
GRANT ROLE sample_role1 TO user2;
```
The following example grants sample\$1role1 to sample\$1role2.
```
GRANT ROLE sample_role1 TO ROLE sample_role2;
```
The following example grants sample\$1role2 to sample\$1role3 and sample\$1role4. Then it attempts to grants sample\$1role3 to sample\$1role1.
```
GRANT ROLE sample_role2 TO ROLE sample_role3;
GRANT ROLE sample_role3 TO ROLE sample_role2;
ERROR: cannot grant this role, a circular dependency was detected between these roles
```
The following example grants the CREATE USER system privileges to sample\$1role1.
```
GRANT CREATE USER TO ROLE sample_role1;
```
The following example grants the system-defined role `sys:dba` to user1.
```
GRANT ROLE sys:dba TO user1;
```
The following example attempts to grant sample\$1role3 in a circular dependency to sample\$1role2.
```
CREATE ROLE sample_role3;
GRANT ROLE sample_role2 TO ROLE sample_role3;
GRANT ROLE sample_role3 TO ROLE sample_role2; -- fail
ERROR: cannot grant this role, a circular dependency was detected between these roles
```