Rechercher dans le manuel MySQL

6.2.3 Grant Tables

The mysql system database includes several grant tables that contain information about user accounts and the privileges held by them. This section describes those tables. For information about other tables in the system database, see Section 5.3, “The mysql System Database”.

To manipulate the contents of grant tables, modify them indirectly by using account-management statements such as CREATE USER, GRANT, and REVOKE to set up accounts and control the privileges available to each one. See Section 13.7.1, “Account Management Statements”. The discussion here describes the underlying structure of the grant tables and how the server uses their contents when interacting with clients.

Note

Direct modification of grant tables using statements such as INSERT, UPDATE, or DELETE is discouraged and done at your own risk. The server is free to ignore rows that become malformed as a result of such modifications.

For any operation that modifies a grant table, the server checks whether the table has the expected structure and produces an error if not. mysql_upgrade must be run to update the tables to the expected structure.

These mysql database tables contain grant information:

  • user: User accounts, global privileges, and other non-privilege columns

  • global_grants: Assignments of dynamic global privileges to users; see Section 6.2.2, “Static Versus Dynamic Privileges”.

  • db: Database-level privileges

  • tables_priv: Table-level privileges

  • columns_priv: Column-level privileges

  • procs_priv: Stored procedure and function privileges

  • proxies_priv: Proxy-user privileges

  • default_roles: Default user roles

  • role_edges: Edges for role subgraphs

  • password_history: Password changes

In MySQL 8.0, grant tables use the InnoDB storage engine and are transactional. Before MySQL 8.0, grant tables used the MyISAM storage engine and were nontransactional. This change of grant table storage engine enables an accompanying change to the behavior of account-management statements such as CREATE USER or GRANT. Previously, an account-management statement that named multiple users could succeed for some users and fail for others. Now, each statement is transactional and either succeeds for all named users or rolls back and has no effect if any error occurs.

Each grant table contains scope columns and privilege columns:

  • Scope columns determine the scope of each row in the tables; that is, the context in which the row applies. For example, a user table row with Host and User values of 'h1.example.net' and 'bob' applies to authenticating connections made to the server from the host h1.example.net by a client that specifies a user name of bob. Similarly, a db table row with Host, User, and Db column values of 'h1.example.net', 'bob' and 'reports' applies when bob connects from the host h1.example.net to access the reports database. The tables_priv and columns_priv tables contain scope columns indicating tables or table/column combinations to which each row applies. The procs_priv scope columns indicate the stored routine to which each row applies.

  • Privilege columns indicate which privileges a table row grants; that is, which operations it permits to be performed. The server combines the information in the various grant tables to form a complete description of a user's privileges. Section 6.2.7, “Access Control, Stage 2: Request Verification”, describes the rules for this.

The server uses the grant tables in the following manner:

  • The user table scope columns determine whether to reject or permit incoming connections. For permitted connections, any privileges granted in the user table indicate the user's global privileges. Any privileges granted in this table apply to all databases on the server.

    Caution

    Because any global privilege is considered a privilege for all databases, any global privilege enables a user to see all database names with SHOW DATABASES or by examining the SCHEMATA table of INFORMATION_SCHEMA.

  • The global_grants table lists current assignments of dynamic privileges to user accounts.

  • The db table scope columns determine which users can access which databases from which hosts. The privilege columns determine the permitted operations. A privilege granted at the database level applies to the database and to all objects in the database, such as tables and stored programs.

  • The tables_priv and columns_priv tables are similar to the db table, but are more fine-grained: They apply at the table and column levels rather than at the database level. A privilege granted at the table level applies to the table and to all its columns. A privilege granted at the column level applies only to a specific column.

  • The procs_priv table applies to stored routines (stored procedures and functions). A privilege granted at the routine level applies only to a single procedure or function.

  • The proxies_priv table indicates which users can act as proxies for other users and whether a user can grant the PROXY privilege to other users.

  • The default_roles and role_edges tables contain information about role relationships.

  • The password_history table retains previously chosen passwords to enable restrictions on password reuse. See Section 6.3.8, “Password Management”.

The server uses the user and db tables in the mysql database at both the first and second stages of access control (see Section 6.2, “The MySQL Access Privilege System”). The columns in the user and db tables are shown here.

Table 6.4 user and db Table Columns

