Managing MySQL User Accounts

The MySQL administrator should know how to set up MySQL user accounts by specifying which users can connect to the server, where they can connect from, and what they can do while connected. This information is stored in the grant tables in the mysql database and is managed primarily by means of two statements:

• GRANT creates MySQL accounts and specifies their privileges.

• REVOKE removes privileges from existing MySQL accounts.

These statements were introduced in MySQL 3.22.11 to make it easier to manage user accounts. Prior to 3.22.11, it was necessary to manipulate the contents of the grant tables directly by issuing SQL statements such as INSERT and UPDATE. GRANT and REVOKE act as a front end to the grant tables. They are more convenient to work with conceptually because you describe the permissions you want to allow, and the server maps your requests onto the proper grant table modifications automatically. Nevertheless, although it's much easier to use GRANT and REVOKE than to modify the grant tables directly, I advise that you supplement the material in this chapter by reading Chapter 12. That chapter discusses the grant tables in more detail, to help you understand how they work beyond the level of the GRANT and REVOKE statements. It also contains a section on setting up accounts without using GRANT, which is how you'll need to set up privileges if your server is older than 3.22.11.

You may also want to consider using the mysqlaccess and mysql_setpermission scripts, which are part of the MySQL distribution. These are Perl scripts that provide an alternative to the GRANT statement for setting up user accounts. mysql_setpermission requires that you have DBI support installed.

The GRANT and REVOKE statements affect four tables:

Grant Table	Contents
user	Users who can connect to the server and their global privileges
db	Database-level privileges
tables_priv	Table-level privileges
columns_priv	Column-level privileges

There is a fifth grant table named host, but it is not affected by GRANT or REVOKE and is not discussed here. For information on how it works, see Chapter 12.

When you issue a GRANT statement for an account, an entry is created for that account in the user table. If the statement specifies any global privileges (administrative privileges or privileges that apply to all databases), those are recorded in the user table, too. If you specify privileges that are specific to a given database, table, or table column, they are recorded in the db, tables_priv, and columns_priv tables.

The rest of this section describes how to set up MySQL user accounts and grant privileges, how to revoke privileges and remove users from the grant tables entirely, and how to change passwords or reset lost passwords.

Creating New Users and Granting Privileges

The syntax for the GRANT statement looks like this:

 GRANT privileges (columns)      ON what     TO account IDENTIFIED BY 'password'     REQUIRE encryption requirements     WITH grant or resource management options; 

Several of these clauses are optional and need not be specified at all. In general, you'll most commonly fill in the following parts:

• privileges

  The privileges to assign to the account. For example, the SELECT privilege allows a user to issue SELECT statements and the SHUTDOWN privilege allows the user to shut down the server.

• columns

  The columns the privileges apply to. This is optional, and you use it only to set up column-specific privileges. If you want to list more than one column, separate their names by commas.

• what

  The level at which the privileges apply. The most powerful level is the global level for which any given privilege applies to all databases and all tables. Global privileges can be thought of as superuser privileges. Privileges also can be made database-specific, table-specific, or (if you specify a columns clause) column-specific.

• account

  The account that is being granted the privileges. The account value consists of a username and a hostname in 'user_name'@'host_name' format because in MySQL, you specify not only who can connect but from where. This allows you to set up separate accounts for two users who have the same name but who connect from different locations. MySQL lets you distinguish between them and assign privileges to each independently of the other. The user_name and host_name values are recorded in the User and Host columns of the user table entry for the account and in any other grant table records that the GRANT statement creates.

  Your username in MySQL is just a name that you use to identify yourself when you connect to the server. The name has no necessary connection to your UNIX login name or Windows name. By default, client programs will use your login name as your MySQL username if you don't specify a name explicitly, but that's just a convention. There is also nothing special about the name root that is used for the MySQL superuser that can do anything. It's just a convention. You could just as well change this name to nobody in the grant tables and then connect as nobody to perform operations that require superuser privileges.

