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

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

6.1.1 Security Guidelines

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

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

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

When running MySQL, follow these guidelines:

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

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.

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

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

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

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

Warning

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

6.1.2 Keeping Passwords Secure

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

6.1.2.1 End-User Guidelines for Password Security

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

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

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

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=file_name
option, where file_name is the full
path name to the file. For example:

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.

6.1.2.2 Administrator Guidelines for Password Security

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

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

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.

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:

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:

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

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

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

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.

6.1.3 Making MySQL Secure Against Attackers

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

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

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

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

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:

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.

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.

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

6.1.5 How to Run MySQL as a Normal User

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

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

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

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=user_name
option. mysqld starts up, then switches to
run as the Unix user 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”.

6.1.6 Security Issues with LOAD DATA LOCAL

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

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

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

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

To avoid LOAD DATA issues, clients
should avoid using LOCAL. To avoid connecting
to untrusted servers, clients can establish a secure connection
and verify the server identity by connecting using the
--ssl-mode=VERIFY_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_INFILECMake 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.

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

[client]
loose-local-infile=0

or:

[client]
loose-local-infile=1

In all cases, successful use of a LOCAL
load operation by a client also requires that the server
permits it.

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

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

6.1.7 Client Programming Security Guidelines

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

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

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

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:

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.

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.

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.

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

6.2.1 Privileges Provided by MySQL

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

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

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

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

Privileges also differ in terms of whether they are static (built
in to the server) or dynamic (defined at runtime). Whether a
privilege is static or dynamic affects its availability to be
granted to user accounts and roles. 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.

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

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

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 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 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-xxx
commands perform functions similar to
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 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.

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

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.

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.

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.

6.2.2 Static Versus Dynamic Privileges

MySQL supports static and dynamic privileges:

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

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

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

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

Note

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

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

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

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

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

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

Granted dynamic privileges appear in the output from the
SHOW GRANTS statement and the
INFORMATION_SCHEMAUSER_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.

Migrating Accounts from SUPER to Dynamic Privileges

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

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

For each account identified by the preceding query,
determine the operations for which it needs
SUPER. Then grant the dynamic
privileges corresponding to those operations, and revoke
SUPER.

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

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

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

6.2.3 Grant Tables

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

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.

Note

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

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

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.

Caution

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

The 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 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_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.

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:

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

Proxied_host,
Proxied_user: The proxied account.

Grantor, Timestamp:
Unused.

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

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

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

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

PRIV: The privilege name.

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

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

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

DEFAULT_ROLE_HOST,
DEFAULT_ROLE_USER: The default role.

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

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

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

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

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.

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.

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.

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:

6.2.4 Specifying Account Names

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

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

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

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.

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
host_ip/netmask.
For example:

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:

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.

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

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

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

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

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

SET ROLE 'myrole'@'%';

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

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

6.2.6 Access Control, Stage 1: Connection Verification

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

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

Whether your account is locked or unlocked

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

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

Your identity is based on two pieces of information:

The client host from which you connect

Your MySQL user name

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

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

Nonblank authentication_string values in the
user table represent encrypted passwords. MySQL
does not store passwords in cleartext form for anyone to see.
Rather, the password supplied by a user who is attempting to
connect is encrypted (using the password hashing method
implemented by the account authentication plugin). The encrypted
password then is used during the connection process when checking
whether the password is correct. This is done without the
encrypted password ever traveling over the connection. See
Section 6.3.1, “User Names and Passwords”.

From MySQL's point of view, the encrypted password is the
real password, so you should never give
anyone access to it. In particular, do not give
nonadministrative users read access to tables in the
mysql 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.

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.

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.

Note

It is a common misconception to think that, for a given user
name, all rows that explicitly name that user are used first
when the server attempts to find a match for the connection.
This is not true. The preceding example illustrates this, where
a connection from 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
user_name@host_name
format that indicates the User and
Host values from the matching
user table row. Suppose that
jeffrey connects and issues the following
query:

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

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

6.2.7 Access Control, Stage 2: Request Verification

After you establish a connection, the server enters Stage 2 of
access control. For each request that you issue through that
connection, the server determines what operation you want to
perform, then checks whether you have sufficient privileges to do
so. This is where the privilege columns in the grant tables come
into play. These privileges can come from any of the
user, db,
tables_priv, columns_priv,
or procs_priv tables. (You may find it helpful
to refer to Section 6.2.3, “Grant Tables”, which lists the
columns present in each of the grant tables.)

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

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

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

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

A '%' or blank Host
value means “any host.”

A '%' or blank Db value
means “any database.”

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

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

The wildcard characters % and
_ can be used in the
Host column. These have the same meaning as
for pattern-matching operations performed with the
LIKE operator.

A '%' or blank Host
value means “any host.”

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

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

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

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

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

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

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

It may not be apparent why, if the global user
row privileges are initially found to be insufficient for the
requested operation, the server adds those privileges to the
database, table, and column privileges later. The reason is that a
request might require more than one type of privilege. For
example, if you execute an
INSERT INTO ...
SELECT statement, you need both the
INSERT and the
SELECT privileges. Your privileges
might be such that the user table row grants
one privilege and the db table row grants the
other. In this case, you have the necessary privileges to perform
the request, but the server cannot tell that from either table by
itself; the privileges granted by the rows in both tables must be
combined.

