6.2 Access Control and Account Management

MySQL enables the creation of accounts that permit client users to connect to the server and access data managed by the server. The primary function of the MySQL privilege system is to authenticate a user who connects from a given host and to associate that user with privileges on a database such as note 'SELECT': select, note 'INSERT': insert, note 'UPDATE': update, and note 'DELETE': delete. Additional functionality includes the ability to grant privileges for administrative operations.

To control which users can connect, each account can be assigned authentication credentials such as a password. The user interface to MySQL accounts consists of SQL statements such as note 'CREATE USER': create-user, note 'GRANT': grant, and note 'REVOKE': revoke. See note account-management-statements::.

The MySQL privilege system ensures that all users may perform only the operations permitted to them. As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify. When you issue requests after connecting, the system grants privileges according to your identity and what you want to do.

MySQL considers both your host name and user name in identifying you because there is no reason to assume that a given user name belongs to the same person on all hosts. For example, the user 'joe' who connects from 'office.example.com' need not be the same person as the user 'joe' who connects from 'home.example.com'. MySQL handles this by enabling you to distinguish users on different hosts that happen to have the same name: You can grant one set of privileges for connections by 'joe' from 'office.example.com', and a different set of privileges for connections by 'joe' from 'home.example.com'. To see what privileges a given account has, use the *note 'SHOW GRANTS': show-grants. statement. For example:

 SHOW GRANTS FOR 'joe'@'office.example.com';
 SHOW GRANTS FOR 'joe'@'home.example.com';

Internally, the server stores privilege information in the grant tables of the 'mysql' system database. The MySQL server reads the contents of these tables into memory when it starts and bases access-control decisions on the in-memory copies of the grant tables.

MySQL access control involves two stages when you run a client program that connects to the server:

Stage 1: The server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password.

Stage 2: Assuming that you can connect, the server checks each statement you issue to determine whether you have sufficient privileges to perform it. For example, if you try to select rows from a table in a database or drop a table from the database, the server verifies that you have the 'SELECT' privilege for the table or the 'DROP' privilege for the database.

For a more detailed description of what happens during each stage, see note connection-access::, and note request-access::. For help in diagnosing privilege-related problems, see *note problems-connecting::.

If your privileges are changed (either by yourself or someone else) while you are connected, those changes do not necessarily take effect immediately for the next statement that you issue. For details about the conditions under which the server reloads the grant tables, see *note privilege-changes::.

There are some things that you cannot do with the MySQL privilege system:

 File: manual.info.tmp, Node: user-names, Next: privileges-provided, Prev: access-control, Up: access-control

6.2.1 Account User Names and Passwords

MySQL stores accounts in the 'user' table of the 'mysql' system database. An account is defined in terms of a user name and the client host or hosts from which the user can connect to the server. For information about account representation in the 'user' table, see *note grant-tables::.

An account may also have authentication credentials such as a password. The credentials are handled by the account authentication plugin. MySQL supports multiple authentication plugins. Some of them use built-in authentication methods, whereas others enable authentication using external authentication methods. See *note pluggable-authentication::.

There are several distinctions between the way user names and passwords are used by MySQL and your operating system:

The MySQL installation process populates the grant tables with an initial 'root' account, as described in note default-privileges::, which also discusses how to assign a password to it. Thereafter, you normally set up, modify, and remove MySQL accounts using statements such as note 'CREATE USER': create-user, note 'DROP USER': drop-user, note 'GRANT': grant, and note 'REVOKE': revoke. See note creating-accounts::, and *note account-management-statements::.

To connect to a MySQL server with a command-line client, specify user name and password options as necessary for the account that you want to use:

 $> mysql --user=finley --password DB_NAME

If you prefer short options, the command looks like this:

 $> mysql -u finley -p DB_NAME

If you omit the password value following the '--password' or '-p' option on the command line (as just shown), the client prompts for one. Alternatively, the password can be specified on the command line:

 $> mysql --user=finley --password=PASSWORD DB_NAME
 $> mysql -u finley -pPASSWORD DB_NAME

If you use the '-p' option, there must be no space between '-p' and the following password value.

Specifying a password on the command line should be considered insecure. See note password-security-user::. To avoid giving the password on the command line, use an option file or a login path file. See note option-files::, and *note mysql-config-editor::.

For additional information about specifying user names, passwords, and other connection parameters, see *note connecting::.

 File: manual.info.tmp, Node: privileges-provided, Next: grant-tables, Prev: user-names, Up: access-control

6.2.2 Privileges Provided by MySQL

The privileges granted to a MySQL account determine which operations the account can perform. MySQL privileges differ in the contexts in which they apply and at different levels of operation:

Information about account privileges is stored in the grant tables in the 'mysql' system database. For a description of the structure and contents of these tables, see note grant-tables::. The MySQL server reads the contents of the grant tables into memory when it starts, and reloads them under the circumstances indicated in note privilege-changes::. The server bases access-control decisions on the in-memory copies of the grant tables.

Important:

Some MySQL releases introduce changes to the grant tables to add new privileges or features. To make sure that you can take advantage of any new capabilities, update your grant tables to the current structure whenever you upgrade MySQL. See *note upgrading::.

The following sections summarize the available privileges, provide more detailed descriptions of each privilege, and offer usage guidelines.

Summary of Available Privileges

The following table shows the privilege names used in note 'GRANT': grant. and note 'REVOKE': revoke. statements, along with the column name associated with each privilege in the grant tables and the context in which the privilege applies.

Permissible Privileges for GRANT and REVOKE

Privilege Grant Table Column Context

'ALL [PRIVILEGES]'

Synonym for 'all Server administration privileges'

'ALTER'

'Alter_priv' Tables

'ALTER ROUTINE'

'Alter_routine_priv' Stored routines

'CREATE'

'Create_priv' Databases, tables, or indexes

'CREATE ROUTINE'

'Create_routine_priv' Stored routines

'CREATE TABLESPACE'

'Create_tablespace_priv'Server administration

'CREATE TEMPORARY TABLES'

'Create_tmp_table_priv'Tables

'CREATE USER'

'Create_user_priv' Server administration

'CREATE VIEW'

'Create_view_priv' Views

'DELETE'

'Delete_priv' Tables

'DROP'

'Drop_priv' Databases, tables, or views

'EVENT'

'Event_priv' Databases

'EXECUTE'

'Execute_priv' Stored routines

'FILE'

'File_priv' File access on server host

'GRANT OPTION'

'Grant_priv' Databases, tables, or stored routines

'INDEX'

'Index_priv' Tables

'INSERT'

'Insert_priv' Tables or columns

'LOCK TABLES'

'Lock_tables_priv' Databases

'PROCESS'

'Process_priv' Server administration

'PROXY'

See 'proxies_priv' Server administration table

'REFERENCES'

'References_priv' Databases or tables

'RELOAD'

'Reload_priv' Server administration

'REPLICATION CLIENT'

'Repl_client_priv' Server administration

'REPLICATION SLAVE'

'Repl_slave_priv' Server administration

'SELECT'

'Select_priv' Tables or columns

'SHOW DATABASES'

'Show_db_priv' Server administration

'SHOW VIEW'

'Show_view_priv' Views

'SHUTDOWN'

'Shutdown_priv' Server administration

'SUPER'

'Super_priv' Server administration

'TRIGGER'

'Trigger_priv' Tables

'UPDATE'

'Update_priv' Tables or columns

'USAGE'

Synonym for 'no Server administration privileges'

Privilege Descriptions

The following list provides general descriptions of each privilege available in MySQL. Particular SQL statements might have more specific privilege requirements than indicated here. If so, the description for the statement in question provides the details.

Privilege-Granting Guidelines

It is a good idea to grant to an account only those privileges that it needs. You should exercise particular caution in granting the 'FILE' and administrative privileges:

 File: manual.info.tmp, Node: grant-tables, Next: account-names, Prev: privileges-provided, Up: access-control

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 *note system-schema::.

The discussion here describes the underlying structure of the grant tables and how the server uses their contents when interacting with clients. However, normally you do not modify the grant tables directly. Modifications occur indirectly when you use account-management statements such as note 'CREATE USER': create-user, note 'GRANT': grant, and note 'REVOKE': revoke. to set up accounts and control the privileges available to each one. See note account-management-statements::. When you use such statements to perform account manipulations, the server modifies the grant tables on your behalf.

Note:

Direct modification of grant tables using statements such as note 'INSERT': insert, note 'UPDATE': update, or *note 'DELETE': 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.

As of MySQL 5.7.18, for any operation that modifies a grant table, the server checks whether the table has the expected structure and produces an error if not. To update the tables to the expected structure, perform the MySQL upgrade procedure. See *note upgrading::.

Grant Table Overview

These 'mysql' database tables contain grant information:

Each grant table contains scope columns and privilege columns:

In addition, a grant table may contain columns used for purposes other than scope or privilege assessment.

The server uses the grant tables in the following manner:

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 note 'mysqladmin flush-privileges': mysqladmin. or note 'mysqladmin reload': mysqladmin. command. Changes to the grant tables take effect as indicated in *note privilege-changes::.

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 *note 'SHOW GRANTS': 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:

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

To display nonprivilege properties of an account, use *note 'SHOW CREATE USER': show-create-user.:

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

The user and db Grant Tables

The server uses the 'user' and 'db' tables in the 'mysql' database at both the first and second stages of access control (see *note access-control::). The columns in the 'user' and 'db' tables are shown here.

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'

Security columns

'ssl_type'

'ssl_cipher'

'x509_issuer'

'x509_subject'

'plugin'

'authentication_string'

'password_expired'

'password_last_changed'

'password_lifetime'

'account_locked'

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.

To address this problem, see *note account-upgrades::.

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 note 'ALTER USER': 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 note 'ALTER USER': alter-user. statement to establish a new account password.

Note:

Although it is possible to 'reset' an expired password by setting it to its current value, it is preferable, as a matter of good policy, to choose a different password.

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

'password_last_changed' is updated by the note 'CREATE USER': create-user, note 'ALTER USER': alter-user, and note 'SET PASSWORD': set-password. statements, and by note 'GRANT': 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 *note account-locking::).

The tables_priv and columns_priv Grant Tables

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.

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.

The procs_priv Grant Table

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.

procs_priv Table Columns

Table Name 'procs_priv'

Scope 'Host' columns

           'Db'
           
           'User'
           
           'Routine_name'
           
           'Routine_type'

Privilege 'Proc_priv' columns

Other 'Timestamp' columns

           'Grantor'

The 'Routine_type' column is an *note 'ENUM': 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 Grant Table

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

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 *note proxy-users::.

Grant Table Scope Column Properties

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.

Grant Table Scope Column Lengths

Column Name Maximum Permitted Characters

'Host', 'Proxied_host' 60

'User', 'Proxied_user' 32

'Password' 41

'Db' 64

'Table_name' 64

'Column_name' 64

'Routine_name' 64

'Host' and 'Proxied_host' values are converted to lowercase before being stored in the grant tables.

For access-checking purposes, comparisons of 'User', 'Proxied_user', 'Password', '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.

Grant Table Privilege Column Properties

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 *note 'SET': 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.

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.

 File: manual.info.tmp, Node: account-names, Next: connection-access, Prev: grant-tables, Up: access-control

6.2.4 Specifying Account Names

MySQL account names consist of a user name and a host name, which enables creation of distinct accounts for users with the same user name who connect from different hosts. This section describes the syntax for account names, including special values and wildcard rules.

Account names appear in SQL statements such as note 'CREATE USER': create-user, note 'GRANT': grant, and *note 'SET PASSWORD': set-password. and follow these rules:

MySQL stores account names in grant tables in the 'mysql' system database using separate columns for the user name and host name parts:

For additional detail about the properties of user names and host names as stored in the grant tables, such as maximum length, see *note grant-tables-scope-column-properties::.

User names and host names have certain special values or wildcard conventions, as described following.

The user name part of an account name is either a nonblank value that literally matches the user name for incoming connection attempts, or a blank value (the empty string) that matches any user name. An account with a blank user name is an anonymous user. To specify an anonymous user in SQL statements, use a quoted empty user name part, such as '''@'localhost''.

The host name part of an account name can take many forms, and wildcards are permitted:

The server performs matching of host values in account names against the client host using the value returned by the system DNS resolver for the client host name or IP address. Except in the case that the account host value is specified using netmask notation, the server performs this comparison as a string match, even for an account host value given as an IP address. This means that you should specify account host values in the same format used by DNS. Here are examples of problems to watch out for:

To avoid problems like these, it is advisable to check the format in which your DNS returns host names and addresses. Use values in the same format in MySQL account names.

 File: manual.info.tmp, Node: connection-access, Next: request-access, Prev: account-names, Up: access-control

6.2.5 Access Control, Stage 1: Connection Verification

When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on these conditions:

The server checks credentials first, then account locking state. A failure at either step causes the server to deny access to you completely. Otherwise, the server accepts the connection, and then enters Stage 2 and waits for requests.

The server performs identity and credentials checking using columns in the 'user' table, accepting the connection only if these conditions are satisfied:

Your identity is based on two pieces of information:

If the 'User' column value is nonblank, the user name in an incoming connection must match exactly. If the 'User' value is blank, it matches any user name. If the 'user' table row that matches an incoming connection has a blank user name, the user is considered to be an anonymous user with no name, not a user with the name that the client actually specified. This means that a blank user name is used for all further access checking for the duration of the connection (that is, during Stage 2).

The 'authentication_string' column can be blank. This is not a wildcard and does not mean that any password matches. It means that the user must connect without specifying a password. The authentication method implemented by the plugin that authenticates the client may or may not use the password in the 'authentication_string' column. In this case, it is possible that an external password is also used to authenticate to the MySQL server.

Nonblank password values stored in the 'authentication_string' column of the 'user' table are encrypted. MySQL does not store passwords as cleartext for anyone to see. Rather, the password supplied by a user who is attempting to connect is encrypted (using the password hashing method implemented by the account authentication plugin). The encrypted password then is used during the connection process when checking whether the password is correct. This is done without the encrypted password ever traveling over the connection. See *note user-names::.

From the MySQL server's point of view, the encrypted password is the real password, so you should never give anyone access to it. In particular, do not give nonadministrative users read access to tables in the 'mysql' system database.

The following table shows how various combinations of 'User' and 'Host' values in the 'user' table apply to incoming connections.

'User' 'Host' Value Permissible Connections Value

''fred''

''h1.example.net'''fred', connecting from 'h1.example.net'

''''

''h1.example.net''Any user, connecting from 'h1.example.net'

''fred''

''%'' 'fred', connecting from any host

''''

''%'' Any user, connecting from any host

''fred''

''%.example.net'''fred', connecting from any host in the 'example.net' domain

''fred''

''x.example.%'''fred', connecting from 'x.example.net', 'x.example.com', 'x.example.edu', and so on; this is probably not useful

''fred''

''198.51.100.177'''fred', connecting from the host with IP address '198.51.100.177'

''fred''

''198.51.100.%'''fred', connecting from any host in the '198.51.100' class C subnet

''fred''

''198.51.100.0/255.255.255.0''Same as previous example

It is possible for the client host name and user name of an incoming connection to match more than one row in the 'user' table. The preceding set of examples demonstrates this: Several of the entries shown match a connection from 'h1.example.net' by 'fred'.

When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:

The server uses sorting rules that order rows with the most-specific 'Host' values first:

Non-TCP (socket file, named pipe, and shared memory) connections are treated as local connections and match a host part of 'localhost' if there are any such accounts, or host parts with wildcards that match 'localhost' otherwise (for example, 'local%', 'l%', '%').

Rows with the same 'Host' value are ordered with the most-specific 'User' values first. A blank 'User' value means 'any user' and is least specific, so for rows with the same 'Host' value, nonanonymous users sort before anonymous users.

For rows with equally-specific 'Host' and 'User' values, the order is nondeterministic.

To see how this works, suppose that the 'user' table looks like this:

 +-----------+----------+-
 | Host      | User     | ...
 +-----------+----------+-
 | %         | root     | ...
 | %         | jeffrey  | ...
 | localhost | root     | ...
 | localhost |          | ...
 +-----------+----------+-

When the server reads the table into memory, it sorts the rows using the rules just described. The result after sorting looks like this:

 +-----------+----------+-
 | Host      | User     | ...
 +-----------+----------+-
 | localhost | root     | ...
 | localhost |          | ...
 | %         | jeffrey  | ...
 | %         | root     | ...
 +-----------+----------+-

When a client attempts to connect, the server looks through the sorted rows and uses the first match found. For a connection from 'localhost' by 'jeffrey', two of the rows from the table match: the one with 'Host' and 'User' values of ''localhost'' and '''', and the one with values of ''%'' and ''jeffrey''. The ''localhost'' row appears first in sorted order, so that is the one the server uses.

Here is another example. Suppose that the 'user' table looks like this:

 +----------------+----------+-
 | Host           | User     | ...
 +----------------+----------+-
 | %              | jeffrey  | ...
 | h1.example.net |          | ...
 +----------------+----------+-

The sorted table looks like this:

 +----------------+----------+-
 | Host           | User     | ...
 +----------------+----------+-
 | h1.example.net |          | ...
 | %              | jeffrey  | ...
 +----------------+----------+-

The first row matches a connection by any user from 'h1.example.net', whereas the second row matches a connection by 'jeffrey' from any host.

Note:

It is a common misconception to think that, for a given user name, all rows that explicitly name that user are used first when the server attempts to find a match for the connection. This is not true. The preceding example illustrates this, where a connection from 'h1.example.net' by 'jeffrey' is first matched not by the row containing ''jeffrey'' as the 'User' column value, but by the row with no user name. As a result, 'jeffrey' is authenticated as an anonymous user, even though he specified a user name when connecting.

If you are able to connect to the server, but your privileges are not what you expect, you probably are being authenticated as some other account. To find out what account the server used to authenticate you, use the 'CURRENT_USER()' function. (See *note information-functions::.) It returns a value in 'USER_NAME@HOST_NAME' format that indicates the 'User' and 'Host' values from the matching 'user' table row. Suppose that 'jeffrey' connects and issues the following query:

 mysql> SELECT CURRENT_USER();
 +----------------+
 | CURRENT_USER() |
 +----------------+
 | @localhost     |
 +----------------+

The result shown here indicates that the matching 'user' table row had a blank 'User' column value. In other words, the server is treating 'jeffrey' as an anonymous user.

Another way to diagnose authentication problems is to print out the 'user' table and sort it by hand to see where the first match is being made.

 File: manual.info.tmp, Node: request-access, Next: creating-accounts, Prev: connection-access, Up: access-control

6.2.6 Access Control, Stage 2: Request Verification

After the server accepts a connection, it enters Stage 2 of access control. For each request that you issue through the connection, the server determines what operation you want to perform, then checks whether your privileges are sufficient. This is where the privilege columns in the grant tables come into play. These privileges can come from any of the 'user', 'db', 'tables_priv', 'columns_priv', or 'procs_priv' tables. (You may find it helpful to refer to *note grant-tables::, which lists the columns present in each grant table.)

The 'user' table grants global privileges. The 'user' table row for an account indicates the account privileges that apply on a global basis no matter what the default database is. For example, if the 'user' table grants you the 'DELETE' privilege, you can delete rows from any table in any database on the server host. It is wise to grant privileges in the 'user' table only to people who need them, such as database administrators. For other users, leave all privileges in the 'user' table set to ''N'' and grant privileges at more specific levels only (for particular databases, tables, columns, or routines).

The 'db' table grants database-specific privileges. Values in the scope columns of this table can take the following forms:

The server reads the 'db' table into memory and sorts it at the same time that it reads the 'user' table. The server sorts the 'db' table based on the 'Host', 'Db', and 'User' scope columns. As with the 'user' table, sorting puts the most-specific values first and least-specific values last, and when the server looks for matching rows, it uses the first match that it finds.

The 'tables_priv', 'columns_priv', and 'procs_priv' tables grant table-specific, column-specific, and routine-specific privileges. Values in the scope columns of these tables can take the following forms:

The server sorts the 'tables_priv', 'columns_priv', and 'procs_priv' tables based on the 'Host', 'Db', and 'User' columns. This is similar to 'db' table sorting, but simpler because only the 'Host' column can contain wildcards.

The server uses the sorted tables to verify each request that it receives. For requests that require administrative privileges such as 'SHUTDOWN' or 'RELOAD', the server checks only the 'user' table row because that is the only table that specifies administrative privileges. The server grants access if the row permits the requested operation and denies access otherwise. For example, if you want to execute *note 'mysqladmin shutdown': mysqladmin. but your 'user' table row does not grant the 'SHUTDOWN' privilege to you, the server denies access without even checking the 'db' table. (The latter table contains no 'Shutdown_priv' column, so there is no need to check it.)

For database-related requests (note 'INSERT': insert, note 'UPDATE': update, and so on), the server first checks the user's global privileges in the 'user' table row. If the row permits the requested operation, access is granted. If the global privileges in the 'user' table are insufficient, the server determines the user's database-specific privileges from the 'db' table:

After determining the database-specific privileges granted by the 'db' table rows, the server adds them to the global privileges granted by the 'user' table. If the result permits the requested operation, access is granted. Otherwise, the server successively checks the user's table and column privileges in the 'tables_priv' and 'columns_priv' tables, adds those to the user's privileges, and permits or denies access based on the result. For stored-routine operations, the server uses the 'procs_priv' table rather than 'tables_priv' and 'columns_priv'.

Expressed in boolean terms, the preceding description of how a user's privileges are calculated may be summarized like this:

 global privileges
 OR database privileges
 OR table privileges
 OR column privileges
 OR routine privileges

It may not be apparent why, if the global privileges are initially found to be insufficient for the requested operation, the server adds those privileges to the database, table, and column privileges later. The reason is that a request might require more than one type of privilege. For example, if you execute an *note 'INSERT INTO ... SELECT': insert-select. statement, you need both the 'INSERT' and the 'SELECT' privileges. Your privileges might be such that the 'user' table row grants one privilege global and the 'db' table row grants the other specifically for the relevant database. In this case, you have the necessary privileges to perform the request, but the server cannot tell that from either your global or database privileges alone. It must make an access-control decision based on the combined privileges.

 File: manual.info.tmp, Node: creating-accounts, Next: reserved-accounts, Prev: request-access, Up: access-control

6.2.7 Adding Accounts, Assigning Privileges, and Dropping Accounts

To manage MySQL accounts, use the SQL statements intended for that purpose:

Account-management statements cause the server to make appropriate modifications to the underlying grant tables, which are discussed in *note grant-tables::.

Note:

Direct modification of grant tables using statements such as note 'INSERT': insert, note 'UPDATE': update, or *note 'DELETE': 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.

As of MySQL 5.7.18, for any operation that modifies a grant table, the server checks whether the table has the expected structure and produces an error if not. *note 'mysql_upgrade': mysql-upgrade. must be run to update the tables to the expected structure.

Another option for creating accounts is to use the GUI tool MySQL Workbench. Also, several third-party programs offer capabilities for MySQL account administration. 'phpMyAdmin' is one such program.

This section discusses the following topics:

For additional information about the statements discussed here, see *note account-management-statements::.

Creating Accounts and Granting Privileges

The following examples show how to use the *note 'mysql': mysql. client program to set up new accounts. These examples assume that the MySQL 'root' account has the 'CREATE USER' privilege and all privileges that it grants to other accounts.

At the command line, connect to the server as the MySQL 'root' user, supplying the appropriate password at the password prompt:

 $> mysql -u root -p
 Enter password: (ENTER ROOT PASSWORD HERE)

After connecting to the server, you can add new accounts. The following example uses note 'CREATE USER ': create-user. and note 'GRANT': grant. statements to set up four accounts (where you see ''PASSWORD'', substitute an appropriate password):

 CREATE USER 'finley'@'localhost'
   IDENTIFIED BY 'PASSWORD';
 GRANT ALL
   ON *.*
   TO 'finley'@'localhost'
   WITH GRANT OPTION;

 CREATE USER 'finley'@'%.example.com'
   IDENTIFIED BY 'PASSWORD';
 GRANT ALL
   ON *.*
   TO 'finley'@'%.example.com'
   WITH GRANT OPTION;

 CREATE USER 'admin'@'localhost'
   IDENTIFIED BY 'PASSWORD';
 GRANT RELOAD,PROCESS
   ON *.*
   TO 'admin'@'localhost';

 CREATE USER 'dummy'@'localhost';

The accounts created by those statements have the following properties:

The previous example grants privileges at the global level. The next example creates three accounts and grants them access at lower levels; that is, to specific databases or objects within databases. Each account has a user name of 'custom', but the host name parts differ:

 CREATE USER 'custom'@'localhost'
   IDENTIFIED BY 'PASSWORD';
 GRANT ALL
   ON bankaccount.*
   TO 'custom'@'localhost';

 CREATE USER 'custom'@'host47.example.com'
   IDENTIFIED BY 'PASSWORD';
 GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
   ON expenses.*
   TO 'custom'@'host47.example.com';

 CREATE USER 'custom'@'%.example.com'
   IDENTIFIED BY 'PASSWORD';
 GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
   ON customer.addresses
   TO 'custom'@'%.example.com';

The three accounts can be used as follows:

Checking Account Privileges and Properties

To see the privileges for an account, use *note 'SHOW GRANTS': show-grants.:

 mysql> SHOW GRANTS FOR 'admin'@'localhost';
 +-----------------------------------------------------+
 | Grants for admin@localhost                          |
 +-----------------------------------------------------+
 | GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost' |
 +-----------------------------------------------------+

To see nonprivilege properties for an account, use *note 'SHOW CREATE USER': show-create-user.:

 mysql> SHOW CREATE USER 'admin'@'localhost'\G
 *************************** 1. row ***************************
 CREATE USER for admin@localhost: CREATE USER 'admin'@'localhost'
 IDENTIFIED WITH 'mysql_native_password'
 AS '*67ACDEBDAB923990001F0FFB017EB8ED41861105'
 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK

Revoking Account Privileges

To revoke account privileges, use the *note 'REVOKE': revoke. statement. Privileges can be revoked at different levels, just as they can be granted at different levels.

Revoke global privileges:

 REVOKE ALL
   ON *.*
   FROM 'finley'@'%.example.com';

 REVOKE RELOAD
   ON *.*
   FROM 'admin'@'localhost';

Revoke database-level privileges:

 REVOKE CREATE,DROP
   ON expenses.*
   FROM 'custom'@'host47.example.com';

Revoke table-level privileges:

 REVOKE INSERT,UPDATE,DELETE
   ON customer.addresses
   FROM 'custom'@'%.example.com';

To check the effect of privilege revocation, use *note 'SHOW GRANTS': show-grants.:

 mysql> SHOW GRANTS FOR 'admin'@'localhost';
 +---------------------------------------------+
 | Grants for admin@localhost                  |
 +---------------------------------------------+
 | GRANT PROCESS ON *.* TO 'admin'@'localhost' |
 +---------------------------------------------+

Dropping Accounts

To remove an account, use the *note 'DROP USER': drop-user. statement. For example, to drop some of the accounts created previously:

 DROP USER 'finley'@'localhost';
 DROP USER 'finley'@'%.example.com';
 DROP USER 'admin'@'localhost';
 DROP USER 'dummy'@'localhost';

 File: manual.info.tmp, Node: reserved-accounts, Next: privilege-changes, Prev: creating-accounts, Up: access-control

6.2.8 Reserved Accounts

One part of the MySQL installation process is data directory initialization (see *note data-directory-initialization::). During data directory initialization, MySQL creates user accounts that should be considered reserved:

 File: manual.info.tmp, Node: privilege-changes, Next: assigning-passwords, Prev: reserved-accounts, Up: access-control

6.2.9 When Privilege Changes Take Effect

If the *note 'mysqld': mysqld. server is started without the '--skip-grant-tables' option, it reads all grant table contents into memory during its startup sequence. The in-memory tables become effective for access control at that point.

If you modify the grant tables indirectly using an account-management statement, the server notices these changes and loads the grant tables into memory again immediately. Account-management statements are described in note account-management-statements::. Examples include note 'GRANT': grant, note 'REVOKE': revoke, note 'SET PASSWORD': set-password, and *note 'RENAME USER': rename-user.

If you modify the grant tables directly using statements such as note 'INSERT': insert, note 'UPDATE': update, or *note 'DELETE': delete. (which is not recommended), the changes have no effect on privilege checking until you either tell the server to reload the tables or restart it. Thus, if you change the grant tables directly but forget to reload them, the changes have no effect until you restart the server. This may leave you wondering why your changes seem to make no difference!

To tell the server to reload the grant tables, perform a flush-privileges operation. This can be done by issuing a 'FLUSH PRIVILEGES' statement or by executing a note 'mysqladmin flush-privileges': mysqladmin. or note 'mysqladmin reload': mysqladmin. command.

A grant table reload affects privileges for each existing client session as follows:

If the server is started with the '--skip-grant-tables' option, it does not read the grant tables or implement any access control. Any user can connect and perform any operation, which is insecure. To cause a server thus started to read the tables and enable access checking, flush the privileges.

 File: manual.info.tmp, Node: assigning-passwords, Next: password-management, Prev: privilege-changes, Up: access-control

6.2.10 Assigning Account Passwords

Required credentials for clients that connect to the MySQL server can include a password. This section describes how to assign passwords for MySQL accounts.

MySQL stores credentials in the 'user' table in the 'mysql' system database. Operations that assign or modify passwords are permitted only to users with the 'CREATE USER' privilege, or, alternatively, privileges for the 'mysql' database ('INSERT' privilege to create new accounts, 'UPDATE' privilege to modify existing accounts). If the 'read_only' system variable is enabled, use of account-modification statements such as note 'CREATE USER': create-user. or note 'ALTER USER': alter-user. additionally requires the 'SUPER' privilege.

The discussion here summarizes syntax only for the most common password-assignment statements. For complete details on other possibilities, see note create-user::, note alter-user::, note grant::, and note set-password::.

MySQL uses plugins to perform client authentication; see note pluggable-authentication::. In password-assigning statements, the authentication plugin associated with an account performs any hashing required of a cleartext password specified. This enables MySQL to obfuscate passwords prior to storing them in the 'mysql.user' system table. For the statements described here, MySQL automatically hashes the password specified. There are also syntax for note 'CREATE USER': create-user. and *note 'ALTER USER': alter-user. that permits hashed values to be specified literally. For details, see the descriptions of those statements.

To assign a password when you create a new account, use *note 'CREATE USER': create-user. and include an 'IDENTIFIED BY' clause:

 CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'PASSWORD';

note 'CREATE USER': create-user. also supports syntax for specifying the account authentication plugin. See note create-user::.

To assign or change a password for an existing account, use the *note 'ALTER USER': alter-user. statement with an 'IDENTIFIED BY' clause:

 ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'PASSWORD';

If you are not connected as an anonymous user, you can change your own password without naming your own account literally:

 ALTER USER USER() IDENTIFIED BY 'PASSWORD';

To change an account password from the command line, use the *note 'mysqladmin': mysqladmin. command:

 mysqladmin -u USER_NAME -h HOST_NAME password "PASSWORD"

The account for which this command sets the password is the one with a row in the 'mysql.user' system table that matches USER_NAME in the 'User' column and the client host from which you connect in the 'Host' column.

Warning:

Setting a password using *note 'mysqladmin': mysqladmin. should be considered insecure. On some systems, your password becomes visible to system status programs such as 'ps' that may be invoked by other users to display command lines. MySQL clients typically overwrite the command-line password argument with zeros during their initialization sequence. However, there is still a brief interval during which the value is visible. Also, on some systems this overwriting strategy is ineffective and the password remains visible to 'ps'. (SystemV Unix systems and perhaps others are subject to this problem.)

If you are using MySQL Replication, be aware that, currently, a password used by a replica as part of a *note 'CHANGE MASTER TO': change-master-to. statement is effectively limited to 32 characters in length; if the password is longer, any excess characters are truncated. This is not due to any limit imposed by the MySQL Server generally, but rather is an issue specific to MySQL Replication. (For more information, see Bug #43439.)

 File: manual.info.tmp, Node: password-management, Next: expired-password-handling, Prev: assigning-passwords, Up: access-control

6.2.11 Password Management

MySQL enables database administrators to expire account passwords manually, and to establish a policy for automatic password expiration. Expiration policy can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.

Internal Versus External Credentials Storage

Some authentication plugins store account credentials internally to MySQL, in the 'mysql.user' system table:

The discussion in this section applies to such authentication plugins because the password-management capabilities described here are based on internal credentials storage handled by MySQL itself.

Other authentication plugins store account credentials externally to MySQL. For accounts that use plugins that perform authentication against an external credentials system, password management must be handled externally against that system as well.

For information about individual authentication plugins, see *note authentication-plugins::.

Password Expiration Policy

To expire an account password manually, use the *note 'ALTER USER': alter-user. statement:

 ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;

This operation marks the password expired in the corresponding 'mysql.user' system table row.

Password expiration according to policy is automatic and is based on password age, which for a given account is assessed from the date and time of its most recent password change. The 'mysql.user' system table indicates for each account when its password was last changed, and the server automatically treats the password as expired at client connection time if its age is greater than its permitted lifetime. This works with no explicit manual password expiration.

To establish automatic password-expiration policy globally, use the 'default_password_lifetime' system variable. Its default value is 0, which disables automatic password expiration. If the value of 'default_password_lifetime' is a positive integer N, it indicates the permitted password lifetime, such that passwords must be changed every N days.

Note:

Prior to 5.7.11, the default 'default_password_lifetime' value is 360 (passwords must be changed approximately once per year). For such versions, be aware that, if you make no changes to the 'default_password_lifetime' variable or to individual user accounts, each user password expires after 360 days and the account starts running in restricted mode. Clients that connect to the server using the account then get an error indicating that the password must be changed: 'ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.'

However, this is easy to miss for clients that automatically connect to the server, such as connections made from scripts. To avoid having such clients suddenly stop working due to a password expiring, make sure to change the password expiration settings for those clients, like this:

 ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER

Alternatively, set the 'default_password_lifetime' variable to '0', thus disabling automatic password expiration for all users.

Examples:

The global password-expiration policy applies to all accounts that have not been set to override it. To establish policy for individual accounts, use the 'PASSWORD EXPIRE' options of the note 'CREATE USER': create-user. and note 'ALTER USER': alter-user. statements. See note create-user::, and note alter-user::.

Example account-specific statements:

When a client successfully connects, the server determines whether the account password has expired:

If the password is expired (whether manually or automatically), the server either disconnects the client or restricts the operations permitted to it (see *note expired-password-handling::). Operations performed by a restricted client result in an error until the user establishes a new account password:

 mysql> SELECT 1;
 ERROR 1820 (HY000): You must reset your password using ALTER USER
 statement before executing this statement.

 mysql> ALTER USER USER() IDENTIFIED BY 'PASSWORD';
 Query OK, 0 rows affected (0.01 sec)

 mysql> SELECT 1;
 +---+
 | 1 |
 +---+
 | 1 |
 +---+
 1 row in set (0.00 sec)

This restricted mode of operation permits note 'SET': set-variable. statements, which is useful before MySQL 5.7.6 if note 'SET PASSWORD': set-password. must be used instead of *note 'ALTER USER': alter-user. and the account password has a hashing format that requires 'old_passwords' to be set to a value different from its default.

After the client resets the password, the server restores normal access for the session, as well as for subsequent connections that use the account. It is also possible for an administrative user to reset the account password, but any existing restricted sessions for that account remain restricted. A client using the account must disconnect and reconnect before statements can be executed successfully.

Note:

Although it is possible to 'reset' an expired password by setting it to its current value, it is preferable, as a matter of good policy, to choose a different password.

 File: manual.info.tmp, Node: expired-password-handling, Next: pluggable-authentication, Prev: password-management, Up: access-control

6.2.12 Server Handling of Expired Passwords

MySQL provides password-expiration capability, which enables database administrators to require that users reset their password. Passwords can be expired manually, and on the basis of a policy for automatic expiration (see *note password-management::).

The *note 'ALTER USER': alter-user. statement enables account password expiration. For example:

 ALTER USER 'myuser'@'localhost' PASSWORD EXPIRE;

For each connection that uses an account with an expired password, the server either disconnects the client or restricts the client to 'sandbox mode,' in which the server permits the client to perform only those operations necessary to reset the expired password. Which action is taken by the server depends on both client and server settings, as discussed later.

If the server disconnects the client, it returns an 'ER_MUST_CHANGE_PASSWORD_LOGIN' (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_must_change_password_login) error:

 $> mysql -u myuser -p
 Password: ******
 ERROR 1862 (HY000): Your password has expired. To log in you must
 change it using a client that supports expired passwords.

If the server restricts the client to sandbox mode, these operations are permitted within the client session:

For any operation not permitted within the session, the server returns an 'ER_MUST_CHANGE_PASSWORD' (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_must_change_password) error:

 mysql> USE performance_schema;
 ERROR 1820 (HY000): You must reset your password using ALTER USER
 statement before executing this statement.

 mysql> SELECT 1;
 ERROR 1820 (HY000): You must reset your password using ALTER USER
 statement before executing this statement.

That is what normally happens for interactive invocations of the *note 'mysql': mysql. client because by default such invocations are put in sandbox mode. To resume normal functioning, select a new password.

For noninteractive invocations of the note 'mysql': mysql. client (for example, in batch mode), the server normally disconnects the client if the password is expired. To permit noninteractive note 'mysql': mysql. invocations to stay connected so that the password can be changed (using the statements permitted in sandbox mode), add the '--connect-expired-password' option to the *note 'mysql': mysql. command.

As mentioned previously, whether the server disconnects an expired-password client or restricts it to sandbox mode depends on a combination of client and server settings. The following discussion describes the relevant settings and how they interact.

Note:

This discussion applies only for accounts with expired passwords. If a client connects using a nonexpired password, the server handles the client normally.

On the client side, a given client indicates whether it can handle sandbox mode for expired passwords. For clients that use the C client library, there are two ways to do this:

Other MySQL Connectors have their own conventions for indicating readiness to handle sandbox mode. See the documentation for the Connector in which you are interested.

On the server side, if a client indicates that it can handle expired passwords, the server puts it in sandbox mode.

If a client does not indicate that it can handle expired passwords (or uses an older version of the client library that cannot so indicate), the server action depends on the value of the 'disconnect_on_expired_password' system variable:

 File: manual.info.tmp, Node: pluggable-authentication, Next: proxy-users, Prev: expired-password-handling, Up: access-control

6.2.13 Pluggable Authentication

When a client connects to the MySQL server, the server uses the user name provided by the client and the client host to select the appropriate account row from the 'mysql.user' system table. The server then authenticates the client, determining from the account row which authentication plugin applies to the client:

Pluggable authentication enables these important capabilities:

Note:

If you start the server with the '--skip-grant-tables' option, authentication plugins are not used even if loaded because the server performs no client authentication and permits any client to connect. Because this is insecure, you might want to use '--skip-grant-tables' in conjunction with enabling the 'skip_networking' system variable to prevent remote clients from connecting.

Available Authentication Plugins

MySQL 5.7 provides these authentication plugins:

Note:

For information about current restrictions on the use of pluggable authentication, including which connectors support which plugins, see *note pluggable-authentication-restrictions::.

Third-party connector developers should read that section to determine the extent to which a connector can take advantage of pluggable authentication capabilities and what steps to take to become more compliant.

If you are interested in writing your own authentication plugins, see Writing Authentication Plugins (https://dev.mysql.com/doc/extending-mysql/5.7/en/writing-authentication-plugins.html).

Authentication Plugin Usage

This section provides general instructions for installing and using authentication plugins. For instructions specific to a given plugin, see the section that describes that plugin under *note authentication-plugins::.

In general, pluggable authentication uses a pair of corresponding plugins on the server and client sides, so you use a given authentication method like this:

In the case that an account uses an authentication method that is the default for both the server and the client program, the server need not communicate to the client which client-side plugin to use, and a round trip in client/server negotiation can be avoided. This is true for accounts that use native MySQL authentication.

For standard MySQL clients such as note 'mysql': mysql. and note 'mysqladmin': mysqladmin, the '--default-auth=PLUGIN_NAME' option can be specified on the command line as a hint about which client-side plugin the program can expect to use, although the server overrides this if the server-side plugin associated with the user account requires a different client-side plugin.

If the client program does not find the client-side plugin library file, specify a '--plugin-dir=DIR_NAME' option to indicate the plugin library directory location.

Restrictions on Pluggable Authentication

The first part of this section describes general restrictions on the applicability of the pluggable authentication framework described at *note pluggable-authentication::. The second part describes how third-party connector developers can determine the extent to which a connector can take advantage of pluggable authentication capabilities and what steps to take to become more compliant.

The term 'native authentication' used here refers to authentication against passwords stored in the 'mysql.user' system table. This is the same authentication method provided by older MySQL servers, before pluggable authentication was implemented. 'Windows native authentication' refers to authentication using the credentials of a user who has already logged in to Windows, as implemented by the Windows Native Authentication plugin ('Windows plugin' for short).

General Pluggable Authentication Restrictions

Pluggable Authentication and Third-Party Connectors

Third-party connector developers can use the following guidelines to determine readiness of a connector to take advantage of pluggable authentication capabilities and what steps to take to become more compliant:

 File: manual.info.tmp, Node: proxy-users, Next: account-locking, Prev: pluggable-authentication, Up: access-control

6.2.14 Proxy Users

The MySQL server authenticates client connections using authentication plugins. The plugin that authenticates a given connection may request that the connecting (external) user be treated as a different user for privilege-checking purposes. This enables the external user to be a proxy for the second user; that is, to assume the privileges of the second user:

This section describes how the proxy user capability works. For general information about authentication plugins, see note pluggable-authentication::. For information about specific plugins, see note authentication-plugins::. For information about writing authentication plugins that support proxy users, see Implementing Proxy User Support in Authentication Plugins (https://dev.mysql.com/doc/extending-mysql/5.7/en/writing-authentication-plugins-proxy-users.html).

Requirements for Proxy User Support

For proxying to occur for a given authentication plugin, these conditions must be satisfied:

The proxy mechanism permits mapping only the external client user name to the proxied user name. There is no provision for mapping host names:

Simple Proxy User Example

Consider the following account definitions:

 -- create proxy account
 CREATE USER 'employee_ext'@'localhost'
   IDENTIFIED WITH my_auth_plugin
   AS 'MY_AUTH_STRING';

 -- create proxied account and grant its privileges;
 -- use mysql_no_login plugin to prevent direct login
 CREATE USER 'employee'@'localhost'
   IDENTIFIED WITH mysql_no_login;
 GRANT ALL
   ON employees.*
   TO 'employee'@'localhost';

 -- grant to proxy account the
 -- PROXY privilege for proxied account
 GRANT PROXY
   ON 'employee'@'localhost'
   TO 'employee_ext'@'localhost';

When a client connects as 'employee_ext' from the local host, MySQL uses the plugin named 'my_auth_plugin' to perform authentication. Suppose that 'my_auth_plugin' returns a user name of 'employee' to the server, based on the content of ''MY_AUTH_STRING'' and perhaps by consulting some external authentication system. The name 'employee' differs from 'employee_ext', so returning 'employee' serves as a request to the server to treat the 'employee_ext' external user, for purposes of privilege checking, as the 'employee' local user.

In this case, 'employee_ext' is the proxy user and 'employee' is the proxied user.

The server verifies that proxy authentication for 'employee' is possible for the 'employee_ext' user by checking whether 'employee_ext' (the proxy user) has the 'PROXY' privilege for 'employee' (the proxied user). If this privilege has not been granted, an error occurs. Otherwise, 'employee_ext' assumes the privileges of 'employee'. The server checks statements executed during the client session by 'employee_ext' against the privileges granted to 'employee'. In this case, 'employee_ext' can access tables in the 'employees' database.

The proxied account, 'employee', uses the 'mysql_no_login' authentication plugin to prevent clients from using the account to log in directly. (This assumes that the plugin is installed. For instructions, see note no-login-pluggable-authentication::.) For alternative methods of protecting proxied accounts against direct use, see note preventing-proxied-account-direct-login::.

When proxying occurs, the 'USER()' and 'CURRENT_USER()' functions can be used to see the difference between the connecting user (the proxy user) and the account whose privileges apply during the current session (the proxied user). For the example just described, those functions return these values:

 mysql> SELECT USER(), CURRENT_USER();
 +------------------------+--------------------+
 | USER()                 | CURRENT_USER()     |
 +------------------------+--------------------+
 | employee_ext@localhost | employee@localhost |
 +------------------------+--------------------+

In the *note 'CREATE USER': create-user. statement that creates the proxy user account, the 'IDENTIFIED WITH' clause that names the proxy-supporting authentication plugin is optionally followed by an 'AS 'AUTH_STRING'' clause specifying a string that the server passes to the plugin when the user connects. If present, the string provides information that helps the plugin determine how to map the proxy (external) client user name to a proxied user name. It is up to each plugin whether it requires the 'AS' clause. If so, the format of the authentication string depends on how the plugin intends to use it. Consult the documentation for a given plugin for information about the authentication string values it accepts.

Preventing Direct Login to Proxied Accounts

Proxied accounts generally are intended to be used only by means of proxy accounts. That is, clients connect using a proxy account, then are mapped onto and assume the privileges of the appropriate proxied user.

There are multiple ways to ensure that a proxied account cannot be used directly:

Granting and Revoking the PROXY Privilege

The 'PROXY' privilege is needed to enable an external user to connect as and have the privileges of another user. To grant this privilege, use the *note 'GRANT': grant. statement. For example:

 GRANT PROXY ON 'PROXIED_USER' TO 'PROXY_USER';

The statement creates a row in the 'mysql.proxies_priv' grant table.

At connect time, PROXY_USER must represent a valid externally authenticated MySQL user, and PROXIED_USER must represent a valid locally authenticated user. Otherwise, the connection attempt fails.

The corresponding *note 'REVOKE': revoke. syntax is:

 REVOKE PROXY ON 'PROXIED_USER' FROM 'PROXY_USER';

MySQL note 'GRANT': grant. and note 'REVOKE': revoke. syntax extensions work as usual. Examples:

 -- grant PROXY to multiple accounts
 GRANT PROXY ON 'a' TO 'b', 'c', 'd';

 -- revoke PROXY from multiple accounts
 REVOKE PROXY ON 'a' FROM 'b', 'c', 'd';

 -- grant PROXY to an account and enable the account to grant
 -- PROXY to the proxied account
 GRANT PROXY ON 'a' TO 'd' WITH GRANT OPTION;

 -- grant PROXY to default proxy account
 GRANT PROXY ON 'a' TO ''@'';

The 'PROXY' privilege can be granted in these cases:

The initial 'root' account created during MySQL installation has the 'PROXY ... WITH GRANT OPTION' 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. For example, 'root' can do this:

 CREATE USER 'admin'@'localhost'
   IDENTIFIED BY 'ADMIN_PASSWORD';
 GRANT PROXY
   ON ''@''
   TO 'admin'@'localhost'
   WITH GRANT OPTION;

Those statements create an 'admin' user that can manage all 'GRANT PROXY' mappings. For example, 'admin' can do this:

 GRANT PROXY ON sally TO joe;

Default Proxy Users

To specify that some or all users should connect using a given authentication plugin, create a 'blank' MySQL account with an empty user name and host name ('''@'''), associate it with that plugin, and let the plugin return the real authenticated user name (if different from the blank user). Suppose that there exists a plugin named 'ldap_auth' that implements LDAP authentication and maps connecting users onto either a developer or manager account. To set up proxying of users onto these accounts, use the following statements:

 -- create default proxy account
 CREATE USER ''@''
   IDENTIFIED WITH ldap_auth
   AS 'O=Oracle, OU=MySQL';

 -- create proxied accounts; use
 -- mysql_no_login plugin to prevent direct login
 CREATE USER 'developer'@'localhost'
   IDENTIFIED WITH mysql_no_login;
 CREATE USER 'manager'@'localhost'
   IDENTIFIED WITH mysql_no_login;

 -- grant to default proxy account the
 -- PROXY privilege for proxied accounts
 GRANT PROXY
   ON 'manager'@'localhost'
   TO ''@'';
 GRANT PROXY
   ON 'developer'@'localhost'
   TO ''@'';

Now assume that a client connects as follows:

 $> mysql --user=myuser --password ...
 Enter password: MYUSER_PASSWORD

The server does not find 'myuser' defined as a MySQL user, but because there is a blank user account ('''@''') that matches the client user name and host name, the server authenticates the client against that account: The server invokes the 'ldap_auth' authentication plugin and passes 'myuser' and MYUSER_PASSWORD to it as the user name and password.

If the 'ldap_auth' plugin finds in the LDAP directory that MYUSER_PASSWORD is not the correct password for 'myuser', authentication fails and the server rejects the connection.

If the password is correct and 'ldap_auth' finds that 'myuser' is a developer, it returns the user name 'developer' to the MySQL server, rather than 'myuser'. Returning a user name different from the client user name of 'myuser' signals to the server that it should treat 'myuser' as a proxy. The server verifies that '''@''' can authenticate as 'developer' (because '''@''' has the 'PROXY' privilege to do so) and accepts the connection. The session proceeds with 'myuser' having the privileges of the 'developer' proxied user. (These privileges should be set up by the DBA using *note 'GRANT': grant. statements, not shown.) The 'USER()' and 'CURRENT_USER()' functions return these values:

 mysql> SELECT USER(), CURRENT_USER();
 +------------------+---------------------+
 | USER()           | CURRENT_USER()      |
 +------------------+---------------------+
 | myuser@localhost | developer@localhost |
 +------------------+---------------------+

If the plugin instead finds in the LDAP directory that 'myuser' is a manager, it returns 'manager' as the user name and the session proceeds with 'myuser' having the privileges of the 'manager' proxied user.

 mysql> SELECT USER(), CURRENT_USER();
 +------------------+-------------------+
 | USER()           | CURRENT_USER()    |
 +------------------+-------------------+
 | myuser@localhost | manager@localhost |
 +------------------+-------------------+

For simplicity, external authentication cannot be multilevel: Neither the credentials for 'developer' nor those for 'manager' are taken into account in the preceding example. However, they are still used if a client tries to connect and authenticate directly as the 'developer' or 'manager' account, which is why those proxied accounts should be protected against direct login (see *note preventing-proxied-account-direct-login::).

Default Proxy User and Anonymous User Conflicts

If you intend to create a default proxy user, check for other existing 'match any user' accounts that take precedence over the default proxy user because they can prevent that user from working as intended.

In the preceding discussion, the default proxy user account has '''' in the host part, which matches any host. If you set up a default proxy user, take care to also check whether nonproxy accounts exist with the same user part and ''%'' in the host part, because ''%'' also matches any host, but has precedence over '''' by the rules that the server uses to sort account rows internally (see *note connection-access::).

Suppose that a MySQL installation includes these two accounts:

 -- create default proxy account
 CREATE USER ''@''
   IDENTIFIED WITH some_plugin
   AS 'SOME_AUTH_STRING';
 -- create anonymous account
 CREATE USER ''@'%'
   IDENTIFIED BY 'ANON_USER_PASSWORD';

The first account ('''@''') is intended as the default proxy user, used to authenticate connections for users who do not otherwise match a more-specific account. The second account ('''@'%'') is an anonymous-user account, which might have been created, for example, to enable users without their own account to connect anonymously.

Both accounts have the same user part (''''), which matches any user. And each account has a host part that matches any host. Nevertheless, there is a priority in account matching for connection attempts because the matching rules sort a host of ''%'' ahead of ''''. For accounts that do not match any more-specific account, the server attempts to authenticate them against '''@'%'' (the anonymous user) rather than '''@''' (the default proxy user). As a result, the default proxy account is never used.

To avoid this problem, use one of the following strategies:

Server Support for Proxy User Mapping

Some authentication plugins implement proxy user mapping for themselves (for example, the PAM and Windows authentication plugins). Other authentication plugins do not support proxy users by default. Of these, some can request that the MySQL server itself map proxy users according to granted proxy privileges: 'mysql_native_password', 'sha256_password'. If the 'check_proxy_users' system variable is enabled, the server performs proxy user mapping for any authentication plugins that make such a request:

For example, to enable all the preceding capabilities, start the server with these lines in the 'my.cnf' file:

 [mysqld]
 check_proxy_users=ON
 mysql_native_password_proxy_users=ON
 sha256_password_proxy_users=ON

Assuming that the relevant system variables have been enabled, create the proxy user as usual using *note 'CREATE USER': create-user, then grant it the 'PROXY' privilege to a single other account to be treated as the proxied user. When the server receives a successful connection request for the proxy user, it finds that the user has the 'PROXY' privilege and uses it to determine the proper proxied user.

 -- create proxy account
 CREATE USER 'proxy_user'@'localhost'
   IDENTIFIED WITH mysql_native_password
   BY 'PASSWORD';

 -- create proxied account and grant its privileges;
 -- use mysql_no_login plugin to prevent direct login
 CREATE USER 'proxied_user'@'localhost'
   IDENTIFIED WITH mysql_no_login;
 -- grant privileges to proxied account
 GRANT ...
   ON ...
   TO 'proxied_user'@'localhost';

 -- grant to proxy account the
 -- PROXY privilege for proxied account
 GRANT PROXY
   ON 'proxied_user'@'localhost'
   TO 'proxy_user'@'localhost';

To use the proxy account, connect to the server using its name and password:

 $> mysql -u proxy_user -p
 Enter password: (ENTER PROXY_USER PASSWORD HERE)

Authentication succeeds, the server finds that 'proxy_user' has the 'PROXY' privilege for 'proxied_user', and the session proceeds with 'proxy_user' having the privileges of 'proxied_user'.

Proxy user mapping performed by the server is subject to these restrictions:

Proxy User System Variables

Two system variables help trace the proxy login process:

 File: manual.info.tmp, Node: account-locking, Next: user-resources, Prev: proxy-users, Up: access-control

6.2.15 Account Locking

MySQL supports locking and unlocking user accounts using the 'ACCOUNT LOCK' and 'ACCOUNT UNLOCK' clauses for the note 'CREATE USER': create-user. and note 'ALTER USER': alter-user. statements:

Account locking state is recorded in the 'account_locked' column of the 'mysql.user' system table. The output from *note 'SHOW CREATE USER': show-create-user. indicates whether an account is locked or unlocked.

If a client attempts to connect to a locked account, the attempt fails. The server increments the 'Locked_connects' status variable that indicates the number of attempts to connect to a locked account, returns an 'ER_ACCOUNT_HAS_BEEN_LOCKED' (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_account_has_been_locked) error, and writes a message to the error log:

 Access denied for user 'USER_NAME'@'HOST_NAME'.
 Account is locked.

Locking an account does not affect being able to connect using a proxy user that assumes the identity of the locked account. It also does not affect the ability to execute stored programs or views that have a 'DEFINER' attribute naming the locked account. That is, the ability to use a proxied account or stored programs or views is not affected by locking the account.

The account-locking capability depends on the presence of the 'account_locked' column in the 'mysql.user' system table. For upgrades from MySQL versions older than 5.7.6, perform the MySQL upgrade procedure to ensure that this column exists. See *note upgrading::. For nonupgraded installations that have no 'account_locked' column, the server treats all accounts as unlocked, and using the 'ACCOUNT LOCK' or 'ACCOUNT UNLOCK' clauses produces an error.

 File: manual.info.tmp, Node: user-resources, Next: problems-connecting, Prev: account-locking, Up: access-control

6.2.16 Setting Account Resource Limits

One means of restricting client use of MySQL server resources is to set the global 'max_user_connections' system variable to a nonzero value. This limits the number of simultaneous connections that can be made by any given account, but places no limits on what a client can do once connected. In addition, setting 'max_user_connections' does not enable management of individual accounts. Both types of control are of interest to MySQL administrators.

To address such concerns, MySQL permits limits for individual accounts on use of these server resources:

Any statement that a client can issue counts against the query limit, unless its results are served from the query cache. Only statements that modify databases or tables count against the update limit.

An 'account' in this context corresponds to a row in the 'mysql.user' system table. That is, a connection is assessed against the 'User' and 'Host' values in the 'user' table row that applies to the connection. For example, an account ''usera'@'%.example.com'' corresponds to a row in the 'user' table that has 'User' and 'Host' values of 'usera' and '%.example.com', to permit 'usera' to connect from any host in the 'example.com' domain. In this case, the server applies resource limits in this row collectively to all connections by 'usera' from any host in the 'example.com' domain because all such connections use the same account.

Before MySQL 5.0, an 'account' was assessed against the actual host from which a user connects. This older method of accounting may be selected by starting the server with the '--old-style-user-limits' option. In this case, if 'usera' connects simultaneously from 'host1.example.com' and 'host2.example.com', the server applies the account resource limits separately to each connection. If 'usera' connects again from 'host1.example.com', the server applies the limits for that connection together with the existing connection from that host.

To establish resource limits for an account at account-creation time, use the note 'CREATE USER': create-user. statement. To modify the limits for an existing account, use note 'ALTER USER': alter-user. Provide a 'WITH' clause that names each resource to be limited. The default value for each limit is zero (no limit). For example, to create a new account that can access the 'customer' database, but only in a limited fashion, issue these statements:

 mysql> CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank'
     ->     WITH MAX_QUERIES_PER_HOUR 20
     ->          MAX_UPDATES_PER_HOUR 10
     ->          MAX_CONNECTIONS_PER_HOUR 5
     ->          MAX_USER_CONNECTIONS 2;

The limit types need not all be named in the 'WITH' clause, but those named can be present in any order. The value for each per-hour limit should be an integer representing a count per hour. For 'MAX_USER_CONNECTIONS', the limit is an integer representing the maximum number of simultaneous connections by the account. If this limit is set to zero, the global 'max_user_connections' system variable value determines the number of simultaneous connections. If 'max_user_connections' is also zero, there is no limit for the account.

To modify limits for an existing account, use an *note 'ALTER USER': alter-user. statement. The following statement changes the query limit for 'francis' to 100:

 mysql> ALTER USER 'francis'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;

The statement modifies only the limit value specified and leaves the account otherwise unchanged.

To remove a limit, set its value to zero. For example, to remove the limit on how many times per hour 'francis' can connect, use this statement:

 mysql> ALTER USER 'francis'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;

As mentioned previously, the simultaneous-connection limit for an account is determined from the 'MAX_USER_CONNECTIONS' limit and the 'max_user_connections' system variable. Suppose that the global 'max_user_connections' value is 10 and three accounts have individual resource limits specified as follows:

 ALTER USER 'user1'@'localhost' WITH MAX_USER_CONNECTIONS 0;
 ALTER USER 'user2'@'localhost' WITH MAX_USER_CONNECTIONS 5;
 ALTER USER 'user3'@'localhost' WITH MAX_USER_CONNECTIONS 20;

'user1' has a connection limit of 10 (the global 'max_user_connections' value) because it has a 'MAX_USER_CONNECTIONS' limit of zero. 'user2' and 'user3' have connection limits of 5 and 20, respectively, because they have nonzero 'MAX_USER_CONNECTIONS' limits.

The server stores resource limits for an account in the 'user' table row corresponding to the account. The 'max_questions', 'max_updates', and 'max_connections' columns store the per-hour limits, and the 'max_user_connections' column stores the 'MAX_USER_CONNECTIONS' limit. (See *note grant-tables::.)

Resource-use counting takes place when any account has a nonzero limit placed on its use of any of the resources.

As the server runs, it counts the number of times each account uses resources. If an account reaches its limit on number of connections within the last hour, the server rejects further connections for the account until that hour is up. Similarly, if the account reaches its limit on the number of queries or updates, the server rejects further queries or updates until the hour is up. In all such cases, the server issues appropriate error messages.

Resource counting occurs per account, not per client. For example, if your account has a query limit of 50, you cannot increase your limit to 100 by making two simultaneous client connections to the server. Queries issued on both connections are counted together.

The current per-hour resource-use counts can be reset globally for all accounts, or individually for a given account:

Per-hour counter resets do not affect the 'MAX_USER_CONNECTIONS' limit.

All counts begin at zero when the server starts. Counts do not carry over through server restarts.

For the 'MAX_USER_CONNECTIONS' limit, an edge case can occur if the account currently has open the maximum number of connections permitted to it: A disconnect followed quickly by a connect can result in an error ('ER_TOO_MANY_USER_CONNECTIONS' (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_too_many_user_connections) or 'ER_USER_LIMIT_REACHED' (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_user_limit_reached)) if the server has not fully processed the disconnect by the time the connect occurs. When the server finishes disconnect processing, another connection is once more permitted.

 File: manual.info.tmp, Node: problems-connecting, Next: account-activity-auditing, Prev: user-resources, Up: access-control

6.2.17 Troubleshooting Problems Connecting to MySQL

If you encounter problems when you try to connect to the MySQL server, the following items describe some courses of action you can take to correct the problem.

 File: manual.info.tmp, Node: account-activity-auditing, Prev: problems-connecting, Up: access-control

6.2.18 SQL-Based Account Activity Auditing

Applications can use the following guidelines to perform SQL-based auditing that ties database activity to MySQL accounts.

MySQL accounts correspond to rows in the 'mysql.user' system table. When a client connects successfully, the server authenticates the client to a particular row in this table. The 'User' and 'Host' column values in this row uniquely identify the account and correspond to the ''USER_NAME'@'HOST_NAME'' format in which account names are written in SQL statements.

The account used to authenticate a client determines which privileges the client has. Normally, the 'CURRENT_USER()' function can be invoked to determine which account this is for the client user. Its value is constructed from the 'User' and 'Host' columns of the 'user' table row for the account.

However, there are circumstances under which the 'CURRENT_USER()' value corresponds not to the client user but to a different account. This occurs in contexts when privilege checking is not based the client's account:

In those contexts, privilege checking is done against the 'DEFINER' account and 'CURRENT_USER()' refers to that account, not to the account for the client who invoked the stored routine or view or who caused the trigger to activate. To determine the invoking user, you can call the 'USER()' function, which returns a value indicating the actual user name provided by the client and the host from which the client connected. However, this value does not necessarily correspond directly to an account in the 'user' table, because the 'USER()' value never contains wildcards, whereas account values (as returned by 'CURRENT_USER()') may contain user name and host name wildcards.

For example, a blank user name matches any user, so an account of '''@'localhost'' enables clients to connect as an anonymous user from the local host with any user name. In this case, if a client connects as 'user1' from the local host, 'USER()' and 'CURRENT_USER()' return different values:

 mysql> SELECT USER(), CURRENT_USER();
 +-----------------+----------------+
 | USER()          | CURRENT_USER() |
 +-----------------+----------------+
 | user1@localhost | @localhost     |
 +-----------------+----------------+

The host name part of an account can contain wildcards, too. If the host name contains a ''%'' or ''_'' pattern character or uses netmask notation, the account can be used for clients connecting from multiple hosts and the 'CURRENT_USER()' value does not indicate which one. For example, the account ''user2'@'%.example.com'' can be used by 'user2' to connect from any host in the 'example.com' domain. If 'user2' connects from 'remote.example.com', 'USER()' and 'CURRENT_USER()' return different values:

 mysql> SELECT USER(), CURRENT_USER();
 +--------------------------+---------------------+
 | USER()                   | CURRENT_USER()      |
 +--------------------------+---------------------+
 | user2@remote.example.com | user2@%.example.com |
 +--------------------------+---------------------+

If an application must invoke 'USER()' for user auditing (for example, if it does auditing from within triggers) but must also be able to associate the 'USER()' value with an account in the 'user' table, it is necessary to avoid accounts that contain wildcards in the 'User' or 'Host' column. Specifically, do not permit 'User' to be empty (which creates an anonymous-user account), and do not permit pattern characters or netmask notation in 'Host' values. All accounts must have a nonempty 'User' value and literal 'Host' value.

With respect to the previous examples, the '''@'localhost'' and ''user2'@'%.example.com'' accounts should be changed not to use wildcards:

 RENAME USER ''@'localhost' TO 'user1'@'localhost';
 RENAME USER 'user2'@'%.example.com' TO 'user2'@'remote.example.com';

If 'user2' must be able to connect from several hosts in the 'example.com' domain, there should be a separate account for each host.

To extract the user name or host name part from a 'CURRENT_USER()' or 'USER()' value, use the 'SUBSTRING_INDEX()' function:

 mysql> SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',1);
 +---------------------------------------+
 | SUBSTRING_INDEX(CURRENT_USER(),'@',1) |
 +---------------------------------------+
 | user1                                 |
 +---------------------------------------+

 mysql> SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',-1);
 +----------------------------------------+
 | SUBSTRING_INDEX(CURRENT_USER(),'@',-1) |
 +----------------------------------------+
 | localhost                              |
 +----------------------------------------+

 File: manual.info.tmp, Node: encrypted-connections, Next: security-plugins, Prev: access-control, Up: security