Skip to main content

GRANT <privileges> to a User or Role

Grants one or more access privileges to a user or role. The privileges that can be granted are grouped into the following categories:

  • Privileges for schema objects (databases, tables, views, stages, UDFs)

Syntax

GRANT { 
schemaObjectPrivileges | ALL [ PRIVILEGES ] ON <privileges_level>
}
TO [ROLE <role_name>] [<user>]

Where:

schemaObjectPrivileges ::=
-- For TABLE
{ SELECT | INSERT }

-- For SCHEMA
{ CREATE | DROP | ALTER }

-- For USER
{ CREATE USER }

-- For ROLE
{ CREATE ROLE}

-- For STAGE
{ CREATE STAGE}
privileges_level ::=
*.*
| db_name.*
| db_name.tbl_name

Examples

Grant Privileges to a User

Create a user:

CREATE USER user1 IDENTIFIED BY 'abc123';

Grant the ALL privilege on all existing tables in the default database to the user user1:

GRANT ALL ON default.* TO user1;
SHOW GRANTS FOR user1;
+-----------------------------------------+
| Grants |
+-----------------------------------------+
| GRANT ALL ON 'default'.* TO 'user1'@'%' |
+-----------------------------------------+

Grant the ALL privilege to all the database to the user user1:

GRANT ALL ON *.* TO 'user1';
SHOW GRANTS FOR user1;
+-----------------------------------------+
| Grants |
+-----------------------------------------+
| GRANT ALL ON 'default'.* TO 'user1'@'%' |
| GRANT ALL ON *.* TO 'user1'@'%' |
+-----------------------------------------+

Grant Privileges to a Role

Grant the SELECT privilege on all existing tables in the mydb database to the role role1:

Create role:

CREATE ROLE role1;

Grant privileges to the role:

GRANT SELECT ON mydb.* TO ROLE role1;

Show the grants for the role:

SHOW GRANTS FOR ROLE role1;
+-------------------------------------+
| Grants |
+-------------------------------------+
| GRANT SELECT ON 'mydb'.* TO 'role1' |
+-------------------------------------+