Team LiB
Previous Section Next Section

Managing MySQL User Accounts

A 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 these statements:

  • GRANT creates MySQL accounts and specifies their privileges.

  • REVOKE removes privileges from existing MySQL accounts.

  • DROP USER removes the last traces of an account from which you have revoked all privileges.

  • SET PASSWORD assigns a password to an existing account.

These statements make it easier to manage user accounts. Before they were introduced, it was necessary to manipulate the contents of the grant tables directly by issuing SQL statements like INSERT and UPDATE. You can still do that if you like, but statements such as 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.

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, the server creates a record for that account in the user table if one does not already exist. 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.

You should be aware that new releases of MySQL sometimes add new privileges. When upgrading an existing MySQL installation to such a version, it's necessary to update the grant tables before you can use the new privileges. The procedure for doing this is given in "Dealing with Changes to Grant Table Structure."

Another account-related issue that you might have to deal with if you upgrade to MySQL 4.1 from an earlier version has to do with a change in how passwords are handled from 4.1 on. For details, see "Configuring Backward Compatibility for Password Handling."

Creating New Accounts 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 use 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. Multiple privileges can be named, separated by commas.

  • columns

    The columns to which the privileges apply. 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 that connect from different locations. MySQL lets you distinguish between them and assign privileges to each, independent of the other. The user_name and host_name values are recorded in the User and Host columns of the user table record 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 or Windows login name. By default, the MySQL username that client programs use on Unix is your login name if you don't specify a name explicitly, but that's just a convention. There also is 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 superduper in the grant tables and then connect as superduper 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 account, that account 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.

    If you want to avoid the possibility of creating an insecure account that has no password, enable the NO_AUTO_CREATE_USER SQL mode. This mode is available as of MySQL 5.0.2 and prevents creation of new users with GRANT unless the statement includes an IDENTIFIED BY clause.

The REQUIRE and WITH clauses are optional. REQUIRE 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. WITH also is used to specify resource management options that allow you to place limits on how many connections or statements 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 is the user's password?

  • 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 shows how to answer these questions and provides examples that illustrate how to use the various clauses of the GRANT statement when setting up MySQL user accounts.

Specifying an Account Name and Password

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 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 single-host 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';

Keep in mind that the hostname part is the host from which the client will be connecting. It is not the server host to which the client will connect (unless they are the same).

If the username or hostname parts of the account value contain any special characters such as '-' or '%', you will need to quote them. If you can use them as unquoted identifiers, you need not quote them. For example, boris@localhost is legal without quotes. However, it is always 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'. Usernames and hostnames can be quoted either with string quoting characters or identifier quoting characters.

Allowing a user to connect from a single host is the strictest form of access you can allow. At the other extreme, you might 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 like this:

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

To specify a literal '%' or '_' wildcard character, precede it by a backslash.

The host part of the account value can be given using an IP address rather than a hostname if you like. You can specify a literal IP address or an address that contains pattern characters. Also, 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, 192.168.128.3, 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 hostname of localhost in a GRANT statement allows a user to connect to the server from the local host in a number of ways:

  • On Unix, the user can connect by specifying a host value of localhost or 127.0.0.1. The localhost connection is made using the Unix socket file. 127.0.0.1 causes a TCP/IP connection to be made using the local host's loopback IP interface.

  • On Windows, the user can connect by specifying a host value of localhost or 127.0.0.1. Both of these connections are made using TCP/IP, except that if the server supports shared-memory connections, a connection to localhost is made using shared memory by default. If the server supports named-pipe connections, the user can connect through the pipe by specifying a hostname of "." (period).

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 that 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 the server write the hostname there 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 the account name when you issue GRANT statements.


Defining What Privileges an Account Has

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

FILE

Read and write files on the server host

GRANT OPTION

Grant the account's privileges to other accounts

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

See all database names with SHOW DATABASES

SHUTDOWN

Shut down the server

SUPER

Kill threads and perform other supervisory operations

ALTER

Alter tables and indexes

ALTER ROUTINE

Alter or drop stored procedures and functions

CREATE

Create databases and tables

CREATE ROUTINE

Create stored procedures and functions

CREATE TEMPORARY TABLES

Create temporary tables using the TEMPORARY keyword

CREATE VIEW

Create views

DELETE

Delete rows from tables

DROP

Drop (remove) databases and tables

EXECUTE

Execute stored procedures and functions

INDEX

Create or drop indexes

INSERT

Insert new rows into tables