• password

  The password to assign to the account. This is optional. If you specify no IDENTIFIED BY clause for a new user, that user is assigned no password (which is insecure). If you use GRANT to modify the privileges of an existing account, the account's password is either replaced or left unchanged, depending on whether you include or omit an IDENTIFIED BY clause. When you do use IDENTIFIED BY, the password value should be the literal text of the password; GRANT will encode the password for you. Don't use the PASSWORD() function as you do with the SET PASSWORD statement.

The REQUIRE and WITH clauses are optional. REQUIRE is available as of MySQL 4.0.0 and is used for setting up accounts that must connect over secure connections using SSL. WITH is used to grant the GRANT OPTION privilege that allows the account to give its own privileges to other users. As of MySQL 4.0.2, WITH is also used to specify resource management options that allow you to place limits on how many connections or queries an account can use per hour. These options help you prevent the account from hogging the server.

Usernames, passwords, and database and table names are case sensitive in grant table entries. Hostnames and column names are not.

When you want to set up an account, it's generally possible to figure out the kind of GRANT statement to issue by asking some simple questions:

• Who can connect, and from where? What is the user's name, and where will that user connect from?

• What type of access should the account be given? That is, what level of privileges should the user have, and what should they apply to?

• Are secure connections required?

• Should the user be allowed to administer privileges?

• Should the user's resource consumption be limited?

The following discussion asks these questions and provides some examples showing how to use the GRANT statement to set up MySQL user accounts.

Who Can Connect, and from Where?

The account part of the GRANT statement specifies the user's name and where that user can connect from. You can allow a user to connect from as specific or broad a set of hosts as you like. At the one extreme, you can limit access to a single host if you know users will be connecting only from that host. For example, to grant access to all the tables in the sampdb database for host-specific accounts, you can use statements like these:

 GRANT ALL ON sampdb.* TO 'boris'@'localhost' IDENTIFIED BY 'ruby';  GRANT ALL ON sampdb.* TO 'fred'@'ares.mars.net' IDENTIFIED BY 'quartz'; 

If the username or hostname parts of the account value do not contain any special characters such as '-' or '%', you may not need to quote them (for example, boris@localhost is legal without quotes). However, it should always be safe to use quotes, and the examples in this book do so as a rule. But note that the username and hostname are quoted separately; use 'boris'@'localhost', not 'boris@localhost'.

Allowing a user to connect from a single host is the strictest form of access you can allow. At the other extreme, you may have a user who travels a lot and needs to be able to connect from hosts all over the world. If the user's name is max, you can allow him to connect from anywhere, as follows:

 GRANT ALL ON sampdb.* TO 'max'@'%' IDENTIFIED BY 'diamond';  

The '%' character functions as a wildcard with the same meaning as in a LIKE pattern match. Thus, as a hostname specifier, % means "any host." This is the easiest way to set up a user, but it's also the least secure. (Using it also may result in occasional head-scratching on your part, for reasons described in the "A Privilege Puzzle" in Chapter 12.)

To take a middle ground, you can allow a user to connect from a limited set of hosts. For example, to allow mary to connect from any host in the snake.net domain, use a host specifier of %.snake.net:

 GRANT ALL ON sampdb.* TO 'mary'@'%.snake.net' IDENTIFIED BY 'topaz';  

The other LIKE wildcard character ('_') can be used in host values to match any single character.

The host part of the account value can be given using an IP address rather than a hostname, if you want. You can specify a literal IP address or an address that contains pattern characters. Also, as of MySQL 3.23, you can specify IP numbers with a netmask indicating which bits to use for the network number:

 GRANT ALL ON sampdb.* TO 'joe'@'192.168.128.3' IDENTIFIED BY 'water';  GRANT ALL ON sampdb.* TO 'ardis'@'192.168.128.%' IDENTIFIED BY 'snow'; GRANT ALL ON sampdb.* TO 'rex'@'192.168.128.0/255.255.128.0'     IDENTIFIED BY 'ice'; 

