SET DEFAULT ROLE
Description
Sets the roles that are activated by default when the user connects to the server.
This command is supported from v3.0.
tip
Common users can set default roles for themselves. Users with the user_admin
role can set default roles for other users. Before you perform this operation, make sure that the user has already been assigned these roles.
You can query the roles of a user using SHOW GRANTS.
Syntax
-- Set specified roles as default roles.
SET DEFAULT ROLE <role_name>[,<role_name>,..] TO <user_identity>;
-- Set all roles of the user, including roles that will be assigned to this user, as default roles.
SET DEFAULT ROLE ALL TO <user_identity>;
-- No default role is set but the public role is still enabled after a user login.
SET DEFAULT ROLE NONE TO <user_identity>;
Parameters
role_name
: the role name
user_identity
: the user identity
Examples
Query the roles of the current user.
SHOW GRANTS FOR test;
+--------------+---------+----------------------------------------------+
| UserIdentity | Catalog | Grants |
+--------------+---------+----------------------------------------------+
| 'test'@'%' | NULL | GRANT 'db_admin', 'user_admin' TO 'test'@'%' |
+--------------+---------+----------------------------------------------+
Example 1: Set roles db_admin
and user_admin
as the default roles for the user test
.
SET DEFAULT ROLE db_admin TO test;
Example 2: Set all roles of the user test
, including roles that will be assigned to this user as default roles.
SET DEFAULT ROLE ALL TO test;
Example 3: Clear all the default roles of the user test
.
SET DEFAULT ROLE NONE TO test;