< Day Day Up > |
4.5 MySQL User Account ManagementThis section describes how to set up accounts for clients of your MySQL server. It discusses the following topics:
4.5.1 MySQL Usernames and PasswordsA 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:
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.5, "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 the GRANT and REVOKE 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 4.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 4.5.6, "Keeping Your Password Secure." To avoid this, 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 Then the client program will print a prompt and wait 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 call 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. 4.5.2 Adding New User Accounts to MySQLYou can create MySQL accounts in two ways:
The preferred method is to use GRANT statements, because they are more concise and less error-prone . GRANT is available as of MySQL 3.22.11; its syntax is described in the MySQL Language Reference . 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.5, "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. 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:
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: 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'); 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 will 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. (Versions prior to 3.22.11 have fewer privilege columns , and versions from 4.0.2 on have more.) For the admin account, the more readable extended INSERT syntax using SET that is available starting with MySQL 3.22.11 is used. In the INSERT statement for the dummy account, only the Host , User , and Password columns in the user table record 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. 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 :
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 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; 4.5.3 Removing User Accounts from MySQLTo remove an account, use the DROP USER statement, which was added in MySQL 4.1.1. For older versions of MySQL, use DELETE instead. To remove a MySQL user account, you should use the following procedure, performing the steps in the order shown:
The DROP USER statement was added in MySQL 4.1.1. Before 4.1.1, you should first revoke the account privileges as just described. Then delete the user table record and flush the grant tables like this: mysql> DELETE FROM mysql.user -> WHERE User=' user_name ' and Host=' host_name '; mysql> FLUSH PRIVILEGES; 4.5.4 Limiting Account ResourcesBefore MySQL 4.0.2, the only available method for limiting use of MySQL server resources is to set the max_user_connections system variable to a non-zero value. But that method is strictly global. It does not allow for management of individual accounts. Also, it limits only the number of simultaneous connections made using a single account, not what a client can do once connected. Both types of control are of interest to many MySQL administrators, particularly those for Internet Service Providers. Starting from MySQL 4.0.2, you can limit the following server resources for individual accounts:
Any statement that a client can issue counts against the query limit. Only statements that modify databases or tables count against the update limit. An account in this context is a single record in the user table. Each account is uniquely identified by its User and Host column values. 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 , and max_connections columns. If your user table doesn't have these columns, it must be upgraded; see Section 2.5.8, "Upgrading the Grant Tables." 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; 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 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). 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. The current resource-use counts can be reset globally for all accounts, or individually for a given count:
4.5.5 Assigning Account PasswordsPasswords 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 record 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 with 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:
When you assign an account a password using SET PASSWORD , INSERT , or UPDATE , you must use the PASSWORD() function to encrypt it. (The only exception is that you need not use PASSWORD() if the password is empty.) 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 set passwords using the GRANT ... IDENTIFIED BY statement or the mysqladmin password command, they both take care of encrypting the password for you. The PASSWORD() function is unnecessary. Note: PASSWORD() encryption is different from Unix password encryption. See Section 4.5.1, "MySQL Usernames and Passwords." 4.5.6 Keeping Your Password SecureOn an administrative level, you should never grant access to the mysql.user table to any non-administrative accounts. Passwords in the user table are stored in encrypted form, but in versions of MySQL earlier than 4.1, knowing the encrypted password for an account makes it possible to connect to the server using that account. 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:
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. 4.5.7 Using Secure ConnectionsBeginning with version 4.0.0, MySQL has support for secure (encrypted) connections between MySQL clients and the server using the Secure Sockets Layer (SSL) protocol. This section discusses how to use SSL connections. It also describes a way to set up SSH on Windows. The standard configuration of MySQL is intended to be as fast as possible, so encrypted connections are not used by default. Doing so would make the client/server protocol much slower. Encrypting data is a CPU- intensive operation that requires the computer to do additional work and can delay other MySQL tasks . For applications that require the security provided by encrypted connections, the extra computation is warranted. MySQL allows encryption to be enabled on a per-connection basis. You can choose a normal unencrypted connection or a secure encrypted SSL connection according to the requirements of individual applications. 4.5.7.1 Basic SSL ConceptsTo understand how MySQL uses SSL, it's necessary to explain some basic SSL and X509 concepts. People who are already familiar with them can skip this part. By default, MySQL uses unencrypted connections between the client and the server. This means that someone with access to the network could watch all your traffic and look at the data being sent or received. They could even change the data while it is in transit between client and server. To improve security a little, you can compress client/server traffic by using the --compress option when invoking client programs. However, this will not foil a determined attacker. When you need to move information over a network in a secure fashion, an unencrypted connection is unacceptable. Encryption is the way to make any kind of data unreadable. In fact, today's practice requires many additional security elements from encryption algorithms. They should resist many kinds of known attacks such as changing the order of encrypted messages or replaying data twice. SSL is a protocol that uses different encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect any data change, loss, or replay. SSL also incorporates algorithms that provide identity verification using the X509 standard. X509 makes it possible to identify someone on the Internet. It is most commonly used in e-commerce applications. In basic terms, there should be some company called a "Certificate Authority" (or CA) that assigns electronic certificates to anyone who needs them. Certificates rely on asymmetric encryption algorithms that have two encryption keys (a public key and a secret key). A certificate owner can show the certificate to another party as proof of identity. A certificate consists of its owner's public key. Any data encrypted with this public key can be decrypted only using the corresponding secret key, which is held by the owner of the certificate. If you need more information about SSL, X509, or encryption, use your favorite Internet search engine to search for keywords in which you are interested. 4.5.7.2 RequirementsTo use SSL connections between the MySQL server and client programs, your system must be able to support OpenSSL and your version of MySQL must be 4.0.0 or newer . To get secure connections to work with MySQL, you must do the following:
4.5.7.3 Setting Up SSL Certificates for MySQLHere is an example for setting up SSL certificates for MySQL: DIR=`pwd`/openssl PRIV=$DIR/private mkdir $DIR $PRIV $DIR/newcerts cp /usr/share/ssl/openssl.cnf $DIR replace ./demoCA $DIR -- $DIR/openssl.cnf # Create necessary files: $database, $serial and $new_certs_dir # directory (optional) touch $DIR/index.txt echo "01" > $DIR/serial # # Generation of Certificate Authority(CA) # openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem \ -config $DIR/openssl.cnf # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Generating a 1024 bit RSA private key # ................++++++ # .........++++++ # writing new private key to '/home/monty/openssl/private/cakey.pem' # Enter PEM pass phrase: # Verifying password - Enter PEM pass phrase: # ----- # You are about to be asked to enter information that will be # incorporated into your certificate request. # What you are about to enter is what is called a Distinguished Name # or a DN. # There are quite a few fields but you can leave some blank # For some fields there will be a default value, # If you enter '.', the field will be left blank. # ----- # Country Name (2 letter code) [AU]:FI # State or Province Name (full name) [Some-State]:. # Locality Name (eg, city) []: # Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB # Organizational Unit Name (eg, section) []: # Common Name (eg, YOUR name) []:MySQL admin # Email Address []: # # Create server request and key # openssl req -new -keyout $DIR/server-key.pem -out \ $DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Generating a 1024 bit RSA private key # ..++++++ # .......... ++++++ # writing new private key to '/home/monty/openssl/server-key.pem' # Enter PEM pass phrase: # Verifying password - Enter PEM pass phrase: # ----- # You are about to be asked to enter information that will be # incorporated into your certificate request. # What you are about to enter is what is called a Distinguished Name # or a DN. # There are quite a few fields but you can leave some blank # For some fields there will be a default value, # If you enter '.', the field will be left blank. # ----- # Country Name (2 letter code) [AU]:FI # State or Province Name (full name) [Some-State]:. # Locality Name (eg, city) []: # Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB # Organizational Unit Name (eg, section) []: # Common Name (eg, YOUR name) []:MySQL server # Email Address []: # # Please enter the following 'extra' attributes # to be sent with your certificate request # A challenge password []: # An optional company name []: # # Remove the passphrase from the key (optional) # openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem # # Sign server cert # openssl ca -policy policy_anything -out $DIR/server-cert.pem \ -config $DIR/openssl.cnf -infiles $DIR/server-req.pem # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Enter PEM pass phrase: # Check that the request matches the signature # Signature ok # The Subjects Distinguished Name is as follows # countryName :PRINTABLE:'FI' # organizationName :PRINTABLE:'MySQL AB' # commonName :PRINTABLE:'MySQL admin' # Certificate is to be certified until Sep 13 14:22:46 2003 GMT # (365 days) # Sign the certificate? [y/n]:y # # # 1 out of 1 certificate requests certified, commit? [y/n]y # Write out database with 1 new entries # Data Base Updated # # Create client request and key # openssl req -new -keyout $DIR/client-key.pem -out \ $DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Generating a 1024 bit RSA private key # .....................................++++++ # .............................................++++++ # writing new private key to '/home/monty/openssl/client-key.pem' # Enter PEM pass phrase: # Verifying password - Enter PEM pass phrase: # ----- # You are about to be asked to enter information that will be # incorporated into your certificate request. # What you are about to enter is what is called a Distinguished Name # or a DN. # There are quite a few fields but you can leave some blank # For some fields there will be a default value, # If you enter '.', the field will be left blank. # ----- # Country Name (2 letter code) [AU]:FI # State or Province Name (full name) [Some-State]:. # Locality Name (eg, city) []: # Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB # Organizational Unit Name (eg, section) []: # Common Name (eg, YOUR name) []:MySQL user # Email Address []: # # Please enter the following 'extra' attributes # to be sent with your certificate request # A challenge password []: # An optional company name []: # # Remove a passphrase from the key (optional) # openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem # # Sign client cert # openssl ca -policy policy_anything -out $DIR/client-cert.pem \ -config $DIR/openssl.cnf -infiles $DIR/client-req.pem # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Enter PEM pass phrase: # Check that the request matches the signature # Signature ok # The Subjects Distinguished Name is as follows # countryName :PRINTABLE:'FI' # organizationName :PRINTABLE:'MySQL AB' # commonName :PRINTABLE:'MySQL user' # Certificate is to be certified until Sep 13 16:45:17 2003 GMT # (365 days) # Sign the certificate? [y/n]:y # # # 1 out of 1 certificate requests certified, commit? [y/n]y # Write out database with 1 new entries # Data Base Updated # # Create a my.cnf file that you can use to test the certificates # cnf="" cnf="$cnf [client]" cnf="$cnf ssl-ca=$DIR/cacert.pem" cnf="$cnf ssl-cert=$DIR/client-cert.pem" cnf="$cnf ssl-key=$DIR/client-key.pem" cnf="$cnf [mysqld]" cnf="$cnf ssl-ca=$DIR/cacert.pem" cnf="$cnf ssl-cert=$DIR/server-cert.pem" cnf="$cnf ssl-key=$DIR/server-key.pem" echo $cnf replace " " ' ' > $DIR/my.cnf To test SSL connections, start the server as follows, where $DIR is the pathname to the directory where the sample my.cnf option file is located: shell> mysqld --defaults-file=$DIR/my.cnf & Then invoke a client program using the same option file: shell> mysql --defaults-file=$DIR/my.cnf If you have a MySQL source distribution, you can also test your setup by modifying the preceding my.cnf file to refer to the demonstration certificate and key files in the SSL directory of the distribution. 4.5.7.4 SSL GRANT OptionsMySQL can check X509 certificate attributes in addition to the usual authentication that is based on the username and password. To specify SSL-related options for a MySQL account, use the REQUIRE clause of the GRANT statement, as described in the MySQL Language Reference . 4.5.7.5 SSL Command-Line OptionsThe following list describes options that are used for specifying the use of SSL, certificate files, and key files. These options are available beginning with MySQL 4.0. They may be given on the command line or in an option file.
4.5.7.6 Connecting to MySQL Remotely from Windows with SSHHere is a note about how to connect to get a secure connection to remote MySQL server with SSH (by David Carlson dcarlson@mplcomm.com):
You should now have an ODBC connection to MySQL, encrypted using SSH. |
< Day Day Up > |