Table Name user db
Scope columns Host Host
User Db
User
Privilege columns Select_priv Select_priv
Insert_priv Insert_priv
Update_priv Update_priv
Delete_priv Delete_priv
Index_priv Index_priv
Alter_priv Alter_priv
Create_priv Create_priv
Drop_priv Drop_priv
Grant_priv Grant_priv
Create_view_priv Create_view_priv
Show_view_priv Show_view_priv
Create_routine_priv Create_routine_priv
Alter_routine_priv Alter_routine_priv
Execute_priv Execute_priv
Trigger_priv Trigger_priv
Event_priv Event_priv
Create_tmp_table_priv Create_tmp_table_priv
Lock_tables_priv Lock_tables_priv
References_priv References_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Show_db_priv
Super_priv
Repl_slave_priv
Repl_client_priv
Create_user_priv
Create_tablespace_priv
Create_role_priv
Drop_role_priv
Security columns ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin
authentication_string
password_expired
password_last_changed
password_lifetime
account_locked
Password_reuse_history
Password_reuse_time
Password_require_current
User_attributes
Resource control columns max_questions
max_updates
max_connections
max_user_connections

The user table plugin and authentication_string columns store authentication plugin and credential information.

The server uses the plugin named in the plugin column of an account row to authenticate connection attempts for the account.

The plugin column must be nonempty. At startup, and at runtime when FLUSH PRIVILEGES is executed, the server checks user table rows. For any row with an empty plugin column, the server writes a warning to the error log of this form:

[Warning] User entry 'user_name'@'host_name' has an empty plugin
value. The user will be ignored and no one can login with this user
anymore.

The password_expired column permits DBAs to expire account passwords and require users to reset their password. The default password_expired value is 'N', but can be set to 'Y' with the ALTER USER statement. After an account's password has been expired, all operations performed by the account in subsequent connections to the server result in an error until the user issues an ALTER USER statement to establish a new account password.

It is possible after password expiration to reset a password by setting it to its current value. As a matter of good policy, it is preferable to choose a different password. DBAs can enforce non-reuse by establishing an appropriate password-reuse policy. See Password Reuse Policy.

password_last_changed is a TIMESTAMP column indicating when the password was last changed. The value is non-NULL only for accounts that use a MySQL built-in authentication plugin (mysql_native_password, sha256_password, or caching_sha2_password). The value is NULL for other accounts, such as those authenticated using an external authentication system.

password_last_changed is updated by the CREATE USER, ALTER USER, and SET PASSWORD statements, and by GRANT statements that create an account or change an account password.

password_lifetime indicates the account password lifetime, in days. If the password is past its lifetime (assessed using the password_last_changed column), the server considers the password expired when clients connect using the account. A value of N greater than zero means that the password must be changed every N days. A value of 0 disables automatic password expiration. If the value is NULL (the default), the global expiration policy applies, as defined by the default_password_lifetime system variable.

account_locked indicates whether the account is locked (see Section 6.3.12, “User Account Locking”).

Password_reuse_history is the value of the PASSWORD HISTORY option for the account, or NULL for the default history.

Password_reuse_time is the value of the PASSWORD REUSE INTERVAL option for the account, or NULL for the default interval.

Password_require_current (available as of MySQL 8.0.13) corresponds to the value of the PASSWORD REQUIRE option for the account, as shown by the following table.

Table 6.5 Permitted Password_require_current Values

Password_require_current Value Corresponding PASSWORD REQUIRE Option
'Y' PASSWORD REQUIRE CURRENT
'N' PASSWORD REQUIRE CURRENT OPTIONAL
NULL PASSWORD REQUIRE CURRENT DEFAULT

User_attributes (available as of MySQL 8.0.14) stores account attributes not stored in other columns, such as the account secondary password, if any.

During the second stage of access control, the server performs request verification to ensure that each client has sufficient privileges for each request that it issues. In addition to the user and db grant tables, the server may also consult the tables_priv and columns_priv tables for requests that involve tables. The latter tables provide finer privilege control at the table and column levels. They have the columns shown in the following table.

Table 6.6 tables_priv and columns_priv Table Columns

Table Name tables_priv columns_priv
Scope columns Host Host
Db Db
User User
Table_name Table_name
Column_name
Privilege columns Table_priv Column_priv
Column_priv
Other columns Timestamp Timestamp
Grantor

The Timestamp and Grantor columns are set to the current timestamp and the CURRENT_USER value, respectively, but are otherwise unused.

For verification of requests that involve stored routines, the server may consult the procs_priv table, which has the columns shown in the following table.

Table 6.7 procs_priv Table Columns

Table Name procs_priv
Scope columns Host
Db
User
Routine_name
Routine_type
Privilege columns Proc_priv
Other columns Timestamp
Grantor

The Routine_type column is an ENUM column with values of 'FUNCTION' or 'PROCEDURE' to indicate the type of routine the row refers to. This column enables privileges to be granted separately for a function and a procedure with the same name.

The Timestamp and Grantor columns are unused.