6.2.8 When Privilege Changes Take Effect

When mysqld starts, it reads all grant table
contents into memory. The in-memory tables become effective for
access control at that point.

If you modify the grant tables indirectly using 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!

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

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

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

Note

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

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

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

6.2.9 Troubleshooting Problems Connecting to MySQL

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

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

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:

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:

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:

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.

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:

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

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

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

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

SELECT * FROM user;

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

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

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

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

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

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

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

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:

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

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

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

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

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

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

If you are able to connect to the MySQL server, but get an
Access denied message whenever you issue a
SELECT ... INTO
OUTFILE or
LOAD DATA
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
user_namedb_name or mysql
-u user_name
-pyour_passdb_name. If you are able
to connect using the mysql client, the
problem lies with your program, not with the access
privileges. (There is no space between -p and
the password; you can also use the
--password=your_pass
syntax to specify the password. If you use the
-p or
--password option with no
password value, MySQL prompts you for the password.)

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

6.3.1 User Names and Passwords

MySQL stores accounts in the user table of the
mysql system database. An account is defined in
terms of a user name and the client host or hosts from which the
user can connect to the server. For information about account
representation in the user table, see
Section 6.2.3, “Grant Tables”.

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

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

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

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

Warning

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

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

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

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

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

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

Standard MySQL client programs support a
--default-character-set option that causes
mysql_options() to be called
as just described. In addition, character set autodetection is
supported as described in
Section 10.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.

6.3.2 Adding User Accounts

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:

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

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

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

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

6.3.3 Removing User Accounts

6.3.4 Using Roles

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

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

The following list summarizes role management capabilities
provided by MySQL:

For descriptions of individual role-manipulation statements, 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.

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.

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

To assign privileges to the roles, execute
GRANT 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:

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:

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

Defining Mandatory Roles

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

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.

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.

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:

Activating Roles

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

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

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:

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:

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.

Revoking Roles or Role Privileges

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

REVOKE role FROM user;

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

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

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

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

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

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.

Removing Roles

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

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

User and Role Interchangeability

As has been hinted at earlier for SHOW
GRANTS, which displays grants for user accounts or
roles, accounts and roles can be used interchangeably. 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:

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

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:

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

6.3.6 Setting Account Resource Limits

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

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

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:

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:

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:

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:

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.

6.3.7 Assigning Account Passwords

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

MySQL stores 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.

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.

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 -u user_name -h host_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.)

6.3.8 Password Expiration Policy

Account passwords have an age, assessed from the date and time of
the most recent password change.

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

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:

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.

Note

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.

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.

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

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:

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.

6.3.10 Pluggable Authentication

When a client connects to the MySQL server, the server uses the
user name provided by the client and the client host to select the
appropriate account row from the mysql.user
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 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 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”.

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.

Authentication Plugin Usage Instructions

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=plugin_name
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.

If the client program does not find the client-side plugin,
specify a
--plugin-dir=dir_name
option to indicate where the plugin is located.

Note

If you start the server with the
--skip-grant-tables option,
authentication plugins are not used even if loaded because the
server performs no client authentication and permits any client
to connect. Because this is insecure, you might want to use
--skip-grant-tables in
conjunction with
--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.

6.3.11 Proxy Users

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

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

Requirements for Proxy User Support

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.

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:

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
'auth_string' clause
specifying a string that the server passes to the plugin when
the user connects. If present, the string provides information
that helps the plugin determine how to map the external client
user name to a proxied user name. It is up to each plugin
whether it requires the AS clause. If so, the
format of the authentication string depends on how the plugin
intends to use it. Consult the documentation for a given plugin
for information about the authentication string values it
accepts.

Granting the Proxy Privilege

The PROXY privilege is needed to
enable an external user to connect as and have the privileges of
another user. To grant this privilege, use the
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.

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:

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

GRANT PROXY ON sally TO joe;

Default Proxy Users

To specify that some or all users should connect using a given
authentication plugin, create a “blank” MySQL
account (''@''), 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:

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:

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.

Default Proxy User and Anonymous User Conflicts

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

In the preceding discussion, the default proxy user account has
'' in the host part, which matches any host.
If you set up a default proxy user, take care to also check
whether nonproxy accounts exist with the same user part and
'%' in the host part, because
'%' also matches any host, but has precedence
over '' by the rules that the server uses to
sort account rows internally (see
Section 6.2.6, “Access Control, Stage 1: Connection Verification”).

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

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.

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.

Server Support for Proxy User Mapping

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:

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.

Proxy User System Variables

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:

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.

6.3.12 User Account Locking

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.

6.3.13 SQL-Based MySQL Account Activity Auditing

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

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

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

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

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

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

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:

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:

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.

6.4.1 Configuring MySQL to Use Encrypted Connections

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:

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.

The server performs certificate and key file autodiscovery. If
--ssl is enabled (possibly along
with --ssl-cipher) and other
--ssl-xxx options
are not given to configure encrypted connections explicitly, the
server attempts to enable support for encrypted connections
automatically at startup:

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

