Authorization IDs, roles and users

Authentication ID

Definition

Each row in the mysql.user table is identified by a user and host tuple. This tuple is the authorization ID. A client can authenticate with an authorization ID and a password. The ID is then referred to as a user or user name.

Privileges ID

Definition

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

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.

Roles

Definition

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.

Active roles

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

Default roles

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.

Mandatory roles

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.

The effective privilege cache

Overview

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

The cache is a lockless hash storage and each element is assembled using read-only operations on the shared role graph.

See also

get_privilege_access_maps

SHOW GRANTS

The statements

SHOW GRANT 

shows all effective privileges using the currently active roles for the current user.

The statement

SHOW GRANT FOR x USING y 

is 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. 

SHOW-statements does not use the privilege cache and the effective privilege is recalculated on every execution.

See also

mysql_show_grants

To show the role graph use

SELECT roles_graphml() 

To investigate the role graph use the built in XML functions or the mysql.role_edges table.