TiDBTiDB Docs Dash 2024

Jan 9 - Jan 12 (UTC)
Join us to improve docs and win a prize!
Sign InTry Free

SET ROLE

The SET ROLE statement is used to enable roles in the current session. After enabling roles, users can use the privileges of the role(s).

Synopsis

SetRoleStmt:

SetRoleStmt

SetRoleOpt:

SetRoleOpt

SetDefaultRoleOpt:

SetDefaultRoleOpt

Examples

Create a user 'u1'@'%' and three roles: 'r1'@'%', 'r2'@'%' and 'r3'@'%'. Grant these roles to 'u1'@'%' and set 'r1'@'%' as the default role of 'u1'@'%'.

CREATE USER 'u1'@'%'; CREATE ROLE 'r1', 'r2', 'r3'; GRANT 'r1', 'r2', 'r3' TO 'u1'@'%'; SET DEFAULT ROLE 'r1' TO 'u1'@'%';

Log in as 'u1'@'%' and execute the following SET ROLE statement to enable all roles.

SET ROLE ALL; SELECT CURRENT_ROLE();
+----------------------------+ | CURRENT_ROLE() | +----------------------------+ | `r1`@`%`,`r2`@`%`,`r3`@`%` | +----------------------------+ 1 row in set (0.000 sec)

Execute the following SET ROLE statement to enable 'r2' and 'r3'.

SET ROLE 'r2', 'r3'; SELECT CURRENT_ROLE();
+-------------------+ | CURRENT_ROLE() | +-------------------+ | `r2`@`%`,`r3`@`%` | +-------------------+ 1 row in set (0.000 sec)

Execute the following SET ROLE statement to enable the default role(s).

SET ROLE DEFAULT; SELECT CURRENT_ROLE();
+----------------+ | CURRENT_ROLE() | +----------------+ | `r1`@`%` | +----------------+ 1 row in set (0.000 sec)

Execute the following SET ROLE statement to cancel all enabled role(s).

SET ROLE NONE; SELECT CURRENT_ROLE();
+----------------+ | CURRENT_ROLE() | +----------------+ | | +----------------+ 1 row in set (0.000 sec)

MySQL compatibility

The SET ROLE statement in TiDB is fully compatible with the roles feature in MySQL 8.0. If you find any compatibility differences, report a bug.

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.