Table of Contents
When thinking about security within a MySQL installation, you should consider a wide range of possible topics and how they affect the security of your MySQL server and related applications:
General factors that affect security. These include choosing good passwords, not granting unnecessary privileges to users, ensuring application security by preventing SQL injections and data corruption, and others. See Section 6.1, “General Security Issues”.
Security of the installation itself. The data files, log files, and the all the application files of your installation should be protected to ensure that they are not readable or writable by unauthorized parties. For more information, see Section 2.10, “Postinstallation Setup and Testing”.
Access control and security within the database system itself, including the users and databases granted with access to the databases, views and stored programs in use within the database. For more information, see Section 6.2, “The MySQL Access Privilege System”, and Section 6.3, “MySQL User Account Management”.
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 6.2, “The MySQL Access Privilege System”.
For answers to some questions that are often asked about MySQL Server security issues, see Section B.9, “MySQL 5.5 FAQ: Security”.
Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes.
In discussing security, it is necessary to consider fully protecting the entire server host (not just the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here.
MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that users can attempt to perform. There is also support for SSL-encrypted connections between MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the same general ideas apply to almost all applications.
When running MySQL, follow these guidelines:
Do not ever give anyone (except MySQL
root
accounts) access to the
user
table in the mysql
database! This is critical.
Learn how the MySQL access privilege system works (see
Section 6.2, “The MySQL Access Privilege System”). Use the
GRANT
and
REVOKE
statements to control
access to MySQL. Do not grant more privileges than necessary.
Never grant privileges to all hosts.
Checklist:
Try mysql -u root
. If you are able to
connect successfully to the server without being asked for
a password, anyone can connect to your MySQL server as the
MySQL root
user with full privileges!
Review the MySQL installation instructions, paying
particular attention to the information about setting a
root
password. See
Section 2.10.2, “Securing the Initial MySQL Accounts”.
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 plaintext passwords in your database. If your
computer becomes compromised, the intruder can take the full
list of passwords and use them. Instead, use
SHA2()
,
SHA1()
,
MD5()
, or some other one-way
hashing function and store the hash value.
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 subsitute digits for the number words to obtain the phrase “4 score and 7 years ago”, yielding the password “4sa7ya” which is even more difficult to guess.
Invest in a firewall. This protects you from at least 50% of all types of exploits in any software. Put MySQL behind the firewall or in a demilitarized zone (DMZ).
Checklist:
Try to scan your ports from the Internet using a tool such
as nmap
. MySQL uses port 3306 by
default. This port should not be accessible from untrusted
hosts. As a simple way to check whether your MySQL port is
open, try the following command from some remote machine,
where server_host
is the host
name or IP address of the host on which your MySQL server
runs:
shell> telnet server_host
3306
If telnet hangs or the connection is refused, the port is blocked, which is how you want it to be. If you get a connection and some garbage characters, the port is open, and should be closed on your firewall or router, unless you really have a good reason to keep it open.
Applications that access MySQL should not trust any data entered by users, and should be written using proper defensive programming techniques. See Section 6.1.7, “Client Programming Security Guidelines”.
Do not transmit plain (unencrypted) data over the Internet. This information is accessible to everyone who has the time and ability to intercept it and use it for their own purposes. Instead, use an encrypted protocol such as SSL or SSH. MySQL supports internal SSL connections. Another technique is to use SSH port-forwarding to create an encrypted (and compressed) tunnel for the communication.
Learn to use the tcpdump and strings utilities. In most cases, you can check whether MySQL data streams are unencrypted by issuing a command like the following:
shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings
This works under Linux and should work with small modifications under other systems.
If you do not see plaintext data, this does not always mean that the information actually is encrypted. If you need high security, consult with a security expert.
Passwords occur in several contexts within MySQL. The following sections provide guidelines that enable administrators and end users to keep these passwords secure and avoid exposing them. There is also a discussion of how MySQL uses password hashing internally.
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 location of the plugin directory can replace plugins and
modify the capabilities provided by plugins, including
authentication plugins.
Passwords can appear as plain text in SQL statements such as
CREATE USER
,
GRANT
, and
SET PASSWORD
, or statements that
invoke the PASSWORD()
function.
If these statements are logged by the MySQL server, the
passwords become available to anyone with access to the logs.
This applies to the general query log, the slow query log, and
the binary log (see Section 5.2, “MySQL Server Logs”). To guard
against unwarranted exposure to log files, they should be
located in a directory that restricts access to only the server
and the database administrator. If you log to tables in the
mysql
database, access to the tables should
never be granted to any nonadministrative accounts.
Replication slaves store the password for the replication master
in the master.info
file. Retrict this file
to be accessible only to the database administrator.
Database backups that include tables or log files containing passwords should be protected using a restricted access mode.
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
-p
or
your_pass
--password=
option on the command line. For example:
your_pass
shell> mysql -u francis -pfrank db_name
This is convenient but insecure, because your password becomes visible to system status programs such as ps that may be invoked by other users to display command lines. MySQL clients typically overwrite the command-line password argument with zeros during their initialization sequence. However, there is still a brief interval during which the value is visible. Also, on some systems this overwriting strategy is ineffective and the password remains visible to ps. (SystemV Unix systems and perhaps others are subject to this problem.)
If your operating environment is set up to display your current command in the title bar of your terminal window, the password remains visible as long as the command is running, even if the command has scrolled out of view in the window content area.
Use the -p
or --password
option on the command line with no password value specified.
In this case, the client program solicits the password
interactively:
shell> mysql -u francis -p db_name
Enter password: ********
The “*
” characters indicate
where you enter your password. The password is not displayed
as you enter it.
It is more secure to enter your password this way than to specify it on the command line because it is not visible to other users. However, this method of entering a password is suitable only for programs that you run interactively. If you want to invoke a client from a script that runs noninteractively, there is no opportunity to enter the password from the keyboard. On some systems, you may even find that the first line of your script is read and interpreted (incorrectly) as your password.
Store your password in an option file. For example, on Unix,
you can list your password in the
[client]
section of the
.my.cnf
file in your home directory:
[client] password=your_pass
To keep the password safe, the file should not be accessible
to anyone but yourself. To ensure this, set the file access
mode to 400
or 600
.
For example:
shell> chmod 600 .my.cnf
To name from the command line a specific option file
containing the password, use the
--defaults-file=
option, where file_name
file_name
is the full
path name to the file. For example:
shell> mysql --defaults-file=/home/francis/mysql-opts
Section 4.2.3.3, “Using Option Files”, discusses option files in more detail.
Store your password in the MYSQL_PWD
environment variable. See
Section 2.12, “Environment Variables”.
This method of specifying your MySQL password must be
considered extremely insecure and
should not be used. Some versions of ps
include an option to display the environment of running
processes. 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 History File”). By default, this file is
named .mysql_history
and is created in your
home directory. Passwords can appear as plain text in SQL
statements such as CREATE USER
,
GRANT
, and
SET PASSWORD
, 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.
MySQL lists user accounts in the user
table
of the mysql
database. Each MySQL account is
assigned a password, although what is stored in the
Password
column of the
user
table is not the plaintext version of
the password, but a hash value computed from it. Password hash
values are computed by the
PASSWORD()
function.
MySQL uses passwords in two phases of client/server communication:
When a client attempts to connect to the server, there is an
initial authentication step in which the client must present
a password that has a hash value matching the hash value
stored in the user
table for the account
that the client wants to use.
After the client connects, it can (if it has sufficient
privileges) set or change the password hashes for accounts
listed in the user
table. The client can
do this by using the
PASSWORD()
function to
generate a password hash, or by using the
GRANT
or
SET PASSWORD
statements.
In other words, the server uses hash values
during authentication when a client first attempts to connect.
The server generates hash values if a
connected client invokes the
PASSWORD()
function or uses a
GRANT
or SET
PASSWORD
statement to set or change a password.
The password hashing mechanism was updated in MySQL 4.1 to provide better security and to reduce the risk of passwords being intercepted. However, this new mechanism is understood only by MySQL 4.1 (and newer) servers and clients, which can result in some compatibility problems. A 4.1 or newer client can connect to a pre-4.1 server, because the client understands both the old and new password hashing mechanisms. However, a pre-4.1 client that attempts to connect to a 4.1 or newer server may run into difficulties. For example, a 3.23 mysql client that attempts to connect to a 5.5 server may fail with the following error message:
shell> mysql -h localhost -u root
Client does not support authentication protocol requested
by server; consider upgrading MySQL client
Another common example of this phenomenon occurs for attempts to
use the older PHP mysql
extension after
upgrading to MySQL 4.1 or newer. (See
Section 22.9.10, “Common Problems with MySQL and PHP”.)
The following discussion describes the differences between the
old and new password mechanisms, and what you should do if you
upgrade your server but need to maintain backward compatibility
with pre-4.1 clients. (Permitting connections by old clients is
not recommended and should be avoided if possible.) Additional
information can be found in Section C.5.2.4, “Client does not support authentication protocol
”. This
information is of particular importance to PHP programmers
migrating MySQL databases from version 4.0 or lower to version
4.1 or higher.
Prior to MySQL 4.1, password hashes computed by the
PASSWORD()
function are 16 bytes
long. Such hashes look like this:
mysql> SELECT PASSWORD('mypass');
+--------------------+
| PASSWORD('mypass') |
+--------------------+
| 6f8c114b58f2ce9e |
+--------------------+
The Password
column of the
user
table (in which these hashes are stored)
also is 16 bytes long before MySQL 4.1.
As of MySQL 4.1, the PASSWORD()
function has been modified to produce a longer 41-byte hash
value:
mysql> SELECT PASSWORD('mypass');
+-------------------------------------------+
| PASSWORD('mypass') |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+
Accordingly, the Password
column in the
user
table also must be 41 bytes long to
store these values:
If you perform a new installation of MySQL 5.5,
the Password
column is made 41 bytes long
automatically.
Upgrading from MySQL 4.1 (4.1.1 or later in the 4.1 series) to MySQL 5.5 should not give rise to any issues in this regard because both versions use the same password hashing mechanism. If you wish to upgrade an older release of MySQL to version 5.5, you should upgrade to version 4.1 first, then upgrade the 4.1 installation to 5.5.
A widened Password
column can store password
hashes in both the old and new formats. The format of any given
password hash value can be determined two ways:
The obvious difference is the length (16 bytes versus 41 bytes).
A second difference is that password hashes in the new
format always begin with a
“*
” character, whereas
passwords in the old format never do.
The longer password hash format has better cryptographic properties, and client authentication based on long hashes is more secure than that based on the older short hashes.
The differences between short and long password hashes are relevant both for how the server uses passwords during authentication and for how it generates password hashes for connected clients that perform password-changing operations.
The way in which the server uses password hashes during
authentication is affected by the width of the
Password
column:
If the column is short, only short-hash authentication is used.
If the column is long, it can hold either short or long hashes, and the server can use either format:
Pre-4.1 clients can connect, although because they know only about the old hashing mechanism, they can authenticate only using accounts that have short hashes.
4.1 and later clients can authenticate using accounts that have short or long hashes.
Even for short-hash accounts, the authentication process is actually a bit more secure for 4.1 and later clients than for older clients. In terms of security, the gradient from least to most secure is:
Pre-4.1 client authenticating with short password hash
4.1 or later client authenticating with short password hash
4.1 or later client authenticating with long password hash
The way in which the server generates password hashes for
connected clients is affected by the width of the
Password
column and by the
--old-passwords
option. A 4.1 or
later server generates long hashes only if certain conditions
are met: The Password
column must be wide
enough to hold long values and the
--old-passwords
option must not
be given. These conditions apply as follows:
The Password
column must be wide enough
to hold long hashes (41 bytes). If the column has not been
updated and still has the pre-4.1 width of 16 bytes, the
server notices that long hashes cannot fit into it and
generates only short hashes when a client performs
password-changing operations using
PASSWORD()
,
GRANT
, or
SET PASSWORD
. This is the
behavior that occurs if you have upgraded to 4.1 but have
not yet run the mysql_upgrade program to
widen the Password
column.
If the Password
column is wide, it can
store either short or long password hashes. In this case,
PASSWORD()
,
GRANT
, and
SET PASSWORD
generate long
hashes unless the server was started with the
--old-passwords
option. That
option forces the server to generate short password hashes
instead.
The purpose of the
--old-passwords
option is to
enable you to maintain backward compatibility with pre-4.1
clients under circumstances where the server would otherwise
generate long password hashes. The option does not affect
authentication (4.1 and later clients can still use accounts
that have long password hashes), but it does prevent creation of
a long password hash in the user
table as the
result of a password-changing operation. Were that to occur, the
account no longer could be used by pre-4.1 clients. Without the
--old-passwords
option, the
following undesirable scenario is possible:
An old client connects to an account that has a short password hash.
The client changes its own password. Without
--old-passwords
, this results
in the account having a long password hash.
The next time the old client attempts to connect to the account, it cannot, because the account has a long password hash that requires the new hashing mechanism during authentication. (Once an account has a long password hash in the user table, only 4.1 and later clients can authenticate for it, because pre-4.1 clients do not understand long hashes.)
This scenario illustrates that, if you must support older
pre-4.1 clients, it is dangerous to run a 4.1 or newer server
without using the --old-passwords
option. By running the server with
--old-passwords
,
password-changing operations do not generate long password
hashes and thus do not cause accounts to become inaccessible to
older clients. (Those clients cannot inadvertently lock
themselves out by changing their password and ending up with a
long password hash.)
The downside of the
--old-passwords
option is that
any passwords you create or change use short hashes, even for
4.1 clients. Thus, you lose the additional security provided by
long password hashes. If you want to create an account that has
a long hash (for example, for use by 4.1 clients), you must do
so while running the server without
--old-passwords
.
The following scenarios are possible for running a 4.1 or later server:
Scenario 1: Short
Password
column in user table:
Only short hashes can be stored in the
Password
column.
The server uses only short hashes during client authentication.
For connected clients, password hash-generating operations
involving PASSWORD()
,
GRANT
, or
SET PASSWORD
use short hashes
exclusively. Any change to an account's password results in
that account having a short password hash.
The --old-passwords
option
can be used but is superfluous because with a short
Password
column, the server generates
only short password hashes anyway.
Scenario 2: Long
Password
column; server not started with
--old-passwords
option:
Short or long hashes can be stored in the
Password
column.
4.1 and later clients can authenticate using accounts that have short or long hashes.
Pre-4.1 clients can authenticate only using accounts that have short hashes.
For connected clients, password hash-generating operations
involving PASSWORD()
,
GRANT
, or
SET PASSWORD
use long hashes
exclusively. A change to an account's password results in
that account having a long password hash.
As indicated earlier, a danger in this scenario is that it is
possible for accounts that have a short password hash to become
inaccessible to pre-4.1 clients. A change to such an account's
password made using GRANT
,
PASSWORD()
, or
SET PASSWORD
results in the
account being given a long password hash. From that point on, no
pre-4.1 client can authenticate to that account until the client
upgrades to 4.1.
To deal with this problem, you can change a password in a
special way. For example, normally you use
SET PASSWORD
as follows to change
an account password:
SET PASSWORD FOR 'some_user
'@'some_host
' = PASSWORD('mypass');
To change the password but create a short hash, use the
OLD_PASSWORD()
function instead:
SET PASSWORD FOR 'some_user
'@'some_host
' = OLD_PASSWORD('mypass');
OLD_PASSWORD()
is useful for
situations in which you explicitly want to generate a short
hash.
Scenario 3: Long
Password
column; 4.1 or newer server started
with --old-passwords
option:
Short or long hashes can be stored in the
Password
column.
4.1 and later clients can authenticate for accounts that
have short or long hashes (but note that it is possible to
create long hashes only when the server is started without
--old-passwords
).
Pre-4.1 clients can authenticate only for accounts that have short hashes.
For connected clients, password hash-generating operations
involving PASSWORD()
,
GRANT
, or
SET PASSWORD
use short hashes
exclusively. Any change to an account's password results in
that account having a short password hash.
In this scenario, you cannot create accounts that have long
password hashes, because the
--old-passwords
option prevents
generation of long hashes. Also, if you create an account with a
long hash before using the
--old-passwords
option, changing
the account's password while
--old-passwords
is in effect
results in the account being given a short password, causing it
to lose the security benefits of a longer hash.
The disadvantages for these scenarios may be summarized as follows:
In scenario 1, you cannot take advantage of longer hashes that provide more secure authentication.
In scenario 2, accounts with short hashes become inaccessible to
pre-4.1 clients if you change their passwords without explicitly
using OLD_PASSWORD()
.
In scenario 3, --old-passwords
prevents accounts with short hashes from becoming inaccessible,
but password-changing operations cause accounts with long hashes
to revert to short hashes, and you cannot change them back to
long hashes while --old-passwords
is in effect.
An upgrade to MySQL version 4.1 or later can cause compatibility
issues for applications that use
PASSWORD()
to generate passwords
for their own purposes. Applications really should not do this,
because PASSWORD()
should be used
only to manage passwords for MySQL accounts. But some
applications use PASSWORD()
for
their own purposes anyway.
If you upgrade to 4.1 or later from a pre-4.1 version of MySQL
and run the server under conditions where it generates long
password hashes, an application using
PASSWORD()
for its own passwords
breaks. The recommended course of action in such cases is to
modify the application to use another function, such as
SHA2()
,
SHA1()
, or
MD5()
, to produce hashed values.
If that is not possible, you can use the
OLD_PASSWORD()
function, which is
provided for generate short hashes in the old format. However,
you should note that
OLD_PASSWORD()
may one day no
longer be supported.
If the server is running under circumstances where it generates
short hashes, OLD_PASSWORD()
is
available but is equivalent to
PASSWORD()
.
PHP programmers migrating their MySQL databases from version 4.0 or lower to version 4.1 or higher should see Section 22.9, “MySQL PHP API”.
When you connect to a MySQL server, you should use a password. The password is not transmitted in clear text over the connection. Password handling during the client connection sequence was upgraded in MySQL 4.1.1 to be very secure. If you are still using pre-4.1.1-style passwords, the encryption algorithm is not as strong as the newer algorithm. With some effort, a clever attacker who can sniff the traffic between the client and the server can crack the password. (See Section 6.1.2.3, “Password Hashing in MySQL”, for a discussion of the different password handling methods.)
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.3.8, “Using SSL for Secure 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 commercial SSH client at http://www.ssh.com/.
To make a MySQL system secure, you should strongly consider the following suggestions:
Require all MySQL accounts to have a password. A client
program does not necessarily know the identity of the person
running it. It is common for client/server applications that
the user can specify any user name to the client program. For
example, anyone can use the mysql program
to connect as any other person simply by invoking it as
mysql -u
if
other_user
db_name
other_user
has no password. If all
accounts have a password, connecting using another user's
account becomes much more difficult.
For a discussion of methods for setting passwords, see Section 6.3.5, “Assigning Account Passwords”.
Never run the MySQL server as the Unix root
user. This is extremely dangerous, because any user with the
FILE
privilege is able to cause
the server to create files as root
(for
example, ~root/.bashrc
). To prevent this,
mysqld refuses to run as
root
unless that is specified explicitly
using the --user=root
option.
mysqld can (and should) be run as an
ordinary, unprivileged user instead. You can create a separate
Unix account named mysql
to make everything
even more secure. Use this account only for administering
MySQL. To start mysqld as a different Unix
user, add a user
option that specifies the
user name in the [mysqld]
group of the
my.cnf
option file where you specify
server options. For example:
[mysqld] user=mysql
This causes the server to start as the designated user whether you start it manually or by using mysqld_safe or mysql.server. For more details, see Section 6.1.5, “How to Run MySQL as a Normal User”.
Running mysqld as a Unix user other than
root
does not mean that you need to change
the root
user name in the
user
table. User names for MySQL
accounts have nothing to do with user names for Unix
accounts.
Do not permit the use of symlinks to tables. (This capability
can be disabled with the
--skip-symbolic-links
option.) This is especially important if you run
mysqld as root
, because
anyone that has write access to the server's data directory
then could delete any file in the system! See
Section 8.11.3.1.2, “Using Symbolic Links for Tables on Unix”.
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.
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 SUPER
privilege, so that a MySQL root
user can
log in and check server activity even if all normal
connections are in use.
The SUPER
privilege can be used
to terminate client connections, change server operation by
changing the value of system variables, and control
replication servers.
Do not 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. To make
this 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
.
Stored programs and views should be written using the security guidelines discussed in Section 19.6, “Access Control for Stored Programs and Views”.
If you do not trust your DNS, you should use IP addresses rather than host names in the grant tables. In any case, you should be very careful about creating grant table entries using host name values that contain wildcards.
If you want to restrict the number of connections permitted to
a single account, you can do so by setting the
max_user_connections
variable
in mysqld. The
GRANT
statement also supports
resource control options for limiting the extent of server use
permitted to an account. See Section 13.7.1.3, “GRANT
Syntax”.
If the plugin directory is writable by the server, it may be
possible for a user to write executable code to a file in the
directory using SELECT
... INTO DUMPFILE
. This can be prevented by making
plugin_dir
read only to the
server or by setting
--secure-file-priv
to a
directory where SELECT
writes
can be made safely.
The following mysqld options affect security:
Table 6.1. Security Option/Variable Summary
Name | Cmd-Line | Option file | System Var | Status Var | Var Scope | Dynamic |
---|---|---|---|---|---|---|
allow-suspicious-udfs | Yes | Yes | ||||
automatic_sp_privileges | Yes | Global | Yes | |||
chroot | Yes | Yes | ||||
des-key-file | Yes | Yes | ||||
local-infile | Yes | Yes | Global | Yes | ||
- Variable: local_infile | Yes | Global | Yes | |||
old-passwords | Yes | Yes | Both | Yes | ||
- Variable: old_passwords | Yes | Both | Yes | |||
safe-show-database | Yes | Yes | Yes | Global | Yes | |
safe-user-create | Yes | Yes | ||||
secure-auth | Yes | Yes | Global | Yes | ||
- Variable: secure_auth | Yes | Global | Yes | |||
secure-file-priv | Yes | Yes | Global | No | ||
- Variable: secure_file_priv | Yes | Global | No | |||
skip-grant-tables | Yes | Yes | ||||
skip-name-resolve | Yes | Yes | Global | No | ||
- Variable: skip_name_resolve | Yes | Global | No | |||
skip-networking | Yes | Yes | Global | No | ||
- Variable: skip_networking | Yes | Global | No | |||
skip-show-database | Yes | Yes | Global | No | ||
- Variable: skip_show_database | Yes | Global | No |
This option controls whether user-defined functions that have
only an xxx
symbol for the main function
can be loaded. By default, the option is off and only UDFs
that have at least one auxiliary symbol can be loaded; this
prevents attempts at loading functions from shared object
files other than those containing legitimate UDFs. See
Section 23.3.2.6, “User-Defined Function Security Precautions”.
If you start the server with
--local-infile=0
, clients
cannot use LOCAL
in
LOAD DATA
statements. See
Section 6.1.6, “Security Issues with LOAD
DATA LOCAL
”.
Force the server to generate short (pre-4.1) password hashes for new passwords. This is useful for compatibility when the server must support older client programs. See Section 6.1.2.3, “Password Hashing in MySQL”.
If this option is enabled, a user cannot create new MySQL
users by using the GRANT
statement unless the user has the
INSERT
privilege for the
mysql.user
table or any column in the
table. If you want a user to have the ability to create new
users that have those privileges that the user has the right
to grant, you should grant the user the following privilege:
GRANT INSERT(user) ON mysql.user TO 'user_name
'@'host_name
';
This ensures that the user cannot change any privilege columns
directly, but has to use the
GRANT
statement to give
privileges to other users.
Disallow authentication for accounts that have old (pre-4.1) passwords.
The mysql client also has a
--secure-auth
option, which
prevents connections to a server if the server requires a
password in old format for the client account.
This option limits the effect of the
LOAD_FILE()
function and the
LOAD DATA
and
SELECT ... INTO
OUTFILE
statements to work only with files in the
specified directory.
This option causes the server to start without using the
privilege system at all, which gives anyone with access to the
server unrestricted access to all
databases. You can cause a running server to start
using the grant tables again by executing mysqladmin
flush-privileges or mysqladmin
reload command from a system shell, or by issuing a
MySQL FLUSH
PRIVILEGES
statement after connecting to the server.
This option also suppresses loading of plugins that were
installed with the INSTALL
PLUGIN
statement, user-defined functions (UDFs), and
scheduled events. To cause plugins to be loaded anyway, use
the --plugin-load
option.
--skip-grant-tables
is
unavailable if MySQL was configured with the
DISABLE_GRANT_OPTIONS
compiler
flag. See Section 2.9.4, “MySQL Source-Configuration Options”.
Host names are not resolved. All Host
column values in the grant tables must be IP addresses or
localhost
.
Do not permit TCP/IP connections over the network. All connections to mysqld must be made using Unix socket files.
With this option, the SHOW
DATABASES
statement is permitted only to users who
have the SHOW DATABASES
privilege, and the statement displays all database names.
Without this option, SHOW
DATABASES
is permitted to all users, but displays
each database name only if the user has the
SHOW DATABASES
privilege or
some privilege for the database. Note that any global
privilege is a privilege for the database.
Options that begin with --ssl
specify whether to permit clients to connect using SSL and
indicate where to find SSL keys and certificates. See
Section 6.3.8.3, “SSL Command Options”.
On Windows, you can run the server as a Windows service using a normal user account.
On Unix, the MySQL server mysqld can be started
and run by any user. However, you should avoid running the server
as the Unix root
user for security reasons. To
change mysqld to run as a normal unprivileged
Unix user user_name
, you must do the
following:
Stop the server if it is running (use mysqladmin shutdown).
Change the database directories and files so that
user_name
has privileges to read
and write files in them (you might need to do this as the Unix
root
user):
shell> chown -R user_name
/path/to/mysql/datadir
If you do not do this, the server will not be able to access
databases or tables when it runs as
user_name
.
If directories or files within the MySQL data directory are
symbolic links, chown -R
might not follow
symbolic links for you. If it does not, you will also need to
follow those links and change the directories and files they
point to.
Start the server as user user_name
.
Another alternative is to start mysqld as
the Unix root
user and use the
--user=
option. mysqld starts up, then switches to
run as the Unix user user_name
user_name
before accepting any connections.
To start the server as the given user automatically at system
startup time, specify the user name by adding a
user
option to the
[mysqld]
group of the
/etc/my.cnf
option file or the
my.cnf
option file in the server's data
directory. For example:
[mysqld]
user=user_name
If your Unix machine itself is not secured, you should assign
passwords to the MySQL root
accounts in the
grant tables. Otherwise, any user with a login account on that
machine can run the mysql client with a
--user=root
option and perform any
operation. (It is a good idea to assign passwords to MySQL
accounts in any case, but especially so when other login accounts
exist on the server host.) See Section 2.10, “Postinstallation Setup and Testing”.
The LOAD DATA
statement can load a
file that is located on the server host, or it can load a file
that is located on the client host when the
LOCAL
keyword is specified.
There are two potential security issues with supporting the
LOCAL
version of LOAD
DATA
statements:
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.
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 command
against the SQL server). In this environment, the client with
respect to the MySQL server actually is the Web server, not
the remote program being run by the user who connects to the
Web server.
To deal with these problems, we changed how
LOAD DATA
LOCAL
is handled as of MySQL 3.23.49 and MySQL 4.0.2
(4.0.13 on Windows):
By default, all MySQL clients and libraries in binary
distributions are compiled with the
-DENABLED_LOCAL_INFILE=1
option,
to be compatible with MySQL 3.23.48 and before.
If you build MySQL from source but do not invoke
CMake with the
-DENABLED_LOCAL_INFILE=1
option,
LOAD DATA
LOCAL
cannot be used by any client unless it is
written explicitly to invoke
mysql_options(...
MYSQL_OPT_LOCAL_INFILE, 0)
. See
Section 22.8.3.49, “mysql_options()
”.
You can disable all
LOAD DATA
LOCAL
statements from the server side by starting
mysqld with the
--local-infile=0
option.
For the mysql command-line client, enable
LOAD DATA
LOCAL
by specifying the
--local-infile[=1]
option, or
disable it with the
--local-infile=0
option. For
mysqlimport, local data file loading is off
by default; enable it with the
--local
or
-L
option. In any case, successful use of a
local load operation requires that the server permits it.
If you use LOAD
DATA LOCAL
in Perl scripts or other programs that
read the [client]
group from option files,
you can add the local-infile=1
option to
that group. However, to keep this from causing problems for
programs that do not understand
local-infile
, specify it using the
loose-
prefix:
[client] loose-local-infile=1
If LOAD DATA
LOCAL
is disabled, either in the server or the
client, a client that attempts to issue such a statement
receives the following error message:
ERROR 1148: The used command is not allowed with this MySQL version
Applications that access MySQL should not trust any data entered
by users, who can try to trick your code by entering special or
escaped character sequences in Web forms, URLs, or whatever
application you have built. Be sure that your application remains
secure if a user enters something like “; DROP
DATABASE mysql;
”. This is an extreme example, but
large security leaks and data loss might occur as a result of
hackers using similar techniques, if you do not prepare for them.
A common mistake is to protect only string data values. Remember
to check numeric data as well. If an application generates a query
such as SELECT * FROM table WHERE ID=234
when a
user enters the value 234
, the user can enter
the value 234 OR 1=1
to cause the application
to generate the query SELECT * FROM table WHERE ID=234 OR
1=1
. As a result, the server retrieves every row in the
table. This exposes every row and causes excessive server load.
The simplest way to protect from this type of attack is to use
single quotation marks around the numeric constants:
SELECT * FROM table WHERE ID='234'
. If the user
enters extra information, it all becomes part of the string. In a
numeric context, MySQL automatically converts this string to a
number and strips any trailing nonnumeric characters from it.
Sometimes people think that if a database contains only publicly available data, it need not be protected. This is incorrect. Even if it is permissible to display any row in the database, you should still protect against denial of service attacks (for example, those that are based on the technique in the preceding paragraph that causes the server to waste resources). Otherwise, your server becomes unresponsive to legitimate users.
Checklist:
Enable strict SQL mode to tell the server to be more restrictive of what data values it accepts. See Section 5.1.6, “Server SQL Modes”.
Try to enter single and double quotation marks
(“'
” and
“"
”) in all of your Web forms.
If you get any kind of MySQL error, investigate the problem
right away.
Try to modify dynamic URLs by adding %22
(“"
”), %23
(“#
”), and
%27
(“'
”)
to them.
Try to modify data types in dynamic URLs from numeric to character types using the characters shown in the previous examples. Your application should be safe against these and similar attacks.
Try to enter characters, spaces, and special symbols rather than numbers in numeric fields. Your application should remove them before passing them to MySQL or else generate an error. Passing unchecked values to MySQL is very dangerous!
Check the size of data before passing it to MySQL.
Have your application connect to the database using a user name different from the one you use for administrative purposes. Do not give your applications any access privileges they do not need.
Many application programming interfaces provide a means of escaping special characters in data values. Properly used, this prevents application users from entering values that cause the application to generate statements that have a different effect than you intend:
MySQL C API: Use the
mysql_real_escape_string()
API
call.
MySQL++: Use the escape
and
quote
modifiers for query streams.
PHP: Use either the mysqli
or
pdo_mysql
extensions, and not the older
ext/mysql
extension. The preferred API's
support the improved MySQL authentication protocol and
passwords, as well as prepared statements with placeholders.
See also Section 22.9.1.3, “Choosing an API”.
If the older ext/mysql
extension must be
used, then for escaping use the
mysql_real_escape_string()
function and not
mysql_escape_string()
or
addslashes()
because only
mysql_real_escape_string()
is
character set-aware; the other functions can be
“bypassed” when using (invalid) multi-byte
character sets.
Perl DBI: Use placeholders or the quote()
method.
Ruby DBI: Use placeholders or the quote()
method.
Java JDBC: Use a PreparedStatement
object
and placeholders.
Other programming interfaces might have similar capabilities.
The primary function of the MySQL privilege system is to
authenticate a user who connects from a given host and to associate
that user with privileges on a database such as
SELECT
,
INSERT
,
UPDATE
, and
DELETE
. Additional functionality
includes the ability to have anonymous users and to grant privileges
for MySQL-specific functions such as
LOAD DATA
INFILE
and administrative operations.
There are some things that you cannot do with the MySQL privilege system:
You cannot explicitly specify that a given user should be denied access. That is, you cannot explicitly match a user and then refuse the connection.
You cannot specify that a user has privileges to create or drop tables in a database but not to create or drop the database itself.
A password applies globally to an account. You cannot associate a password with a specific object such as a database, table, or routine.
The user interface to the MySQL privilege system consists of SQL
statements such as CREATE USER
,
GRANT
, and
REVOKE
. See
Section 13.7.1, “Account Management Statements”.
Internally, the server stores privilege information in the grant
tables of the mysql
database (that is, in the
database named mysql
). The MySQL server reads the
contents of these tables into memory when it starts and bases
access-control decisions on the in-memory copies of the grant
tables.
The MySQL privilege system ensures that all users may perform only the operations permitted to them. As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify. When you issue requests after connecting, the system grants privileges according to your identity and what you want to do.
MySQL considers both your host name and user name in identifying you
because there is no reason to assume that a given user name belongs
to the same person on all hosts. For example, the user
joe
who connects from
office.example.com
need not be the same person as
the user joe
who connects from
home.example.com
. MySQL handles this by enabling
you to distinguish users on different hosts that happen to have the
same name: You can grant one set of privileges for connections by
joe
from office.example.com
,
and a different set of privileges for connections by
joe
from home.example.com
. To
see what privileges a given account has, use the
SHOW GRANTS
statement. For example:
SHOW GRANTS FOR 'joe'@'office.example.com'; SHOW GRANTS FOR 'joe'@'home.example.com';
MySQL access control involves two stages when you run a client program that connects to the server:
Stage 1: The server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password.
Stage 2: Assuming that you can
connect, the server checks each statement you issue to determine
whether you have sufficient privileges to perform it. For example,
if you try to select rows from a table in a database or drop a table
from the database, the server verifies that you have the
SELECT
privilege for the table or the
DROP
privilege for the database.
For a more detailed description of what happens during each stage, see Section 6.2.4, “Access Control, Stage 1: Connection Verification”, and Section 6.2.5, “Access Control, Stage 2: Request Verification”.
If your privileges are changed (either by yourself or someone else) while you are connected, those changes do not necessarily take effect immediately for the next statement that you issue. For details about the conditions under which the server reloads the grant tables, see Section 6.2.6, “When Privilege Changes Take Effect”.
For general security-related advice, see Section 6.1, “General Security Issues”. For help in diagnosing privilege-related problems, see Section 6.2.7, “Causes of Access-Denied Errors”.
MySQL provides privileges that apply in different contexts 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).
Information about account privileges is stored in the
user
, db
,
host
, tables_priv
,
columns_priv
, and procs_priv
tables in the mysql
database (see
Section 6.2.2, “Privilege System 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.6, “When Privilege Changes Take Effect”. Access-control decisions are
based on the in-memory copies of the grant tables.
Some releases of MySQL introduce changes to the structure of the grant tables to add new access privileges or features. Whenever you update to a new version of MySQL, you should update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 4.4.7, “mysql_upgrade — Check Tables for MySQL Upgrade”.
The following table shows the privilege names used at the SQL
level in the GRANT
and
REVOKE
statements, along with the
column name associated with each privilege in the grant tables and
the context in which the privilege applies.
Table 6.2. Permissible Privileges for GRANT
and
REVOKE
Privilege | Column | Context |
---|---|---|
CREATE | Create_priv | databases, tables, or indexes |
DROP | Drop_priv | databases, tables, or views |
GRANT OPTION | Grant_priv | databases, tables, or stored routines |
LOCK TABLES | Lock_tables_priv | databases |
REFERENCES | References_priv | databases or tables |
EVENT | Event_priv | databases |
ALTER | Alter_priv | tables |
DELETE | Delete_priv | tables |
INDEX | Index_priv | tables |
INSERT | Insert_priv | tables or columns |
SELECT | Select_priv | tables or columns |
UPDATE | Update_priv | tables or columns |
CREATE TEMPORARY TABLES | Create_tmp_table_priv | tables |
TRIGGER | Trigger_priv | tables |
CREATE VIEW | Create_view_priv | views |
SHOW VIEW | Show_view_priv | views |
ALTER ROUTINE | Alter_routine_priv | stored routines |
CREATE ROUTINE | Create_routine_priv | stored routines |
EXECUTE | Execute_priv | stored routines |
FILE | File_priv | file access on server host |
CREATE TABLESPACE | Create_tablespace_priv | server administration |
CREATE USER | Create_user_priv | server administration |
PROCESS | Process_priv | server administration |
PROXY | see proxies_priv table | server administration |
RELOAD | Reload_priv | server administration |
REPLICATION CLIENT | Repl_client_priv | server administration |
REPLICATION SLAVE | Repl_slave_priv | server administration |
SHOW DATABASES | Show_db_priv | server administration |
SHUTDOWN | Shutdown_priv | server administration |
SUPER | Super_priv | server administration |
ALL [PRIVILEGES] | server administration | |
USAGE | server administration |
The following list provides a general description of each privilege available in MySQL. Particular SQL statements might have more specific privilege requirements than indicated here. If so, the description for the statement in question provides the details.
The ALL
or
ALL PRIVILEGES
privilege specifier is shorthand. It stands for “all
privileges available at a given privilege level”
(except GRANT OPTION
). For
example, granting ALL
at the
global or table level grants all global privileges or all
table-level privileges.
The ALTER
privilege enables use
of ALTER TABLE
to change the
structure of tables. ALTER
TABLE
also requires the
CREATE
and
INSERT
privileges. Renaming a
table requires ALTER
and
DROP
on the old table,
ALTER
,
CREATE
, and
INSERT
on the new table.
The ALTER ROUTINE
privilege is
needed to alter or drop stored routines (procedures and
functions).
The CREATE
privilege enables
creation of new databases and tables.
The CREATE ROUTINE
privilege is
needed to create stored routines (procedures and functions).
The CREATE TABLESPACE
privilege
is needed to create, alter, or drop tablespaces and log file
groups.
The CREATE TEMPORARY TABLES
privilege enables the creation of temporary tables using the
CREATE TEMPORARY
TABLE
statement.
However, other operations on a temporary table, such as
INSERT
,
UPDATE
, or
SELECT
, require additional
privileges for those operations for the database containing
the temporary table, or for the nontemporary table of the same
name.
To keep privileges for temporary and nontemporary tables
separate, a common workaround for this situation is to create
a database dedicated to the use of temporary tables. Then for
that database, a user can be granted the
CREATE TEMPORARY TABLES
privilege, along with any other privileges required for
temporary table operations done by that user.
The CREATE USER
privilege
enables use of CREATE USER
,
DROP USER
,
RENAME USER
, and
REVOKE ALL
PRIVILEGES
.
The CREATE VIEW
privilege
enables use of CREATE VIEW
.
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. As a security measure, the server
will not overwrite existing files.
The GRANT OPTION
privilege
enables you to give to other users or remove from other users
those privileges that you yourself possess.
The INDEX
privilege enables you
to create or drop (remove) indexes.
INDEX
applies to existing
tables. If you have the CREATE
privilege for a table, you can include index definitions in
the CREATE TABLE
statement.
The INSERT
privilege enables
rows to be inserted into tables in a database.
INSERT
is also required for the
ANALYZE TABLE
,
OPTIMIZE TABLE
, and
REPAIR TABLE
table-maintenance
statements.
The LOCK TABLES
privilege
enables the use of explicit LOCK
TABLES
statements to lock tables for which you have
the SELECT
privilege. This
includes the use of write locks, which prevents other sessions
from reading the locked table.
The PROCESS
privilege pertains
to display of information about the threads executing within
the server (that is, information about the statements being
executed by sessions). The privilege enables use of
SHOW PROCESSLIST
or
mysqladmin processlist to see threads
belonging to other accounts; you can always see your own
threads.
The PROXY
privilege enables a
user to impersonate or become known as another user. See
Section 6.3.7, “Proxy Users”. This privilege was added in
MySQL 5.5.7.
The REFERENCES
privilege
currently is unused.
The RELOAD
privilege enables
use of the FLUSH
statement. It
also enables mysqladmin commands that are
equivalent to FLUSH
operations:
flush-hosts
, flush-logs
,
flush-privileges
,
flush-status
,
flush-tables
,
flush-threads
, refresh
,
and reload
.
The reload
command tells the server to
reload the grant tables into memory.
flush-privileges
is a synonym for
reload
. The refresh
command closes and reopens the log files and flushes all
tables. The other
flush-
commands perform functions similar to
xxx
refresh
, but are more specific and may be
preferable in some instances. For example, if you want to
flush just the log files, flush-logs
is a
better choice than refresh
.
The REPLICATION CLIENT
privilege enables the use of SHOW MASTER
STATUS
and SHOW SLAVE
STATUS
. In MySQL 5.5.25 and later, it also enables
the use of the SHOW BINARY LOGS
statement.
The REPLICATION SLAVE
privilege
should be granted to accounts that are used by slave servers
to connect to the current server as their master. Without this
privilege, the slave cannot request updates that have been
made to databases on the master server.
The SELECT
privilege enables
you to select rows from tables in a database.
SELECT
statements require the
SELECT
privilege only if they
actually retrieve rows from a table. Some
SELECT
statements do not access
tables and can be executed without permission for any
database. For example, you can use
SELECT
as a simple calculator
to evaluate expressions that make no reference to tables:
SELECT 1+1; SELECT PI()*2;
The SELECT
privilege is also
needed for other statements that read column values. For
example, SELECT
is needed for
columns referenced on the right hand side of
col_name
=expr
assignment in UPDATE
statements
or for columns named in the WHERE
clause of
DELETE
or
UPDATE
statements.
The SHOW DATABASES
privilege
enables the account to see database names by issuing the
SHOW DATABASE
statement. Accounts that do
not have this privilege see only databases for which they have
some privileges, and cannot use the statement at all if the
server was started with the
--skip-show-database
option.
Note that any global privilege is a
privilege for the database.
The SHOW VIEW
privilege enables
use of SHOW CREATE VIEW
.
The SHUTDOWN
privilege enables
use of the mysqladmin shutdown command.
There is no corresponding SQL statement.
The SUPER
privilege enables an
account to use CHANGE MASTER
TO
, KILL
or
mysqladmin kill to kill threads belonging
to other accounts (you can always kill your own threads),
PURGE BINARY LOGS
,
configuration changes using
SET
GLOBAL
to modify global system variables, the
mysqladmin debug command, enabling or
disabling logging, performing updates even if the
read_only
system variable is
enabled, starting and stopping replication on slave servers,
specification of any account in the DEFINER
attribute of stored programs and views, and enables you to
connect (once) even if the connection limit controlled by the
max_connections
system
variable is reached.
To create or alter stored functions if binary logging is
enabled, you may also need the
SUPER
privilege, as described
in Section 19.7, “Binary Logging of Stored Programs”.
The TRIGGER
privilege enables
trigger operations. You must have this privilege for a table
to create, drop, or execute triggers for that table.
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 affecting existing account privileges.
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 Password
column can change an account's password, and then connect to
the MySQL server using that account.
Normally, you manipulate the contents of the grant tables in the
mysql
database indirectly by using statements
such as 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.
These mysql
database tables contain grant
information:
user
: Contains user accounts, global
privileges, and other non-privilege columns.
db
: Contains database-level privileges.
host
: Obsolete.
tables_priv
: Contains table-level
privileges.
columns_priv
: Contains column-level
privileges.
procs_priv
: Contains stored procedure and
function privileges.
proxies_priv
: Contains proxy-user
privileges.
Other tables in the mysql
database do not hold
grant information and are discussed elsewhere:
event
: Contains information about Event
Scheduler events: See Section 19.4, “Using the Event Scheduler”.
func
: Contains information about
user-defined functions: See
Section 23.3, “Adding New Functions to MySQL”.
help_
: These
tables are used for server-side help: See
Section 5.1.9, “Server-Side Help”.
xxx
plugin
: Contains information about server
plugins: See Section 5.1.7.1, “Installing and Uninstalling Plugins”, and
Section 23.2, “The MySQL Plugin API”.
proc
: Contains information about stored
procedures and functions: See
Section 19.2, “Using Stored Routines (Procedures and Functions)”.
servers
: Used by the
FEDERATED
storage engine: See
Section 14.11.2.2, “Creating a FEDERATED
Table Using
CREATE SERVER
”.
time_zone_
:
These tables contain time zone information: See
Section 10.6, “MySQL Server Time Zone Support”.
xxx
Tables with _log
in their name are used for
logging: See Section 5.2, “MySQL Server Logs”.
Each grant table contains scope columns and privilege columns:
Scope columns determine the scope of each row (entry) 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'
would be used for 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'
would be used 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 are granted by a table row; that is, what operations can 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.5, “Access Control, Stage 2: Request Verification”, describes the rules that are used to do 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 privilege granted in this table applies to
all databases on the server.
Because any global privilege is considered a privilege for
all databases, any global privilege enables a user to see
all database names with SHOW
DATABASES
or by examining the
SCHEMATA
table of
INFORMATION_SCHEMA
.
The db
table scope columns determine which
users can access which databases from which hosts. The
privilege columns determine which operations are permitted. 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 host
table is used in conjunction with
the db
table when you want a given
db
table row to apply to several hosts. For
example, if you want a user to be able to use a database from
several hosts in your network, leave the
Host
value empty in the user's
db
table row, then populate the
host
table with a row for each of those
hosts. This mechanism is described more detail in
Section 6.2.5, “Access Control, Stage 2: Request Verification”.
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. A privilege granted at the routine level applies
only to a single routine.
The proxies_priv
table indicates which
users can act as proxies for other users and whether proxy
users can grant the PROXY
privilege to other users.
The server uses the user
,
db
, and host
tables in the
mysql
database at both the first and second
stages of access control (see Section 6.2, “The MySQL Access Privilege System”).
The columns in the user
and
db
tables are shown here. The
host
table is similar to the
db
table but has a specialized use as described
in Section 6.2.5, “Access Control, Stage 2: Request Verification”.
Table 6.3. user
and db
Table Columns
Table Name | user | db |
---|---|---|
Scope columns | Host | Host |
User | Db | |
Password | User | |
Privilege columns | Select_priv | Select_priv |
Insert_priv | Insert_priv | |
Update_priv | Update_priv | |
Delete_priv | Delete_priv | |
Index_priv | Index_priv | |
Alter_priv | Alter_priv | |
Create_priv | Create_priv | |
Drop_priv | Drop_priv | |
Grant_priv | Grant_priv | |
Create_view_priv | Create_view_priv | |
Show_view_priv | Show_view_priv | |
Create_routine_priv | Create_routine_priv | |
Alter_routine_priv | Alter_routine_priv | |
Execute_priv | Execute_priv | |
Trigger_priv | Trigger_priv | |
Event_priv | Event_priv | |
Create_tmp_table_priv | Create_tmp_table_priv | |
Lock_tables_priv | Lock_tables_priv | |
References_priv | References_priv | |
Reload_priv | ||
Shutdown_priv | ||
Process_priv | ||
File_priv | ||
Show_db_priv | ||
Super_priv | ||
Repl_slave_priv | ||
Repl_client_priv | ||
Create_user_priv | ||
Create_tablespace_priv | ||
Security columns | ssl_type | |
ssl_cipher | ||
x509_issuer | ||
x509_subject | ||
plugin | ||
authentication_string | ||
Resource control columns | max_questions | |
max_updates | ||
max_connections | ||
max_user_connections |
As of MySQL 5.5.7, the mysql.user
table has
plugin
and
authentication_string
columns for storing
authentication plugin information.
If the plugin
column for an account row is
empty, the server uses native authentication for connection
attempts for the account: Clients must match the password in the
Password
column of the account row.
If an account row names a plugin in the plugin
column, the server uses it to authenticate connection attempts for
the account. Whether the plugin uses the value in the
Password
column is up to the plugin.
Prior to MySQL 5.5.11, the length of the plugin
column was 60 characters. This was increased to 64 characters in
MySQL 5.5.11 for compatibility with the
mysql.plugin
table's
name
column. (Bug #11766610, Bug #59752)
During the second stage of access control, the server performs
request verification to make sure that each client has sufficient
privileges for each request that it issues. In addition to the
user
, db
, and
host
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.4. 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 currently are unused and are discussed no further here.
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.5. 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 currently are unused and are discussed no further here.
The proxies_priv
table was added in MySQL 5.5.7
and records information about proxy users. It has these columns:
Host
, User
: These
columns indicate the user account that has the
PROXY
privilege for the proxied
account.
Proxied_host
,
Proxied_user
: These columns indicate the
account of the proxied user.
Grantor
: Currently unused.
Timestamp
: Currently unused.
With_grant
: This column indicates whether
the proxy account can grant the
PROXY
privilege to other
accounts.
Scope columns in the grant tables contain strings. They are declared as shown here; the default value for each is the empty string.
Table 6.6. Grant Table Scope Column Types
Column Name | Type |
---|---|
Host , Proxied_host | CHAR(60) |
User , Proxied_user | CHAR(16) |
Password | CHAR(41) |
Db | CHAR(64) |
Table_name | CHAR(64) |
Column_name | CHAR(64) |
Routine_name | CHAR(64) |
For access-checking purposes, comparisons of
User
, Proxied_user
,
Password
, Db
, and
Table_name
values are case sensitive.
Comparisons of Host
,
Proxied_host
, Column_name
,
and Routine_name
values are not case sensitive.
In the user
, db
, and
host
tables, each privilege is listed 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.
In the tables_priv
,
columns_priv
, and procs_priv
tables, the privilege columns are declared as
SET
columns. Values in these
columns can contain any combination of the privileges controlled
by the table. Only those privileges listed in the column value are
enabled.
Table 6.7. Set-Type Privilege Column Values
Table Name | Column Name | Possible Set Elements |
---|---|---|
tables_priv | Table_priv | 'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop',
'Grant', 'References', 'Index', 'Alter', 'Create View',
'Show view', 'Trigger' |
tables_priv | Column_priv | 'Select', 'Insert', 'Update', 'References' |
columns_priv | Column_priv | 'Select', 'Insert', 'Update', 'References' |
procs_priv | Proc_priv | 'Execute', 'Alter Routine', 'Grant' |
Administrative privileges (such as
RELOAD
or
SHUTDOWN
) are specified only in the
user
table. 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, to determine whether you can perform an
administrative operation, the server need consult only the
user
table.
The FILE
privilege also is
specified only in the user
table. It is not an
administrative privilege as such, but your ability to read or
write files on the server host is independent of the database you
are accessing.
The mysqld server reads the contents of the
grant tables into memory when it starts. You can tell it to reload
the tables by issuing a
FLUSH PRIVILEGES
statement or executing a mysqladmin
flush-privileges or mysqladmin reload
command. Changes to the grant tables take effect as indicated in
Section 6.2.6, “When Privilege Changes Take Effect”.
When you modify an account's privileges, it is a good idea to
verify that the changes set up privileges the way you want. To
check the privileges for a given account, use the
SHOW GRANTS
statement (see
Section 13.7.5.22, “SHOW GRANTS
Syntax”). For example, to determine the
privileges that are granted to an account with user name and host
name values of bob
and
pc84.example.com
, use this statement:
SHOW GRANTS FOR 'bob'@'pc84.example.com';
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.
In SQL statements such as CREATE
USER
, GRANT
, and
SET PASSWORD
, write account names
using the following rules:
Syntax for account names is
'
.
user_name
'@'host_name
'
An account name consisting only of a user name is equivalent
to
'
.
For example, user_name
'@'%''me'
is equivalent to
'me'@'%'
.
The user name and host name need not be quoted if they are
legal as unquoted identifiers. Quotes are necessary to specify
a user_name
string containing
special characters (such as
“-
”), or a
host_name
string containing special
characters or wildcard characters (such as
“%
”); for example,
'test-user'@'%.com'
.
Quote user names and host names as identifiers or as strings,
using either backticks (“`
”),
single quotation marks (“'
”),
or double quotation marks
(“"
”).
The user name and host name parts, if quoted, must be quoted
separately. That is, write
'me'@'localhost'
, not
'me@localhost'
; the latter is interpreted
as '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
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 additional detail about grant table structure, see Section 6.2.2, “Privilege System Grant Tables”.
User names and host names have certain special values or wildcard conventions, as described following.
A user 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.
You can use the wildcard characters
“%
” and
“_
” 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 you can use IP wildcard values 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 disallows matching on host names
that start with digits and a dot. Thus, if you have a host
named something like 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, you can specify a netmask indicating how many address bits to use for the network number. Netmask notation cannot be used for IPv6 addresses.
The syntax is
.
For example:
host_ip
/netmask
CREATE USER 'david'@'192.58.197.0/255.255.255.0';
This enables david
to connect from any
client host having an IP address
client_ip
for which the following
condition is true:
client_ip
&netmask
=host_ip
That is, for the CREATE USER
statement just shown:
client_ip
& 255.255.255.0 = 192.58.197.0
IP addresses that satisfy this condition and can connect to
the MySQL server are those in the range from
192.58.197.0
to
192.58.197.255
.
The netmask can only be used to tell the server to use 8, 16, 24, or 32 bits of the address. 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 following netmask will not work because it masks 28 bits, and 28 is not a multiple of 8:
192.168.0.1/255.255.255.240
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, this comparison is performed 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. But 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 this, it is advisable to check the format in which your DNS returns host names and addresses, and use values in the same format in MySQL account names.
When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, and then enters Stage 2 and waits for requests.
Your identity is based on two pieces of information:
The client host from which you connect
Your MySQL user name
Identity checking is performed using the three
user
table scope columns
(Host
, User
, and
Password
). 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 and the client supplies the password
specified in that row. The rules for permissible
Host
and User
values are
given in Section 6.2.3, “Specifying Account Names”.
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 Password
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 Password
column. In this case, it is
possible that an external password is also used to authenticate to
the MySQL server.
Nonblank Password
values in the
user
table represent encrypted passwords. MySQL
does not store passwords in plaintext form for anyone to see.
Rather, the password supplied by a user who is attempting to
connect is encrypted (using the
PASSWORD()
function). 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
Host
and User
values in the
user
table apply to incoming connections.
Host Value | User Value | Permissible Connections |
---|---|---|
'thomas.loc.gov' | 'fred' | 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 |
'%.loc.gov' | 'fred' | fred , connecting from any host in the
loc.gov domain |
'x.y.%' | 'fred' | fred , connecting from x.y.net ,
x.y.com , x.y.edu ,
and so on; this is probably not useful |
'144.155.166.177' | 'fred' | fred , connecting from the host with IP address
144.155.166.177 |
'144.155.166.%' | 'fred' | fred , connecting from any host in the
144.155.166 class C subnet |
'144.155.166.0/255.255.255.0' | 'fred' | 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).
To see how this works, suppose that the user
table looks like this:
+-----------+----------+- | Host | User | ... +-----------+----------+- | % | root | ... | % | jeffrey | ... | localhost | root | ... | localhost | | ... +-----------+----------+-
When the server reads the table into memory, it sorts the rows using the rules just described. The result after sorting looks like this:
+-----------+----------+- | Host | User | ... +-----------+----------+- | localhost | root | ... | localhost | | ... | % | jeffrey | ... | % | root | ... +-----------+----------+-
When a client attempts to connect, the server looks through the
sorted rows and uses the first match found. For a connection from
localhost
by jeffrey
, two of
the rows from the table match: the one with
Host
and User
values of
'localhost'
and ''
, and the
one with values of '%'
and
'jeffrey'
. The 'localhost'
row appears first in sorted order, so that is the one the server
uses.
Here is another example. Suppose that the user
table looks like this:
+----------------+----------+- | Host | User | ... +----------------+----------+- | % | jeffrey | ... | thomas.loc.gov | | ... +----------------+----------+-
The sorted table looks like this:
+----------------+----------+- | Host | User | ... +----------------+----------+- | thomas.loc.gov | | ... | % | jeffrey | ... +----------------+----------+-
A connection by jeffrey
from
thomas.loc.gov
is matched by the first row,
whereas a connection by jeffrey
from any host
is matched by the second.
It is a common misconception to think that, for a given user
name, all rows that explicitly name that user are used first
when the server attempts to find a match for the connection.
This is not true. The preceding example illustrates this, where
a connection from thomas.loc.gov
by
jeffrey
is first matched not by the row
containing 'jeffrey'
as the
User
column value, but by the row with no
user name. As a result, jeffrey
is
authenticated as an anonymous user, even though he specified a
user name when connecting.
If you are able to connect to the server, but your privileges are
not what you expect, you probably are being authenticated as some
other account. To find out what account the server used to
authenticate you, use the
CURRENT_USER()
function. (See
Section 12.14, “Information Functions”.) It returns a value in
format that indicates the user_name
@host_name
User
and
Host
values from the matching
user
table row. Suppose that
jeffrey
connects and issues the following
query:
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost |
+----------------+
The result shown here indicates that the matching
user
table row had a blank
User
column value. In other words, the server
is treating jeffrey
as an anonymous user.
Another way to diagnose authentication problems is to print out
the user
table and sort it by hand to see where
the first match is being made.
After you establish a connection, the server enters Stage 2 of
access control. For each request that you issue through that
connection, the server determines what operation you want to
perform, then checks whether you have sufficient privileges to do
so. This is where the privilege columns in the grant tables come
into play. These privileges can come from any of the
user
, db
,
host
, tables_priv
,
columns_priv
, or procs_priv
tables. (You may find it helpful to refer to
Section 6.2.2, “Privilege System 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
and host
tables grant
database-specific privileges. Values in the scope columns of these
tables can take the following forms:
A blank User
value in the
db
table 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 of
either table. 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 '%'
Host
value in the
db
table means “any host.” A
blank Host
value in the
db
table means “consult the
host
table for further information”
(a process that is described later in this section).
A '%'
or blank Host
value in the host
table means “any
host.”
A '%'
or blank Db
value
in either table means “any database.”
The server reads the db
and
host
tables into memory and sorts them 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, and sorts the
host
table based on the Host
and Db
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 entries, 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
or host
tables. (They
contain 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
and host
tables:
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.
If there is a matching db
table row and its
Host
column is not blank, that row defines
the user's database-specific privileges.
If the matching db
table row's
Host
column is blank, it signifies that the
host
table enumerates which hosts should be
permitted access to the database. In this case, a further
lookup is done in the host
table to find a
match on the Host
and Db
columns. If no host
table row matches,
access is denied. If there is a match, the user's
database-specific privileges are computed as the intersection
(not the union!) of the privileges in the
db
and host
table
entries; that is, the privileges that are
'Y'
in both entries. (This way you can
grant general privileges in the db
table
row and then selectively restrict them on a host-by-host basis
using the host
table entries.)
After determining the database-specific privileges granted by the
db
and host
table entries,
the server adds them to the global privileges granted by the
user
table. If the result permits the requested
operation, access is granted. Otherwise, the server successively
checks the user's table and column privileges in the
tables_priv
and columns_priv
tables, adds those to the user's privileges, and permits or denies
access based on the result. For stored-routine operations, the
server uses the procs_priv
table rather than
tables_priv
and
columns_priv
.
Expressed in boolean terms, the preceding description of how a user's privileges are calculated may be summarized like this:
global privileges OR (database privileges AND host privileges) OR table privileges OR column privileges OR routine privileges
It may not be apparent why, if the global user
row privileges are initially found to be insufficient for the
requested operation, the server adds those privileges to the
database, table, and column privileges later. The reason is that a
request might require more than one type of privilege. For
example, if you execute an
INSERT INTO ...
SELECT
statement, you need both the
INSERT
and the
SELECT
privileges. Your privileges
might be such that the user
table row grants
one privilege and the db
table row grants the
other. In this case, you have the necessary privileges to perform
the request, but the server cannot tell that from either table by
itself; the privileges granted by the entries in both tables must
be combined.
The host
table is not affected by the
GRANT
or
REVOKE
statements, so it is unused
in most MySQL installations. If you modify it directly, you can
use it for some specialized purposes, such as to maintain a list
of secure servers on the local network that are granted all
privileges.
You can also use the host
table to indicate
hosts that are not secure. Suppose that you
have a machine public.your.domain
that is
located in a public area that you do not consider secure. You can
enable access to all hosts on your network except that machine by
using host
table entries like this:
+--------------------+----+- | Host | Db | ... +--------------------+----+- | public.your.domain | % | ... (all privileges set to 'N') | %.your.domain | % | ... (all privileges set to 'Y') +--------------------+----+-
When mysqld starts, it reads all grant table contents into memory. The in-memory tables become effective for access control at that point.
If you modify the grant tables indirectly using account-management
statements such as GRANT
,
REVOKE
, SET
PASSWORD
, or RENAME USER
,
the server notices these changes and loads the grant tables into
memory again immediately.
If you modify the grant tables directly using statements such as
INSERT
,
UPDATE
, or
DELETE
, your changes have no effect
on privilege checking until you either restart the server or tell
it to reload the tables. If you change the grant tables directly
but forget to reload them, your changes have no
effect until you restart the server. This may leave you
wondering why your changes seem to make no difference!
To tell the server to reload the grant tables, perform a
flush-privileges operation. This can be done by issuing a
FLUSH PRIVILEGES
statement or by executing a mysqladmin
flush-privileges or mysqladmin reload
command.
A grant table reload affects privileges for each existing client connection as follows:
Table and column privilege changes take effect with the client's next request.
Database privilege changes take effect the next time the
client executes a USE
statement.
db_name
Client applications may cache the database name; thus, this effect may not be visible to them without actually changing to a different database or flushing the privileges.
Global privileges and passwords are unaffected for a connected client. These changes take effect only for subsequent connections.
If the server is started with the
--skip-grant-tables
option, it does
not read the grant tables or implement any access control. Anyone
can connect and do anything, which is
insecure. To cause a server thus started to read the
tables and enable access checking, flush the privileges.
If you encounter problems when you try to connect to the MySQL server, the following items describe some courses of action you can take to correct the problem.
Make sure that the server is running. If it is not, clients cannot connect to it. For example, if an attempt to connect to the server fails with a message such as one of those following, one cause might be that the server is not running:
shell>mysql
ERROR 2003: Can't connect to MySQL server on 'host_name
' (111) shell>mysql
ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)
It might be that the server is running, but you are trying to
connect using a TCP/IP port, named pipe, or Unix socket file
different from the one on which the server is listening. To
correct this when you invoke a client program, specify a
--port
option to indicate the
proper port number, or a
--socket
option to indicate
the proper named pipe or Unix socket file. To find out where
the socket file is, you can use this command:
shell> netstat -ln | grep mysql
Make sure that the server has not been configured to ignore
network connections or (if you are attempting to connect
remotely) that it has not been configured to listen only
locally on its network interfaces. If the server was started
with --skip-networking
, it will
not accept TCP/IP connections at all. If the server was
started with
--bind-address=127.0.0.1
, it
will listen for TCP/IP connections only locally on the
loopback interface and will not accept remote connections.
Check to make sure that there is no firewall blocking access to MySQL. Your firewall may be configured on the basis of the application being executed, or the port number used by MySQL for communication (3306 by default). Under Linux or Unix, check your IP tables (or similar) configuration to ensure that the port has not been blocked. Under Windows, applications such as ZoneAlarm or the Windows XP personal 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
database containing the grant tables.
For distributions that do not do this, you must initialize the
grant tables manually by running the
mysql_install_db script. For details, see
Section 2.10.1, “Unix Postinstallation Procedures”.
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,
execute the mysql_install_db script. After
running this script and starting the server, test the initial
privileges by executing this command:
shell> mysql -u root test
The server should let you connect without error.
After a fresh installation, you should connect to the server and set up your users and their access permissions:
shell> mysql -u root mysql
The server should let you connect because the MySQL
root
user has no password initially. That
is also a security risk, so setting the password for the
root
accounts is something you should do
while you're setting up your other MySQL accounts. For
instructions on setting the initial passwords, see
Section 2.10.2, “Securing the Initial MySQL Accounts”.
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.7, “mysql_upgrade — Check Tables for MySQL Upgrade”.
If a client program receives the following error message when it tries to connect, it means that the server expects passwords in a newer format than the client is capable of generating:
shell> mysql
Client does not support authentication protocol requested
by server; consider upgrading MySQL client
For information on how to deal with this, see
Section 6.1.2.3, “Password Hashing in MySQL”, and
Section C.5.2.4, “Client does not support authentication protocol
”.
Remember that client programs use connection parameters
specified in option files or environment variables. If a
client program seems to be sending incorrect default
connection parameters when you have not specified them on the
command line, check any applicable option files and your
environment. For example, if you get Access
denied
when you run a client without any options,
make sure that you have not specified an old password in any
of your option files!
You can suppress the use of option files by a client program
by invoking it with the
--no-defaults
option. For
example:
shell> mysqladmin --no-defaults -u root version
The option files that clients use are listed in Section 4.2.3.3, “Using Option Files”. Environment variables are listed in Section 2.12, “Environment Variables”.
If you get the following error, it means that you are using an
incorrect root
password:
shell> mysqladmin -u root -pxxxx
ver
Access denied for user 'root'@'localhost' (using password: YES)
If the preceding error occurs even when you have not specified
a password, it means that you have an incorrect password
listed in some option file. Try the
--no-defaults
option as
described in the previous item.
For information on changing passwords, see Section 6.3.5, “Assigning Account Passwords”.
If you have lost or forgotten the root
password, see Section C.5.4.1, “How to Reset the Root Password”.
If you change a password by using SET
PASSWORD
, INSERT
, or
UPDATE
, you must encrypt the
password using the PASSWORD()
function. If you do not use
PASSWORD()
for these
statements, the password will not work. For example, the
following statement assigns a password, but fails to encrypt
it, so the user is not able to connect afterward:
SET PASSWORD FOR 'abe'@'host_name
' = 'eagle';
Instead, set the password like this:
SET PASSWORD FOR 'abe'@'host_name
' = PASSWORD('eagle');
The PASSWORD()
function is
unnecessary when you specify a password using the
CREATE USER
or
GRANT
statements or the
mysqladmin password command. Each of those
automatically uses PASSWORD()
to encrypt the password. See
Section 6.3.5, “Assigning Account Passwords”, and
Section 13.7.1.1, “CREATE USER
Syntax”.
localhost
is a synonym for your local host
name, and is also the default host to which clients try to
connect if you specify no host explicitly.
To avoid this problem on such systems, you can use a
--host=127.0.0.1
option to
name the server host explicitly. This will make a TCP/IP
connection to the local mysqld server. You
can also use TCP/IP by specifying a
--host
option that uses the
actual host name of the local host. In this case, the host
name must be specified in a user
table row
on the server host, even though you are running the client
program on the same host as the server.
The Access denied
error message tells you
who you are trying to log in as, the client host from which
you are trying to connect, and whether you were using a
password. Normally, you should have one row in the
user
table that exactly matches the host
name and user name that were given in the error message. For
example, if you get an error message that contains
using password: NO
, it means that you tried
to log in without a password.
If you get an Access denied
error when
trying to connect to the database with mysql -u
, you may have a
problem with the user_name
user
table. Check this by
executing mysql -u root mysql
and issuing
this SQL statement:
SELECT * FROM user;
The result should include a row with the
Host
and User
columns
matching your client's host name and your MySQL user name.
If the following error occurs when you try to connect from a
host other than the one on which the MySQL server is running,
it means that there is no row in the user
table with a Host
value that matches the
client host:
Host ... is not allowed to connect to this MySQL server
You can fix this by setting up an account for the combination of client host name and user name that you are using when trying to connect.
If you do not know the IP address or host name of the machine
from which you are connecting, you should put a row with
'%'
as the Host
column
value in the user
table. After trying to
connect from the client machine, use a SELECT
USER()
query to see how you really did connect. Then
change the '%'
in the
user
table row to the actual host name that
shows up in the log. Otherwise, your system is left insecure
because it permits connections from any host for the given
user name.
On Linux, another reason that this error might occur is that
you are using a binary MySQL version that is compiled with a
different version of the glibc
library than
the one you are using. In this case, you should either upgrade
your operating system or glibc
, or download
a source distribution of MySQL version and compile it
yourself. A source RPM is normally trivial to compile and
install, so this is not a big problem.
If you specify a host name when trying to connect, but get an error message where the host name is not shown or is an IP address, it means that the MySQL server got an error when trying to resolve the IP address of the client host to a name:
shell> mysqladmin -u root -pxxxx
-h some_hostname
ver
Access denied for user 'root'@'' (using password: YES)
If you try to connect as root
and get the
following error, it means that you do not have a row in the
user
table with a User
column value of 'root'
and that
mysqld cannot resolve the host name for
your client:
Access denied for user ''@'unknown'
These errors indicate a DNS problem. To fix it, execute mysqladmin flush-hosts to reset the internal DNS host cache. See Section 8.11.5.2, “DNS Lookup Optimization and the Host Cache”.
Some permanent solutions are:
Determine what is wrong with your DNS server and fix it.
Specify IP addresses rather than host names in the MySQL grant tables.
Put an entry for the client machine name in
/etc/hosts
on Unix or
\windows\hosts
on Windows.
Start mysqld with the
--skip-name-resolve
option.
Start mysqld with the
--skip-host-cache
option.
On Unix, if you are running the server and the client on
the same machine, connect to localhost
.
Unix connections to localhost
use a
Unix socket file rather than TCP/IP.
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 test
works but
mysql -h
results in your_hostname
-u
root testAccess
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 an entry with host 'pluto'
in the
user
table, but your DNS tells MySQL that
your host name is 'pluto.example.com'
, the
entry does not work. Try adding an entry to the
user
table that contains the IP address of
your host as the Host
column value.
(Alternatively, you could add an entry to the
user
table with a Host
value that contains a wildcard; for example,
'pluto.%'
. However, use of
Host
values ending with
“%
” is
insecure and is not
recommended!)
If mysql -u
works but user_name
testmysql -u
does not, you
have not granted access to the given user for the database
named user_name
other_db
other_db
.
If mysql -u
works when
executed on the server host, but user_name
mysql -h
does not work
when executed on a remote client host, you have not enabled
access to the server for the given user name from the remote
host.
host_name
-u
user_name
If you cannot figure out why you get Access
denied
, remove from the user
table all entries that have Host
values
containing wildcards (entries that contain
'%'
or '_'
characters).
A very common error is to insert a new entry with
Host
='%'
and
User
='
,
thinking that this enables you to specify
some_user
'localhost
to connect from the same machine.
The reason that this does not work is that the default
privileges include an entry with
Host
='localhost'
and
User
=''
. Because that
entry has a Host
value
'localhost'
that is more specific than
'%'
, it is used in preference to the new
entry when connecting from localhost
! The
correct procedure is to insert a second entry with
Host
='localhost'
and
User
='
,
or to delete the entry with
some_user
'Host
='localhost'
and
User
=''
. After deleting
the entry, remember to issue a
FLUSH
PRIVILEGES
statement to reload the grant tables. See
also Section 6.2.4, “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 entry 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.6, “When Privilege Changes Take Effect”.
If you have access problems with a Perl, PHP, Python, or ODBC
program, try to connect to the server with mysql -u
or user_name
db_name
mysql
-u
. If you are able
to connect using the mysql client, the
problem lies with your program, not with the access
privileges. (There is no space between user_name
-pyour_pass
db_name
-p
and
the password; you can also use the
--password=
syntax to specify the password. If you use the
your_pass
-p
or
--password
option with no
password value, MySQL prompts you for the password.)
For testing purposes, start the mysqld
server with the
--skip-grant-tables
option.
Then you can change the MySQL grant tables and use the
mysqlaccess script to check whether your
modifications have the desired effect. When you are satisfied
with your changes, execute mysqladmin
flush-privileges to tell the
mysqld server to reload the privileges.
This enables you to begin using the new grant table contents
without stopping and restarting the server.
If you get the following error, you may have a problem with
the db
or host
table:
Access to database denied
If the entry selected from the db
table has
an empty value in the Host
column, make
sure that there are one or more corresponding entries in the
host
table specifying which hosts the
db
table entry applies to. This problem
occurs infrequently because the host
table
is rarely used.
If everything else fails, start the mysqld
server with a debugging option (for example,
--debug=d,general,query
). This
prints host and user information about attempted connections,
as well as information about each command issued. See
MySQL
Internals: Porting.
If you have any other problems with the MySQL grant tables and
feel you must post the problem to the mailing list, always
provide a dump of the MySQL grant tables. You can dump the
tables with the mysqldump mysql command. To
file a bug report, see the instructions at
Section 1.7, “How to Report Bugs or Problems”. In some cases, you may need to
restart mysqld with
--skip-grant-tables
to run
mysqldump.
This section describes how to set up accounts for clients of your MySQL server. It discusses the following topics:
The meaning of account names and passwords as used in MySQL and how that compares to names and passwords used by your operating system
How to set up new accounts and remove existing accounts
How to change passwords
Guidelines for using passwords securely
How to use secure connections with SSL
See also Section 13.7.1, “Account Management Statements”, which describes the syntax and use for all user-management SQL statements.
MySQL stores accounts in the user
table of the
mysql
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. The account may also have a password.
For information about account representation in the
user
table, see
Section 6.2.2, “Privilege System Grant Tables”. MySQL 5.5
supports authentication plugins, so it is possible that an account
authenticates using some external authentication method. See
Section 6.3.6, “Pluggable Authentication”.
There are several distinctions between the way user names and passwords are used by MySQL and the way they are used by 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. Because this means that anyone
can attempt to connect to the server using any user name, 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 16 characters long. Operating system user names, because they are completely unrelated to MySQL user names, may be of a different maximum length. For example, Unix user names typically are limited to eight characters.
The limit on MySQL user name length is hard-coded in the
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 any of the tables in the
mysql
database in any manner whatsoever
except by means of the procedure that is described in
Section 4.4.7, “mysql_upgrade — Check Tables for MySQL Upgrade”. Attempting to redefine
MySQL's system tables in any other fashion results in
undefined (and unsupported!) behavior.
The server uses MySQL passwords stored in the
user
table to authenticate client
connections using MySQL native authentication (against
passwords stored in the mysql.user
table).
These passwords have nothing to do with 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 a plugin, the
authentication method that the plugin implements may or may
not use the password in the user
table. In
this case, it is possible that an external password is also
used to authenticate to the MySQL server.
MySQL encrypts passwords stored in the user
table using its own algorithm. This encryption is the same as
that implemented by the
PASSWORD()
SQL function but
differs from that used during the Unix login process. Unix
password encryption is the same as that implemented by the
ENCRYPT()
SQL function. See the
descriptions of the PASSWORD()
and ENCRYPT()
functions in
Section 12.13, “Encryption and Compression Functions”.
From version 4.1 on, MySQL employs a stronger authentication
method that has better password protection during the
connection process than in earlier versions. It is secure even
if TCP/IP packets are sniffed or the mysql
database is captured. (In earlier versions, even though
passwords are stored in encrypted form in the
user
table, knowledge of the encrypted
password value could be used to connect to the MySQL server.)
Section 6.1.2.3, “Password Hashing in MySQL”, discusses password
encryption further.
It is possible to connect to the server regardless of
character set settings if the user name and password contain
only ASCII characters. 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.
When you install MySQL, the grant tables are populated with an
initial set of accounts. The names and access privileges for these
accounts are described in Section 2.10.2, “Securing the Initial MySQL Accounts”,
which also discusses how to assign passwords to them. Thereafter,
you normally set up, modify, and remove MySQL accounts using
statements such as CREATE USER
,
GRANT
, and
REVOKE
. See
Section 13.7.1, “Account Management Statements”.
When you connect to a MySQL server with a command-line client, specify the user name and password as necessary for the account that you want to use:
shell> mysql --user=monty --password=password
db_name
If you prefer short options, the command looks like this:
shell> mysql -u monty -ppassword
db_name
There must be no space between the
-p
option and the following password value.
If you omit the password
value
following the --password
or
-p
option on the command line, the client prompts
for one.
Specifying a password on the command line should be considered insecure. See Section 6.1.2.2, “End-User Guidelines for Password Security”. You can use an option file to avoid giving the password on the command line.
For additional information about specifying user names, passwords, and other connection parameters, see Section 4.2.2, “Connecting to the MySQL Server”.
You can create MySQL accounts in two ways:
By using statements intended for creating accounts, such as
CREATE USER
or
GRANT
. These statements cause
the server to make appropriate modifications to the grant
tables.
By manipulating the MySQL grant tables directly with
statements such as INSERT
,
UPDATE
, or
DELETE
.
The preferred method is to use account-creation statements because
they are more concise and less error-prone than manipulating the
grant tables directly. CREATE USER
and GRANT
are described in
Section 13.7.1, “Account Management Statements”.
Another option for creating accounts is to use one of several
available third-party programs that offer capabilities for MySQL
account administration. phpMyAdmin
is one such
program.
The following examples show how to use the
mysql client program to set up new accounts.
These examples assume that privileges have been set up according
to the defaults described in Section 2.10.2, “Securing the Initial MySQL Accounts”.
This means that to make changes, you must connect to the MySQL
server as the MySQL root
user, and the
root
account must have the
INSERT
privilege for the
mysql
database and the
RELOAD
administrative privilege.
As noted in the examples where appropriate, some of the statements
will fail if the server's SQL mode has been set to enable certain
restrictions. In particular, strict mode
(STRICT_TRANS_TABLES
,
STRICT_ALL_TABLES
) and
NO_AUTO_CREATE_USER
will prevent
the server from accepting some of the statements. Workarounds are
indicated for these cases. For more information about SQL modes
and their effect on grant table manipulation, see
Section 5.1.6, “Server SQL Modes”, and Section 13.7.1.3, “GRANT
Syntax”.
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 will also need to supply a
--password
or -p
option, both
for this mysql command and for those later in
this section.
After connecting to the server as root
, you can
add new accounts. The following statements use
GRANT
to set up four new accounts:
mysql>CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql>GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
->WITH GRANT OPTION;
mysql>CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql>GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
->WITH GRANT OPTION;
mysql>CREATE USER 'admin'@'localhost';
mysql>GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql>CREATE USER 'dummy'@'localhost';
The accounts created by these statements have the following properties:
Two of the accounts have a user name of
monty
and a password of
some_pass
. Both accounts 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.
It is necessary to have both accounts for
monty
to be able to connect from anywhere
as monty
. Without the
localhost
account, the anonymous-user
account for localhost
that is created by
mysql_install_db would take precedence when
monty
connects from the local host. As a
result, monty
would be 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.4, “Access Control, Stage 1: Connection Verification”.)
The 'admin'@'localhost'
account has no
password. This account can be used only by
admin
to connect from the local host. It is
granted the RELOAD
and
PROCESS
administrative
privileges. These privileges enable the
admin
user to execute the
mysqladmin reload, mysqladmin
refresh, and mysqladmin
flush-xxx
commands, as
well as mysqladmin processlist . No
privileges are granted for accessing any databases. You could
add such privileges later by issuing other
GRANT
statements.
The 'dummy'@'localhost'
account has no
password. 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 later.
The statements that create accounts with no password will fail if
the NO_AUTO_CREATE_USER
SQL mode
is enabled. To deal with this, use an IDENTIFIED
BY
clause that specifies a nonempty password.
To check the privileges for an account, use
SHOW GRANTS
:
mysql> SHOW GRANTS FOR 'admin'@'localhost';
+-----------------------------------------------------+
| Grants for admin@localhost |
+-----------------------------------------------------+
| GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost' |
+-----------------------------------------------------+
As an alternative to CREATE USER
and GRANT
, you can create the same
accounts directly by issuing INSERT
statements and then telling the server to reload the grant tables
using FLUSH
PRIVILEGES
:
shell>mysql --user=root mysql
mysql>INSERT INTO user
->VALUES('localhost','monty',PASSWORD('some_pass'),
->'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql>INSERT INTO user
->VALUES('%','monty',PASSWORD('some_pass'),
->'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
->'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
->'','','','',0,0,0,0);
mysql>INSERT INTO user SET Host='localhost',User='admin',
->Reload_priv='Y', Process_priv='Y';
mysql>INSERT INTO user (Host,User,Password)
->VALUES('localhost','dummy','');
mysql>FLUSH PRIVILEGES;
When you create accounts with
INSERT
, it is necessary to use
FLUSH PRIVILEGES
to tell the server to reload the grant tables. Otherwise, the
changes go unnoticed until you restart the server. With
CREATE USER
,
FLUSH PRIVILEGES
is unnecessary.
The reason for using the PASSWORD()
function with INSERT
is to encrypt
the password. The CREATE USER
statement encrypts the password for you, so
PASSWORD()
is unnecessary.
The 'Y'
values enable privileges for the
accounts. Depending on your MySQL version, you may have to use a
different number of 'Y'
values in the first two
INSERT
statements. The
INSERT
statement for the
admin
account employs the more readable
extended INSERT
syntax using
SET
.
In the INSERT
statement for the
dummy
account, only the
Host
, User
, and
Password
columns in the user
table row are assigned values. None of the privilege columns are
set explicitly, so MySQL assigns them all the default value of
'N'
. This is equivalent to what
CREATE USER
does.
If strict SQL mode is enabled, all columns that have no default
value must have a value specified. In this case,
INSERT
statements must explicitly
specify values for the ssl_cipher
,
x509_issuer
, and
x509_subject
columns.
To set up a superuser account, it is necessary only to insert a
user
table row with all privilege columns set
to 'Y'
. The user
table
privileges are global, so no entries in any of the other grant
tables are needed.
The next examples create three accounts and give them access to
specific databases. Each of them has a user name of
custom
and password of
obscure
.
To create the accounts with CREATE
USER
and GRANT
, use the
following statements:
shell>mysql --user=root mysql
mysql>CREATE USER 'custom'@'localhost' IDENTIFIED BY 'obscure';
mysql>GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
->ON bankaccount.*
->TO 'custom'@'localhost';
mysql>CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'obscure';
mysql>GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
->ON expenses.*
->TO 'custom'@'host47.example.com';
mysql>CREATE USER 'custom'@'server.domain' IDENTIFIED BY 'obscure';
mysql>GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
->ON customer.*
->TO 'custom'@'server.domain';
The three accounts can be used as follows:
The first account can access the
bankaccount
database, but only from the
local host.
The second account can access the expenses
database, but only from the host
host47.example.com
.
The third account can access the customer
database, but only from the host
server.domain
.
To set up the custom
accounts without
GRANT
, use
INSERT
statements as follows to
modify the grant tables directly:
shell>mysql --user=root mysql
mysql>INSERT INTO user (Host,User,Password)
->VALUES('localhost','custom',PASSWORD('obscure'));
mysql>INSERT INTO user (Host,User,Password)
->VALUES('host47.example.com','custom',PASSWORD('obscure'));
mysql>INSERT INTO user (Host,User,Password)
->VALUES('server.domain','custom',PASSWORD('obscure'));
mysql>INSERT INTO db
->(Host,Db,User,Select_priv,Insert_priv,
->Update_priv,Delete_priv,Create_priv,Drop_priv)
->VALUES('localhost','bankaccount','custom',
->'Y','Y','Y','Y','Y','Y');
mysql>INSERT INTO db
->(Host,Db,User,Select_priv,Insert_priv,
->Update_priv,Delete_priv,Create_priv,Drop_priv)
->VALUES('host47.example.com','expenses','custom',
->'Y','Y','Y','Y','Y','Y');
mysql>INSERT INTO db
->(Host,Db,User,Select_priv,Insert_priv,
->Update_priv,Delete_priv,Create_priv,Drop_priv)
->VALUES('server.domain','customer','custom',
->'Y','Y','Y','Y','Y','Y');
mysql>FLUSH PRIVILEGES;
The first three INSERT
statements
add user
table entries that permit the user
custom
to connect from the various hosts with
the given password, but grant no global privileges (all privileges
are set to the default value of 'N'
). The next
three INSERT
statements add
db
table entries that grant privileges to
custom
for the bankaccount
,
expenses
, and customer
databases, but only when accessed from the proper hosts. As usual
when you modify the grant tables directly, you must tell the
server to reload them with
FLUSH PRIVILEGES
so that the privilege changes take effect.
To create a user who has access from all machines in a given
domain (for example, mydomain.com
), you can use
the “%
” wildcard character in the
host part of the account name:
mysql> CREATE USER 'myname'@'%.mydomain.com' IDENTIFIED BY 'mypass';
To do the same thing by modifying the grant tables directly, do this:
mysql>INSERT INTO user (Host,User,Password,...)
->VALUES('%.mydomain.com','myname',PASSWORD('mypass'),...);
mysql>FLUSH PRIVILEGES;
To remove an account, use the DROP
USER
statement, which is described in
Section 13.7.1.2, “DROP USER
Syntax”.
One means of limiting 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 many MySQL administrators, particularly those
working for Internet Service Providers.
In MySQL 5.5, you can limit use of the following server resources for individual accounts:
The number of queries that an account can issue per hour
The number of updates that 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 (unless its results are served from the query cache). Only statements that modify databases or tables count against the update limit.
An “account” in this context corresponds to a row in
the mysql.user
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
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 set resource limits for an account, use the
GRANT
statement (see
Section 13.7.1.3, “GRANT
Syntax”). Provide a WITH
clause
that names each resource to be limited. The default value for each
limit is zero (no limit). For example, to create a new account
that can access the customer
database, but only
in a limited fashion, issue these statements:
mysql>CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank';
mysql>GRANT ALL ON customer.* TO 'francis'@'localhost'
->WITH MAX_QUERIES_PER_HOUR 20
->MAX_UPDATES_PER_HOUR 10
->MAX_CONNECTIONS_PER_HOUR 5
->MAX_USER_CONNECTIONS 2;
The limit types need not all be named in the
WITH
clause, but those named can be present in
any order. The value for each per-hour limit should be an integer
representing a count per hour. For
MAX_USER_CONNECTIONS
, the limit is an integer
representing the maximum number of simultaneous connections by the
account. If this limit is set to zero, the global
max_user_connections
system
variable value determines the number of simultaneous connections.
If max_user_connections
is also
zero, there is no limit for the account.
To modify existing limits for an account, use a
GRANT USAGE
statement at the global level (ON *.*
). The
following statement changes the query limit for
francis
to 100:
mysql>GRANT USAGE ON *.* TO 'francis'@'localhost'
->WITH MAX_QUERIES_PER_HOUR 100;
The statement modifies only the limit value specified and leaves the account otherwise unchanged.
To remove a limit, set its value to zero. For example, to remove
the limit on how many times per hour francis
can connect, use this statement:
mysql>GRANT USAGE ON *.* TO 'francis'@'localhost'
->WITH MAX_CONNECTIONS_PER_HOUR 0;
As mentioned previously, the simultaneous-connection limit for an
account is determined from the
MAX_USER_CONNECTIONS
limit and the
max_user_connections
system
variable. Suppose that the global
max_user_connections
value is 10
and three accounts have resource limits specified with
GRANT
:
GRANT ... TO 'user1'@'localhost' WITH MAX_USER_CONNECTIONS 0; GRANT ... TO 'user2'@'localhost' WITH MAX_USER_CONNECTIONS 5; GRANT ... TO 'user3'@'localhost' WITH MAX_USER_CONNECTIONS 20;
user1
has a connection limit of 10 (the global
max_user_connections
value)
because it has a zero MAX_USER_CONNECTIONS
limit). 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.2, “Privilege System 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, further connections for the account are rejected until that hour is up. Similarly, if the account reaches its limit on the number of queries or updates, further queries or updates are rejected until the hour is up. In all such cases, an appropriate error message is issued.
Resource counting is done per account, not per client. For example, if your account has a query limit of 50, you cannot increase your limit to 100 by making two simultaneous client connections to the server. Queries issued on both connections are counted together.
The current per-hour resource-use counts can be reset globally for all accounts, or individually for a given account:
To reset the current counts to zero for all accounts, issue a
FLUSH
USER_RESOURCES
statement. The counts also can be
reset by reloading the grant tables (for example, with a
FLUSH
PRIVILEGES
statement or a mysqladmin
reload command).
The counts for an individual account can be set to zero by
re-granting it any of its limits. To do this, use
GRANT USAGE
as described earlier and specify a limit value equal to the
value that the account currently has.
Counter resets do not affect the
MAX_USER_CONNECTIONS
limit.
All counts begin at zero when the server starts; counts are not carried over through a restart.
For the MAX_USER_CONNECTIONS
limit, an edge
case can occur if the account currently has open the maximum
number of connections permitted to it: A disconnect followed
quickly by a connect can result in an error
(ER_TOO_MANY_USER_CONNECTIONS
or
ER_USER_LIMIT_REACHED
) if the
server has not fully processed the disconnect by the time the
connect occurs. When the server finishes disconnect processing,
another connection will once more be permitted.
Required credentials for clients that connect to the MySQL server can include a password. This section describes how to assign passwords for MySQL accounts. In MySQL 5.5, it is also possible for clients to authenticate using plugins. For information, see Section 6.3.6, “Pluggable Authentication”.
To assign a password when you create a new account with
CREATE USER
, include an
IDENTIFIED BY
clause:
mysql>CREATE USER 'jeffrey'@'localhost'
->IDENTIFIED BY 'mypass';
To assign or change a password for an existing account, one way is
to issue a SET PASSWORD
statement:
mysql>SET PASSWORD FOR
->'jeffrey'@'localhost' = PASSWORD('mypass');
MySQL stores passwords in the user
table in the
mysql
database. Only users such as
root
that have update access to the
mysql
database can change the password for
other users. If you are not connected as an anonymous user, you
can change your own password by omitting the
FOR
clause:
mysql> SET PASSWORD = PASSWORD('mypass');
You can also use a GRANT
USAGE
statement at the global level (ON
*.*
) to assign a password to an account without
affecting the account's current privileges:
mysql>GRANT USAGE ON *.* TO 'jeffrey'@'localhost'
->IDENTIFIED BY 'mypass';
To assign a password from the command line, use the mysqladmin command:
shell> mysqladmin -u user_name
-h host_name
password "newpwd
"
The account for which this command sets the password is the one
with a user
table row that matches
user_name
in the
User
column and the client host from
which you connect in the Host
column.
It is preferable to assign passwords using one of the preceding
methods, but it is also possible to modify the
user
table directly. In this case, you must
also use FLUSH
PRIVILEGES
to cause the server to reread the grant
tables. Otherwise, the change remains unnoticed by the server
until you restart it.
To establish a password for a new account, provide a value for
the Password
column:
mysql>INSERT INTO mysql.user (Host,User,Password)
->VALUES('localhost','jeffrey',PASSWORD('mypass'));
mysql>FLUSH PRIVILEGES;
To change the password for an existing account, use
UPDATE
to set the
Password
column value:
mysql>UPDATE mysql.user SET Password = PASSWORD('bagel')
->WHERE Host = 'localhost' AND User = 'francis';
mysql>FLUSH PRIVILEGES;
During authentication when a client connects to the server, MySQL
treats the password in the user
table as an
encrypted hash value (the value that
PASSWORD()
would return for the
password). When assigning a password to an account, it is
important to store an encrypted value, not the plaintext password.
Use the following guidelines:
When you assign a password using CREATE
USER
, GRANT
with an
IDENTIFIED BY
clause, or the
mysqladmin password command, they encrypt
the password for you. Specify the literal plaintext password:
mysql>CREATE USER 'jeffrey'@'localhost'
->IDENTIFIED BY 'mypass';
For CREATE USER
or
GRANT
, you can avoid sending
the plaintext password if you know the hash value that
PASSWORD()
would return for the
password. Specify the hash value preceded by the keyword
PASSWORD
:
mysql>CREATE USER 'jeffrey'@'localhost'
->IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';
When you assign an account a nonempty password using
SET PASSWORD
,
INSERT
, or
UPDATE
, you must use the
PASSWORD()
function to encrypt
the password, otherwise the password is stored as plaintext.
Suppose that you assign a password like this:
mysql>SET PASSWORD FOR
->'jeffrey'@'localhost' = 'mypass';
The result is that the literal value
'mypass'
is stored as the password in the
user
table, not the encrypted value. When
jeffrey
attempts to connect to the server
using this password, the value is encrypted and compared to
the value stored in the user
table.
However, the stored value is the literal string
'mypass'
, so the comparison fails and the
server rejects the connection with an Access
denied
error.
In MySQL 5.5, enabling the
read_only
system variable
prevents the use of the SET
PASSWORD
statement by any user not having the
SUPER
privilege.
PASSWORD()
encryption differs
from Unix password encryption. See Section 6.3.1, “User Names and Passwords”.
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. It then uses this row to authenticate the client.
Before MySQL 5.5.7, the server authenticates the password provided
by the client against the Password
column of
the account row.
As of MySQL 5.5.7, the server authenticates clients using plugins.
Selection of the proper account row from the
mysql.user
table is based on the user name and
client host, as before, but the server authenticates the client
credentials as follows:
The server determines from the account row which authentication plugin applies for the client:
If the account row specifies no plugin name, the server
uses native authentication; that is, authentication
against the password stored in the
Password
column of the account row.
This is the same authentication method provided by MySQL
servers older than 5.5.7, before pluggable authentication
was implemented, but now is implemented using two plugins
that are built in and cannot be disabled.
If the account row specifies a plugin, the server invokes it to authenticate the user. If the server cannot find the plugin, an error occurs.
The plugin returns a status to the server indicating whether the user is permitted to connect.
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 other than native
authentication based on passwords stored 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. While the connection lasts, the proxy user is treated, for purposes of access control, as having the privileges of a different user. In effect, one user impersonates another. For more information, see Section 6.3.7, “Proxy Users”.
Several authentication plugins are available in MySQL. The following sections provide details about specific plugins.
Plugins that perform native authentication that matches the
password against the Password
column of the
account row. See
Section 6.3.6.1, “The Native Authentication Plugins”. Native
authentication is the default for accounts that have no plugin
named explicitly in their account row.
A plugin that performs external authentication against PAM (Pluggable Authentication Modules), enabling MySQL Server to use PAM to authenticate MySQL users. This plugin supports proxy users as well. See Section 6.3.6.2, “The PAM Authentication Plugin”.
A plugin that performs external authentication on Windows, enabling MySQL Server to use native Windows services to authenticate client connections. Users who have logged in to Windows can connect from MySQL client programs to the server based on the information in their environment without specifying an additional password. This plugin supports proxy users as well. See Section 6.3.6.3, “The Windows Native Authentication Plugin”.
A client-side plugin that sends the password to the server without hashing or encryption. This plugin can be used by server-side plugins that require access to the password exactly as provided by the client user. See Section 6.3.6.4, “The Clear-Text Client-Side Authentication Plugin”.
A plugin that authenticates clients that connect from the local host through the Unix socket file. See Section 6.3.6.5, “The Socket Peer-Credential Authentication Plugin”.
A test plugin that authenticates using MySQL native authentication. This plugin is intended for testing and development purposes, and as an example of how to write an authentication plugin. See Section 6.3.6.6, “The Test Authentication Plugin”.
For information about current restrictions on the use of pluggable authentication, including which connectors support which plugins, see Section E.9, “Restrictions on Pluggable Authentication”.
Third-party connector developers should read that section to determine the extent to which a connector can take advantage of pluggable authentication capabilities and what steps to take to become more compliant.
If you are interested in writing your own authentication plugins, see Section 23.2.4.9, “Writing Authentication Plugins”.
In general, pluggable authentication uses corresponding plugins on the server and client sides, so you use a given authentication method like this:
On the server host, install the appropriate library containing the server plugin, if necessary, so that the server can use it to authenticate client connections. Similarly, on each client host, install the appropriate library containing the client plugin for use by client programs.
Create MySQL accounts that specify use of the plugin for authentication.
When a client connects, the server plugin tells the client program which client plugin to use for authentication.
The remainder of this section provides general instructions for installing and using authentication plugins. The instructions use an an example authentication plugin included in MySQL distributions (see Section 6.3.6.6, “The Test Authentication Plugin”). The procedure is similar for other authentication plugins; substitute the appropriate plugin and file names.
The example authentication plugin has these characteristics:
The server-side plugin name is
test_plugin_server
.
The client-side plugin name is
auth_test_plugin
.
Both plugins are located in the shared library object file
named auth_test_plugin.so
in the plugin
directory (the directory named by the
plugin_dir
system variable).
The file name suffix might differ on your system.
Install and use the example authentication plugin as follows:
Make sure that the plugin library is installed on the server and client hosts.
Install the server-side test plugin at server startup or at runtime:
To install the plugin at startup, use the
--plugin-load
option. For
example, use these lines in a my.cnf
option file:
[mysqld] plugin-load=test_plugin_server=auth_test_plugin.so
With this plugin-loading method, the option must be given each time you start the server. The plugin is not installed if you omit the option.
To install the plugin at runtime, use the
INSTALL PLUGIN
statement:
mysql> INSTALL PLUGIN test_plugin_server SONAME 'auth_test_plugin.so';
This installs the plugin permanently and need be done only once.
Verify that the plugin is installed. For example, use
SHOW PLUGINS
:
mysql> SHOW PLUGINS\G
...
*************************** 21. row ***************************
Name: test_plugin_server
Status: ACTIVE
Type: AUTHENTICATION
Library: auth_test_plugin.so
License: GPL
For other ways to check the plugin, see Section 5.1.7.2, “Obtaining Server Plugin Information”.
To specify that a MySQL user must be authenticated using the
plugin, name it in the IDENTIFIED WITH
clause of the CREATE USER
statement that creates the user:
CREATE USER 'testuser'@'localhost' IDENTIFIED WITH test_plugin_server;
Connect to the server using a client program. The test plugin
authenticates the same way as native MySQL authentication, so
provide the usual --user
and
--password
options that you
normally use to connect to the server. For example:
shell> mysql --user=your_name
--password=your_pass
For connections by testuser
, the server
sees that the account must be authenticated using the
server-side plugin named test_plugin_server
and communicates to the client program which client-side
plugin it must use—in this case,
auth_test_plugin
.
In the case that the account uses the authentication method
that is the default for both the server and the client
program, the server need not communicate to the client which
plugin to use, and a round trip in client/server negotiation
can be avoided. Currently this is true for accounts that use
native MySQL authentication
(mysql_native_password
).
The
--default-auth=
option can be specified on the mysql
command line to make explicit which client-side plugin the
program can expect to use, although the server will override
this if the user account requires a different plugin.
plugin_name
If mysql does not find the plugin, specify
a
--plugin-dir=
option to indicate where the plugin is located.
dir_name
If you start the server with the
--skip-grant-tables
option,
authentication plugins are not used even if loaded because the
server performs no client authentication and permits any client
to connect. Because this is insecure, you might want to use
--skip-grant-tables
in
conjunction with
--skip-networking
to prevent
remote clients from connecting.
MySQL includes two plugins that implement the same kind of
native authentication that older servers provide; that is,
authentication against passwords stored in the
Password
column of the
mysql.user
table:
The mysql_native_password
authentication
plugin implements the same default authentication against
the mysql.user
table as used prior to the
implementation of pluggable authentication.
The mysql_old_password
plugin implements
authentication as used before MySQL 4.1.1 that is based on
shorter password hash values. For information about this
authentication method, see
Section 6.1.2.3, “Password Hashing in MySQL”.
The native authentication plugins are backward compatible. Clients older than MySQL 5.5.7 do not support authentication plugins but use native authentication, so they can connect to servers from 5.5.7 and up.
The following tables show the plugin names. Both are considered to implement native authentication even though only one has “native” in the name.
Table 6.8. MySQL Native Password Authentication Plugin
Server-side plugin name | mysql_native_password |
Client-side plugin name | mysql_native_password |
Library object file name | None (built in) |
Table 6.9. MySQL Native Old-Password Authentication Plugin
Server-side plugin name | mysql_old_password |
Client-side plugin name | mysql_old_password |
Library object file name | None (built in) |
Each plugin exists in both client and server form. MySQL client
programs use mysql_native_password
by
default. The --default-auth
option
can be used to specify either plugin explicitly:
shell>mysql --default-auth=mysql_native_password ...
shell>mysql --default-auth=mysql_old_password ...
The server-side plugins are built into the server and cannot be
disabled by unloading them. The client-side plugins are built
into the libmysql
client library as of MySQL
5.5.7 and available to any program linked against
libmysql
from that version or newer.
For general information about pluggable authentication in MySQL, see Section 6.3.6, “Pluggable Authentication”.
PAM (Pluggable Authentication Modules) enables a system to access various kinds of authentication methods through a standard interface. As of MySQL 5.5.16, commercial distributions of MySQL include a PAM authentication plugin that enables MySQL Server to use PAM to authenticate MySQL users.
The PAM plugin uses the information passed to it by the MySQL
server (such as user name, host name, password, and
authentication string), plus whatever is available for PAM
lookup (such as Unix passwords or an LDAP directory). The plugin
checks the user credentials against PAM and returns
'Authentication succeeded, Username is
or
user_name
''Authentication failed'
.
The PAM authentication plugin provides these capabilities:
External authentication: The plugin enables MySQL Server to accept connections from users defined outside the MySQL grant tables.
Proxy user support: The plugin can return to MySQL a user
name different from the login user, based on the groups the
external user is in and the authentication string provided.
This means that the plugin can return the MySQL user that
defines the privileges the external PAM-authenticated user
should have. For example, a PAM user named
joe
can connect and have the privileges
of the MySQL user named developer
.
The following table shows the plugin and library file names. The
file name suffix might be different on your system. The file
location must be the directory named by the
plugin_dir
system variable. For
installation information, see
Section 6.3.6.2.1, “Installing the PAM Authentication Plugin”.
Table 6.10. MySQL PAM Authentication Plugin
Server-side plugin name | authentication_pam |
Client-side plugin name | mysql_clear_password |
Library object file name | authentication_pam.so |
The library file includes only the server-side plugin. As of
MySQL 5.5.10, the client-side plugin is built into the
libmysql
client library. See
Section 6.3.6.4, “The Clear-Text Client-Side Authentication Plugin”.
The server-side PAM authentication plugin is included only in commercial distributions. It is not included in MySQL community distributions. The client-side clear-text plugin that communicates with the server-side plugin is built into the MySQL client library and is included in all distributions, including community distributions. This permits clients from any 5.5.10 or newer distribution to connect to a server that has the server-side plugin loaded.
The PAM authentication plugin has been tested on Linux and Mac OS X. It requires MySQL Server 5.5.16 or newer.
For general information about pluggable authentication in MySQL, see Section 6.3.6, “Pluggable Authentication”. For proxy user information, see Section 6.3.7, “Proxy Users”.
The PAM authentication plugin must be installed in the MySQL
plugin directory (the directory named by the
plugin_dir
system variable).
To enable the plugin, start the server with the
--plugin-load
option. For
example, put the following lines in your
my.cnf
file. If object files have a
suffix different from .so
on your system,
substitute the correct suffix.
[mysqld] plugin-load=authentication_pam.so
Use the plugin name authentication_pam
in
the IDENTIFIED WITH
clause of
CREATE USER
or
GRANT
statements for MySQL
accounts that should be authenticated with this plugin.
You can also use a
--plugin-dir=
option if it is necessary to tell the server the location of
the plugin directory.
path_name
To verify plugin installation, examine the
INFORMATION_SCHEMA.PLUGINS
table
or use the SHOW PLUGINS
statement. See Section 5.1.7.2, “Obtaining Server Plugin Information”.
This section describes how to use the PAM authentication plugin to connect from MySQL client programs to the server. It is assumed that the server-side plugin is enabled and that client programs are recent enough to include the client-side plugin.
The client-side plugin with which the PAM plugin communicates simply sends the password to the server in clear text so it can be passed to PAM. This may be a security problem in some configurations, but is necessary to use the server-side PAM library. To avoid problems if there is any possibility that the password would be intercepted, clients should connect to MySQL Server using SSL. See Section 6.3.6.4, “The Clear-Text Client-Side Authentication Plugin”.
To refer to the PAM authentication plugin in the
IDENTIFIED WITH
clause of a
CREATE USER
or
GRANT
statement, use the name
authentication_pam
. For example:
CREATE USERuser
IDENTIFIED WITH authentication_pam AS 'authentication_string
';
The authentication string specifies the following types of information:
PAM supports the notion of “service name,” which is a name that the system administrator can use to configure the authentication method for a particular application. There can be several such “applications” associated with a single database server instance, so the choice of service name is left to the SQL application developer. When you define an account that should authenticate using PAM, specify the service name in the authentication string.
PAM provides a way for a PAM module to return to the server a MySQL user name other than the login name supplied at login time. Use the authentication string to control the mapping between login name and MySQL user name. If you want to take advantage of proxy user capabilities, the authentication string must include this kind of mapping.
For example, if the service name is mysql
and users in the root
and
users
PAM groups should be mapped to the
developer
and data_entry
users, respectively, use a statement like this:
CREATE USER user
IDENTIFIED WITH authentication_pam
AS 'mysql, root=developer, users=data_entry';
Authentication string syntax for the PAM authentication plugin follows these rules:
The string consists of a PAM service name, optionally followed by a group mapping list consisting of one or more keyword/value pairs each specifying a group name and a SQL user name:
pam_service_name
[,group_name
=sql_user_name
]...
Each
pair must be preceded by a comma.
group_name
=sql_user_name
Leading and trailing spaces not inside double quotation marks are ignored.
Unquoted pam_service_name
,
group_name
, and
sql_user_name
values can
contain anything except equal sign, comma, or space.
If a pam_service_name
,
group_name
, or
sql_user_name
value is quoted
with double quotation marks, everything between the
quotation marks is part of the value. This is necessary,
for example, if the value contains space characters. All
characters are legal except double quotation mark and
backslash ('\'
). To include either
character, escape it with a backslash.
The plugin parses the authentication string on each login check. To minimize overhead, keep the string as short as possible.
If the plugin successfully authenticates a login name, it looks for a group mapping list in the authentication string and uses it to return a different user name to the MySQL server based on the groups the external user is a member of:
If the authentication string contains no group mapping list, the plugin returns the login name.
If the authentication string does contain a group mapping
list, the plugin examines each
pair in the list from left to right and tries to find a
match for the group_name
=sql_user_name
group_name
value
in a non-MySQL directory of the groups assigned to the
authenticated user and returns
sql_user_name
for the first
match it finds. If the plugin finds no match for any
group, it returns the login name. If the plugin is not
capable of looking up a group in a directory, it ignores
the group mapping list and returns the login name.
The following sections describe how to set up several authentication scenarios that use the PAM authentication plugin:
No proxy users. This uses PAM only to check login names and passwords. Every external user permitted to connect to MySQL Server should have a matching MySQL account that is defined to use external PAM authentication. Authentication can be performed by various PAM-supported methods. The discussion shows how to use traditional Unix passwords and LDAP.
PAM authentication, when not done through proxy users or groups, requires the MySQL account to have the same user name as the Unix account. Because MySQL user names are limited to 16 characters (see Section 6.2.2, “Privilege System Grant Tables”), this limits PAM nonproxy authentication to Unix accounts with names of at most 16 characters.
Proxy login only and group mapping. For this scenario, create a few MySQL accounts that define different sets of privileges. (Ideally, nobody should log in through these directly.) Then define a default user authenticating through PAM that uses some mapping scheme (usually by the external groups the users are in) to map all the external logins to the few MySQL accounts holding the privilege sets. Any user that logs in is mapped to one of the MySQL accounts and uses its privileges. The discussion shows how to set this up using Unix passwords, but other PAM methods such as LDAP could be used instead.
Variations on these scenarios are possible. For example, you can permit some users to log in directly but require others to connect through proxy users.
The examples make the following assumptions. You might need to make some adjustments if your system is set up differently.
The PAM configuration directory is
/etc/pam.d
.
The PAM service name is mysql
, which
means that you must set up a PAM file named
mysql
in the PAM configuration
directory (creating the file if it does not exist). If you
use a different service name, the file name will be
different and you must use a different name in the
AS
clause of
CREATE USER
and
GRANT
statements.
The examples use a login name of
antonio
and password of
verysecret
. Change these to correspond
to the users you want to authenticate.
The PAM authentication plugin checks at initialization time
whether the AUTHENTICATION_PAM_LOG
environment value is set. If so, the plugin enables logging of
diagnostic messages to the standard output. These messages may
be helpful for debugging PAM-related problems that occur when
the plugin performs authentication. For more information, see
Section 6.3.6.2.3, “PAM Authentication Plugin Debugging”.
This authentication scenario uses PAM only to check Unix user login names and passwords. Every external user permitted to connect to MySQL Server should have a matching MySQL account that is defined to use external PAM authentication.
Verify that Unix authentication in PAM permits you to
log in as antonio
with password
verysecret
.
Set up PAM to authenticate the mysql
service. Put the following in
/etc/pam.d/mysql
:
#%PAM-1.0 auth include password-auth account include password-auth
Create a MySQL account with the same user name as the Unix login name and define it to authenticate using the PAM plugin:
CREATE USER 'antonio'@'localhost' IDENTIFIED WITH authentication_pam AS 'mysql'; GRANT ALL PRIVILEGES ON mydb.* TO 'antonio'@'localhost';
Try to connect to the MySQL server using the mysql command-line client. For example:
mysql --user=antonio --password=verysecret mydb
The server should permit the connection and the following query should return output as shown:
mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+-------------------+-------------------+--------------+
| USER() | CURRENT_USER() | @@proxy_user |
+-------------------+-------------------+--------------+
| antonio@localhost | antonio@localhost | NULL |
+-------------------+-------------------+--------------+
This shows that antonio
uses the
privileges granted to the antonio
MySQL account, and that no proxying has occurred.
This authentication scenario uses PAM only to check LDAP user login names and passwords. Every external user permitted to connect to MySQL Server should have a matching MySQL account that is defined to use external PAM authentication.
Verify that LDAP authentication in PAM permits you to
log in as antonio
with password
verysecret
.
Set up PAM to authenticate the mysql
service through LDAP. Put the following in
/etc/pam.d/mysql
:
#%PAM-1.0 auth required pam_ldap.so account required pam_ldap.so
If PAM object files have a suffix different from
.so
on your system, substitute the
correct suffix.
MySQL account creation and connecting to the server is the same as previously described in Section 6.3.6.2.2.1, “Unix Password Authentication without Proxy Users”.
This authentication scheme uses proxying and group mapping to map users who connect to the MySQL server through PAM onto a few MySQL accounts that define different sets of privileges. Users do not connect directly through the accounts that define the privileges. Instead, they connect through a default proxy user authenticating through PAM that uses a mapping scheme to map all the external logins to the few MySQL accounts holding the privileges. Any user who connects is mapped to one of the MySQL accounts and uses its privileges.
The procedure shown here uses Unix password authentication. To use LDAP instead, see the early steps of Section 6.3.6.2.2.2, “LDAP Authentication without Proxy Users”.
Verify that Unix authentication in PAM permits you to
log in as antonio
with password
verysecret
and that
antonio
is a member of the
root
or users
group.
Set up PAM to authenticate the mysql
service. Put the following in
/etc/pam.d/mysql
:
#%PAM-1.0 auth include password-auth account include password-auth
Create the default proxy user that maps the external PAM
users to the proxied accounts. It maps external users
from the root
PAM group to the
developer
MySQL account and the
external users from the users
PAM
group to the data_entry
MySQL
account:
CREATE USER ''@'' IDENTIFIED WITH authentication_pam AS 'mysql, root=developer, users=data_entry';
The mapping list following the service name is required when you set up proxy users. Otherwise, the plugin cannot tell how to map the name of PAM groups to the proper proxied user name.
Create the proxied accounts that will be used to access the databases:
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'very secret password
'; GRANT ALL PRIVILEGES ON mydevdb.* TO 'developer'@'localhost'; CREATE USER 'data_entry'@'localhost' IDENTIFIED BY 'very secret password
'; GRANT ALL PRIVILEGES ON mydb.* TO 'data_entry'@'localhost';
If you do not let anyone know the passwords for these
accounts, other users cannot use them to connect
directly to the MySQL server. Instead, it is expected
that users will authenticate using PAM and that they
will use the developer
or
data_entry
account by proxy based on
their PAM group.
Grant the PROXY
privilege
to the proxy account for the proxied accounts:
GRANT PROXY ON 'developer'@'localhost' TO ''@''; GRANT PROXY ON 'data_entry'@'localhost' TO ''@'';
Try to connect to the MySQL server using the mysql command-line client. For example:
mysql --user=antonio --password=verysecret mydb
The server authenticates the connection using the
''@''
account. The privileges
antonio
will have depends on what PAM
groups he is a member of. If antonio
is
a member of the root
PAM group, the
PAM plugin maps root
to the
developer
MySQL user name and returns
that name to the server. The server verifies that
''@''
has the
PROXY
privilege for
developer
and permits the connection.
the following query should return output as shown:
mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+-------------------+---------------------+--------------+
| USER() | CURRENT_USER() | @@proxy_user |
+-------------------+---------------------+--------------+
| antonio@localhost | developer@localhost | ''@'' |
+-------------------+---------------------+--------------+
This shows that antonio
uses the
privileges granted to the developer
MySQL account, and that proxying occurred through the
default proxy user account.
If antonio
is not a member of the
root
PAM group but is a member of the
users
group, a similar process
occurs, but the plugin maps user
group membership to the data_entry
MySQL user name and returns that name to the server. In
this case, antonio
uses the
privileges of the data_entry
MySQL
account:
mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+-------------------+----------------------+--------------+
| USER() | CURRENT_USER() | @@proxy_user |
+-------------------+----------------------+--------------+
| antonio@localhost | data_entry@localhost | ''@'' |
+-------------------+----------------------+--------------+
The PAM authentication plugin checks at initialization time
whether the AUTHENTICATION_PAM_LOG
environment value is set (the value does not matter). If so,
the plugin enables logging of diagnostic messages to the
standard output. These messages may be helpful for debugging
PAM-related problems that occur when the plugin performs
authentication.
Some messages include reference to PAM plugin source files and line numbers, which enables plugin actions to be tied more closely to the location in the code where they occur.
The following transcript demonstrates the kind of information produced by enabling logging. It resulted from a successful proxy authentication attempt.
entering auth_pam_server entering auth_pam_next_token auth_pam_next_token:reading at [cups,admin=writer,everyone=reader], sep=[,] auth_pam_next_token:state=PRESPACE, ptr=[cups,admin=writer,everyone=reader], out=[] auth_pam_next_token:state=IDENT, ptr=[cups,admin=writer,everyone=reader], out=[] auth_pam_next_token:state=AFTERSPACE, ptr=[,admin=writer,everyone=reader], out=[cups] auth_pam_next_token:state=DELIMITER, ptr=[,admin=writer,everyone=reader], out=[cups] auth_pam_next_token:state=DONE, ptr=[,admin=writer,everyone=reader], out=[cups] leaving auth_pam_next_token on /Users/gkodinov/mysql/work/x-5.5.16-release-basket/release/plugin/pam-authentication-plugin/src/parser.c:191 auth_pam_server:password 12345qq received auth_pam_server:pam_start rc=0 auth_pam_server:pam_set_item(PAM_RUSER,gkodinov) rc=0 auth_pam_server:pam_set_item(PAM_RHOST,localhost) rc=0 entering auth_pam_server_conv auth_pam_server_conv:PAM_PROMPT_ECHO_OFF [Password:] received leaving auth_pam_server_conv on /Users/gkodinov/mysql/work/x-5.5.16-release-basket/release/plugin/pam-authentication-plugin/src/authentication_pam.c:257 auth_pam_server:pam_authenticate rc=0 auth_pam_server:pam_acct_mgmt rc=0 auth_pam_server:pam_setcred(PAM_ESTABLISH_CRED) rc=0 auth_pam_server:pam_get_item rc=0 auth_pam_server:pam_setcred(PAM_DELETE_CRED) rc=0 entering auth_pam_map_groups entering auth_pam_walk_namevalue_list auth_pam_walk_namevalue_list:reading at: [admin=writer,everyone=reader] entering auth_pam_next_token auth_pam_next_token:reading at [admin=writer,everyone=reader], sep=[=] auth_pam_next_token:state=PRESPACE, ptr=[admin=writer,everyone=reader], out=[] auth_pam_next_token:state=IDENT, ptr=[admin=writer,everyone=reader], out=[] auth_pam_next_token:state=AFTERSPACE, ptr=[=writer,everyone=reader], out=[admin] auth_pam_next_token:state=DELIMITER, ptr=[=writer,everyone=reader], out=[admin] auth_pam_next_token:state=DONE, ptr=[=writer,everyone=reader], out=[admin] leaving auth_pam_next_token on /Users/gkodinov/mysql/work/x-5.5.16-release-basket/release/plugin/pam-authentication-plugin/src/parser.c:191 auth_pam_walk_namevalue_list:name=[admin] entering auth_pam_next_token auth_pam_next_token:reading at [writer,everyone=reader], sep=[,] auth_pam_next_token:state=PRESPACE, ptr=[writer,everyone=reader], out=[] auth_pam_next_token:state=IDENT, ptr=[writer,everyone=reader], out=[] auth_pam_next_token:state=AFTERSPACE, ptr=[,everyone=reader], out=[writer] auth_pam_next_token:state=DELIMITER, ptr=[,everyone=reader], out=[writer] auth_pam_next_token:state=DONE, ptr=[,everyone=reader], out=[writer] leaving auth_pam_next_token on /Users/gkodinov/mysql/work/x-5.5.16-release-basket/release/plugin/pam-authentication-plugin/src/parser.c:191 walk, &error_namevalue_list:value=[writer] entering auth_pam_map_group_to_user auth_pam_map_group_to_user:pam_user=gkodinov, name=admin, value=writer examining member root examining member gkodinov substitution was made to mysql user writer leaving auth_pam_map_group_to_user on /Users/gkodinov/mysql/work/x-5.5.16-release-basket/release/plugin/pam-authentication-plugin/src/authentication_pam.c:118 auth_pam_walk_namevalue_list:found mapping leaving auth_pam_walk_namevalue_list on /Users/gkodinov/mysql/work/x-5.5.16-release-basket/release/plugin/pam-authentication-plugin/src/parser.c:270 auth_pam_walk_namevalue_list returned 0 leaving auth_pam_map_groups on /Users/gkodinov/mysql/work/x-5.5.16-release-basket/release/plugin/pam-authentication-plugin/src/authentication_pam.c:171 auth_pam_server:authenticated_as=writer auth_pam_server: rc=0 leaving auth_pam_server on /Users/gkodinov/mysql/work/x-5.5.16-release-basket/release/plugin/pam-authentication-plugin/src/authentication_pam.c:429
As of MySQL 5.5.16, commercial distributions of MySQL for Windows include an authentication plugin that performs external authentication on Windows, enabling MySQL Server to use native Windows services to authenticate client connections. Users who have logged in to Windows can connect from MySQL client programs to the server based on the information in their environment without specifying an additional password.
The client and server exchange data packets in the authentication handshake. As a result of this exchange, the server creates a security context object that represents the identity of the client in the Windows OS. This identity includes the name of the client account. The Windows authentication plugin uses the identity of the client to check whether it is a given account or a member of a group. By default, negotiation uses Kerberos to authenticate, then NTLM if Kerberos is unavailable.
The Windows authentication plugin provides these capabilities:
External authentication: The plugin enables MySQL Server to accept connections from users defined outside the MySQL grant tables.
Proxy user support: The plugin can return to MySQL a user
name different from the client user. This means that the
plugin can return the MySQL user that defines the privileges
the external Windows-authenticated user should have. For
example, a Windows user named joe
can
connect and have the privileges of the MySQL user named
developer
.
The following table shows the plugin and library file names. The
file location must be the directory named by the
plugin_dir
system variable. For
installation information, see
Section 6.3.6.3.1, “Installing the Windows Authentication Plugin”.
Table 6.11. MySQL Windows Authentication Plugin
Server-side plugin name | authentication_windows |
Client-side plugin name | authentication_windows_client |
Library object file name | authentication_windows.dll |
The library file includes only the server-side plugin. As of
MySQL 5.5.13, the client-side plugin is built into the
libmysql
client library.
The server-side Windows authentication plugin is included only in commercial distributions. It is not included in MySQL community distributions. The client-side plugin is included in all distributions, including community distributions. This permits clients from any 5.5.13 or newer distribution to connect to a server that has the server-side plugin loaded.
The Windows authentication plugin should work on Windows 2000 Professional and up. It requires MySQL Server 5.5.16 or newer.
For general information about pluggable authentication in MySQL, see Section 6.3.6, “Pluggable Authentication”. For proxy user information, see Section 6.3.7, “Proxy Users”.
The Windows authentication plugin must be installed in the
MySQL plugin directory (the directory named by the
plugin_dir
system variable).
To enable the plugin, start the server with the
--plugin-load
option. For
example, put these lines in your my.ini
file:
[mysqld] plugin-load=authentication_windows.dll
Use the plugin name authentication_windows
in the IDENTIFIED WITH
clause of
CREATE USER
or
GRANT
statements for MySQL
accounts that should be authenticated with this plugin.
You can also use a
--plugin-dir=
option if it is necessary to tell the server the location of
the plugin directory.
path_name
To verify plugin installation, examine the
INFORMATION_SCHEMA.PLUGINS
table
or use the SHOW PLUGINS
statement. See Section 5.1.7.2, “Obtaining Server Plugin Information”.
The Windows authentication plugin supports the use of MySQL
accounts such that users who have logged in to Windows can
connect to the MySQL server without having to specify an
additional password. It is assumed that the server-side plugin
is enabled and that client programs are recent enough to
include the client-side plugin built into
libmysql
(MySQL 5.5.13 or higher). Once the
DBA has enabled the server-side plugin and set up accounts to
use it, clients can connect using those accounts with no other
setup required on their part.
To refer to the Windows authentication plugin in the
IDENTIFIED WITH
clause of a
CREATE USER
or
GRANT
statement, use the name
authentication_windows
. Suppose that the
Windows users Rafal
and
Tasha
should be permitted to connect to
MySQL, as well as any users in the
Administrators
or Power
Users
group. To set this up, create a MySQL account
named sql_admin
that uses the Windows
plugin for authentication:
CREATE USER sql_admin IDENTIFIED WITH authentication_windows AS 'Rafal, Tasha, Administrators, "Power Users"';
The plugin name is authentication_windows
.
The string following the AS
keyword is the
authentication string. It specifies that the Windows users
named Rafal
or Tasha
are
permitted to authenticate to the server as the MySQL user
sql_admin
, as are any Windows users in the
Administrators
or Power
Users
group. The latter group name contains a space,
so it must be quoted with double quote characters.
After you create the sql_admin
account, a
user who has logged in to Windows can attempt to connect to
the server using that account:
C:\> mysql --user=sql_admin
No password is required here. The
authentication_windows
plugin uses the
Windows security API to check which Windows user is
connecting. If that user is named Rafal
or
Tasha
, or is in the
Administrators
or Power
Users
group, the server grants access and the client
is authenticated as sql_admin
and has
whatever privileges are granted to the
sql_admin
account. Otherwise, the server
denies access.
Authentication string syntax for the Windows authentication plugin follows these rules:
The string consists of one or more user mappings separated by commas.
Each user mapping associates a Windows user or group name with a MySQL user name:
win_user_or_group_name=sql_user_name
win_user_or_group_name
For the latter syntax, with no
sql_user_name
value given, the
implicit value is the MySQL user created by the
CREATE USER
statement.
Thus, these statements are equivalent:
CREATE USER sql_admin IDENTIFIED WITH authentication_windows AS 'Rafal, Tasha, Administrators, "Power Users"'; CREATE USER sql_admin IDENTIFIED WITH authentication_windows AS 'Rafal=sql_admin, Tasha=sql_admin, Administrators=sql_admin, "Power Users"=sql_admin';
Each backslash ('\'
) in a value must be
doubled because backslash is the escape character in MySQL
strings.
Leading and trailing spaces not inside double quotation marks are ignored.
Unquoted win_user_or_group_name
and sql_user_name
values can
contain anything except equal sign, comma, or space.
If a win_user_or_group_name
and
or sql_user_name
value is
quoted with double quotation marks, everything between the
quotation marks is part of the value. This is necessary,
for example, if the name contains space characters. All
characters within double quotes are legal except double
quotation mark and backslash. To include either character,
escape it with a backslash.
win_user_or_group_name
values
use conventional syntax for Windows principals, either
local or in a domain. Examples (note the doubling of
backslashes):
domain\\user .\\user domain\\group .\\group BUILTIN\\WellKnownGroup
When invoked by the server to authenticate a client, the
plugin scans the authentication string left to right for a
user or group match to the Windows user. If there is a match,
the plugin returns the corresponding
sql_user_name
to the MySQL server.
If there is no match, authentication fails.
A user name match takes preference over a group name match.
Suppose that the Windows user named
win_user
is a member of
win_group
and the authentication string
looks like this:
'win_group = sql_user1, win_user = sql_user2'
When win_user
connects to the MySQL server,
there is a match both to win_group
and to
win_user
. The plugin authenticates the user
as sql_user2
because the more-specific user
match takes precedence over the group match, even though the
group is listed first in the authentication string.
Windows authentication always works for connections from the same computer on which the server is running. For cross-computer connections, both computers must be registered with Windows Active Directory. If they are in the same Windows domain, it is unnecessary to specify a domain name. It is also possible to permit connections from a different domain, as in this example:
CREATE USER sql_accounting IDENTIFIED WITH authentication_windows AS 'SomeDomain\\Accounting';
Here SomeDomain
is the name of the other
domain. The backslash character is doubled because it is the
MySQL escape character within strings.
MySQL supports the concept of proxy users whereby a client can connect and authenticate to the MySQL server using one account but while connected has the privileges of another account (see Section 6.3.7, “Proxy Users”). Suppose that you want Windows users to connect using a single user name but be mapped based on their Windows user and group names onto specific MySQL accounts as follows:
The local_user
and
MyDomain\domain_user
local and domain
Windows users should map to the
local_wlad
MySQL account.
Users in the MyDomain\Developers
domain
group should map to the local_dev
MySQL
account.
Local machine administrators should map to the
local_admin
MySQL account.
To set this up, create a proxy account for Windows users to
connect to, and configure this account so that users and
groups map to the appropriate MySQL accounts
(local_wlad
, local_dev
,
local_admin
). In addition, grant the MySQL
accounts the privileges appropriate to the operations they
need to perform. The following instructions use
win_proxy
as the proxy account, and
local_wlad
, local_dev
,
and local_admin
as the proxied accounts.
Create the proxy MySQL account:
CREATE USER win_proxy IDENTIFIED WITH authentication_windows AS 'local_user = local_wlad, MyDomain\\domain_user = local_wlad, MyDomain\\Developers = local_dev, BUILTIN\\Administrators = local_admin';
For proxying to work, the proxied accounts must exist, so create them:
CREATE USER local_wlad IDENTIFIED BY 'wlad_pass'; CREATE USER local_dev IDENTIFIED BY 'dev_pass'; CREATE USER local_admin IDENTIFIED BY 'admin_pass';
If you do not let anyone know the passwords for these accounts, other users cannot use them to connect directly to the MySQL server.
You should also issue GRANT
statements (not shown) that grant each proxied account the
privileges it needs.
The proxy account must have the
PROXY
privilege for each of
the proxied accounts:
GRANT PROXY ON local_wlad TO win_proxy; GRANT PROXY ON local_dev TO win_proxy; GRANT PROXY ON local_admin TO win_proxy;
Now the Windows users local_user
and
MyDomain\domain_user
can connect to the
MySQL server as win_proxy
and when
authenticated have the privileges of the account given in the
authentication string—in this case,
local_wlad
. A user in the
MyDomain\Developers
group who connects as
win_proxy
has the privileges of the
local_dev
account. A user in the
BUILTIN\Administrators
group has the
privileges of the local_admin
account.
To configure authentication so that all Windows users who do
not have their own MySQL account go through a proxy account,
substitute the default proxy user (''@''
)
for win_proxy
in the preceding
instructions. For information about the default proxy user,
see Section 6.3.7, “Proxy Users”.
To use the Windows authentication plugin with Connector/Net connection strings in Connection/Net 6.4.4 and higher, see Section 22.2.5.5, “Using the Windows Native Authentication Plugin”.
Additional control over the Windows authentication plugin is
provided by the
authentication_windows_use_principal_name
and
authentication_windows_log_level
system variables. See
Section 5.1.3, “Server System Variables”.
As of MySQL 5.5.10, 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. MySQL Clear Text Authentication Plugin
Server-side plugin name | None, see discussion |
Client-side plugin name | mysql_clear_password |
Library object file name | None (built in) |
With native MySQL authentication, the client performs one-way hashing on the password before sending it to the server. This enables the client to avoid sending the password in clear text. See Section 6.1.2.3, “Password Hashing in MySQL”. However, because the hash algorithm is one way, the original password cannot be recovered on the server side.
One-way hashing cannot be done for authentication schemes that
require the server to receive the password as entered on the
client side. In such cases, the
mysql_clear_password
client-side plugin can
be 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 clear
text password.
For general information about pluggable authentication in MySQL, see Section 6.3.6, “Pluggable Authentication”.
Sending passwords in clear text may be a security problem in some configurations. To avoid problems if there is any possibility that the password would be intercepted, clients should connect to MySQL Server using a method that protects the password. Possibilities include SSL (see Section 6.3.8, “Using SSL for Secure Connections”), IPsec, or a private network.
As of MySQL 5.5.10, a server-side authentication plugin is available that authenticates clients that connect from the local host through the Unix socket file. This plugin works only on Linux systems.
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 name suffix might differ on your system. The file location
is the directory named by the
plugin_dir
system variable. For
installation information, see
Section 6.3.6, “Pluggable Authentication”.
Table 6.13. MySQL Socket Peer-Credential Authentication Plugin
Server-side plugin name | auth_socket |
Client-side plugin name | None, see discussion |
Library object file name | auth_socket.so |
The 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. The
plugin checks whether the user name matches the MySQL user name
specified by the client program to the server, and permits the
connection only if the names match. The plugin can be built only
on systems that support the SO_PEERCRED
option, such as Linux.
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.
For general information about pluggable authentication in MySQL, see Section 6.3.6, “Pluggable Authentication”.
MySQL includes a test plugin that authenticates using MySQL native authentication, but is a loadable plugin (not built in) and must be installed prior to use. It can authenticate against either normal or older (shorter) password hash values.
This plugin is intended for testing and development purposes, and not for use in production environments. 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.
The following table shows the plugin and library file names. The
file name suffix might differ on your system. The file location
is the directory named by the
plugin_dir
system variable. For
installation information, see
Section 6.3.6, “Pluggable Authentication”.
Table 6.14. MySQL Test Authentication Plugin
Server-side plugin name | test_plugin_server |
Client-side plugin name | auth_test_plugin |
Library object file name | auth_test_plugin.so |
Because the test plugin authenticates the same way as native
MySQL authentication, provide the usual
--user
and
--password
options that you
normally use for accounts that use native authentication when
you connect to the server. For example:
shell> mysql --user=your_name
--password=your_pass
For general information about pluggable authentication in MySQL, see Section 6.3.6, “Pluggable Authentication”.
When authentication to the MySQL server occurs by means of an authentication plugin, the plugin 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 have the privileges of the second user. In other words, the external user is a “proxy user” (a user who can impersonate or become known as another user) and the second user is a “proxied user” (a user whose identity can be taken on by a proxy user).
This section describes how the proxy user capability works. For general information about authentication plugins, see Section 6.3.6, “Pluggable Authentication”. If you are interested in writing your own authentication plugins that support proxy users, see Section 23.2.4.9.4, “Implementing Proxy User Support in Authentication Plugins”.
For proxying to occur, these conditions must be satisfied:
When a connecting client should be treated as a proxy user, the plugin must return a different name, to indicate the proxied user name.
A proxy user account must be set up to be authenticated by the
plugin. Use the CREATE USER
or
GRANT
statement to associate an
account with a plugin.
A proxy user account must have the
PROXY
privilege for the proxied
account. Use the GRANT
statement for this.
Consider the following definitions:
CREATE USER 'empl_external'@'localhost' IDENTIFIED WITH auth_plugin AS 'auth_string'; CREATE USER 'employee'@'localhost' IDENTIFIED BY 'employee_pass'; GRANT PROXY ON 'employee'@'localhost' TO 'empl_external'@'localhost';
When a client connects as empl_external
from
the local host, MySQL uses auth_plugin
to
perform authentication. If auth_plugin
returns
the employee
user name to the server (based on
the content of 'auth_string'
and perhaps by
consulting some external authentication system), that serves as a
request to the server to treat this client, for purposes of
privilege checking, as the employee
local user.
In this case, empl_external
is the proxy user
and employee
is the proxied user.
The server verifies that proxy authentication for
employee
is possible for the
empl_external
user by checking whether
empl_external
has the
PROXY
privilege for
employee
. (If this privilege had not been
granted, an error would occur.)
When proxying occurs, the USER()
and
CURRENT_USER()
functions can be used to see the
difference between the connecting user and the account whose
privileges apply during the current session. For the example just
described, those functions return these values:
mysql> SELECT USER(), CURRENT_USER();
+-------------------------+--------------------+
| USER() | CURRENT_USER() |
+-------------------------+--------------------+
| empl_external@localhost | employee@localhost |
+-------------------------+--------------------+
The IDENTIFIED WITH
clause that names the
authentication plugin may be followed by an AS
clause specifying a string that the server passes to the plugin
when the user connects. It is up to each plugin whether the
AS
clause is required. If it is required, 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.
A special 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
';
proxy_user
must represent a valid
externally authenticated MySQL user at connection time or
connection attempts fail. proxied_user
must represent a valid locally authenticated user at connection
time or connection attempts fail.
The corresponding REVOKE
syntax is:
REVOKE PROXY ON 'proxied_user
' FROM 'proxy_user
';
MySQL GRANT
and
REVOKE
syntax extensions work as
usual. For example:
GRANT PROXY ON 'a' TO 'b', 'c', 'd'; GRANT PROXY ON 'a' TO 'd' IDENTIFIED BY ...; GRANT PROXY ON 'a' TO 'd' WITH GRANT OPTION; GRANT PROXY ON 'a' TO ''@''; REVOKE PROXY ON 'a' FROM 'b', 'c', 'd';
In the preceding example, ''@''
is the default
proxy user and means “any user.” The default proxy
user is discussed later in this section.
The PROXY
privilege can be granted
in these cases:
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.
By a user that has GRANT PROXY ... WITH GRANT
OPTION
for proxied_user
.
The root
account created by default during
MySQL installation has the
PROXY ... WITH GRANT
OPTION
privilege for ''@''
, that is,
for all users. This enables root
to set up
proxy users, as well as to delegate to other accounts the
authority to set up proxy users. For example,
root
can do this:
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'test'; GRANT PROXY ON ''@'' TO 'admin'@'localhost' WITH GRANT OPTION;
Now the admin
user can manage all the specific
GRANT PROXY
mappings. For example,
admin
can do this:
GRANT PROXY ON sally TO joe;
To specify that some or all users should connect using a given
external plugin, create a “blank” MySQL user, set it
up to use that plugin for authentication, and let the plugin
return the real authenticated user name (if different from the
blank user). For example, suppose that there exists a hypothetical
plugin named ldap_auth
that implements LDAP
authentication:
CREATE USER ''@'' IDENTIFIED WITH ldap_auth AS 'O=Oracle, OU=MySQL'; CREATE USER 'developer'@'localhost' IDENTIFIED BY 'developer_pass'; CREATE USER 'manager'@'localhost' IDENTIFIED BY 'manager_pass'; GRANT PROXY ON 'manager'@'localhost' TO ''@''; GRANT PROXY ON 'developer'@'localhost' TO ''@'';
Now assume that a client tries to connect as follows:
mysql --user=myuser --password='myuser_pass' ...
The server will not find myuser
defined as a
MySQL user. But because there is a blank user account
(''@''
), that matches the client user name and
host name, the server authenticates the client against that
account: The server invokes ldap_auth
, passing
it myuser
and myuser_pass
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
. The server verifies that
''@''
can authenticate as
developer
(because it has the
PROXY
privilege to do so) and
accepts the connection. The session proceeds with
myuser
having the privileges of
developer
. (These privileges should be set up
by the DBA using GRANT
statements, not shown.)
The USER()
and
CURRENT_USER()
functions return
these values:
mysql> SELECT USER(), CURRENT_USER();
+------------------+---------------------+
| USER() | CURRENT_USER() |
+------------------+---------------------+
| myuser@localhost | developer@localhost |
+------------------+---------------------+
If the plugin instead finds in the LDAP directory that
myuser
is a manager, it returns
manager
as the user name and the session
proceeds with myuser
having the privileges of
manager
.
mysql> SELECT USER(), CURRENT_USER();
+------------------+-------------------+
| USER() | CURRENT_USER() |
+------------------+-------------------+
| myuser@localhost | manager@localhost |
+------------------+-------------------+
For simplicity, external authentication cannot be multilevel:
Neither the credentials for developer
nor those
for manager
are taken into account in the
preceding example. However, they are still used if a client tries
to authenticate directly against the developer
or manager
account, which is why those accounts
should be assigned passwords.
The default proxy account uses ''
in the host
part, which matches any host. If you set up a default proxy user,
take care to also check for accounts with '%'
in the host part, because that also matches any host, but has
precedence over ''
by the rules that the server
uses to sort account rows internally (see
Section 6.2.4, “Access Control, Stage 1: Connection Verification”).
Suppose that a MySQL installation includes these two accounts:
CREATE USER ''@'' IDENTIFIED WITH some_plugin; CREATE USER ''@'%' IDENTIFIED BY 'some_password';
The intent of the first account is to serve as the default proxy user, to be used to authenticate connections for users who do not otherwise match a more-specific account. The second account might have been created, for example, to enable users without their own account as the anonymous user.
However, in this configuration, the first account will never be
used because the matching rules sort ''@'%'
ahead of ''@''
. For accounts that do not match
any more-specific account, the server will attempt to authenticate
them against ''@'%'
rather than
''@''
.
If you intend to create a default proxy user, check for other existing “match any user” accounts that will take precedence over the default proxy user and thus prevent that user from working as intended. It may be necessary to remove any such accounts.
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 default proxy account, this variable
will be set as follows:
mysql> SELECT @@proxy_user;
+--------------+
| @@proxy_user |
+--------------+
| ''@'' |
+--------------+
external_user
: Sometimes the
authentication plugin may use an external user to authenticate
to the MySQL server. For example, when using Windows native
authentication, a plugin that authenticates using the windows
API does not need the login ID passed to it. However, it still
uses an Windows user ID to authenticate. The plugin may return
this external user ID (or the first 512 UTF-8 bytes of it) to
the server using the external_user
read-only session variable. If the plugin does not set this
variable, its value is NULL
.
MySQL supports secure (encrypted) connections between MySQL
clients and the server using the Secure Sockets Layer (SSL)
protocol. This section discusses how to use SSL connections. For
information on how to require users to use SSL connections, see
the discussion of the REQUIRE
clause of the
GRANT
statement in
Section 13.7.1.3, “GRANT
Syntax”.
The standard configuration of MySQL is intended to be as fast as possible, so encrypted connections are not used by default. For applications that require the security provided by encrypted connections, the extra computation to encrypt the data is worthwhile.
MySQL enables encryption on a per-connection basis. You can choose a normal unencrypted connection or a secure encrypted SSL connection according the requirements of individual applications.
Secure connections are based on the OpenSSL API and are available through the MySQL C API. Replication uses the C API, so secure connections can be used between master and slave servers.
Another way to connect securely is from within an SSH connection to the MySQL server host. For an example, see Section 6.3.9, “Connecting to MySQL Remotely from Windows with SSH”.
To understand how MySQL uses SSL, it is necessary to explain some basic SSL and X509 concepts. People who are familiar with these can skip this part of the discussion.
By default, MySQL uses unencrypted connections between the
client and the server. This means that someone with access to
the network could watch all your traffic and look at the data
being sent or received. They could even change the data while it
is in transit between client and server. To improve security a
little, you can compress client/server traffic by using the
--compress
option when invoking client
programs. However, this does not foil a determined attacker.
When you need to move information over a network in a secure fashion, an unencrypted connection is unacceptable. Encryption is the way to make any kind of data unreadable. In fact, today's practice requires many additional security elements from encryption algorithms. They should resist many kind of known attacks such as changing the order of encrypted messages or replaying data twice.
SSL is a protocol that uses different encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect any data change, loss, or replay. SSL also incorporates algorithms that provide identity verification using the X509 standard.
X509 makes it possible to identify someone on the Internet. It is most commonly used in e-commerce applications. In basic terms, there should be some company 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 show the certificate to another party as proof of identity. A certificate consists of its owner's public key. Any data encrypted with this public key can be decrypted only using the corresponding secret key, which is held by the owner of the certificate.
If you need more information about SSL, X509, or encryption, use your favorite Internet search engine to search for the keywords in which you are interested.
To use SSL connections between the MySQL server and client programs, your system must support either OpenSSL or yaSSL and your version of MySQL must be built with SSL support.
To make it easier to use secure connections, MySQL is bundled with yaSSL. (MySQL and yaSSL employ the same licensing model, whereas OpenSSL uses an Apache-style license.) yaSSL support initially was available only for a few platforms, but now it is available on all MySQL platforms supported by Oracle Corporation.
To get secure connections to work with MySQL and SSL, you must do the following:
If you are not using a binary (precompiled) version of MySQL that has been built with SSL support, and you are going to use OpenSSL rather than the bundled yaSSL library, install OpenSSL if it has not already been installed. We have tested MySQL with OpenSSL 0.9.6. To obtain OpenSSL, visit http://www.openssl.org.
Building MySQL using OpenSSL requires a shared OpenSSL library, otherwise linker errors occur. Alternatively, build MySQL using yaSSL.
If you are not using a binary (precompiled) version of MySQL that has been built with SSL support, configure a MySQL source distribution to use SSL. When you configure MySQL, invoke CMake like this:
shell> cmake . -DWITH_SSL=bundled
That configures the distribution to use the bundled yaSSL
library. To use the system SSL library instead, specify the
option as -DWITH_SSL=system
instead. See Section 2.9.4, “MySQL Source-Configuration Options”.
Note that yaSSL support on Unix platforms requires that
either /dev/urandom
or
/dev/random
be available to retrieve
true random numbers. For additional information (especially
regarding yaSSL on Solaris versions prior to 2.8 and HP-UX),
see Bug #13164.
Make sure that the user
in the
mysql
database includes the SSL-related
columns (beginning with ssl_
and
x509_
). If your user
table does not have these columns, it must be upgraded; see
Section 4.4.7, “mysql_upgrade — Check Tables for MySQL Upgrade”.
To check whether a server binary is compiled with SSL
support, invoke it with the
--ssl
option. An error will
occur if the server does not support SSL:
shell> mysqld --ssl --help
060525 14:18:52 [ERROR] mysqld: unknown option '--ssl'
To check whether a running mysqld server
supports SSL, examine the value of the
have_ssl
system variable
(if you have no have_ssl
variable, check for
have_openssl
):
mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl | YES |
+---------------+-------+
If the value is YES
, the server supports
SSL connections. If the value is
DISABLED
, the server supports SSL
connections but was not started with the appropriate
--ssl-
options (described later in this section).
xxx
To enable SSL connections, the proper SSL-related options must be used (see Section 6.3.8.3, “SSL Command Options”).
To start the MySQL server so that it permits clients to connect using SSL, use the options that identify the key and certificate files the server needs when establishing a secure connection:
shell>mysqld --ssl-ca=
ca-cert.pem
\--ssl-cert=
server-cert.pem
\--ssl-key=
server-key.pem
--ssl-ca
identifies the
Certificate Authority (CA) certificate.
--ssl-cert
identifies the
server public key. This can be sent to the client and
authenticated against the CA certificate that it has.
--ssl-key
identifies the
server private key.
To establish a secure connection to a MySQL server with SSL
support, the options that a client must specify depend on the
SSL requirements of the user account that the client uses. (See
the discussion of the REQUIRE
clause in
Section 13.7.1.3, “GRANT
Syntax”.)
If the account has no special SSL requirements or was created
using a GRANT
statement that
includes the REQUIRE SSL
option, a client can
connect securely by using just the
--ssl-ca
option:
shell> mysql --ssl-ca=ca-cert.pem
To require that a client certificate also be specified, create
the account using the REQUIRE X509
option.
Then the client must also specify the proper client key and
certificate files or the server will reject the connection:
shell>mysql --ssl-ca=
ca-cert.pem
\--ssl-cert=
client-cert.pem
\--ssl-key=
client-key.pem
In other words, the options are similar to those used for the server. Note that the Certificate Authority certificate has to be the same.
A client can determine whether the current connection with the
server uses SSL by checking the value of the
Ssl_cipher
status variable.
The value of Ssl_cipher
is
nonempty if SSL is used, and empty otherwise. For example:
mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| Ssl_cipher | DHE-RSA-AES256-SHA |
+---------------+--------------------+
For the mysql client, you can 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-AES256-SHA
...
To establish a secure connection from within an application
program, use the mysql_ssl_set()
C API function to set the appropriate certificate options before
calling mysql_real_connect()
.
See Section 22.8.3.67, “mysql_ssl_set()
”. After the connection is
established, you can use
mysql_get_ssl_cipher()
to
determine whether SSL is in use. A non-NULL
return value indicates a secure connection and names the SSL
cipher used for encryption. A NULL
return
value indicates that SSL is not being used. See
Section 22.8.3.33, “mysql_get_ssl_cipher()
”.
The following list describes options that are used for specifying the use of SSL, certificate files, and key files. They can be given on the command line or in an option file. These options are not available unless MySQL has been built with SSL support. See Section 6.3.8.2, “Using SSL Connections”.
Table 6.15. SSL Option/Variable Summary
Name | Cmd-Line | Option file | System Var | Status Var | Var Scope | Dynamic |
---|---|---|---|---|---|---|
have_openssl | Yes | Global | No | |||
have_ssl | Yes | Global | No | |||
skip-ssl | Yes | Yes | ||||
ssl | Yes | Yes | ||||
ssl-ca | Yes | Yes | Global | No | ||
- Variable: ssl_ca | Yes | Global | No | |||
ssl-capath | Yes | Yes | Global | No | ||
- Variable: ssl_capath | Yes | Global | No | |||
ssl-cert | Yes | Yes | Global | No | ||
- Variable: ssl_cert | Yes | Global | No | |||
ssl-cipher | Yes | Yes | Global | No | ||
- Variable: ssl_cipher | Yes | Global | No | |||
ssl-key | Yes | Yes | Global | No | ||
- Variable: ssl_key | Yes | Global | No | |||
ssl-verify-server-cert | Yes | Yes |
For the server, this option specifies that the server
permits SSL connections. For a client program, it permits
the client to connect to the server using SSL. This option
is not sufficient in itself to cause an SSL connection to be
used. You must also specify the
--ssl-ca
option, and
possibly the --ssl-cert
and
--ssl-key
options.
This option is more often used in its opposite form to
override any other SSL options and indicate that SSL should
not be used. To do this, specify the
option as
--skip-ssl
or --ssl=0
.
Note that use of --ssl
does
not require an SSL connection. For
example, if the server or client is compiled without SSL
support, a normal unencrypted connection is used.
The secure way to require use of an SSL connection is to
create an account on the server that includes a
REQUIRE SSL
clause in the
GRANT
statement. Then use
that account to connect to the server, where both the server
and the client have SSL support enabled.
The REQUIRE
clause permits other
SSL-related restrictions as well. The description of
REQUIRE
in Section 13.7.1.3, “GRANT
Syntax”,
provides additional detail about which SSL command options
may or must be specified by clients that connect using
accounts that are created using the various
REQUIRE
options.
The path to a file that contains a list of trusted SSL CAs.
The path to a directory that contains trusted SSL CA certificates in PEM format.
The name of the SSL certificate file to use for establishing a secure connection.
A list of permissible ciphers to use for SSL encryption. For
greatest portability, cipher_list
should be a list of one or more cipher names, separated by
colons. Examples:
--ssl-cipher=AES128-SHA --ssl-cipher=DHE-RSA-AES256-SHA:AES128-SHA
This format is understood both by OpenSSL and yaSSL. OpenSSL supports a more flexible syntax for specifying ciphers, as described in the OpenSSL documentation at http://www.openssl.org/docs/apps/ciphers.html. However, this extended syntax will fail if used with a MySQL installation compiled against yaSSL.
If no cipher in the list is supported, SSL connections will not work.
The name of the SSL key file to use for establishing a secure connection.
If the key file is protected by a passphrase, and the MySQL distribution was built using OpenSSL, the program will prompt 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. An error occurs if the key file is protected by a passphrase for distributions built using yaSSL.
This option is available for client programs only, not the server. It causes the server's Common Name value in the certificate that the server sends to the client to be verified against the host name that the client uses for connecting to the server, and the connection is rejected if there is a mismatch. This feature can be used to prevent man-in-the-middle attacks. Verification is disabled by default.
If you use SSL when establishing a client connection, you can
tell the client not to authenticate the server certificate by
specifying neither --ssl-ca
nor
--ssl-capath
. The server still
verifies the client according to any applicable requirements
established using GRANT
statements for the client, and it still uses any
--ssl-ca
/--ssl-capath
values that were passed to server at startup time.
This section demonstrates how 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.
Following the third example, instructions are given for using the files to test SSL connections. You can also use the files as described in Section 6.3.8.2, “Using SSL Connections”.
The following example shows a set of commands to create MySQL server and client certificate and key files. You will need to respond to several prompts by the openssl commands. To generate test files, you can press Enter to all prompts. To generate files for production use, you should provide nonempty responses.
# Create clean environment shell>rm -rf newcerts
shell>mkdir newcerts && cd newcerts
# Create CA certificate shell>openssl genrsa 2048 > ca-key.pem
shell>openssl req -new -x509 -nodes -days 1000 \
-key ca-key.pem -out ca-cert.pem
# Create server certificate, remove passphrase, and sign it shell>openssl req -newkey rsa:2048 -days 1000 \
-nodes -keyout server-key.pem -out server-req.pem
shell>openssl rsa -in server-key.pem -out server-key.pem
shell>openssl x509 -req -in server-req.pem -days 1000 \
-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
# Create client certificate, remove passphrase, and sign it shell>openssl req -newkey rsa:2048 -days 1000 \
-nodes -keyout client-key.pem -out client-req.pem
shell>openssl rsa -in client-key.pem -out client-key.pem
shell>openssl x509 -req -in client-req.pem -days 1000 \
-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
After generating the certificates, verify them:
mysql> openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
Here is an example script that shows how to set up SSL certificates for MySQL:
DIR=`pwd`/openssl PRIV=$DIR/private mkdir $DIR $PRIV $DIR/newcerts cp /usr/share/ssl/openssl.cnf $DIR replace ./demoCA $DIR -- $DIR/openssl.cnf # Create necessary files: $database, $serial and $new_certs_dir # directory (optional) touch $DIR/index.txt echo "01" > $DIR/serial # # Generation of Certificate Authority(CA) # openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/ca-cert.pem \ -days 3600 -config $DIR/openssl.cnf # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Generating a 1024 bit RSA private key # ................++++++ # .........++++++ # writing new private key to '/home/monty/openssl/private/cakey.pem' # Enter PEM pass phrase: # Verifying password - Enter PEM pass phrase: # ----- # You are about to be asked to enter information that will be # incorporated into your certificate request. # What you are about to enter is what is called a Distinguished Name # or a DN. # There are quite a few fields but you can leave some blank # For some fields there will be a default value, # If you enter '.', the field will be left blank. # ----- # Country Name (2 letter code) [AU]:FI # State or Province Name (full name) [Some-State]:. # Locality Name (eg, city) []: # Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB # Organizational Unit Name (eg, section) []: # Common Name (eg, YOUR name) []:MySQL admin # Email Address []: # # Create server request and key # openssl req -new -keyout $DIR/server-key.pem -out \ $DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Generating a 1024 bit RSA private key # ..++++++ # ..........++++++ # writing new private key to '/home/monty/openssl/server-key.pem' # Enter PEM pass phrase: # Verifying password - Enter PEM pass phrase: # ----- # You are about to be asked to enter information that will be # incorporated into your certificate request. # What you are about to enter is what is called a Distinguished Name # or a DN. # There are quite a few fields but you can leave some blank # For some fields there will be a default value, # If you enter '.', the field will be left blank. # ----- # Country Name (2 letter code) [AU]:FI # State or Province Name (full name) [Some-State]:. # Locality Name (eg, city) []: # Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB # Organizational Unit Name (eg, section) []: # Common Name (eg, YOUR name) []:MySQL server # Email Address []: # # Please enter the following 'extra' attributes # to be sent with your certificate request # A challenge password []: # An optional company name []: # # Remove the passphrase from the key # openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem # # Sign server cert # openssl ca -policy policy_anything -out $DIR/server-cert.pem \ -config $DIR/openssl.cnf -infiles $DIR/server-req.pem # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Enter PEM pass phrase: # Check that the request matches the signature # Signature ok # The Subjects Distinguished Name is as follows # countryName :PRINTABLE:'FI' # organizationName :PRINTABLE:'MySQL AB' # commonName :PRINTABLE:'MySQL admin' # Certificate is to be certified until Sep 13 14:22:46 2003 GMT # (365 days) # Sign the certificate? [y/n]:y # # # 1 out of 1 certificate requests certified, commit? [y/n]y # Write out database with 1 new entries # Data Base Updated # # Create client request and key # openssl req -new -keyout $DIR/client-key.pem -out \ $DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Generating a 1024 bit RSA private key # .....................................++++++ # .............................................++++++ # writing new private key to '/home/monty/openssl/client-key.pem' # Enter PEM pass phrase: # Verifying password - Enter PEM pass phrase: # ----- # You are about to be asked to enter information that will be # incorporated into your certificate request. # What you are about to enter is what is called a Distinguished Name # or a DN. # There are quite a few fields but you can leave some blank # For some fields there will be a default value, # If you enter '.', the field will be left blank. # ----- # Country Name (2 letter code) [AU]:FI # State or Province Name (full name) [Some-State]:. # Locality Name (eg, city) []: # Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB # Organizational Unit Name (eg, section) []: # Common Name (eg, YOUR name) []:MySQL user # Email Address []: # # Please enter the following 'extra' attributes # to be sent with your certificate request # A challenge password []: # An optional company name []: # # Remove the passphrase from the key # openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem # # Sign client cert # openssl ca -policy policy_anything -out $DIR/client-cert.pem \ -config $DIR/openssl.cnf -infiles $DIR/client-req.pem # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Enter PEM pass phrase: # Check that the request matches the signature # Signature ok # The Subjects Distinguished Name is as follows # countryName :PRINTABLE:'FI' # organizationName :PRINTABLE:'MySQL AB' # commonName :PRINTABLE:'MySQL user' # Certificate is to be certified until Sep 13 16:45:17 2003 GMT # (365 days) # Sign the certificate? [y/n]:y # # # 1 out of 1 certificate requests certified, commit? [y/n]y # Write out database with 1 new entries # Data Base Updated # # Create a my.cnf file that you can use to test the certificates # cnf="" cnf="$cnf [client]" cnf="$cnf ssl-ca=$DIR/ca-cert.pem" cnf="$cnf ssl-cert=$DIR/client-cert.pem" cnf="$cnf ssl-key=$DIR/client-key.pem" cnf="$cnf [mysqld]" cnf="$cnf ssl-ca=$DIR/ca-cert.pem" cnf="$cnf ssl-cert=$DIR/server-cert.pem" cnf="$cnf ssl-key=$DIR/server-key.pem" echo $cnf | replace " " ' ' > $DIR/my.cnf
Download OpenSSL for Windows. An overview of available packages
can be seen here:
http://www.slproweb.com/products/Win32OpenSSL.html
Choose the Win32 OpenSSL Light or Win64 OpenSSL Light package,
depending on your architecture (32-bit or 64-bit). The default
installation location will be
C:\OpenSSL-Win32
or
C:\OpenSSL-Win64
, depending on which package
you downloaded. The following instructions assume a default
location of C:\OpenSSL-Win32
. Modify this as
necessary if you are using the 64-bit package.
if a message occurs during setup indicating
'...critical component is missing: Microsoft Visual C++
2008 Redistributables'
, cancel the setup and download
one of the following packages as well, again depending on your
architecture (32-bit or 64-bit):
Visual C++ 2008 Redistributables (x86), available at: http://www.microsoft.com/downloads/details.aspx?familyid=9B2DA534-3E03-4391-8A4D-074B9F2BC1BF
Visual C++ 2008 Redistributables (x64), available at: http://www.microsoft.com/downloads/details.aspx?familyid=bd2a6171-e2d6-4230-b809-9a8d7548c1b6
After installing the additional package, restart the OpenSSL setup.
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:
On the Windows desktop, right-click the My Computer icon, and select .
Select the
tab from the menu that appears, and click the button.Under System Variables, select , then click the button. The dialogue should appear.
Add ';C:\OpenSSL-Win32\bin'
to the end
(notice the semicolon).
Press OK 3 times.
Check that OpenSSL was correctly integrated into the Path variable by opening a new command console (Start>Run>cmd.exe) and verifying that OpenSSL is available:
Microsoft Windows [Version ...] Copyright (c) 2006 Microsoft Corporation. All rights reserved. C:\Windows\system32>cd \
C:\>openssl
OpenSSL>exit
<<< If you see the OpenSSL prompt, installation was successful. C:\>
Depending on your version of Windows, the preceding instructions might be slightly different.
After OpenSSL has been installed, use instructions similar to those from from Example 1 (shown earlier in this section), with the following changes:
Change the following Unix commands:
# Create clean environment shell>rm -rf newcerts
shell>mkdir newcerts && cd newcerts
On Windows, use these commands instead:
# Create clean environment shell>md c:\newcerts
shell>cd c:\newcerts
When a '\'
character is shown at the end
of a command line, this '\'
character
must be removed and the command lines entered all on a
single line.
To test SSL connections, start the server as follows, where
$DIR
is the path name to the directory where
the sample my.cnf
option file (or
my.ini
on Windows) is located:
shell> mysqld --defaults-file=$DIR/my.cnf &
Then invoke a client program using the same option file:
shell> mysql --defaults-file=$DIR/my.cnf
If you have a MySQL source distribution, you can also test your
setup by modifying the preceding my.cnf
file to refer to the demonstration certificate and key files in
the mysql-test/std_data
directory of the
distribution.
This section describes how to get a secure connection to a remote
MySQL server with SSH. The information was provided by David
Carlson <dcarlson@mplcomm.com>
.
Install an SSH client on your Windows machine. As a user, the
best nonfree one I have found is from
SecureCRT
from
http://www.vandyke.com/. Another option is
f-secure
from
http://www.f-secure.com/. You can also find
some free ones on Google
at
http://directory.google.com/Top/Computers/Internet/Protocols/SSH/Clients/Windows/.
Start your Windows SSH client. Set Host_Name =
.
Set
yourmysqlserver_URL_or_IP
userid=
to log in to your server. This your_userid
userid
value
might not be the same as the user name of your MySQL account.
Set up port forwarding. Either do a remote forward (Set
local_port: 3306
, remote_host:
,
yourmysqlservername_or_ip
remote_port: 3306
) or a local forward (Set
port: 3306
, host:
localhost
, remote port: 3306
).
Save everything, otherwise you will have to redo it the next time.
Log in to your server with the SSH session you just created.
On your Windows machine, start some ODBC application (such as Access).
Create a new file in Windows and link to MySQL using the ODBC
driver the same way you normally do, except type in
localhost
for the MySQL host server, not
yourmysqlservername
.
At this point, you should have an ODBC connection to MySQL, encrypted using SSH.
Applications can use the following guidelines to perform auditing that ties database activity to MySQL accounts.
MySQL accounts correspond to rows in the
mysql.user
table. When a client connects
successfully, the server authenticates the client to a particular
row in this table. The User
and
Host
column values in this row uniquely
identify the account and correspond to the
'
format in which account names are written in SQL statements.
user_name
'@'host_name
'
The account used to authenticate a client determines which
privileges the client has. Normally, the
CURRENT_USER()
function can be
invoked to determine which account this is for the client user.
Its value is constructed from the User
and
Host
columns of the user
table row for the account.
However, there are circumstances under which the
CURRENT_USER()
value corresponds
not to the client user but to a different account. This occurs in
contexts when privilege checking is not based the client's
account:
Stored routines (procedures and functions) defined with the
SQL SECURITY DEFINER
characteristic
Views defined with the SQL SECURITY DEFINER
characteristic
Triggers and events
In those contexts, privilege checking is done against the
DEFINER
account and
CURRENT_USER()
refers to that
account, not to the account for the client who invoked the stored
routine or view or who caused the trigger to activate. To
determine the invoking user, you can call the
USER()
function, which returns a
value indicating the actual user name provided by the client and
the host from which the client connected. However, this value does
not necessarily correspond directly to an account in the
user
table, because the
USER()
value never contains
wildcards, whereas account values (as returned by
CURRENT_USER()
) may contain user
name and host name wildcards.
For example, a blank user name matches any user, so an account of
''@'localhost'
enables clients to connect as an
anonymous user from the local host with any user name. If this
case, if a client connects as user1
from the
local host, USER()
and
CURRENT_USER()
return different
values:
mysql> SELECT USER(), CURRENT_USER();
+-----------------+----------------+
| USER() | CURRENT_USER() |
+-----------------+----------------+
| user1@localhost | @localhost |
+-----------------+----------------+
The host name part of an account can contain wildcards, too. If
the host name contains a '%'
or
'_'
pattern character or uses netmask notation,
the account can be used for clients connecting from multiple hosts
and the CURRENT_USER()
value will
not indicate which one. For example, the account
'user2'@'%.example.com'
can be used by
user2
to connect from any host in the
example.com
domain. If user2
connects from remote.example.com
,
USER()
and
CURRENT_USER()
return different
values:
mysql> SELECT USER(), CURRENT_USER();
+--------------------------+---------------------+
| USER() | CURRENT_USER() |
+--------------------------+---------------------+
| user2@remote.example.com | user2@%.example.com |
+--------------------------+---------------------+
If an application must invoke
USER()
for user auditing (for
example, if it does auditing from within triggers) but must also
be able to associate the USER()
value with an account in the user
table, it is
necessary to avoid accounts that contain wildcards in the
User
or Host
column.
Specifically, do not permit User
to be empty
(which creates an anonymous-user account), and do not permit
pattern characters or netmask notation in Host
values. All accounts must have a nonempty User
value and literal Host
value.
With respect to the previous examples, the
''@'localhost'
and
'user2'@'%.example.com'
accounts should be
changed not to use wildcards:
RENAME USER ''@'localhost' TO 'user1'@'localhost'; RENAME USER 'user2'@'%.example.com' TO 'user2'@'remote.example.com';
If user2
must be able to connect from several
hosts in the example.com
domain, there should
be a separate account for each host.
To extract the user name or host name part from a
CURRENT_USER()
or
USER()
value, use the
SUBSTRING()
function:
mysql>SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',1);
+---------------------------------------+ | SUBSTRING_INDEX(CURRENT_USER(),'@',1) | +---------------------------------------+ | user1 | +---------------------------------------+ mysql>SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',-1);
+----------------------------------------+ | SUBSTRING_INDEX(CURRENT_USER(),'@',-1) | +----------------------------------------+ | localhost | +----------------------------------------+