Home arrow MySQL arrow Page 6 - Working with the MySQL Access Privilege System

4.4.7 When Privilege Changes Take Effect - MySQL

If you need to administer MySQL, this article gets you off to a good start. In this section, we continue our discussion of security issues with the MySQL access privilege system. The third of a multi-part series, it is excerpted from chapter four of the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345).

  1. Working with the MySQL Access Privilege System
  2. 4.4.3 Privileges Provided by MySQL
  3. 4.4.4 Connecting to the MySQL Server
  4. 4.4.5 Access Control, Stage 1: Connection Verification
  5. 4.4.6 Access Control, Stage 2: Request Verification
  6. 4.4.7 When Privilege Changes Take Effect
  7. 4.4.9 Password Hashing in MySQL 4.1
By: Sams Publishing
Rating: starstarstarstarstar / 6
June 08, 2006

print this article



When mysqld starts, all grant table contents are read into memory and become effective for access control at that point.

When the server reloads the grant tables, privileges for existing client connections are affected as follows:

  • Table and column privilege changes take effect with the client's next request.

  • Database privilege changes take effect at the next USE db_name statement.

  • Changes to global privileges and passwords take effect the next time the client connects.

If you modify the grant tables using GRANT, REVOKE, or SET PASSWORD, the server notices these changes and reloads the grant tables into memory again immediately.

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE, your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables. To reload the grant tables manually, issue a FLUSH PRIVILEGES statement or execute a mysqladmin flush-privileges or mysqladmin reload command.

If you change the grant tables directly but forget to reload them, your changes will have no effect until you restart the server. This may leave you wondering why your changes don't seem to make any difference!

4.4.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.

  • The grant tables must be properly set up so that the server can use them for access control. For installations on Windows using a binary distribution or on Linux using a server RPM distribution, the installation process initializes the mysql database containing the grant tables. For other MySQL installation types, you should initialize the grant tables manually by running the mysql_install_db script. See Section 2.4.2, "Unix Post-Installation Procedures."

    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 Section 2.4.5, "Securing the Initial MySQL Accounts."

  • 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 Section 2.5.8, "Upgrading the 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 Section 4.4.9, "Password Hashing in MySQL 4.1," and Section A.2.3, "Client does not support authentication protocol."

  • If you try to connect as root and get the following error, it 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:

    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
  • The option files that clients use are listed in Section 3.3.2, "Using Option Files." Environment variables are listed in Appendix B, "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 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 Section 4.5.5, "Assigning Account Passwords."

    If you have lost or forgotten the root password, you can restart mysqld with --skip-grant-tables to change the password. See Section A.4.1, "How to Reset the Root Password."

  • 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' =
  • Instead, set the password like this:

    mysql> SET PASSWORD FOR 'abe'@'host_name' =
  • 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. See Section 4.5.5, "Assigning Account 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 entry 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:

    mysql> SELECT * FROM user;
  • The result should include an entry 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 entry 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 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 change the '%' in the user table entry 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. See Section 6.5.5, "How MySQL Uses 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 tables.

    • 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 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 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 Section 4.4.7, "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 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.

  • 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. See Section, "How to Report Bugs or Problems." In some cases, you may need to restart mysqld with --skip-grant-tables to run mysqldump.

>>> More MySQL Articles          >>> More By Sams Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: