(mysql.info) access-denied
Info Catalog
(mysql.info) privilege-changes
(mysql.info) privilege-system
(mysql.info) password-hashing
5.8.8 Causes of `Access denied' Errors
--------------------------------------
If you encounter problems when you try to connect to the MySQL server,
the following items describe some courses of action you can take to
correct the problem.
* Make sure that the server is running. If it is not running, you
cannot connect to it. For example, if you attempt to connect to
the server and see a message such as one of those following, one
cause might be that the server is not running:
shell> mysql
ERROR 2003: Can't connect to MySQL server on 'HOST_NAME' (111)
shell> mysql
ERROR 2002: Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (111)
It might also be that the server is running, but you are trying to
connect using a TCP/IP port, named pipe, or Unix socket file
different from the one on which the server is listening. To
correct this when you invoke a client program, specify a -port
option to indicate the proper port number, or a -socket option to
indicate the proper named pipe or Unix socket file. To find out
where the socket file is, you can use this command:
shell> netstat -ln | grep mysql
* The grant tables must be properly set up so that the server can
use them for access control. For some distribution types (such as
binary distributions on Windows, or RPM distributions on Linux),
the installation process initializes the `mysql' database
containing the grant tables. For distributions that do not do
this, you must initialize the grant tables manually by running the
`mysql_install_db' script. For details, see
unix-post-installation.
One way to determine whether you need to initialize the grant
tables is to look for a `mysql' directory under the data
directory. (The data directory normally is named `data' or `var'
and is located under your MySQL installation directory.) Make sure
that you have a file named `user.MYD' in the `mysql' database
directory. If you do not, execute the `mysql_install_db' script.
After running this script and starting the server, test the
initial privileges by executing this command:
shell> mysql -u root test
The server should let you connect without error.
* After a fresh installation, you should connect to the server and
set up your users and their access permissions:
shell> mysql -u root mysql
The server should let you connect because the MySQL `root' user
has no password initially. That is also a security risk, so
setting the password for the `root' accounts is something you
should do while you're setting up your other MySQL accounts. For
instructions on setting the initial passwords, see
default-privileges.
* If you have updated an existing MySQL installation to a newer
version, did you run the `mysql_upgrade' script? If not, do so. The
structure of the grant tables changes occasionally when new
capabilities are added, so after an upgrade you should always make
sure that your tables have the current structure. For
instructions, see mysql-upgrade.
* If a client program receives the following error message when it
tries to connect, it means that the server expects passwords in a
newer format than the client is capable of generating:
shell> mysql
Client does not support authentication protocol requested
by server; consider upgrading MySQL client
For information on how to deal with this, see
password-hashing, and old-client.
* If you try to connect as `root' and get the following error, it
means that you do not have a row in the `user' table with a `User'
column value of `'root'' and that `mysqld' cannot resolve the
hostname for your client:
Access denied for user ''@'unknown' to database mysql
In this case, you must restart the server with the
-skip-grant-tables option and edit your `/etc/hosts' file or
`\windows\hosts' file to add an entry for your host.
* Remember that client programs use connection parameters specified
in option files or environment variables. If a client program
seems to be sending incorrect default connection parameters when
you have not specified them on the command line, check your
environment and any applicable option files. For example, if you
get `Access denied' when you run a client without any options,
make sure that you have not specified an old password in any of
your option files!
You can suppress the use of option files by a client program by
invoking it with the -no-defaults option. For example:
shell> mysqladmin --no-defaults -u root version
DONTPRINTYET The option files that clients use are listed in
option-files. Environment variables are listed in *Note
DONTPRINTYET The option files that clients use are listed in
option-files. Environment variables are listed in
environment-variables.
* If you get the following error, it means that you are using an
incorrect `root' password:
shell> mysqladmin -u root -pXXXX ver
Access denied for user 'root'@'localhost' (using password: YES)
If the preceding error occurs even when you have not specified a
password, it means that you have an incorrect password listed in
some option file. Try the -no-defaults option as described in the
previous item.
For information on changing passwords, see passwords.
If you have lost or forgotten the `root' password, you can restart
`mysqld' with -skip-grant-tables to change the password. See
resetting-permissions.
* If you change a password by using `SET PASSWORD', `INSERT', or
`UPDATE', you must encrypt the password using the `PASSWORD()'
function. If you do not use `PASSWORD()' for these statements, the
password will not work. For example, the following statement sets
a password, but fails to encrypt it, so the user is not able to
connect afterward:
SET PASSWORD FOR 'abe'@'HOST_NAME' = 'eagle';
Instead, set the password like this:
SET PASSWORD FOR 'abe'@'HOST_NAME' = PASSWORD('eagle');
The `PASSWORD()' function is unnecessary when you specify a
password using the `GRANT' or (beginning with MySQL 5.0.2) `CREATE
USER' statements, or the `mysqladmin password' command. Each of
those automatically uses `PASSWORD()' to encrypt the password. See
passwords, and create-user.
* `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.
To avoid this problem on such systems, you can use a
-host=127.0.0.1 option to name the server host explicitly. This
will make a TCP/IP connection to the local `mysqld' server. You
can also use TCP/IP by specifying a -host option that uses the
actual hostname of the local host. In this case, the hostname must
be specified in a `user' table row on the server host, even though
you are running the client program on the same host as the server.
* If you get an `Access denied' error when trying to connect to the
database with `mysql -u USER_NAME', you may have a problem with
the `user' table. Check this by executing `mysql -u root mysql' and
issuing this SQL statement:
SELECT * FROM user;
The result should include a row with the `Host' and `User' columns
matching your computer's hostname and your MySQL username.
* The `Access denied' error message tells you who you are trying to
log in as, the client host from which you are trying to connect,
and whether you were using a password. Normally, you should have
one row in the `user' table that exactly matches the hostname and
username that were given in the error message. For example, if
you get an error message that contains `using password: NO', it
means that you tried to log in without a password.
* If the following error occurs when you try to connect from a host
other than the one on which the MySQL server is running, it means
that there is no row in the `user' table with a `Host' value that
matches the client host:
Host ... is not allowed to connect to this MySQL server
You can fix this by setting up an account for the combination of
client hostname and username that you are using when trying to
connect.
If you do not know the IP number or hostname of the machine from
which you are connecting, you should put a row with `'%'' as the
`Host' column value in the `user' table. After trying to connect
from the client machine, use a `SELECT USER()' query to see how
you really did connect. (Then change the `'%'' in the `user'
table row to the actual hostname that shows up in the log.
Otherwise, your system is left insecure because it allows
connections from any host for the given username.)
On Linux, another reason that this error might occur is that you
are using a binary MySQL version that is compiled with a different
version of the `glibc' library than the one you are using. In this
case, you should either upgrade your operating system or `glibc',
or download a source distribution of MySQL version and compile it
yourself. A source RPM is normally trivial to compile and install,
so this is not a big problem.
* If you specify a hostname when trying to connect, but get an error
message where the hostname is not shown or is an IP number, it
means that the MySQL server got an error when trying to resolve
the IP number of the client host to a name:
shell> mysqladmin -u root -pXXXX -h SOME_HOSTNAME ver
Access denied for user 'root'@'' (using password: YES)
This indicates a DNS problem. To fix it, execute `mysqladmin
flush-hosts' to reset the internal DNS hostname cache. See
dns.
Some permanent solutions are:
* Determine what is wrong with your DNS server and fix it.
* Specify IP numbers rather than hostnames in the MySQL grant
tables.
* Put an entry for the client machine name in `/etc/hosts' or
`\windows\hosts'.
* Start `mysqld' with the -skip-name-resolve option.
* Start `mysqld' with the -skip-host-cache option.
* On Unix, if you are running the server and the client on the
same machine, connect to `localhost'. Unix connections to
`localhost' use a Unix socket file rather than TCP/IP.
* On Windows, if you are running the server and the client on
the same machine and the server supports named pipe
connections, connect to the hostname `.' (period).
Connections to `.' use a named pipe rather than TCP/IP.
* If `mysql -u root test' works but `mysql -h YOUR_HOSTNAME -u root
test' results in `Access denied' (where YOUR_HOSTNAME is the actual
hostname of the local host), you may not have the correct name for
your host in the `user' table. A common problem here is that the
`Host' value in the `user' table row specifies an unqualified
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
does 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' does not, you have not granted database access for
OTHER_DB_NAME to the given user.
* If `mysql -u USER_NAME' works when executed on the server host,
but `mysql -h HOST_NAME -u USER_NAME' does not work when executed
on a remote client host, you have not enabled access to the server
for the given username from the remote host.
* If you cannot figure out why you get `Access denied', remove from
the `user' table all entries that have `Host' values containing
wildcards (entries that contain ‘`%'’ or ‘`_'’). A very
common error is to insert a new entry with `Host'=`'%'' and
`User'=`'SOME_USER'', thinking that this allows you to specify
`localhost' to connect from the same machine. The reason that this
does not work is that the default privileges include an entry with
`Host'=`'localhost'' and `User'=`'''. Because that entry has a
`Host' value `'localhost'' that is more specific than `'%'', it is
used in preference to the new entry when connecting from
`localhost'! The correct procedure is to insert a second entry with
`Host'=`'localhost'' and `User'=`'SOME_USER'', or to delete the
entry with `Host'=`'localhost'' and `User'=`'''. After deleting
the entry, remember to issue a `FLUSH PRIVILEGES' statement to
reload the grant tables.
* If you get the following error, you may have a problem with the
`db' or `host' table:
Access to database denied
If the entry selected from the `db' table has an empty value in
the `Host' column, make sure that there are one or more
corresponding entries in the `host' table specifying which hosts
the `db' table entry applies to.
* If you are able to connect to the MySQL server, but get an `Access
denied' message whenever you issue a `SELECT ... INTO OUTFILE' or
`LOAD DATA INFILE' statement, your entry in the `user' table does
not have the `FILE' privilege enabled.
* If you change the grant tables directly (for example, by using
`INSERT', `UPDATE', or `DELETE' statements) and your changes seem
to be ignored, remember that you must execute a `FLUSH PRIVILEGES'
statement or a `mysqladmin flush-privileges' command to cause the
server to re-read the privilege tables. Otherwise, your changes
have no effect until the next time the server is restarted.
Remember that after you change the `root' password with an
`UPDATE' command, you won't need to specify the new password until
after you flush the privileges, because the server won't know
you've changed the password yet!
* If your privileges seem to have changed in the middle of a
session, it may be that a MySQL administrator has changed them.
Reloading the grant tables affects new client connections, but it
also affects existing connections as indicated in
privilege-changes.
* 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, the problem lies with
your program, not with the access privileges. (There is no space
between -p and the password; you can also use the
-password=YOUR_PASS syntax to specify the password. If you use the
-p -passwordoption with no password value, MySQL prompts you for
the password.)
* For testing, start the `mysqld' server with the -skip-grant-tables
option. Then you can change the MySQL grant tables and use the
`mysqlaccess' script to check whether your modifications have the
desired effect. When you are satisfied with your changes, execute
`mysqladmin flush-privileges' to tell the `mysqld' server to start
using the new grant tables. (Reloading the grant tables overrides
the -skip-grant-tables option. This enables you to tell the server
to begin using the grant tables again without stopping and
restarting it.)
* If everything else fails, start the `mysqld' server with a
debugging option (for example, -debug=d,general,query). This
prints host and user information about attempted connections, as
well as information about each command issued. See
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. To file a bug report, see the
instructions at bug-reports. In some cases, you may need
to restart `mysqld' with -skip-grant-tables to run `mysqldump'.
Info Catalog
(mysql.info) privilege-changes
(mysql.info) privilege-system
(mysql.info) password-hashing
automatically generated byinfo2html