Causes of Access-Denied Errors
594
entry to the
user
table with a
Host
value that contains a wildcard; for example,
'pluto.%'
.
However, use of
Host
values ending with “
%
” is insecure and is not recommended!)
• If
mysql -u user_name test
works but
mysql -u user_name other_db
does not, you have
not granted access to the given user for the database named
other_db
.
• If
mysql -u user_name
works when executed on the server host, but
mysql -h host_name -
u user_name
does not work when executed on a remote client host, you have not enabled access
to the server for the given user name from the remote host.
• If you cannot figure out why you get
Access denied
, remove from the
user
table all entries that
have
Host
values containing wildcards (entries that contain
'%'
or
'_'
characters). A very common
error is to insert a new entry with
Host
=
'%'
and
User
=
'some_user'
, thinking that this enables
you to specify
localhost
to connect from the same machine. The reason that this does not work
is that the default privileges include 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. See also
Section 6.2.4, “Access Control, Stage 1: Connection Verification”
.
• If you are able to connect to the MySQL server, but get an
Access denied
message whenever you
issue a
SELECT ... INTO OUTFILE
or
LOAD DATA INFILE
statement, your entry in the
user
table does not have the
FILE
[577]
privilege enabled.
• If you change the grant tables directly (for example, by using
INSERT
,
UPDATE
, or
DELETE
statements) and your changes seem to be ignored, remember that you must execute a
FLUSH
PRIVILEGES
statement or a
mysqladmin flush-privileges
command to cause the server to
reload the privilege tables. Otherwise, your changes have no effect until the next time the server is
restarted. Remember that after you change the
root
password with an
UPDATE
statement, you will
not need to specify the new password until after you flush the privileges, because the server will not
know you've changed the password yet!
• If your privileges seem to have changed in the middle of a session, it may be that a MySQL
administrator has changed them. Reloading the grant tables affects new client connections, but
it also affects existing connections as indicated in
Section 6.2.6, “When Privilege Changes Take
Effect”
.
• If you have access problems with a Perl, PHP, Python, or ODBC program, try to connect to
the server with
mysql -u 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
[231]
syntax to specify the password. If you use the
-p
or
--
password
[231]
option with no password value, MySQL prompts you for the password.)
• For testing purposes, start the
mysqld
server with the
--skip-grant-tables
[420]
option.
Then you can change the MySQL grant tables and use the
mysqlaccess
script to check whether
your modifications have the desired effect. When you are satisfied with your changes, execute
mysqladmin flush-privileges
to tell the
mysqld
server to reload the privileges. This enables
you to begin using the new grant table contents without stopping and restarting the server.
• If you get the following error, you may have a problem with the
db
or
host
table:
Access to database denied
If the entry selected from the
db
table has an empty value in the
Host
column, make sure that there
are one or more corresponding entries in the
host
table specifying which hosts the
db
table entry
applies to. This problem occurs infrequently because the
host
table is rarely used.
• If everything else fails, start the
mysqld
server with a debugging option (for example,
--
debug=d,general,query
[405]
). This prints host and user information about attempted
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
