Roles

As (or close to) defined in the SQL:2003 standard.

New statements

CREATE ROLE role

DROP ROLE role

GRANT role TO { user | user }

REVOKE role FROM {user | role }

SET ROLE { role_name | NONE }

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.

Privileges

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.

Existing statements need to work with roles

REVOKE privilege FROM { user | role }

GRANT privilege TO { user | role }

SHOW GRANTS FOR { user | role }

New functions

CURRENT_ROLE

CURRENT_ROLE()

If there is no current role, the function returns NULL, not "NONE".

Informational tables

INFORMATION_SCHEMA.APPLICABLE_ROLES

INFORMATION_SCHEMA.ENABLED_ROLES

there are more tables in the INFORMATION_SCHEMA that are relevant for roles. They are not part of this task.

DEFINER=CURRENT_ROLE

Everywhere where one can write DEFINER=xxx, we should allow xxx to be a role name. And additionally we'll support DEFINER=CURRENT_ROLE.

Reserved role names

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.

Default role (not part of this task)

Syntax variants (which ones we'll do?):

CREATE USER xxx DEFAULT ROLE yyy;

ALTER USER xxx DEFAULT ROLE yyy;

SET DEFAULT ROLE yyy;

SET DEFAULT ROLE yyy [ FOR xxx ];

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.