WL#924: Allow grants and revokes for PUBLIC
Affects: Server-8.0 — Status: Complete
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: `role1`@`%`,`role2`,role3,role4@localhost 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 authenticated. 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 statement. 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.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.