Table of Contents
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.9, “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. For more information, see 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 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.
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.9, “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”.
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
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.9.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.
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.
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 Plugin”.
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
or
your_pass
--password=
option on the command line. For example:
your_pass
shell> mysql -u francis -pfrank db_name
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=
option, where file_name
file_name
is the full
path name to the file. For example:
shell> mysql --defaults-file=/home/francis/mysql-opts
Section 4.2.6, “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 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.
Database administrators should use the following guidelines to keep passwords secure.
MySQL stores passwords for user accounts in the
mysql.user
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 Expiration Policy”, and Section 6.3.9, “Password Expiration and Sandbox Mode”.
The validate_password
plugin can be used to
enforce a policy on acceptable password. See
Section 6.5.3, “The Password Validation Plugin”.
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”.
Passwords can be written as plain text in SQL statements such as
CREATE USER
,
GRANT
, SET
PASSWORD
, and statements that invoke the
PASSWORD()
function. 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 ... GRANT ... 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
table that refer to literal
passwords are logged as is, so you should avoid such statements.
(Direct manipulation 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.
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:
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. For example, the following statement is logged as shown because a password hash value is expected:
CREATE USER 'user1'@'localhost' IDENTIFIED BY PASSWORD 'not-so-secret';
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 can be either a file or a
table (see Section 18.2.4, “Replication Relay and Status Logs”). Ensure that the
repository can be accessed only by the database administrator.
An alternative to storing the password in a file 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.
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
if
other_user
db_name
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.5, “Server System Variables”.
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 such as UPDATE user SET
password=PASSWORD('not_secure')
.
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 23.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
GRANT
statement also supports
resource control options for limiting the extent of server use
permitted to an account. See Section 13.7.1.6, “GRANT 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.
The following table shows mysqld options and system variables that affect security. For descriptions of each of these, see Section 5.1.4, “Server Command Options”, and Section 5.1.5, “Server System Variables”.
Table 6.1 Security Option/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 |
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:
Stop the server if it is running (use mysqladmin shutdown).
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.
Start the server as user user_name
.
Another alternative is to start mysqld as
the Unix root
user and use the
--user=
option. mysqld starts up, then switches to
run as the Unix user user_name
user_name
before accepting any connections.
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.9.4, “Securing the Initial MySQL Account”.
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_IDENTIFY
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 27.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
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.8, “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.
See also 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.
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
INFILE
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
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”.
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. See Section 6.2.2, “Static Versus Dynamic Privileges”.
Information about account privileges is stored in the
user
, db
,
tables_priv
, columns_priv
,
procs_priv
, and
global_grants
tables in the
mysql
system database (see
Section 6.2.3, “Grant Tables”). The MySQL server reads the
contents of these tables into memory when it starts and reloads
them under the circumstances indicated in
Section 6.2.8, “When Privilege Changes Take Effect”. Access-control decisions are
based on the in-memory copies of the grant tables.
Some MySQL releases introduce changes to the structure of 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 have the current structure whenever you upgrade MySQL. See Section 4.4.5, “mysql_upgrade — Check and Upgrade MySQL Tables”.
The following tables show the static and dynamic 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 | 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 |
Table 6.3 Permissible Dynamic Privileges for GRANT and REVOKE
Privilege | Column | Context |
---|---|---|
AUDIT_ADMIN | See global_grants table | Server administration |
BINLOG_ADMIN | See global_grants table | Server administration |
CONNECTION_ADMIN | See global_grants table | Server administration |
ENCRYPTION_KEY_ADMIN | See global_grants table | Server administration |
FIREWALL_ADMIN | See global_grants table | Server administration |
FIREWALL_USER | See global_grants table | Server administration |
GROUP_REPLICATION_ADMIN | See global_grants table | Server administration |
PERSIST_RO_VARIABLES_ADMIN | See global_grants table | Server administration |
REPLICATION_SLAVE_ADMIN | See global_grants table | Server administration |
ROLE_ADMIN | See global_grants table | Server administration |
SET_USER_ID | See global_grants table | Server administration |
SYSTEM_VARIABLES_ADMIN | See global_grants table | Server administration |
VERSION_TOKEN_ADMIN | See global_grants table | Server administration |
XA_RECOVER_ADMIN | See global_grants table | Server administration |
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:
The FILE
privilege 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.
The GRANT OPTION
privilege
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.
The ALTER
privilege may be used
to subvert the privilege system by renaming tables.
The SHUTDOWN
privilege can be
abused to deny service to other users entirely by terminating
the server.
The PROCESS
privilege can be
used to view the plain text of currently executing statements,
including statements that set or change passwords.
The SUPER
privilege can be used
to terminate other sessions or change how the server operates.
Privileges granted for the mysql
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
user
table
authentication_string
column can change an
account's password, and then connect to the MySQL server using
that account.
The SELECT
privilege is also
needed for tables or views being used with
EXPLAIN
, including any
underlying tables of views.
The following sections provide general descriptions of the static and dynamic privileges available in MySQL. (For information about the differences between these two types of privileges, see Section 6.2.2, “Static Versus Dynamic Privileges”.) Particular SQL statements might have more specific privilege requirements than indicated here. If so, the description for the statement in question provides the details.
Static privileges are built in to the server, in contrast to dynamic privileges, which are defined at runtime. The following list describes the static privileges available in MySQL.
The ALL
or
ALL
PRIVILEGES
privilege specifier is shorthand. It
stands for “all privileges available at a given
privilege level” (except GRANT
OPTION
). For example, granting
ALL
at the global or table
level grants all global privileges or all table-level
privileges.
The ALTER
privilege enables
use of the ALTER TABLE
statement to change the structure of tables.
ALTER TABLE
also requires the
CREATE
and
INSERT
privileges. Renaming a
table requires ALTER
and
DROP
on the old table,
CREATE
, and
INSERT
on the new table.
The ALTER ROUTINE
privilege
is needed to alter or drop stored routines (procedures and
functions).
The CREATE
privilege enables
creation of new databases and tables.
The CREATE ROLE
privilege
enables use of the CREATE
ROLE
statement. (The CREATE
USER
privilege also enables use of the
CREATE ROLE
statement.)
The CREATE ROUTINE
privilege
is needed to create stored routines (procedures and
functions).
The CREATE TABLESPACE
privilege is needed to create, alter, or drop tablespaces
and log file groups.
The CREATE TEMPORARY TABLES
privilege enables the creation of temporary tables using the
CREATE TEMPORARY TABLE
statement.
After a session has created a temporary table, the server
performs no further privilege checks on the table. The
creating session can perform any operation on the table,
such as DROP TABLE
,
INSERT
,
UPDATE
, or
SELECT
. For more information,
see Section 13.1.16.3, “CREATE TEMPORARY TABLE Syntax”.
The CREATE USER
privilege
enables use of the ALTER
USER
, CREATE ROLE
,
CREATE USER
,
DROP ROLE
,
DROP USER
,
RENAME USER
, and
REVOKE ALL
PRIVILEGES
statements.
The CREATE VIEW
privilege
enables use of the CREATE
VIEW
statement.
The DELETE
privilege enables
rows to be deleted from tables in a database.
The DROP
privilege enables
you to drop (remove) existing databases, tables, and views.
The DROP
privilege is
required in order to use the statement ALTER TABLE
... DROP PARTITION
on a partitioned table. The
DROP
privilege is also
required for TRUNCATE TABLE
.
If you grant the
DROP
privilege for the
mysql
database to a user, that user can
drop the database in which the MySQL access privileges are
stored.
The DROP ROLE
privilege
enables use of the DROP ROLE
statement. (The CREATE USER
privilege also enables use of the DROP
ROLE
statement.)
The EVENT
privilege is
required to create, alter, drop, or see events for the Event
Scheduler.
The EXECUTE
privilege is
required to execute stored routines (procedures and
functions).
The FILE
privilege gives you
permission to read and write files on the server host using
the LOAD DATA
INFILE
and
SELECT ... INTO
OUTFILE
statements and the
LOAD_FILE()
function. A user
who has the FILE
privilege
can read any file on the server host that is either
world-readable or readable by the MySQL server. (This
implies the user can read any file in any database
directory, because the server can access any of those
files.) The FILE
privilege
also enables the user to create new files in any directory
where the MySQL server has write access. This includes the
server's data directory containing the files that implement
the privilege tables. As a security measure, the server will
not overwrite existing files. The
FILE
privilege is required to
use the DATA DIRECTORY
or INDEX
DIRECTORY
table option for the
CREATE TABLE
statement.
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.5, “Server System Variables”.
The GRANT OPTION
privilege
enables you to give to other users or remove from other
users those privileges that you yourself possess.
The INDEX
privilege enables
you to create or drop (remove) indexes.
INDEX
applies to existing
tables. If you have the
CREATE
privilege for a table,
you can include index definitions in the
CREATE TABLE
statement.
The INSERT
privilege enables
rows to be inserted into tables in a database.
INSERT
is also required for
the ANALYZE TABLE
,
OPTIMIZE TABLE
, and
REPAIR TABLE
table-maintenance statements.
The LOCK TABLES
privilege
enables the use of explicit LOCK
TABLES
statements to lock tables for which you
have the SELECT
privilege.
This includes the use of write locks, which prevents other
sessions from reading the locked table.
The PROCESS
privilege
pertains to display of information about the threads
executing within the server (that is, information about the
statements being executed by sessions). The privilege
enables use of SHOW
PROCESSLIST
or mysqladmin
processlist to see threads belonging to other
accounts; you can always see your own threads. The
PROCESS
privilege also
enables use of SHOW ENGINE
.
The PROXY
privilege enables a
user to impersonate or become known as another user. See
Section 6.3.11, “Proxy Users”.
The creation of a foreign key constraint requires the
REFERENCES
privilege for the
parent table.
The RELOAD
privilege enables
use of the FLUSH
statement.
It also enables mysqladmin commands that
are equivalent to FLUSH
operations: flush-hosts
,
flush-logs
,
flush-privileges
,
flush-status
,
flush-tables
,
flush-threads
,
refresh
, and reload
.
The reload
command tells the server to
reload the grant tables into memory.
flush-privileges
is a synonym for
reload
. The refresh
command closes and reopens the log files and flushes all
tables. The other
flush-
commands perform functions similar to
xxx
refresh
, but are more specific and may be
preferable in some instances. For example, if you want to
flush just the log files, flush-logs
is a
better choice than refresh
.
The REPLICATION CLIENT
privilege enables the use of the SHOW
MASTER STATUS
, SHOW SLAVE
STATUS
, and SHOW BINARY
LOGS
statements.
The REPLICATION SLAVE
privilege should be granted to accounts that are used by
slave servers to connect to the current server as their
master. Without this privilege, the slave cannot request
updates that have been made to databases on the master
server.
The SELECT
privilege enables
you to select rows from tables in a database.
SELECT
statements require the
SELECT
privilege only if they
actually retrieve rows from a table. Some
SELECT
statements do not
access tables and can be executed without permission for any
database. For example, you can use
SELECT
as a simple calculator
to evaluate expressions that make no reference to tables:
SELECT 1+1; SELECT PI()*2;
The SELECT
privilege is also
needed for other statements that read column values. For
example, SELECT
is needed for
columns referenced on the right hand side of
col_name
=expr
assignment in UPDATE
statements or for columns named in the
WHERE
clause of
DELETE
or
UPDATE
statements.
The SHOW DATABASES
privilege
enables the account to see database names by issuing the
SHOW DATABASE
statement. Accounts that do
not have this privilege see only databases for which they
have some privileges, and cannot use the statement at all if
the server was started with the
--skip-show-database
option.
Note that any global privilege is a
privilege for the database.
The SHOW VIEW
privilege
enables use of the SHOW CREATE
VIEW
statement. This privilege is also needed for
views being used with
EXPLAIN
.
The SHUTDOWN
privilege
enables use of the SHUTDOWN
statement, the mysqladmin shutdown
command, and the
mysql_shutdown()
C API
function.
The SUPER
privilege enables
these operations and server behaviors:
Enables configuration changes by modifying or persisting
global system variables. For some system variables,
setting the session value also requires the
SUPER
privilege; if so,
it is indicated in the variable description. Examples
include binlog_format
,
sql_log_bin
, and
sql_log_off
.
The corresponding dynamic privilege is
SYSTEM_VARIABLES_ADMIN
.
Enables changes to global transaction characteristics (see Section 13.3.6, “SET TRANSACTION Syntax”).
The corresponding dynamic privilege is
SYSTEM_VARIABLES_ADMIN
.
Enables starting and stopping replication on slave servers, including Group Replication.
The corresponding dynamic privilege is
REPLICATION_SLAVE_ADMIN
for regular replication,
GROUP_REPLICATION_ADMIN
for Group Replication.
Enables use of the CHANGE MASTER
TO
and CHANGE REPLICATION
FILTER
statements.
The corresponding dynamic privilege is
REPLICATION_SLAVE_ADMIN
.
Enables binary log control by means of the
PURGE BINARY LOGS
and
BINLOG
statements.
The corresponding dynamic privilege is
BINLOG_ADMIN
.
Enables setting the effective authorization ID when
executing a view or stored program. A user with this
privilege can specify any account in the
DEFINER
attribute of a view or stored
program.
The corresponding dynamic privilege is
SET_USER_ID
.
Enables use of the CREATE
SERVER
, ALTER
SERVER
, and DROP
SERVER
statements.
Enables use of the mysqladmin debug command.
Enables InnoDB
key rotation.
The corresponding dynamic privilege is
ENCRYPTION_KEY_ADMIN
.
Enables reading the DES key file by the
DES_ENCRYPT()
function.
Enables execution of Version Tokens user-defined functions.
The corresponding dynamic privilege is
VERSION_TOKEN_ADMIN
.
Enables nonempty <graphml>
element content in the result from the
ROLES_GRAPHML()
function.
The corresponding dynamic privilege is
ROLE_ADMIN
.
Enables control over client connections not permitted to
non-SUPER
accounts:
Enables use of the
KILL
statement or
mysqladmin kill command to kill
threads belonging to other accounts. (You can always
kill your own threads.)
The corresponding dynamic privilege is
CONNECTION_ADMIN
.
The server accepts one connection from a
SUPER
client even if
the connection limit controlled by the
max_connections
system variable is reached.
The corresponding dynamic privilege is
CONNECTION_ADMIN
.
Updates can be performed even when the
read_only
system
variable is enabled. This applies to table updates
and use of account-management statements such as
GRANT
and
REVOKE
.
The corresponding dynamic privilege is
CONNECTION_ADMIN
.
The server does not execute
init_connect
system
variable content when
SUPER
clients
connect.
The corresponding dynamic privilege is
CONNECTION_ADMIN
.
A server in offline mode
(offline_mode
enabled) does not terminate
SUPER
client
connections at the next client request, and accepts
new connections from
SUPER
clients.
The corresponding dynamic privilege is
CONNECTION_ADMIN
.
You may also need the SUPER
privilege to create or alter stored functions if binary
logging is enabled, as described in
Section 23.7, “Binary Logging of Stored Programs”.
The TRIGGER
privilege enables
trigger operations. You must have this privilege for a table
to create, drop, execute, or display triggers for that
table.
When a trigger is activated (by a user who has privileges to
execute INSERT
,
UPDATE
, or
DELETE
statements for the
table associated with the trigger), trigger execution
requires that the user who defined the trigger still have
the TRIGGER
privilege.
The UPDATE
privilege enables
rows to be updated in tables in a database.
The USAGE
privilege specifier
stands for “no privileges.” It is used at the
global level with GRANT
to
modify account attributes such as resource limits or SSL
characteristics without naming specific account privileges.
SHOW GRANTS
displays
USAGE
to indicate that an
account has no privileges at a privilege level.
Dynamic privileges are defined at runtime, in contrast to static privileges, which are built in to the server. The following list describes the dynamic privileges available in MySQL.
AUDIT_ADMIN
: Enables audit
log configuration.
BINLOG_ADMIN
: Enables binary
log control by means of the PURGE
BINARY LOGS
and
BINLOG
statements. Defined at
server startup.
CONNECTION_ADMIN
: Enables
setting system variables related to client connections, or
circumventing restrictions related to client connections.
Defined at server startup.
CONNECTION_ADMIN
applies to
the effects of these system variables:
init_connect
: The
server does not execute
init_connect
system
variable content when
CONNECTION_ADMIN
clients
connect.
offline_mode
: A server
in offline mode
(offline_mode
enabled)
does not terminate
CONNECTION_ADMIN
client
connections at the next client request, and accepts new
connections from
CONNECTION_ADMIN
clients.
read_only
: Updates can
be performed even when the
read_only
system
variable is enabled. This applies to table updates and
use of account-management statements such as
GRANT
and
REVOKE
.
ENCRYPTION_KEY_ADMIN
: Enables
InnoDB
encryption key rotation. Defined
at server startup.
FIREWALL_ADMIN
: Enables a
user to administer firewall rules for any user.
FIREWALL_USER
: Enables users
to update their own firewall rules.
GROUP_REPLICATION_ADMIN
: On a
slave server, enables starting and stopping Group
Replication. Defined at server startup.
PERSIST_RO_VARIABLES_ADMIN
:
Enables use of
SET
PERSIST_ONLY
to persist global system variables to
the mysqld-auto.cnf
option file in the
data directory. This statement is similar to
SET
PERSIST
but does not modify the runtime global
system variable value, making it suitable for configuring
read-only system variables that can be set only at server
startup. Defined at server startup.
REPLICATION_SLAVE_ADMIN
: On a
slave server, enables connecting to and disconnecting from
the master server, starting and stopping replication, and
use of the CHANGE MASTER TO
and CHANGE REPLICATION FILTER
statements. Defined at server startup. This privilege does
not apply to Group Replication; use
GROUP_REPLICATION_ADMIN
for that.
ROLE_ADMIN
: Enables use of
the WITH ADMIN OPTION
clause of the
GRANT
statement. Enables
nonempty <graphml>
element content
in the result from the
ROLES_GRAPHML()
function.
Defined at server startup.
SET_USER_ID
: Enables setting
the effective authorization ID when executing a view or
stored program. A user with this privilege can specify any
account in the DEFINER
attribute of a
view or stored program. Defined at server startup.
SYSTEM_VARIABLES_ADMIN
:
Enables configuration changes by modifying or persisting
global system variables. For some system variables, setting
the session value also requires the
SYSTEM_VARIABLES_ADMIN
privilege; if so, it is indicated in the variable
description. Examples include
binlog_format
,
sql_log_bin
, and
sql_log_off
. Defined at
server startup.
VERSION_TOKEN_ADMIN
: Enables
execution of Version Tokens user-defined functions. Defined
by the version_tokens
plugin; see
Section 5.6.4, “Version Tokens”.
XA_RECOVER_ADMIN
: Enables
execution of the
XA
RECOVER
statement; see
Section 13.3.7.1, “XA Transaction SQL Syntax”. Defined at server startup.
Prior to MySQL 8.0, any user could execute the
XA
RECOVER
statement to discover the XID values for
outstanding prepared XA transactions, possibly leading to
commit or rollback of an XA transaction by a user other than
the one who started it. In MySQL 8.0,
XA
RECOVER
is permitted only to users who have the
XA_RECOVER_ADMIN
privilege,
which is expected to be granted only to administrative users
who have need for it. This might be the case, for example,
for administrators of an XA application if it has crashed
and it is necessary to find outstanding transactions started
by the application so they can be rolled back. This
privilege requirement prevents users from discovering the
XID values for outstanding prepared XA transactions other
than their own. It does not affect normal commit or rollback
of an XA transaction because the user who started it knows
its XID.
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.
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”.
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:
Execute this query to identify accounts that are granted
SUPER
:
SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE PRIVILEGE_TYPE = 'SUPER';
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.
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”.
Normally, to manipulate the contents of grant tables, you 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.
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
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
'thomas.loc.gov'
and
'bob'
applies to authenticating connections
made to the server from the host
thomas.loc.gov
by a client that specifies a
user name of bob
. Similarly, a
db
table row with Host
,
User
, and Db
column
values of 'thomas.loc.gov'
,
'bob'
and 'reports'
applies when bob
connects from the host
thomas.loc.gov
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.
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 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 (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 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 | ||
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.
password_last_changed
is a
TIMESTAMP
column indicating when the password
was last changed. The value is non-NULL
only
for accounts that use MySQL built-in authentication methods
(accounts that use an authentication plugin of
mysql_native_password
or
sha256_password
). The value is
NULL
for other accounts, such as those
authenticated using an external authentication system.
password_last_changed
is updated by the
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”).
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.5 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.6 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:
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
.
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.7 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.8 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';
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
'
.
For example, user_name
'@'%''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.
'192.168.1.%'
matches any host in the
192.168.1 class C network.
Because IP wildcard values are permitted in host values (for
example, '192.168.1.%'
to match every host
on a subnet), someone could try to exploit this capability by
naming a host 192.168.1.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
.
For example:
host_ip
/netmask
CREATE USER 'david'@'192.58.197.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 = 192.58.197.0
IP addresses that satisfy this condition range from
192.58.197.0
to
192.58.197.255
.
A netmask typically begins with bits set to 1, followed by bits set to 0. Examples:
192.0.0.0/255.0.0.0
: Any host on the
192 class A network
192.168.0.0/255.255.0.0
: Any host on
the 192.168 class B network
192.168.1.0/255.255.255.0
: Any host on
the 192.168.1 class C network
192.168.1.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
192.168.1.2
, that will match an account
host value of 192.168.1.2
but not
192.168.01.2
. Similarly, it will match an
account host pattern like 192.168.1.%
but
not 192.168.01.%
.
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.
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.
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
database.
The following table shows how various combinations of
User
and Host
values in the
user
table apply to incoming connections.
User Value | Host Value | Permissible Connections |
---|---|---|
'fred' | 'thomas.loc.gov' | fred , connecting from
thomas.loc.gov |
'' | 'thomas.loc.gov' | Any user, connecting from thomas.loc.gov |
'fred' | '%' | fred , connecting from any host |
'' | '%' | Any user, connecting from any host |
'fred' | '%.loc.gov' | fred , connecting from any host in the
loc.gov domain |
'fred' | 'x.y.%' | fred , connecting from x.y.net ,
x.y.com , x.y.edu ,
and so on; this is probably not useful |
'fred' | '192.168.10.177' | fred , connecting from the host with IP address
192.168.10.177 |
'fred' | '192.168.10.%' | fred , connecting from any host in the
192.168.10 class C subnet |
'fred' | '192.168.10.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 thomas.loc.gov
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 192.168.1.13
and
192.168.1.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 indeterminate.
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 | ... | thomas.loc.gov | | ... +----------------+----------+-
The sorted table looks like this:
+----------------+----------+- | Host | User | ... +----------------+----------+- | thomas.loc.gov | | ... | % | jeffrey | ... +----------------+----------+-
A connection by jeffrey
from
thomas.loc.gov
is matched by the first row,
whereas a connection by jeffrey
from any host
is matched by the second.
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 thomas.loc.gov
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.14, “Information Functions”.) It returns a value in
format that indicates the user_name
@host_name
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.
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.
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 account-management
statements such as GRANT
,
REVOKE
, SET
PASSWORD
, or RENAME USER
,
the server notices these changes and loads the grant tables into
memory again immediately.
If you modify the grant tables directly using statements such as
INSERT
,
UPDATE
, or
DELETE
, 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
statement.
db_name
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.
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
database
containing the grant tables. For distributions that do not do
this, you must initialize the data directory manually. For
details, see Section 2.9, “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, test the initial privileges by executing this command:
shell> mysql -u root
The server should let you connect without error.
After a fresh installation, you should connect to the server and set up your users and their access permissions:
shell> mysql -u root mysql
The server should let you connect with no password if you
initialized MySQL using mysqld
--initialize-secure to not create a password for the
initial root
account (see
Section 2.9.1.1, “Initializing the Data Directory Manually Using mysqld”). That
is a security risk, so setting the password for the
root
account is something you should do
while you're setting up your other MySQL accounts. For
instructions on setting the initial password, see
Section 2.9.4, “Securing the Initial MySQL Account”.
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.6, “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.5.3.2, “How to Reset the Root Password”.
If you change a password by using SET
PASSWORD
, INSERT
, or
UPDATE
, you must encrypt the
password using the PASSWORD()
function. If you do not use
PASSWORD()
for these
statements, the password will not work. For example, the
following statement assigns a password, but fails to encrypt
it, so the user is not able to connect afterward:
SET PASSWORD FOR 'abe'@'host_name
' = 'eagle';
Instead, set the password like this:
SET PASSWORD FOR 'abe'@'host_name
' = PASSWORD('eagle');
The PASSWORD()
function is
unnecessary when you specify a password using the
CREATE USER
or
GRANT
statements or the
mysqladmin password command. Each of those
automatically uses PASSWORD()
to encrypt the password. See
Section 6.3.7, “Assigning Account Passwords”, and
Section 13.7.1.3, “CREATE USER Syntax”.
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
, you may have a
problem with the user_name
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
results in your_hostname
-u rootAccess 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
works but
user_name
mysql -u
does not, you
have not granted access to the given user for the database
named user_name
some_db
some_db
.
If mysql -u
works when
executed on the server host, but user_name
mysql -h
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.
host_name
-u
user_name
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
='
,
thinking that this enables you to specify
some_user
'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
='
,
or to delete the row with
some_user
'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
INFILE
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
or user_name
db_name
mysql
-u
. 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 user_name
-pyour_pass
db_name
-p
and
the password; you can also use the
--password=
syntax to specify the password. If you use the
your_pass
-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 28.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.
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
See also Section 13.7.1, “Account Management Statements”, which describes the syntax and use for all user-management SQL statements.
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.
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.1.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.9.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=monty --password db_name
If you prefer short options, the command looks like this:
shell> mysql -u monty -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=monty --password=
shell>password
db_name
mysql -u monty -p
password
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.6, “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”.
You can create MySQL accounts two ways:
By using 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.
By manipulating the MySQL grant tables directly with
statements such as INSERT
,
UPDATE
, or
DELETE
.
The preferred method is to use account-management statements because they are more concise and less error-prone than manipulating the grant tables directly. All such statements are described in Section 13.7.1, “Account Management Statements”. Direct grant table manipulation is discouraged, and is not described here. The server is free to ignore rows that become malformed as a result of such modifications.
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.9.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 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql>GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
->WITH GRANT OPTION;
mysql>CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql>GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
->WITH GRANT OPTION;
mysql>CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_pass';
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 monty
and
a password of some_pass
. Both are superuser
accounts with full privileges to do anything. The
'monty'@'localhost'
account can be used
only when connecting from the local host. The
'monty'@'%'
account uses the
'%'
wildcard for the host part, so it can
be used to connect from any host.
The 'monty'@'localhost'
account is
necessary if there is an anonymous-user account for
localhost
. Without the
'monty'@'localhost'
account, that
anonymous-user account takes precedence when
monty
connects from the local host and
monty
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
'monty'@'%'
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 has a
password of admin_pass
. This 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
obscure
:
mysql>CREATE USER 'custom'@'localhost' IDENTIFIED BY 'obscure';
mysql>GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
->ON bankaccount.*
->TO 'custom'@'localhost';
mysql>CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'obscure';
mysql>GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
->ON expenses.*
->TO 'custom'@'host47.example.com';
mysql>CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'obscure';
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.
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';
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:
CREATE ROLE
and
DROP ROLE
enable roles to be
created and removed.
GRANT
and
REVOKE
enable privilege
assignment and revocation for user accounts and roles.
SHOW GRANTS
displays privilege
and role assignments for user accounts and roles.
SET DEFAULT ROLE
specifies
which account roles are active by default.
SET ROLE
changes the active
roles within the current session.
The CURRENT_ROLE()
function
displays the active roles within the current session.
The mandatory_roles
and
activate_all_roles_on_login
system variables enable defining mandatory roles and automatic
activation of granted roles when users log in to the server.
For descriptions of individual role-manipulation statements, 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 administrative privileges, such as the
root
account.
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 CREATE
ROLE
:
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
'
format. The host part, if omitted, defaults to
user_name
'@'host_name
''%'
. 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
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.)
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 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.4.1, “SET Syntax for Variable Assignment”. To change a value
only for the running MySQL instance without saving it for
subsequent restarts, use the GLOBAL
keyword
rather than PERSIST
.
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.5.21, “SHOW GRANTS Syntax”.
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.5.21, “SHOW GRANTS Syntax”.
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 23.6, “Access Control for Stored Programs and Views”):
Stored program and view objects that execute in invoker context execute with the active roles 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.
Just as roles can be granted to an account, they can be revoked from an account:
REVOKErole
FROMuser
;
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.
To remove 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.
As has been hinted at earlier for SHOW
GRANTS
, which displays grants for user accounts or
roles, accounts and roles can be used interchangeably. You can
treat a user account like a role and grant that account to
another user or a role. The effect is to grant the account's
privileges and roles to the other user or role.
This set of statements demonstrates that you can grant a user to a user, a role to a user, a user to a role, or a role to a role:
CREATE USER 'u1'; CREATE ROLE 'r1'; GRANT SELECT ON db1.* TO 'u1'; GRANT SELECT ON db2.* TO 'r1'; CREATE USER 'u2'; CREATE ROLE 'r2'; GRANT 'u1', 'r1' TO 'u2'; GRANT 'u1', 'r1' TO 'r2';
The result in each case is to grant to the grantee object the
privileges associated with the granted object. After executing
those statements, each of u2
and
r2
have been granted privileges from a user
(u1
) and a role (r1
):
mysql>SHOW GRANTS FOR 'u2' USING 'u1', 'r1';
+-------------------------------------+ | Grants for u2@% | +-------------------------------------+ | GRANT USAGE ON *.* TO `u2`@`%` | | GRANT SELECT ON `db1`.* TO `u2`@`%` | | GRANT SELECT ON `db2`.* TO `u2`@`%` | | GRANT `u1`@`%`,`r1`@`%` TO `u2`@`%` | +-------------------------------------+ mysql>SHOW GRANTS FOR 'r2' USING 'u1', 'r1';
+-------------------------------------+ | Grants for r2@% | +-------------------------------------+ | GRANT USAGE ON *.* TO `r2`@`%` | | GRANT SELECT ON `db1`.* TO `r2`@`%` | | GRANT SELECT ON `db2`.* TO `r2`@`%` | | GRANT `u1`@`%`,`r1`@`%` TO `r2`@`%` | +-------------------------------------+
The preceding example is illustrative only, but interchangeability of user accounts and roles has practical application, such as in the following situation: Suppose that a legacy application development project began before the advent of roles in MySQL, so all user accounts associated with the project are granted privileges directly (rather than granted privileges by virtue of being granted roles). One of these accounts is a developer account that was originally granted privileges as follows:
CREATE USER 'old_app_dev'@'localhost' IDENTIFIED BY 'old_app_devpass'; GRANT ALL ON old_app.* TO 'old_app_dev'@'localhost';
If this developer leaves the project, it becomes necessary to assign the privileges to another user, or perhaps multiple users if development activies have expanded. Here are some ways to deal with the issue:
Without using roles: Change the account password so the original developer cannot use it, and have a new developer use the account instead:
ALTER USER 'old_app_dev'@'localhost' IDENTIFIED BY 'new_password';
Using roles: Lock the account to prevent anyone from using it to connect to the server:
ALTER USER 'old_app_dev'@'localhost' ACCOUNT LOCK;
Then treat the account as a role. For each developer new to the project, create a new account and grant to it the original developer account:
CREATE USER 'new_app_dev1'@'localhost' IDENTIFIED BY 'new_password'; GRANT 'old_app_dev'@'localhost' TO 'new_app_dev1'@'localhost';
The effect is to assign the original developer account privileges to the new account.
One part of the MySQL installation process is data directory initialization (see Section 2.9.1.1, “Initializing the Data Directory Manually Using mysqld”). During data directory initialization, MySQL creates user accounts that should be considered reserved:
'root'@'localhost
: Used for administrative
purposes. This account has all privileges and can perform any
operation.
Strictly speaking, this account name is not reserved, in the
sense that some installations rename the
root
account to something else to avoid
exposing a highly privileged account with a well-known name.
'mysql.sys'@'localhost'
: Used as the
DEFINER
for
sys
schema objects. Use of the
mysql.sys
account avoids problems that
occur if a DBA renames or removes the root
account. This account is locked so that it cannot be used for
client connections.
'mysql.session'@'localhost'
: Used
internally by plugins to access the server. This account is
locked so that it cannot be used for client connections.
One means of restricting client use of MySQL server resources is
to set the global
max_user_connections
system
variable to a nonzero value. This limits the number of
simultaneous connections that can be made by any given account,
but places no limits on what a client can do once connected. In
addition, setting
max_user_connections
does not
enable management of individual accounts. Both types of control
are of interest to MySQL administrators.
To address such concerns, MySQL permits limits for individual accounts on use of these server resources:
The number of queries an account can issue per hour
The number of updates an account can issue per hour
The number of times an account can connect to the server per hour
The number of simultaneous connections to the server by an account
Any statement that a client can issue counts against the query limit. Only statements that modify databases or tables count against the update limit.
An “account” in this context corresponds to a row in
the mysql.user
table. That is, a connection is
assessed against the User
and
Host
values in the user
table row that applies to the connection. For example, an account
'usera'@'%.example.com'
corresponds to a row in
the user
table that has User
and Host
values of usera
and
%.example.com
, to permit
usera
to connect from any host in the
example.com
domain. In this case, the server
applies resource limits in this row collectively to all
connections by usera
from any host in the
example.com
domain because all such connections
use the same account.
Before MySQL 5.0.3, an “account” was assessed against
the actual host from which a user connects. This older method of
accounting may be selected by starting the server with the
--old-style-user-limits
option. In
this case, if usera
connects simultaneously
from host1.example.com
and
host2.example.com
, the server applies the
account resource limits separately to each connection. If
usera
connects again from
host1.example.com
, the server applies the
limits for that connection together with the existing connection
from that host.
To establish resource limits for an account at account-creation
time, use the CREATE USER
statement. To modify the limits for an existing account, use
ALTER USER
. Provide a
WITH
clause that names each resource to be
limited. The default value for each limit is zero (no limit). For
example, to create a new account that can access the
customer
database, but only in a limited
fashion, issue these statements:
mysql>CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank'
->WITH MAX_QUERIES_PER_HOUR 20
->MAX_UPDATES_PER_HOUR 10
->MAX_CONNECTIONS_PER_HOUR 5
->MAX_USER_CONNECTIONS 2;
The limit types need not all be named in the
WITH
clause, but those named can be present in
any order. The value for each per-hour limit should be an integer
representing a count per hour. For
MAX_USER_CONNECTIONS
, the limit is an integer
representing the maximum number of simultaneous connections by the
account. If this limit is set to zero, the global
max_user_connections
system
variable value determines the number of simultaneous connections.
If max_user_connections
is also
zero, there is no limit for the account.
To modify limits for an existing account, use an
ALTER USER
statement. The following
statement changes the query limit for francis
to 100:
mysql> ALTER USER 'francis'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;
The statement modifies only the limit value specified and leaves the account otherwise unchanged.
To remove a limit, set its value to zero. For example, to remove
the limit on how many times per hour francis
can connect, use this statement:
mysql> ALTER USER 'francis'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;
As mentioned previously, the simultaneous-connection limit for an
account is determined from the
MAX_USER_CONNECTIONS
limit and the
max_user_connections
system
variable. Suppose that the global
max_user_connections
value is 10
and three accounts have individual resource limits specified as
follows:
ALTER USER 'user1'@'localhost' WITH MAX_USER_CONNECTIONS 0; ALTER USER 'user2'@'localhost' WITH MAX_USER_CONNECTIONS 5; ALTER USER 'user3'@'localhost' WITH MAX_USER_CONNECTIONS 20;
user1
has a connection limit of 10 (the global
max_user_connections
value)
because it has a MAX_USER_CONNECTIONS
limit of
zero. user2
and user3
have
connection limits of 5 and 20, respectively, because they have
nonzero MAX_USER_CONNECTIONS
limits.
The server stores resource limits for an account in the
user
table row corresponding to the account.
The max_questions
,
max_updates
, and
max_connections
columns store the per-hour
limits, and the max_user_connections
column
stores the MAX_USER_CONNECTIONS
limit. (See
Section 6.2.3, “Grant Tables”.)
Resource-use counting takes place when any account has a nonzero limit placed on its use of any of the resources.
As the server runs, it counts the number of times each account uses resources. If an account reaches its limit on number of connections within the last hour, the server rejects further connections for the account until that hour is up. Similarly, if the account reaches its limit on the number of queries or updates, the server rejects further queries or updates until the hour is up. In all such cases, the server issues appropriate error messages.
Resource counting occurs per account, not per client. For example, if your account has a query limit of 50, you cannot increase your limit to 100 by making two simultaneous client connections to the server. Queries issued on both connections are counted together.
The current per-hour resource-use counts can be reset globally for all accounts, or individually for a given account:
To reset the current counts to zero for all accounts, issue a
FLUSH USER_RESOURCES
statement.
The counts also can be reset by reloading the grant tables
(for example, with a FLUSH
PRIVILEGES
statement or a mysqladmin
reload command).
The counts for an individual account can be reset to zero by setting any of its limits again. Specify a limit value equal to the value currently assigned to the account.
Per-hour counter resets do not affect the
MAX_USER_CONNECTIONS
limit.
All counts begin at zero when the server starts. Counts do not carry over through server restarts.
For the MAX_USER_CONNECTIONS
limit, an edge
case can occur if the account currently has open the maximum
number of connections permitted to it: A disconnect followed
quickly by a connect can result in an error
(ER_TOO_MANY_USER_CONNECTIONS
or
ER_USER_LIMIT_REACHED
) if the
server has not fully processed the disconnect by the time the
connect occurs. When the server finishes disconnect processing,
another connection will once more be permitted.
Required credentials for clients that connect to the MySQL server can include a password. This section describes how to assign passwords for MySQL accounts.
MySQL stores passwords in the user
table in the
mysql
system database. Operations that assign
or modify passwords are permitted only to users with the
CREATE USER
privilege, or,
alternatively, privileges for the mysql
database (INSERT
privilege to
create new accounts, UPDATE
privilege to modify existing accounts). If the
read_only
system variable is
enabled, use of account-modification statements such as
CREATE USER
or
ALTER USER
additionally requires
the CONNECTION_ADMIN
or
SUPER
privilege.
The discussion here summarizes syntax only for the most common password-assignment statements. For complete details on other possibilities, see Section 13.7.1.3, “CREATE USER Syntax”, Section 13.7.1.1, “ALTER USER Syntax”, Section 13.7.1.6, “GRANT Syntax”, and Section 13.7.1.10, “SET PASSWORD Syntax”.
MySQL hashes passwords stored in the mysql.user
table to obfuscate them. For the statements described here, MySQL
automatically hashes the password specified. There are also
syntaxes for CREATE USER
and
ALTER USER
that permit hashed
values to be specified literally; for details, see the
descriptions of those statements.
MySQL uses plugins to perform client authentication; see Section 6.3.10, “Pluggable Authentication”. The authentication plugin associated with an account determines the algorithm used to hash passwords for that account.
To assign a password when you create a new account, use
CREATE USER
and include an
IDENTIFIED BY
clause:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
For this CREATE USER
syntax, MySQL
automatically hashes the password before storing it in the
mysql.user
table.
CREATE USER
also supports syntax
for specifying the account authentication plugin. See
Section 13.7.1.3, “CREATE USER Syntax”.
To assign or change a password for an existing account, use one of the following methods:
Use the ALTER USER
statement
with an IDENTIFIED BY
clause:
ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
If you are not connected as an anonymous user, you can change your own password without naming your own account literally:
ALTER USER USER() IDENTIFIED BY 'mypass';
For these ALTER USER
syntaxes,
MySQL automatically hashes the password before storing it in
the mysql.user
table.
To change an account password from the command line, use the mysqladmin command:
mysqladmin -uuser_name
-hhost_name
password "new_password
"
The account for which this command sets the password is the
one with a mysql.user
table row that
matches user_name
in the
User
column and the client host
from which you connect in the
Host
column.
For password changes made using mysqladmin,
MySQL automatically hashes the password before storing it in
the mysql.user
table.
If you are using MySQL Replication, be aware that, currently, a
password used by a replication slave as part of a
CHANGE MASTER TO
statement is
effectively limited to 32 characters in length; if the password is
longer, any excess characters are truncated. This is not due to
any limit imposed by the MySQL Server generally, but rather is an
issue specific to MySQL Replication. (For more information, see
Bug #43439.)
Account passwords have an age, assessed from the date and time of the most recent password change.
MySQL enables database administrators to expire account passwords manually, and to establish a policy for automatic password expiration.
For example, to expire an account password manually, use the
ALTER USER
statement:
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
This operation marks the password expired in the corresponding
mysql.user
table row. The
mysql.user
table indicates for each account
when its password was last changed, and the server automatically
treats the password as expired at client connection time if its
age is greater than its permitted lifetime. This works with no
explicit manual password expiration.
The default_password_lifetime
system variable defines the global automatic password expiration
policy. It applies to accounts that use MySQL built-in
authentication methods (accounts that use an authentication plugin
of mysql_native_password
or
sha256_password
).
The default
default_password_lifetime
value
is 0, which disables automatic password expiration. If the value
of default_password_lifetime
is a
positive integer N
, it indicates the
permitted password lifetime; passwords must be changed every
N
days.
Examples:
To establish a global policy that passwords have a lifetime of approximately six months, start the server with these lines in an option file:
[mysqld] default_password_lifetime=180
To establish a global policy such that passwords never expire,
set default_password_lifetime
to 0:
[mysqld] default_password_lifetime=0
default_password_lifetime
can
also be changed at runtime (this requires the
SYSTEM_VARIABLES_ADMIN
or
SUPER
privilege):
SET GLOBAL default_password_lifetime = 180; SET GLOBAL default_password_lifetime = 0;
No matter the global policy, it can be established with
CREATE USER
or changed with
ALTER USER
for individual accounts:
Require the password to be changed every 90 days:
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY; ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
Disable password expiration:
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER; ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
Defer to the global expiration policy:
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT; ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
PASSWORD EXPIRE DEFAULT
defers to the
global exipration policy and in the
mysql.user
table sets the
password_lifetime
field to
NULL
for the named account.
When a client successfully connects, the server determines whether the account password is expired:
The server checks whether the password has been manually expired and, if so, restricts the session.
Otherwise, the server checks whether the password age is greater than its permitted lifetime according to the automatic password expiration policy. If so, the server considers the password expired and restricts the session.
A restricted client operates in “sandbox mode,”, which limits the operations permitted to the client (see Section 6.3.9, “Password Expiration and Sandbox Mode”). Operations performed by a restricted client result in an error until the user establishes a new account password:
mysql>SELECT 1;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql>ALTER USER USER() IDENTIFIED BY '
Query OK, 0 rows affected (0.01 sec) mysql>new_password
';SELECT 1;
+---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)
This restricted mode of operation permits
SET
statements, which is useful if the deprecated
SET PASSWORD
is used instead of
ALTER USER
and the account password
has a hashing format that requires
old_passwords
to be set to a
value different from its default.
It is possible for an administrative user to reset the account password, but any existing sessions for that account remain restricted. A client using the account must disconnect and reconnect before statements can be executed successfully.
It is possible 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.
MySQL provides password-expiration capability to enable database administrators to expire account passwords and require users to reset their password. This section describes how password expiration works.
To expire an account password, use the ALTER
USER
statement. For example:
ALTER USER 'myuser'@'localhost' PASSWORD EXPIRE;
This statement modifies the row of the
mysql.user
table associated with the named
account, setting the password_expired
column to
'Y'
. This does not affect any current
connections the account has open. For each subsequent connection
that uses the account, the server either disconnects the client or
handles the client in “sandbox mode,” in which the
server permits to the client only those operations necessary to
reset the expired password. The action taken by the server depends
on both client and server settings.
If the server disconnects the client, it returns an
ER_MUST_CHANGE_PASSWORD_LOGIN
error:
shell>mysql -u myuser -p
Password:******
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.
If the server puts the client in sandbox mode, these operations are permitted within the client session:
The client can reset the account password with
ALTER USER
or
SET PASSWORD
. This modifies the
row of the mysql.user
table associated with
the current account, setting the
password_expired
column to
'N'
. After the password has been reset, the
server restores normal access for the session, as well as for
subsequent connections that use the account.
It is possible 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.
The client can use
SET
statements, which is useful if the deprecated
SET PASSWORD
is used instead of
ALTER USER
and the account
password has a hashing format that requires
old_passwords
to be set to a
value different from its default.
For any operation not permitted within the session, the server
returns an ER_MUST_CHANGE_PASSWORD
error:
mysql>USE performance_schema;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql>SELECT 1;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
For noninteractive invocations of the mysql
client (for example, in batch mode), the server normally
disconnects the client if the password is expired. To permit
noninteractive mysql invocations to stay
connected so that the password can be changed (using the
statements just described), add the
--connect-expired-password
option to
the mysql command.
As mentioned previously, whether the server disconnects an expired-password client or puts it in sandbox mode depends on a combination of client and server settings. The following discussion describes the relevant settings and how they interact.
On the client side, a given client indicates whether it can handle sandbox mode for expired passwords. For clients that use the C client library, there are two ways to do this:
Pass the
MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS
flag
to mysql_options()
prior to
connecting:
arg = 1; result = mysql_options(mysql, MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS, &arg);
The mysql client enables
MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS
if
invoked interactively or the
--connect-expired-password
option is given.
Pass the
CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS
flag to
mysql_real_connect()
at
connection time:
mysql = mysql_real_connect(mysql, host, user, password, "test", port, unix_socket, CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS);
Other MySQL Connectors have their own conventions for indicating readiness to handle sandbox mode. See the relevant Connector documentation.
On the server side, if a client indicates that it can handle expired passwords, the server puts it in sandbox mode.
If a client does not indicate that it can handle expired passwords
(or uses an older version of the client library that cannot so
indicate), the server action depends on the value of the
disconnect_on_expired_password
system variable:
If
disconnect_on_expired_password
is enabled (the default), the server disconnects the client
with an
ER_MUST_CHANGE_PASSWORD_LOGIN
error.
If
disconnect_on_expired_password
is disabled, the server puts the client in sandbox mode.
The preceding client and server settings apply only for accounts with expired passwords. If a client connects using a nonexpired password, the server handles the client normally.
When a client connects to the MySQL server, the server uses the
user name provided by the client and the client host to select the
appropriate account row from the mysql.user
table. The server then authenticates the client, determining from
the account row which authentication plugin applies to the client.
The server invokes that plugin to authenticate the user, and the
plugin returns a status to the server indicating whether the user
is permitted to connect. If the server cannot find the plugin, an
error occurs and the connection attempt is rejected.
Pluggable authentication enables two important capabilities:
External authentication:
Pluggable authentication makes it possible for clients to
connect to the MySQL server with credentials that are
appropriate for authentication methods that store credentials
elsewhere than in the mysql.user
table. For
example, plugins can be created to use external authentication
methods such as PAM, Windows login IDs, LDAP, or Kerberos.
Proxy users: If a user is permitted to connect, an authentication plugin can return to the server a user name different from the name of the connecting user, to indicate that the connecting user is a proxy for another user (the proxied user). While the connection lasts, the proxy user is treated, for purposes of access control, as having the privileges of the proxied user. In effect, one user impersonates another. For more information, see Section 6.3.11, “Proxy Users”.
Several authentication plugins are available in MySQL:
A plugin that performs native authentication; that is,
authentication based on the password hashing method in use
from before the introduction of pluggable authentication in
MySQL. The mysql_native_password
plugin
implements authentication based on this native password
hashing method. See
Section 6.5.1.1, “Native Pluggable Authentication”. Native
authentication using mysql_native_password
is the default for new accounts, unless the
default_authentication_plugin
system
variable is set otherwise.
A plugin that performs authentication using SHA-256 password hashing. This encryption is stronger than that available with native authentication. See Section 6.5.1.2, “SHA-256 Pluggable Authentication”.
A client-side plugin that sends the password to the server without hashing or encryption. This plugin is used in conjunction with server-side plugins that require access to the password exactly as provided by the client user. See Section 6.5.1.3, “Client-Side Cleartext Pluggable Authentication”.
A plugin prevents all client connections to any account that uses it. Use cases for this plugin include accounts that must be able to execute stored programs and views with elevated privileges without exposing those privileges to ordinary users, and proxied accounts that should never permit direct login but are accessed only through proxy accounts. See Section 6.5.1.4, “No-Login Pluggable Authentication”.
A plugin that authenticates clients that connect from the local host through the Unix socket file. See Section 6.5.1.5, “Socket Peer-Credential Pluggable Authentication”.
A test plugin that checks account credentials and logs success or failure to the server error log. This plugin is intended for testing and development purposes, and as an example of how to write an authentication plugin. See Section 6.5.1.6, “Test Pluggable Authentication”.
For information about current restrictions on the use of pluggable authentication, including which connectors support which plugins, see Section C.9, “Restrictions on Pluggable Authentication”.
Third-party connector developers should read that section to determine the extent to which a connector can take advantage of pluggable authentication capabilities and what steps to take to become more compliant.
If you are interested in writing your own authentication plugins, see Section 28.2.4.9, “Writing Authentication Plugins”.
This section provides general instructions for installing and using authentication plugins. For instructions specific to a given plugin, see the section that describes that plugin.
In general, pluggable authentication uses corresponding plugins on the server and client sides, so you use a given authentication method like this:
If necessary, install the plugin library or libraries containing the appropriate plugins. On the server host, install the library containing the server-side plugin, so that the server can use it to authenticate client connections. Similarly, on each client host, install the library containing the client-side plugin for use by client programs. Authentication plugins that are built in need not be installed.
For each MySQL account that you create, specify the appropriate server-side plugin to use for authentication.
When a client connects, the server-side plugin tells the client program which client-side plugin to use for authentication.
In the case that an account uses an authentication method that is
the default for both the server and the client program, the server
need not communicate to the client which client-side plugin to
use, and a round trip in client/server negotiation can be avoided.
This is true for accounts that use native MySQL authentication
(mysql_native_password
).
The
--default-auth=
option can be specified on the mysql command
line as a hint about which client-side plugin the program can
expect to use, although the server will override this if the
server-side plugin associated with the user account requires a
different client-side plugin.
plugin_name
If the client program does not find the client-side plugin,
specify a
--plugin-dir=
option to indicate where the plugin is located.
dir_name
If you start the server with the
--skip-grant-tables
option,
authentication plugins are not used even if loaded because the
server performs no client authentication and permits any client
to connect. Because this is insecure, you might want to use
--skip-grant-tables
in
conjunction with
--skip-networking
to prevent
remote clients from connecting. As of MySQL 8.0.3, if the server
is started with the
--skip-grant-tables
option, the
server enables --skip-networking
automatically to prevent remote connections.
The MySQL server authenticates client connections using authentication plugins. The plugin that authenticates a given connection may request that the connecting (external) user be treated as a different user for privilege-checking purposes. This enables the external user to be a proxy for the second user; that is, to assume the privileges of the second user:
The external user is a “proxy user” (a user who can impersonate or become known as another user).
The second user is a “proxied user” (a user whose identity and privileges can be assumed by a proxy user).
This section describes how the proxy user capability works. For general information about authentication plugins, see Section 6.3.10, “Pluggable Authentication”. For information about specific plugins, see Section 6.5.1, “Authentication Plugins”. For information about writing authentication plugins that support proxy users, see Section 28.2.4.9.4, “Implementing Proxy User Support in Authentication Plugins”.
For proxying to occur for a given authentication plugin, these conditions must be satisfied:
Proxying must be supported, either by the plugin itself, or by the MySQL server on behalf of the plugin. In the latter case, server support may need to be enabled explicitly; see Server Support for Proxy User Mapping.
The proxy user account must be set up to be authenticated by
the plugin. Use the CREATE
USER
statement to associate an account with an
authentication plugin, or ALTER
USER
to change its plugin.
The proxied user account must be created and granted the
privileges to be assumed by the proxy user. Use the
CREATE USER
and
GRANT
statements for this.
The proxy user account must have the
PROXY
privilege for the
proxied account. Use the
GRANT
statement for this.
For a client connecting to the proxy account to be treated as a proxy user, the authentication plugin must return a user name different from the client user name, to indicate the user name of the proxied account that defines the privileges to be assumed by the proxy user.
Alternatively, for plugins that are provided proxy mapping
by the server, the proxied user is determined from the
PROXY
privilege held by the
proxy user.
The proxy mechanism permits mapping only the client user name to the proxied user name. There is no provision for mapping host names. When a connecting client matches a proxy account, the server attempts to find a match for a proxied account using the user name returned by the authentication plugin and the host name of the proxy account.
Consider the following account definitions:
-- create proxy account CREATE USER 'employee_ext'@'localhost' IDENTIFIED WITH my_auth_plugin AS 'my_auth_string'; -- create proxied account and grant its privileges CREATE USER 'employee'@'localhost' IDENTIFIED BY 'employee_pass'; GRANT ALL ON employees.* TO 'employee'@'localhost'; -- grant PROXY privilege to proxy account for proxied account GRANT PROXY ON 'employee'@'localhost' TO 'employee_ext'@'localhost';
When a client connects as employee_ext
from
the local host, MySQL uses the plugin named
my_auth_plugin
to perform authentication.
Suppose that my_auth_plugin
returns a user
name of employee
to the server, based on the
content of 'my_auth_string'
and perhaps by
consulting some external authentication system. The name
employee
differs from
employee_ext
, so returning
employee
serves as a request to the server to
treat the employee_ext
client, for purposes
of privilege checking, as the employee
local
user.
In this case, employee_ext
is the proxy user
and employee
is the proxied user.
The server verifies that proxy authentication for
employee
is possible for the
employee_ext
user by checking whether
employee_ext
(the proxy user) has the
PROXY
privilege for
employee
(the proxied user). If this
privilege has not been granted, an error occurs. Otherwise,
employee_ext
assumes the privileges of
employee
. The server checks statements
executed during the client session by
employee_ext
against the privileges granted
to employee
. In this case,
employee_ext
can access tables in the
employees
database.
When proxying occurs, the USER()
and CURRENT_USER()
functions can
be used to see the difference between the connecting user (the
proxy user) and the account whose privileges apply during the
current session (the proxied user). For the example just
described, those functions return these values:
mysql> SELECT USER(), CURRENT_USER();
+------------------------+--------------------+
| USER() | CURRENT_USER() |
+------------------------+--------------------+
| employee_ext@localhost | employee@localhost |
+------------------------+--------------------+
In the CREATE USER
statement that
creates the proxy user account, the IDENTIFIED
WITH
clause that names the authentication plugin is
optionally followed by an AS
'
clause
specifying a string that the server passes to the plugin when
the user connects. If present, the string provides information
that helps the plugin determine how to map the external client
user name to a proxied user name. It is up to each plugin
whether it requires the auth_string
'AS
clause. If so, the
format of the authentication string depends on how the plugin
intends to use it. Consult the documentation for a given plugin
for information about the authentication string values it
accepts.
The PROXY
privilege is needed to
enable an external user to connect as and have the privileges of
another user. To grant this privilege, use the
GRANT
statement. For example:
GRANT PROXY ON 'proxied_user
' TO 'proxy_user
';
The statement creates a row in the
mysql.proxies_priv
grant table.
At connection time, proxy_user
must
represent a valid externally authenticated MySQL user, and
proxied_user
must represent a valid
locally authenticated user. Otherwise, the connection attempt
fails.
The corresponding REVOKE
syntax
is:
REVOKE PROXY ON 'proxied_user
' FROM 'proxy_user
';
MySQL GRANT
and
REVOKE
syntax extensions work as
usual. For example:
GRANT PROXY ON 'a' TO 'b', 'c', 'd'; GRANT PROXY ON 'a' TO 'd' WITH GRANT OPTION; GRANT PROXY ON 'a' TO ''@''; REVOKE PROXY ON 'a' FROM 'b', 'c', 'd';
The PROXY
privilege can be
granted in these cases:
By a user that has GRANT PROXY ... WITH GRANT
OPTION
for
proxied_user
.
By proxied_user
for itself: The
value of USER()
must exactly
match CURRENT_USER()
and
proxied_user
, for both the user
name and host name parts of the account name.
The initial root
account created during MySQL
installation has the
PROXY ... WITH GRANT
OPTION
privilege for ''@''
, that
is, for all users and all hosts. This enables
root
to set up proxy users, as well as to
delegate to other accounts the authority to set up proxy users.
For example, root
can do this:
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'test'; GRANT PROXY ON ''@'' TO 'admin'@'localhost' WITH GRANT OPTION;
Those statements create an admin
user that
can manage all GRANT PROXY
mappings. For
example, admin
can do this:
GRANT PROXY ON sally TO joe;
To specify that some or all users should connect using a given
authentication plugin, create a “blank” MySQL
account (''@''
), associate it with that
plugin, and let the plugin return the real authenticated user
name (if different from the blank user). For example, suppose
that there exists a plugin named ldap_auth
that implements LDAP authentication and maps connecting users
onto either a developer or manager account. To set up proxying
of users onto these accounts, use the following statements:
-- create default proxy account CREATE USER ''@'' IDENTIFIED WITH ldap_auth AS 'O=Oracle, OU=MySQL'; -- create proxied accounts CREATE USER 'developer'@'localhost' IDENTIFIED BY 'developer_pass'; CREATE USER 'manager'@'localhost' IDENTIFIED BY 'manager_pass'; -- grant PROXY privilege to default proxy account for proxied accounts GRANT PROXY ON 'manager'@'localhost' TO ''@''; GRANT PROXY ON 'developer'@'localhost' TO ''@'';
Now assume that a client connects as follows:
shell>mysql --user=myuser --password ...
Enter password:myuser_pass
The server will not find myuser
defined as a
MySQL user. But because there is a blank user account
(''@''
) that matches the client user name and
host name, the server authenticates the client against that
account: The server invokes the ldap_auth
authentication plugin and passes myuser
and
myuser_pass
to it as the user name and
password.
If the ldap_auth
plugin finds in the LDAP
directory that myuser_pass
is not the correct
password for myuser
, authentication fails and
the server rejects the connection.
If the password is correct and ldap_auth
finds that myuser
is a developer, it returns
the user name developer
to the MySQL server,
rather than myuser
. Returning a user name
different from the client user name of myuser
signals to the server that it should treat
myuser
as a proxy. The server verifies that
''@''
can authenticate as
developer
(because that account has the
PROXY
privilege to do so) and
accepts the connection. The session proceeds with
myuser
having the privileges of
developer
, the proxied user. (These
privileges should be set up by the DBA using
GRANT
statements, not shown.) The
USER()
and
CURRENT_USER()
functions return
these values:
mysql> SELECT USER(), CURRENT_USER();
+------------------+---------------------+
| USER() | CURRENT_USER() |
+------------------+---------------------+
| myuser@localhost | developer@localhost |
+------------------+---------------------+
If the plugin instead finds in the LDAP directory that
myuser
is a manager, it returns
manager
as the user name and the session
proceeds with myuser
having the privileges of
manager
.
mysql> SELECT USER(), CURRENT_USER();
+------------------+-------------------+
| USER() | CURRENT_USER() |
+------------------+-------------------+
| myuser@localhost | manager@localhost |
+------------------+-------------------+
For simplicity, external authentication cannot be multilevel:
Neither the credentials for developer
nor
those for manager
are taken into account in
the preceding example. However, they are still used if a client
tries to connect and authenticate directly as the
developer
or manager
account, which is why those accounts should be assigned
passwords.
If you intend to create a default proxy user, check for other existing “match any user” accounts that take precedence over the default proxy user because they can prevent that user from working as intended.
In the preceding discussion, the default proxy user account has
''
in the host part, which matches any host.
If you set up a default proxy user, take care to also check
whether nonproxy accounts exist with the same user part and
'%'
in the host part, because
'%'
also matches any host, but has precedence
over ''
by the rules that the server uses to
sort account rows internally (see
Section 6.2.6, “Access Control, Stage 1: Connection Verification”).
Suppose that a MySQL installation includes these two accounts:
-- create default proxy account CREATE USER ''@'' IDENTIFIED WITH some_plugin AS 'some_auth_string'; -- create anonymous account CREATE USER ''@'%' IDENTIFIED BY 'some_password';
The first account (''@''
) is intended as the
default proxy user, used to authenticate connections for users
who do not otherwise match a more-specific account. The second
account (''@'%'
) is an anonymous-user
account, which might have been created, for example, to enable
users without their own account to connect anonymously.
Both accounts have the same user part (''
),
which matches any user. And each account has a host part that
matches any host. Nevertheless, there is a priority in account
matching for connection attempts because the matching rules sort
a host of '%'
ahead of ''
.
For accounts that do not match any more-specific account, the
server attempts to authenticate them against
''@'%'
(the anonymous user) rather than
''@''
(the default proxy user). The result is
that the default proxy account is never used.
To avoid this problem, use one of the following strategies:
Remove the anonymous account so that it does not conflict with the default proxy user. This might be a good idea anyway if you want to associate every connection with a named user.
Use a more-specific default proxy user that matches ahead of
the anonymous user. For example, to permit only
localhost
proxy connections, use
''@'localhost'
:
CREATE USER ''@'localhost' IDENTIFIED WITH some_plugin AS 'some_auth_string';
In addition, modify any GRANT PROXY
statements to name ''@'localhost'
rather
than ''@''
as the proxy user.
Be aware that this strategy prevents anonymous-user
connections from localhost
.
Create multiple proxy users, one for local connections and one for “everything else” (remote connections). This can be useful particularly when local users should have different privileges from remote users.
Create the proxy users:
-- create proxy user for local connections CREATE USER ''@'localhost' IDENTIFIED WITH some_plugin AS 'some_auth_string'; -- create proxy user for remote connections CREATE USER ''@'%' IDENTIFIED WITH some_plugin AS 'some_auth_string';
Create the proxied users:
-- create proxied user for local connections CREATE USER 'developer'@'localhost' IDENTIFIED BY 'some_password'; -- create proxied user for remote connections CREATE USER 'developer'@'%' IDENTIFIED BY 'some_password';
Grant the proxy privilege to each proxy user for the corresponding proxied user:
GRANT PROXY ON 'developer'@'localhost' TO ''@'localhost'; GRANT PROXY ON 'developer'@'%' TO ''@'%';
Finally, grant appropriate privileges to the local and remote proxied users (not shown).
Assume that the
some_plugin
/'some_auth_string'
combination causes some_plugin
to map the
client user name to developer
. Local
connections match the ''@'localhost'
proxy user, which maps to the
'developer'@'localhost'
proxied user.
Remote connections match the ''@'%'
proxy
user, which maps to the 'developer'@'%'
proxied user.
Some authentication plugins implement proxy user mapping for
themselves. For certain others, the MySQL server itself can map
proxy users according to granted proxy privileges. If the
check_proxy_users
system
variable is enabled, the server performs proxy user mapping for
any authentication plugins that request it:
By default,
check_proxy_users
is
disabled, so the server performs no proxy user mapping even
for authentication plugins that request it.
With check_proxy_users
enabled, it may also be necessary to enable plugin-specific
system variables to take advantage of server proxy user
mapping support:
For the mysql_native_password
plugin,
enable
mysql_native_password_proxy_users
.
For the sha256_password
plugin,
enable
sha256_password_proxy_users
.
Proxy user mapping performed by the server is subject to these restrictions:
The server will not proxy to or from an anonymous user, even
if the associated PROXY
privilege is granted.
When a single account has been granted proxy privileges for more than one proxied account, server proxy user mapping is nondeterministic. Therefore, granting to a single account proxy privileges for multiple proxied accounts is discouraged.
Two system variables help trace the proxy login process:
proxy_user
: This value is
NULL
if proxying is not used. Otherwise,
it indicates the proxy user account. For example, if a
client authenticates through the ''@''
proxy account, this variable is set as follows:
mysql> SELECT @@proxy_user;
+--------------+
| @@proxy_user |
+--------------+
| ''@'' |
+--------------+
external_user
: Sometimes
the authentication plugin may use an external user to
authenticate to the MySQL server. For example, when using
Windows native authentication, a plugin that authenticates
using the windows API does not need the login ID passed to
it. However, it still uses a Windows user ID to
authenticate. The plugin may return this external user ID
(or the first 512 UTF-8 bytes of it) to the server using the
external_user
read-only session variable.
If the plugin does not set this variable, its value is
NULL
.
MySQL supports locking and unlocking user accounts using the
ACCOUNT LOCK
and ACCOUNT
UNLOCK
clauses for the CREATE
USER
and ALTER USER
statements:
When used with CREATE USER
,
these clauses specify the initial locking state for a new
account. In the absence of either clause, the account is
created in an unlocked state.
When used with ALTER USER
,
these clauses specify the new locking state for an existing
account. In the absence of either clause, the account locking
state remains unchanged.
Account locking state is recorded in the
account_locked
column of the
mysql.user
table. The output from
SHOW CREATE USER
indicates whether
an account is locked or unlocked.
If a client attempts to connect to a locked account, the attempt
fails. The server increments the
Locked_connects
status variable
that indicates the number of attempts to connect to a locked
account, returns an
ER_ACCOUNT_HAS_BEEN_LOCKED
error,
and writes a message to the error log:
Access denied for user 'user_name
'@'host_name
'. Account is locked.
Locking an account does not affect being able to connect using a
proxy user that assumes the identity of the locked account. It
also does not affect the ability to execute stored programs or
views that have a DEFINER
clause naming the
locked account. That is, the ability to use a proxied account or
stored programs or views is not affected by locking the account.
The account-locking capability depends on the presence of the
account_locked
column in the
mysql.user
table. For upgrades to MySQL 5.7.6
and later from older versions, run
mysql_upgrade to ensure that this column
exists. For nonupgraded installations that have no
account_locked
column, the server treats all
accounts as unlocked, and using the ACCOUNT
LOCK
or ACCOUNT UNLOCK
clauses
produces an error.
Applications can use the following guidelines to perform SQL-based auditing that ties database activity to MySQL accounts.
MySQL accounts correspond to rows in the
mysql.user
table. When a client connects
successfully, the server authenticates the client to a particular
row in this table. The User
and
Host
column values in this row uniquely
identify the account and correspond to the
'
format in which account names are written in SQL statements.
user_name
'@'host_name
'
The account used to authenticate a client determines which
privileges the client has. Normally, the
CURRENT_USER()
function can be
invoked to determine which account this is for the client user.
Its value is constructed from the User
and
Host
columns of the user
table row for the account.
However, there are circumstances under which the
CURRENT_USER()
value corresponds
not to the client user but to a different account. This occurs in
contexts when privilege checking is not based the client's
account:
Stored routines (procedures and functions) defined with the
SQL SECURITY DEFINER
characteristic
Views defined with the SQL SECURITY DEFINER
characteristic
Triggers and events
In those contexts, privilege checking is done against the
DEFINER
account and
CURRENT_USER()
refers to that
account, not to the account for the client who invoked the stored
routine or view or who caused the trigger to activate. To
determine the invoking user, you can call the
USER()
function, which returns a
value indicating the actual user name provided by the client and
the host from which the client connected. However, this value does
not necessarily correspond directly to an account in the
user
table, because the
USER()
value never contains
wildcards, whereas account values (as returned by
CURRENT_USER()
) may contain user
name and host name wildcards.
For example, a blank user name matches any user, so an account of
''@'localhost'
enables clients to connect as an
anonymous user from the local host with any user name. In this
case, if a client connects as user1
from the
local host, USER()
and
CURRENT_USER()
return different
values:
mysql> SELECT USER(), CURRENT_USER();
+-----------------+----------------+
| USER() | CURRENT_USER() |
+-----------------+----------------+
| user1@localhost | @localhost |
+-----------------+----------------+
The host name part of an account can contain wildcards, too. If
the host name contains a '%'
or
'_'
pattern character or uses netmask notation,
the account can be used for clients connecting from multiple hosts
and the CURRENT_USER()
value will
not indicate which one. For example, the account
'user2'@'%.example.com'
can be used by
user2
to connect from any host in the
example.com
domain. If user2
connects from remote.example.com
,
USER()
and
CURRENT_USER()
return different
values:
mysql> SELECT USER(), CURRENT_USER();
+--------------------------+---------------------+
| USER() | CURRENT_USER() |
+--------------------------+---------------------+
| user2@remote.example.com | user2@%.example.com |
+--------------------------+---------------------+
If an application must invoke
USER()
for user auditing (for
example, if it does auditing from within triggers) but must also
be able to associate the USER()
value with an account in the user
table, it is
necessary to avoid accounts that contain wildcards in the
User
or Host
column.
Specifically, do not permit User
to be empty
(which creates an anonymous-user account), and do not permit
pattern characters or netmask notation in Host
values. All accounts must have a nonempty User
value and literal Host
value.
With respect to the previous examples, the
''@'localhost'
and
'user2'@'%.example.com'
accounts should be
changed not to use wildcards:
RENAME USER ''@'localhost' TO 'user1'@'localhost'; RENAME USER 'user2'@'%.example.com' TO 'user2'@'remote.example.com';
If user2
must be able to connect from several
hosts in the example.com
domain, there should
be a separate account for each host.
To extract the user name or host name part from a
CURRENT_USER()
or
USER()
value, use the
SUBSTRING_INDEX()
function:
mysql>SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',1);
+---------------------------------------+ | SUBSTRING_INDEX(CURRENT_USER(),'@',1) | +---------------------------------------+ | user1 | +---------------------------------------+ mysql>SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',-1);
+----------------------------------------+ | SUBSTRING_INDEX(CURRENT_USER(),'@',-1) | +----------------------------------------+ | localhost | +----------------------------------------+
With an unencrypted connection between the MySQL client and the server, someone with access to the network could watch all your traffic and inspect the data being sent or received between client and server.
When you must move information over a network in a secure fashion, an unencrypted connection is unacceptable. To make any kind of data unreadable, use encryption. Encryption algorithms must include security elements to resist many kinds of known attacks such as changing the order of encrypted messages or replaying data twice.
MySQL supports encrypted connections between clients and the server using the TLS (Transport Layer Security) protocol. TLS is sometimes referred to as SSL (Secure Sockets Layer) but MySQL does not actually use the SSL protocol for encrypted connections because its encryption is weak (see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”).
TLS uses encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect data change, loss, or replay. TLS also incorporates algorithms that provide identity verification using the X509 standard.
X509 makes it possible to identify someone on the Internet. In basic terms, there should be some entity called a “Certificate Authority” (or CA) that assigns electronic certificates to anyone who needs them. Certificates rely on asymmetric encryption algorithms that have two encryption keys (a public key and a secret key). A certificate owner can present the certificate to another party as proof of identity. A certificate consists of its owner's public key. Any data encrypted using this public key can be decrypted only using the corresponding secret key, which is held by the owner of the certificate.
MySQL can be compiled for encrypted-connection support using OpenSSL or yaSSL. For a comparison of the two packages, see Section 6.4.4, “OpenSSL Versus yaSSL” For information about the encryption protocols and ciphers each package supports, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
By default, MySQL programs attempt to connect using encryption if the server supports encrypted connections, falling back to an unencrypted connection if an encrypted connection cannot be established. For information about options that affect use of encrypted connections, see Section 6.4.1, “Configuring MySQL to Use Encrypted Connections” and Section 6.4.2, “Command Options for Encrypted Connections”.
MySQL performs encryption on a per-connection basis, and use of
encryption for a given user can be optional or mandatory. This
enables you to choose an encrypted or unencrypted connection
according to the requirements of individual applications. For
information on how to require users to use encrypted connections,
see the discussion of the REQUIRE
clause of the
CREATE USER
statement in
Section 13.7.1.3, “CREATE USER Syntax”. See also the description of the
require_secure_transport
system
variable at Section 5.1.5, “Server System Variables”
Encrypted connections can be used between master and slave replication servers. See Section 18.3.9, “Setting Up Replication to Use Encrypted Connections”.
For information about using encrypted connections from the MySQL C API, see Section 27.7.18, “C API Encrypted Connection Support”.
It is also possible to connect using encryption from within an SSH connection to the MySQL server host. For an example, see Section 6.4.7, “Connecting to MySQL Remotely from Windows with SSH”.
Several options are available to indicate whether to use encrypted connections, and to specify the appropriate certificate and key files. This section provides general guidance about configuring the server and clients for encrypted connections:
For a complete list of options related to establishment of encrypted connections, see Section 6.4.2, “Command Options for Encrypted Connections”. If you need to create the required certificate and key files, see Section 6.4.3, “Creating SSL and RSA Certificates and Keys”.
Encrypted connections can be used between master and slave replication servers. See Section 18.3.9, “Setting Up Replication to Use Encrypted Connections”.
Encrypted connections are available through the MySQL C API. See Section 27.7.18, “C API Encrypted Connection Support”.
On the server side, the --ssl
option specifies that the server permits but does not require
encrypted connections. This option is enabled by default.
These options on the server side identify the certificate and key files the server uses when permitting clients to establish encrypted connections:
--ssl-ca
identifies the
Certificate Authority (CA) certificate.
--ssl-cert
identifies the
server public key certificate. This can be sent to the
client and authenticated against the CA certificate that it
has.
--ssl-key
identifies the
server private key.
For example, to enable the server for encrypted connections,
start it with these lines in the my.cnf
file, changing the file names as necessary:
[mysqld] ssl-ca=ca.pem ssl-cert=server-cert.pem ssl-key=server-key.pem
Each option names a file in PEM format. If you need to create
the required certificate and key files, see
Section 6.4.3, “Creating SSL and RSA Certificates and Keys”. Alternatively, if you
have a MySQL source distribution, you can test your setup using
the demonstration certificate and key files in its
mysql-test/std_data
directory.
MySQL servers compiled using OpenSSL can generate missing certificate and key files automatically at startup. See Section 6.4.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”.
The server performs certificate and key file autodiscovery. If
--ssl
is enabled (possibly along
with --ssl-cipher
) and other
--ssl-
options
are not given to configure encrypted connections explicitly, the
server attempts to enable support for encrypted connections
automatically at startup:
xxx
If the server discovers valid certificate and key files
named ca.pem
,
server-cert.pem
, and
server-key.pem
in the data directory,
it enables support for encrypted connections by clients.
(The files need not have been generated automatically; what
matters is that they have the indicated names and are
valid.)
If the server does not find valid certificate and key files in the data directory, it continues executing but without support for encrypted connections.
If the server automatically enables support for encrypted connections, it writes a note to the error log. If the server discovers that the CA certificate is self-signed, it writes a warning to the error log. (The certificate is self-signed if created automatically by the server, or manually using mysql_ssl_rsa_setup.)
The server uses the names of any automatically discovered and
used certificate and key files to set the corresponding system
variables (ssl_ca
,
ssl_cert
,
ssl_key
).
For further control over whether clients must connect using
encryption, use the
require_secure_transport
system
variable; see Section 5.1.5, “Server System Variables”. To
specify permitted encryption protocols explicitly, use the
tls_version
system variable;
see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
By default, MySQL client programs attempt to establish an
encrypted connection if the server supports encrypted
connections, with further control available through the
--ssl-mode
option:
In the absence of an
--ssl-mode
option, clients
attempt to connect using encryption, falling back to an
unencrypted connection if an encrypted connection cannot be
established. This is also the behavior with an explicit
--ssl-mode=PREFFERED
option.
With --ssl-mode=REQUIRED
,
clients require an encrypted connection and fail if one
cannot be established.
With --ssl-mode=DISABLED
,
clients use an unencrypted connection.
For additional security, the following options on the client
side identify the certificate and key files clients use when
establishing encrypted connections to the server. They are
similar to the options used on the server side, but
--ssl-cert
and
--ssl-key
identify the client
public and private key:
--ssl-ca
identifies the
Certificate Authority (CA) certificate. This option, if
used, must specify the same certificate used by the server.
--ssl-cert
identifies the
client public key certificate.
--ssl-key
identifies the
client private key.
Depending on the encryption requirements of the MySQL account used by a client, the client may be required to specify certain options to connect using encryption to a MySQL server that supports encrypted connections.
Suppose that you want to connect using an account that has no
special encryption requirements or was created using a
CREATE USER
statement that
includes the REQUIRE SSL
option. Assuming
that the server supports encrypted connections, a client can
connect using encryption with no
--ssl-mode
option or with an
explicit --ssl-mode=PREFFERED
option:
mysql
Or:
mysql --ssl-mode=PREFERRED
For an account with REQUIRE SSL
, the
connection attempt fails if an encrypted connection cannot be
established. For an account with no special encryption
requirements, the attempt falls back to an unencrypted
connection if an encrypted connection cannot be established. To
prevent fallback and fail if an encrypted connection cannot be
obtained, connect like this:
mysql --ssl-mode=REQUIRED
If the account has more stringent security requirements, other options must be specified to establish an encrypted connection:
For accounts with REQUIRE X509
, clients
must specify at least
--ssl-cert
and
--ssl-key
. In addition,
--ssl-ca
is recommended so
that the public certificate provided by the server can be
verified. For example:
mysql --ssl-ca=ca.pem \ --ssl-cert=client-cert.pem \ --ssl-key=client-key.pem
For accounts that have REQUIRE ISSUER
or
REQUIRE SUBJECT
, the option requirements
are the same as for REQUIRE X509
, but the
certificate must match the issue or subject, respectively,
specified in the account definition.
For additional information about the REQUIRE
clause, see the discussion in Section 13.7.1.3, “CREATE USER Syntax”.
To prevent use of encryption and override other
--ssl-
options,
invoke the client program with
xxx
--ssl-mode=DISABLED
:
mysql --ssl-mode=DISABLED
To specify permitted encryption protocols explicitly, use the
--tls-version
option; see
Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
To determine whether the current connection with the server uses
encryption, check the value of the
Ssl_cipher
status variable. If
the value is empty, the connection is not encrypted. Otherwise,
the connection is encrypted and the value indicates the
encryption cipher. For example:
mysql> SHOW SESSION STATUS LIKE 'Ssl_cipher';
+---------------+---------------------------+
| Variable_name | Value |
+---------------+---------------------------+
| Ssl_cipher | DHE-RSA-AES128-GCM-SHA256 |
+---------------+---------------------------+
For the mysql client, an alternative is to
use the STATUS
or \s
command and check the SSL
line:
mysql> \s
...
SSL: Not in use
...
Or:
mysql> \s
...
SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256
...
This section describes options that specify whether to use encrypted connections, the names of certificate and key files, and other parameters related to encrypted-connection support. These options can be given on the command line or in an option file. For examples of suggested use and how to check whether a connection is encrypted, see Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.
For information about using encrypted connections from the MySQL C API, see Section 27.7.18, “C API Encrypted Connection Support”.
Table 6.9 Encrypted-Connection Option Summary
Format | Description |
---|---|
--skip-ssl | Do not use encrypted connection |
--ssl | Enable encrypted connection |
--ssl-ca | Path of file that contains list of trusted SSL CAs |
--ssl-capath | Path of directory that contains trusted SSL CA certificates in PEM format |
--ssl-cert | Path of file that contains X509 certificate in PEM format |
--ssl-cipher | List of permitted ciphers to use for connection encryption |
--ssl-crl | Path of file that contains certificate revocation lists |
--ssl-crlpath | Path of directory that contains certificate revocation list files |
--ssl-key | Path of file that contains X509 key in PEM format |
--ssl-mode | Security state of connection to server |
--tls-version | Protocols permitted for encrypted connections |
The client-side --ssl
option
is removed in MySQL 8.0. For client programs,
use --ssl-mode
instead.
On the server side, the --ssl
option specifies that the server permits but does not require
encrypted connections. The option is enabled on the server
side by default. --ssl
is
implied by other
--ssl-
options,
as indicated in the descriptions for those options.
xxx
The --ssl
option in negated
form indicates that encryption should not
be used and overrides other
--ssl-
options.
Specify the option as xxx
--ssl=0
or a synonym
(--skip-ssl
,
--disable-ssl
).
To specify additional parameters for encrypted connections,
use at least --ssl-cert
and
--ssl-key
on the server side
and --ssl-ca
on the client
side. See Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”. That
section also describes server capabilities for certificate and
key file autogeneration and autodiscovery.
The path to a file in PEM format that contains a list of
trusted SSL certificate authorities. On the server side, this
option implies --ssl
.
If you use encryption when establishing a client connection,
to tell the client not to authenticate the server certificate,
specify neither --ssl-ca
nor
--ssl-capath
. The server still
verifies the client according to any applicable requirements
established for the client account, and it still uses any
--ssl-ca
or
--ssl-capath
option values
specified at server startup.
The path to a directory that contains trusted SSL certificate
authority certificates in PEM format. On the server side, this
option implies --ssl
.
If you use encryption when establishing a client connection,
to tell the client not to authenticate the server certificate,
specify neither --ssl-ca
nor
--ssl-capath
. The server still
verifies the client according to any applicable requirements
established for the client account, and it still uses any
--ssl-ca
or
--ssl-capath
option values
specified at server startup.
MySQL distributions compiled using OpenSSL support the
--ssl-capath
option (see
Section 6.4.4, “OpenSSL Versus yaSSL”). Distributions
compiled using yaSSL do not because yaSSL does not look in any
directory and does not follow a chained certificate tree.
yaSSL requires that all components of the CA certificate tree
be contained within a single CA certificate tree and that each
certificate in the file has a unique SubjectName value. To
work around this yaSSL limitation, concatenate the individual
certificate files comprising the certificate tree into a new
file and specify that file as the value of the
--ssl-ca
option.
The name of the SSL certificate file in PEM format to use for
establishing an encrypted connection. On the server side, this
option implies --ssl
.
A list of permissible ciphers to use for connection
encryption. If no cipher in the list is supported, encrypted
connections will not work. On the server side, this option
implies --ssl
.
For greatest portability,
cipher_list
should be a list of one
or more cipher names, separated by colons. This format is
understood both by OpenSSL and yaSSL. Examples:
--ssl-cipher=AES128-SHA --ssl-cipher=DHE-RSA-AES128-GCM-SHA256:AES128-SHA
OpenSSL supports a more flexible syntax for specifying ciphers, as described in the OpenSSL documentation at https://www.openssl.org/docs/manmaster/man1/ciphers.html. yaSSL does not, so attempts to use that extended syntax fail for a MySQL distribution compiled using yaSSL.
For information about which encryption ciphers MySQL supports, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
The path to a file containing certificate revocation lists in
PEM format. On the server side, this option implies
--ssl
.
If neither --ssl-crl
nor
--ssl-crlpath
is given, no CRL
checks are performed, even if the CA path contains certificate
revocation lists.
MySQL distributions compiled using OpenSSL support the
--ssl-crl
option (see
Section 6.4.4, “OpenSSL Versus yaSSL”). Distributions
compiled using yaSSL do not because revocation lists do not
work with yaSSL.
The path to a directory that contains files containing
certificate revocation lists in PEM format. On the server
side, this option implies
--ssl
.
If neither --ssl-crl
nor
--ssl-crlpath
is given, no CRL
checks are performed, even if the CA path contains certificate
revocation lists.
MySQL distributions compiled using OpenSSL support the
--ssl-crlpath
option (see
Section 6.4.4, “OpenSSL Versus yaSSL”). Distributions
compiled using yaSSL do not because revocation lists do not
work with yaSSL.
The name of the SSL key file in PEM format to use for
establishing an encrypted connection. On the server side, this
option implies --ssl
.
If the key file is protected by a passphrase, the program prompts the user for the passphrase. The password must be given interactively; it cannot be stored in a file. If the passphrase is incorrect, the program continues as if it could not read the key.
For better security, use a certificate with an RSA key size of at least 2048 bits.
This option is available only for client programs, not the server. It specifies the security state of the connection to the server. These option values are permitted:
PREFERRED
: Establish an encrypted
connection if the server supports encrypted connections,
falling back to an unencrypted connection if an encrypted
connection cannot be established. This is the default if
--ssl-mode
is not
specified.
REQUIRED
: Establish an encrypted
connection if the server supports encrypted connections.
The connection attempt fails if an encrypted connection
cannot be established.
VERIFY_CA
: Like
REQUIRED
, but additionally verify the
server TLS certificate against the configured Certificate
Authority (CA) certificates. The connection attempt fails
if no valid matching CA certificates are found.
VERIFY_IDENTITY
: Like
VERIFY_CA
, but additionally verify that
the server certificate matches the host to which the
connection is attempted. The connection attempt fails if
there is a mismatch.
DISABLED
: Establish an unencrypted
connection.
If --ssl-mode
is not
explicitly set otherwise, use of the
--ssl-ca
or
--ssl-capath
option implies
--ssl-mode=VERIFY_CA
. However,
if --ssl-mode
is explicit, use
of values other than VERIFY_CA
or
VERIFY_IDENTITY
together with an explicit
--ssl-ca
or
--ssl-capath
option produces a
warning that no verification of the server certificate will be
done, despite CA certificate options being specified.
To require use of encrypted connections by a MySQL account,
use CREATE USER
to create the
account with a REQUIRE SSL
clause, or use
ALTER USER
for an existing
account to add a REQUIRE SSL
clause.
Connection attempts by clients that use the account will be
rejected unless MySQL supports encrypted connections and an
encrypted connection can be established.
The REQUIRE
clause permits other
encryption-related options, which can be used to enforce
security requirements stricter than REQUIRE
SSL
. For additional details about which command
options may or must be specified by clients that connect using
accounts configured using the various
REQUIRE
options, see the description of
REQUIRE
in Section 13.7.1.3, “CREATE USER Syntax”.
For client programs, the protocols permitted by the client for encrypted connections. The value is a comma-separated list containing one or more protocol names. For example:
mysql --tls-version="TLSv1.1,TLSv1.2"
The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
On the server side, use the
tls_version
system variable
instead.
The following discussion describes how to create the files required for SSL and RSA support in MySQL. File creation can be performed using facilities provided by MySQL itself, or by invoking the openssl command directly.
SSL certificate and key files enable MySQL to support sencrypted connections using SSL. See Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.
RSA key files enable MySQL to support secure password exchange
over unencrypted connections for accounts authenticated by the
sha256_password
plugin. See
Section 6.5.1.2, “SHA-256 Pluggable Authentication”.
MySQL provides these ways to create the SSL certificate and key files and RSA key-pair files required to support encrypted connections using SSL and secure password exchange using RSA over unencrypted connections, if those files are missing:
The server can autogenerate these files at startup, for MySQL distributions compiled using OpenSSL.
Users can invoke the mysql_ssl_rsa_setup utility manually.
For some distribution types, such as RPM packages, mysql_ssl_rsa_setup invocation occurs during data directory initialization. In this case, the MySQL distribution need not have been compiled using OpenSSL as long as the openssl command is available.
Server autogeneration and mysql_ssl_rsa_setup help lower the barrier to using SSL by making it easier to generate the required files. However, certificates generated by these methods are self-signed, which may not be very secure. After you gain experience using such files, consider obtaining certificate/key material from a registered certificate authority.
MySQL servers have the capability of automatically generating
missing SSL and RSA files at startup, for MySQL distributions
compiled using OpenSSL. The
auto_generate_certs
and
sha256_password_auto_generate_rsa_keys
system variables control automatic generation of these files.
Both variables are enabled by default. They can be enabled at
startup and inspected but not set at runtime.
At startup, the server automatically generates server-side and
client-side SSL certificate and key files in the data
directory if the
auto_generate_certs
system
variable is enabled, no SSL options other than
--ssl
are specified, and the
server-side SSL files are missing from the data directory.
These files enable encrypted client connections using SSL; see
Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.
The server checks the data directory for SSL files with the following names:
ca.pem server-cert.pem server-key.pem
If any of those files are present, the server creates no SSL files. Otherwise, it creates them, plus some additional files:
ca.pem Self-signed CA certificate ca-key.pem CA private key server-cert.pem Server certificate server-key.pem Server private key client-cert.pem Client certificate client-key.pem Client private key
If the server autogenerates SSL files, it uses the names
of the ca.pem
,
server-cert.pem
, and
server-key.pem
files to set the
corresponding system variables
(ssl_ca
,
ssl_cert
,
ssl_key
).
At startup, the server automatically generates RSA
private/public key-pair files in the data directory if the
sha256_password_auto_generate_rsa_keys
system variable is enabled, no RSA options are specified, and
the RSA files are missing from the data directory. These files
enable secure password exchange using RSA over unencrypted
connections for accounts authenticated by the
sha256_password
plugin; see
Section 6.5.1.2, “SHA-256 Pluggable Authentication”.
The server checks the data directory for RSA files with the following names:
private_key.pem Private member of private/public key pair public_key.pem Public member of private/public key pair
If any of these files are present, the server creates no RSA files. Otherwise, it creates them.
If the server autogenerates the RSA files, it uses their
names to set the corresponding system variables
(sha256_password_private_key_path
,
sha256_password_public_key_path
).
MySQL distributions include a mysql_ssl_rsa_setup utility that can be invoked manually to generate SSL and RSA files. This utility is included with all MySQL distributions (whether compiled using OpenSSL or yaSSL), but it does require that the openssl command be available. For usage instructions, see Section 4.4.3, “mysql_ssl_rsa_setup — Create SSL/RSA Files”.
SSL and RSA files created automatically by the server or by invoking mysql_ssl_rsa_setup have these characteristics:
SSL and RSA keys are have a size of 2048 bits.
The SSL CA certificate is self signed.
The SSL server and client certificates are signed with the
CA certificate and key, using the
sha256WithRSAEncryption
signature
algorithm.
SSL certificates use these Common Name (CN) values, with the appropriate certificate type (CA, Server, Client):
ca.pem: MySQL_Server_suffix
_Auto_Generated_CA_Certificate server-cert.pm: MySQL_Server_suffix
_Auto_Generated_Server_Certificate client-cert.pm: MySQL_Server_suffix
_Auto_Generated_Client_Certificate
The suffix
value is based on
the MySQL version number. For files generated by
mysql_ssl_rsa_setup, the suffix can be
specified explicitly using the
--suffix
option.
For files generated by the server, if the resulting CN
values exceed 64 characters, the
_
portion of the name is omitted.
suffix
SSL files have blank values for Country (C), State or Province (ST), Organization (O), Organization Unit Name (OU) and email address.
SSL files created by the server or by mysql_ssl_rsa_setup are valid for ten years from the time of generation.
RSA files do not expire.
SSL files have different serial numbers for each certificate/key pair (1 for CA, 2 for Server, 3 for Client).
Files created automatically by the server are owned by the
account that runs the server. Files created using
mysql_ssl_rsa_setup are owned by the
user who invoked that program. This can be changed on
systems that support the chown()
system
call if the program is invoked by root
and the --uid
option is given to specify the user who should own the
files.
On Unix and Unix-like systems, the file access mode is 644 for certificate files (that is, world readable) and 600 for key files (that is, accessible only by the account that runs the server).
To see the contents of an SSL certificate (for example, to check the range of dates over which it is valid), invoke openssl directly:
openssl x509 -text -in ca.pem openssl x509 -text -in server-cert.pem openssl x509 -text -in client-cert.pem
It is also possible to check SSL certificate expiration information using this SQL statement:
mysql> SHOW STATUS LIKE 'Ssl_server_not%';
+-----------------------+--------------------------+
| Variable_name | Value |
+-----------------------+--------------------------+
| Ssl_server_not_after | Apr 28 14:16:39 2027 GMT |
| Ssl_server_not_before | May 1 14:16:39 2017 GMT |
+-----------------------+--------------------------+
This section describes how to use the openssl command to set up SSL certificate and key files for use by MySQL servers and clients. The first example shows a simplified procedure such as you might use from the command line. The second shows a script that contains more detail. The first two examples are intended for use on Unix and both use the openssl command that is part of OpenSSL. The third example describes how to set up SSL files on Windows.
There are easier alternatives to generating the files required for SSL than the procedure described here: Let the server autogenerate them or use the mysql_ssl_rsa_setup program. See Section 6.4.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”.
Whatever method you use to generate the certificate and key files, the Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. A typical error in this case is:
ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)
The following example shows a set of commands to create MySQL server and client certificate and key files. You will need to respond to several prompts by the openssl commands. To generate test files, you can press Enter to all prompts. To generate files for production use, you should provide nonempty responses.
# Create clean environment rm -rf newcerts mkdir newcerts && cd newcerts # Create CA certificate openssl genrsa 2048 > ca-key.pem openssl req -new -x509 -nodes -days 3600 \ -key ca-key.pem -out ca.pem # Create server certificate, remove passphrase, and sign it # server-cert.pem = public key, server-key.pem = private key openssl req -newkey rsa:2048 -days 3600 \ -nodes -keyout server-key.pem -out server-req.pem openssl rsa -in server-key.pem -out server-key.pem openssl x509 -req -in server-req.pem -days 3600 \ -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem # Create client certificate, remove passphrase, and sign it # client-cert.pem = public key, client-key.pem = private key openssl req -newkey rsa:2048 -days 3600 \ -nodes -keyout client-key.pem -out client-req.pem openssl rsa -in client-key.pem -out client-key.pem openssl x509 -req -in client-req.pem -days 3600 \ -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
After generating the certificates, verify them:
openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
You should see a response like this:
server-cert.pem: OK client-cert.pem: OK
To see the contents of a certificate (for example, to check the range of dates over which a certificate is valid), invoke openssl like this:
openssl x509 -text -in ca.pem openssl x509 -text -in server-cert.pem openssl x509 -text -in client-cert.pem
Now you have a set of files that can be used as follows:
ca.pem
: Use this as the argument to
--ssl-ca
on the server and
client sides. (The CA certificate, if used, must be the
same on both sides.)
server-cert.pem
,
server-key.pem
: Use these as the
arguments to --ssl-cert
and --ssl-key
on the
server side.
client-cert.pem
,
client-key.pem
: Use these as the
arguments to --ssl-cert
and --ssl-key
on the
client side.
For additional usage instructions, see Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.
Here is an example script that shows how to set up SSL certificate and key files for MySQL. After executing the script, use the files for SSL connections as described in Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.
DIR=`pwd`/openssl PRIV=$DIR/private mkdir $DIR $PRIV $DIR/newcerts cp /usr/share/ssl/openssl.cnf $DIR replace ./demoCA $DIR -- $DIR/openssl.cnf # Create necessary files: $database, $serial and $new_certs_dir # directory (optional) touch $DIR/index.txt echo "01" > $DIR/serial # # Generation of Certificate Authority(CA) # openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/ca.pem \ -days 3600 -config $DIR/openssl.cnf # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Generating a 1024 bit RSA private key # ................++++++ # .........++++++ # writing new private key to '/home/monty/openssl/private/cakey.pem' # Enter PEM pass phrase: # Verifying password - Enter PEM pass phrase: # ----- # You are about to be asked to enter information that will be # incorporated into your certificate request. # What you are about to enter is what is called a Distinguished Name # or a DN. # There are quite a few fields but you can leave some blank # For some fields there will be a default value, # If you enter '.', the field will be left blank. # ----- # Country Name (2 letter code) [AU]:FI # State or Province Name (full name) [Some-State]:. # Locality Name (eg, city) []: # Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB # Organizational Unit Name (eg, section) []: # Common Name (eg, YOUR name) []:MySQL admin # Email Address []: # # Create server request and key # openssl req -new -keyout $DIR/server-key.pem -out \ $DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Generating a 1024 bit RSA private key # ..++++++ # ..........++++++ # writing new private key to '/home/monty/openssl/server-key.pem' # Enter PEM pass phrase: # Verifying password - Enter PEM pass phrase: # ----- # You are about to be asked to enter information that will be # incorporated into your certificate request. # What you are about to enter is what is called a Distinguished Name # or a DN. # There are quite a few fields but you can leave some blank # For some fields there will be a default value, # If you enter '.', the field will be left blank. # ----- # Country Name (2 letter code) [AU]:FI # State or Province Name (full name) [Some-State]:. # Locality Name (eg, city) []: # Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB # Organizational Unit Name (eg, section) []: # Common Name (eg, YOUR name) []:MySQL server # Email Address []: # # Please enter the following 'extra' attributes # to be sent with your certificate request # A challenge password []: # An optional company name []: # # Remove the passphrase from the key # openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem # # Sign server cert # openssl ca -cert $DIR/ca.pem -policy policy_anything \ -out $DIR/server-cert.pem -config $DIR/openssl.cnf \ -infiles $DIR/server-req.pem # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Enter PEM pass phrase: # Check that the request matches the signature # Signature ok # The Subjects Distinguished Name is as follows # countryName :PRINTABLE:'FI' # organizationName :PRINTABLE:'MySQL AB' # commonName :PRINTABLE:'MySQL admin' # Certificate is to be certified until Sep 13 14:22:46 2003 GMT # (365 days) # Sign the certificate? [y/n]:y # # # 1 out of 1 certificate requests certified, commit? [y/n]y # Write out database with 1 new entries # Data Base Updated # # Create client request and key # openssl req -new -keyout $DIR/client-key.pem -out \ $DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Generating a 1024 bit RSA private key # .....................................++++++ # .............................................++++++ # writing new private key to '/home/monty/openssl/client-key.pem' # Enter PEM pass phrase: # Verifying password - Enter PEM pass phrase: # ----- # You are about to be asked to enter information that will be # incorporated into your certificate request. # What you are about to enter is what is called a Distinguished Name # or a DN. # There are quite a few fields but you can leave some blank # For some fields there will be a default value, # If you enter '.', the field will be left blank. # ----- # Country Name (2 letter code) [AU]:FI # State or Province Name (full name) [Some-State]:. # Locality Name (eg, city) []: # Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB # Organizational Unit Name (eg, section) []: # Common Name (eg, YOUR name) []:MySQL user # Email Address []: # # Please enter the following 'extra' attributes # to be sent with your certificate request # A challenge password []: # An optional company name []: # # Remove the passphrase from the key # openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem # # Sign client cert # openssl ca -cert $DIR/ca.pem -policy policy_anything \ -out $DIR/client-cert.pem -config $DIR/openssl.cnf \ -infiles $DIR/client-req.pem # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Enter PEM pass phrase: # Check that the request matches the signature # Signature ok # The Subjects Distinguished Name is as follows # countryName :PRINTABLE:'FI' # organizationName :PRINTABLE:'MySQL AB' # commonName :PRINTABLE:'MySQL user' # Certificate is to be certified until Sep 13 16:45:17 2003 GMT # (365 days) # Sign the certificate? [y/n]:y # # # 1 out of 1 certificate requests certified, commit? [y/n]y # Write out database with 1 new entries # Data Base Updated # # Create a my.cnf file that you can use to test the certificates # cat <<EOF > $DIR/my.cnf [client] ssl-ca=$DIR/ca.pem ssl-cert=$DIR/client-cert.pem ssl-key=$DIR/client-key.pem [mysqld] ssl-ca=$DIR/ca.pem ssl-cert=$DIR/server-cert.pem ssl-key=$DIR/server-key.pem EOF
Download OpenSSL for Windows if it is not installed on your system. An overview of available packages can be seen here:
http://www.slproweb.com/products/Win32OpenSSL.html
Choose the Win32 OpenSSL Light or Win64 OpenSSL Light package,
depending on your architecture (32-bit or 64-bit). The default
installation location will be
C:\OpenSSL-Win32
or
C:\OpenSSL-Win64
, depending on which
package you downloaded. The following instructions assume a
default location of C:\OpenSSL-Win32
.
Modify this as necessary if you are using the 64-bit package.
If a message occurs during setup indicating
'...critical component is missing: Microsoft Visual
C++ 2008 Redistributables'
, cancel the setup and
download one of the following packages as well, again
depending on your architecture (32-bit or 64-bit):
Visual C++ 2008 Redistributables (x86), available at:
http://www.microsoft.com/downloads/details.aspx?familyid=9B2DA534-3E03-4391-8A4D-074B9F2BC1BF
Visual C++ 2008 Redistributables (x64), available at:
http://www.microsoft.com/downloads/details.aspx?familyid=bd2a6171-e2d6-4230-b809-9a8d7548c1b6
After installing the additional package, restart the OpenSSL setup procedure.
During installation, leave the default
C:\OpenSSL-Win32
as the install path, and
also leave the default option 'Copy OpenSSL DLL files
to the Windows system directory'
selected.
When the installation has finished, add
C:\OpenSSL-Win32\bin
to the Windows
System Path variable of your server (depending on your version
of Windows, the following path-setting instructions might
differ slightly):
On the Windows desktop, right-click the My Computer icon, and select .
Select the
tab from the menu that appears, and click the button.Under System Variables, select , then click the button. The dialogue should appear.
Add ';C:\OpenSSL-Win32\bin'
to the end
(notice the semicolon).
Press OK 3 times.
Check that OpenSSL was correctly integrated into the Path variable by opening a new command console (Start>Run>cmd.exe) and verifying that OpenSSL is available:
Microsoft Windows [Version ...] Copyright (c) 2006 Microsoft Corporation. All rights reserved. C:\Windows\system32>cd \
C:\>openssl
OpenSSL>exit
<<< If you see the OpenSSL prompt, installation was successful. C:\>
After OpenSSL has been installed, use instructions similar to those from Example 1 (shown earlier in this section), with the following changes:
Change the following Unix commands:
# Create clean environment rm -rf newcerts mkdir newcerts && cd newcerts
On Windows, use these commands instead:
# Create clean environment md c:\newcerts cd c:\newcerts
When a '\'
character is shown at the
end of a command line, this '\'
character must be removed and the command lines entered
all on a single line.
After generating the certificate and key files, to use them for SSL connections, see Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.
This section describes how to use the openssl
command to set up the RSA key files that enable MySQL to support
secure password exchange over unencrypted connections for
accounts authenticated by the sha256_password
plugin.
There are easier alternatives to generating the files required for RSA than the procedure described here: Let the server autogenerate them or use the mysql_ssl_rsa_setup program. See Section 6.4.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”.
To create the RSA private and public key-pair files, run these commands while logged into the system account used to run the MySQL server so the files will be owned by that account:
openssl genrsa -out private_key.pem 2048 openssl rsa -in private_key.pem -pubout -out public_key.pem
Those commands create 2,048-bit keys. To create stronger keys, use a larger value.
Then set the access modes for the key files. The private key should be readable only by the server, whereas the public key can be freely distributed to client users:
chmod 400 private_key.pem chmod 444 public_key.pem
MySQL can be compiled using OpenSSL or yaSSL, both of which enable encrypted connections based on the OpenSSL API:
MySQL Enterprise Edition binary distributions are compiled using OpenSSL. It is not possible to use yaSSL with MySQL Enterprise Edition.
MySQL Community Edition binary distributions are compiled using yaSSL.
MySQL Community Edition source distributions can be compiled using either OpenSSL or yaSSL (see Section 6.4.5, “Building MySQL with Support for Encrypted Connections”).
OpenSSL and yaSSL offer the same basic functionality, but MySQL distributions compiled using OpenSSL have additional features:
OpenSSL supports a wider range of encryption ciphers from
which to choose for the
--ssl-cipher
option. OpenSSL
supports the --ssl-capath
,
--ssl-crl
, and
--ssl-crlpath
options. See
Section 6.4.2, “Command Options for Encrypted Connections”.
Accounts that authenticate using the
sha256_password
plugin can use RSA key
files for secure password exchange over unencrypted
connections. See
Section 6.5.1.2, “SHA-256 Pluggable Authentication”.
The server can automatically generate missing SSL and RSA certificate and key files at startup. See Section 6.4.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”.
OpenSSL supports more encryption modes for the
AES_ENCRYPT()
and
AES_DECRYPT()
functions. See
Section 12.13, “Encryption and Compression Functions”
Certain OpenSSL-related system and status variables are present only if MySQL was compiled using OpenSSL:
To determine whether your server was compiled using OpenSSL, test the existence of any of those variables. For example, this statement returns a row if OpenSSL was used and an empty result if yaSSL was used:
SHOW STATUS LIKE 'Rsa_public_key';
To use SSL connections between the MySQL server and client programs, your system must support either OpenSSL or yaSSL:
MySQL Enterprise Edition binary distributions are compiled using OpenSSL. It is not possible to use yaSSL with MySQL Enterprise Edition.
MySQL Community Edition binary distributions are compiled using yaSSL.
MySQL Community Edition source distributions can be compiled using either OpenSSL or yaSSL.
If you compile MySQL from a source distribution, CMake configures the distribution to use yaSSL by default. To compile using OpenSSL instead, use this procedure:
Ensure OpenSSL 1.0.1 or higher is installed on your system. To obtain OpenSSL, visit http://www.openssl.org.
If the installed OpenSSL version is lower than 1.0.1, CMake produces an error at MySQL configuration time.
To use OpenSSL, add the
-DWITH_SSL=system
option to the
CMake command you normally use to configure
the MySQL source distribution. For example:
cmake . -DWITH_SSL=system
That command configures the distribution to use the installed OpenSSL library. Alternatively, to explicitly specify the path name to the OpenSSL installation, use the following syntax. This can be useful if you have multiple versions of OpenSSL installed, to prevent CMake from choosing the wrong one:
cmake . -DWITH_SSL=path_name
Compile and install the distribution.
To check whether a mysqld server supports
encrypted connections, examine the value of the
have_ssl
system variable:
mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl | YES |
+---------------+-------+
If the value is YES
, the server supports
encrypted connections. If the value is
DISABLED
, the server is capable of supporting
encrypted connections but was not started with the appropriate
--ssl-
options to
enable encrypted connections to be used; see
Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.
xxx
To determine whether a server was compiled using OpenSSL or yaSSL, check the existence of any of the system or status variables that are present only for OpenSSL. See Section 6.4.4, “OpenSSL Versus yaSSL”
To determine which encryption protocol and cipher are in use for
an encrypted connection, use the following statements to check the
values of the Ssl_version
and
Ssl_cipher
status variables:
mysql>SHOW SESSION STATUS LIKE 'Ssl_version';
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | Ssl_version | TLSv1 | +---------------+-------+ mysql>SHOW SESSION STATUS LIKE 'Ssl_cipher';
+---------------+---------------------------+ | Variable_name | Value | +---------------+---------------------------+ | Ssl_cipher | DHE-RSA-AES128-GCM-SHA256 | +---------------+---------------------------+
If the connection is not encrypted, both variables have an empty value.
MySQL supports encrypted connections using TLS protocols:
When compiled using OpenSSL 1.0.1 or higher, MySQL supports the TLSv1, TLSv1.1, and TLSv1.2 protocols.
When compiled using the bundled version of yaSSL, MySQL supports the TLSv1 and TLSv1.1 protocols.
The value of the tls_version
system variable determines which protocols the server is permitted
to use from those that are available. The
tls_version
value is a
comma-separated list containing one or more of these protocols
(not case sensitive): TLSv1, TLSv1.1, TLSv1.2. By default, this
variable lists all protocols supported by the SSL library used to
compile MySQL (TLSv1,TLSv1.1,TLSv1.2
for
OpenSSL, TLSv1,TLSv1.1
for yaSSL). To determine
the value of tls_version
at
runtime, use this statement:
mysql> SHOW GLOBAL VARIABLES LIKE 'tls_version';
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
+---------------+-----------------------+
To change the value of
tls_version
, set it at server
startup. For example, to prohibit connections that use the
less-secure TLSv1 protocol, use these lines in the server
my.cnf
file:
[mysqld] tls_version=TLSv1.1,TLSv1.2
To be even more restrict and permit only TLSv1.2 connections, set
tls_version
like this (assuming
that your server is compiled using OpenSSL because yaSSL does not
support TLSv1.2):
[mysqld] tls_version=TLSv1.2
For client programs, the
--tls-version
option enables
specifying the TLS protocols permitted per client invocation. The
value format is the same as for
tls_version
.
By default, MySQL attempts to use the highest TLS protocol version
available, depending on which SSL library was used to compile the
server and client, which key size is used, and whether the server
or client are restricted from using some protocols; for example,
by means of
tls_version
/--tls-version
:
If the server and client are compiled using OpenSSL, TLSv1.2 is used if possible.
If either or both the server and client are compiled using yaSSL, TLSv1.1 is used if possible.
TLSv1.2 does not work with all ciphers that have a key size of
512 bits or less. To use this protocol with such a key, use
--ssl-cipher
to specify the
cipher name explicitly:
AES128-SHA AES128-SHA256 AES256-SHA AES256-SHA256 CAMELLIA128-SHA CAMELLIA256-SHA DES-CBC3-SHA DHE-RSA-AES256-SHA RC4-MD5 RC4-SHA SEED-SHA
For better security, use a certificate with an RSA key size of at least 2048 bits.
If the server and client protocol capabilities have no protocol in
common, the server terminates the connection request. For example,
if the server is configured with
tls_version=TLSv1.1,TLSv1.2
,
connection attempts will fail for clients invoked with
--tls-version=TLSv1
, and for older
clients that do not support the
--tls-version
option and
implicitly support only TLSv1.
To determine which ciphers a given server supports, use the
following statement to check the value of the
Ssl_cipher_list
status variable:
SHOW SESSION STATUS LIKE 'Ssl_cipher_list';
The set of available ciphers depends on your MySQL version and whether MySQL was compiled using OpenSSL or yaSSL, and (for OpenSSL) the library version used to compile MySQL.
Order of ciphers passed by MySQL to the SSL library is significant. More secure ciphers are mentioned first in the list, and the first cipher supported by the provided certificate is selected.
MySQL passes this cipher list to OpenSSL:
ECDHE-ECDSA-AES128-GCM-SHA256 ECDHE-ECDSA-AES256-GCM-SHA384 ECDHE-RSA-AES128-GCM-SHA256 ECDHE-RSA-AES256-GCM-SHA384 ECDHE-ECDSA-AES128-SHA256 ECDHE-RSA-AES128-SHA256 ECDHE-ECDSA-AES256-SHA384 ECDHE-RSA-AES256-SHA384 DHE-RSA-AES128-GCM-SHA256 DHE-DSS-AES128-GCM-SHA256 DHE-RSA-AES128-SHA256 DHE-DSS-AES128-SHA256 DHE-DSS-AES256-GCM-SHA384 DHE-RSA-AES256-SHA256 DHE-DSS-AES256-SHA256 ECDHE-RSA-AES128-SHA ECDHE-ECDSA-AES128-SHA ECDHE-RSA-AES256-SHA ECDHE-ECDSA-AES256-SHA DHE-DSS-AES128-SHA DHE-RSA-AES128-SHA TLS_DHE_DSS_WITH_AES_256_CBC_SHA DHE-RSA-AES256-SHA AES128-GCM-SHA256 DH-DSS-AES128-GCM-SHA256 ECDH-ECDSA-AES128-GCM-SHA256 AES256-GCM-SHA384 DH-DSS-AES256-GCM-SHA384 ECDH-ECDSA-AES256-GCM-SHA384 AES128-SHA256 DH-DSS-AES128-SHA256 ECDH-ECDSA-AES128-SHA256 AES256-SHA256 DH-DSS-AES256-SHA256 ECDH-ECDSA-AES256-SHA384 AES128-SHA DH-DSS-AES128-SHA ECDH-ECDSA-AES128-SHA AES256-SHA DH-DSS-AES256-SHA ECDH-ECDSA-AES256-SHA DHE-RSA-AES256-GCM-SHA384 DH-RSA-AES128-GCM-SHA256 ECDH-RSA-AES128-GCM-SHA256 DH-RSA-AES256-GCM-SHA384 ECDH-RSA-AES256-GCM-SHA384 DH-RSA-AES128-SHA256 ECDH-RSA-AES128-SHA256 DH-RSA-AES256-SHA256 ECDH-RSA-AES256-SHA384 ECDHE-RSA-AES128-SHA ECDHE-ECDSA-AES128-SHA ECDHE-RSA-AES256-SHA ECDHE-ECDSA-AES256-SHA DHE-DSS-AES128-SHA DHE-RSA-AES128-SHA TLS_DHE_DSS_WITH_AES_256_CBC_SHA DHE-RSA-AES256-SHA AES128-SHA DH-DSS-AES128-SHA ECDH-ECDSA-AES128-SHA AES256-SHA DH-DSS-AES256-SHA ECDH-ECDSA-AES256-SHA DH-RSA-AES128-SHA ECDH-RSA-AES128-SHA DH-RSA-AES256-SHA ECDH-RSA-AES256-SHA DES-CBC3-SHA
MySQL passes this cipher list to yaSSL:
DHE-RSA-AES256-SHA DHE-RSA-AES128-SHA AES128-RMD DES-CBC3-RMD DHE-RSA-AES256-RMD DHE-RSA-AES128-RMD DHE-RSA-DES-CBC3-RMD AES256-SHA RC4-SHA RC4-MD5 DES-CBC3-SHA DES-CBC-SHA EDH-RSA-DES-CBC3-SHA EDH-RSA-DES-CBC-SHA AES128-SHA:AES256-RMD
These cipher restrictions are in place:
The following ciphers are permanently restricted:
!DHE-DSS-DES-CBC3-SHA !DHE-RSA-DES-CBC3-SHA !ECDH-RSA-DES-CBC3-SHA !ECDH-ECDSA-DES-CBC3-SHA !ECDHE-RSA-DES-CBC3-SHA !ECDHE-ECDSA-DES-CBC3-SHA
The following categories of ciphers are permanently restricted:
!aNULL !eNULL !EXPORT !LOW !MD5 !DES !RC2 !RC4 !PSK !SSLv3
If the server is started using a compatible certificate that uses any of the preceding restricted ciphers or cipher categories, the server starts with support for encrypted connections disabled.
This section describes how to get an encrypted connection to a
remote MySQL server with SSH. The information was provided by
David Carlson <dcarlson@mplcomm.com>
.
Install an SSH client on your Windows machine. For a comparison of SSH clients, see http://en.wikipedia.org/wiki/Comparison_of_SSH_clients.
Start your Windows SSH client. Set Host_Name =
.
Set
yourmysqlserver_URL_or_IP
userid=
to log in to your server. This your_userid
userid
value
might not be the same as the user name of your MySQL account.
Set up port forwarding. Either do a remote forward (Set
local_port: 3306
, remote_host:
,
yourmysqlservername_or_ip
remote_port: 3306
) or a local forward (Set
port: 3306
, host:
localhost
, remote port: 3306
).
Save everything, otherwise you will have to redo it the next time.
Log in to your server with the SSH session you just created.
On your Windows machine, start some ODBC application (such as Access).
Create a new file in Windows and link to MySQL using the ODBC
driver the same way you normally do, except type in
localhost
for the MySQL host server, not
yourmysqlservername
.
At this point, you should have an ODBC connection to MySQL, encrypted using SSH.
MySQL includes several plugins that implement security features:
Plugins for authenticating attempts by clients to connect to MySQL Server. Plugins are available for several authentication protocols. For general discussion of the authentication process, see Section 6.3.10, “Pluggable Authentication”. For characteristics of specific authentication plugins, see Section 6.5.1, “Authentication Plugins”.
A password-validation plugin for implementing password strength policies and assessing the strength of potential passwords. See Section 6.5.3, “The Password Validation Plugin”.
A keyring plugin that provides secure storage for sensitive information. See Section 6.5.4, “The MySQL Keyring”.
The following sections describe pluggable authentication methods available in MySQL and the plugins that implement these methods. For general discussion of the authentication process, see Section 6.3.10, “Pluggable Authentication”.
The default plugin is mysql_native_password
unless the
default_authentication_plugin
system variable is set otherwise.
MySQL includes a mysql_native_password
plugin
that implements native authentication; that is, authentication
based on the password hashing method in use from before the
introduction of pluggable authentication.
The following table shows the plugin names on the server and client sides.
Table 6.10 Plugin and Library Names for Native Password Authentication
Server-side plugin name | mysql_native_password |
Client-side plugin name | mysql_native_password |
Library file name | None (plugins are built in) |
The following sections provide installation and usage information specific to native pluggable authentication:
For general information about pluggable authentication in MySQL, see Section 6.3.10, “Pluggable Authentication”.
The mysql_native_password
plugin exists in
server and client forms:
The server-side plugin is built into the server, need not be loaded explicitly, and cannot be disabled by unloading it.
The client-side plugin is built into the
libmysqlclient
client library and
available to any program linked against
libmysqlclient
.
MySQL client programs use
mysql_native_password
by default. The
--default-auth
option can be
used as a hint about which client-side plugin the program can
expect to use:
shell> mysql --default-auth=mysql_native_password ...
MySQL provides an authentication plugin that implements SHA-256 hashing for user account passwords.
To connect to the server using an account that authenticates
with the sha256_password
plugin, you must
use either an SSL connection or an unencrypted connection that
encrypts the password using RSA, as described later in this
section. Either way, the sha256_password
plugin uses MySQL's SSL capabilities. See
Section 6.4, “Using Encrypted Connections”.
The following table shows the plugin names on the server and client sides.
Table 6.11 Plugin and Library Names for SHA-256 Authentication
Server-side plugin name | sha256_password |
Client-side plugin name | sha256_password |
Library file name | None (plugins are built in) |
The following sections provide installation and usage information specific to SHA-256 pluggable authentication:
For general information about pluggable authentication in MySQL, see Section 6.3.10, “Pluggable Authentication”.
The sha256_password
plugin exists in server
and client forms:
The server-side plugin is built into the server, need not be loaded explicitly, and cannot be disabled by unloading it.
The client-side plugin is built into the
libmysqlclient
client library and
available to any program linked against
libmysqlclient
.
To set up an account that uses the
sha256_password
plugin for SHA-256 password
hashing, use the following statement:
CREATE USER 'sha256user'@'localhost' IDENTIFIED WITH sha256_password BY 'Sh@256Pa33';
Alternatively, start the server with the default
authentication plugin set to
sha256_password
. For example, put these
lines in the server option file:
[mysqld] default_authentication_plugin=sha256_password
That causes the sha256_password
plugin to
be used by default for new accounts. As a result, it is
possible to create the account and set its password without
naming the plugin explicitly using this
CREATE USER
syntax:
CREATE USER 'sha256user'@'localhost' IDENTIFIED BY 'Sh@256Pa33';
In this case, the server assigns the
sha256_password
plugin to the account and
encrypts the password using SHA-256.
Accounts in the mysql.user
table that use
SHA-256 passwords can be identified as rows with
'sha256_password'
in the
plugin
column and a SHA-256 password hash
in the authentication_string
column.
Another consequence of using
sha256_password
as the default
authentication plugin is that to create an account that uses a
different plugin, you must specify that plugin using an
IDENTIFIED WITH
clause in the
CREATE USER
statement. For
example, to use the mysql_native_password
plugin, use this statement:
CREATE USER 'nativeuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'N@tivePa33';
MySQL can be compiled using either OpenSSL or yaSSL (see
Section 6.4.4, “OpenSSL Versus yaSSL”). The
sha256_password
plugin works with
distributions compiled using either package, but if MySQL is
compiled using OpenSSL, RSA encryption is available and
sha256_password
implements the following
additional capabilities. (To enable these capabilities, you
must also follow the RSA configuration procedure given later
in this section.)
It is possible for the client to transmit passwords to the server using RSA encryption during the client connection process, as described later.
The server exposes two additional system variables,
sha256_password_private_key_path
and
sha256_password_public_key_path
.
It is intended that the database administrator will set
these to the names of the RSA private and public key-pair
files at server startup if the key files have names that
differ from the system variable default values.
The server exposes a status variable,
Rsa_public_key
, that
displays the RSA public key value.
The mysql and
mysqltest client programs support a
--server-public-key-path
option for specifying an RSA public key file explicitly.
For clients that use the sha256_password
plugin, passwords are never exposed as cleartext when
connecting to the server. How password transmission occurs
depends on whether an SSL connection is used and whether RSA
encryption is available:
If an SSL connection is used, the password is sent as cleartext but cannot be snooped because the connection is encrypted using SSL.
If an SSL connection is not used but RSA encryption is available, the password is sent within an unencrypted connection, but the password is RSA-encrypted to prevent snooping. When the server receives the password, it decrypts it. A scramble is used in the encryption to prevent repeat attacks.
If an SSL connection is not used and RSA encryption is not
available, the sha256_password
plugin
causes the connection attempt to fail because the password
cannot be sent without being exposed as cleartext.
As mentioned previously, RSA password encryption is available only if MySQL was compiled using OpenSSL. The implication for MySQL distributions compiled using yaSSL is that SHA-256 passwords can be used only when clients use SSL to access the server. See Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.
Assuming that MySQL has been compiled using OpenSSL, the following procedure describes how to enable RSA encryption of passwords during the client connection process:
Create the RSA private and public key-pair files using the instructions in Section 6.4.3, “Creating SSL and RSA Certificates and Keys”.
If the private and public key files are located in the
data directory and are named
private_key.pem
and
public_key.pem
(the default values of
the
sha256_password_private_key_path
and
sha256_password_public_key_path
system variables), the server uses them automatically at
startup.
Otherwise, in the server option file, set the system variables to the key file names. If the files are located in the server data directory, you need not specify their full path names:
[mysqld] sha256_password_private_key_path=myprivkey.pem sha256_password_public_key_path=mypubkey.pem
If the key files are not located in the data directory, or to make their locations explicit in the system variable values, use full path names:
[mysqld] sha256_password_private_key_path=/usr/local/mysql/myprivkey.pem sha256_password_public_key_path=/usr/local/mysql/mypubkey.pem
Restart the server, then connect to it and check the
Rsa_public_key
status
variable value. The value will differ from that shown
here, but should be nonempty:
mysql> SHOW STATUS LIKE 'Rsa_public_key'\G
*************************** 1. row ***************************
Variable_name: Rsa_public_key
Value: -----BEGIN PUBLIC KEY-----
MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDO9nRUDd+KvSZgY7cNBZMNpwX6
MvE1PbJFXO7u18nJ9lwc99Du/E7lw6CVXw7VKrXPeHbVQUzGyUNkf45Nz/ckaaJa
aLgJOBCIDmNVnyU54OT/1lcs2xiyfaDMe8fCJ64ZwTnKbY2gkt1IMjUAB5Ogd5kJ
g8aV7EtKwyhHb0c30QIDAQAB
-----END PUBLIC KEY-----
If the value is empty, the server found some problem with the key files. Check the error log for diagnostic information.
After the server has been configured with the RSA key files,
accounts that authenticate with the
sha256_password
plugin have the option of
using those key files to connect to the server. As mentioned
previously, such accounts can use either an SSL connection (in
which case RSA is not used) or an unencrypted connection that
encrypts the password using RSA. Assume for the following
discussion that SSL is not used. Connecting to the server
involves no special preparation on the client side. For
example:
shell>mysql --ssl-mode=DISABLED -u sha256user -p
Enter password:Sh@256Pa33
For connection attempts by sha256user
, the
server determines that sha256_password
is
the appropriate authentication plugin and invokes it. The
plugin finds that the connection does not use SSL and thus
requires the password to be transmitted using RSA encryption.
In this case, the plugin sends the RSA public key to the
client, which uses it to encrypt the password and returns the
result to the server. The plugin uses the RSA key on the
server side to decrypt the password and accepts or rejects the
connection based on whether the password is correct.
The server sends the public key to the client as needed, but if a copy of the RSA public key is available on the client host, the client can use it to save a round trip in the client/server protocol:
shell>mysql --ssl-mode=DISABLED -u sha256user -p --server-public-key-path=
Enter password:file_name
Sh@256Pa33
The public key value in the file named by the
--server-public-key-path
option
should be the same as the key value in the server-side file
named by the
sha256_password_public_key_path
system variable. If the key file contains a valid public key
value but the value is incorrect, an access-denied error
occurs. If the key file does not contain a valid public key,
the client program cannot use it. In this case, the
sha256_password
plugin sends the public key
to the client as if no
--server-public-key-path
option
had been specified.
Client users can get the RSA public key two ways:
The database administrator can provide a copy of the public key file.
A client user who can connect to the server some other way
can use a SHOW STATUS LIKE
'Rsa_public_key'
statement and save the returned
key value in a file.
A client-side authentication plugin is available that sends the password to the server without hashing or encryption. This plugin is built into the MySQL client library.
The following table shows the plugin name.
Table 6.12 Plugin and Library Names for Cleartext Authentication
Server-side plugin name | None, see discussion |
Client-side plugin name | mysql_clear_password |
Library file name | None (plugin is built in) |
With many MySQL authentication methods, the client performs hashing or encryption of the password before sending it to the server. This enables the client to avoid sending the password in clear text.
Hashing or encryption cannot be done for authentication schemes
that require the server to receive the password as entered on
the client side. In such cases, the client-side
mysql_clear_password
plugin is used to send
the password to the server in clear text. There is no
corresponding server-side plugin. Rather, the client-side plugin
can be used by any server-side plugin that needs a cleartext
password. (The PAM authentication plugin is one such; see
PAM Pluggable Authentication.)
The following discussion provides usage information specific to clear text pluggable authentication. For For general information about pluggable authentication in MySQL, see Section 6.3.10, “Pluggable Authentication”.
Sending passwords in clear text may be a security problem in some configurations. To avoid problems if there is any possibility that the password would be intercepted, clients should connect to MySQL Server using a method that protects the password. Possibilities include SSL (see Section 6.4, “Using Encrypted Connections”), IPsec, or a private network.
To make inadvertent use of the
mysql_clear_password
plugin less likely,
MySQL clients must explicitly enable it. This can be done
several ways:
Set the LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN
environment variable to a value that begins with
1
, Y
, or
y
. This enables the plugin for all client
connections.
The mysql, mysqladmin,
mysqlcheck, mysqldump,
mysqlshow, and
mysqlslap client programs support an
--enable-cleartext-plugin
option that
enables the plugin on a per-invocation basis.
The mysql_options()
C API
function supports a
MYSQL_ENABLE_CLEARTEXT_PLUGIN
option that
enables the plugin on a per-connection basis. Also, any
program that uses libmysqlclient
and
reads option files can enable the plugin by including an
enable-cleartext-plugin
option in an
option group read by the client library.
The mysql_no_login
server-side authentication
plugin prevents all client connections to any account that uses
it. Use cases for such a plugin includes accounts that must be
able to execute stored programs and views with elevated
privileges without exposing those privileges to ordinary users,
and proxied accounts that should never permit direct login but
are accessed only through proxy accounts.
The following table shows the plugin and library file names. The
file name suffix might differ on your system. The file must be
located in the directory named by the
plugin_dir
system variable.
Table 6.13 Plugin and Library Names for “No Login” Authentication
Server-side plugin name | mysql_no_login |
Client-side plugin name | None |
Library file name | mysql_no_login.so |
The following sections provide installation and usage information specific to no-login pluggable authentication:
For general information about pluggable authentication in MySQL, see Section 6.3.10, “Pluggable Authentication”. For proxy user information, see Section 6.3.11, “Proxy Users”.
This section describes how to install the no-login authentication plugin. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.
To be usable by the server, the plugin library file must be
located in the MySQL plugin directory (the directory named by
the plugin_dir
system
variable). If necessary, set the value of
plugin_dir
at server startup
to tell the server the plugin directory location.
The plugin library file base name is
mysql_no_login
. The file name suffix
differs per platform (for example, .so
for Unix and Unix-like systems, .dll
for
Windows).
To load the plugin at server startup, use the
--plugin-load-add
option to
name the library file that contains it. With this
plugin-loading method, the option must be given each time the
server starts. For example, put these lines in your
my.cnf
file (adjust the
.so
suffix for your platform as
necessary):
[mysqld] plugin-load-add=mysql_no_login.so
After modifying my.cnf
, restart the
server to cause the new settings to take effect.
Alternatively, to register the plugin at runtime, use this
statement (adjust the .so
suffix as
necessary):
INSTALL PLUGIN mysql_no_login SONAME 'mysql_no_login.so';
INSTALL PLUGIN
loads a plugin,
and also registers it in the mysql.plugins
system table to cause the plugin to be loaded for each
subsequent normal server startup.
To verify plugin installation, examine the
INFORMATION_SCHEMA.PLUGINS
table
or use the SHOW PLUGINS
statement (see
Section 5.6.2, “Obtaining Server Plugin Information”). For example:
mysql>SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE '%login%';
+----------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +----------------+---------------+ | mysql_no_login | ACTIVE | +----------------+---------------+
If the plugin fails to initialize, check the server error log for diagnostic messages.
To associate MySQL accounts with the no-login plugin, see Using No-Login Pluggable Authentication.
The method used to uninstall the no-login authentication plugin depends on how you installed it:
If you installed the plugin at server startup using a
--plugin-load-add
option,
restart the server without the option.
If you installed the plugin at runtime using
INSTALL PLUGIN
, it remains
installed across server restarts. To uninstall it, use
UNINSTALL PLUGIN
:
UNINSTALL PLUGIN mysql_no_login;
This section describes how to use the no-login authentication plugin to prevent connections from MySQL client programs to the server. It is assumed that the server is running with the server-side plugin enabled, as described in Installing No-Login Pluggable Authentication.
To refer to the no-login authentication plugin in the
IDENTIFIED WITH
clause of a
CREATE USER
statement, use the
name mysql_no_login
.
An account that authenticates using
mysql_no_login
may be used as the
DEFINER
for stored program and view
objects. If such an object definition also includes
SQL SECURITY DEFINER
, it executes with that
account's privileges. DBAs can use this behavior to provide
access to confidential or sensitive data that is exposed only
through well-controlled interfaces.
The following example provides a simple illustration of these
principles. It defines an account that does not permit client
connections, and associates with it a view that exposes only
certain columns of the mysql.user
table:
CREATE DATABASE nologindb; CREATE USER 'nologin'@'localhost' IDENTIFIED WITH mysql_no_login; GRANT ALL ON nologindb.* TO 'nologin'@'localhost'; GRANT SELECT ON mysql.user TO 'nologin'@'localhost'; CREATE DEFINER = 'nologin'@'localhost' SQL SECURITY DEFINER VIEW nologindb.myview AS SELECT User, Host FROM mysql.user;
To provide protected access to the view to an ordinary user, do this:
GRANT SELECT ON nologindb.myview TO 'ordinaryuser'@'localhost';
Now the ordinary user can use the view to access the limited information it presents:
SELECT * FROM nologindb.myview;
Attempts by the user to access columns other than those exposed by the view result in an error, as do all attempts to select from the view by users not granted access to it.
Because the nologin
account cannot be
used directly, the operations required to set up objects
that it uses must be performed by root
or
similar account with the privileges required to create the
objects and set DEFINER
values.
An account that authenticates using
mysql_no_login
may be used as a proxied
base user for proxy accounts:
-- create proxied account CREATE USER 'proxy_base'@'localhost' IDENTIFIED WITH mysql_no_login; -- grant privileges to proxied account GRANT ... TO 'proxy_base'@'localhost'; -- permit real_user to be proxy for proxied account GRANT PROXY ON 'proxy_base'@'localhost' TO 'real_user'@'localhost';
This enables clients to access MySQL through the proxy account
(real_user
) but not to bypass the proxy
mechanism by connecting directly as the proxied user
(proxy_base
).
The server-side auth_socket
authentication
plugin authenticates clients that connect from the local host
through the Unix socket file. The plugin uses the
SO_PEERCRED
socket option to obtain
information about the user running the client program. Thus, the
plugin can be used only on systems that support the
SO_PEERCRED
option, such as Linux.
The source code for this plugin can be examined as a relatively simple example demonstrating how to write a loadable authentication plugin.
The following table shows the plugin and library file names. The
file must be located in the directory named by the
plugin_dir
system variable.
Table 6.14 Plugin and Library Names for Socket Peer-Credential Authentication
Server-side plugin name | auth_socket |
Client-side plugin name | None, see discussion |
Library file name | auth_socket.so |
The following sections provide installation and usage information specific to socket pluggable authentication:
For general information about pluggable authentication in MySQL, see Section 6.3.10, “Pluggable Authentication”.
This section describes how to install the socket authentication plugin. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.
To be usable by the server, the plugin library file must be
located in the MySQL plugin directory (the directory named by
the plugin_dir
system
variable). If necessary, set the value of
plugin_dir
at server startup
to tell the server the plugin directory location.
To load the plugin at server startup, use the
--plugin-load-add
option to
name the library file that contains it. With this
plugin-loading method, the option must be given each time the
server starts. For example, put these lines in your
my.cnf
file:
[mysqld] plugin-load-add=auth_socket.so
After modifying my.cnf
, restart the
server to cause the new settings to take effect.
Alternatively, to register the plugin at runtime, use this statement:
INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
INSTALL PLUGIN
loads a plugin,
and also registers it in the mysql.plugins
system table to cause the plugin to be loaded for each
subsequent normal server startup.
To verify plugin installation, examine the
INFORMATION_SCHEMA.PLUGINS
table
or use the SHOW PLUGINS
statement (see
Section 5.6.2, “Obtaining Server Plugin Information”). For example:
mysql>SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE '%socket%';
+-------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +-------------+---------------+ | auth_socket | ACTIVE | +-------------+---------------+
If the plugin fails to initialize, check the server error log for diagnostic messages.
To associate MySQL accounts with the socket plugin, see Using Socket Pluggable Authentication.
The method used to uninstall the socket authentication plugin depends on how you installed it:
If you installed the plugin at server startup using a
--plugin-load-add
option,
restart the server without the option.
If you installed the plugin at runtime using
INSTALL PLUGIN
, it remains
installed across server restarts. To uninstall it, use
UNINSTALL PLUGIN
:
UNINSTALL PLUGIN auth_socket;
The socket plugin checks whether the socket user name matches
the MySQL user name specified by the client program to the
server. If the names do not match, the plugin also checks
whether the socket user name matches the name specified in the
authentication_string
column of the
mysql.user
table row. If a match is found,
the plugin permits the connection.
Suppose that a MySQL account is created for a user named
valerie
who is to be authenticated by the
auth_socket
plugin for connections from the
local host through the socket file:
CREATE USER 'valerie'@'localhost' IDENTIFIED WITH auth_socket;
If a user on the local host with a login name of
stefanie
invokes mysql
with the option --user=valerie
to connect
through the socket file, the server uses
auth_socket
to authenticate the client. The
plugin determines that the --user
option
value (valerie
) differs from the client
user's name (stephanie
) and refuses the
connection. If a user named valerie
tries
the same thing, the plugin finds that the user name and the
MySQL user name are both valerie
and
permits the connection. However, the plugin refuses the
connection even for valerie
if the
connection is made using a different protocol, such as TCP/IP.
MySQL includes a test plugin that checks account credentials and logs success or failure to the server error log. This is a loadable plugin (not built in) and must be installed prior to use.
The test plugin source code is separate from the server source, unlike the built-in native plugin, so it can be examined as a relatively simple example demonstrating how to write a loadable authentication plugin.
This plugin is intended for testing and development purposes, and is not for use in production environments or on servers that are exposed to public networks.
The following table shows the plugin and library file names. The
file name suffix might differ on your system. The file must be
located in the directory named by the
plugin_dir
system variable.
Table 6.15 Plugin and Library Names for Test Authentication
Server-side plugin name | test_plugin_server |
Client-side plugin name | auth_test_plugin |
Library file name | auth_test_plugin.so |
The following sections provide installation and usage information specific to test pluggable authentication:
For general information about pluggable authentication in MySQL, see Section 6.3.10, “Pluggable Authentication”.
This section describes how to install the test authentication plugin. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.
To be usable by the server, the plugin library file must be
located in the MySQL plugin directory (the directory named by
the plugin_dir
system
variable). If necessary, set the value of
plugin_dir
at server startup
to tell the server the plugin directory location.
To load the plugin at server startup, use the
--plugin-load-add
option to
name the library file that contains it. With this
plugin-loading method, the option must be given each time the
server starts. For example, put these lines in your
my.cnf
file (adjust the
.so
suffix for your platform as
necessary):
[mysqld] plugin-load-add=auth_test_plugin.so
After modifying my.cnf
, restart the
server to cause the new settings to take effect.
Alternatively, to register the plugin at runtime, use this
statement (adjust the .so
suffix as
necessary):
INSTALL PLUGIN test_plugin_server SONAME 'auth_test_plugin.so';
INSTALL PLUGIN
loads a plugin,
and also registers it in the mysql.plugins
system table to cause the plugin to be loaded for each
subsequent normal server startup.
To verify plugin installation, examine the
INFORMATION_SCHEMA.PLUGINS
table
or use the SHOW PLUGINS
statement (see
Section 5.6.2, “Obtaining Server Plugin Information”). For example:
mysql>SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE '%test_plugin%';
+--------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +--------------------+---------------+ | test_plugin_server | ACTIVE | +--------------------+---------------+
If the plugin fails to initialize, check the server error log for diagnostic messages.
To associate MySQL accounts with the test plugin, see Using Test Pluggable Authentication.
The method used to uninstall the test authentication plugin depends on how you installed it:
If you installed the plugin at server startup using a
--plugin-load-add
option,
restart the server without the option.
If you installed the plugin at runtime using
INSTALL PLUGIN
, it remains
installed across server restarts. To uninstall it, use
UNINSTALL PLUGIN
:
UNINSTALL PLUGIN test_plugin_server;
To use the test authentication plugin, create an account and
name that plugin in the IDENTIFIED WITH
clause:
CREATE USER 'testuser'@'localhost' IDENTIFIED WITH test_plugin_server BY 'testpassword';
Then provide the --user
and
--password
options for that
account when you connect to the server. For example:
shell>mysql --user=testuser --password
Enter password:testpassword
The plugin fetches the password as received from the client
and compares it with the value stored in the
authentication_string
column of the account
row in the mysql.user
table. If the two
values match, the plugin returns the
authentication_string
value as the new
effective user ID.
You can look in the server error log for a message indicating whether authentication succeeded (notice that the password is reported as the “user”):
[Note] Plugin test_plugin_server reported: 'successfully authenticated user testpassword'
MySQL Server includes a plugin library that enables administrators to introduce an increasing delay in server response to clients after a certain number of consecutive failed connection attempts. This capability provides a deterrent that slows down brute force attacks that attempt to access MySQL user accounts. The plugin library contains two plugins:
CONNECTION_CONTROL
checks incoming
connections and adds a delay to server responses as necessary.
This plugin also exposes system variables that enable plugin
operation to be configured and a status variable that provides
rudimentary monitoring information.
The CONNECTION_CONTROL
plugin uses the
audit plugin interface (see
Section 28.2.4.8, “Writing Audit Plugins”). To collect
information, it subscribes to the
MYSQL_AUDIT_CONNECTION_CLASSMASK
event
class, and processes
MYSQL_AUDIT_CONNECTION_CONNECT
and
MYSQL_AUDIT_CONNECTION_CHANGE_USER
subevents to check whether the server should introduce a delay
before responding to client connection attempts.
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
implements an INFORMATION_SCHEMA
table that
exposes more detailed monitoring information for failed
connection attempts.
The following sections provide information about
connection-control plugin installation and configuration. For
information about the
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
table, see
Section 24.32.1, “The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Table”.
This section describes how to install the connection-control
plugins, CONNECTION_CONTROL
and
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
. For
general information about installing plugins, see
Section 5.6.1, “Installing and Uninstalling Plugins”.
To be usable by the server, the plugin library file must be
located in the MySQL plugin directory (the directory named by
the plugin_dir
system
variable). If necessary, set the value of
plugin_dir
at server startup to
tell the server the plugin directory location.
The plugin library file base name is
connection_control
. The file name suffix
differs per platform (for example, .so
for
Unix and Unix-like systems, .dll
for
Windows).
To load the plugins at server startup, use the
--plugin-load-add
option to name
the library file that contains them. With this plugin-loading
method, the option must be given each time the server starts.
For example, put these lines in your my.cnf
file (adjust the .so
suffix for your
platform as necessary):
[mysqld] plugin-load-add=connection_control.so
After modifying my.cnf
, restart the server
to cause the new settings to take effect.
Alternatively, to register the plugins at runtime, use these
statements (adjust the .so
suffix as
necessary):
INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so'; INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
INSTALL PLUGIN
loads a plugin,
and also registers it in the mysql.plugins
system table to cause the plugin to be loaded for each
subsequent normal server startup.
To verify plugin installation, examine the
INFORMATION_SCHEMA.PLUGINS
table or
use the SHOW PLUGINS
statement
(see Section 5.6.2, “Obtaining Server Plugin Information”). For
example:
mysql>SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'connection%';
+------------------------------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +------------------------------------------+---------------+ | CONNECTION_CONTROL | ACTIVE | | CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE | +------------------------------------------+---------------+
If a plugin fails to initialize, check the server error log for diagnostic messages.
If the plugins have been previously registered with
INSTALL PLUGIN
or are loaded with
--plugin-load-add
, you can use
the --connection-control
and
--connection-control-failed-login-attempts
options at server startup to control plugin activation. For
example, to load the plugins at startup and prevent them from
being removed at runtime, use these options:
[mysqld] plugin-load-add=connection_control.so connection-control=FORCE_PLUS_PERMANENT connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT
If it is desired to prevent the server from running without a
given connection-control plugin, use an option value of
FORCE
or
FORCE_PLUS_PERMANENT
to force server startup
to fail if the plugin does not initialize successfully.
It is possible to install one plugin without the other, but
both must be installed for full connection-control capability.
In particular, installing only the
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
plugin is of little use because without the
CONNECTION_CONTROL
plugin to provide the
data that populates the
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
table, retrievals from the table will always be empty.
To enable you to configure its operation, the
CONNECTION_CONTROL
plugin exposes several
system variables:
connection_control_failed_connections_threshold
:
The number of consecutive failed connection attempts
permitted to clients before the server adds a delay for
subsequent connection attempts.
connection_control_min_connection_delay
:
The amount of delay to add for each consecutive connection
failure above the threshold.
connection_control_max_connection_delay
:
The maximum delay to add.
To entirely disable checking for failed connection attempts,
set
connection_control_failed_connections_threshold
to zero. If
connection_control_failed_connections_threshold
is nonzero, the amount of delay is zero up through that many
consecutive failed connection attempts. Thereafter, the amount
of delay is the number of failed attempts above the threshold,
multiplied by
connection_control_min_connection_delay
milliseconds. For example, with the default
connection_control_failed_connections_threshold
and
connection_control_min_connection_delay
values of 3 and 1000, respectively, there is no delay for the
first three consecutive failed connection attempts by a
client, a delay of 1000 milliseconds for the fourth failed
attempt, 2000 milliseconds for the fifth failed attempt, and
so on, up to the maximum delay permitted by
connection_control_max_connection_delay
.
You can set the CONNECTION_CONTROL
system
variables at server startup or runtime. Suppose that you want
to permit four consecutive failed connection attempts before
the server starts delaying its responses, and to increase the
delay by 1500 milliseconds for each additional failure after
that. To set the relevant variables at server startup, put
these lines in your my.cnf
file:
[mysqld] plugin-load-add=connection_control.so connection_control_failed_connections_threshold=4 connection_control_min_connection_delay=1500
To set the variables at runtime, use these statements:
SET PERSIST connection_control_failed_connections_threshold = 4; SET PERSIST connection_control_min_connection_delay = 1500;
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.4.1, “SET Syntax for Variable Assignment”. To change
a value only for the running MySQL instance without saving it
for subsequent restarts, use the GLOBAL
keyword rather than PERSIST
.
The
connection_control_min_connection_delay
and
connection_control_max_connection_delay
system variables have fixed minimum and maximum values of 1000
and 2147483647, respectively. In addition, the permitted range
of values of each variable also depends on the current value
of the other:
connection_control_min_connection_delay
cannot be set greater than the current value of
connection_control_max_connection_delay
.
connection_control_max_connection_delay
cannot be set less than the current value of
connection_control_min_connection_delay
.
Thus, to make the changes required for some configurations,
you might need to set the variables in a specific order.
Suppose that the current minimum and maximum delays are 1000
and 2000, and that you want to set them to 3000 and 5000. You
cannot first set
connection_control_min_connection_delay
to 3000 because that is greater than the current
connection_control_max_connection_delay
value of 2000. Instead, set
connection_control_max_connection_delay
to 5000, then set
connection_control_min_connection_delay
to 3000.
When the CONNECTION_CONTROL
plugin is
installed, it checks connection attempts and tracks whether
they fail or succeed. For this purpose, a failed connection
attempt is one for which the client user and host match a
known MySQL account but the provided credentials are
incorrect, or do not match any known account.
Failed-connection counting is based on the user/host combination for each connection attempt. Determination of the applicable user name and host name takes proxying into account and occurs as follows:
If the client user proxies another user, the proxying
user's information is used. For example, if
external_user@example.com
proxies
proxy_user@example.com
, connection
counting uses the proxying user,
external_user@example.com
, rather than
the proxied user,
proxy_user@example.com
. Both
external_user@example.com
and
proxy_user@example.com
must have valid
entries in the mysql.user
system table
and a proxy relationship between them must be defined in
the mysql.proxies_priv
system table
(see Section 6.3.11, “Proxy Users”).
If the client user does not proxy another user, but does
match a mysql.user
entry, counting uses
the CURRENT_USER()
value
corresponding to that entry. For example, if a user
user1
connecting from a host
host1.example.com
matches a
user1@host1.example.com
entry, counting
uses user1@host1.example.com
. If the
user matches a user1@%.example.com
,
user1@%.com
, or
user1@%
entry instead, counting uses
user1@%.example.com
,
user1@%.com
, or
user1@%
, respectively.
For the cases just described, the connection attempt matches
some mysql.user
entry, and whether the
request succeeds or fails depends on whether the client
provides the correct authentication credentials. For example,
if the client presents an incorrect password, the connection
attempt fails.
If the connection attempt matches no
mysql.user
entry, the attempt fails. In
this case, no CURRENT_USER()
value is available and connection-failure counting uses the
user name provided by the client and the client host as
determined by the server. For example, if a client attempts to
connect as user user2
from host
host2.example.com
, the user name part is
available in the client request and the server determines the
host information. The user/host combination used for counting
is user2@host2.example.com
.
The server maintains information about which client hosts
can possibly connect to the server (essentially the union of
host values for mysql.user
entries). If a
client attempts to connect from any other host, the server
rejects the attempt at an early stage of connection setup:
ERROR 1130 (HY000): Host 'host_name
' is not
allowed to connect to this MySQL server
Because this type of rejection occurs so early,
CONNECTION_CONTROL
does not see it, and
does not count it.
To monitor failed connections, use these information sources:
The
Connection_control_delay_generated
status variable indicates the number of times the server
added a delay to its response to a failed connection
attempt. This does not count attempts that occur before
reaching the threshold defined by the
connection_control_failed_connections_threshold
system variable.
The INFORMATION_SCHEMA
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
table provides information about the current number of
consecutive failed connection attempts per client
user/host combination. This counts all failed attempts,
regardless of whether they were delayed.
Assigning a value to
connection_control_failed_connections_threshold
at runtime resets all accumulated failed-connection counters
to zero, which has these visible effects:
The
Connection_control_delay_generated
status variable is reset to zero.
The
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
table becomes empty.
This section describes the system and status variables that the
CONNECTION_CONTROL
plugin provides to enable
its operation to be configured and monitored.
If the CONNECTION_CONTROL
plugin is
installed, it exposes these system variables:
connection_control_failed_connections_threshold
Introduced | 8.0.1 | ||
Command-Line Format | --connection-control-failed-connections-threshold=# | ||
System Variable | Name | connection_control_failed_connections_threshold | |
Variable Scope | Global | ||
Dynamic Variable | Yes | ||
Permitted Values | Type | integer | |
Default | 3 | ||
Min Value | 0 | ||
Max Value | 2147483647 |
The number of consecutive failed connection attempts permitted to clients before the server adds a delay for subsequent connection attempts:
If the variable has a nonzero value
N
, the server adds a delay
beginning with consecutive failed attempt
N
+1. If a client has reached
the point where connection responses are delayed, the
delay also occurs for the next subsequent successful
connection.
Setting this variable to zero disables failed-connection counting. In this case, the server never adds delays.
For information about how
connection_control_failed_connections_threshold
interacts with other connection-control system and status
variables, see
Section 6.5.2.1, “Connection-Control Plugin Installation”.
connection_control_max_connection_delay
Introduced | 8.0.1 | ||
Command-Line Format | --connection-control-max-connection-delay=# | ||
System Variable | Name | connection_control_max_connection_delay | |
Variable Scope | Global | ||
Dynamic Variable | Yes | ||
Permitted Values | Type | integer | |
Default | 2147483647 | ||
Min Value | 1000 | ||
Max Value | 2147483647 |
The maximum delay in milliseconds for server response to
failed connection attempts, if
connection_control_failed_connections_threshold
is greater than zero.
For information about how
connection_control_max_connection_delay
interacts with other connection-control system and status
variables, see
Section 6.5.2.1, “Connection-Control Plugin Installation”.
connection_control_min_connection_delay
Introduced | 8.0.1 | ||
Command-Line Format | --connection-control-min-connection-delay=# | ||
System Variable | Name | connection_control_min_connection_delay | |
Variable Scope | Global | ||
Dynamic Variable | Yes | ||
Permitted Values | Type | integer | |
Default | 1000 | ||
Min Value | 1000 | ||
Max Value | 2147483647 |
The minimum delay in milliseconds for server response to
failed connection attempts, if
connection_control_failed_connections_threshold
is greater than zero. This is also the amount by which the
server increases the delay for additional successive
failures once it begins delaying.
For information about how
connection_control_min_connection_delay
interacts with other connection-control system and status
variables, see
Section 6.5.2.1, “Connection-Control Plugin Installation”.
If the CONNECTION_CONTROL
plugin is
installed, it exposes this status variable:
Connection_control_delay_generated
The number of times the server added a delay to its response
to a failed connection attempt. This does not count attempts
that occur before reaching the threshold defined by the
connection_control_failed_connections_threshold
system variable.
This variable provides a simple counter. For more detailed
connection-control monitoring information, examine the
INFORMATION_SCHEMA
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
table; see
Section 24.32.1, “The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Table”.
Assigning a value to
connection_control_failed_connections_threshold
at runtime resets
Connection_control_delay_generated
to zero.
The validate_password
plugin serves to test
passwords and improve security. The plugin exposes a set of system
variables that enable you to define password policy.
This plugin implements two capabilities:
In statements that assign a password supplied as a cleartext
value, the plugin checks the password against the current
password policy and rejects it if it is weak (the statement
returns an
ER_NOT_VALID_PASSWORD
error).
This affects the ALTER USER
,
CREATE USER
,
GRANT
, and
SET PASSWORD
statements.
Passwords given as arguments to the
PASSWORD()
function are checked
as well.
The
VALIDATE_PASSWORD_STRENGTH()
SQL function assesses the strength of potential passwords. The
function takes a password argument and returns an integer from
0 (weak) to 100 (strong).
For example, the cleartext password in the following statement is checked. Under the default password policy, which requires passwords to be at least 8 characters long, the password is weak and the statement produces an error:
mysql> ALTER USER USER() IDENTIFIED BY 'abc';
ERROR 1819 (HY000): Your password does not satisfy the current
policy requirements
Passwords specified as hashed values are not checked because the original password value is not available:
mysql>ALTER USER 'jeffrey'@'localhost'
IDENTIFIED WITH mysql_native_password
AS '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E';
Query OK, 0 rows affected (0.01 sec)
System variables having names of the form
validate_password_
represent the parameters that control password policy. To
configure password checking, modify these variables; see
Section 6.5.3.2, “Password Validation Plugin Options and Variables”.
xxx
If the validate_password
plugin is not
installed, the
validate_password_
system variables are not available, passwords in statements are
not checked, and the
xxx
VALIDATE_PASSWORD_STRENGTH()
function always returns 0. For example, without the plugin
installed, accounts can be assigned passwords shorter than 8
characters.
Assuming that the validate_password
plugin is
installed, it implements three levels of password checking:
LOW
, MEDIUM
, and
STRONG
. The default is
MEDIUM
; to change this, modify the value of
validate_password_policy
. The
policies implement increasingly strict password tests. The
following descriptions refer to default parameter values, which
can be modified by changing the appropriate system variables.
LOW
policy tests password length only.
Passwords must be at least 8 characters long.
MEDIUM
policy adds the conditions that
passwords must contain at least 1 numeric character, 1
lowercase character, 1 uppercase character, and 1 special
(nonalphanumeric) character.
STRONG
policy adds the condition that
password substrings of length 4 or longer must not match words
in the dictionary file, if one has been specified.
In addition, the validate_password
plugin
supports the capability of rejecting passwords that match the user
name part of the effective user account for the current session,
either forward or in reverse. To enable control over this
capability, the plugin exposes a
validate_password_check_user_name
system variable. By default, this variable is enabled.
This section describes how to install the
validate_password
password-validation plugin.
For general information about installing plugins, see
Section 5.6.1, “Installing and Uninstalling Plugins”.
To be usable by the server, the plugin library file must be
located in the MySQL plugin directory (the directory named by
the plugin_dir
system
variable). If necessary, set the value of
plugin_dir
at server startup to
tell the server the plugin directory location.
The plugin library file base name is
validate_password
. The file name suffix
differs per platform (for example, .so
for
Unix and Unix-like systems, .dll
for
Windows).
To load the plugin at server startup, use the
--plugin-load-add
option to name
the library file that contains it. With this plugin-loading
method, the option must be given each time the server starts.
For example, put these lines in your my.cnf
file (adjust the .so
suffix for your
platform as necessary):
[mysqld] plugin-load-add=validate_password.so
After modifying my.cnf
, restart the server
to cause the new settings to take effect.
Alternatively, to register the plugin at runtime, use this
statement (adjust the .so
suffix as
necessary):
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
INSTALL PLUGIN
loads the plugin,
and also registers it in the mysql.plugins
system table to cause the plugin to be loaded for each
subsequent normal server startup.
To verify plugin installation, examine the
INFORMATION_SCHEMA.PLUGINS
table or
use the SHOW PLUGINS
statement
(see Section 5.6.2, “Obtaining Server Plugin Information”). For
example:
mysql>SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'validate%';
+-------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +-------------------+---------------+ | validate_password | ACTIVE | +-------------------+---------------+
If the plugin fails to initialize, check the server error log for diagnostic messages.
If the plugin has been previously registered with
INSTALL PLUGIN
or is loaded with
--plugin-load-add
, you can use
the --validate-password
option at server
startup to control plugin activation. For example, to load the
plugin at startup and prevent it from being removed at runtime,
use these options:
[mysqld] plugin-load-add=validate_password.so validate-password=FORCE_PLUS_PERMANENT
If it is desired to prevent the server from running without the
password-validation plugin, use
--validate-password
with a value
of FORCE
or
FORCE_PLUS_PERMANENT
to force server startup
to fail if the plugin does not initialize successfully.
To control the activation of the
validate_password
plugin, use this option:
Command-Line Format | --validate-password[=value] | ||
Permitted Values | Type | enumeration | |
Default | ON | ||
Valid Values | ON | ||
OFF | |||
FORCE | |||
FORCE_PLUS_PERMANENT |
This option controls how the server loads the
validate_password
plugin at startup. The
value should be one of those available for plugin-loading
options, as described in
Section 5.6.1, “Installing and Uninstalling Plugins”. For example,
--validate-password=FORCE_PLUS_PERMANENT
tells the server to load the plugin at startup and prevents
it from being removed while the server is running.
This option is available only if the
validate_password
plugin has been
previously registered with INSTALL
PLUGIN
or is loaded with
--plugin-load-add
. See
Section 6.5.3.1, “Password Validation Plugin Installation”.
If the validate_password
plugin is enabled,
it exposes several system variables representing the parameters
that control password checking:
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | ON |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
To change how passwords are checked, you can set these system variables at server startup or at runtime. The following list describes the meaning of each variable.
validate_password_check_user_name
Command-Line Format | --validate-password-check-user-name | ||
System Variable | Name | validate_password_check_user_name | |
Variable Scope | Global | ||
Dynamic Variable | Yes | ||
Permitted Values | Type | boolean | |
Default | ON |
Whether passwords are compared to the user name part of the
effective user account for the current session and rejected
if they match. By default,
validate_password_check_user_name
is enabled. This variable controls user name matching
independent of the value of
validate_password_policy
.
When
validate_password_check_user_name
is enabled, it has these effects:
Checking occurs in all contexts for which the
validate_password
plugin is invoked,
which includes use of statements such as
ALTER USER
or
SET PASSWORD
to change
the current user's password, and invocation of functions
such as PASSWORD()
and
VALIDATE_PASSWORD_STRENGTH()
.
If a password is the same as the user name or its reverse, a match occurs and the password is rejected.
If a password matches the user name,
VALIDATE_PASSWORD_STRENGTH()
returns 0 regardless of how other
validate_password
system variables
are set.
The user names used for comparison are taken from the
values of the USER()
and
CURRENT_USER()
functions
for the current session. (An implication is that a user
who has the SUPER
privilege can execute a statement to set another user's
password to that user name, and cannot set that user's
password to the name of the user executing the
statement.)
Only the user name part of the
USER()
and
CURRENT_USER()
function
values is used, not the host name part. If a user name
is empty, no comparison is done.
User name matching is case sensitive. The password and user name values are compared as binary strings on a byte-by-byte basis.
validate_password_dictionary_file
System Variable | Name | validate_password_dictionary_file | |
Variable Scope | Global | ||
Dynamic Variable | Yes | ||
Permitted Values | Type | file name |
The path name of the dictionary file used by the
validate_password
plugin for checking
passwords. This variable is unavailable unless that plugin
is installed.
By default, this variable has an empty value and dictionary
checks are not performed. To enable dictionary checks, you
must set this variable to a nonempty value. If the file is
named as a relative path, it is interpreted relative to the
server data directory. Its contents should be lowercase, one
word per line. Contents are treated as having a character
set of utf8
. The maximum permitted file
size is 1MB.
For the dictionary file to be used during password checking,
the password policy must be set to 2
(STRONG
); see the description of the
validate_password_policy
system variable. Assuming that is true, each substring of
the password of length 4 up to 100 is compared to the words
in the dictionary file. Any match causes the password to be
rejected. Comparisons are not case sensitive.
For
VALIDATE_PASSWORD_STRENGTH()
,
the password is checked against all policies, including
STRONG
, so the strength assessment
includes the dictionary check regardless of the
validate_password_policy
value.
validate_password_dictionary_file
can be set at runtime and assigning a value causes the named
file to be read without a restart.
System Variable | Name | validate_password_length | |
Variable Scope | Global | ||
Dynamic Variable | Yes | ||
Permitted Values | Type | integer | |
Default | 8 | ||
Min Value | 0 |
The minimum number of characters that passwords checked by
the validate_password
plugin must have.
This variable is unavailable unless that plugin is
installed.
The
validate_password_length
minimum value is a function of several other related system
variables. The server will not set the value less than the
value of this expression:
validate_password_number_count + validate_password_special_char_count + (2 * validate_password_mixed_case_count)
If the validate_password
plugin adjusts
the value of
validate_password_length
due to the preceding constraint, it writes a message to the
error log.
validate_password_mixed_case_count
System Variable | Name | validate_password_mixed_case_count | |
Variable Scope | Global | ||
Dynamic Variable | Yes | ||
Permitted Values | Type | integer | |
Default | 1 | ||
Min Value | 0 |
The minimum number of lowercase and uppercase characters
that passwords checked by the
validate_password
plugin must have if the
password policy is MEDIUM
or stronger.
For a given value, the password must have that many
lowercase characters, and that many uppercase characters.
This variable is unavailable unless that plugin is
installed.
validate_password_number_count
System Variable | Name | validate_password_number_count | |
Variable Scope | Global | ||
Dynamic Variable | Yes | ||
Permitted Values | Type | integer | |
Default | 1 | ||
Min Value | 0 |
The minimum number of numeric (digit) characters that
passwords checked by the
validate_password
plugin must have if the
password policy is MEDIUM
or stronger.
This variable is unavailable unless that plugin is
installed.
System Variable | Name | validate_password_policy | |
Variable Scope | Global | ||
Dynamic Variable | Yes | ||
Permitted Values | Type | enumeration | |
Default | 1 | ||
Valid Values | 0 | ||
1 | |||
2 |
The password policy enforced by the
validate_password
plugin. This variable
is unavailable unless that plugin is installed.
validate_password_policy
affects how the plugin uses its other policy-setting system
variables, except for checking passwords against user names,
which is controlled independently by
validate_password_check_user_name
.
The
validate_password_policy
value can be specified using numeric values 0, 1, 2, or the
corresponding symbolic values LOW
,
MEDIUM
, STRONG
. The
following table describes the tests performed for each
policy. For the length test, the required length is the
value of the
validate_password_length
system variable. Similarly, the required values for the
other tests are given by other
validate_password_
variables.
xxx
Policy | Tests Performed |
---|---|
0 or LOW | Length |
1 or MEDIUM | Length; numeric, lowercase/uppercase, and special characters |
2 or STRONG | Length; numeric, lowercase/uppercase, and special characters; dictionary file |
validate_password_special_char_count
System Variable | Name | validate_password_special_char_count | |
Variable Scope | Global | ||
Dynamic Variable | Yes | ||
Permitted Values | Type | integer | |
Default | 1 | ||
Min Value | 0 |
The minimum number of nonalphanumeric characters that
passwords checked by the
validate_password
plugin must have if the
password policy is MEDIUM
or stronger.
This variable is unavailable unless that plugin is
installed.
If the validate_password
plugin is enabled,
it exposes status variables that provide operational
information:
mysql> SHOW STATUS LIKE 'validate_password%';
+-----------------------------------------------+---------------------+
| Variable_name | Value |
+-----------------------------------------------+---------------------+
| validate_password_dictionary_file_last_parsed | 2015-06-29 11:08:51 |
| validate_password_dictionary_file_words_count | 1902 |
+-----------------------------------------------+---------------------+
The following list describes the meaning of each status variable.
validate_password_dictionary_file_last_parsed
When the dictionary file was last parsed.
validate_password_dictionary_file_words_count
The number of words read from the dictionary file.
MySQL Server supports a keyring service that enables internal server components and plugins to securely store sensitive information for later retrieval. The implementation is plugin-based:
The keyring_file
plugin stores keyring data
in a file local to the server host. This plugin is available
in all MySQL distributions, Community Edition and Enterprise
Edition included.
The keyring_file
plugin for encryption
key management is not intended as a regulatory compliance
solution. Security standards such as PCI, FIPS, and others
require use of key management systems to secure, manage, and
protect encryption keys in key vaults or hardware security
modules (HSMs).
An SQL interface for keyring key management is implemented as a set of user-defined functions (UDFs).
The InnoDB
storage engine uses the keyring to
store its key for tablespace encryption. InnoDB
can use any supported keyring plugin.
For general keyring installation instructions, see Section 6.5.4.1, “Keyring Plugin Installation”. For information specific to a given keyring plugin, see the section describing that plugin.
For information about using the keyring UDFs, see Section 6.5.4.4, “General-Purpose Keyring Key-Management Functions”.
Keyring plugins and UDFs access a keyring service that provides the interface for server components to the keyring. For information about accessing the keyring plugin service and writing keyring plugins, see Section 28.3.2, “The Keyring Service”, and Section 28.2.4.12, “Writing Keyring Plugins”.
Keyring service consumers require a keyring plugin to be installed. MySQL provides these plugin choices:
keyring_file
: A plugin that stores
keyring data in a file local to the server host. Available
in all MySQL distributions.
This section describes how to install the keyring plugin of your choosing. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.
To be usable by the server, the plugin library file must be
located in the MySQL plugin directory (the directory named by
the plugin_dir
system
variable). If necessary, set the value of
plugin_dir
at server startup to
tell the server the plugin directory location.
Installation for each keyring plugin is similar. The following
instructions use keyring_file
. Users of a
different keyring plugin can substitute its name for
keyring_file
.
The keyring_file
plugin library file base
name is keyring_file
. The file name suffix
differs per platform (for example, .so
for
Unix and Unix-like systems, .dll
for
Windows).
Only one keyring plugin should be enabled at a time. Enabling multiple keyring plugins is unsupported and results may not be as anticipated.
The keyring plugin must be loaded early during the server
startup sequence so that server components can access it as
necessary during their own initialization. For example, the
InnoDB
storage engine uses the keyring for
tablespace encryption, so the keyring plugin must be loaded and
available prior to InnoDB
initialization.
To load the plugin, use the
--early-plugin-load
option to
name the plugin library file that contains it. For example, on
platforms where the plugin library file suffix is
.so
, use these lines in the server
my.cnf
file (adjust the
.so
suffix for your platform as necessary):
[mysqld] early-plugin-load=keyring_file.so
Before starting the server, check the notes for your chosen keyring plugin to see whether it permits or requires additional configuration:
For keyring_file
:
Section 6.5.4.2, “Using the keyring_file File-Based Plugin”.
After performing any plugin-specific configuration, verify
plugin installation. With the MySQL server running, examine the
INFORMATION_SCHEMA.PLUGINS
table or
use the SHOW PLUGINS
statement
(see Section 5.6.2, “Obtaining Server Plugin Information”). For
example:
mysql>SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'keyring%';
+--------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +--------------+---------------+ | keyring_file | ACTIVE | +--------------+---------------+
If the plugin fails to initialize, check the server error log for diagnostic messages.
If no keyring plugin is available when a server component tries
to access the keyring service, the service cannot be used by
that component. As a result, the component may fail to
initialize or may initialize with limited functionality. For
example, if InnoDB
finds that there are
encrypted tablespaces when it initializes, it attempts to access
the keyring. If the keyring is unavailable,
InnoDB
can access only unencrypted
tablespaces. To ensure that InnoDB
can access
encrypted tablespaces as well, use
--early-plugin-load
to load the
keyring plugin.
Plugins can be loaded by other methods, such as the
--plugin-load
or
--plugin-load-add
option or the
INSTALL PLUGIN
statement.
However, keyring plugins loaded using those methods may be
available too late in the server startup sequence for certain
server components, such as InnoDB
:
Plugin loading using
--plugin-load
or
--plugin-load-add
occurs
after InnoDB
initialization.
Plugins installed using INSTALL
PLUGIN
are registered in the
mysql.plugin
system table and loaded
automatically for subsequent server restarts. However,
because mysql.plugin
is an
InnoDB
table, any plugins named in it can
be loaded during startup only after
InnoDB
initialization.
The keyring_file
plugin is a keyring plugin
that stores keyring data in a file local to the server host.
The keyring_file
plugin for encryption key
management is not intended as a regulatory compliance
solution. Security standards such as PCI, FIPS, and others
require use of key management systems to secure, manage, and
protect encryption keys in key vaults or hardware security
modules (HSMs).
To install the keyring_file
plugin, use the
general keyring installation instructions found in
Section 6.5.4.1, “Keyring Plugin Installation”, together with the
configuration information specific to
keyring_file
found here.
keyring_file
must be loaded at each server
startup using the
--early-plugin-load
option. The
keyring_file_data
system
variable optionally configures the location of the file used by
the keyring_file
plugin for data storage. The
default value is platform specific. To configure the file
location explicitly, set the variable value at startup. For
example, use these lines in the server
my.cnf
file (adjust the
.so
suffix and file location for your
platform as necessary):
[mysqld] early-plugin-load=keyring_file.so keyring_file_data=/usr/local/mysql/mysql-keyring/keyring
Keyring operations are transactional: The
keyring_file
plugin uses a backup file during
write operations to ensure that it can roll back to the original
file if an operation fails. The backup file has the same name as
the value of the
keyring_file_data
system
variable with a suffix of .backup
.
For additional information about
keyring_file_data
, see
Section 6.5.4.6, “Keyring System Variables”.
To ensure that keys are flushed only when the correct keyring
storage file exists, keyring_file
stores a
SHA-256 checksum of the keyring in the file. Before updating the
file, the plugin verifies that it contains the expected
checksum.
The keyring_file
plugin supports the
functions that comprise the standard keyring service interface.
Keyring operations performed by those functions are accessible
at two levels:
SQL interface: In SQL statements, call the user-defined functions (UDFs) described in Section 6.5.4.4, “General-Purpose Keyring Key-Management Functions”.
C interface: In C-language code, call the keyring service functions described in Section 28.3.2, “The Keyring Service”.
Example (using UDFs):
SELECT keyring_key_generate('MyKey', 'AES', 32); SELECT keyring_key_remove('MyKey');
The key types permitted by keyring_file
are
described in Section 6.5.4.3, “Supported Keyring Key Types”.
MySQL Keyring supports generating keys of different types (encryption algorithms) and lengths. The available key types depend on which keyring plugin is installed. A given plugin may also impose constraints on key lengths per key type.
Table 6.16, “Keyring Plugin Key Types” summarizes the permitted key types per keyring plugin. Lengths are in bytes. For a key generated using one of the keyring user-defined functions (UDFs) described in Section 6.5.4.4, “General-Purpose Keyring Key-Management Functions”, the length can be no more than 2,048 bytes, due to limitations of the UDF interface.
Table 6.16 Keyring Plugin Key Types
Plugin Name | Permitted Key Type | Permitted Key Lengths for Key Type |
keyring_file |
AES |
No special restrictions |
DSA |
No special restrictions | |
RSA |
No special restrictions |
MySQL Server supports a keyring service that enables internal server components and plugins to securely store sensitive information for later retrieval.
MySQL Server also includes an SQL interface for keyring key
management, implemented as a set of general-purpose user-defined
functions (UDFs) that access the functions provided by the
internal keyring service. The keyring UDFs are contained in a
plugin library file, which also contains a
keyring_udf
plugin that must be enabled prior
to UDF invocation. For these UDFs to be used, a keyring plugin
such as keyring_file
must be enabled.
The UDFs described here are general purpose and intended for use with any keyring plugin. A given keyring plugin might have UDFs of its own that are intended for use only with that plugin; see Section 6.5.4.5, “Plugin-Specific Keyring Key-Management Functions”.
The following sections provide installation instructions for the keyring UDFs and demonstrate how to use them. For information about the keyring service functions invoked by the UDFs, see Section 28.3.2, “The Keyring Service”. For general keyring information, see Section 6.5.4, “The MySQL Keyring”.
This section describes how to install or uninstall the keyring
user-defined functions (UDFs), which are implemented in a
plugin library file that also contains a
keyring_udf
plugin. For general information
about installing or uninstalling plugins and UDFs, see
Section 5.6.1, “Installing and Uninstalling Plugins”, and
Section 28.4.2.5, “UDF Compiling and Installing”.
The keyring UDFs enable keyring key management operations, but
the keyring_udf
plugin must also be
installed because the UDFs will not work correctly without it.
Attempts to use the UDFs without the
keyring_udf
plugin result in an error.
To be usable by the server, the plugin library file must be
located in the MySQL plugin directory (the directory named by
the plugin_dir
system
variable). If necessary, set the value of
plugin_dir
at server startup
to tell the server the plugin directory location.
The plugin library file base name is
keyring_udf
. The file name suffix differs
per platform (for example, .so
for Unix
and Unix-like systems, .dll
for Windows).
To install the keyring_udf
plugin and the
UDFs, use the INSTALL PLUGIN
and CREATE FUNCTION
statements
(adjust the .so
suffix for your platform
as necessary):
INSTALL PLUGIN keyring_udf SONAME 'keyring_udf.so'; CREATE FUNCTION keyring_key_generate RETURNS INTEGER SONAME 'keyring_udf.so'; CREATE FUNCTION keyring_key_fetch RETURNS STRING SONAME 'keyring_udf.so'; CREATE FUNCTION keyring_key_length_fetch RETURNS INTEGER SONAME 'keyring_udf.so'; CREATE FUNCTION keyring_key_type_fetch RETURNS STRING SONAME 'keyring_udf.so'; CREATE FUNCTION keyring_key_store RETURNS INTEGER SONAME 'keyring_udf.so'; CREATE FUNCTION keyring_key_remove RETURNS INTEGER SONAME 'keyring_udf.so';
If the plugin and the UDFs are used on a master replication server, install them on all slave servers as well to avoid replication problems.
Once installed as just described, the
keyring_udf
plugin and the UDFs remain
installed until uninstalled. To remove them, use the
UNINSTALL PLUGIN
and
DROP FUNCTION
statements:
UNINSTALL PLUGIN keyring_udf; DROP FUNCTION keyring_key_generate; DROP FUNCTION keyring_key_fetch; DROP FUNCTION keyring_key_length_fetch; DROP FUNCTION keyring_key_type_fetch; DROP FUNCTION keyring_key_store; DROP FUNCTION keyring_key_remove;
Before using the keyring user-defined functions (UDFs), install them according to the instructions provided in Section 6.5.4.4.1, “Installing or Uninstalling General-Purpose Keyring Functions”.
The keyring UDFs are subject to these constraints:
To use any keyring UDF, the keyring_udf
plugin must be enabled. Otherwise, an error occurs:
ERROR 1123 (HY000): Can't initialize function 'keyring_key_generate'; This function requires keyring_udf plugin which is not installed. Please install
To install the keyring_udf
plugin, see
Section 6.5.4.4.1, “Installing or Uninstalling General-Purpose Keyring Functions”.
The keyring UDFs invoke keyring service functions (see
Section 28.3.2, “The Keyring Service”). The service functions
in turn use whatever keyring plugin is installed (for
example, keyring_file
). Therefore, to use any keyring UDF, some underlying
keyring plugin must be enabled. Otherwise, an error
occurs:
ERROR 3188 (HY000): Function 'keyring_key_generate' failed because underlying keyring service returned an error. Please check if a keyring plugin is installed and that provided arguments are valid for the keyring you are using.
To install a keyring plugin, see Section 6.5.4.1, “Keyring Plugin Installation”.
To use any keyring UDF, a user must possess the global
EXECUTE
privilege.
Otherwise, an error occurs:
ERROR 1123 (HY000): Can't initialize function 'keyring_key_generate'; The user is not privileged to execute this function. User needs to have EXECUTE
To grant the global EXECUTE
privilege to a user, use this statement:
GRANT EXECUTE ON *.* TO user
;
Alternatively, should you prefer to avoid granting the
global EXECUTE
privilege
while still permitting users to access specific
key-management operations, “wrapper” stored
programs can be defined (a technique described later in
this section).
A key stored in the keyring by a given user can be
manipulated later only by the same user. That is, the
value of the CURRENT_USER()
function at the time of key manipulation must have the
same value as when the key was stored in the keyring.
(This constraint rules out the use of the keyring UDFs for
manipulation of instance-wide keys, such as those created
by InnoDB
to support tablespace
encryption.)
To enable multiple users to perform operations on the same key, “wrapper” stored programs can be defined (a technique described later in this section).
Keyring UDFs support the key types and lengths supported by the underlying keyring plugin, with the additional constraint that keys cannot be longer than 2,048 bytes (16,384 bits), due to limitations of the UDF interface. See Section 6.5.4.3, “Supported Keyring Key Types”.
To create a new random key and store it in the keyring, call
keyring_key_generate()
, passing to it an ID
for the key, along with the key type (encryption method) and
its length in bytes. The following call creates a 2,048-bit
DSA-encrypted key named MyKey
:
mysql> SELECT keyring_key_generate('MyKey', 'DSA', 256);
+-------------------------------------------+
| keyring_key_generate('MyKey', 'DSA', 256) |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
A return value of 1 indicates success. If the key cannot be
created, the return value is NULL
and an
error occurs. One reason this might be is that the underlying
keyring plugin does not support the specified combination of
key type and key length; see
Section 6.5.4.3, “Supported Keyring Key Types”.
To be able to check the return type regardless of whether an
error occurs, use SELECT ... INTO
@
and test the
variable value:
var_name
mysql>SELECT keyring_key_generate('', '', -1) INTO @x;
ERROR 3188 (HY000): Function 'keyring_key_generate' failed because underlying keyring service returned an error. Please check if a keyring plugin is installed and that provided arguments are valid for the keyring you are using. mysql>SELECT @x;
+------+ | @x | +------+ | NULL | +------+ mysql>SELECT keyring_key_generate('x', 'AES', 16) INTO @x;
mysql>SELECT @x;
+------+ | @x | +------+ | 1 | +------+
This technique also applies to other keyring UDFs that for failure return a value and an error.
The ID passed to keyring_key_generate()
provides a means by which to refer to the key in subsequent
UDF calls. For example, use the key ID to retrieve its type as
a string or its length in bytes as an integer:
mysql>SELECT keyring_key_type_fetch('MyKey');
+---------------------------------+ | keyring_key_type_fetch('MyKey') | +---------------------------------+ | DSA | +---------------------------------+ mysql>SELECT keyring_key_length_fetch('MyKey');
+-----------------------------------+ | keyring_key_length_fetch('MyKey') | +-----------------------------------+ | 256 | +-----------------------------------+
To retrieve a key value, pass the key ID to
keyring_key_fetch()
. The following example
uses HEX()
to display the key
value because it may contain nonprintable characters. The
example also uses a short key for brevity, but be aware that
longer keys provide better security:
mysql>SELECT keyring_key_generate('MyShortKey', 'DSA', 8);
+----------------------------------------------+ | keyring_key_generate('MyShortKey', 'DSA', 8) | +----------------------------------------------+ | 1 | +----------------------------------------------+ mysql>SELECT HEX(keyring_key_fetch('MyShortKey'));
+--------------------------------------+ | HEX(keyring_key_fetch('MyShortKey')) | +--------------------------------------+ | 1DB3B0FC3328A24C | +--------------------------------------+
Keyring UDFs treat key IDs, types, and values as binary
strings, so comparisons are case sensitive. For example, IDs
of MyKey
and mykey
refer
to different keys.
To remove a key, pass the key ID to
keyring_key_remove()
:
mysql> SELECT keyring_key_remove('MyKey');
+-----------------------------+
| keyring_key_remove('MyKey') |
+-----------------------------+
| 1 |
+-----------------------------+
To obfuscate and store a key that you provide, pass the key
ID, type, and value to keyring_key_store()
:
mysql> SELECT keyring_key_store('AES_key', 'AES', 'Secret string');
+------------------------------------------------------+
| keyring_key_store('AES_key', 'AES', 'Secret string') |
+------------------------------------------------------+
| 1 |
+------------------------------------------------------+
As indicated previously, a user must have the global
EXECUTE
privilege to call
keyring UDFs, and the user who stores a key in the keyring
initially must be the same user who performs subsequent
operations on the key later, as determined from the
CURRENT_USER()
value in effect
for each UDF call. To permit key operations to users who do
not have the global EXECUTE
privilege or who may not be the key “owner,” use
this technique:
Define “wrapper” stored programs that
encapsulate the required key operations and have a
DEFINER
value equal to the key owner.
Grant the EXECUTE
privilege
for specific stored programs to the individual users who
should be able to invoke them.
If the operations implemented by the wrapper stored
programs do not include key creation, create any necessary
keys in advance, using the account named as the
DEFINER
in the stored program
definitions.
This technique enables keys to be shared among users and provides to DBAs more fine-grained control over who can do what with keys, without having to grant global privileges.
The following example shows how to set up a shared key named
SharedKey
that is owned by the DBA, and a
get_shared_key()
stored function that
provides access to the current key value. The value can be
retrieved by any user with the
EXECUTE
privilege for that
function, which is created in the
key_schema
schema.
From a MySQL administrative account
('root'@'localhost'
in this example),
create the administrative schema and the stored function to
access the key:
mysql>CREATE SCHEMA key_schema;
mysql>CREATE DEFINER = 'root'@'localhost'
FUNCTION key_schema.get_shared_key()
RETURNS BLOB READS SQL DATA
RETURN keyring_key_fetch('SharedKey');
From the administrative account, ensure that the shared key exists:
mysql> SELECT keyring_key_generate('SharedKey', 'DSA', 8);
+---------------------------------------------+
| keyring_key_generate('SharedKey', 'DSA', 8) |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
From the administrative account, create an ordinary user account to which key access is to be granted:
mysql>CREATE USER 'key_user'@'localhost'
IDENTIFIED BY 'key_user_pwd';
From the key_user
account, verify that,
without the proper EXECUTE
privilege, the new account cannot access the shared key:
mysql> SELECT HEX(key_schema.get_shared_key());
ERROR 1370 (42000): execute command denied to user 'key_user'@'localhost'
for routine 'key_schema.get_shared_key'
From the administrative account, grant
EXECUTE
to
key_user
for the stored function:
mysql>GRANT EXECUTE ON FUNCTION key_schema.get_shared_key
TO 'key_user'@'localhost';
From the key_user
account, verify that the
key is now accessible:
mysql> SELECT HEX(key_schema.get_shared_key());
+----------------------------------+
| HEX(key_schema.get_shared_key()) |
+----------------------------------+
| 9BAFB9E75CEEB013 |
+----------------------------------+
For each general-purpose keyring user-defined function (UDF), this section describes its purpose, calling sequence, and return value. For information about the conditions under which these UDFs can be invoked, see Section 6.5.4.4.2, “Using General-Purpose Keyring Functions”.
keyring_key_fetch()
Given a key ID, deobfuscates and returns the key value.
Syntax:
STRING keyring_key_fetch(STRING key_id)
Arguments:
key_id
: The key ID as a string.
Return values:
Returns the key value for success, NULL
if the key does not exist, or NULL
and
an error for failure.
Keyring values retrieved using
keyring_key_fetch()
are limited to
2,048 bytes, due to limitations of the UDF interface. A
keyring value longer than that length can be stored
using a keyring service function (see
Section 28.3.2, “The Keyring Service”), but if retrieved
using keyring_key_fetch()
, is
truncated to 2,048 bytes.
Example:
mysql>SELECT keyring_key_generate('RSA_key', 'RSA', 16);
+--------------------------------------------+ | keyring_key_generate('RSA_key', 'RSA', 16) | +--------------------------------------------+ | 1 | +--------------------------------------------+ mysql>SELECT HEX(keyring_key_fetch('RSA_key'));
+-----------------------------------+ | HEX(keyring_key_fetch('RSA_key')) | +-----------------------------------+ | 91C2253B696064D3556984B6630F891A | +-----------------------------------+ mysql>SELECT keyring_key_type_fetch('RSA_key');
+-----------------------------------+ | keyring_key_type_fetch('RSA_key') | +-----------------------------------+ | RSA | +-----------------------------------+ mysql>SELECT keyring_key_length_fetch('RSA_key');
+-------------------------------------+ | keyring_key_length_fetch('RSA_key') | +-------------------------------------+ | 16 | +-------------------------------------+
The example uses HEX()
to
display the key value because it may contain nonprintable
characters. The example also uses a short key for brevity,
but be aware that longer keys provide better security.
keyring_key_generate()
Generates a new random key with a given ID, type, and length, and stores it in the keyring. The type and length values must be consistent with the values supported by the underlying keyring plugin, with the additional constraint that keys cannot be longer than 2,048 bytes (16,384 bits), due to limitations of the UDF interface. For the permitted types per plugin, see Section 28.3.2, “The Keyring Service”.
Syntax:
STRING keyring_key_generate(STRING key_id, STRING key_type, INTEGER key_length)
Arguments:
key_id
: The key ID as a string.
key_type
: The key type as a string.
key_length
: The key length in bytes
as an integer. The maximum length is 2,048.
Return values:
Returns 1 for success, or NULL
and an
error for failure.
Example:
mysql> SELECT keyring_key_generate('RSA_key', 'RSA', 384);
+---------------------------------------------+
| keyring_key_generate('RSA_key', 'RSA', 384) |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
keyring_key_length_fetch()
Given a key ID, returns the key length.
Syntax:
INTEGER keyring_key_length_fetch(STRING key_id)
Arguments:
key_id
: The key ID as a string.
Return values:
Returns the key length in bytes as an integer for success,
NULL
if the key does not exist, or
NULL
and an error for failure.
Example:
See the description of
keyring_key_fetch()
.
keyring_key_remove()
Removes the key with a given ID from the keyring.
Syntax:
INTEGER keyring_key_remove(STRING key_id)
Arguments:
key_id
: The key ID as a string.
Return values:
Returns 1 for success, or NULL
for
failure.
Example:
mysql> SELECT keyring_key_remove('AES_key');
+-------------------------------+
| keyring_key_remove('AES_key') |
+-------------------------------+
| 1 |
+-------------------------------+
keyring_key_store()
Obfuscates and stores a key in the keyring.
Syntax:
INTEGER keyring_key_store(STRING key_id, STRING key_type, STRING key)
Arguments:
key_id
: The key ID as a string.
key_type
: The key type as a string.
key
: The key value as a string.
Return values:
Returns 1 for success, or NULL
and an
error for failure.
Example:
mysql> SELECT keyring_key_store('new key', 'DSA', 'My key value');
+-----------------------------------------------------+
| keyring_key_store('new key', 'DSA', 'My key value') |
+-----------------------------------------------------+
| 1 |
+-----------------------------------------------------+
keyring_key_type_fetch()
Given a key ID, returns the key type.
Syntax:
STRING keyring_key_type_fetch(STRING key_id)
Arguments:
key_id
: The key ID as a string.
Return values:
Returns the key type as a string for success,
NULL
if the key does not exist, or
NULL
and an error for failure.
Example:
See the description of
keyring_key_fetch()
.
This section describes user-defined functions (UDFs) that are specific to individual keyring plugins. Currently, no keyring plugin in MySQL 8.0 has plugin-specific UDFs. For information about general-purpose keyring UDFs, see Section 6.5.4.4, “General-Purpose Keyring Key-Management Functions”.
MySQL Keyring plugins support the following system variables. Use them to configure keyring plugin operation. These variables are unavailable unless the appropriate keyring plugin is installed (see Section 6.5.4.1, “Keyring Plugin Installation”).
Command-Line Format | --keyring-file-data=file_name | ||
System Variable | Name | keyring_file_data | |
Variable Scope | Global | ||
Dynamic Variable | Yes | ||
Permitted Values | Type | file name | |
Default | platform specific |
The path name of the data file used for secure data storage
by the keyring_file
plugin. This variable
is unavailable unless that plugin is installed. The file
location should be in a directory considered for use only by
the keyring_file
plugin. For example, do
not locate the file under the data directory.
Keyring operations are transactional: The
keyring_file
plugin uses a backup file
during write operations to ensure that it can roll back to
the original file if an operation fails. The backup file has
the same name as the value of the
keyring_file_data
system
variable with a suffix of .backup
.
Do not use the same keyring_file
data
file for multiple MySQL instances. Each instance should have
its own unique data file.
The default file name is keyring
,
located in a directory that is platform specific and depends
on the value of the
INSTALL_LAYOUT
CMake option, as shown in the following
table. To specify the default directory for the file
explicitly if you are building from source, use the
INSTALL_MYSQLKEYRINGDIR
CMake option.
INSTALL_LAYOUT Value | Default keyring_file_data Value |
---|---|
DEB , RPM , SLES ,
SVR4 | /var/lib/mysql-keyring/keyring |
Otherwise | keyring/keyring under the
CMAKE_INSTALL_PREFIX
value |
At plugin startup, if the value assigned to
keyring_file_data
specifies
a file that does not exist, the
keyring_file
plugin attempts to create it
(as well as its parent directory, if necessary).
If you create the directory manually, it should have a
restrictive mode and be accessible only to the account used
to run the MySQL server. For example, on Unix and Unix-like
systems, to use
/usr/local/mysql/mysql-keyring/keyring
,
the following commands (executed as root
)
create the directory and set its mode and ownership:
cd /usr/local/mysql mkdir mysql-keyring chmod 750 mysql-keyring chown mysql mysql-keyring chgrp mysql mysql-keyring
If the keyring_file
plugin cannot create
or access the file, it writes an error message to the error
log. If an attempted runtime assignment to
keyring_file_data
results
in an error, the variable value remains unchanged.
Once the keyring_file
plugin has
created the keyring_file
plugin data
file and started to use it, it is important not to remove
the file. For example, InnoDB
uses the
file to store the master key used to decrypt the data in
tables that use InnoDB
tablespace
encryption; see
Section 15.7.10, “InnoDB Tablespace Encryption”. Loss of
the file will cause data in such tables to become
inaccessible. (It is permissible to rename or move the
file, as long as you change the value of
keyring_file_data
to
match.) It is recommended that you create a separate
backup of the keyring
file
immediately after you create the first encrypted table and
before and after master key rotation.