|
5.5.1. MySQL Usernames and Passwords
A MySQL account is defined in terms of a username and the client host or hosts from which the user can connect to the server. The account also has a password. There are several distinctions between the way usernames and passwords are used by MySQL and the way they are used by your operating system:
- Usernames, as used by MySQL for authentication purposes, have nothing to do with usernames (login names) as used by Windows or Unix. On Unix, most MySQL clients by default try to log in using the current Unix username as the MySQL username, but that is for convenience only. The default can be overridden easily, because client programs allow any username to be specified with a -u or --user option. Because this means that anyone can attempt to connect to the server using any username, you cannot make a database secure in any way unless all MySQL accounts have passwords. Anyone who specifies a username for an account that has no password is able to connect successfully to the server.
- MySQL usernames can be up to 16 characters long. This limit is hard-coded in the MySQL servers and clients, and trying to circumvent it by modifying the definitions of the tables in the mysql database does not work.
Note
You should never alter any of the tables in the mysql database in any manner whatsoever except by means of the procedure prescribed by MySQL AB that is described in Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”. Attempting to redefine MySQL's system tables in any other fashion results in undefined (and unsupported!) behavior.
Operating system usernames are completely unrelated to MySQL usernames and may even be of a different maximum length. For example, Unix usernames typically are limited to eight characters.
- MySQL passwords have nothing to do with passwords for logging in to your operating system. There is no necessary connection between the password you use to log in to a Windows or Unix machine and the password you use to access the MySQL server on that machine.
- MySQL encrypts passwords using its own algorithm. This encryption is different from that used during the Unix login process. MySQL password encryption is the same as that implemented by the PASSWORD() SQL function. Unix password encryption is the same as that implemented by the ENCRYPT() SQL function. See the descriptions of the PASSWORD() and ENCRYPT() functions in Section 11.10.2, “Encryption and Compression Functions”. From version 4.1 on, MySQL employs a stronger authentication method that has better password protection during the connection process than in earlier versions. It is secure even if TCP/IP packets are sniffed or the mysql database is captured. (In earlier versions, even though passwords are stored in encrypted form in the user table, knowledge of the encrypted password value could be used to connect to the MySQL server.)
When you install MySQL, the grant tables are populated with an initial set of accounts. These accounts have names and access privileges that are described in Section 2.4.16.3, “Securing the Initial MySQL Accounts”, which also discusses how to assign passwords to them. Thereafter, you normally set up, modify, and remove MySQL accounts using statements such as GRANT and REVOKE. See Section 12.5.1, “Account Management Statements”.
When you connect to a MySQL server with a command-line client, you should specify the username and password for the account that you want to use:
shell> mysql --user=monty --password=guess db_name
If you prefer short options, the command looks like this:
shell> mysql -u monty -pguess db_name
There must be no space between the -p option and the following password value. See Section 5.4.4, “Connecting to the MySQL Server”.
The preceding commands include the password value on the command line, which can be a security risk. See Section 5.5.6, “Keeping Your Password Secure”. To avoid this problem, specify the --password or -p option without any following password value:
shell> mysql --user=monty --password db_name
shell> mysql -u monty -p db_name
When the password option has no password value, the client program prints a prompt and waits for you to enter the password. (In these examples, db_name is not interpreted as a password because it is separated from the preceding password option by a space.)
On some systems, the library routine that MySQL uses to prompt for a password automatically limits the password to eight characters. That is a problem with the system library, not with MySQL. Internally, MySQL doesn't have any limit for the length of the password. To work around the problem, change your MySQL password to a value that is eight or fewer characters long, or put your password in an option file.
5.5.2. Adding New User Accounts to MySQL
You can create MySQL accounts in two ways:
- By using statements intended for creating accounts, such as CREATE USER or GRANT
- By manipulating the MySQL grant tables directly with statements such as INSERT, UPDATE, or DELETE
The preferred method is to use account-creation statements because they are more concise and less error-prone. CREATE USER and GRANT are described in Section 12.5.1.1, “CREATE USER Syntax”, and Section 12.5.1.3, “GRANT Syntax”.
Another option for creating accounts is to use one of several available third-party programs that offer capabilities for MySQL account administration. phpMyAdmin is one such program.
The following examples show how to use the mysql client program to set up new users. These examples assume that privileges are set up according to the defaults described in Section 2.4.16.3, “Securing the Initial MySQL Accounts”. This means that to make changes, you must connect to the MySQL server as the MySQL root user, and the root account must have the INSERT privilege for the mysql database and the RELOAD administrative privilege.
As noted in the examples where appropriate, some of the statements will fail if you have the server's SQL mode has been set to enable certain restrictions. In particular, strict mode (STRICT_TRANS_TABLES, STRICT_ALL_TABLES) and NO_AUTO_CREATE_USER will prevent the server from accepting some of the statements. Workarounds are indicated for these cases. For more information about SQL modes and their effect on grant table manipulation, see Section 5.1.6, “SQL Modes”, and Section 12.5.1.3, “GRANT Syntax”.
First, use the mysql program to connect to the server as the MySQL root user:
shell> mysql --user=root mysql
If you have assigned a password to the root account, you'll also need to supply a --password or -p option for this mysql command and also for those later in this section.
After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';
The accounts created by these GRANT statements have the following properties:
- Two of the accounts have a username of monty and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. One account ('monty'@'localhost') can be used only when connecting from the local host. The other ('monty'@'%') can be used to connect from any other host. Note that it is necessary to have both accounts for monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 5.4.5, “Access Control, Stage 1: Connection Verification”.)
- One account has a username of admin and no password. This account can be used only by connecting from the local host. It is granted the RELOAD and PROCESS administrative privileges. These privileges allow the admin user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-xxx commands, as well as mysqladmin processlist . No privileges are granted for accessing any databases. You could add such privileges later by issuing additional GRANT statements.
- One account has a username of dummy and no password. This account can be used only by connecting from the local host. No privileges are granted. The USAGE privilege in the GRANT statement enables you to create an account without giving it any privileges. It has the effect of setting all the global privileges to 'N'. It is assumed that you will grant specific privileges to the account later.
- The statements that create accounts with no password will fail if the NO_AUTO_CREATE_USER SQL mode is enabled. To deal with this, use an IDENTIFIED BY clause that specifies a non-empty password.
As an alternative to GRANT, you can create the same accounts directly by issuing INSERT statements and then telling the server to reload the grant tables using FLUSH PRIVILEGES:
shell> mysql --user=root mysql
mysql> INSERT INTO user
-> VALUES('localhost','monty',PASSWORD('some_pass'),
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user
-> VALUES('%','monty',PASSWORD('some_pass'),
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
-> '','','','',0,0,0,0);
mysql> INSERT INTO user SET Host='localhost',User='admin',
-> Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;
The reason for using FLUSH PRIVILEGES when you create accounts with INSERT is to tell the server to re-read the grant tables. Otherwise, the changes go unnoticed until you restart the server. With GRANT, FLUSH PRIVILEGES is unnecessary.
The reason for using the PASSWORD() function with INSERT is to encrypt the password. The GRANT statement encrypts the password for you, so PASSWORD() is unnecessary.
The 'Y' values enable privileges for the accounts. Depending on your MySQL version, you may have to use a different number of 'Y' values in the first two INSERT statements. For the admin account, you may also employ the more readable extended INSERT syntax using SET.
In the INSERT statement for the dummy account, only the Host, User, and Password columns in the user table row are assigned values. None of the privilege columns are set explicitly, so MySQL assigns them all the default value of 'N'. This is equivalent to what GRANT USAGE does.
If strict SQL mode is enabled, all columns that have no default value must have a value specified. In this case, INSERT statements must explicitly specify values for the ssl_cipher, x509_issuer, and x509_subject columns.
Note that to set up a superuser account, it is necessary only to create a user table entry with the privilege columns set to 'Y'. user table privileges are global, so no entries in any of the other grant tables are needed.
The next examples create three accounts and give them access to specific databases. Each of them has a username of custom and password of obscure.
To create the accounts with GRANT, use the following statements:
shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON bankaccount.*
-> TO 'custom'@'localhost'
-> IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON expenses.*
-> TO 'custom'@'whitehouse.gov'
-> IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON customer.*
-> TO 'custom'@'server.domain'
-> IDENTIFIED BY 'obscure';
The three accounts can be used as follows:
- The first account can access the bankaccount database, but only from the local host.
- The second account can access the expenses database, but only from the host whitehouse.gov.
- The third account can access the customer database, but only from the host server.domain.
To set up the custom accounts without GRANT, use INSERT statements as follows to modify the grant tables directly:
shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('whitehouse.gov','custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('server.domain','custom',PASSWORD('obscure'));
mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,
-> Update_priv,Delete_priv,Create_priv,Drop_priv)
-> VALUES('localhost','bankaccount','custom',
-> 'Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,
-> Update_priv,Delete_priv,Create_priv,Drop_priv)
-> VALUES('whitehouse.gov','expenses','custom',
-> 'Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,
-> Update_priv,Delete_priv,Create_priv,Drop_priv)
-> VALUES('server.domain','customer','custom',
-> 'Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;
The first three INSERT statements add user table entries that allow the user custom to connect from the various hosts with the given password, but grant no global privileges (all privileges are set to the default value of 'N'). The next three INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only when accessed from the proper hosts. As usual when you modify the grant tables directly, you must tell the server to reload them with FLUSH PRIVILEGES so that the privilege changes take effect.
If you want to give a specific user access from all machines in a given domain (for example, mydomain.com), you can issue a GRANT statement that uses the “%” wildcard character in the host part of the account name:
mysql> GRANT ...
-> ON *.*
-> TO 'myname'@'%.mydomain.com'
-> IDENTIFIED BY 'mypass';
To do the same thing by modifying the grant tables directly, do this:
mysql> INSERT INTO user (Host,User,Password,...)
-> VALUES('%.mydomain.com','myname',PASSWORD('mypass'),...);
mysql> FLUSH PRIVILEGES;
Previous / Next / Up / Table of Contents
User Comments
|
Posted by Jim Dennis on January 18 2005 9:05pm |
[Delete] [Edit] |
|
WARNING: ~/.mysql_history shows any PASSWORD('xxx') calls.
Just a note to admins and users out there; be sure to chmod
0600 your ~/.mysql_history as well as any ~/.my.cnf files.
Otherwise anyone with a local account might be able to read any
of your GRANT and SET PASSWORD statements.
Posted by Max on August 5 2006 6:12pm |
[Delete] [Edit] |
Indeed one solution to your password problem could lie in the value of 'host'.
Check it out by issuing "SELECT user()" using different interfaces (CLI, web server), and giving different values for 'host'.
On one server where I'm not admin but I have to work on, there happen strange things :
sometimes host='' defaults to 'localhost', sometimes to 'localhost.localdomain' (on the same mysql server !!) and the two corresponding accounts can have different passwords.
Or one may need a password, and the other one not.
Even worse, on this server:
<?php
mysql_connect('localhost');
print_r( mysql_fetch_assoc( mysql_query( "SELECT user()")))
?>
shows:
Array ( [user()] => nobody@localhost.localdomain )
i.e. it is IMPOSSIBLE to connect from PHP with host='localhost', while the CLI shows that there exist two different users,
nobody@localhost and nobody@localhost.localdomain
(and its the same for user='root'! and they have different privileges!)
In general, phpMyAdmin will do what you need for your PHP scripts, because it uses the same interface (as long as they run on the same web server), while the CLI does not (but allows for more direct and less 'default prone' access to the db server).
Also keep in mind the VARIOUS security risks using phpMyAdmin (among others, both Apache HTTP_AUTH passwords and MySQL passwords transit in clear text in the POST data, and might be available to 'everybody' as global variables (eg. if register_globals is on) ...) !
|
Posted by jon doe on October 27 2006 7:42am |
[Delete] [Edit] |
Say you wanted to add a couple of users,
but wanted to prevent access to the entire db to one of them -
mysql> grant all on DATABASENAME to USERNAME1 identified by 'PASSWORD';
This allows them all privs, but only to that database.
To restrict privs:
mysql>grant select (FIELD1, FIELD2) on DATABASENAME to USERNAME2 identified by 'PASSWORD';
5.5.3. Removing User Accounts from MySQL
To remove an account, use the DROP USER statement, which is described in Section 12.5.1.2, “DROP USER Syntax”.
12.5.1.2. DROP USER Syntax
DROP USER user [, user] ...
The DROP USER statement removes one or more MySQL accounts. To use it, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database. Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. If you specify only the username part of the account name, a hostname part of '%' is used. For additional information about specifying account names, see Section 12.5.1.3, “GRANT Syntax”.
DROP USER as present in MySQL 5.0.0 removes only accounts that have no privileges. In MySQL 5.0.2, it was modified to remove account privileges as well. This means that the procedure for removing an account depends on your version of MySQL.
As of MySQL 5.0.2, you can remove an account and its privileges as follows:
DROP USER user;
The statement removes privilege rows for the account from all grant tables.
Before MySQL 5.0.2, DROP USER serves only to remove account rows from the user table for accounts that have no privileges. To remove a MySQL account completely (including all of its privileges), you should use the following procedure, performing these steps in the order shown:
- Use SHOW GRANTS to determine what privileges the account has. See Section 12.5.4.12, “SHOW GRANTS Syntax”.
- Use REVOKE to revoke the privileges displayed by SHOW GRANTS. This removes rows for the account from all the grant tables except the user table, and revokes any global privileges listed in the user table. See Section 12.5.1.3, “GRANT Syntax”.
- Delete the account by using DROP USER to remove the user table row.
Important
DROP USER does not automatically close any open user sessions. Rather, in the event that a user with an open session is dropped, the statement does not take effect until that user's session is closed. Once the session is closed, the user is dropped, and that user's next attempt to log in will fail. This is by design.
DROP USER does not automatically delete or invalidate any database objects that the user created. This applies to tables, views, stored routines, and triggers.
5.5.4. Limiting Account Resources
One means of limiting use of MySQL server resources is to set the max_user_connections system variable to a non-zero value. However, this method is strictly global, and does not allow for management of individual accounts. In addition, it limits only the number of simultaneous connections made using a single account, and not what a client can do once connected. Both types of control are of interest to many MySQL administrators, particularly those working for Internet Service Providers.
In MySQL 5.0, you can limit the following server resources for individual accounts:
- The number of queries that an account can issue per hour
- The number of updates that an account can issue per hour
- The number of times an account can connect to the server per hour
Any statement that a client can issue counts against the query limit. Only statements that modify databases or tables count against the update limit.
From MySQL 5.0.3 on, it is also possible to limit the number of simultaneous connections to the server on a per-account basis.
Before MySQL 5.0.3, an “account” in this context is assessed against the actual host from which a user connects. Suppose that there is a row in the user table that has User and Host values of usera and %.example.com, to allow usera to connect from any host in the example.com domain. If usera connects simultaneously from host1.example.com and host2.example.com, the server applies the account resource limits separately to each connection. If usera connects again from host1.example.com, the server applies the limits for that connection together with the existing connection from that host.
As of MySQL 5.0.3, an “account” is assessed as a single row in the user table. That is, connections are assessed against the Host value in the user table row that applies to the connection. In this case, the server applies resource limits collectively to all connections by usera from any host in the example.com domain. The pre-5.0.3 method of accounting may be selected by starting the server with the --old-style-user-limits option.
As a prerequisite for using this feature, the user table in the mysql database must contain the resource-related columns. Resource limits are stored in the max_questions, max_updates, max_connections, and max_user_connections columns. If your user table doesn't have these columns, it must be upgraded; see Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”.
To set resource limits with a GRANT statement, use a WITH clause that names each resource to be limited and a per-hour count indicating the limit value. For example, to create a new account that can access the customer database, but only in a limited fashion, issue this statement:
mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'
-> IDENTIFIED BY 'frank'
-> WITH MAX_QUERIES_PER_HOUR 20
-> MAX_UPDATES_PER_HOUR 10
-> MAX_CONNECTIONS_PER_HOUR 5
-> MAX_USER_CONNECTIONS 2;
The limit types need not all be named in the WITH clause, but those named can be present in any order. The value for each per-hour limit should be an integer representing a count per hour. If the GRANT statement has no WITH clause, the limits are each set to the default value of zero (that is, no limit). For MAX_USER_CONNECTIONS, the limit is an integer indicating the maximum number of simultaneous connections the account can make at any one time. If the limit is set to the default value of zero, the max_user_connections system variable determines the number of simultaneous connections for the account.
To set or change limits for an existing account, use a GRANT USAGE statement at the global level (ON *.*). The following statement changes the query limit for francis to 100:
mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'
-> WITH MAX_QUERIES_PER_HOUR 100;
This statement leaves the account's existing privileges unchanged and modifies only the limit values specified.
To remove an existing limit, set its value to zero. For example, to remove the limit on how many times per hour francis can connect, use this statement:
mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'
-> WITH MAX_CONNECTIONS_PER_HOUR 0;
Resource-use counting takes place when any account has a non-zero limit placed on its use of any of the resources.
As the server runs, it counts the number of times each account uses resources. If an account reaches its limit on number of connections within the last hour, further connections for the account are rejected until that hour is up. Similarly, if the account reaches its limit on the number of queries or updates, further queries or updates are rejected until the hour is up. In all such cases, an appropriate error message is issued.
Resource counting is done per account, not per client. For example, if your account has a query limit of 50, you cannot increase your limit to 100 by making two simultaneous client connections to the server. Queries issued on both connections are counted together.
Queries for which results are served from the query cache do not count against the MAX_QUERIES_PER_HOUR limit.
The current per-hour resource-use counts can be reset globally for all accounts, or individually for a given account:
- To reset the current counts to zero for all accounts, issue a FLUSH USER_RESOURCES statement. The counts also can be reset by reloading the grant tables (for example, with a FLUSH PRIVILEGES statement or a mysqladmin reload command).
- The counts for an individual account can be set to zero by re-granting it any of its limits. To do this, use GRANT USAGE as described earlier and specify a limit value equal to the value that the account currently has.
Counter resets do not affect the MAX_USER_CONNECTIONS limit.
All counts begin at zero when the server starts; counts are not carried over through a restart.
5.5.5. Assigning Account Passwords
Passwords may be assigned from the command line by using the mysqladmin command:
shell> mysqladmin -u user_name -h host_name password "newpwd"
The account for which this command resets the password is the one with a user table row that matches user_name in the User column and the client host from which you connect in the Host column.
Another way to assign a password to an account is to issue a SET PASSWORD statement:
mysql> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');
Only users such as root that have update access to the mysql database can change the password for other users. If you are not connected as an anonymous user, you can change your own password by omitting the FOR clause:
mysql> SET PASSWORD = PASSWORD('biscuit');
You can also use a GRANT USAGE statement at the global level (ON *.*) to assign a password to an account without affecting the account's current privileges:
mysql> GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';
Although it is generally preferable to assign passwords using one of the preceding methods, you can also do so by modifying the user table directly:
- To establish a password when creating a new account, provide a value for the Password column:
· shell> mysql -u root mysql
· mysql> INSERT INTO user (Host,User,Password)
· -> VALUES('%','jeffrey',PASSWORD('biscuit'));
· mysql> FLUSH PRIVILEGES;
- To change the password for an existing account, use UPDATE to set the Password column value:
· shell> mysql -u root mysql
· mysql> UPDATE user SET Password = PASSWORD('bagel')
· -> WHERE Host = '%' AND User = 'francis';
· mysql> FLUSH PRIVILEGES;
When you assign an account a non-empty password using SET PASSWORD, INSERT, or UPDATE, you must use the PASSWORD() function to encrypt it. PASSWORD() is necessary because the user table stores passwords in encrypted form, not as plaintext. If you forget that fact, you are likely to set passwords like this:
shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;
The result is that the literal value 'biscuit' is stored as the password in the user table, not the encrypted value. When jeffrey attempts to connect to the server using this password, the value is encrypted and compared to the value stored in the user table. However, the stored value is the literal string 'biscuit', so the comparison fails and the server rejects the connection:
shell> mysql -u jeffrey -pbiscuit test
Access denied
If you assign passwords using the GRANT ... IDENTIFIED BY statement or the mysqladmin password command, they both take care of encrypting the password for you. In these cases, using PASSWORD() function is unnecessary. .5.6. Keeping Your Password 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.)
- 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.2.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.4.20, “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.
NotePASSWORD() encryption is different from Unix password encryption. See Section 5.5.1, “MySQL Usernames and Passwords”.
| 
