MySQL Administrator Guide-MySQL User Management-MySQL

The MySQL administrator should know how to set up the MySQL User account and specify which user can connect to the server, where to connect, and what to do after the connection. MySQL3.22.11 introduces two statements to make this work easier: GRANT statements create MySQL Users and specify their permissions, while REVOKE statements delete permissions. The two statements assume the front-end role of the mysql database, and the MySQL administrator should know how to set the MySQL User account, which user can connect to the server, where to connect, and what to do after the connection. MySQL 3.22.11 introduces two statements to make this work easier: GRANT statements create MySQL Users and specify their permissions, while REVOKE statements delete permissions. The two statements assume the front-end role of the mysql database and provide a different method than directly operating the contents of these tables. The CREATE and REVOKE statements affect four tables:
　　　
There are 5th other authorization tables (hosts), but they are not affected by GRANT and REVOKE.
　　
When you issue a GRANT statement to a user, create a record for the user in the user table. If the statement specifies any global permissions (administrative permissions or permissions applicable to all databases), these are also recorded in the user table. If you specify database, table, and column-level permissions, they are recorded in db, tables_priv, and columns_priv tables respectively.
　　
Using GRANT and REVOKE is easier than directly modifying the authorization table. however, read MySQL Security Guide. These tables are exceptionally important, and as an administrator, you should understand how they go beyond the functional level of GRANT and REVOKE statements.
　　
In the following sections, we will introduce how to set up and authorize a MySQL User account. We also involve how to revoke permissions and delete users from the authorization table.
　　
You may also want to use mysqlaccess and mysql_setpermission scripts, which are part of MySQL distribution. they are Perl scripts and provide another option to set user accounts for GRANT statements. DBI support is required for mysql_setpermission.
　　
　　 1. create and authorize a user
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 must enter the following parts:
　　
Privileges
The following table lists the permissions that can be used for GRANT statements:
　　
　　
The table above shows that the permission specifiers in the first group apply to databases, tables, and columns, and the second group manages permissions. Generally, these are relatively strictly authorized because they allow users to affect server operations. The third group has special permissions. "ALL" means "ALL permissions", and "UASGE" means "no permissions", that is, creating users, but not granting permissions.
　　
Columns
The permission column is optional, and you can only set specific permissions for the column. If the command has more than one column, separate them with commas.
　　
What
Permission usage level. Permissions can be global (applicable to all databases and tables), specific databases (applicable to all tables in a database), or specific tables. You can specify a columns statement to indicate that the permission is column-specific.
　　
User
The user authorized by the permission, which consists of a user name and host name. In MySQL, you not only specify who can connect, but also where to connect. This allows two users with the same name to connect from different places. MySQL allows you to differentiate them and grant them permissions independently.
　　
A user name in MySQL is the user name specified when you connect to the server. it does not need to be associated with your Unix or Windows name. By default, if you do not specify a specific name, the customer program uses your login name as the MySQL User name. This is just an agreement. You can change the name to nobody in the authorization table, and then use the nobody connection to perform operations that require superuser permissions.
　　
Password
The password assigned to the user. it is optional. If you do not specify the identified by clause for a new user, the user is not assigned a password (insecure ). For existing users, any password you specify will replace the old password. If you do not specify a password, the old password remains unchanged. when you use identified by, the password string uses the literal meaning of the password, and GRANT will encode the password for you, do not use the PASSWORD () function as you use SET password.
　　
The with grant option clause is optional. If you include it, you can GRANT permissions to other users through the GRANT statement. You can use this clause to grant permissions to other users.
　　
The username, password, database, and table name are case sensitive in the authorization table record, and the host name and column name are not.
　　
Generally, you can identify the types of GRANT statements by asking a few simple questions:
　　
Who can connect from there?
What level of permissions should users have and what do they apply?
Should the user be allowed to manage permissions?
The following are some examples.
　　
1.1 who can connect and connect from there?
You can allow a user to connect from a specific host or a series of hosts. There is one extreme: If you know that a demotion is connected from a host, 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. * indicates "all tables in the samp_db database.) another extreme is that you may have a user max that is frequently traveling and needs to be connected from hosts around the world. In this case, you can allow him to connect from anywhere:
　　
Grant all on samp_db. * TO max @ % identified by "diamond"
The "%" character acts as a wildcard and matches the LIKE pattern. In the preceding 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.
　　
You can allow a user to access from a restricted host set. For example, to allow mary to be connected from any host in the snake.net domain, use the following identifier:
　　
Grant all on samp_db. * TO mary @ .snke.net identified by "quartz ";
If you like, the host part of the user identifier can be specified by an IP address instead of a host name. You can specify an IP address or an address that contains a pattern character. In addition, from MySQL 3.23, you can also specify an IP number with a network mask indicating 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 that the user can connect to a specific host, and the second specifies the IP mode for the Class C subnet 192.168.128. in the third statement, 192.168.128.0/17: specify a 17-bit network number and match the IP address with the header 192.168.128.
　　
If MySQL complains about the user value you specified, you may need to use quotation marks (only separate the user name and host name with quotation marks ).
　　
Grant all on samp_db.president TO "my friend" @ "boa.snke.net"
1.2 What level of permissions should users have and what should they apply?
You can grant different levels of permissions. global permissions are the most powerful because they apply to any database. To make Etel a super user who can do anything, including authorizing other users, issue the following statement:
　　
Grant all on *. * TO Etel @ localhost identified by "coffee" WITH GRANT OPTION
*. * In the ON clause indicates "all databases and all tables ". For security considerations, we specify that Etel can only be connected locally. It is usually wise to restrict the host that a super user can connect to because it limits the host that tries to crack the password.
　　
Some permissions (FILE, PROCESS, RELOAD, and SHUTDOWN) are administrative permissions and can only be authorized with the "ON *. *" global permissions. If you want to, you can grant these permissions without authorizing Database permissions. For example, if the following statement sets a flush user, it can only issue flush statements. This may be useful when you need to execute management scripts such as clearing logs:
　　
Grant reload on *. * TO flushl @ localhost identified by "flushpass"
Generally, you want to authorize management permissions, because users with these permissions can affect the operations on your servers.
　　
Database-level permissions apply to all tables in a specific database. they can be granted by using the ON db_name. * clause:
　　
Grant all on samp_db TO bill@racer.snake.net indetified by "rock"
Grant select on samp_db TO ro_user @ % indetified by "rock"
The first statement grants permissions to all tables in the samp_db database to bill, and the second statement creates a user ro_user (read-only user) that strictly restricts access, which can only access all tables in the samp_db database, but only read, that is, you can only issue SELECT statements.
　　
You can list a series of permissions granted at the same time. For example, if you want to allow users to read and modify the content of an existing database but cannot create or delete a new table, grant the following permissions:
　　
Grant select, INSERT, DELETE, update on samp_db TO bill@snake.net indetified by "rock"
For more refined access control, you can grant permissions on each table or even on each column of the table. When you want to hide a part of a table from a user, or you want a user to modify only specific columns, column-specific permissions are very useful. For example:
　　
Grant select on samp_db.member TO bill @ localhost indetified by "rock"
Grant update (expiration) ON samp_db. member TO bill @ localhost
The first statement grants the read permission to the entire member table and sets a password. The second statement adds the UPDATE permission when only the expiration column is applied. You do not need to specify a password because the first statement has already been specified.
　　
If you want to grant permissions to multiple columns, specify a list separated by commas. For example, to add the UPDATE permission for the address field of the member table to the assistant user, use the following statement to add the new permission to the user's existing permissions:
　　
Grant update (street, city, state, zip) ON samp_db TO assistant @ localhost
　　
Generally, you do not want to grant any permissions that are wider than what the user really needs. However, when you want users to create a temporary table to save intermediate results, but you do not want them to do so in a database that contains the content they should not modify, A relatively loose permission is granted to a database. You can create a separate database (such as tmp) and Grant all permissions to the database. For example, if you want any user from a host in the mars.net domain to use the tmp database, you can issue the following GRANT statement:
　　
Grant all on tmp. * TO "" @ mars.net
　　
After you finish, you can create a table in tmp. tbl_name and reference it in the form of tmp (create an anonymous user in "" specified by the user, and all users match the blank user name ).