Sign InTry Free

GRANT <role>

Assigns a previously created role to an existing user. The user can use then use the statement SET ROLE <rolename> to assume the privileges of the role, or SET ROLE ALL to assume all roles that have been assigned.

Synopsis

GrantRoleStmt
GRANTRolenameListTOUsernameList
RolenameList
Rolename,
UsernameList
Username,

Examples

Create a new role for the analytics team, and a new user called jennifer:

$ mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 37 Server version: 5.7.25-TiDB-v4.0.0-beta.2-728-ga9177fe84 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE ROLE analyticsteam; Query OK, 0 rows affected (0.02 sec) mysql> GRANT SELECT ON test.* TO analyticsteam; Query OK, 0 rows affected (0.02 sec) mysql> CREATE USER jennifer; Query OK, 0 rows affected (0.01 sec) mysql> GRANT analyticsteam TO jennifer; Query OK, 0 rows affected (0.01 sec)

Note that by default jennifer needs to SET ROLE analyticsteam in order to be able to use the privileges associated with the role:

$ mysql -ujennifer Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 32 Server version: 5.7.25-TiDB-v4.0.0-beta.2-728-ga9177fe84 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW GRANTS; +---------------------------------------------+ | Grants for User | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'jennifer'@'%' | | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' | +---------------------------------------------+ 2 rows in set (0.00 sec) mysql> SHOW TABLES in test; ERROR 1044 (42000): Access denied for user 'jennifer'@'%' to database 'test' mysql> SET ROLE analyticsteam; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS; +---------------------------------------------+ | Grants for User | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'jennifer'@'%' | | GRANT Select ON test.* TO 'jennifer'@'%' | | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' | +---------------------------------------------+ 3 rows in set (0.00 sec) mysql> SHOW TABLES IN test; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec)

The statement SET DEFAULT ROLE can be used to associated a role to jennifer so that she will not have to execute the statement SET ROLE in order to assume the privileges associated with the role:

$ mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 Server version: 5.7.25-TiDB-v4.0.0-beta.2-728-ga9177fe84 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SET DEFAULT ROLE analyticsteam TO jennifer; Query OK, 0 rows affected (0.02 sec)
$ mysql -ujennifer Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35 Server version: 5.7.25-TiDB-v4.0.0-beta.2-728-ga9177fe84 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW GRANTS; +---------------------------------------------+ | Grants for User | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'jennifer'@'%' | | GRANT Select ON test.* TO 'jennifer'@'%' | | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' | +---------------------------------------------+ 3 rows in set (0.00 sec) mysql> SHOW TABLES IN test; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec)

MySQL compatibility

This statement is understood to be fully compatible with roles, which are a feature of MySQL 8.0. Any compatibility differences should be reported via an issue on GitHub.

See also

Was this page helpful?

Download PDFRequest docs changesAsk questions on Discord
Playground
New
One-stop & interactive experience of TiDB's capabilities WITHOUT registration.
Products
TiDB
TiDB Dedicated
TiDB Serverless
Pricing
Get Demo
Get Started
© 2024 PingCAP. All Rights Reserved.
Privacy Policy.