|
|
Managing MySQL User AccountsA 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:
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:
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 PrivilegesThe 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:
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:
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 PasswordThe 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:
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. Defining What Privileges an Account HasYou 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. 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:
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. Requiring an Account to Use Secure ConnectionsAs 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:
Allowing an Account to Administer PrivilegesYou 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 ConsumptionThe 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 PrivilegesTo 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 UsersTo 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 PasswordsOne 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 StructureSome 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:
|
|
|