SET DEFAULT ROLE

Syntax

SET DEFAULT ROLE { role | NONE } [ FOR user@host ]

Description

The SET DEFAULT ROLE statement sets a default role for a specified (or current) user. A default role is automatically enabled when a user connects (an implicit SET ROLE 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 dropped or revoked, 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 mysql.user table/view, as well as in the Information Schema APPLICABLE_ROLES table, so these can be viewed to see which role has been assigned to a user as the default.

Examples

Setting a default role for the current user:

SET DEFAULT ROLE journalist;

Removing a default role from the current user:

SET DEFAULT ROLE NONE;

Setting a default role for another user. The role has to have been granted to the user before it can be set as default:

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;

Viewing mysql.user:

select * from mysql.user where user='taniel'\G
*************************** 1. row ***************************
                  Host: %
                  User: taniel
...
               is_role: N
          default_role: journalist
...

Removing a default role for another user

SET DEFAULT ROLE NONE FOR taniel;