There are circumstances when you might want to run multiple servers on the same machine.For example, you might want to test a new MySQL release while leaving your existing
production setup undisturbed.
Or you might be an Internet service provider that wants to provide independent MySQL installations for di erent customers.
If you want to run multiple servers, the easiest way is to compile the servers with di erent TCP/IP ports and socket files so they are not both listening to the same TCP/IP port or socket file.
Assume an existing server is configured for the default port number and socket file. Then configure the new server with a configure command something like this:
shell> ./configure --with-tcp-port=port_number \
--with-unix-socket-path=file_name \
--prefix=/usr/local/mysql-3.22.9
Here port_number and file_name should be di erent than the default port number and
socket file pathname, and the --prefix value should specify an installation directory dif-
ferent than the one under which the existing MySQL installation is located.
You can check the socket used by any currently executing MySQL server with this command:
shell> mysqladmin -h hostname --port=port_number variables
Note that if you specify “localhost” as a hostname, mysqladmin will default to using Unix
sockets instead of TCP/IP.
If you have a MySQL server running on the port you used, you will get a list of some of the
most important configurable variables in MySQL, including the socket name.
You don’t have to recompile a new MySQL server just to start with a di erent port and
socket.
You can change the port and socket to be used by specifying them at run time as
options to safe_mysqld:
shell> /path/to/safe_mysqld --socket=file_name --port=port_number
mysqld_multi can also take safe_mysqld (or mysqld) as an argument and pass the options
from a configuration file to safe_mysqld and further to mysqld.
If you run the new server on the same database directory as another server with logging
enabled, you should also specify the name of the log files to safe_mysqld with --log, --
log-update, or --log-slow-queries. Otherwise, both servers may be trying to write to
the same log file.
Warning: Normally you should never have two servers that update data in the same
database! If your OS doesn’t support fault-free system locking, this may lead to unpleasant
surprises!
If you want to use another database directory for the second server, you can use the --
datadir=path option to safe_mysqld.
Note also that starting several MySQL servers (mysqlds) in di erent machines and letting
them access one data directory over NFS is generally a bad idea! The problem is that the
NFS will become the bottleneck with the speed. It is not meant for such use. And last but
not least, you would still have to come up with a solution how to make sure that two or
more mysqlds are not interfering with each other. At the moment there is no platform that
would 100% reliable do the file locking (lockd daemon usually) in every situation. Yet there
would be one more possible risk with NFS; it would make the work even more complicated
for lockd daemon to handle. So make it easy for your self and forget about the idea. The
working solution is to have one computer with an operating system that e ciently handles
threads and have several CPUs in it.
When you want to connect to a MySQL server that is running with a di erent port than
the port that is compiled into your client, you can use one of the following methods:
• Start the client with --host ’hostname’ --port=port_number to connect with
TCP/IP, or [--host localhost] --socket=file_name to connect via a Unix socket.
• In your C or Perl programs, you can give the port or socket arguments when connecting
to the MySQL server.
• If your are using the Perl DBD::mysql module you can read the options from the MySQL
option files. See Section 4.1.2 [Option files], page 168.
$dsn = "DBI:mysql:test;mysql_read_default_group=client;mysql_read_default_file=/usr/local/mysql/data/my.cnf"
$dbh = DBI->connect($dsn, $user, $password);
• Set the MYSQL_UNIX_PORT and MYSQL_TCP_PORT environment variables to point to the
Unix socket and TCP/IP port before you start your clients. If you normally use a
specific socket or port, you should place commands to set these environment variables
in your ‘.login’ file. See Appendix F [Environment variables], page 695.
• Specify the default socket and TCP/IP port in the ‘.my.cnf’ file in your home directory.
General Security Issues and the MySQL Access Privilege System
MySQL has an advanced but non-standard security/privilege system. This section describes
how it works.
General Security Guidelines
Anyone using MySQL on a computer connected to the Internet should read this section to
avoid the most common security mistakes.
MySQL Technical Reference for Version 4.0.1-alpha
In discussing security, we emphasize the necessity of fully protecting the entire server host
(not simply 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 a user may attempt to perform. There is also some 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 whenever possible:
• Do not ever give anyone (except the mysql root user) access to the user table in the
mysql database! This is critical. The encrypted password is the real password in
MySQL. Anyone who knows the password which is listed in the user table and has
access to the host listed for the account can easily log in as that user.
• Learn the MySQL access privilege system. The GRANT and REVOKE commands are used
for controlling access to MySQL. Do not grant any 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, you have problems. 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 item about setting a
root password.
- Use the command SHOW GRANTS and check to see who has access to what. Remove
those privileges that are not necessary using the REVOKE command.
• Do not keep any plain-text passwords in your database. When your computer becomes
compromised, the intruder can take the full list of passwords and use them. Instead
use MD5() or another one-way hashing function.
• Do not choose passwords from dictionaries. There are special programs to break them.
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 “Mhall” which is taken from the first characters of each word
in the sentence “Mary had a little lamb.” This is easy to remember and type, but
di cult to guess for someone who does not know it.
• 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 demilitarised 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 be inaccessible from untrusted hosts.
Another simple way to check whether or not your MySQL port is open is to try
the following command from some remote machine, where server_host is the
hostname of your MySQL server:
shell> telnet server_host 3306
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. If telnet just hangs or the connection is refused, everything is OK; the
port is blocked.
• Do not trust any data entered by your users. They 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 may occur as a result of hackers using similar techniques, if you do not
prepare for them.
Also remember to check numeric data. A common mistake is to protect only strings.
Sometimes people think that if a database contains only publicly available data that it
need not be protected. This is incorrect. At least denial-of-service type attacks can be
performed on such databases. The simplest way to protect from this type of attack is to
use apostrophes around the numeric constants: SELECT * FROM table WHERE ID=’234’
rather than SELECT * FROM table WHERE ID=234. MySQL automatically converts this
string to a number and strips all non-numeric symbols from it.
Checklist:
- All Web applications:
• Try to enter ‘’’ and ‘"’ in all your Web forms. If you get any kind of MySQL
error, investigate the problem right away.
• Try to modify any dynamic URLs by adding %22 (‘"’), %23 (‘#’), and %27 (‘’’)
in the URL.
• Try to modify datatypes in dynamic URLs from numeric ones to character
ones containing characters from previous examples. Your application should
be safe against this and similar attacks.
• Try to enter characters, spaces, and special symbols instead of numbers in
numeric fields. Your application should remove them before passing them
to MySQL or your application should generate an error. Passing unchecked
values to MySQL is very dangerous!
• Check data sizes before passing them to MySQL.
• Consider having your application connect to the database using a di erent
user name than the one you use for administrative purposes. Do not give
your applications any more access privileges than they need.
- Users of PHP:
• Check out the addslashes() function. As of PHP 4.0.3, a mysql_escape_
string() function is available that is based on the function of the same name
in the MySQL C API.
- Users of MySQL C API:
• Check out the mysql_escape_string() API call.
- Users of MySQL++:
• Check out the escape and quote modifiers for query streams.
- Users of Perl DBI:
• Check out the quote() method or use placeholders.
- Users of Java JDBC:
• Use a PreparedStatement object and placeholders.
• Do not transmit plain (unencrypted) data over the Internet. These data are 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 as of Version 4.0.0. SSH port-forwarding can be used to create
an encrypted (and compressed) tunnel for the communication.
• Learn to use the tcpdump and strings utilities. For most cases, you can check whether
or not 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 sys-
tems.) Warning: If you do not see data this doesn’t always actually mean that it is
encrypted. If you need high security, you should consult with a security expert.
4.2.2 How to Make MySQL Secure Against Crackers
When you connect to a MySQL server, you normally should use a password. The password
is not transmitted in clear text over the connection, however the encryption algorithm is
not very strong, and with some e ort a clever attacker can crack the password if he is able
to sni the tra c between the client and the server. If the connection between the client
and the server goes through an untrusted network, you should use an SSH tunnel to encrypt
the communication.
All other information is transferred as text that can be read by anyone who is able to
watch the connection. If you are concerned about this, you can use the compressed
protocol (in MySQL Version 3.22 and above) to make things much harder. To make
things even more secure you should use ssh. You can find an Open Source ssh client at
http://www.openssh.org/, and a commercial ssh client at http://www.ssh.com/. With
this, you can get an encrypted TCP/IP connection between a MySQL server and a MySQL
client.
If you are using MySQL 4.0, you can also use internal openssl support.
To make a MySQL system secure, you should strongly consider the following suggestions:
• Use passwords for all MySQL users. Remember that anyone can log in as any other
person as simply as mysql -u other_user db_name if other_user has no password.
It is common behavior with client/server applications that the client may specify any
user name. You can change the password of all users by editing the mysql_install_db
script before you run it, or only the password for the MySQL root user like this:
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD(’new_password’)
WHERE user=’root’;
mysql> FLUSH PRIVILEGES;
• Don’t run the MySQL daemon as the Unix root user. This is very dangerous, be-
cause any user with FILE privileges will be able to create files as root (for example,
~root/.bashrc). To prevent this, mysqld will refuse to run as root unless it is specified
directly using a --user=root option.
mysqld can be run as an ordinary unprivileged user instead. You can also create a new
Unix user mysql to make everything even more secure. If you run mysqld as another
Unix user, you don’t need to change the root user name in the user table, because
MySQL user names have nothing to do with Unix user names. To start mysqld as
another Unix user, add a user line that specifies the user name 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=mysql
This will cause the server to start as the designated user whether you start it manu-
ally or by using safe_mysqld or mysql.server. For more details, see Section A.3.2
[Changing MySQL user], page 577.
• Don’t support symlinks to tables (this can be disabled with the --skip-symlink op-
tion). This is especially important if you run mysqld as root as anyone that has write
access to the mysqld data directories could then delete any file in the system! See
• Check that the Unix user that mysqld runs as is the only user with read/write privileges
in the database directories.
• Don’t give the process privilege to all users. The output of mysqladmin processlist
shows the text of the currently executing queries, so any user who is allowed to ex-
ecute that command might be able to see if another user issues an UPDATE user SET
password=PASSWORD(’not_secure’) query.
mysqld reserves an extra connection for users who have the process privilege, so that
a MySQL root user can log in and check things even if all normal connections are in
use.
• Don’t give the file privilege to all 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, all files generated with SELECT ... INTO OUTFILE are readable to everyone,
and you cannot overwrite existing files.
The file privilege may also be used to read any file accessible to the Unix user that
the server runs as. This could be abused, for example, by using LOAD DATA to load
‘/etc/passwd’ into a table, which can then be read with SELECT.
• If you don’t trust your DNS, you should use IP numbers instead of hostnames in the
grant tables. In any case, you should be very careful about creating grant table entries
using hostname values that contain wild cards!
• If you want to restrict the number of connections for a single user, you can do this by
setting the max_user_connections variable in mysqld.
4.2.3 Startup Options for mysqld Concerning Security
The following mysqld options a ect security:
--safe-show-database
With this option, SHOW DATABASES returns only those databases for which the
user has some kind of privilege.
--safe-user-create
If this is enabled, an user can’t create new users with the GRANT command, if
the user doesn’t have INSERT privilege to the mysql.user table. If you want to
give a user access to just create new users with those privileges that the user
has right to grant, you should give the user the following privilege:
GRANT INSERT(user) on mysql.user to ’user’@’hostname’;
This will ensure that the user can’t change any privilege columns directly, but
has to use the GRANT command to give privileges to other users.
--skip-grant-tables
This option causes the server not to use the privilege system at all. This gives
everyone ful l access to all databases! (You can tell a running server to start
using the grant tables again by executing mysqladmin flush-privileges or
mysqladmin reload.)
--skip-name-resolve
Hostnames are not resolved. All Host column values in the grant tables must
be IP numbers or localhost.
--skip-networking
Don’t allow TCP/IP connections over the network. All connections to mysqld
must be made via Unix sockets. This option is unsuitable for systems that
use MIT-pthreads, because the MIT-pthreads package doesn’t support Unix
sockets.
--skip-show-database
With this option, the SHOW DATABASES statement doesn’t return anything.
4.2.4 What the Privilege System Does
The primary function of the MySQL privilege system is to authenticate a user connecting
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 an anonymous user and to grant priv-
ileges for MySQL-specific functions such as LOAD DATA INFILE and administrative opera-
tions.
How the Privilege System Works
The MySQL privilege system ensures that all users may do exactly the things that they
are supposed to be allowed to do. When you connect to a MySQL server, your identity
is determined by the host from which you connect and the user name you specify. The
system grants privileges according to your identity and what you want to do.
MySQL considers both your hostname and user name in identifying you because there is
little reason to assume that a given user name belongs to the same person everywhere on
the Internet. For example, the user bill who connects from whitehouse.gov need not
be the same person as the user bill who connects from microsoft.com. MySQL handles
this by allowing you to distinguish users on di erent hosts that happen to have the same
name: you can grant bill one set of privileges for connections from whitehouse.gov, and
a di erent set of privileges for connections from microsoft.com.
MySQL access control involves two stages:
• Stage 1: The server checks whether or not you are even allowed to connect.
• Stage 2: Assuming you can connect, the server checks each request you issue to see
whether or not you have su cient 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
makes sure you have the select privilege for the table or the drop privilege for the
database.
The server uses the user, db, and host tables in the mysql database at both stages of access
control. The fields in these grant tables are shown below:
Table name user db
host
Scope fields Host Host Host
User Db
Db
Password User
Privilege fields Select_priv Select_priv Select_priv
Insert_priv Insert_priv Insert_priv
Update_priv Update_priv Update_priv
Delete_priv Delete_priv Delete_priv
Index_priv Index_priv Index_priv
Alter_priv Alter_priv Alter_priv
Create_priv Create_priv Create_priv
Drop_priv Drop_priv Drop_priv
Grant_priv Grant_priv Grant_priv
References_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
For the second stage of access control (request verification), the server may, if the request
involves tables, additionally consult the tables_priv and columns_priv tables. The fields
in these tables are shown below:
Table name tables_priv columns_priv
Scope fields Host Host
Db
Db
User User
Table_name Table_name
Column_name
Privilege fields Table_priv Column_priv
Column_priv
Other fields Timestamp Timestamp
Grantor
Each grant table contains scope fields and privilege fields.
Scope fields determine the scope of each entry in the tables, that is, the context in
which the entry applies. For example, a user table entry with Host and User values of
’thomas.loc.gov’ and ’bob’ would be used for authenticating connections made to the
server by bob from the host thomas.loc.gov. Similarly, a db table entry with Host, User,
and Db fields 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 fields indicating tables or table/column combinations
to which each entry applies.
For access-checking purposes, comparisons of Host values are case insensitive. User,
Password, Db, and Table_name values are case sensitive. Column_name values are case
insensitive in MySQL Version 3.22.12 or later.
Privilege fields indicate the privileges granted by a table entry, 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. The rules used to do this are described in
Scope fields are strings, declared as shown below; the default value for each is the empty
string:
Field name Type Notes
Host CHAR(60)
User CHAR(16)
Password CHAR(16)
Db CHAR(64) (CHAR(60) for the tables_priv and columns_priv tables)
Table_name CHAR(60)
Column_name CHAR(60)
In the user, db and host tables, all privilege fields are declared as ENUM(’N’,’Y’) — each
can have a value of ’N’ or ’Y’, and the default value is ’N’.
In the tables_priv and columns_priv tables, the privilege fields are declared as SET fields:
Table name Field name Possible set elements
tables_priv Table_priv ’Select’, ’Insert’, ’Update’, ’Delete’,
’Create’, ’Drop’, ’Grant’, ’References’,
’Index’, ’Alter’
tables_priv Column_priv ’Select’, ’Insert’, ’Update’, ’References’
columns_priv Column_priv ’Select’, ’Insert’, ’Update’, ’References’
Brie y, the server uses the grant tables like this:
• The user table scope fields determine whether to allow or reject incoming connections.
For allowed connections, any privileges granted in the user table indicate the user’s
global (superuser) privileges. These privileges apply to all databases on the server.
• The db and host tables are used together:
- The db table scope fields determine which users can access which databases from
which hosts. The privilege fields determine which operations are allowed.
- The host table is used as an extension of the db table when you want a given db
table entry 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 entry, then populate the host table with an entry for each
of those hosts.
• 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.
Note that administrative privileges (reload, shutdown, etc.) are specified only in the user
table. This is because administrative operations are operations on the server itself and are
not database-specific, so there is no reason to list such privileges in the other grant tables.
In fact, only the user table need be consulted to determine whether or not you can perform
an administrative operation.
The file privilege is specified only in the user table, too. 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 once, when it starts up.
When you modify the contents of the grant tables, it is a good idea to make sure that
your changes set up privileges the way you want. For help in diagnosing problems, see
A useful diagnostic tool is the mysqlaccess script, which Yves Carlier has provided for the
MySQL distribution. Invoke mysqlaccess with the --help option to find out how it works.
Note that mysqlaccess checks access using only the user, db and host tables. It does not
check table- or column-level privileges.
Privileges Provided by MySQL
Information about user privileges is stored in the user, db, host, tables_priv, and
columns_priv tables in the mysql database (that is, in the database named mysql).
The names used in this manual to refer to the privileges provided by MySQL are shown
below, along with the table column name associated with each privilege in the grant tables
and the context in which the privilege applies:
Privilege Column Context
select Select_priv tables
insert Insert_priv tables
update Update_priv tables
delete Delete_priv tables
index Index_priv tables
alter Alter_priv tables
create Create_priv databases, tables, or indexes
drop Drop_priv databases or tables
grant Grant_priv databases or tables
references References_priv databases or tables
reload Reload_priv server administration
shutdown Shutdown_priv server administration
process Process_priv server administration
file File_priv file access on server
The select, insert, update, and delete privileges allow you to perform operations on rows in
existing tables in a database.
SELECT statements require the select privilege only if they actually retrieve rows from a
table. You can execute certain SELECT statements even without permission to access any
of the databases on the server. For example, you could use the mysql client as a simple
calculator:
mysql> SELECT 1+1;
mysql> SELECT PI()*2;
The index privilege allows you to create or drop (remove) indexes.
The alter privilege allows you to use ALTER TABLE.
The create and drop privileges allow you to create new databases and tables, or to drop
(remove) existing databases and tables.
Note that 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 grant privilege allows you to give to other users those privileges you yourself possess.
The file privilege gives you permission to read and write files on the server using the LOAD
DATA INFILE and SELECT ... INTO OUTFILE statements. Any user to whom this privilege
is granted can read or write any file that the MySQL server can read or write.
The remaining privileges are used for administrative operations, which are performed us-
ing the mysqladmin program. The table below shows which mysqladmin commands each
administrative privilege allows you to execute:
Privilege Commands permitted to privilege holders reload reload, refresh, flush-privileges, flush-hosts, flush-logs, and
flush-tables
shutdown shutdown
process processlist, kill
The reload command tells the server to re-read the grant tables. The refresh command
ushes all tables and opens and closes the log files. flush-privileges is a synonym for
reload. The other flush-* commands perform functions similar to refresh but are more
limited in scope, and may be preferable in some instances. For example, if you want to
ush just the log files, flush-logs is a better choice than refresh.
The shutdown command shuts down the server.
The processlist command displays information about the threads executing within the
server. The kill command kills server threads. You can always display or kill your own
threads, but you need the process privilege to display or kill threads initiated by other
users. See Section 4.5.4 [KILL], page 228.
It is a good idea in general to grant privileges only to those users who need them, but you
should exercise particular caution in granting certain privileges:
• The grant privilege allows users to give away their privileges to other users. Two users
with di erent privileges and with the grant privilege are able to combine privileges.
• The alter privilege may be used to subvert the privilege system by renaming tables.
• The file privilege can be abused to read any world-readable file on the server into a
database table, the contents of which can then be accessed using SELECT. This includes
the contents of all databases hosted by the server!
• The shutdown privilege can be abused to deny service to other users entirely, by ter-
minating the server.
• The process privilege can be used to view the plain text of currently executing queries,
including queries that set or change passwords.
• Privileges on the mysql database 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.) If they can access the mysql.user
password column, they can use it to log into the MySQL server for the given user.
(With su cient privileges, the same user can replace a password with a di erent one.)
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.
Connecting to the MySQL Server
MySQL client programs generally require that you specify connection parameters when you
want to access a MySQL server: the host you want to connect to, your user name, and your
password. For example, the mysql client can be started like this (optional arguments are
enclosed between ‘[’ and ‘]’):
shell> mysql [-h host_name] [-u user_name] [-pyour_pass]
Alternate forms of the -h, -u, and -p options are --host=host_name, --user=user_name,
and --password=your_pass. Note that there is no space between -p or --password= and
the password following it.
Note: Specifying a password on the command line is not secure! Any user on your system
may then find out your password by typing a command like: ps auxww.
mysql uses default values for connection parameters that are missing from the command
line:
• The default hostname is localhost.
• The default user name is your Unix login name.
• No password is supplied if -p is missing.
Thus, for a Unix user joe, the following commands are equivalent:
shell> mysql -h localhost -u joe
shell> mysql -h localhost
shell> mysql -u joe
shell> mysql
Other MySQL clients behave similarly.
On Unix systems, you can specify di erent default values to be used when you make a
connection, so that you need not enter them on the command line each time you invoke a
client program. This can be done in a couple of ways:
• You can specify connection parameters in the [client] section of the ‘.my.cnf’ con-
figuration file in your home directory. The relevant section of the file might look like
this:
[client]
host=host_name
user=user_name
password=your_pass
See Section 4.1.2 [Option files], page 168.
• You can specify connection parameters using environment variables. The host can
be specified for mysql using MYSQL_HOST. The MySQL user name can be specified
using USER (this is for Windows only). The password can be specified using MYSQL_PWD
(but this is insecure; see the next section).
Access Control, Stage 1: Connection Verification
When you attempt to connect to a MySQL server, the server accepts or rejects the connec-
tion based on your identity and whether or not 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, then enters Stage 2 and waits for requests.
Your identity is based on two pieces of information:
• The host from which you connect
• Your MySQL user name
Identity checking is performed using the three user table scope fields (Host, User, and
Password). The server accepts the connection only if a user table entry matches your
hostname and user name, and you supply the correct password.
Values in the user table scope fields may be specified as follows:
• A Host value may be a hostname or an IP number, or ’localhost’ to indicate the
local host.
• You can use the wild-card characters ‘%’ and ‘_’ in the Host field.
• A Host value of ’%’ matches any hostname.
• A blank Host value means that the privilege should be anded with the entry in the
host table that matches the given host name. You can find more information about
this in the next chapter.
• As of MySQL Version 3.23, for Host values specified as IP numbers, you can specify a
netmask indicating how many address bits to use for the network number. For example:
GRANT ALL PRIVILEGES on db.* to david@’192.58.197.0/255.255.255.0’;
This will allow everyone to connect from an IP where the following is true:
user_ip & netmask = host_ip.
In the above example all IP:s in the interval 192.58.197.0 - 192.58.197.255 can connect
to the MySQL server.
• Wild-card characters are not allowed in the User field, but you can specify a blank
value, which matches any name. If the user table entry that matches an incoming
connection has a blank user name, the user is considered to be the anonymous user
(the user with no name), rather than 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 field can be blank. This does not mean that any password matches, it
means the user must connect without specifying a password.
Non-blank Password values represent encrypted passwords. MySQL does not store pass-
words 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 is then used when the client/server is checking if the password is correct. (This is
done without the encrypted password ever traveling over the connection.) Note that from
MySQL’s point of view the encrypted password is the REAL password, so you should not
give anyone access to it! In particular, don’t give normal users read access to the tables in
the mysql database!
The examples below show how various combinations of Host and User values in user table
entries apply to incoming connections:
Host value User value Connections matched by entry
’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,
etc. (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
Because you can use IP wild-card values in the Host field (for example, ’144.155.166.%’
to match every host on a subnet), there is the possibility that someone might try to exploit
this capability by naming a host 144.155.166.somewhere.com. To foil such attempts,
MySQL disallows matching on hostnames that start with digits and a dot. Thus, if you
have a host named something like 1.2.foo.com, its name will never match the Host column
of the grant tables. Only an IP number can match an IP wild-card value.
An incoming connection may be matched by more than one entry in the user table. For
example, a connection from thomas.loc.gov by fred would be matched by several of the
entries just shown above. How does the server choose which entry to use if more than
one matches? The server resolves this question by sorting the user table after reading it
at startup time, then looking through the entries in sorted order when a user attempts to
connect. The first matching entry is the one that is used.
user table sorting works as follows. Suppose the user table looks like this:
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| % | root | ...
| % | jeffrey | ...
| localhost | root | ...
| localhost | | ...
+-----------+----------+-
When the server reads in the table, it orders the entries with the most-specific Host values
first (’%’ in the Host column means “any host” and is least specific). Entries 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). The resulting sorted user table looks like this:
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| localhost | root | ...
| localhost | | ...
| % | jeffrey | ...
| % | root | ...
+-----------+----------+-
When a connection is attempted, the server looks through the sorted entries and uses
the first match found. For a connection from localhost by jeffrey, the entries with
’localhost’ in the Host column match first. Of those, the entry with the blank user name
matches both the connecting hostname and user name. (The ’%’/’jeffrey’ entry would
have matched, too, but it is not the first match in the table.)
Here is another example. Suppose 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 from thomas.loc.gov by jeffrey is matched by the first entry, whereas a
connection from whitehouse.gov by jeffrey is matched by the second.
A common misconception is to think that for a given user name, all entries that explicitly
name that user will be used first when the server attempts to find a match for the connection.
This is simply not true. The previous example illustrates this, where a connection from
thomas.loc.gov by jeffrey is first matched not by the entry containing ’jeffrey’ as the
User field value, but by the entry with no user name!
If you have problems connecting to the server, print out the user table and sort it by hand
to see where the first match is being made.
4.2.9 Access Control, Stage 2: Request Verification
Once you establish a connection, the server enters Stage 2. For each request that comes in
on the connection, the server checks whether you have su cient privileges to perform it,
based on the type of operation you wish to perform. This is where the privilege fields in
the grant tables come into play. These privileges can come from any of the user, db, host,
tables_priv, or columns_priv tables. The grant tables are manipulated with GRANT and
REVOKE commands.
The user table grants privileges that are assigned to you on a global basis and that apply
no matter what the current database is. For example, if the user table grants you the
delete privilege, you can delete rows from any database on the server host! In other words,
user table privileges are superuser privileges. It is wise to grant privileges in the user table
only to superusers such as server or database administrators. For other users, you should
leave the privileges in the user table set to ’N’ and grant privileges on a database-specific
basis only, using the db and host tables.
The db and host tables grant database-specific privileges. Values in the scope fields may
be specified as follows:
• The wild-card characters ‘%’ and ‘_’ can be used in the Host and Db fields of either
table.
• 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 ’%’ or blank Host value in the host table means “any host.”
• A ’%’ or blank Db value in either table means “any database.”
• A blank User value in either table matches the anonymous user.
The db and host tables are read in and sorted when the server starts up (at the same time
that it reads the user table). The db table is sorted on the Host, Db, and User scope fields,
and the host table is sorted on the Host and Db scope fields. 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 and columns_priv tables grant table- and column-specific privileges.
Values in the scope fields may be specified as follows:
• The wild-card characters ‘%’ and ‘_’ can be used in the Host field of either table.
• A ’%’ or blank Host value in either table means “any host.”
• The Db, Table_name and Column_name fields cannot contain wild cards or be blank in
either table.
The tables_priv and columns_priv tables are sorted on the Host, Db, and User fields.
This is similar to db table sorting, although the sorting is simpler because only the Host
field may contain wild cards.
The request verification process is described below. (If you are familiar with the access-
checking source code, you will notice that the description here di ers slightly from the
algorithm used in the code. The description is equivalent to what the code actually does;
it di ers only to make the explanation simpler.)
For administrative requests (shutdown, reload, etc.), the server checks only the user table
entry, because that is the only table that specifies administrative privileges. Access is
granted if the entry allows the requested operation and denied otherwise. For example, if
you want to execute mysqladmin shutdown but your user table entry doesn’t grant the
shutdown privilege to you, access is denied 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, etc.), the server first checks the user’s global
(superuser) privileges by looking in the user table entry. If the entry allows the requested
operation, access is granted. If the global privileges in the user table are insu cient, the
server determines the user’s database-specific privileges by checking the db and host tables:
1. The server looks in the db table for a match on the Host, Db, and User fields. The
Host and User fields are matched to the connecting user’s hostname and MySQL user
name. The Db field is matched to the database the user wants to access. If there is no
entry for the Host and User, access is denied.
2. If there is a matching db table entry and its Host field is not blank, that entry defines
the user’s database-specific privileges.
3. If the matching db table entry’s Host field is blank, it signifies that the host table
enumerates which hosts should be allowed 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 fields. If
no host table entry 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 entry 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 allows
the requested operation, access is granted. Otherwise, the server checks the user’s table
and column privileges in the tables_priv and columns_priv tables and adds those to the
user’s privileges. Access is allowed or denied based on the result.
Expressed in boolean terms, the preceding description of how a user’s privileges are calcu-
lated may be summarised like this:
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
It may not be apparent why, if the global user entry privileges are initially found to be
insu cient for the requested operation, the server adds those privileges to the database-,
table-, and column-specific privileges later. The reason is that a request might require more
than one type of privilege. For example, if you execute an INSERT ... SELECT statement,
you need both insert and select privileges. Your privileges might be such that the user
table entry grants one privilege and the db table entry 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 can be used to maintain a list of secure servers.
At TcX, the host table contains a list of all machines on the local network. These are
granted all privileges.
You can also use the host table to indicate hosts that are not secure. Suppose you have
a machine public.your.domain that is located in a public area that you do not consider
secure. You can allow 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’)
+--------------------+----+-
Naturally, you should always test your entries in the grant tables (for example, using
mysqlaccess) to make sure your access privileges are actually set up the way you think
they are.
Causes of Access denied Errors
If you encounter Access denied errors when you try to connect to the MySQL server, the
list below indicates some courses of action you can take to correct the problem:
• After installing MySQL, did you run the mysql_install_db script to set up the initial
grant table contents? If not, do so.
Test the initial privileges by executing this command:
shell> mysql -u root test
The server should let you connect without error. You should also make sure you have a
file ‘user.MYD’ in the MySQL database directory. Ordinarily, this is ‘PATH/var/mysql/user.MYD’,
where PATH is the pathname to the MySQL installation root.
• 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 root password is something you
should do while you’re setting up your other MySQL users.
If you try to connect as root and get this error:
Access denied for user: ’@unknown’ to database mysql
this means that you don’t have an entry in the user table with a User column value of
’root’ and that mysqld cannot resolve the hostname for your client. In this case,
you must restart the server with the --skip-grant-tables option and edit your
‘/etc/hosts’ or ‘\windows\hosts’ file to add an entry for your host.
• If you get an error like the following:
shell> mysqladmin -u root -pxxxx ver
Access denied for user: ’root@localhost’ (Using password: YES)
It means that you are using a wrong password. See Section 4.3.6 [Passwords], page 202.
If you have forgot the root password, you can restart mysqld with --skip-grant-
tables to change the password. You can find more about this option later on in this
manual section.
If you get the above error even if you haven’t specified a password, this means that you
a wrong password in some my.ini file. See Section 4.1.2 [Option files], page 168. You
can avoid using option files with the --no-defaults option, as follows:
shell> mysqladmin --no-defaults -u root ver
• If you updated an existing MySQL installation from a version earlier than Version
3.22.11 to Version 3.22.11 or later, did you run the mysql_fix_privilege_tables
script? If not, do so. The structure of the grant tables changed with MySQL Version
3.22.11 when the GRANT statement became functional.
• If your privileges seem to have changed in the middle of a session, it may be that a
superuser has changed them.
• If you can’t get your password to work, remember that you must use the PASSWORD()
function if you set the password with the INSERT, UPDATE, or SET PASSWORD state-
ments. The PASSWORD() function is unnecessary if you specify the password using the
GRANT ... INDENTIFIED BY statement or the mysqladmin password command. See
• localhost is a synonym for your local hostname, and is also the default host to
which clients try to connect if you specify no host explicitly. However, connections
to localhost do not work if you are running on a system that uses MIT-pthreads
(localhost connections are made using Unix sockets, which are not supported by
MIT-pthreads). To avoid this problem on such systems, you should use the --host
option to name the server host explicitly. This will make a TCP/IP connection to the
mysqld server. In this case, you must have your real hostname in user table entries
on the server host. (This is true even if you are running a client program on the same
host as the server.)
• If you get an Access denied error when trying to connect to the database with mysql
-u user_name db_name, you may have a problem with the user table. Check this by
executing mysql -u root mysql and issuing this SQL statement:
mysql> SELECT * FROM user;
The result should include an entry with the Host and User columns matching your
computer’s hostname and your MySQL user name.
• The Access denied error message will tell you who you are trying to log in as, the
host from which you are trying to connect, and whether or not you were using a
password. Normally, you should have one entry in the user table that exactly matches
the hostname and user name that were given in the error message. For example if you
get an error message that contains Using password: NO, this means that you tried to
login without an password.
• If you get the following error when you try to connect from a di erent host than the
one on which the MySQL server is running, then there is no row in the user table that
matches that host:
Host ... is not allowed to connect to this MySQL server
You can fix this by using the command-line tool mysql (on the server host!) to add
a row to the user, db, or host table for the user/hostname combination from which
you are trying to connect and then execute mysqladmin flush-privileges. If you are
not running MySQL Version 3.22 and you don’t know the IP number or hostname of
the machine from which you are connecting, you should put an entry with ’%’ as the
Host column value in the user table and restart mysqld with the --log option on the
server machine. After trying to connect from the client machine, the information in
the MySQL log will indicate how you really did connect. (Then replace the ’%’ in the
user table entry with the actual hostname that shows up in the log. Otherwise, you’ll
have a system that is insecure.)
Another reason for this error on Linux is that you are using a binary MySQL version
that is compiled with a di erent glibc version than the one you are using. In this case
you should either upgrade your OS/glibc or download the source MySQL version and
compile this yourself. A source RPM is normally trivial to compile and install, so this
isn’t a big problem.
• If you get an error message where the hostname is not shown or where the hostname
is an IP, even if you try to connect with a hostname:
shell> mysqladmin -u root -pxxxx -h some-hostname ver
Access denied for user: ’root@’ (Using password: YES)
This means that MySQL got some error when trying to resolve the IP to a hostname.
In this case you can execute mysqladmin flush-hosts to reset the internal DNS cache.
Some permanent solutions are:
- Try to find out what is wrong with your DNS server and fix this.
- Specify IPs instead of hostnames in the MySQL privilege tables.
- Start mysqld with --skip-name-resolve.
- Start mysqld with --skip-host-cache.
- Connect to localhost if you are running the server and the client on the same
machine.
- Put the client machine names in /etc/hosts.
• If mysql -u root test works but mysql -h your_hostname -u root test results in
Access denied, then you may not have the correct name for your host in the user ta-
ble. A common problem here is that the Host value in the user table entry specifies an
unqualified hostname, but your system’s name resolution routines return a fully quali-
fied domain name (or vice-versa). For example, if you have an entry with host ’tcx’ in
the user table, but your DNS tells MySQL that your hostname is ’tcx.subnet.se’,
the entry will not work. Try adding an entry to the user table that contains the IP
number 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 wild card—for example, ’tcx.%’.
However, use of hostnames ending with ‘%’ is insecure and is not recommended!)
• If mysql -u user_name test works but mysql -u user_name other_db_name doesn’t
work, you don’t have an entry for other_db_name listed in the db table.
• If mysql -u user_name db_name works when executed on the server machine, but mysql
-u host_name -u user_name db_name doesn’t work when executed on another client
machine, you don’t have the client machine listed in the user table or the db table.
• If you can’t figure out why you get Access denied, remove from the user table all
entries that have Host values containing wild cards (entries that contain ‘%’ or ‘_’). A
very common error is to insert a new entry with Host=’%’ and User=’some user’,
thinking that this will allow you to specify localhost to connect from the same ma-
chine. The reason that this doesn’t 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 en-
try when connecting from localhost! The correct procedure is to insert a second
entry with Host=’localhost’ and User=’some_user’, or to remove the entry with
Host=’localhost’ and User=’’.
• 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 there are one or more corresponding entries in the host table specifying which
hosts the db table entry applies to.
If you get the error when using the SQL commands SELECT ... INTO OUTFILE or LOAD
DATA INFILE, your entry in the user table probably doesn’t have the file privilege
enabled.
• Remember that client programs will use connection parameters specified in configura-
tion files or environment variables. See Appendix F [Environment variables], page 695.
If a client seems to be sending the wrong default connection parameters when you don’t
specify them on the command line, check your environment and the ‘.my.cnf’ file in
your home directory. You might also check the system-wide MySQL configuration files,
though it is far less likely that client connection parameters will be specified there.
• If you make changes to the grant tables directly (using an INSERT or UPDATE state-
ment) and your changes seem to be ignored, remember that you must issue a FLUSH
PRIVILEGES statement or execute a mysqladmin flush-privileges command to cause
the server to re-read the privilege tables. Otherwise your changes have no e ect until
the next time the server is restarted. Remember that after you set the root pass-
word with an UPDATE command, you won’t need to specify it until after you ush the
privileges, because the server won’t know you’ve changed the password yet!
• If you have access problems with a Perl, PHP, Python, or ODBC program, try to con-
nect to the server with mysql -u user_name db_name or mysql -u user_name -pyour_
pass db_name. If you are able to connect using the mysql client, there is a problem
with your program and not with the access privileges. (Note that there is no space
between -p and the password; you can also use the --password=your_pass syntax to
specify the password. If you use the -p option alone, MySQL will prompt you for the
password.)
• For testing, start the mysqld daemon with the --skip-grant-tables option. Then
you can change the MySQL grant tables and use the mysqlaccess script to check
whether or not your modifications have the desired e ect. When you are satisfied
with your changes, execute mysqladmin flush-privileges to tell the mysqld server
to start using the new grant tables. Note: Reloading the grant tables overrides the
--skip-grant-tables option. This allows you to tell the server to begin using the
grant tables again without bringing it down and restarting it.
• If everything else fails, start the mysqld daemon with a debugging option (for ex-
ample, --debug=d,general,query). This will print host and user information about
attempted connections, as well as information about each command issued.
• 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.
MySQL User Account Management
GRANT and REVOKE Syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY ’password’]
[, user_name [IDENTIFIED BY ’password’] ...]
[REQUIRE
[{SSL| X509}]
[CIPHER cipher [AND]]
[ISSUER issuer [AND]]
[SUBJECT subject]]
[WITH GRANT OPTION]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]
GRANT is implemented in MySQL Version 3.22.11 or later. For earlier MySQL versions, the
GRANT statement does nothing.
The GRANT and REVOKE commands allow system administrators to create users and grant
and revoke rights to MySQL users at four privilege levels:
Global level
Global privileges apply to all databases on a given server. These privileges are
stored in the mysql.user table.
Database level
Database privileges apply to all tables in a given database. These privileges are
stored in the mysql.db and mysql.host tables.
Table level
Table privileges apply to all columns in a given table. These privileges are
stored in the mysql.tables_priv table.
Column level
Column privileges apply to single columns in a given table. These privileges
are stored in the mysql.columns_priv table.
If you give a grant for a users that doesn’t exists, that user is created. For examples of how
GRANT works, see Section 4.3.5 [Adding users], page 199.
For the GRANT and REVOKE statements, priv_type may be specified as any of the following:
ALL PRIVILEGES FILE RELOAD
ALTER INDEX SELECT
CREATE INSERT SHUTDOWN
DELETE PROCESS UPDATE
DROP REFERENCES USAGE
ALL is a synonym for ALL PRIVILEGES. REFERENCES is not yet implemented. USAGE is
currently a synonym for “no privileges.” It can be used when you want to create a user
that has no privileges.
To revoke the grant privilege from a user, use a priv_type value of GRANT OPTION:
REVOKE GRANT OPTION ON ... FROM ...;
The only priv_type values you can specify for a table are SELECT, INSERT, UPDATE, DELETE,
CREATE, DROP, GRANT, INDEX, and ALTER.
The only priv_type values you can specify for a column (that is, when you use a column_
list clause) are SELECT, INSERT, and UPDATE.
You can set global privileges by using ON *.* syntax. You can set database privileges by
using ON db_name.* syntax. If you specify ON * and you have a current database, you will
set the privileges for that database. (Warning: If you specify ON * and you don’t have a
current database, you will a ect the global privileges!)
In order to accommodate granting rights to users from arbitrary hosts, MySQL supports
specifying the user_name value in the form user@host. If you want to specify a user string
containing special characters (such as ‘-’), or a host string containing special characters
or wild-card characters (such as ‘%’), you can quote the user or host name (for example,
’test-user’@’test-hostname’).
You can specify wild cards in the hostname. For example, user@"%.loc.gov" applies to
user for any host in the loc.gov domain, and user@"144.155.166.%" applies to user for
any host in the 144.155.166 class C subnet.
The simple form user is a synonym for user@"%". Note: If you allow anonymous users
to connect to the MySQL server (which is the default), you should also add all local users
as user@localhost because otherwise the anonymous user entry for the local host in the
mysql.user table will be used when the user tries to log into the MySQL server from the
local machine! Anonymous users are defined by inserting entries with User=’’ into the
mysql.user table. You can verify if this applies to you by executing this query:
mysql> SELECT Host,User FROM mysql.user WHERE User=’’;
For the moment, GRANT only supports host, table, database, and column names up to 60
characters long. A user name can be up to 16 characters.
The privileges for a table or column are formed from the logical OR of the privileges at each
of the four privilege levels. For example, if the mysql.user table specifies that a user has
a global select privilege, this can’t be denied by an entry at the database, table, or column
level.
The privileges for a column can be calculated as follows:
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
In most cases, you grant rights to a user at only one of the privilege levels, so life isn’t
normally as complicated as above. The details of the privilege-checking procedure are
presented in Section 4.2 [Privilege system], page 173.
If you grant privileges for a user/hostname combination that does not exist in the
mysql.user table, an entry is added and remains there until deleted with a DELETE
command. In other words, GRANT may create user table entries, but REVOKE will not
remove them; you must do that explicitly using DELETE.
In MySQL Version 3.22.12 or later, if a new user is created or if you have global grant
privileges, the user’s password will be set to the password specified by the IDENTIFIED BY
clause, if one is given. If the user already had a password, it is replaced by the new one.
Warning: If you create a new user but do not specify an IDENTIFIED BY clause, the user
has no password. This is insecure.
Passwords can also be set with the SET PASSWORD command. See Section 5.5.6 [SET OPTION],
If you grant privileges for a database, an entry in the mysql.db table is created if needed.
When all privileges for the database have been removed with REVOKE, this entry is deleted.
If a user doesn’t have any privileges on a table, the table is not displayed when the user
requests a list of tables (for example, with a SHOW TABLES statement).
The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges
the user has at the specified privilege level. You should be careful to whom you give the
grant privilege, as two users with di erent privileges may be able to join privileges!
You cannot grant another user a privilege you don’t have yourself; the grant privilege allows
you to give away only those privileges you possess.
Be aware that when you grant a user the grant privilege at a particular privilege level,
any privileges the user already possesses (or is given in the future!) at that level are also
grantable by that user. Suppose you grant a user the insert privilege on a database. If you
then grant the select privilege on the database and specify WITH GRANT OPTION, the user
can give away not only the select privilege, but also insert. If you then grant the update
privilege to the user on the database, the user can give away the insert, select and update.
You should not grant alter privileges to a normal user. If you do that, the user can try to
subvert the privilege system by renaming tables!
Note that if you are using table or column privileges for even one user, the server examines
table and column privileges for all users and this will slow down MySQL a bit.
When mysqld starts, all privileges are read into memory. Database, table, and column priv-
ileges take e ect at once, and user-level privileges take e ect the next time the user connects.
Modifications to the grant tables that you perform using GRANT or REVOKE are noticed by the
server immediately. If you modify the grant tables manually (using INSERT, UPDATE, etc.),
you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges
to tell the server to reload the grant tables. See Section 4.3.3 [Privilege changes], page 197.
The biggest di erences between the ANSI SQL and MySQL versions of GRANT are:
• In MySQL privileges are given for an username + hostname combination and not only
for an username.
• ANSI SQL doesn’t have global or database-level privileges, and ANSI SQL doesn’t
support all privilege types that MySQL supports. MySQL doesn’t support the ANSI
SQL TRIGGER, EXECUTE or UNDER privileges.
• ANSI SQL privileges are structured in a hierarchal manner. If you remove an user, all
privileges the user has granted are revoked. In MySQL the granted privileges are not
automatically revoked, but you have to revoke these yourself if needed.
• If you in MySQL have the INSERT grant on only part of the columns in a table, you
can execute INSERT statements on the table; The columns for which you don’t have
the INSERT privilege will set to their default values. ANSI SQL requires you to have
the INSERT privilege on all columns.
• When you drop a table in ANSI SQL, all privileges for the table are revoked. If you
revoke a privilege in ANSI SQL, all privileges that were granted based on this privilege
are also revoked. In MySQL, privileges can be dropped only with explicit REVOKE
commands or by manipulating the MySQL grant tables.
MySQL User Names and Passwords
There are several distinctions between the way user names and passwords are used by
MySQL and the way they are used by Unix or Windows:
• User names, as used by MySQL for authentication purposes, have nothing to do with
Unix user names (login names) or Windows user names. 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. Client programs allow a di erent name to be specified with the
-u or --user options. This means that you can’t make a database secure in any way
unless all MySQL user names have passwords. Anyone may attempt to connect to the
server using any name, and they will succeed if they specify any name that doesn’t
have a password.
• MySQL user names can be up to 16 characters long; Unix user names typically are
limited to 8 characters.
• MySQL passwords have nothing to do with Unix passwords. There is no necessary
connection between the password you use to log in to a Unix machine and the password
you use to access a database on that machine.
• MySQL encrypts passwords using a di erent algorithm than the one used during the
Unix login process. See the descriptions of the PASSWORD() and ENCRYPT() functions
in Section 6.3.5.2 [Miscellaneous functions], page 395. Note that even if the password
is stored ’scrambled’, and knowing your ’scrambled’ password is enough to be able to
connect to the MySQL server!
MySQL users and their privileges are normally created with the GRANT command.
mysql --user=monty --password=guess database_name
If you want the client to prompt for a password, you should use --password without any
argument
mysql --user=monty --password database_name
or the short form:
mysql -u monty -p database_name
Note that in the last example the password is not ’database name’.
If you want to use the -p option to supply a password you should do so like this:
mysql -u monty -pguess database_name
On some systems, the library call that MySQL uses to prompt for a password will auto-
matically cut the password to 8 characters. Internally MySQL doesn’t have any limit for
the length of the password.
When Privilege Changes Take E ect
When mysqld starts, all grant table contents are read into memory and become e ective at
that point.
Modifications to the grant tables that you perform using GRANT, REVOKE, or SET PASSWORD
are noticed by the server immediately.
If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should exe-
cute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges or mysqladmin
reload to tell the server to reload the grant tables. Otherwise your changes will have no
e ect until you restart the server. If you change the grant tables manually but forget to
reload the privileges, you will be wondering why your changes don’t seem to make any
di erence!
When the server notices that the grant tables have been changed, existing client connections
are a ected as follows:
• Table and column privilege changes take e ect with the client’s next request.
• Database privilege changes take e ect at the next USE db_name command.
Global privilege changes and password changes take e ect the next time the client connects.
4.3.4 Setting Up the Initial MySQL Privileges
After installing MySQL, you set up the initial access privileges by running scripts/mysql_
install_db. See Section 2.3.1 [Quick install], page 69. The mysql_install_db script
starts up the mysqld server, then initialises the grant tables to contain the following set of
privileges:
• The MySQL root user is created as a superuser who can do anything. Connections
must be made from the local host.
Note: The initial root password is empty, so anyone can connect as root without a
password and be granted all privileges.
• An anonymous user is created that can do anything with databases that have a name of
’test’ or starting with ’test_’. Connections must be made from the local host. This
means any local user can connect without a password and be treated as the anonymous
user.
• Other privileges are denied. For example, normal users can’t use mysqladmin shutdown
or mysqladmin processlist.
Note: The default privileges are di erent for Windows. See Section 2.6.2.3 [Windows
running], page 101.
Because your installation is initially wide open, one of the first things you should do is
specify a password for the MySQL root user. You can do this as follows (note that you
specify the password using the PASSWORD() function):
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD(’new_password’)
WHERE user=’root’;
mysql> FLUSH PRIVILEGES;
You can, in MySQL Version 3.22 and above, use the SET PASSWORD statement:
shell> mysql -u root mysql
mysql> SET PASSWORD FOR root=PASSWORD(’new_password’);
Another way to set the password is by using the mysqladmin command:
shell> mysqladmin -u root password new_password
Only users with write/update access to the mysql database can change the password for
others users. All normal users (not anonymous ones) can only change their own password
with either of the above commands or with SET PASSWORD=PASSWORD(’new password’).
Note that if you update the password in the user table directly using the first method,
you must tell the server to re-read the grant tables (with FLUSH PRIVILEGES), because the
change will go unnoticed otherwise.
Once the root password has been set, thereafter you must supply that password when you
connect to the server as root.
You may wish to leave the root password blank so that you don’t need to specify it while you
perform additional setup or testing. However, be sure to set it before using your installation
for any real production work.
See the scripts/mysql_install_db script to see how it sets up the default privileges. You
can use this as a basis to see how to add other users.
If you want the initial privileges to be di erent than those just described above, you can
modify mysql_install_db before you run it.
To re-create the grant tables completely, remove all the ‘.frm’, ‘.MYI’, and ‘.MYD’ files in
the directory containing the mysql database. (This is the directory named ‘mysql’ under
the database directory, which is listed when you run mysqld --help.) Then run the mysql_
install_db script, possibly after editing it first to have the privileges you want.
Note: For MySQL versions older than Version 3.22.10, you should not delete the ‘.frm’
files.
| | |
before running mysql_install_db.
0 commenti:
Post a Comment