LOCK TABLES

Explicitly lock tables with LOCK TABLES statements

REFERENCES

Unused (reserved for future use)

SELECT

Retrieve rows from tables

SHOW VIEW

Show view definitions

UPDATE

Modify table rows

ALL, ALL PRIVILEGES

All operations (except GRANT)

USAGE

A special "no privileges" privilege


The privilege specifiers in the first group shown in the table are administrative privileges. Normally, you grant them relatively sparingly because they allow users to affect the operation of the server. (The SHUTDOWN privilege is not one you should 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 existing account without changing its current privileges.

CREATE VIEW and SHOW VIEW were introduced in MySQL 5.0.1. ALTER ROUTINE and CREATE ROUTINE were introduced in MySQL 5.0.3, which is also the version in which the EXECUTE privilege became operational.

You can grant privileges at different levels, 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 default 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


For the table-level specifiers, you can specify a (column_list) clause following the privilege list to grant the privileges at the column level. The syntax for this is shown in a later example.

To grant a privilege, you must have that privilege yourself, and you must have the GRANT OPTION privilege.

The ALL privilege specifier grants all privileges that are available at a given level. (For example, at the global level, it grants all privileges. At the table level, it grants only privileges that apply to tables.) ALL can be used only when granting global, database, or table privileges. For column privileges, you must name each privilege that you want to grant.

The USAGE privilege should be used only at the global level.

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 a good idea because it limits the set of hosts from which password-cracking attempts can be mounted.

Some privileges are administrative in nature and can be granted only using the ON *.* global-privilege specifier. These include FILE, PROCESS, RELOAD, SHUTDOWN, and most of 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 "Log File Expiration" 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 when he connects from racer.snake.net. The second creates a restricted-access user named reader that can connect from any host to 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, grant only the specific privileges to be allowed:

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 for individual tables, or even for 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 that someone volunteers to help you out at the Historical League as an office assistant. 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 needed 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 of the member table. It's not necessary to specify the password again in the second statement because that was already done by the first statement.

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 columns 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';

If you quote database, table, or column names in the GRANT statement, quote them as identifiers, not as strings. For example:

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

Records in the grant tables do not "follow" database, table, or column renaming operations. For example, any privileges tied specifically to a given table no longer apply if the table is renamed.

Why the "No Privileges" USAGE Privilege Is Useful

The special USAGE privilege specifier means "no privileges." This may not seem very useful at first glance, 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 these:

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;


Requiring an Account to Use Secure Connections

As of MySQL 4, secure connections can be made using the Secure Sockets Layer (SSL) 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.

It is easier to use SSL connections on Unix. To enable SSL support, you likely will have to compile MySQL yourself, which is easier on Unix than on Windows.

To specify requirements 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 strict, you can require that the client present a valid X509 certificate:

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

REQUIRE X509 imposes no constraints on the certificate's contents other than that it be valid. To be even more strict, REQUIRE allows you to indicate that the client's X509 certificate must have certain characteristics. These characteristics are given with ISSUER or SUBJECT 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 -issuer -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';

You can also use REQUIRE to indicate that the connection must be encrypted using a particular cipher type:

GRANT ALL ON sampdb.* TO 'eladio'@'%.snake.net' IDENTIFIED BY 'flint'
    REQUIRE CIPHER 'DHE-RSA-AES256-SHA';

To indicate explicitly that secure connections are not required, use REQUIRE NONE. This is the default when you create a new account, but it can be useful for removing a requirement for SSL from an account that currently has it.

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 program 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 you specify that connections for an account must use SSL, but SSL is not supported by the server or client, the account effectively is unusable.

  • REQUIRE is used only to indicate that an account must connect using secure connections. If the server and client programs are configured with SSL support, any user is still able to use secure connections, even if not required to do so.

  • 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 encrypted gains you nothing and incurs increased computational load without benefit. Accounts like this include those that connect to the server only through a Unix socket file, a named pipe, shared memory, or to the IP address 127.0.0.1 (the host's loopback interface). These connections use interfaces that are handled entirely internal to the host and for which no traffic crosses an external network.

Allowing an Account to Administer Privileges

You can allow an account to grant its own privileges to other accountsby specifying the WITH GRANT OPTION clause. To use this clause, you must have the GRANT OPTION privilege yourself.

One reason to give an account the GRANT OPTION privilege is to allow the owner of a database to control access to the database: Granting the owner all privileges on the database, including the GRANT OPTION privilege. 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 this 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';

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

GRANT OPTION is specific to the level at which it is granted, not to individual privileges. If you given an account the GRANT OPTION privilege at a given level, the account can grant all privileges that it holds at that level. You cannot specify that the account can grant some of the privileges that it holds at that level but not others.

Limiting an Account's Resource Consumption

The MySQL grant system allows you to place limits on the number of times per hour that a user can connect to the server, and the number of statements or updates per hour the user can issue. To specify these limits, use a 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 statements 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 default value for each option is zero, which means "no limit." This means that if you have placed a resource limit on an account, you can remove the limit by changing the limit value to zero. For example, to remove the limit on the number of times per hour that spike can connect, use this statement:

GRANT USAGE ON *.* TO 'spike'@'localhost'
    WITH MAX_CONNECTIONS_PER_HOUR 0;

A user cannot subvert these limits by using multiple connections to the server, because all connections for a given account are counted together.

As of MySQL 5.0.3, a fourth resource limit, MAX_USER_CONNECTIONS, is available to control the maximum number of simultaneous connections that the account can have. If the limit is zero (the default), the limit is controlled by the value of the max_user_connections system variable. A non-zero value limits the account to that many simultaneous connections.

The order of the resource management options within the WITH clause does not matter.

Any 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 once again can connect and issue statements. A reset also occurs for an individual account if you issue a GRANT statement that sets that account's limits.

Displaying Account Privileges

To see what privileges an account has, use the SHOW GRANTS statement:

SHOW GRANTS FOR 'sampadm'@'localhost';

To see your own privileges, use either of these statements, which are available as of MySQL 4.1.2:

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();

Revoking Privileges and Removing Users

To take away an account'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 account from which privileges should be revoked. 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. If you have granted it, revoke it by naming it explicitly in the privileges part of a REVOKE statement:

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

To revoke a privilege, you must have that privilege yourself, and you have the GRANT OPTION privilege.

To revoke all privileges including GRANT OPTION at the global, database, or table level, you can combine ALL and GRANT OPTION. For example:

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

To revoke all privileges held by an account at any level, use this statement:

REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'account';

This syntax is available as of MySQL 4.1.2. Notice that there is no ON clause. It requires the GRANT OPTION privilege for the mysql database.

If you revoke all of an account's privileges at the database, table, or column level, MySQL removes the corresponding account record from the db, tables_priv, or columns_priv table. Revoking all of an account's global privileges sets privilege columns to 'N' in its user table record, but does not delete the record. That is, REVOKE does not delete the account entirely. This means the user can still connect to the server. To remove the last remains for an account that has had all its privileges revoked, you can use the DROP USER statement. For example, if the mary@%.snake.net has no privileges, use this statement:

DROP USER 'mary'@'%.snake.net';

DROP USER removes the account's user table record. It requires the GRANT OPTION privilege for the mysql database.

An equivalent operation that deletes the record manually consists of these statements:

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

With DELETE, it is necessary to use FLUSH PRIVILEGES to tell the server to reload the grant tables. With DROP USER, the server reloads them automatically.

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 that 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, and then flush the privileges:

mysql> USE mysql;
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 that is used 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, less common, 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';

You can always change your own password, unless you have connected as an anonymous user. To change the password for another account, you must have the UPDATE privilege for the mysql database.

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 problem, because normally you must 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 earlier in the chapter, in "Regaining Control of the Server When You Can't Connect to It."

Dealing with Changes to Grant Table Structure

Some versions of MySQL introduce changes to the structure of the grant tables. The first time you install MySQL on a machine, the installation procedure creates the grant tables with the structure current to the version that you install. If you upgrade an older version of MySQL to a newer version, you must update the grant tables manually. To do this, run the script that updates each table with any modifications that have been made since you installed the older version of MySQL:

  • On Unix, you can bring the tables up to date by running the mysql_fix_privilege_tables script. It needs to connect to the local server as the MySQL root user, so invoke it with the appropriate password:

    % mysql_fix_privilege_tables --password=root-password
    

  • On Windows, mysql_fix_privilege_tables cannot be used because it is a shell script. Instead, connect to the server as root using the mysql program and execute the contents of the mysql_fix_privilege_tables.sql file. For example, if MySQL is installed at C:\mysql, use these commands:

    C:\> mysql -p -u root mysql
    mysql> source C:\mysql\scripts\mysql_fix_privilege_tables.sql
    

    Team LiB
    Previous Section Next Section