WL#924: Allow grants and revokes for PUBLIC

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

Introduce configuration variables which allow for the automatic assignment and
granting of default roles when new users are created.

D-1 An authorization identifier consist of two parts: A user name and a host
name separated by a '@' character. Each name is an identifier and can be quoted
as such when handling spaces and special characters. If the host name part and
the @ character of the authorization identifier is omitted it implies that the
host name is `%`.

R-1 New server configuration variable "mandatory-roles". The variable should
accept a comma separated list of authorization identifiers. Example:
Command Line: Yes
System Variable: Yes
Status Variable: No
Option File: Yes
Scope: Global
Dynamic: Yes
Default: ""
Type: String, Max length: 65534 after this behaviour is undefined.

R-1.1 All the specified roles are always considered granted to every user and
they can't be revoked. The still require activation unless they are made into
default roles. The granted roles will not be visible in the role_edges table.

R-1.1.1 The roles should be listed when SHOW GRANTS is executed for the current
user but mandatory roles should NOT be visible when SHOW GRANTS FOR is executed
because of compatibility restrictions from mysqlpump.

R-1.1.2 If the authorization ID isn't present in the mysql.user table, the role
won't be granted. A warning will be issued if the authorization ID can't be used.

R-1.1.3 A 'FLUSH PRIVILEGES' statement may be required after the variable
"mandatory_roles" is updated in order for the policy to take effect. If a
non-existing authorization id is set in mandatory_roles, then a flush privilege
is again required for the mandatory_roles variable to take affect when this
authorization ID is created later.

R-1.1.4 Roles acting as mandatory roles can't be revoked (using a REVOKE
statement) even if they were explicitly granted to an authorization ID.
Consequently they can't be dropped with a DROP USER/ROLE statement either

R-2 New server configuration variable: "activate-all-roles-on-login"
When set to "ON" all granted roles are always activated after the user has
Command Line: Yes
System Variable: Yes
Status Variable: No
Option File: Yes
Scope: Global
Dynamic: Yes
Default: FALSE
Type: boolean

S-0 Authentication

S-0.1 A client can authenticate with an authorization ID. This ID is referred to
as a user or user name.

S-1 Different privileges

S-1.1 A privilege ID is a named token which can be granted to an authorization ID.

S-1.2 A privilege can either be effective or not effective. An effective
privilege is a privilege which used in a session to evaluate if a particular
operation is allowed or not. All effective privileges are granted or inherited
but not all privileges are effective.

S-2 The different roles

S-2.1 A role is an authorization ID which can be granted to another
authorization ID by forming an directed edge between them in the role graph
where every vertex is a unique authorization ID. When the effective privilege is
calculated, all connected roles are visited according to their edge direction
and their corresponding granted privileges are aggregated.

S-2.2 A role can either be /active/ or inactive. Active roles are kept in a
thread local list which exists solely for the lifetime of a client session.
Granted roles can be made active by 1) a SET ROLE statement, 2) after
authentication if the role is a default role, 3) after authentication if the
global variable opt_always_activate_roles_on_login is set to true.
Example: To set the grated role `team`@`%` as an active role, after
authentication, execute: SET ROLE team.

S-2.3 Each authorization ID has a list of /default roles/. Default roles
belonging to an authorization ID are made into active roles after authentication
iff they are granted to this ID. If the list of default roles is empty then no
roles are made active after authentication unless the client sets a SET ROLE

S-2.4 A mandatory role is an authorization ID which is implicitly granted to
every other authorization ID which has authenticated, regardless if this role
has been previously granted or not. Mandatory roles are specified in a global
variable. It's not required that the specified list maps to any existing
authorization ID but if there's no previous authorization ID then no mandatory
role can be granted. Mandatory roles are processed sequentially as any other
granted role when the effective privilege of an authorization ID needs to be
calculated iff they are active.

S-3 The effective privilege cache

S-3.1 To avoid recalculating the effective privilege at every step the result is
saved into a cache (See Acl_cache in the source code). The key to this cache is
formed by concatenating authorization ID, the active roles and the version ID of
the cache.

S-3.2 The cache is a lockless hash storage and each element is assembled using
read-only operations on the shared role graph. (See get_privilege_access_maps()
in the source code)

S-4 Showing grants

S-4.1 The statements SHOW GRANT shows all effective privileges using the
currently active roles for the current user.

S-4.2 The statement SHOW GRANT FOR x USING y are used for listing the effective
privilege for x given y as active roles. If If y isn't specified then no roles
are used. If x isn't specified then the current_user() is used. Mandatory roles
are always excluded from the list of granted roles when this statement is used.
Example: To show the privilege for a role using no roles: SHOW GRANTS FOR x.

S-4.3 SHOW-statements does not use the privilege cache and the effective
privilege is recalculated on every execution. (See mysql_show_grants() in the
source code)

S-4.4 To show the role graph use the "SELECT roles_graphml()" statement. To
investigate the role graph use the built in XML functions or the
mysql.role_edges table.