Client-Side Configuration for Encrypted Connections

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.

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.

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:

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.

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:

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

6.4.2 Command Options for Encrypted Connections

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

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-xxx options,
as indicated in the descriptions for those options.

The --ssl option in negated
form indicates that encryption should not
be used and overrides other
--ssl-xxx options.
Specify the option as --ssl=0
or a synonym
(--skip-ssl,
--disable-ssl).

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

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

6.4.3 Creating SSL and RSA Certificates and Keys

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.

6.4.3.1 Creating SSL and RSA Certificates and Keys using MySQL

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.

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.

Important

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.

Automatic SSL and RSA File Generation

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.

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.

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
_suffix
portion of the name is omitted.

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:

6.4.3.2 Creating SSL Certificates and Keys Using openssl

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.

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:

Example 1: Creating SSL Files from the Command Line on Unix

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.

Example 3: Creating SSL Files on Windows

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

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

Select the Advanced tab from
the System Properties menu that
appears, and click the Environment
Variables button.

Under System Variables, select
Path, then click the
Edit button. The Edit
System Variable 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:

6.4.3.3 Creating RSA Keys Using openssl

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.

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';

6.4.5 Building MySQL with Support for Encrypted Connections

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:

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:

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-xxx options to
enable encrypted connections to be used; see
Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.

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”

6.4.6 Encrypted Connection Protocols and Ciphers

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:

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:

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:

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.

Start your Windows SSH client. Set Host_Name =
yourmysqlserver_URL_or_IP.
Set
userid=your_userid
to log in to your server. This userid value
might not be the same as the user name of your MySQL account.

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

6.5.1.1 Native Pluggable Authentication

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.

6.5.1.2 SHA-256 Pluggable Authentication

To connect to the server using an account that authenticates
with the sha256_password plugin, you must
use either a TLS connection or an unencrypted connection that
supports password exchange using an RSA key pair, as described
later in this section. Either way, the
sha256_password plugin uses MySQL's
encryption 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:

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 by 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 system table
that use the SHA-256 plugin 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:

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 perform password exchange
with the server using an RSA key pair during the client
connection process, as described later.

The server exposes two system variables,
sha256_password_private_key_path
and
sha256_password_public_key_path,
that name the RSA private and public key-pair files. The
database administrator must set these variables at server
startup if the key files to be used 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.

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 a TLS connection is used and whether RSA
encryption is available:

If a TLS connection is used, the password is sent as
cleartext but cannot be snooped because the connection is
encrypted.

If a TLS 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 a TLS 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, to use
SHA-256 passwords, clients must use an
encrypted connection 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 password exchange
using an RSA key pair during the client connection process:

Otherwise, to name the key files explicitly, set the
system variables to the key file names in the server
option file. If the files are located in the server data
directory, you need not specify their full path names:

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 a TLS connection (in
which case RSA is not used) or an unencrypted connection that
encrypts the password using RSA. Assume for the following
discussion that an encrypted connection is not used.
Connecting to the server involves no special preparation on
the client side. For example:

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 is not encrypted 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:

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.

6.5.1.3 Client-Side Cleartext Pluggable Authentication

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

Note

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

6.5.1.4 No-Login Pluggable Authentication

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:

Installing No-Login Pluggable Authentication

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.

Using No-Login Pluggable Authentication

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:

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.

Note

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:

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

6.5.1.5 Socket Peer-Credential Pluggable Authentication

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.

Installing Socket Pluggable Authentication

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.

Using Socket Pluggable Authentication

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.

6.5.1.6 Test Pluggable Authentication

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.

Note

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:

Installing Test Pluggable Authentication

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.

Using Test Pluggable Authentication

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”):

6.5.2 The Connection-Control Plugins

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.

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

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:

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.

Note

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.

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:

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.

Connection Failure Assessment

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.

Note

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.

The INFORMATION_SCHEMACONNECTION_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.

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.

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.

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.

6.5.3 The Password Validation Plugin

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:

If the validate_password plugin is not
installed, the
validate_password_xxx
system variables are not available, passwords in statements are
not checked, and the
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.

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.

6.5.3.1 Password Validation Plugin Installation

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.

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:

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.

6.5.3.2 Password Validation Plugin Options and Variables

To control the activation of the
validate_password plugin, use this option:

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.

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.

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.

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.

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.

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_xxx
variables.

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

Warning

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.

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

Note

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:

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:

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.

6.5.4.2 Using the keyring_file File-Based Plugin

The keyring_file plugin is a keyring plugin
that stores keyring data in a file local to the server host.

Warning

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

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

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.

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:

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.

6.5.4.4 General-Purpose Keyring Key-Management Functions

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

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.

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:

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
@var_name and test the
variable value:

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:

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:

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:

Returns the key value for success, NULL
if the key does not exist, or NULL and
an error for failure.

Note

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.

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

6.5.4.6 Keyring System Variables

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”).

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_LAYOUTCMake 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_MYSQLKEYRINGDIRCMake option.

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:

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.

Important

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.