Define the default role for a user. Learn how to configure which role is automatically active when a user connects to the server.
SET DEFAULT ROLE { role | NONE } [ FOR user@host ]The SET DEFAULT ROLE statement sets a default for a specified (or current) user. A default role is automatically enabled when a user connects (an implicit statement is executed immediately after a connection is established).
To be able to set a role as a default, the role must already have been granted to that user, and one needs the privileges to enable this role (if you cannot do SET ROLE X, you won't be able to do SET DEFAULT ROLE X). To set a default role for another user one needs to have write access to the mysql database.
To remove a user's default role, use SET DEFAULT ROLE NONE [ FOR user@host ]. The record of the default role is not removed if the role is or , so if the role is subsequently re-created or granted, it will again be the user's default role.
The default role is stored in the default_role column in the table/view, as well as in the , so these can be viewed to see which role has been assigned to a user as the default.
Setting a default role for the current user:
Removing a default role from the current user:
Setting a default role for another user. The role has to have been granted to the user before it can be set as default:
Viewing mysql.user:
Removing a default role for another user
This page is licensed: CC BY-SA / Gnu FDL
SET DEFAULT ROLE journalist;SET DEFAULT ROLE NONE;CREATE ROLE journalist;
CREATE USER taniel;
SET DEFAULT ROLE journalist FOR taniel;
ERROR 1959 (OP000): Invalid role specification `journalist`
GRANT journalist TO taniel;
SET DEFAULT ROLE journalist FOR taniel;SELECT * FROM mysql.user WHERE user='taniel'\G*************************** 1. row ***************************
Host: %
User: taniel
...
is_role: N
default_role: journalist
...SET DEFAULT ROLE NONE FOR taniel;