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;

URL: https://mariadb.com/kb/en/set-default-role/