The first of these statements indicates a specific host from which the user can connect. The second specifies an IP pattern for the 192.168.128 Class C subnet. In the third statement, 192.168.128.0/255.255.128.0 specifies a netmask that has the first 17 bits turned on. It matches any host with 192.168.128 in the first 17 bits of its IP address.

Using a host value of localhost in a GRANT statement allows the user to connect to the server from the local host by specifying a host value of localhost or 127.0.0.1 (the local host's loopback IP address). A localhost account also matches on Windows when the user connects by specifying a hostname of '.' (period) if the server supports named pipes. On UNIX, connections to localhost are made via the UNIX socket file. On Windows, connections to '.' are made via a named pipe if named pipes are available. All other connections are made via TCP/IP, including connections to 127.0.0.1, the loopback address.

If you give no hostname part at all in an account specifier, it's the same as using a host part of %. Thus, 'max' and 'max'@'%' are equivalent account values in GRANT statements. This means that if you intend to specify an account of 'boris'@'localhost' but mistakenly write 'boris@localhost' instead, MySQL will accept it as legal. What happens is that MySQL interprets 'boris@localhost' as containing only a user part and adds the default host part of % to it, resulting in an effective account name of 'boris@localhost'@'%'. To avoid this, be sure always to quote the user and host parts of account specifiers separately.

How to Specify Your Local Hostname in Grant Table Entries

It's common to have problems connecting from the server host if you use the server's hostname rather than localhost. This can occur due to a mismatch between the way the name is specified in the grant tables and the way your name resolver reports the name to programs. Suppose the server host's fully qualified name is cobra.snake.net. If the resolver reports an unqualified name, such as cobra, but the grant tables contain entries with the fully qualified name (or vice versa), this mismatch will occur.

To determine if this is happening on your system, try connecting to the local server using a -h option that specifies the name of your host:

 % mysql -h cobra.snake.net  

Then look in the server's general log file. How does it write the hostname when it reports the connection attempt? Is the name in unqualified or fully qualified form? Whichever form it's in, that tells you how you'll need to specify the hostname part of account name specifiers when you issue GRANT statements.

What Type of Access Should the User Be Given?

You can grant several types of privileges. These are summarized in Table 11.1 and described in more detail in Chapter 12. That chapter discusses the privileges in terms of both their purpose and their relationship to the underlying grant tables.

Table 11.1. MySQL Privilege Types

Privilege Specifier	Operation Allowed by Privilege
CREATE TEMPORARY TABLES	Create temporary tables
EXECUTE	Execute stored procedures (reserved for future use)
FILE	Read and write files on the server host
GRANT OPTION	Grant the account's privileges to other accounts
LOCK TABLES	Explicitly lock tables with LOCK TABLES statements
PROCESS	View information about the threads executing within the server
RELOAD	Reload the grant tables or flush the logs or caches
REPLICATION CLIENT	Ask about master and slave server locations
REPLICATION SLAVE	Act as a replication slave server
SHOW DATABASES	Issue SHOW DATABASES statements
SHUTDOWN	Shut down the server
SUPER	Kill threads and perform other supervisory operations
ALTER	Alter tables and indexes
CREATE	Create databases and tables
DELETE	Delete existing rows from tables
DROP	Drop (remove) databases and tables
INDEX	Create or drop indexes
INSERT	Insert new rows into tables
REFERENCES	Unused (reserved for future use)
SELECT	Retrieve existing rows from tables
UPDATE	Modify existing table rows
ALL	All operations (except GRANT); ALL PRIVILEGES is a synonym
USAGE	A special "no privileges" privilege

The privilege specifiers in the first group shown in the table are administrative privileges. Normally, these are granted relatively sparingly because they allow users to affect the operation of the server. (The SHUTDOWN privilege is not one you hand out on an everyday basis, for example.) The privileges in the second group apply to databases, tables, and columns, and control access to data managed by the server. The specifiers in the third group are special. ALL means "all privileges" (except that it does not include the GRANT OPTION privilege). USAGE means "no privileges" that is, "create the account, but don't grant it any privileges." USAGE also can be used to modify non-privilege-related aspects of an account without changing its current privileges.

Some of the privileges are new in MySQL 4.0.2, so you can't grant them if you have an earlier version. These are CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, and SUPER. The introduction of these privileges changes how some operations are controlled. For example, the ability to kill any thread running within the server is granted through the PROCESS privilege prior to MySQL 4.0.2, and through the SUPER privilege from 4.0.2 on. Normally, this is not a problem, as long as you upgrade your grant tables when updating from a pre-4.0.2 server to 4.0.2 or later; MySQL distributions contain a mysql_fix_privilege_tables script that installs columns for the newer privileges, and each account's SUPER privilege is initialized from its PROCESS privilege. Thus, any user who can kill threads prior to 4.0.2 can continue to do so from 4.0.2 on. (The specific rules that are used to migrate privileges when you upgrade to 4.0.2 or later are described in Chapter 12 in the "Dealing with Changes to Grant Table Structure" sidebar.)

Be aware that you cannot grant the CREATE TEMPORARY TABLES or LOCK TABLES privileges on a database-specific basis in MySQL 4.0.2 or 4.0.3, so you should avoid using those versions if possible. This problem was corrected in MySQL 4.0.4.

You can grant privileges at different levels all the way from global down to column-specific. This is controlled by the ON clause specifier, as shown in the following table:

Privilege Specifier	Level At Which Privileges Apply
ON *.*	Global privileges; all databases, all tables
ON *	Global privileges if no default database has been selected, database-level privileges for the current database otherwise
ON db_name.*	Database-level privileges; all tables in the named database
ON db_name.tbl_name	Table-level privileges; all columns in the named table
ON tbl_name	Table-level privileges; all columns in the named table in the default database

Global privileges are the most powerful because they apply to any database. To make ethel a superuser who can do anything, including being able to grant privileges to other users, issue this statement:

 GRANT ALL ON *.* TO 'ethel'@'localhost' IDENTIFIED BY 'coffee'      WITH GRANT OPTION; 

The ON *.* clause means "all databases, all tables." As a safety precaution, the statement specifies that ethel can connect only from the local host. Limiting the hosts from which a superuser can connect is usually wise because it limits the hosts from which password-cracking attempts can be mounted.

Some privileges are administrative in nature and can be granted using only the ON *.* global-privilege specifier. These include FILE, PROCESS, RELOAD, SHUTDOWN, and the other privileges in the first section of Table 11.1. For example, the RELOAD privilege allows use of FLUSH, so the following statement sets up a user named flush that can do nothing but issue FLUSH statements:

 GRANT RELOAD ON *.* TO 'flush'@'localhost' IDENTIFIED BY 'flushpass';  

This type of MySQL account can be useful for writing administrative scripts in which you need to perform operations such as flushing the logs during log file rotation. (See the "Log File Expiration" section later in this chapter.)

Database-level privileges apply to all tables in a particular database. These are granted by using an ON db_name.* clause:

 GRANT ALL ON sampdb.* TO 'bill'@'racer.snake.net' IDENTIFIED BY 'rock';  GRANT SELECT ON menagerie.* TO 'reader'@'%' IDENTIFIED BY 'dirt'; 

The first of these statements grants bill full privileges for any table in the sampdb database. The second creates a restricted-access user named reader who can access any table in the menagerie database, but only with SELECT statements. That is, reader is a "read-only" user.

You can list multiple privileges to be granted at the same time by naming them separated by commas. For example, to give a user the ability to read and modify the contents of existing tables in the sampdb database but not to create new tables or drop tables, you would not grant the ALL privilege for the database. Instead, you grant several more-specific privileges:

 GRANT SELECT,INSERT,DELETE,UPDATE ON sampdb.* TO 'jennie'@'%'      IDENTIFIED BY 'boron'; 

For more fine-grained access control below the database level, you can grant privileges on individual tables, or even on individual columns in tables. Column-specific privileges are useful when there are parts of a table you want to hide from a user or when you want a user to be able to modify only particular columns. Suppose someone volunteers to help you at the Historical League office with the duties that you perform as the League secretary. That's good news, but you decide to begin by granting your new assistant read-only access to the member table that contains membership information, plus a column-specific UPDATE privilege on the expiration column of that table. That way, your assistant will have write access only for the rather modest task of updating expiration dates as people renew their memberships.The statements to set up this MySQL account are as follows:

 GRANT SELECT ON sampdb.member      TO 'assistant'@'localhost' IDENTIFIED BY 'officehelp'; GRANT UPDATE (expiration) ON sampdb.member     TO 'assistant'@'localhost'; 

The first statement grants read access to the entire member table and sets up a password. The second statement adds the UPDATE privilege, but only for the expiration column. It's not necessary to specify the password again because that was done by the first statement.

If you want to grant column-specific privileges for more than one column, specify a list of column names separated by commas. For example, to add UPDATE privileges for the address fields of the member table for the assistant user, issue the following statement. The new privileges will be added to any that already exist for the user:

 GRANT UPDATE (street,city,state,zip) ON sampdb.member      TO 'assistant'@'localhost'; 

Records in the grant tables do not "follow" renaming operations. For example, any privileges tied specifically to a given table will no longer apply if the table is renamed. This principle is true at the database, table, and column levels.

Why the "No Privileges" USAGE Privilege is Useful

The special privilege specifier USAGE means "no privileges." At first glance, this may not seem very useful, but it is. It allows you to change characteristics of an account other than those that pertain to privileges, while leaving the existing privileges alone. To use it, grant the USAGE privilege at the global level, specify the account name, and provide the new non-privilege characteristics of the account. For example, if you want to change an account password, require that the user connect using SSL, or impose a connection limit on an account without affecting the privileges held by the account, use statements like the following:

 GRANT USAGE ON *.* TO account IDENTIFIED BY 'new_password';  GRANT USAGE ON *.* TO account REQUIRE SSL; GRANT USAGE ON *.* TO account WITH MAX_CONNECTIONS_PER_HOUR 10; 

Are Secure Connections Required?

As of MySQL 4, secure connections can be made using the SSL (Secure Sockets Layer) protocol, which encrypts the data stream between the client and the server so that it is not sent in the clear. In addition, X509 can be used as a means for the client to provide identification information over SSL connections. Secure connections provide an extra measure of protection, but this comes at the price of the extra CPU horsepower required to perform encryption and decryption. SSL is supported only on UNIX at the moment.

To specify options for secure connections, use a REQUIRE clause. To require that a user connect via SSL without being more specific about the type of secure connection the user must make, use REQUIRE SSL:

 GRANT ALL ON sampdb.* TO 'eladio'@'%.snake.net' IDENTIFIED BY 'flint'      REQUIRE SSL; 

To be more specific, you can require that the client present a valid X509 certificate:

 GRANT ALL ON sampdb.* TO 'eladio'@'%.snake.net' IDENTIFIED BY 'flint'      REQUIRE X509; 

To be more specific yet, REQUIRE allows you to indicate that the client's X509 certificate must have certain characteristics or that the connection must be encrypted using a particular cipher type. These characteristics are given with ISSUER, SUBJECT, or CIPHER options in the REQUIRE clause. (ISSUER and SUBJECT refer to the certificate issuer and recipient.) For example, the ssl directory of the sampdb distribution includes a client certificate file, client-cert.pem, that you can use for testing SSL connections. The issuer and subject values in the certificate can be displayed like this:

 % openssl x509 -subject -noout -in client-cert.pem  issuer= /C=US/ST=WI/L=Madison/O=sampdb/OU=CA/CN=sampdb subject= /C=US/ST=WI/L=Madison/O=sampdb/OU=client/CN=sampdb 

The following GRANT statement creates an account for which the client must present a certificate that matches both of those values:

 GRANT ALL ON sampdb.* TO 'eladio'@'%.snake.net' IDENTIFIED BY 'flint'      REQUIRE ISSUER '/C=US/ST=WI/L=Madison/O=sampdb/OU=CA/CN=sampdb'     AND SUBJECT '/C=US/ST=WI/L=Madison/O=sampdb/OU=client/CN=sampdb'; 

To indicate explicitly that secure connections are not required, use REQUIRE NONE. The NONE option is available as of MySQL 4.0.4.

Some additional points to be aware of when using a REQUIRE clause:

• Issuing a GRANT statement that requires an account to use secure connections only sets up a constraint on the account. It doesn't actually provide the means for a client to connect securely with that account. For that to happen, MySQL must be configured to include SSL support, and you must start the server and clients in a particular way. Instructions for doing so are given in Chapter 12.

• If the server and client programs are configured with SSL support, any user will be able to use secure connections. REQUIRE is used only to indicate that an account must connect using secure connections.

• There is little point in using a REQUIRE clause for accounts that don't connect to the server over an external network. Such connections can't be snooped, so making them secure gains you nothing and incurs the increased computational load without benefit. Accounts like this include those that connect to the server only through a UNIX socket file or a named pipe or to the IP address 127.0.0.1 (the host's loopback interface). These connections use interfaces that are handled entirely internally to the host and for which no traffic crosses an external network.

Should the User Be Allowed to Administer Privileges?

You can allow the owner of a database to control access to the database by granting the owner all privileges on the database and specifying the WITH GRANT OPTION when you do. For example, if you want alicia to be able to connect from any host in the big-corp.com domain and administer privileges for all tables in the sales database, you could use the following GRANT statement:

 GRANT ALL ON sales.*      TO 'alicia'@'%.big-corp.com' IDENTIFIED BY 'shale'     WITH GRANT OPTION; 

In effect, the WITH GRANT OPTION clause allows you to delegate access-granting rights to another user. Be aware that two users with the GRANT OPTION privilege can grant each other their own privileges. If you've given one user only the SELECT privilege but another user has GRANT OPTION plus other privileges in addition to SELECT, the second user can make the first one "stronger."

Another way to grant the GRANT OPTION privilege is simply to list it in the beginning part of the GRANT statement:

 GRANT GRANT OPTION ON sales.* TO 'alicia'@'%.big-corp.com';  

However, a statement such as this one will not work:

 GRANT ALL,GRANT OPTION ON sales.* TO 'alicia'@'%.big-corp.com';  

ALL can be used only by itself, not in a list that names other privilege specifiers.

Should the User's Resource Consumption Be Limited?

As of MySQL 4.0.2, you can place limits on the number of times per hour that a user can connect to the server and the number of queries or updates per hour the user can issue. These limits are specified in the WITH clause. The following statement sets up a user spike that has full access to the sampdb database, but can connect only ten times per hour and issue 200 queries per hour (of which at most 50 can be updates):

 GRANT ALL ON sampdb.* TO 'spike'@'localhost' IDENTIFIED BY 'pyrite'      WITH MAX_CONNECTIONS_PER_HOUR 10 MAX_QUERIES_PER_HOUR 200     MAX_UPDATES_PER_HOUR 50; 

The order of the resource management options within the WITH clause does not matter. The default value for each option is zero, which means "no limit."

An administrative user who has the RELOAD privilege can reset the current counter values by issuing a FLUSH USER_RESOURCES statement. FLUSH PRIVILEGES does this as well. After the counters have been reset, accounts that have reached their hourly limits can once again connect and issue queries.

Revoking Privileges and Removing Users

To take away a user's privileges, use the REVOKE statement. The syntax for REVOKE is somewhat similar to that for the GRANT statement, except that TO is replaced by FROM; and there are no IDENTIFIED BY, REQUIRE, or WITH clauses:

 REVOKE privileges (columns) ON what FROM account;  

The account part must match the account part of the original GRANT statement for the user whose privileges you want to revoke. The privileges part need not match; you can grant privileges with a GRANT statement and then revoke only some of them with REVOKE. For example, the following GRANT statement grants all privileges on the sampdb database, and the REVOKE statement removes the account's privileges for making changes to existing records:

 GRANT ALL ON sampdb.* TO 'boris'@'localhost' IDENTIFIED BY 'ruby';  REVOKE DELETE,UPDATE ON sampdb.* FROM 'boris'@'localhost'; 

The GRANT OPTION privilege is not included in ALL, so if you have granted it, you can revoke it only by naming it explicitly in the privileges part of a REVOKE statement:

 REVOKE GRANT OPTION ON sales.* FROM 'alicia'@'%.big-corp.com';  

REVOKE removes privileges from an account, but it will not delete the account entirely. An entry for the account will remain in the user table, even if you revoke all of its privileges. This means the user can still connect to the server. To remove an account entirely, you must explicitly delete its record from the user table with a DELETE statement. For example, to delete the account for mary@%.snake.net, do this:

 % mysql -u root  mysql> USE mysql; mysql> DELETE FROM user     -> WHERE User = 'mary' and Host = '%.snake.net'; mysql> FLUSH PRIVILEGES; 

The DELETE statement removes the account's entry from the user table, and the FLUSH statement tells the server to reload the grant tables. FLUSH is necessary because the server reloads the grant tables automatically when you use GRANT or REVOKE, but not when you modify them directly. (To be complete, check the other grant tables for any records relating to the account, and delete any that may be present before flushing the privileges.)

Somewhat paradoxically, there are a few revocation operations that are done with GRANT. For example, if you specify that an account must connect using SSL, there is no REVOKE syntax for rescinding that requirement. Instead, issue a GRANT statement grants the USAGE privilege at the global level (to leave existing privileges unchanged) and include a REQUIRE NONE clause to indicate that SSL is not required:

 GRANT USAGE ON *.* TO account REQUIRE NONE;  

Similarly, if you set up resource limits on a user, you don't remove those limits with REVOKE. Instead, use GRANT with USAGE to set the limit values to zero ("no limit"):

 GRANT USAGE ON *.* TO account      WITH MAX_CONNECTIONS_PER_HOUR 0 MAX_QUERIES_PER_HOUR 0     MAX_UPDATES_PER_HOUR 0; 

Changing Passwords or Resetting Lost Passwords

One way to change or reset an account's password is to use an UPDATE statement that identifies the User and Host values for the account's user table record:

 mysql> UPDATE user SET Password=PASSWORD('silicon')      -> WHERE User='boris' AND Host='localhost'; mysql> FLUSH PRIVILEGES; 

However, it's a lot easier to use SET PASSWORD because you name the account using the same format as for GRANT, and it's unnecessary to flush the privileges explicitly:

 mysql> SET PASSWORD FOR 'boris'@'localhost' = PASSWORD('silicon');  

SET PASSWORD also is safer than UPDATE; with UPDATE, it's easier to make a mistake and change the wrong user table entry.

Another way to change a password is to use GRANT USAGE with an IDENTIFIED BY clause, in which case you specify the password literally rather than by using the PASSWORD() function:

 mysql> GRANT USAGE ON *.* TO 'boris'@'localhost' IDENTIFIED BY 'silicon';  

If you need to reset the root password because you've forgotten it and can't connect to the server, you have something of a conundrum because normally you have to connect as root to change the root password. If you don't know the password, you'll need to force down the server and restart it without grant table validation. The procedure for this is described in the "Regaining Control of the Server When You Can't Connect to It" section earlier in this chapter.