MariaDB [(none)]> CREATE ROLE y;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES;

+----------------+-----------+--------------+------------+

| GRANTEE        | ROLE_NAME | IS_GRANTABLE | IS_DEFAULT |

+----------------+-----------+--------------+------------+

| root@localhost | x         | YES          | YES        |

| root@localhost | y         | YES          | NO         |

+----------------+-----------+--------------+------------+

2 rows in set (0.00 sec)

MariaDB [(none)]> SET DEFAULT ROLE y;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT default_role FROM mysql.user WHERE user='root';

+--------------+

| default_role |

+--------------+

| y            |

|              |

|              |

|              |

+--------------+

4 rows in set (0.00 sec)

MariaDB [(none)]> DROP ROLE y;

Query OK, 0 rows affected (0.00 sec)

# Default role is still there in mysql.user table ?

MariaDB [(none)]> SELECT default_role FROM mysql.user WHERE user='root';

+--------------+

| default_role |

+--------------+

| y            |

|              |

|              |

|              |

+--------------+

4 rows in set (0.00 sec)

MariaDB [(none)]> # After dropping the role and creating it again it will stay as default

MariaDB [(none)]> CREATE ROLE y;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES;

+----------------+-----------+--------------+------------+

| GRANTEE        | ROLE_NAME | IS_GRANTABLE | IS_DEFAULT |

+----------------+-----------+--------------+------------+

| root@localhost | x         | YES          | NO         |

| root@localhost | y         | YES          | YES        |

+----------------+-----------+--------------+------------+

2 rows in set (0.00 sec)