MySQL

General MySQL Administration

User Account Management

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.

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.
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, 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 'userxyz'@'localhost' IDENTIFIED BY 'user'; GRANT ALL ON sampdb.* TO 'userabc'@'ares.mars.net' IDENTIFIED BY 'ebiz9999032942';

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, ravish@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 'ravish'@'localhost', not 'ravish@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 'user'@'%' 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.

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 'xyz'@'%.ebizel.net' IDENTIFIED BY '26198509';

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 'user'@'192.168.0.36' IDENTIFIED BY 'water';
GRANT ALL ON sampdb.* TO 'abc'@'192.168.0.%' IDENTIFIED BY 'snow';
GRANT ALL ON sampdb.* TO 'xyz'@'192.168.128.0/255.255.128.0'
IDENTIFIED BY 'coke';

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.0 Class C subnet. In the third statement, 192.168.0.0/255.255.0.0 specifies a netmask that has the first 17 bits turned on. It matches any host with 192.168.0 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, 'xyz' and 'max'@'%' are equivalent account values in GRANT statements. This means that if you intend to specify an account of 'xyz'@'localhost' but mistakenly write 'xyz@localhost' instead, MySQL will accept it as legal.

What happens is that MySQL interprets 'xyz@localhost' as containing only a user part and adds the default host part of % to it, resulting in an effective account name of 'xyz@localhost'@'%'. To avoid this, be sure always to quote the user and host parts of account specifiers separately.