As (or close to) defined in the SQL:2003 standard.
According to the standard, role and user names live in the same namespace. But in MariaDB a user name has a mandatory @host part (even if it's @%). And we probably wouldn't want it for roles. So, practically, we will have users and roles in different name spaces, with a little ambiguity, when a user name is specified without a @host part, where allowed. Alternatively, we can specify that a role name cannot match the first part of any user name. May be it'll be less confusing this way.
Only one role can be set to a user at any specific point in any given session. In other words, CURRENT_ROLE (see below) can never return a list.
One needs CREATE USER privilege to create or drop a role. Or an appropriate (insert or delete) privilege on the mysql database.
One needs a role to be granted to himself WITH ADMIN OPTION to be able to grant or revoke it further. A creator of a role gets it automatically granted to himself WITH ADMIN CURRENT_USER. Alternatively, one can use WITH ADMIN CURRENT_ROLE or (with SUPER privilege) WITH ADMIN arbitrary_definer.
If there is no current role, the function returns NULL, not "NONE".
there are more tables in the INFORMATION_SCHEMA that are relevant for roles. They are not part of this task.
Everywhere where one can write DEFINER=xxx, we should allow xxx to be a role name. And additionally we'll support DEFINER=CURRENT_ROLE.
The role name of PUBLIC is reserved. There can be no role with this name. But this name can be used in GRANT and REVOKE statements as a grantee.Privileges granted to PUBLIC are always available to everyone. Implementing it is not part of this task.
The role name of NONE is reserved too.
Syntax variants (which ones we'll do?):
When a default role is set, the server implicitly runs SET ROLE yyy for every new connection (or after a COM_CHANGE_USER for a user xxx.