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.
and denial of
service. We do not cover all aspects of availability and fault tolerance
here.
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()', `SHA1()' 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 difficult 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 different 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_real_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 MySQL data streams are unencrypted by
issuing a command like the following:
shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings
(This works under Linux and should work with small modifications
under other systems.) Warning: If you do not see data this
doesn't always actually mean that it is encrypted. If you need
high security, you should consult with a security expert.
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
effort a clever attacker can crack the password if he is able to sniff
the traffic 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.
*Note Secure connections::.
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 behaviour 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, because any user with the `FILE' privilege 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.
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 manually or by using `mysqld_safe' or `mysql.server'.
For more details, see *Note Changing MySQL user::.
* Don't support symlinks to tables (this can be disabled with the
`--skip-symlink' option). 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! *Note
Symbolic links to tables::.
* Check that the Unix user that `mysqld' runs as is the only user
with read/write privileges in the database directories.
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 filesystem with
the privileges of the `mysqld' daemon! To make this a bit safer,
all files generated with `SELECT ... INTO OUTFILE' are writeable
by everyone, and you cannot overwrite existing files.
The `FILE' privilege may also be used to read any world readable
file that is accessible to the Unix user that the server runs as.
One can also read any file to the current database (which the user
need some privilege for). 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 wildcards!
* 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'.
Startup Options for `mysqld' Concerning Security
------------------------------------------------
The following `mysqld' options affect security:
`--local-infile[=(0|1)]'
If one uses `--local-infile=0' then one can't use `LOAD DATA LOCAL
INFILE'.
`--safe-show-database'
With this option, the `SHOW DATABASES' command returns only those
databases for which the user has some kind of privilege. From
version 4.0.2 this option is deprecated and doesn't do anything
(the option is enabled by default) as we now have the `SHOW
DATABASES' privilege. *Note GRANT::.
`--safe-user-create'
If this is enabled, an user can't create new users with the `GRANT'
command, if the user doesn't have the `INSERT' privilege for 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:
mysql> 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 *full 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 when using a MySQL version prior to 3.23.27 with the
MIT-pthreads package, because Unix sockets were not supported by
MIT-pthreads at that time.
`--skip-show-database'
Don't allow `SHOW DATABASES' command, unless the user has the
`SHOW DATABASES' privilege. From version 4.0.2 you should no longer
need this option, since access can now be granted specifically
with the `SHOW DATABASES' privilege.
Security issues with `LOAD DATA LOCAL'
--------------------------------------
In MySQL 3.23.49 and MySQL 4.0.2, we added some new options to deal with
possible security issues when it comes to `LOAD DATA LOCAL'.
There are two possible problems with supporting this command:
As the reading of the file is initiated from the server, one could
theoretically create a patched MySQL server that could read any file on
the client machine that the current user has read access to, when the
client issues a query against the table.
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 a user could run any
command against the SQL server).
There are two separate fixes for this:
If you don't configure MySQL with `--enable-local-infile', then `LOAD
DATA LOCAL' will be disabled by all clients, unless one calls
`mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0)' in the client. *Note
`mysql_options()': mysql_options.
For the `mysql' command-line client, `LOAD DATA LOCAL' can be enabled
by specifying the option `--local-infile[=1]', or disabled with
`--local-infile=0'.
By default, all MySQL clients and libraries are compiled with
`--enable-local-infile', to be compatible with MySQL 3.23.48 and before.
One can disable all `LOAD DATA LOCAL' commands in the MySQL server by
starting `mysqld' with `--local-infile=0'.
In the case that `LOAD DATA LOCAL INFILE' is disabled in the server or
the client, you will get the error message (1148):
The used command is not allowed with this MySQL version
What the Privilege System Does
------------------------------
and
`DELETE'.
Additional functionality includes the ability to have an anonymous user
and to grant privileges for MySQL-specific functions such as `LOAD DATA
INFILE' and administrative operations.
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
`joe' who connects from `office.com' need not be the same person as the
user `joe' who connects from `elsewhere.com'. MySQL handles this by
allowing you to distinguish users on different hosts that happen to
have the same name: you can grant `joe' one set of privileges for
connections from `office.com', and a different set of privileges for
connections from `elsewhere.com'.
MySQL access control involves two stages:
* Stage 1: The server checks whether you are even allowed to connect.
* Stage 2: Assuming you can connect, the server checks each request
you issue to see 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 makes sure
you have the `SELECT' privilege for the table or the `DROP'
privilege for the database.
Note that if your privileges are changed (either by yourself or someone
else) while you are connected, those changes will not necessarily take
effect with your next query or queries. See *Note Privilege changes::
for details.
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 here:
*Table name* *user* *db* *host*
*Scope fields* `Host' `Host' `Host'
`User' `Db' `Db'
`Password' `User'
*Privilege `Select_priv' `Select_priv' `Select_priv'
fields*
`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'`References_priv'`References_priv'
`Reload_priv'
`Shutdown_priv'
`Process_priv'
`File_priv'
`Show_db_priv'
`Super_priv'
`Create_tmp_table_priv'`Create_tmp_table_priv'`Create_tmp_table_priv'
`Lock_tables_priv'`Lock_tables_priv'`Lock_tables_priv'
`Execute_priv'
`Repl_slave_priv'
`Repl_client_priv'
`ssl_type'
`ssl_cypher'
`x509_issuer'
`x509_cubject'
`max_questions'
`max_updates'
`max_connections'
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 here:
*Table name* *tables_priv* *columns_priv*
*Scope `Host' `Host'
fields*
`Db' `Db'
`User' `User'
`Table_name' `Table_name'
`Column_name'
*Privilege `Table_priv' `Column_priv'
fields*
`Column_priv'
*Other `Timestamp' `Timestamp'
fields*
`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
*Note Request access::.
Scope fields are strings, declared as shown here; the default value for
each is the empty string:
`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 *Field *Possible set elements*
name* name*
`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''
Briefly, the server uses the grant tables like this:
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. This
mechanism is described more detail in *Note Request access::.
* 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 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. Changes to the grant tables take effect as indicated in
*Note Privilege changes::.
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 *Note Access denied::. For advice on
security issues, see *Note Security::.
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
----------------------------
reads the contents of these tables when it starts up and under the
circumstances indicated in *Note Privilege changes::.
in
which the privilege applies. Further information about the meaning of
each privilege may be found at *Note `GRANT': GRANT.
*Privilege* *Column* *Context*
`ALTER' `Alter_priv' tables
`DELETE' `Delete_priv' tables
`INDEX' `Index_priv' tables
`INSERT' `Insert_priv' tables
`SELECT' `Select_priv' tables
`UPDATE' `Update_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
`CREATE `Create_tmp_table_priv'server administration
TEMPORARY
TABLES'
`EXECUTE' `Execute_priv' server administration
`FILE' `File_priv' file access on server
`LOCK `Lock_tables_priv'server administration
TABLES'
`PROCESS' `Process_priv' server administration
`RELOAD' `Reload_priv' server administration
`REPLICATION`Repl_client_priv'server administration
CLIENT'
`REPLICATION`Repl_slave_priv'server administration
SLAVE'
`SHOW `Show_db_priv' server administration
DATABASES'
`SHUTDOWN' `Shutdown_priv'server administration
`SUPER' `Super_priv' server administration
The `SELECT', `INSERT', `UPDATE', and `DELETE' privileges allow you to
perform operations on rows in existing tables in a database.
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.
read any
world readable file accessable by the MySQL server and create a new
world readable file in any directory where the MySQL server can write.
The user can also read any file in the current database directory. The
user can however not change any existing file.
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'
`SUPER' `kill'
The `reload' command tells the server to re-read the grant tables. The
`refresh' command flushes 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 flush just the log files, `flush-logs' is a better choice
than `refresh'.
The `shutdown' command shuts down the server.
`PROCESS' privilege to display and `SUPER' privilege to kill threads
initiated by other users. *Note KILL::.
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 different 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 or any file in the current database directory on the
server into a database table, the contents of which can then be
accessed using `SELECT'.
* 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 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 sufficient privileges, the same user can replace a
password with a different 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
------------------------------
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'. *Note Option files::.
`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.
done
in a couple of ways:
* You can specify connection parameters in the `[client]' section of
the `.my.cnf' configuration 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
*Note Option files::.
Windows
only). The password can be specified using `MYSQL_PWD' (but this
is insecure; see the next section). *Note Environment variables::.
Access Control, Stage 1: Connection Verification
------------------------------------------------
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, 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 wildcard 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:
mysql> 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.
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.
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! From version 4.1, MySQL employs a different
password and login mechanism that is secure even if TCP/IP packets are
sniffed and/or the mysql database is captured.
The examples here show how various combinations of `Host' and `User'
values in `user' table entries apply to incoming connections:
`Host' *value* `User' *Connections matched by entry*
*value*
`'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 wildcard 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 wildcard 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 shown in the preceding
table. 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:
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.
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.
If connection was successful, but your privileges are not what you
expected you may use `CURRENT_USER()' function (new in version 4.0.6)
to see what user/host combination your connection actually matched.
*Note `CURRENT_USER()': Miscellaneous functions.
Access Control, Stage 2: Request Verification
---------------------------------------------
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. *Note
`GRANT': GRANT. (You may find it helpful to refer to *Note
Privileges::, which lists the fields 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 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 wildcard characters `%' and `_' can be used in the `Host' and
`Db' fields of either table. If you wish to use for instance a `_'
character as part of a database name, specify it as `\_' in the
`GRANT' command.
* 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.
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 wildcard 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
wildcards or be blank in either table.
contain wildcards.
code.
The description is equivalent to what the code actually does; it
differs only to make the explanation simpler.)
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
insufficient, 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.)
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 calculated 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 insufficient 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.
allow access
to all hosts on your network except that machine by using `host' table
entries like this:
... (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.
Password Hashing in MySQL 4.1
-----------------------------
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:
* First, when a client attempts to connect to the server, there is an
initial authentication step in which the client must present a
password that matches the hash value stored in the user table for
the account that the client wants to use.
* Second, after the client connects, it may set or change the
password hashes for accounts listed in the user table (if it has
sufficient privileges). The client may 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 stolen.
However, this new mechanism is understood only by the 4.1 server and
4.1 clients, which can result in some compatibility problems. A 4.1
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 server may run into
difficulties. For example, a 4.0 `mysql' client that attempts to
connect to a 4.1 server may fail with the following error message:
shell> mysql
Client does not support authentication protocol requested
by server; consider upgrading MySQL client
The following discussion describes the differences between the old and
new password mechanisms, and what you should do if you upgrade your
server to 4.1 but need to maintain backward compatibility with pre-4.1
clients.
*Note:* This discussion contrasts 4.1 behaviour with pre-4.1 behaviour,
but the 4.1 behaviour described here actually begins with 4.1.1. MySQL
4.1.0 is an "odd" release because it has a slightly different mechanism
than that implemented in 4.1.1 and up. Differences between 4.1.0 and
more recent versions are described later.
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') |
+-----------------------------------------------+
| *43c8aa34cdc98eddd3de1fe9a9c2c2a9f92bb2098d75 |
+-----------------------------------------------+
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 4.1, the `Password'
column will be made 41 bytes long automatically.
change
existing password values, which remain 16 bytes long.)
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 narrow, only short-hash authentication is used.
* If the column is wide, it can hold either short or long hashes, and
the server can use either format:
* Pre-4.1 clients can connect, though because they know only
about the old hashing mechanism, they can authenticate only
for accounts that have short hashes.
* 4.1 clients can authenticate for accounts that have short or
long hashes.
* Pre-4.1 client authenticating for account with short password hash
* 4.1 client authenticating for account with short password hash
* 4.1 client authenticating for account 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 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 (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 behaviour occurs if you have upgraded to
4.1 but have not run the `mysql_fix_privilege_tables' script 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' will generate long hashes unless the server was started
with the `--old-passwords' option. This option forces the server
to generate short passsword hashes instead.
The purpose of the `--old-passwords' option is to allow you to maintain
backward compatibility with pre-4.1 clients under circumstances where
the server would otherwise generate long password hashes. It doesn't
affect authentication (4.1 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
scenario is possible:
* An old client connects to an account that has a short password
hash.
* The client changes the account's 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 now requires the new hashing mechanism
during authentication. (Once an account has a long password hash in
the user table, only 4.1 clients can authenticate for it, because
pre-4.1 clients do not understand long hashes.)
This scenario illustrates that it is dangerous to run a 4.1 server
without using the `--old-passwords' option if you must support older
pre-4.1 clients. By running the server with `--old-passwords',
password-changing operations will 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 will 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 server:
Scenario 1) Narrow `Password' column in user table
* Only short hashes can be stored in the `Password' column.
* The server uses only short hashes during client authentication.
that
account having a short password hash.
* The `--old-passwords' option can be used but is superfluous because
with a narrow `Password' column, the server will be generating
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 clients can authenticate for accounts that have short or long
hashes.
* Pre-4.1 clients can authenticate only for accounts that have short
hashes.
that
account having a long password hash.
* `OLD_PASSWORD()' may be used to explicitly generate a short hash.
For example, to assign an account a short password, use `UPDATE'
as follows:
mysql> UPDATE user SET Password = OLD_PASSWORD('mypass')
-> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;
made via
`GRANT', `SET PASSWORD', or `PASSWORD()' results in the account being
given a long password hash, and from that point on, no pre-4.1 client
can authenticate to that account until the client upgrades to 4.1.
Scenario 3) Long `Password' column; server started with
`--old-passwords' option
* Short or long hashes can be stored in the `Password' column.
* 4.1 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.
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:
Scenario 1) You cannot take advantage of longer hashes that provide more
secure authentication.
Scenario 2) Accounts with short hashes become inaccessible to pre-4.1
clients if you change their passwords without explicitly using
`OLD_PASSWORD()'.
change them
back to long hashes while `--old-passwords' is in effect.
Implications of Password Hashing Changes for Application Programs
-----------------------------------------------------------------
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 and run the server under conditions where it generates
long password hashes, an application that uses `PASSWORD()' for its own
passwords will break. The recommended course of action is to modify
the application to use another function such as `SHA1()' or `MD5()' to
produce hashed values. If that is not possible, you can use the
`OLD_PASSWORD()' function, which is provided to generate short hashes
in the old format. (But 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()'.
Password hashing in MySQL 4.1.0 differs from hashing in 4.1.1 and up.
The 4.1.0 differences are:
* Password hashes are 45 bytes long rather than 41 bytes.
* The `PASSWORD()' function is non-repeatable. That is, with a given
argument `X', successive calls to `PASSWORD(X)' generate different
results.
Causes of `Access denied' Errors
--------------------------------
If you encounter `Access denied' errors when you try to connect to the
MySQL server, the following list indicates some courses of action you
can take to correct the problem:
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 an incorrect password. *Note
Passwords::.
If you have forgot the root password, you can restart `mysqld' with
`--skip-grant-tables' to change the password. *Note Resetting
permissions::.
If you get the above error even if you haven't specified a
password, this means that you have an incorrect password in some
`my.ini' file. *Note Option files::. 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. *Note
Upgrading-grant-tables::.
* If your privileges seem to have changed in the middle of a
session, it may be that a superuser has changed them. Reloading
the grant tables affects new client connections, but it also
affects existing connections as indicated in *Note Privilege
changes::.
* 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' statements. The
`PASSWORD()' function is unnecessary if you specify the password
using the `GRANT ... IDENTIFIED BY' statement or the `mysqladmin
password' command. *Note Passwords::.
* `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 using a MySQL version prior to 3.23.27 that uses
MIT-pthreads (`localhost' connections are made using Unix sockets,
which were not supported by MIT-pthreads at that time). 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
different 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.)
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)
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'.
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 qualified 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 wildcard--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.
the
client machine listed in the `user' table or the `db' table.
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 machine. 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 entry 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 configuration files or environment variables. *Note
Environment variables::. 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. *Note Option
files::. If you get `Access denied' when you run a client without
any options, make sure you haven't specified an old password in
any of your option files! *Note Option files::.
* If you make changes to the grant tables directly (using an
`INSERT' or `UPDATE' statement) 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 effect until the next time the server is
restarted. Remember that after you set the `root' password with
an `UPDATE' command, you won't need to specify it until after you
flush 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 connect 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 your
modifications have the desired effect. 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 example, `--debug=d,general,query'). This
will print host and user information about attempted connections,
as well as information about each command issued. *Note Making
trace files::.
* 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. As always, post your problem using the
`mysqlbug' script. *Note Bug reports::. In some cases you may
need to restart `mysqld' with `--skip-grant-tables' to run
`mysqldump'.
[Назад] [Содержание] [Вперед]
| Главная |