Skip navigation links

Additional languages
5.5.7 Using SSL for Secure Connections »
Section Navigation      [Toggle]

5.5.6. Keeping Passwords Secure

On an administrative level, you should never grant access to the user grant table to any non-administrative accounts.

When you run a client program to connect to the MySQL server, it is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed here, along with an assessment of the risks of each method:

  • Use a -pyour_pass or --password=your_pass option on the command line. For example: 

    shell> mysql -u francis -pfrank db_name

    This is convenient but insecure, because your password becomes visible to system status programs such as ps that may be invoked by other users to display command lines. MySQL clients typically overwrite the command-line password argument with zeros during their initialization sequence. However, there is still a brief interval during which the value is visible. On some systems this strategy is ineffective, anyway, and the password remains visible to ps. (SystemV Unix systems and perhaps others are subject to this problem.)

    If your operating environment is set up to display your current command in the title bar of your terminal window, the password remains visible as long as the command is running, even if the command has scrolled out of view in the window content area.

  • Use the -p or --password option with no password value specified. In this case, the client program solicits the password from the terminal: 

    shell> mysql -u francis -p db_name
Enter password: ********

    The “*” characters indicate where you enter your password. The password is not displayed as you enter it.

    It is more secure to enter your password this way than to specify it on the command line because it is not visible to other users. However, this method of entering a password is suitable only for programs that you run interactively. If you want to invoke a client from a script that runs non-interactively, there is no opportunity to enter the password from the terminal. On some systems, you may even find that the first line of your script is read and interpreted (incorrectly) as your password.

  • Store your password in an option file. For example, on Unix you can list your password in the [client] section of the .my.cnf file in your home directory: 

    [client]
password=your_pass

    If you store your password in .my.cnf, the file should not be accessible to anyone but yourself. To ensure this, set the file access mode to 400 or 600. For example: 

    shell> chmod 600 .my.cnf

    Section 4.2.3.2, “Using Option Files”, discusses option files in more detail.

  • Store your password in the MYSQL_PWD environment variable. This method of specifying your MySQL password must be considered extremely insecure and should not be used. Some versions of ps include an option to display the environment of running processes. If you set MYSQL_PWD, your password is exposed to any other user who runs ps. Even on systems without such a version of ps, it is unwise to assume that there are no other methods by which users can examine process environments. See Section 2.14, “Environment Variables”.

All in all, the safest methods are to have the client program prompt for the password or to specify the password in a properly protected option file.


User Comments

Posted by Ute Diestel on July 25 2007 10:50am[Delete] [Edit]

Sorry, I have to correct it: the "mysql -u francis -psecret ..." write in .bash_history (Linux). But the "SET PASSWORD FOR user@host=password("****");" and GRANT- / UPDATE-Statements on the grant tables you will find in your .mysql_history !

Posted by Olivier Debré on January 15 2008 4:28pm[Delete] [Edit]

'Use the -p or --password option with no password value specified'
Contrary to what's written in this paragraph, one can automate interactive behaviors like password entering using the Expect tool (http://expect.nist.gov/).

Posted by Tyler Larson on September 30 2008 5:04pm[Delete] [Edit]

Using the MYSQL_PWD environment variable is, in fact, secure on Linux systems (tested with kernel 2.6+). On these systems, the process environment is private to the user who runs that process. To test your system, do a long directory listing of a process environment pseudo-file, as follows:
ls -l /proc/1/environ
If it shows up as readable only by owner "-r--------" then only the process owner has permission to retrieve it. If the mechanism is secure on your system, then you can use it to securely pass authentication tokens in scripts, as follows:

#!/bin/bash
MYSQL_PWD="tinkerbell" mysqldump -ubackup --all-databases > dump.sql

The password is therefore saved in the script (so set the script's file permissions appropriately), but doesn't become visible to other users at run-time.

Note that you still should never export this value into your "standard" working environment, such as you could by editing your .bashrc or .bashprofile file. Doing so makes the value available to EVERY program you ever run, which could lead to inadvertently disclosing the value to a third party.

Add your own comment.