( Access denied

Info Catalog ( Privilege changes ( Privilege system ( Password hashing
 5.5.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 those on which the server is listening. To correct
      this when you invoke a client program, specify a `--port' option
      to indicate the proper port, or a `--socket' option to indicate
      the proper named pipe or Unix socket file.  To find out what port
      is used, and where the socket is, you can do:
           shell> netstat -l | 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 on RPM distributions on Linux),
      the installation process initializes the `mysql' database
      containing the grant tables.  For distributions that do not do
      this, you should initialize the grant tables manually by running
      the `mysql_install_db' script.  For details, see  Unix
      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 users.  For
      instructions on setting the initial passwords, see  Default
    * If you have updated an existing MySQL installation to a newer
      version, did you run the `mysql_fix_privilege_tables' 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  Upgrading-grant-tables.
    * 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 don't have an 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' or
      `\windows\hosts' file to add an entry for your host.
    * Remember that client programs will use connection parameters
      specified in option files or environment variables.  If a client
      program seems to be sending incorrect default connection
      parameters when you don't specify 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 haven't 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 Environment
DONTPRINTYET       The option files that clients use are listed in  Option
      files.  Environment variables are listed in  Environment

    * 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 haven't 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.  
      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 will not be
      able to connect afterward:
           mysql> SET PASSWORD FOR 'abe'@'HOST_NAME' = 'eagle';
      Instead, set the password like this:
           mysql> SET PASSWORD FOR 'abe'@'HOST_NAME' = PASSWORD('eagle');
      The `PASSWORD()' function is unnecessary when you specify a
      password using the `GRANT' statement or the `mysqladmin password'
      command, both of which automatically use `PASSWORD()' to encrypt
      the password.   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' on Unix systems
      do not work if you are using a MySQL version older than 3.23.27
      that uses MIT-pthreads: `localhost' connections are made using
      Unix socket files, which were not supported by MIT-pthreads at
      that time.
      To avoid this problem on such systems, you can use a
      `--host=' 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
    * 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:
           mysql> SELECT * FROM user;
      The result should include an row with the `Host' and `User'
      columns matching your computer's hostname and your MySQL username.
    * The `Access denied' error message will tell you who you are trying
      to log in as, the client host from which you are trying to
      connect, and whether or not 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 an
    * 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
      If you don't know the IP number or hostname of the machine from
      which you are connecting, you should put an row 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 change the `'%'' in
      the `user' table row to the actual hostname that shows up in the
      log.  Otherwise, you'll have a system that is 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 isn't 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.  DNS.
      Some permanent solutions are:
         - Try to find out what is wrong with your DNS server and fix it.
         - Specify IP numbers rather than hostnames in the MySQL grant
         - Put an entry for the client machine name in `/etc/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
      `''', 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' 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' doesn't 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 can't 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 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 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
      doesn't 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
    * 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' option alone, MySQL will prompt 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 allows 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
      will print host and user information about attempted connections,
      as well as information about each command issued.  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.   Bug reports.  In some cases, you may
      need to restart `mysqld' with `--skip-grant-tables' to run
Info Catalog ( Privilege changes ( Privilege system ( Password hashing
automatically generated byinfo2html