The proxies_priv table records information about proxy accounts. It has these columns:

  • Host, User: The proxy account; that is, the account that has the PROXY privilege for the proxied account.

  • Proxied_host, Proxied_user: The proxied account.

  • Grantor, Timestamp: Unused.

  • With_grant: Whether the proxy account can grant the PROXY privilege to other accounts.

For an account to be able to grant the PROXY privilege to other accounts, it must have a row in the proxies_priv table with With_grant set to 1 and Proxied_host and Proxied_user set to indicate the account or accounts for which the privilege can be granted. For example, the 'root'@'localhost' account created during MySQL installation has a row in the proxies_priv table that enables granting the PROXY privilege for ''@'', that is, for all users and all hosts. This enables root to set up proxy users, as well as to delegate to other accounts the authority to set up proxy users. See Section 6.3.11, “Proxy Users”.

The global_grants table lists current assignments of dynamic privileges to user accounts. These privileges are global. The table has these columns:

  • USER, HOST: The user name and host name of the account to which the privilege is granted.

  • PRIV: The privilege name.

  • WITH_GRANT_OPTION: Whether the account can grant the privilege to other accounts.

The default_roles table lists default user roles. It has these columns:

  • HOST, USER: The account or role to which the default role applies.

  • DEFAULT_ROLE_HOST, DEFAULT_ROLE_USER: The default role.

The role_edges table lists edges for role subgraphs. It has these columns:

  • FROM_HOST, FROM_USER: The account that is granted a role.

  • TO_HOST, TO_USER: The role that is granted to the account.

  • WITH_ADMIN_OPTION: Whether the account can grant the role to and revoke it from other accounts by using WITH ADMIN OPTION.

The password_history table contains information about password changes. It has these columns:

  • Host, User: The account for which the password change occurred.

  • Password_timestamp: The time when the password change occurred.

  • Password: The new password hash value.

The password_history table accumulates a sufficient number of nonempty passwords per account to enable MySQL to perform checks against both the account password history length and reuse interval. Automatic pruning of entries that are outside both limits occurs when password-change attempts occur.

Note

The empty password does not count in the password history and is subject to reuse at any time.

If an account is renamed, its entries are renamed to match. If an account is dropped or its authentication plugin is changed, its entries are removed.

Scope columns in the grant tables contain strings. The default value for each is the empty string. The following table shows the number of characters permitted in each column.

Table 6.8 Grant Table Scope Column Lengths

Column Name Maximum Permitted Characters
Host, Proxied_host 60
User, Proxied_user 32
Db 64
Table_name 64
Column_name 64
Routine_name 64

For access-checking purposes, comparisons of User, Proxied_user, authentication_string, Db, and Table_name values are case-sensitive. Comparisons of Host, Proxied_host, Column_name, and Routine_name values are not case-sensitive.

The user and db tables list each privilege in a separate column that is declared as ENUM('N','Y') DEFAULT 'N'. In other words, each privilege can be disabled or enabled, with the default being disabled.

The tables_priv, columns_priv, and procs_priv tables declare the privilege columns as SET columns. Values in these columns can contain any combination of the privileges controlled by the table. Only those privileges listed in the column value are enabled.

Table 6.9 Set-Type Privilege Column Values

Table Name Column Name Possible Set Elements
tables_priv Table_priv 'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter', 'Create View', 'Show view', 'Trigger'
tables_priv Column_priv 'Select', 'Insert', 'Update', 'References'
columns_priv Column_priv 'Select', 'Insert', 'Update', 'References'
procs_priv Proc_priv 'Execute', 'Alter Routine', 'Grant'

Only the user table specifies administrative privileges, such as RELOAD and SHUTDOWN. Administrative operations are operations on the server itself and are not database-specific, so there is no reason to list these privileges in the other grant tables. Consequently, the server need consult only the user table to determine whether a user can perform an administrative operation.

The FILE privilege also is specified only in the user table. It is not an administrative privilege as such, but a user's ability to read or write files on the server host is independent of the database being accessed.

The server reads the contents of the grant tables into memory when it starts. You can tell it to reload the tables by issuing a FLUSH PRIVILEGES statement or executing a mysqladmin flush-privileges or mysqladmin reload command. Changes to the grant tables take effect as indicated in Section 6.2.8, “When Privilege Changes Take Effect”.

When you modify an account, it is a good idea to verify that your changes have the intended effect. To check the privileges for a given account, use the SHOW GRANTS statement. For example, to determine the privileges that are granted to an account with user name and host name values of bob and pc84.example.com, use this statement:

  1. SHOW GRANTS FOR 'bob'@'pc84.example.com';

To display nonprivilege properties of an account, use SHOW CREATE USER:

  1. SHOW CREATE USER 'bob'@'pc84.example.com';

Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-grant-tables.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut