Chapter 6 Security


内容表

6.1 General Security Issues
6.1.1 Security Guidelines
6.1.2 Keeping Passwords Secure
6.1.3 Making MySQL Secure Against Attackers
6.1.4 Security-Related mysqld Options and Variables
6.1.5 How to Run MySQL as a Normal User
6.1.6 Security Issues with LOAD DATA LOCAL
6.1.7 Client Programming Security Guidelines
6.2 The MySQL Access Privilege System
6.2.1 Privileges Provided by MySQL
6.2.2 Static Versus Dynamic Privileges
6.2.3 Grant Tables
6.2.4 Specifying Account Names
6.2.5 Specifying Role Names
6.2.6 Access Control, Stage 1: Connection Verification
6.2.7 Access Control, Stage 2: Request Verification
6.2.8 When Privilege Changes Take Effect
6.2.9 Troubleshooting Problems Connecting to MySQL
6.3 MySQL User Account Management
6.3.1 User Names and Passwords
6.3.2 Adding User Accounts
6.3.3 Removing User Accounts
6.3.4 Using Roles
6.3.5 Reserved User Accounts
6.3.6 Setting Account Resource Limits
6.3.7 Assigning Account Passwords
6.3.8 Password Management
6.3.9 Server Handling of Expired Passwords
6.3.10 Pluggable Authentication
6.3.11 Proxy Users
6.3.12 User Account Locking
6.3.13 SQL-Based MySQL Account Activity Auditing
6.4 Using Encrypted Connections
6.4.1 Configuring MySQL to Use Encrypted Connections
6.4.2 Command Options for Encrypted Connections
6.4.3 Creating SSL and RSA Certificates and Keys
6.4.4 SSL Library-Dependent Capabilities
6.4.5 Building MySQL with Support for Encrypted Connections
6.4.6 Encrypted Connection Protocols and Ciphers
6.4.7 Connecting to MySQL Remotely from Windows with SSH
6.5 Security Components and Plugins
6.5.1 Authentication Plugins
6.5.2 The Connection-Control Plugins
6.5.3 The Password Validation Component
6.5.4 The MySQL Keyring
6.5.5 MySQL Enterprise Audit
6.5.6 The Audit Message Component
6.5.7 MySQL Enterprise Firewall
6.5.8 MySQL Enterprise Data Masking and De-Identification
6.6 FIPS Support

When thinking about security within a MySQL installation, you should consider a wide range of possible topics and how they affect the security of your MySQL server and related applications:

  • General factors that affect security. These include choosing good passwords, not granting unnecessary privileges to users, ensuring application security by preventing SQL injections and data corruption, and others. See Section 6.1, “General Security Issues” .

  • Security of the installation itself. The data files, log files, and the all the application files of your installation should be protected to ensure that they are not readable or writable by unauthorized parties. For more information, see Section 2.10, “Postinstallation Setup and Testing” .

  • Access control and security within the database system itself, including the users and databases granted with access to the databases, views and stored programs in use within the database. 更多信息,请见 Section 6.2, “The MySQL Access Privilege System” , and Section 6.3, “MySQL User Account Management” .

  • The features offered by security-related plugins. See Section 6.5, “Security Components and Plugins” .

  • Network security of MySQL and your system. The security is related to the grants for individual users, but you may also wish to restrict MySQL so that it is available only locally on the MySQL server host, or to a limited set of other hosts.

  • Ensure that you have adequate and appropriate backups of your database files, configuration and log files. Also be sure that you have a recovery solution in place and test that you are able to successfully recover the information from your backups. See Chapter 7, Backup and Recovery .

6.1 General Security Issues

This section describes general security issues to be aware of and what you can do to make your MySQL installation more secure against attack or misuse. For information specifically about the access control system that MySQL uses for setting up user accounts and checking database access, see Section 2.10, “Postinstallation Setup and Testing” .

For answers to some questions that are often asked about MySQL Server security issues, see Section A.9, “MySQL 8.0 FAQ: Security” .

6.1.1 Security Guidelines

Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes.

In discussing security, it is necessary to consider fully protecting the entire server host (not just the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here.

MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that users can attempt to perform. There is also support for SSL-encrypted connections between MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the same general ideas apply to almost all applications.

When running MySQL, follow these guidelines:

  • Do not ever give anyone (except MySQL root accounts) access to the user table in the mysql system database! This is critical.

  • Learn how the MySQL access privilege system works (see Section 6.2, “The MySQL Access Privilege System” ). Use the GRANT and REVOKE statements to control access to MySQL. Do not grant more privileges than necessary. Never grant privileges to all hosts.

    Checklist:

    • Try mysql -u root . If you are able to connect successfully to the server without being asked for a password, anyone can connect to your MySQL server as the MySQL root user with full privileges! Review the MySQL installation instructions, paying particular attention to the information about setting a root password. See Section 2.10.4, “Securing the Initial MySQL Account” .

    • Use the SHOW GRANTS statement to check which accounts have access to what. Then use the REVOKE statement to remove those privileges that are not necessary.

  • Do not store cleartext passwords in your database. If your computer becomes compromised, the intruder can take the full list of passwords and use them. Instead, use SHA2() or some other one-way hashing function and store the hash value.

    To prevent password recovery using rainbow tables, do not use these functions on a plain password; instead, choose some string to be used as a salt, and use hash(hash(password)+salt) values.

  • Do not choose passwords from dictionaries. Special programs exist to break passwords. Even passwords like xfish98 are very bad. Much better is duag98 which contains the same word fish but typed one key to the left on a standard QWERTY keyboard. Another method is to use a password that is taken from the first characters of each word in a sentence (for example, Four score and seven years ago results in a password of Fsasya ). The password is easy to remember and type, but difficult to guess for someone who does not know the sentence. In this case, you can additionally substitute digits for the number words to obtain the phrase 4 score and 7 years ago , yielding the password 4sa7ya which is even more difficult to guess.

  • Invest in a firewall. This protects you from at least 50% of all types of exploits in any software. Put MySQL behind the firewall or in a demilitarized zone (DMZ).

    Checklist:

    • Try to scan your ports from the Internet using a tool such as nmap . MySQL uses port 3306 by default. This port should not be accessible from untrusted hosts. As a simple way to check whether your MySQL port is open, try the following command from some remote machine, where server_host is the host name or IP address of the host on which your MySQL server runs:

      shell> telnet server_host 3306
                                                      

      If telnet hangs or the connection is refused, the port is blocked, which is how you want it to be. If you get a connection and some garbage characters, the port is open, and should be closed on your firewall or router, unless you really have a good reason to keep it open.

  • Applications that access MySQL should not trust any data entered by users, and should be written using proper defensive programming techniques. See Section 6.1.7, “Client Programming Security Guidelines” .

  • Do not transmit plain (unencrypted) data over the Internet. This information is accessible to everyone who has the time and ability to intercept it and use it for their own purposes. Instead, use an encrypted protocol such as SSL or SSH. MySQL supports internal SSL connections. Another technique is to use SSH port-forwarding to create an encrypted (and compressed) tunnel for the communication.

  • Learn to use the tcpdump and strings utilities. In most cases, you can check whether MySQL data streams are unencrypted by issuing a command like the following:

    shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings
                                        

    This works under Linux and should work with small modifications under other systems.

    Warning

    If you do not see cleartext data, this does not always mean that the information actually is encrypted. If you need high security, consult with a security expert.

6.1.2 Keeping Passwords Secure

Passwords occur in several contexts within MySQL. The following sections provide guidelines that enable end users and administrators to keep these passwords secure and avoid exposing them. In addition, the validate_password plugin can be used to enforce a policy on acceptable password. See Section 6.5.3, “The Password Validation Component” .

6.1.2.1 End-User Guidelines for Password Security

MySQL users should use the following guidelines to keep passwords secure.

When you run a client program to connect to the MySQL server, it is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed here, along with an assessment of the risks of each method. In short, the safest methods are to have the client program prompt for the password or to specify the password in a properly protected option file.

  • Use the mysql_config_editor utility, which enables you to store authentication credentials in an encrypted login path file named .mylogin.cnf . The file can be read later by MySQL client programs to obtain authentication credentials for connecting to MySQL Server. See Section 4.6.7, “ mysql_config_editor — MySQL Configuration Utility” .

  • Use a -p your_pass or --password= your_pass option on the command line. For example:

    shell> mysql -u francis -pfrank db_name
                                            
    Warning

    This is convenient but 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 your operating environment is set up to display your current command in the title bar of your terminal window, the password remains visible as long as the command is running, even if the command has scrolled out of view in the window content area.

  • Use the -p or --password option on the command line with no password value specified. In this case, the client program solicits the password interactively:

    shell> mysql -u francis -p db_name
    Enter password: ********
                                            

    The * characters indicate where you enter your password. The password is not displayed as you enter it.

    It is more secure to enter your password this way than to specify it on the command line because it is not visible to other users. However, this method of entering a password is suitable only for programs that you run interactively. If you want to invoke a client from a script that runs noninteractively, there is no opportunity to enter the password from the keyboard. On some systems, you may even find that the first line of your script is read and interpreted (incorrectly) as your password.

  • Store your password in an option file. For example, on Unix, you can list your password in the [client] section of the .my.cnf file in your home directory:

    [client]
    password=your_pass
                                            

    To keep the password safe, the file should not be accessible to anyone but yourself. To ensure this, set the file access mode to 400 or 600 . For example:

    shell> chmod 600 .my.cnf
                                            

    To name from the command line a specific option file containing the password, use the --defaults-file= file_name option, where file_name is the full path name to the file. For example:

    shell> mysql --defaults-file=/home/francis/mysql-opts
                                            

    Section 4.2.7, “Using Option Files” , discusses option files in more detail.

  • Store your password in the MYSQL_PWD environment variable. See Section 4.9, “MySQL Program Environment Variables” .

    This method of specifying your MySQL password must be considered extremely insecure and should not be used. Some versions of ps include an option to display the environment of running processes. On some systems, if you set MYSQL_PWD , your password is exposed to any other user who runs ps . Even on systems without such a version of ps , it is unwise to assume that there are no other methods by which users can examine process environments.

On Unix, the mysql client writes a record of executed statements to a history file (see Section 4.5.1.3, “mysql Client Logging” ). By default, this file is named .mysql_history and is created in your home directory. Passwords can be written as plain text in SQL statements such as CREATE USER and ALTER USER , so if you use these statements, they are logged in the history file. To keep this file safe, use a restrictive access mode, the same way as described earlier for the .my.cnf file.

If your command interpreter is configured to maintain a history, any file in which the commands are saved will contain MySQL passwords entered on the command line. For example, bash uses ~/.bash_history . Any such file should have a restrictive access mode.

6.1.2.2 Administrator Guidelines for Password Security

Database administrators should use the following guidelines to keep passwords secure.

MySQL stores passwords for user accounts in the mysql.user system table. Access to this table should never be granted to any nonadministrative accounts.

Account passwords can be expired so that users must reset them. See Section 6.3.8, “Password Management” , and Section 6.3.9, “Server Handling of Expired Passwords” .

The validate_password plugin can be used to enforce a policy on acceptable password. See Section 6.5.3, “The Password Validation Component” .

A user who has access to modify the plugin directory (the value of the plugin_dir system variable) or the my.cnf file that specifies the plugin directory location can replace plugins and modify the capabilities provided by plugins, including authentication plugins.

Files such as log files to which passwords might be written should be protected. See Section 6.1.2.3, “Passwords and Logging” .

6.1.2.3 Passwords and Logging

Passwords can be written as plain text in SQL statements such as CREATE USER , GRANT and SET PASSWORD . If such statements are logged by the MySQL server as written, passwords in them become visible to anyone with access to the logs.

Statement logging avoids writing passwords in cleartext for the following statements:

CREATE USER ... IDENTIFIED BY ...
ALTER USER ... IDENTIFIED BY ...
SET PASSWORD ...
SLAVE START ... PASSWORD = ...
CREATE SERVER ... OPTIONS(... PASSWORD ...)
ALTER SERVER ... OPTIONS(... PASSWORD ...)
                            

Passwords in those statements are rewritten to not appear literally in statement text written to the general query log, slow query log, and binary log. Rewriting does not apply to other statements. In particular, INSERT or UPDATE statements for the mysql.user system table that refer to literal passwords are logged as is, so you should avoid such statements. (Direct modification of grant tables is discouraged, anyway.)

For the general query log, password rewriting can be suppressed by starting the server with the --log-raw option. For security reasons, this option is not recommended for production use. For diagnostic purposes, it may be useful to see the exact text of statements as received by the server.

By default, contents of audit log files produced by the audit log plugin are not encrypted and may contain sensitive information, such as the text of SQL statements. For security reasons, audit log files should be written to a directory accessible only to the MySQL server and to users with a legitimate reason to view the log. See Section 6.5.5.3, “MySQL Enterprise Audit Security Considerations” .

Statements received by the server may be rewritten if a query rewrite plugin is installed (see Query Rewrite Plugins ). In this case, the --log-raw option affects statement logging as follows:

  • Without --log-raw , the server logs the statement returned by the query rewrite plugin. This may differ from the statement as received.

  • With --log-raw , the server logs the original statement as received.

An implication of password rewriting is that statements that cannot be parsed (due, for example, to syntax errors) are not written to the general query log because they cannot be known to be password free. Use cases that require logging of all statements including those with errors should use the --log-raw option, bearing in mind that this also bypasses password rewriting.

Password rewriting occurs only when plain text passwords are expected. For statements with syntax that expect a password hash value, no rewriting occurs. If a plain text password is supplied erroneously for such syntax, the password is logged as given, without rewriting.

To guard log files against unwarranted exposure, locate them in a directory that restricts access to the server and the database administrator. If the server logs to tables in the mysql database, grant access to those tables only to the database administrator.

Replication slaves store the password for the replication master in the master info repository, which by default is a table in the mysql database named slave_master_info . The use of a file in the data directory for the master info repository is now deprecated, but still possible (see Section 17.2.4, “Replication Relay and Status Logs” ). Ensure that the master info repository can be accessed only by the database administrator. An alternative to storing the password in the master info repository is to use the START SLAVE statement to specify credentials for connecting to the master.

Use a restricted access mode to protect database backups that include log tables or log files containing passwords.

6.1.3 Making MySQL Secure Against Attackers

When you connect to a MySQL server, you should use a password. The password is not transmitted in clear text over the connection.

All other information is transferred as text, and can be read by anyone who is able to watch the connection. If the connection between the client and the server goes through an untrusted network, and you are concerned about this, you can use the compressed protocol to make traffic much more difficult to decipher. You can also use MySQL's internal SSL support to make the connection even more secure. See Section 6.4, “Using Encrypted Connections” . Alternatively, use SSH to get an encrypted TCP/IP connection between a MySQL server and a MySQL client. You can find an Open Source SSH client at http://www.openssh.org/ , and a comparison of both Open Source and Commercial SSH clients at http://en.wikipedia.org/wiki/Comparison_of_SSH_clients .

To make a MySQL system secure, you should strongly consider the following suggestions:

  • Require all MySQL accounts to have a password. A client program does not necessarily know the identity of the person running it. It is common for client/server applications that the user can specify any user name to the client program. For example, anyone can use the mysql program to connect as any other person simply by invoking it as mysql -u other_user db_name if other_user has no password. If all accounts have a password, connecting using another user's account becomes much more difficult.

    For a discussion of methods for setting passwords, see Section 6.3.7, “Assigning Account Passwords” .

  • Make sure that the only Unix user account with read or write privileges in the database directories is the account that is used for running mysqld .

  • Never run the MySQL server as the Unix root user. This is extremely dangerous, because any user with the FILE privilege is able to cause the server to create files as root (for example, ~root/.bashrc ). To prevent this, mysqld refuses to run as root unless that is specified explicitly using the --user=root option.

    mysqld can (and should) be run as an ordinary, unprivileged user instead. You can create a separate Unix account named mysql to make everything even more secure. Use this account only for administering MySQL. To start mysqld as a different Unix user, add a user option that specifies the user name in the [mysqld] group of the my.cnf option file where you specify server options. For example:

    [mysqld]
    user=mysql
                                        

    This causes the server to start as the designated user whether you start it manually or by using mysqld_safe or mysql.server . For more details, see Section 6.1.5, “How to Run MySQL as a Normal User” .

    Running mysqld as a Unix user other than root does not mean that you need to change the root user name in the user table. User names for MySQL accounts have nothing to do with user names for Unix accounts .

  • Do not grant the FILE privilege to nonadministrative users. Any user that has this privilege can write a file anywhere in the file system with the privileges of the mysqld daemon. This includes the server's data directory containing the files that implement the privilege tables. To make FILE -privilege operations a bit safer, files generated with SELECT ... INTO OUTFILE do not overwrite existing files and are writable by everyone.

    The FILE privilege may also be used to read any file that is world-readable or accessible to the Unix user that the server runs as. With this privilege, you can read any file into a database table. This could be abused, for example, by using LOAD DATA to load /etc/passwd into a table, which then can be displayed with SELECT .

    To limit the location in which files can be read and written, set the secure_file_priv system to a specific directory. See Section 5.1.8, “Server System Variables” .

  • Encrypt binary log files and relay log files. Encryption helps to protect these files and the potentially sensitive data contained in them from being misused by outside attackers, and also from unauthorized viewing by users of the operating system where they are stored. You enable encryption on a MySQL server by setting the binlog_encryption system variable to ON . For more information, see Section 17.3.10, “Encrypting Binary Log Files and Relay Log Files” .

  • Do not grant the PROCESS or SUPER privilege to nonadministrative users. The output of mysqladmin processlist and SHOW PROCESSLIST shows the text of any statements currently being executed, so any user who is permitted to see the server process list might be able to see statements issued by other users.

    mysqld reserves an extra connection for users who have the CONNECTION_ADMIN or SUPER privilege, so that a MySQL root user can log in and check server activity even if all normal connections are in use.

    The SUPER privilege can be used to terminate client connections, change server operation by changing the value of system variables, and control replication servers.

  • Do not permit the use of symlinks to tables. (This capability can be disabled with the --skip-symbolic-links option.) This is especially important if you run mysqld as root , because anyone that has write access to the server's data directory then could delete any file in the system! See Section 8.12.2.2, “Using Symbolic Links for MyISAM Tables on Unix” .

  • Stored programs and views should be written using the security guidelines discussed in Section 24.6, “Access Control for Stored Programs and Views” .

  • If you do not trust your DNS, you should use IP addresses rather than host names in the grant tables. In any case, you should be very careful about creating grant table entries using host name values that contain wildcards.

  • If you want to restrict the number of connections permitted to a single account, you can do so by setting the max_user_connections variable in mysqld . The CREATE USER and ALTER USER statements also support resource control options for limiting the extent of server use permitted to an account. See Section 13.7.1.3, “CREATE USER Syntax” , and Section 13.7.1.1, “ALTER USER Syntax” .

  • If the plugin directory is writable by the server, it may be possible for a user to write executable code to a file in the directory using SELECT ... INTO DUMPFILE . This can be prevented by making plugin_dir read only to the server or by setting --secure-file-priv to a directory where SELECT writes can be made safely.

6.1.4 Security-Related mysqld Options and Variables

The following table shows mysqld options and system variables that affect security. For descriptions of each of these, see Section 5.1.7, “Server Command Options” , and Section 5.1.8, “Server System Variables” .

Table 6.1 Security Option and Variable Summary

Name Cmd-Line Option File System Var Status Var Var Scope Dynamic
allow-suspicious-udfs Yes Yes
automatic_sp_privileges Yes Global Yes
chroot Yes Yes
des-key-file Yes Yes
local_infile Yes Global Yes
old_passwords Yes Both Yes
safe-user-create Yes Yes
secure-auth Yes Yes Global Yes
- Variable : secure_auth Yes Global Yes
secure-file-priv Yes Yes Global No
- Variable : secure_file_priv Yes Global No
skip-grant-tables Yes Yes
skip-name-resolve Yes Yes Global No
- Variable : skip_name_resolve Yes Global No
skip-networking Yes Yes Global No
- Variable : skip_networking Yes Global No
skip-show-database Yes Yes Global No
- Variable : skip_show_database Yes Global No

6.1.5 How to Run MySQL as a Normal User

On Windows, you can run the server as a Windows service using a normal user account.

On Linux, for installations performed using a MySQL repository or RPM packages, the MySQL server mysqld should be started by the local mysql operating system user. Starting by another operating system user is not supported by the init scripts that are included as part of the MySQL repositories.

On Unix (or Linux for installations performed using tar.gz packages) , the MySQL server mysqld can be started and run by any user. However, you should avoid running the server as the Unix root user for security reasons. To change mysqld to run as a normal unprivileged Unix user user_name , you must do the following:

  1. Stop the server if it is running (use mysqladmin shutdown ).

  2. Change the database directories and files so that user_name has privileges to read and write files in them (you might need to do this as the Unix root user):

    shell> chown -R user_name /path/to/mysql/datadir
                                        

    If you do not do this, the server will not be able to access databases or tables when it runs as user_name .

    If directories or files within the MySQL data directory are symbolic links, chown -R might not follow symbolic links for you. If it does not, you will also need to follow those links and change the directories and files they point to.

  3. Start the server as user user_name . Another alternative is to start mysqld as the Unix root user and use the --user= user_name option. mysqld starts up, then switches to run as the Unix user user_name before accepting any connections.

  4. To start the server as the given user automatically at system startup time, specify the user name by adding a user option to the [mysqld] group of the /etc/my.cnf option file or the my.cnf option file in the server's data directory. For example:

    [mysqld]
    user=user_name
                                        

If your Unix machine itself is not secured, you should assign passwords to the MySQL root account in the grant tables. Otherwise, any user with a login account on that machine can run the mysql client with a --user=root option and perform any operation. (It is a good idea to assign passwords to MySQL accounts in any case, but especially so when other login accounts exist on the server host.) See Section 2.10.4, “Securing the Initial MySQL Account” .

6.1.6 Security Issues with LOAD DATA LOCAL

The LOAD DATA statement can load a file located on the server host, or, if the LOCAL keyword is specified, on the client host.

There are two potential security issues with the LOCAL version of LOAD DATA :

  • The transfer of the file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server's choosing rather than the file named by the client in the LOAD DATA statement. Such a server could access any file on the client host to which the client user has read access. (A patched server could in fact reply with a file-transfer request to any statement, not just LOAD DATA LOCAL , so a more fundamental issue is that clients should not connect to untrusted servers.)

  • In a Web environment where the clients are connecting from a Web server, a user could use LOAD DATA LOCAL to read any files that the Web server process has read access to (assuming that a user could run any statement against the SQL server). In this environment, the client with respect to the MySQL server actually is the Web server, not a remote program being run by users who connect to the Web server.

To avoid LOAD DATA issues, clients should avoid using LOCAL . To avoid connecting to untrusted servers, clients can establish a secure connection and verify the server identity by connecting using the --ssl-mode=VERIFY_IDENTITY option and the appropriate CA certificate.

To enable adminstrators and applications to manage the local data loading capability, LOCAL configuration works like this:

  • On the server side:

    • The local_infile system variable controls server-side LOCAL capability. Depending on the local_infile setting, the server refuses or permits local data loading by clients that have LOCAL enabled on the client side. By default, local_infile is disabled.

    • To explicitly cause the server to refuse or permit LOAD DATA LOCAL statements (regardless of how client programs and libraries are configured at build time or runtime), start mysqld with local_infile disabled or enabled, respectively. local_infile can also be set at runtime.

  • On the client side:

    • The ENABLED_LOCAL_INFILE CMake option controls the compiled-in default LOCAL capability for the MySQL client library. Clients that make no explicit arrangements therefore have LOCAL capability disabled or enabled according to the ENABLED_LOCAL_INFILE setting specified at MySQL build time.

      By default, the client library in MySQL binary distributions is compiled with ENABLED_LOCAL_INFILE disabled. If you compile MySQL from source, configure it with ENABLED_LOCAL_INFILE disabled or enabled based on whether clients that make no explicit arrangements should have LOCAL capability disabled or enabled, respectively.

    • Client programs that use the C API can control load data loading explicitly by invoking mysql_options() to disable or enable the MYSQL_OPT_LOCAL_INFILE option. See Section 28.7.7.50, “mysql_options()” .

    • For the mysql client, local data loading is disabled by default. To disable or enable it explicitly, use the --local-infile=0 or --local-infile[=1] option.

    • For the mysqlimport client, local data loading is disabled by default. To disable or enable it explicitly, use the --local=0 or --local[=1] option.

    • If you use LOAD DATA LOCAL in Perl scripts or other programs that read the [client] group from option files, you can add an local-infile option setting to that group. To prevent problems for programs that do not understand this option, specify it using the loose- prefix:

      [client]
      loose-local-infile=0
                                                      

      or:

      [client]
      loose-local-infile=1
                                                      
    • In all cases, successful use of a LOCAL load operation by a client also requires that the server permits it.

If LOCAL capability is disabled, on either the server or client side, a client that attempts to issue a LOAD DATA LOCAL statement receives the following error message:

ERROR 1148: The used command is not allowed with this MySQL version
                        

6.1.7 Client Programming Security Guidelines

Applications that access MySQL should not trust any data entered by users, who can try to trick your code by entering special or escaped character sequences in Web forms, URLs, or whatever application you have built. Be sure that your application remains secure if a user enters something like ; DROP DATABASE mysql; . This is an extreme example, but large security leaks and data loss might occur as a result of hackers using similar techniques, if you do not prepare for them.

A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234 , the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1 . As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotation marks around the numeric constants: SELECT * FROM table WHERE ID='234' . If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing nonnumeric characters from it.

Sometimes people think that if a database contains only publicly available data, it need not be protected. This is incorrect. Even if it is permissible to display any row in the database, you should still protect against denial of service attacks (for example, those that are based on the technique in the preceding paragraph that causes the server to waste resources). Otherwise, your server becomes unresponsive to legitimate users.

Checklist:

  • Enable strict SQL mode to tell the server to be more restrictive of what data values it accepts. See Section 5.1.11, “Server SQL Modes” .

  • Try to enter single and double quotation marks ( ' and " ) in all of your Web forms. If you get any kind of MySQL error, investigate the problem right away.

  • Try to modify dynamic URLs by adding %22 ( " ), %23 ( # ), and %27 ( ' ) to them.

  • Try to modify data types in dynamic URLs from numeric to character types using the characters shown in the previous examples. Your application should be safe against these and similar attacks.

  • Try to enter characters, spaces, and special symbols rather than numbers in numeric fields. Your application should remove them before passing them to MySQL or else generate an error. Passing unchecked values to MySQL is very dangerous!

  • Check the size of data before passing it to MySQL.

  • Have your application connect to the database using a user name different from the one you use for administrative purposes. Do not give your applications any access privileges they do not need.

Many application programming interfaces provide a means of escaping special characters in data values. Properly used, this prevents application users from entering values that cause the application to generate statements that have a different effect than you intend:

  • MySQL C API: Use the mysql_real_escape_string_quote() API call.

  • MySQL++: Use the escape and quote modifiers for query streams.

  • PHP: Use either the mysqli or pdo_mysql extensions, and not the older ext/mysql extension. The preferred API's support the improved MySQL authentication protocol and passwords, as well as prepared statements with placeholders. 另请参阅 Choosing an API .

    If the older ext/mysql extension must be used, then for escaping use the mysql_real_escape_string_quote() function and not mysql_escape_string() or addslashes() because only mysql_real_escape_string_quote() is character set-aware; the other functions can be bypassed when using (invalid) multibyte character sets.

  • Perl DBI: Use placeholders or the quote() method.

  • Ruby DBI: Use placeholders or the quote() method.

  • Java JDBC: Use a PreparedStatement object and placeholders.

Other programming interfaces might have similar capabilities.

6.2 The MySQL Access Privilege System

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 SELECT , INSERT , UPDATE , and DELETE . Additional functionality includes the ability to have anonymous users and to grant privileges for MySQL-specific functions such as LOAD DATA and administrative operations.

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

  • You cannot explicitly specify that a given user should be denied access. That is, you cannot explicitly match a user and then refuse the connection.

  • You cannot specify that a user has privileges to create or drop tables in a database but not to create or drop the database itself.

  • A password applies globally to an account. You cannot associate a password with a specific object such as a database, table, or routine.

The user interface to the MySQL privilege system consists of SQL statements such as CREATE USER , GRANT , and REVOKE . See Section 13.7.1, “Account Management Statements” .

Internally, the server stores privilege information in the grant tables of the mysql system database (that is, in the database named mysql ). 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.

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 SHOW GRANTS statement. For example:

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

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 Section 6.2.6, “Access Control, Stage 1: Connection Verification” , and Section 6.2.7, “Access Control, Stage 2: Request Verification” .

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 Section 6.2.8, “When Privilege Changes Take Effect” .

For general security-related advice, see Section 6.1, “General Security Issues” . For help in diagnosing privilege-related problems, see Section 6.2.9, “Troubleshooting Problems Connecting to MySQL” .

6.2.1 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:

  • Administrative privileges enable users to manage operation of the MySQL server. These privileges are global because they are not specific to a particular database.

  • Database privileges apply to a database and to all objects within it. These privileges can be granted for specific databases, or globally so that they apply to all databases.

  • Privileges for database objects such as tables, indexes, views, and stored routines can be granted for specific objects within a database, for all objects of a given type within a database (for example, all tables in a database), or globally for all objects of a given type in all databases.

Privileges also differ in terms of whether they are static (built in to the server) or dynamic (defined at runtime). Whether a privilege is static or dynamic affects its availability to be granted to user accounts and roles. For information about the differences between static and dynamic privileges, see Section 6.2.2, “Static Versus Dynamic Privileges” .)

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 Section 6.2.3, “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 Section 6.2.8, “When Privilege Changes Take Effect” . 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 Section 4.4.5, “ mysql_upgrade — Check and Upgrade MySQL Tables” .

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 static privilege names used in GRANT and REVOKE statements, along with the column name associated with each privilege in the grant tables and the context in which the privilege applies.

Table 6.2 Permissible Static Privileges for GRANT and REVOKE

Privilege Grant Table Column Context
ALL [PRIVILEGES] Synonym for all privileges Server administration
ALTER Alter_priv Tables
ALTER ROUTINE Alter_routine_priv Stored routines
CREATE Create_priv Databases, tables, or indexes
CREATE ROLE Create_role_priv Server administration
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
DROP ROLE Drop_role_priv Server administration
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 table Server administration
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 privileges Server administration

The following table shows the dynamic privilege names used in GRANT and REVOKE statements, along with the context in which the privilege applies.

Table 6.3 Permissible Dynamic Privileges for GRANT and REVOKE

Privilege Context
APPLICATION_PASSWORD_ADMIN Dual password administration
AUDIT_ADMIN Audit log administration
BACKUP_ADMIN Backup administration
BINLOG_ADMIN Backup and Replication administration
BINLOG_ENCRYPTION_ADMIN Backup and Replication administration
CONNECTION_ADMIN Server administration
ENCRYPTION_KEY_ADMIN Server administration
FIREWALL_ADMIN Firewall administration
FIREWALL_USER Firewall administration
GROUP_REPLICATION_ADMIN Replication administration
PERSIST_RO_VARIABLES_ADMIN Server administration
REPLICATION_SLAVE_ADMIN Replication administration
RESOURCE_GROUP_ADMIN Resource group administration
RESOURCE_GROUP_USER Resource group administration
ROLE_ADMIN Server administration
SESSION_VARIABLES_ADMIN Server administration
SET_USER_ID Server administration
SYSTEM_VARIABLES_ADMIN Server administration
VERSION_TOKEN_ADMIN Server administration
XA_RECOVER_ADMIN Server administration

Static Privilege Descriptions

Static privileges are built in to the server, in contrast to dynamic privileges, which are defined at runtime. The following list describes each static 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.

Dynamic Privilege Descriptions

Dynamic privileges are defined at runtime, in contrast to static privileges, which are built in to the server. The following list describes each dynamic privilege available in MySQL.

Most dynamic privileges are defined at server startup. Others are defined by a particular server component or plugin, as indicated in the privilege descriptions. In such cases, the privilege is unavailable unless the component or plugin that defines it is enabled.

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 can be abused to read into a database table any files that the MySQL server can read on the server host. This includes all world-readable files and files in the server's data directory. The table can then be accessed using SELECT to transfer its contents to the client host.

  • GRANT OPTION enables users to give their privileges to other users. Two users that have different privileges and with the GRANT OPTION privilege are able to combine privileges.

  • ALTER may be used to subvert the privilege system by renaming tables.

  • SHUTDOWN can be abused to deny service to other users entirely by terminating the server.

  • PROCESS can be used to view the plain text of currently executing statements, including statements that set or change passwords.

  • SUPER can be used to terminate other sessions or change how the server operates.

  • Privileges granted for the mysql system database itself can be used to change passwords and other access privilege information:

    • Passwords are stored encrypted, so a malicious user cannot simply read them to know the plain text password. However, a user with write access to the mysql.user system table authentication_string column can change an account's password, and then connect to the MySQL server using that account.

    • INSERT or UPDATE granted for the mysql system database enable a user to add privileges or modify existing privileges, respectively.

    • DROP for the mysql system database enables a user to remote privilege tables, or even the database itself.

6.2.2 Static Versus Dynamic Privileges

MySQL supports static and dynamic privileges:

  • Static privileges are built in to the server. They are always available to be granted to user accounts and cannot be unregistered.

  • Dynamic privileges can be registered and unregistered at runtime. This affects their availability: A dynamic privilege that has not been registered cannot be granted.

For example, the SELECT and INSERT privileges are static and always available, whereas a dynamic privilege becomes available only if the server component that implements it has been enabled.

The remainder of this section describes how dynamic privileges work in MySQL. The discussion uses the term components but applies equally to plugins.

Note

Server administrators should be aware of which server components define dynamic privileges. For MySQL distributions, documentation of components that define dynamic privileges describes those privileges.

Third-party components may also define dynamic privileges; an administrator should understand those privileges and not install components that might conflict or compromise server operation. For example, one component conflicts with another if both define a privilege with the same name. Component developers can reduce the likelihood of this occurrence by choosing privilege names having a prefix based on the component name.

The server maintains the set of registered dynamic privileges internally in memory. Unregistration occurs at server shutdown.

Normally, a server component that defines dynamic privileges registers them when it is installed, during its initialization sequence. When uninstalled, a server component does not unregister its registered dynamic privileges. (This is current practice, not a requirement. That is, components could, but do not, unregister at any time privileges they register.)

No warning or error occurs for attempts to register an already registered dynamic privilege. Consider the following sequence of statements:

INSTALL COMPONENT 'my_component';
UNINSTALL COMPONENT 'my_component';
INSTALL COMPONENT 'my_component';
                        

The first INSTALL COMPONENT statement registers any privileges defined by server component my_component , but UNINSTALL COMPONENT does not unregister them. For the second INSTALL COMPONENT statement, the component privileges it registers are found to be already registered, but no warnings or errors occur.

Dynamic privileges apply only at the global level. The server stores information about current assignments of dynamic privileges to user accounts in the mysql.global_grants system table:

  • The server automatically registers privileges named in global_grants during server startup (unless the --skip-grant-tables option is given).

  • The GRANT and REVOKE statements modify the contents of global_grants .

  • Dynamic privilege assignments listed in global_grants are persistent. They are not removed at server shutdown.

Example: The following statement grants to user u1 the privileges required to control replication (including Group Replication) on a slave server, and to modify system variables:

GRANT REPLICATION_SLAVE_ADMIN, GROUP_REPLICATION_ADMIN, BINLOG_ADMIN
ON *.* TO 'u1'@'localhost';
                        

Granted dynamic privileges appear in the output from the SHOW GRANTS statement and the INFORMATION_SCHEMA USER_PRIVILEGES table.

For GRANT and REVOKE at the global level, any named privileges not recognized as static are checked against the current set of registered dynamic privileges and granted if found. Otherwise, an error occurs to indicate an unknown privilege identifier.

For GRANT and REVOKE the meaning of ALL [PRIVILEGES] at the global level includes all static global privileges, as well as all currently registered dynamic privileges:

  • GRANT ALL at the global level grants all static global privileges and all currently registered dynamic privileges. A dynamic privilege registered subsequent to execution of the GRANT statement is not granted retroactively to any account.

  • REVOKE ALL at the global level revokes all granted static global privileges and all granted dynamic privileges.

The FLUSH PRIVILEGES statement reads the global_grants table for dynamic privilege assignments and registers any unregistered privileges found there.

For descriptions of the dynamic privileges provided by MySQL Server and server components included in MySQL distributions, see Section 6.2.1, “Privileges Provided by MySQL” .

Migrating Accounts from SUPER to Dynamic Privileges

In MySQL 8.0, many operations that previously required the SUPER privilege are also associated with a dynamic privilege of more limited scope. (For descriptions of these privileges, see Section 6.2.1, “Privileges Provided by MySQL” .) Each such operation can be permitted to an account by granting the associated dynamic privilege rather than SUPER . This change improves security by enabling DBAs to avoid granting SUPER and tailor user privileges more closely to the operations permitted. SUPER is now deprecated and will be removed in a future version of MySQL.

When removal of SUPER occurs, operations that formerly required SUPER will fail unless accounts granted SUPER are migrated to the appropriate dynamic privileges. Use the following instructions to accomplish that goal so that accounts are ready prior to SUPER removal:

  1. Execute this query to identify accounts that are granted SUPER :

    SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGES
    WHERE PRIVILEGE_TYPE = 'SUPER';
                                            
  2. For each account identified by the preceding query, determine the operations for which it needs SUPER . Then grant the dynamic privileges corresponding to those operations, and revoke SUPER .

    For example, if 'u1'@'localhost' requires SUPER for binary log purging and system variable modification, these statements make the required changes to the account:

    GRANT BINLOG_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'u1'@'localhost';
    REVOKE SUPER ON *.* FROM 'u1'@'localhost';
                                            

    After you have modified all applicable accounts, the INFORMATION_SCHEMA query in the first step should produce an empty result set.

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:

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

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

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

6.2.4 Specifying Account Names

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

MySQL role names are similar to account names, with some differences described at Section 6.2.5, “Specifying Role Names” .

In SQL statements such as CREATE USER , GRANT , and SET PASSWORD , account names follow these rules:

  • Account name syntax is ' user_name '@' host_name ' .

  • An account name consisting only of a user name is equivalent to ' user_name '@'%' . For example, 'me' is equivalent to 'me'@'%' .

  • The user name and host name need not be quoted if they are legal as unquoted identifiers. Quotes are necessary to specify a user_name string containing special characters (such as space or - ), or a host_name string containing special characters or wildcard characters (such as . or % ); for example, 'test-user'@'%.com' .

  • Quote user names and host names as identifiers or as strings, using either backticks ( ` ), single quotation marks ( ' ), or double quotation marks ( " ). For string-quoting and identifier-quoting guidelines, see Section 9.1.1, “String Literals” , and Section 9.2, “Schema Object Names” .

  • The user name and host name parts, if quoted, must be quoted separately. That is, write 'me'@'localhost' , not 'me@localhost' ; the latter is actually equivalent to 'me@localhost'@'%' .

  • A reference to the CURRENT_USER or CURRENT_USER() function is equivalent to specifying the current client's user name and host name literally.

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

  • The user table contains one row for each account. The User and Host columns store the user name and host name. This table also indicates which global privileges the account has.

  • Other grant tables indicate privileges an account has for databases and objects within databases. These tables have User and Host columns to store the account name. Each row in these tables associates with the account in the user table that has the same User and Host values.

  • For access-checking purposes, comparisons of User values are case-sensitive. Comparisons of Host values are not case sensitive.

For additional detail about grant table structure, see Section 6.2.3, “Grant Tables” .

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 (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:

  • A host value can be a host name or an IP address (IPv4 or IPv6). The name 'localhost' indicates the local host. The IP address '127.0.0.1' indicates the IPv4 loopback interface. The IP address '::1' indicates the IPv6 loopback interface.

  • The % and _ wildcard characters are permitted in host name or IP address values. These have the same meaning as for pattern-matching operations performed with the LIKE operator. For example, a host value of '%' matches any host name, whereas a value of '%.mysql.com' matches any host in the mysql.com domain. '198.51.100.%' matches any host in the 198.51.100 class C network.

    Because IP wildcard values are permitted in host values (for example, '198.51.100.%' to match every host on a subnet), someone could try to exploit this capability by naming a host 198.51.100.somewhere.com . To foil such attempts, MySQL does not perform matching on host names that start with digits and a dot. For example, if a host is named 1.2.example.com , its name never matches the host part of account names. An IP wildcard value can match only IP addresses, not host names.

  • For a host value specified as an IPv4 address, a netmask can be given to indicate how many address bits to use for the network number. Netmask notation cannot be used for IPv6 addresses.

    The syntax is host_ip / netmask . For example:

    CREATE USER 'david'@'198.51.100.0/255.255.255.0';
                                        

    This enables david to connect from any client host having an IP address client_ip for which the following condition is true:

    client_ip & netmask = host_ip
                                        

    That is, for the CREATE USER statement just shown:

    client_ip & 255.255.255.0 = 198.51.100.0
                                        

    IP addresses that satisfy this condition range from 198.51.100.0 to 198.51.100.255 .

    A netmask typically begins with bits set to 1, followed by bits set to 0. Examples:

    • 198.0.0.0/255.0.0.0 : Any host on the 198 class A network

    • 198.51.100.0/255.255.0.0 : Any host on the 198.51 class B network

    • 198.51.100.0/255.255.255.0 : Any host on the 198.51.100 class C network

    • 198.51.100.1 : Only the host with this specific IP address

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:

  • Suppose that a host on the local network has a fully qualified name of host1.example.com . If DNS returns name lookups for this host as host1.example.com , use that name in account host values. If DNS returns just host1 , use host1 instead.

  • If DNS returns the IP address for a given host as 198.51.100.2 , that will match an account host value of 198.51.100.2 but not 198.051.100.2 . Similarly, it will match an account host pattern like 198.51.100.% but not 198.051.100.% .

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.

6.2.5 Specifying Role Names

MySQL role names refer to roles, which are named collections of privileges. For role usage examples, see Section 6.3.4, “Using Roles” .

Role names have syntax and semantics similar to account names ( Section 6.2.4, “Specifying Account Names” ). Role names differ from account names in these respects:

  • The user part of role names cannot be blank. Thus, there is no anonymous role analogous to the concept of anonymous user.

  • As for an account name, omitting the host part of a role name results in a host part of '%' . But unlike '%' in an account name, a host part of '%' in a role name has no wildcard properties. For example, for a name 'me'@'%' used as a role name, the host part ( '%' ) is just a literal value; it has no any host matching property.

  • Netmask notation in the host part of a role name has no significance.

  • An account name is permitted to be CURRENT_USER() in several contexts. A role name is not.

It is possible for a row in the mysql.user system table to serve as both an account and a role. In this case, any special user or host name matching properties do not apply in contexts for which the name is used as a role name. For example, you cannot execute the following statement with the expectation that it will set the current session roles using all roles that have a user part of myrole and any host name:

SET ROLE 'myrole'@'%';
                        

Instead, the statement sets the active role for the session to the role with exactly the name 'myrole'@'%' .

For this reason, role names are often specified using only the user name part and letting the host name part implicitly be '%' . Specifying a role with a non- '%' host part can be useful if you intend to create a name that works both as a role an as a user account that is permitted to connect from the given host.

6.2.6 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:

  • Your identity and whether you can verify your identity by supplying the correct password

  • Whether your account is locked or unlocked

The server checks credentials first, then account locking state. A failure for 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.

Credential checking is performed using the three user table scope columns ( Host , User , and authentication_string ). Locking state is recorded in the user table account_locked column. The server accepts the connection only if the Host and User columns in some user table row match the client host name and user name, the client supplies the password specified in that row, and the account_locked value is 'N' . The rules for permissible Host and User values are given in Section 6.2.4, “Specifying Account Names” . Account locking can be changed with the ALTER USER statement.

Your identity is based on two pieces of information:

  • The client host from which you connect

  • Your MySQL user name

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. If the server authenticates a client using a plugin, the authentication method that the plugin implements 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 authentication_string values in the user table represent encrypted passwords. MySQL does not store passwords in cleartext form 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 Section 6.3.1, “User Names and Passwords” .

From MySQL'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 Permissible Connections
'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:

  • Whenever the server reads the user table into memory, it sorts the rows.

  • When a client attempts to connect, the server looks through the rows in sorted order.

  • The server uses the first row that matches the client host name and user name.

The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 198.51.100.13 and 198.51.100.0/255.255.255.0 are considered equally specific.) The pattern '%' means any host and is least specific. The empty string '' also means any host but sorts after '%' . 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). 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  | ...
+----------------+----------+-
                        

A connection by jeffrey from h1.example.net is matched by the first row, whereas a connection by jeffrey from any host is matched by the second.

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 Section 12.15, “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.

6.2.7 Access Control, Stage 2: Request Verification

After you establish a connection, the server enters Stage 2 of access control. For each request that you issue through that connection, the server determines what operation you want to perform, then checks whether you have sufficient privileges to do so. 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 Section 6.2.3, “Grant Tables” , which lists the columns present in each of the grant tables.)

The user table grants privileges that are assigned to you on a global basis and that apply 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, you should leave all privileges in the user table set to 'N' and grant privileges at more specific levels only. You can grant privileges 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:

  • A blank User value matches the anonymous user. A nonblank value matches literally; there are no wildcards in user names.

  • The wildcard characters % and _ can be used in the Host and Db columns. These have the same meaning as for pattern-matching operations performed with the LIKE operator. If you want to use either character literally when granting privileges, you must escape it with a backslash. For example, to include the underscore character ( _ ) as part of a database name, specify it as \_ in the GRANT statement.

  • A '%' or blank Host value means any host.

  • A '%' or blank Db value means any database.

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 wildcard characters % and _ can be used in the Host column. These have the same meaning as for pattern-matching operations performed with the LIKE operator.

  • A '%' or blank Host value means any host.

  • The Db , Table_name , Column_name , and Routine_name columns cannot contain wildcards or be blank.

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 mysqladmin shutdown but your user table row does not grant the SHUTDOWN privilege to you, the server denies access without even checking the db table. (It contains no Shutdown_priv column, so there is no need to do so.)

For database-related requests ( INSERT , UPDATE , and so on), the server first checks the user's global privileges by looking 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 by checking the db table:

The server looks in the db table for a match on the Host , Db , and User columns. The Host and User columns are matched to the connecting user's host name and MySQL user name. The Db column is matched to the database that the user wants to access. If there is no row for the Host and User , access is denied.

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 AND host privileges)
OR table privileges
OR column privileges
OR routine privileges
                        

It may not be apparent why, if the global user row 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 INSERT INTO ... SELECT statement, you need both the INSERT and the SELECT privileges. Your privileges might be such that the user table row grants one privilege and the db table row grants the other. In this case, you have the necessary privileges to perform the request, but the server cannot tell that from either table by itself; the privileges granted by the rows in both tables must be combined.

6.2.8 When Privilege Changes Take Effect

When mysqld starts, it reads all grant table contents into memory. 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 Section 13.7.1, “Account Management Statements” . Examples include GRANT , REVOKE , SET PASSWORD , and RENAME USER .

If you modify the grant tables directly using statements such as INSERT , UPDATE , or DELETE (which is not recommended), your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables. If you change the grant tables directly but forget to reload them, your 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 mysqladmin flush-privileges or mysqladmin reload command.

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

  • Table and column privilege changes take effect with the client's next request.

  • Database privilege changes take effect the next time the client executes a USE db_name statement.

    Note

    Client applications may cache the database name; thus, this effect may not be visible to them without actually changing to a different database.

  • Global privileges and passwords are unaffected for a connected client. These changes take effect only for subsequent connections.

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

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

  • Make sure that the server is running. If it is not, clients cannot connect to it. For example, if an attempt to connect to the server fails with a message such as one of those following, one cause might be that the server is not running:

    shell> mysql
    ERROR 2003: Can't connect to MySQL server on 'host_name' (111)
    shell> mysql
    ERROR 2002: Can't connect to local MySQL server through socket
    '/tmp/mysql.sock' (111)
                                        
  • It might be that the server is running, but you are trying to connect using a TCP/IP port, named pipe, or Unix socket file different from the one on which the server is listening. To correct this when you invoke a client program, specify a --port option to indicate the proper port number, or a --socket option to indicate the proper named pipe or Unix socket file. To find out where the socket file is, you can use this command:

    shell> netstat -ln | grep mysql
                                        
  • Make sure that the server has not been configured to ignore network connections or (if you are attempting to connect remotely) that it has not been configured to listen only locally on its network interfaces. If the server was started with --skip-networking , it will not accept TCP/IP connections at all. If the server was started with --bind-address=127.0.0.1 , it will listen for TCP/IP connections only locally on the loopback interface and will not accept remote connections.

  • Check to make sure that there is no firewall blocking access to MySQL. Your firewall may be configured on the basis of the application being executed, or the port number used by MySQL for communication (3306 by default). Under Linux or Unix, check your IP tables (or similar) configuration to ensure that the port has not been blocked. Under Windows, applications such as ZoneAlarm or Windows Firewall may need to be configured not to block the MySQL port.

  • The grant tables must be properly set up so that the server can use them for access control. For some distribution types (such as binary distributions on Windows, or RPM distributions on Linux), the installation process initializes the MySQL data directory, including the mysql system database containing the grant tables. For distributions that do not do this, you must initialize the data directory manually. For details, see Section 2.10, “Postinstallation Setup and Testing” .

    To determine whether you need to initialize the grant tables, look for a mysql directory under the data directory. (The data directory normally is named data or var and is located under your MySQL installation directory.) Make sure that you have a file named user.MYD in the mysql database directory. If not, initialize the data directory. After doing so and starting the server, you should be able to connect to the server.

  • After a fresh installation, if you try to log on to the server as root without using a password, you might get the following error message.

    shell> mysql -u root 
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
                                        

    It means a root password has already been assigned during installation and it has to be supplied. See Section 2.10.4, “Securing the Initial MySQL Account” on the different ways the password could have been assigned and, in some cases, how to find it. If you need to reset the root password, see instructions in Section B.6.3.2, “How to Reset the Root Password” . After you have found or reset your password, log on again as root using the --password (or -p ) option:

    shell> mysql -u root -p 
    Enter password:
                                        

    However, the server is going to let you connect as root without using a password if you have initialized MySQL using mysqld --initialize-insecure (see Section 2.10.1.1, “Initializing the Data Directory Manually Using mysqld” for details). That is a security risk, so you should set a password for the root account; see Section 2.10.4, “Securing the Initial MySQL Account” for instructions.

  • If you have updated an existing MySQL installation to a newer version, did you run the mysql_upgrade script? If not, do so. The structure of the grant tables changes occasionally when new capabilities are added, so after an upgrade you should always make sure that your tables have the current structure. For instructions, see Section 4.4.5, “ mysql_upgrade — Check and Upgrade MySQL Tables” .

  • If a client program receives the following error message when it tries to connect, it means that the server expects passwords in a newer format than the client is capable of generating:

    shell> mysql
    Client does not support authentication protocol requested
    by server; consider upgrading MySQL client
                                        
  • Remember that client programs use connection parameters specified in option files or environment variables. If a client program seems to be sending incorrect default connection parameters when you have not specified them on the command line, check any applicable option files and your environment. For example, if you get Access denied when you run a client without any options, make sure that you have not specified an old password in any of your option files!

    You can suppress the use of option files by a client program by invoking it with the --no-defaults option. For example:

    shell> mysqladmin --no-defaults -u root version
                                        

    The option files that clients use are listed in Section 4.2.7, “Using Option Files” . Environment variables are listed in Section 4.9, “MySQL Program Environment Variables” .

  • If you get the following error, it means that you are using an incorrect root password:

    shell> mysqladmin -u root -pxxxx ver
    Access denied for user 'root'@'localhost' (using password: YES)
                                        

    If the preceding error occurs even when you have not specified a password, it means that you have an incorrect password listed in some option file. Try the --no-defaults option as described in the previous item.

    For information on changing passwords, see Section 6.3.7, “Assigning Account Passwords” .

    If you have lost or forgotten the root password, see Section B.6.3.2, “How to Reset the Root Password” .

  • localhost is a synonym for your local host name, and is also the default host to which clients try to connect if you specify no host explicitly.

    You can use a --host=127.0.0.1 option to name the server host explicitly. This will make a TCP/IP connection to the local mysqld server. You can also use TCP/IP by specifying a --host option that uses the actual host name of the local host. In this case, the host name must be specified in a user table row on the server host, even though you are running the client program on the same host as the server.

  • The Access denied error message tells you who you are trying to log in as, the client host from which you are trying to connect, and whether you were using a password. Normally, you should have one row in the user table that exactly matches the host name and user name that were given in the error message. For example, if you get an error message that contains using password: NO , it means that you tried to log in without a password.

  • If you get an Access denied error when trying to connect to the database with mysql -u user_name , you may have a problem with the user table. Check this by executing mysql -u root mysql and issuing this SQL statement:

    SELECT * FROM user;
                                        

    The result should include a row with the Host and User columns matching your client's host name and your MySQL user name.

  • If the following error occurs when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host:

    Host ... is not allowed to connect to this MySQL server
                                        

    You can fix this by setting up an account for the combination of client host name and user name that you are using when trying to connect.

    If you do not know the IP address or host name of the machine from which you are connecting, you should put a row with '%' as the Host column value in the user table. After trying to connect from the client machine, use a SELECT USER() query to see how you really did connect. Then change the '%' in the user table row to the actual host name that shows up in the log. Otherwise, your system is left insecure because it permits connections from any host for the given user name.

    On Linux, another reason that this error might occur is that you are using a binary MySQL version that is compiled with a different version of the glibc library than the one you are using. In this case, you should either upgrade your operating system or glibc , or download a source distribution of MySQL version and compile it yourself. A source RPM is normally trivial to compile and install, so this is not a big problem.

  • If you specify a host name when trying to connect, but get an error message where the host name is not shown or is an IP address, it means that the MySQL server got an error when trying to resolve the IP address of the client host to a name:

    shell> mysqladmin -u root -pxxxx -h some_hostname ver
    Access denied for user 'root'@'' (using password: YES)
                                        

    If you try to connect as root and get the following error, it means that you do not have a row in the user table with a User column value of 'root' and that mysqld cannot resolve the host name for your client:

    Access denied for user ''@'unknown'
                                        

    These errors indicate a DNS problem. To fix it, execute mysqladmin flush-hosts to reset the internal DNS host cache. See Section 8.12.4.2, “DNS Lookup Optimization and the Host Cache” .

    Some permanent solutions are:

    • Determine what is wrong with your DNS server and fix it.

    • Specify IP addresses rather than host names in the MySQL grant tables.

    • Put an entry for the client machine name in /etc/hosts on Unix or \windows\hosts on Windows.

    • Start mysqld with the --skip-name-resolve option.

    • Start mysqld with the --skip-host-cache option.

    • On Unix, if you are running the server and the client on the same machine, connect to localhost . For connections to localhost , MySQL programs attempt to connect to the local server by using a Unix socket file, unless there are connection parameters specified to ensure that the client makes a TCP/IP connection. For more information, see Section 4.2.2, “Connecting to the MySQL Server” .

    • On Windows, if you are running the server and the client on the same machine and the server supports named pipe connections, connect to the host name . (period). Connections to . use a named pipe rather than TCP/IP.

  • If mysql -u root works but mysql -h your_hostname -u root results in Access denied (where your_hostname is the actual host name of the local host), you may not have the correct name for your host in the user table. A common problem here is that the Host value in the user table row specifies an unqualified host name, but your system's name resolution routines return a fully qualified domain name (or vice versa). For example, if you have a row with host 'pluto' in the user table, but your DNS tells MySQL that your host name is 'pluto.example.com' , the row does not work. Try adding a row to the user table that contains the IP address of your host as the Host column value. (Alternatively, you could add a row to the user table with a Host value that contains a wildcard; for example, 'pluto.%' . However, use of Host values ending with % is insecure and is not recommended!)

  • If mysql -u user_name works but mysql -u user_name some_db does not, you have not granted access to the given user for the database named some_db .

  • If mysql -u user_name works when executed on the server host, but mysql -h host_name -u user_name does not work when executed on a remote client host, you have not enabled access to the server for the given user name from the remote host.

  • If you cannot figure out why you get Access denied , remove from the user table all rows that have Host values containing wildcards (rows that contain '%' or '_' characters). A very common error is to insert a new row with Host = '%' and User = ' some_user ' , thinking that this enables you to specify localhost to connect from the same machine. The reason that this does not work is that the default privileges include a row with Host = 'localhost' and User = '' . Because that row has a Host value 'localhost' that is more specific than '%' , it is used in preference to the new row when connecting from localhost ! The correct procedure is to insert a second row with Host = 'localhost' and User = ' some_user ' , or to delete the row with Host = 'localhost' and User = '' . After deleting the row, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables. See also Section 6.2.6, “Access Control, Stage 1: Connection Verification” .

  • If you are able to connect to the MySQL server, but get an Access denied message whenever you issue a SELECT ... INTO OUTFILE or LOAD DATA statement, your row in the user table does not have the FILE privilege enabled.

  • If you change the grant tables directly (for example, by using INSERT , UPDATE , or DELETE statements) and your changes seem to be ignored, remember that you must execute a FLUSH PRIVILEGES statement or a mysqladmin flush-privileges command to cause the server to reload the privilege tables. Otherwise, your changes have no effect until the next time the server is restarted. Remember that after you change the root password with an UPDATE statement, you will not need to specify the new password until after you flush the privileges, because the server will not know you've changed the password yet!

  • If your privileges seem to have changed in the middle of a session, it may be that a MySQL administrator has changed them. Reloading the grant tables affects new client connections, but it also affects existing connections as indicated in Section 6.2.8, “When Privilege Changes Take Effect” .

  • If you have access problems with a Perl, PHP, Python, or ODBC program, try to connect to the server with mysql -u user_name db_name or mysql -u user_name -p your_pass db_name . If you are able to connect using the mysql client, the problem lies with your program, not with the access privileges. (There is no space between -p and the password; you can also use the --password= your_pass syntax to specify the password. If you use the -p or --password option with no password value, MySQL prompts you for the password.)

  • For testing purposes, start the mysqld server with the --skip-grant-tables option. Then you can change the MySQL grant tables and use the SHOW GRANTS statement to check whether your modifications have the desired effect. When you are satisfied with your changes, execute mysqladmin flush-privileges to tell the mysqld server to reload the privileges. This enables you to begin using the new grant table contents without stopping and restarting the server.

  • If everything else fails, start the mysqld server with a debugging option (for example, --debug=d,general,query ). This prints host and user information about attempted connections, as well as information about each command issued. See Section 29.5.3, “The DBUG Package” .

  • If you have any other problems with the MySQL grant tables and feel you must post the problem to the mailing list, always provide a dump of the MySQL grant tables. You can dump the tables with the mysqldump mysql command. To file a bug report, see the instructions at Section 1.7, “How to Report Bugs or Problems” . In some cases, you may need to restart mysqld with --skip-grant-tables to run mysqldump .

6.3 MySQL User Account Management

This section describes how to set up accounts for clients of your MySQL server. It discusses the following topics:

  • The meaning of account names and passwords as used in MySQL and how that compares to names and passwords used by your operating system

  • How to set up new accounts and remove existing accounts

  • How to use roles, which are named collections of privileges

  • How to change passwords

  • Guidelines for using passwords securely

另请参阅 Section 13.7.1, “Account Management Statements” , which describes the syntax and use for all user-management SQL statements.

6.3.1 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 Section 6.2.3, “Grant Tables” .

The account may also have a password. MySQL supports authentication plugins, so it is possible that an account authenticates using some external authentication method. See Section 6.3.10, “Pluggable Authentication” .

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

  • User names, as used by MySQL for authentication purposes, have nothing to do with user names (login names) as used by Windows or Unix. On Unix, most MySQL clients by default try to log in using the current Unix user name as the MySQL user name, but that is for convenience only. The default can be overridden easily, because client programs permit any user name to be specified with a -u or --user option. This means that anyone can attempt to connect to the server using any user name, so you cannot make a database secure in any way unless all MySQL accounts have passwords. Anyone who specifies a user name for an account that has no password is able to connect successfully to the server.

  • MySQL user names can be up to 32 characters long. Operating system user names may be of a different maximum length. For example, Unix user names typically are limited to eight characters.

    Warning

    The limit on MySQL user name length is hardcoded in MySQL servers and clients, and trying to circumvent it by modifying the definitions of the tables in the mysql database does not work .

    You should never alter the structure of tables in the mysql database in any manner whatsoever except by means of the procedure that is described in Section 4.4.5, “ mysql_upgrade — Check and Upgrade MySQL Tables” . Attempting to redefine MySQL's system tables in any other fashion results in undefined (and unsupported!) behavior. The server is free to ignore rows that become malformed as a result of such modifications.

  • To authenticate client connections for accounts that use MySQL native authentication (implemented by the mysql_native_password authentication plugin), the server uses passwords stored in the user table. These passwords are distinct from passwords for logging in to your operating system. There is no necessary connection between the external password you use to log in to a Windows or Unix machine and the password you use to access the MySQL server on that machine.

    If the server authenticates a client using some other plugin, the authentication method that the plugin implements may or may not use a password stored in the user table. In this case, it is possible that an external password is also used to authenticate to the MySQL server.

  • Passwords stored in the user table are encrypted using plugin-specific algorithms.

  • If the user name and password contain only ASCII characters, it is possible to connect to the server regardless of character set settings. To connect when the user name or password contain non-ASCII characters, the client should call the mysql_options() C API function with the MYSQL_SET_CHARSET_NAME option and appropriate character set name as arguments. This causes authentication to take place using the specified character set. Otherwise, authentication will fail unless the server default character set is the same as the encoding in the authentication defaults.

    Standard MySQL client programs support a --default-character-set option that causes mysql_options() to be called as just described. In addition, character set autodetection is supported as described in Section 10.4, “Connection Character Sets and Collations” . For programs that use a connector that is not based on the C API, the connector may provide an equivalent to mysql_options() that can be used instead. Check the connector documentation.

    The preceding notes do not apply for ucs2 , utf16 , and utf32 , which are not permitted as client character sets.

The MySQL installation process populates the grant tables with an initial root account, as described in Section 2.10.4, “Securing the Initial MySQL Account” , which also discusses how to assign passwords to it. Thereafter, you normally set up, modify, and remove MySQL accounts using statements such as CREATE USER , DROP USER , GRANT , and REVOKE . See Section 13.7.1, “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:

shell> mysql --user=finley --password db_name
                        

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

shell> 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:

shell> mysql --user=finley --password=password db_name
shell> 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 Section 6.1.2.1, “End-User Guidelines for Password Security” . You can use an option file or a login path file to avoid giving the password on the command line. See Section 4.2.7, “Using Option Files” , and Section 4.6.7, “ mysql_config_editor — MySQL Configuration Utility” .

For additional information about specifying user names, passwords, and other connection parameters, see Section 4.2.2, “Connecting to the MySQL Server” .

6.3.2 Adding User Accounts

To create MySQL accounts, use the account-management statements intended for creating accounts and establishing their privileges, such as CREATE USER and GRANT . These statements cause the server to make appropriate modifications to the underlying grant tables. All such statements are described in Section 13.7.1, “Account Management Statements” .

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.

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.

The following examples show how to use the mysql client program to set up new accounts. These examples assume that privileges have been set up according to the defaults described in Section 2.10.4, “Securing the Initial MySQL Account” . This means that to make changes, you must connect to the MySQL server as the MySQL root user, which has the CREATE USER privilege.

First, use the mysql program to connect to the server as the MySQL root user:

shell> mysql --user=root mysql
                        

If you have assigned a password to the root account, you must also supply a --password or -p option.

After connecting to the server as root , you can add new accounts. The following example uses CREATE USER and GRANT statements to set up four accounts:

mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'finley'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> CREATE USER 'dummy'@'localhost';
                        

The accounts created by those statements have the following properties:

  • Two accounts have a user name of finley . Both are superuser accounts with full privileges to do anything. The 'finley'@'localhost' account can be used only when connecting from the local host. The 'finley'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.

    The 'finley'@'localhost' account is necessary if there is an anonymous-user account for localhost . Without the 'finley'@'localhost' account, that anonymous-user account takes precedence when finley connects from the local host and finley is treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'finley'@'%' account and thus comes earlier in the user table sort order. ( user table sorting is discussed in Section 6.2.6, “Access Control, Stage 1: Connection Verification” .)

  • The 'admin'@'localhost' account can be used only by admin to connect from the local host. It is granted the RELOAD and PROCESS administrative privileges. These privileges enable the admin user to execute the mysqladmin reload , mysqladmin refresh , and mysqladmin flush- xxx commands, as well as mysqladmin processlist . No privileges are granted for accessing any databases. You could add such privileges using GRANT statements.

  • The 'dummy'@'localhost' account has no password (which is insecure and not recommended). This account can be used only to connect from the local host. No privileges are granted. It is assumed that you will grant specific privileges to the account using GRANT statements.

To see the privileges for an account, use 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 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
                        

The next examples create three accounts and grant them access to specific databases. Each of them has a user name of custom and password of password :

mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON bankaccount.*
    ->     TO 'custom'@'localhost';
mysql> CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'password';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON expenses.*
    ->     TO 'custom'@'host47.example.com';
mysql> CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'password';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON customer.*
    ->     TO 'custom'@'%.example.com';
                        

The three accounts can be used as follows:

  • The first account can access the bankaccount database, but only from the local host.

  • The second account can access the expenses database, but only from the host host47.example.com .

  • The third account can access the customer database, from any host in the example.com domain. This account has access from all machines in the domain due to use of the % wildcard character in the host part of the account name.

6.3.3 Removing User Accounts

To remove an account, use the DROP USER statement, which is described in Section 13.7.1.5, “DROP USER Syntax” . For example:

mysql> DROP USER 'jeffrey'@'localhost';
                        

6.3.4 Using Roles

A MySQL role is a named collection of privileges. Like user accounts, roles can have privileges granted to and revoked from them.

A user account can be granted roles, which grants to the account the privileges associated with each role. This enables assignment of sets of privileges to accounts and provides a convenient alternative to granting individual privileges, both for conceptualizing desired privilege assignments and implementing them.

The following list summarizes role-management capabilities provided by MySQL:

For descriptions of individual role-manipulation statements (including the privileges required to use them), see Section 13.7.1, “Account Management Statements” . The following discussion provides examples of role usage. Unless otherwise specified, SQL statements shown here should be executed using a MySQL account with sufficient administrative privileges, such as the root account.

Creating Roles and Granting Privileges to Them

Consider this scenario:

  • An application uses a database named app_db .

  • Associated with the application, there can be accounts for developers who create and maintain the application, and for users who interact with it.

  • Developers need full access to the database. Some users need only read access, others need read/write access.

To avoid granting privileges individually to possibly many user accounts, create roles as names for the required privilege sets. This makes it easy to grant the required privileges to user accounts, by granting the appropriate roles.

To create the roles, use the CREATE ROLE statement:

CREATE ROLE 'app_developer', 'app_read', 'app_write';
                            

Role names are much like user account names and consist of a user part and host part in ' user_name '@' host_name ' format. The host part, if omitted, defaults to '%' . The user and host parts can be unquoted unless they contain special characters such as - or % . Unlike account names, the user part of role names cannot be blank. For additional information, see Section 6.2.5, “Specifying Role Names” .

To assign privileges to the roles, execute GRANT statements using the same syntax as for assigning privileges to user accounts:

GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
                            

Now suppose that initially you require one developer account, two user accounts that need read-only access, and one user account that needs read/write access. Use CREATE USER to create the accounts:

CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';
                            

To assign each user account its required privileges, you could use GRANT statements of the same form as just shown, but that requires enumerating individual privileges for each user. Instead, use an alternative GRANT syntax that permits granting roles rather than privileges:

GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';
                            

The GRANT statement for the rw_user1 account grants the read and write roles, which combine to provide the required read and write privileges.

The GRANT syntax for granting roles to an account differs from the syntax for granting privileges: There is an ON clause to assign privileges, whereas there is no ON clause to assign roles. Because the syntaxes are distinct, you cannot mix assigning privileges and roles in the same statement. (It is permitted to assign both privileges and roles to an account, but you must use separate GRANT statements, each with syntax appropriate to what is to be granted.)

A role when created is locked, has no password, and is assigned the default authentication plugin. (These role attributes can be changed later with the ALTER USER statement, by users who have the global CREATE USER privilege.)

While locked, a role cannot be used to authenticate to the server. If unlocked, a role can be used to authenticate. This is because roles and users are both authorization identifiers with much in common and little to distinguish them. See also User and Role Interchangeability .

Defining Mandatory Roles

It is possible to specify roles as mandatory by naming them in the value of the mandatory_roles system variable. The server treats a mandatory role as granted to all users, so that it need not be granted explicitly to any account.

To specify mandatory roles at server startup, define mandatory_roles in your server my.cnf file:

[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.example.com'
                            

To set and persist mandatory_roles at runtime, use a statement like this:

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';
                            

SET PERSIST sets the value for the running MySQL instance. It also saves the value to be used for subsequent server restarts; see Section 13.7.5.1, “SET Syntax for Variable Assignment” . To change a value for the running MySQL instance without saving it for subsequent restarts, use the GLOBAL keyword rather than PERSIST .

Setting mandatory_roles requires the ROLE_ADMIN privilege, in addition to the SYSTEM_VARIABLES_ADMIN or SUPER privilege normally required to set a global system variable.

Mandatory roles, like explicitly granted roles, do not take effect until activated (see Activating Roles ). At login time, role activation occurs for all granted roles if the activate_all_roles_on_login system variable is enabled, or only for roles that are set as default roles otherwise. At runtime, SET ROLE activates roles.

Roles named in the value of mandatory_roles cannot be revoked with REVOKE or dropped with DROP ROLE or DROP USER .

If a role named in mandatory_roles is not present in the mysql.user system table, the role is not granted to users. When the server attempts role activation for a user, it does not treat the nonexistent role as mandatory and writes a warning to the error log. If the role is created later and thus becomes valid, FLUSH PRIVILEGES may be necessary to cause the server to treat it as mandatory.

SHOW GRANTS displays mandatory roles according to the rules described in Section 13.7.6.21, “SHOW GRANTS Syntax” .

Checking Role Privileges

To verify the privileges assigned to an account, use SHOW GRANTS . For example:

mysql> SHOW GRANTS FOR 'dev1'@'localhost';
+-------------------------------------------------+
| Grants for dev1@localhost                       |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`        |
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
+-------------------------------------------------+
                            

However, that shows each granted role without expanding it to the privileges the role represents. To show role privileges as well, add a USING clause naming the granted roles for which to display privileges:

mysql> SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';
+----------------------------------------------------------+
| Grants for dev1@localhost                                |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost` |
| GRANT `app_developer`@`%` TO `dev1`@`localhost`          |
+----------------------------------------------------------+
                            

Verify each other type of user similarly:

mysql> SHOW GRANTS FOR 'read_user1'@'localhost' USING 'app_read';
+--------------------------------------------------------+
| Grants for read_user1@localhost                        |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `read_user1`@`localhost`         |
| GRANT SELECT ON `app_db`.* TO `read_user1`@`localhost` |
| GRANT `app_read`@`%` TO `read_user1`@`localhost`       |
+--------------------------------------------------------+
mysql> SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';
+------------------------------------------------------------------------------+
| Grants for rw_user1@localhost                                                |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `rw_user1`@`localhost` |
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost`               |
+------------------------------------------------------------------------------+
                            

SHOW GRANTS displays mandatory roles according to the rules described in Section 13.7.6.21, “SHOW GRANTS Syntax” .

Activating Roles

Roles granted to a user account can be active or inactive within account sessions. If a granted role is active within a session, its privileges apply; otherwise, they do not. To determine which roles are active within the current session, use the CURRENT_ROLE() function.

By default, granting a role to an account or naming it in the mandatory_roles system variable value does not automatically cause the role to become active within account sessions. For example, because thus far in the preceding discussion no rw_user1 roles have been activated, if you connect to the server as rw_user1 and invoke the CURRENT_ROLE() function, the result is NONE (no active roles):

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
                            

To specify which roles should become active each time a user connects to the server and authenticates, use SET DEFAULT ROLE . To set the default to all assigned roles for each account created earlier, use this statement:

SET DEFAULT ROLE ALL TO
  'dev1'@'localhost',
  'read_user1'@'localhost',
  'read_user2'@'localhost',
  'rw_user1'@'localhost';
                            

Now if you connect as rw_user1 , the initial value of CURRENT_ROLE() reflects the new default role assignments:

mysql> SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+
                            

To cause all explicitly granted and mandatory roles to be automatically activated when users connect to the server, enable the activate_all_roles_on_login system variable. By default, automatic role activation is disabled.

Within a session, a user can execute SET ROLE to change the set of active roles. For example, for rw_user1 :

mysql> SET ROLE NONE; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
mysql> SET ROLE ALL EXCEPT 'app_write'; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `app_read`@`%` |
+----------------+
mysql> SET ROLE DEFAULT; SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+
                            

The first SET ROLE statement deactivates all roles. The second makes rw_user1 effectively read only. The third restores the default roles.

The effective user for stored program and view objects is subject to the DEFINER and SQL SECURITY attributes, which determine whether execution occurs in invoker or definer context (see Section 24.6, “Access Control for Stored Programs and Views” ):

  • Stored program and view objects that execute in invoker context execute with the roles that are active within the current session.

  • Stored program and view objects that execute in definer context execute with the default roles of the user named in their DEFINER attribute. If activate_all_roles_on_login is enabled, such objects execute with all roles granted to the DEFINER user, including mandatory roles. For stored programs, if execution should occur with roles different from the default, the program body should execute SET ROLE to activate the required roles.

Revoking Roles or Role Privileges

Just as roles can be granted to an account, they can be revoked from an account:

REVOKE role FROM user;
                            

Roles named in the mandatory_roles system variable value cannot be revoked.

REVOKE can also be applied to a role to modify the privileges granted to it. This affects not only the role itself, but any account granted that role. Suppose that you want to temporarily make all application users read only. To do this, use REVOKE to revoke the modification privileges from the app_write role:

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write';
                            

As it happens, that leaves the role with no privileges at all, as can be seen using SHOW GRANTS (which demonstrates that this statement can be used with roles, not just users):

mysql> SHOW GRANTS FOR 'app_write';
+---------------------------------------+
| Grants for app_write@%                |
+---------------------------------------+
| GRANT USAGE ON *.* TO `app_write`@`%` |
+---------------------------------------+
                            

Because revoking privileges from a role affects the privileges for any user who is assigned the modified role, rw_user1 now has no table modification privileges ( INSERT , UPDATE , and DELETE are no longer present):

mysql> SHOW GRANTS FOR 'rw_user1'@'localhost'
       USING 'app_read', 'app_write';
+----------------------------------------------------------------+
| Grants for rw_user1@localhost                                  |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                   |
| GRANT SELECT ON `app_db`.* TO `rw_user1`@`localhost`           |
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost` |
+----------------------------------------------------------------+
                            

In effect, the rw_user1 read/write user has become a read-only user. This also occurs for any other accounts that are granted the app_write role, illustrating how use of roles makes it unnecessary to modify privileges for individual accounts.

To restore modification privileges to the role, simply re-grant them:

GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
                            

Now rw_user1 again has modification privileges, as do any other accounts granted the app_write role.

Dropping Roles

To drop roles, use DROP ROLE :

DROP ROLE 'app_read', 'app_write';
                            

Dropping a role revokes it from every account to which it was granted.

Roles named in the mandatory_roles system variable value cannot be dropped.

User and Role Interchangeability

As has been hinted at earlier for SHOW GRANTS , which displays grants for user accounts or roles, accounts and roles can be used interchangeably.

One difference between roles and users is that CREATE ROLE creates an authorization identifier that is locked by default, whereas CREATE USER