MySQL Database administrator's Guide

The MySQL administrator should know how to set up a MySQL user account, indicating which user can connect to the server, where to connect, and what to do when connected. MySQL 3.22.11 started introducing two statements to make this work easier: The GRANT statement creates the MySQL user and assigns its permissions, while the REVOKE statement deletes the permissions. The two statements act as the front-end role of the MySQL database and provide a different approach to the contents of the tables directly manipulated. The Create and REVOKE statements affect 4 tables:

Authorization table	Content
User	Users who can connect to the server and any global permissions they have
Db	Database-level permissions
Tables_priv	Table-level permissions
Columns_priv	Column-level permissions

There is also a 5th authorization table (host), but it is not affected by grant and revoke.

When you issue a grant statement to a user, create a record in the user table for that person. If the statement specifies any global permissions (administrative permissions or permissions that apply to all databases), these are also recorded in the user table. If you specify database, table, and column-level permissions, they are recorded separately in DB, Tables_priv, and Columns_priv tables.

It is easier to use grant and revoke than to modify the authorization form directly, however, it is recommended that you read the MySQL security Guide. These tables are extremely important, and as an administrator, you should understand how they go beyond the functional level of the grant and REVOKE statements.

In the following sections, we will describe how to set up a MySQL user account and authorize it. We also deal with how to withdraw and remove users from the authorization table.

You may also want to consider using mysqlaccess and mysql_setpermission scripts, which are part of the MySQL distribution, which are Perl scripts that provide another option for grant statements to set up user accounts. Mysql_setpermission need to install DBI support.

1 Create a user and authorize

The syntax of the GRANT statement looks like this:

GRANT privileges (columns) ON what TO user IDENTIFIED BY "password" WITH GRANT OPTION

To use this statement, you need to fill in the following sections:

privileges

Permissions granted to the user, the following table lists the permission specifiers available for the GRANT statement:

Permission specifier	Actions allowed by permission
Alter	modifying tables and Indexes
CREATE	Creating databases and Tables
DELETE	Delete existing records in a table
DROP	Discard (delete) databases and tables
INDEX	Create or discard an index
INSERT	Insert a new row into the table
REFERENCE	Not used
SELECT	Retrieving records in a table
UPDATE	Modify an existing table record
FILE	Read or write files on the server
PROCESS	To view the thread information executed in the server or kill the thread
RELOAD	Overload authorization table or empty log, host cache, or table cache
SHUTDOWN	Shutting down the server
All; All privileges	Synonyms
USAGE	Special "No Permissions" permission

The table above shows the permissions specified in the first set of Fu Yi for databases, tables, and columns, and the second set of administrative permissions. Generally, these are relatively strictly authorized because they allow the user to affect the operation of the server. The third set of privileges is special, all means "All permissions", Uasge means no permissions, that is, create the user, but do not grant permissions.

Connect with the local area. MySQL lets you differentiate them and give each other independent permission.

A username in MySQL is the user name you specify when you connect to the server, which does not have to be associated with your UNIX login or Windows name. By default, if you do not specify a name explicitly, the client program will use your login name as the MySQL user name. It's just a pact. You can change the name to nobody in the authorization table and then perform an operation that requires superuser privileges with a nobody connection.

Password

The password given to the user, which is optional. If you do not specify a identified by clause for a new user, the user is not assigned a password (unsafe). For existing users, any password you specify will replace the old password. If you do not specify a password, the old password remains the same, and when you use identified by, the password string uses the literal meaning of the password, grant will encode the password for you, and do not use the PASSWORD () function as you did with set PASSWORD.

The WITH GRANT option clause is optional. If you include it, users can grant permissions to other users through grant statements. You can use this clause to give other users the ability to authorize them.

User names, passwords, databases, and table names are case-sensitive in authorization table records, and host and column names are not.

Generally, you can identify the type of grant statement by asking a few simple questions:

Who can connect and connect from there?

What level of permissions should users have and what do they apply to?

Should the user allow administrative permissions?

Some examples are discussed below.

1.1 Who can connect and connect from there?

You can allow a user to connect from a specific or a range of hosts. There's an extreme if you know the demotion from a host connection, you can limit the permissions to a single host:

GRANT ALL ON samp_db.* TO boris@localhost IDENTIFIED BY "ruby"GRANT ALL ON samp_db.* TO fred@res.mars.com IDENTIFIED BY "quartz"

(samp_db.* means "All tables of the SAMP_DB database") The other extreme is that you may have a user Max who travels frequently and needs to be able to connect with hosts from all over the world. In this case, you can allow him to connect from wherever:

GRANT ALL ON samp_db.* TO max@% IDENTIFIED BY "diamond"

The "%" character Fu Qi wildcard action, the same meaning as like pattern matching. In the above statement, it means "any host". So Max and max@% are equivalent. This is the easiest way to build a user, but it is also the least secure.

To take it, you can allow a user to access from a restricted set of hosts. For example, to allow Mary to connect from any host in the snake.net domain, use a%.snake.net host specifier:

GRANT ALL ON samp_db.* TO mary@.snake.net IDENTIFIED BY "quartz";

If you like, the host part of the user identifier can be given with an IP address instead of a host name. You can specify an IP address or an address that contains a pattern character, and, from MySQL 3.23, you can also specify the IP number of the netmask that indicates the number of digits used for the network number:

GRANT ALL ON samp_db.* TO boris@192.168.128.3 IDENTIFIED BY "ruby" GRANT ALL ON samp_db.* TO fred@192.168.128.% IDENTIFIED BY "quartz" GRANT ALL ON samp_db.* TO rex@192.168.128.0/17 IDENTIFIED BY "ruby"

The first example indicates the specific host from which the user is connected, the second specifies the IP mode for the C-class subnet 192.168.128, and in the third statement, 192.168.128.0/17 specifies a 17-bit network number and matches an IP address with 192.168.128 first 17 digits.

If MySQL complains about the user value you specify, you may need to use quotes (only the user name and the hostname part are quoted separately).

GRANT ALL ON samp_db.president TO "my friend"@"boa.snake.net"