Basic knowledge of MySQL database user account management _ MySQL

The MySQL administrator should know how to set the MySQL User account by specifying which users can connect to the server, where to connect, and what to do during connection. MySQL3.22.11 introduces two statements that are easier to perform this work: GRANT statement to create a MySQL User and specify its permissions, and REVOKE statement to delete permissions. These two statements are used to grant permissions in the mysql database.

The MySQL administrator should know how to set the MySQL User account by specifying which users can connect to the server, where to connect, and what to do during connection. MySQL3.22.11 introduces two statements that are easier to perform this work: GRANT statement to create a MySQL User and specify its permissions, and REVOKE statement to delete permissions. These two statements act as the front-end of the authorization table in the mysql database and provide an alternative method for directly manipulating the table content. The GRANT and REVOKE statements affect the following four tables:

Authorization table content

Users can connect to users on the server and have any global privileges.

Database-level db privileges

Tables_priv table-level privileges

Columns_priv column-level privileges

There is also the fifth authorization table (host), but it is not affected by GRANT or REVOKE.

When you publish a GRANT statement for a user, you should create an item for the user in the user table. If this statement specifies all global privileges (administrative permissions or permissions for all databases), these are also recorded in the user table. If database, table, or column permissions are specified, they are recorded in db, tables_priv, and columns_priv tables.

Using GRANT and REVOKE statements is easier than directly modifying the authorization table. However, it is recommended that you read chapter 12th to add the content of this chapter. Chapter 12th details the authorization table. These tables are very important. as an administrator, you should understand how these tables work at the GRANT and REVOKE statement levels.

The following sections describe how to set the account and authorization of a MySQL User. They also describe how to cancel permissions and delete all users from the authorization table, in addition, we will consider a problem that has plagued many new MySQL administrators.

You must also consider using mysqlaccess and mysql_setpermission scripts, which are components of the MySQL distribution package. These are Perl scripts that provide substitutes for GRANT statements for setting user accounts. Mysql_setpermission requires a DBI support environment.

Create new user and authorize

The syntax of the GRANT statement is as follows:

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

To use this statement, enter the following:

The permissions that privileges assigns to the user. The following table lists the permission specifiers that can be used in a GRANT statement:

Permitted operations

The first set of permission specifiers shown in the table above applies to databases, tables, and columns. The second set of specifiers is the management privilege. In general, these permissions are quite conservative because they affect server operations (for example, the SHUTDOWN privilege is not distributed on a daily basis ). The third set of specifiers is special. ALL means "ALL permissions", while USAGE means "no permissions"-that is, creating a user without granting any permissions.

Columns. This is optional. you can only set the column-specific permissions. If you name more than one column, separate them with commas.

What permission application level. The permission can be global (applicable to all databases and tables), database proprietary (applicable to all tables in a database), or table proprietary. You can grant permissions to specific columns by specifying a c o l u m n s clause.

User. It consists of the user name and host name. In MySQL, not only specify who is connected, but also specify where to connect. It allows you to have two users with the same name connected from different locations. MySQL allows you to differentiate between them and assign permissions to each other independently.

The MySQL User name is the name you specify when you connect to the server. This name is not necessarily related to your UNIX registration name or Windows name. By default, the client uses the name you registered as the MySQL User name (if you do not specify a name explicitly), but this is just a convention. This is also a convention about using root as the super user name that can operate on all MySQL databases. You can also change this name to nobody in the authorization table and connect it as a nobody user to perform operations that require the superuser privilege.

The password assigned to the user. This is optional. If you do not specify an IDENTIFIEDBY clause for a new user, the user will not be assigned a password (insecure ). For existing users, any specified password will replace the old password. If no new password is specified, the old password remains unchanged. When you do want to use IDENTIFIEDBY, the password string should be a direct quantity, and GRANT will encode the password. Do not use the PASSWORD () function when using the set password statement.

The with grant option clause is optional. If this clause is included, the user can GRANT any permissions granted by the GRANT statement to other users. You can use this clause to grant the authorization capability to other users.

The username, password, and database and table names are case-sensitive in the authorization table, while the host name and column name are not.

By querying some problems, we can generally infer the type of the required GRANT statement:

Who can connect and from where?

What level of permissions should a user have and what permissions should they apply?

Are users allowed to manage permissions?

Let's ask these questions and look at some examples of using the GRANT statement to set the MySQL User account.

1. Who can connect and where to connect.

You can allow users to connect to a specific host or a wide range of hosts. In an extreme condition, if you know that the user will only connect from that host, you can restrict access to a single host:

GRANT ALL ON samp_db.* TO boris@localhost IDENTFIEDBY "ruby" GRANT ALL ON samp_db.* TO fred@ares.mars.net IDENTFIEDBY "quartz"

(Symbol samp_db. * meaning "all tables in the samp_db database") in another extreme, you may have a user, max, who travels around the world and needs to be able to connect from hosts around the world. In this case, you are allowed to connect from anywhere:

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

The '%' character acts as a wildcard and has the same meaning as the LIKE pattern. in the previous statement, it means "any host ". If you do not provide the host name at all, it means the same as the specified "%. Therefore, max and max @ % are equivalent. This is the easiest way to set a user, but with minimal security.

To compromise, you can allow users to connect to a limited set of hosts. For example, to enable mary to connect from any host in the snake.net domain, you can use the % .snak.net host Specifier:

GRANT ALL ON samp_db.* TO mary@%.snake.net IDENTFIEDBY "topaz"

Some available IP addresses of the host with this user identifier are provided instead of the host name (if you prefer ). You can specify a direct IP address or an address that contains mode characters. Similarly, from MySQL3.23, you can use a network mask to specify the IP number. The network mask indicates the binary digits used for the network number:

GRANT ALL ON samp_db.* TO joe@192.168.0.3 IDENTIFIEDBY "water" GRANT ALL ON samp_db.* TO ardis@192.168.128.% IDENTIFIEDBY "snow" GRANT ALL ON samp_db.* TO rex@192.168.128.0/17 IDENTIFIEDBY "ice"

The first statement specifies the specific host that the user can connect. The second statement specifies the IP address mode of the 129.168.128 Class C subnet. In the third statement, 192.168.128.0/17 specifies a 17-bit binary network number, and matches any host with 192.168.128.0/17 in the first 17 binary digits of its IP address.

If MySQL complains about the user value you specified, you may need to use quotation marks (but the user name and host name are respectively quoted ):

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

2. what level of permissions should the user have and what permissions these permissions 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 that can perform all operations (including authorizing other users), publish the following statements:

GRANT ALL ON *.* TO ethel@localhost IDENTIFIEDBY "coffee" WITH GRANT OPTION

*. * In the ON clause indicates "all databases, all tables". to be safe, we have specified that Etel can only be connected from the local host. It is usually wise to restrict the hosts on which super users connect, because it limits other users to test their passwords.

Some permissions (FILE, PROCESS, RELOAD, and SHUTDOWN) are administrative permissions and can only be granted with the NO *. * Global permission specifier. If you want to, you do not need to grant database-level permissions. For example, the following statement creates a flush user, which does not do anything except publish the FLUSH statement. This may be useful in management scripts because you need to execute operations such as refreshing logs during log file loops in these scripts:

GRANT RELOAD ON *.* TO flush@localhost IDENTIFIEDBY "flushpass"

Generally, granting administrative permissions should be conservative, because users with these permissions may affect server operations.

Database-level permissions apply to all tables in a specific database. These permissions are granted using the ON db_name. * clause:

GRANT ALL ON samp_db.* TO bill@racer.snake.net IDENTIFIEDBY "rock" GRANT SELECT ON menagerie.* TO ro_user@% IDENTIFIEDBY "dirt"

The first statement grants all the permissions of bill to any table in the samp_db database. The second statement creates a restricted access user, Ro _ user (read-only user), which can access all tables in the menagerie database but can only be read. That is to say, the user can only publish SELECT statements.

Specify the local host name in the authorization table

If you use the server's host name instead of localhost, there is usually a problem of connecting from the server host. This may be due to an error match between the method with the name specified in the authorization table and the method with the name reported to the program by name reslover routine. If the parser reports an unqualified name (such as pit-viper), but the authorization table contains an item with a fully qualified name (such as a pit-viper.snake.net, and vice versa ), an error occurs.

To determine whether this situation is happening in the system, you can use the-h option to connect to the local server. this option specifies the host name. View the common log files of the server. How does one report the host name? Is it a non-restricted form or a restricted form? Regardless of the form, it tells you how to specify the host name of the user specifier when releasing the GRANT statement.

You can list multiple granted permissions at the same time. For example, if you want to allow users to read and modify the content of an existing table, but do not allow users to create or delete a new table, perform the following authorization:

GRANT SELECT,INSERT,DELETE,UPDATE ON samp_db.* TO jennie@% IDENTIFIEDBY "boron"

For fine-grained access control, authorization can be performed on a single table or even on a single column of the table. When a table is to be hidden from the user, or when only the user is allowed to modify a specific column, the column-specific permissions are useful. It is assumed that some volunteers in the past league will use your responsibilities as the delegate of the league to help you. This is good news, but you decided to first grant the new assistant the read-only permission to the member table (the table contains the membership information ), then they are granted the expiration column exclusive UPDATE permission for the table. That is to say, your assistant can make changes when people update their membership.

The first statement grants read access to the entire member table and sets a password. The second statement adds the UPDATE permission, but only applies to the expiration column. In this case, you do not need to specify a password because it is completed in the first statement.

End date of work. The statement for setting this MySQL User is as follows:

If you want to grant column-specific permissions to multiple columns, you can specify a column list and separate these columns with commas. For example, to grant the assistant user the UPDATE permission on the address column of the member table, you can use the following statements. The new permissions will be added to all columns that already exist for the User:

GRANT UPDATE (street,city,state,zip) ON samp_db.member TO assistant@localhost

Generally, do not grant users more permissions than they actually need. However, if you want to enable users to create temporary tables that store intermediate results, but do not allow users to do so in databases that contain data that they cannot modify, there is a reason to grant a considerable number of permissions to the database. You can create a separate database (I call it t m p) and Grant all permissions to the user. For example, if you want any user on the host of the mars.net domain to use the tmp database, you can publish the following GRANT statement:

GRANT ALL ON tmp.* TO ""@%.mars.net

After completing these steps, you can use tmp. create and reference tables in the tmp database by name in tbl_name format ("" in the user description creates an anonymous user item, and any user matches the blank user name ).

3. allow users to manage permissions:

By granting all Database permissions to the database owner and specifying with grant option during operations, the database owner can control access to the database. For example, to allow alicia to connect to and manage all tables in the sales database on all hosts in the big.corp.com domain, use the GRANT statement.

In fact, the with grant option clause allows another user to be granted access rights. You must know that two users with the GRANT permission can GRANT their own permissions to each other. If only one user is granted the SELECT permission and another user is granted the GRANT and other permissions in addition to the SELECT permission, the second user can make the first user "powerful.

Cancel permissions and delete users

To REVOKE the permissions of a user, you can use the REVOKE statement. Except for replacing to with from without the IDENTIFIEDBY or with grant option clause, the REVOKE syntax is very similar TO that of the GRANT statement:

REVOKE privileges (columns) ON what FROM user

The user part must match the user part of the original GRANT statement of the user whose permissions are to be revoked. Privileges does not need to be matched. you can use the GRANT statement to GRANT permissions, and then use the REVOKE statement to cancel some of them. The REVOKE statement only deletes permissions and does not delete users. The user's items are still in the user table, even if you cancel all permissions of the user. This means that the user can still connect to the server. To DELETE the entire user, you must use the DELETE statement to directly DELETE the user's records from the user table:

The DELETE statement deletes the user's items. the FLUSH statement tells the server to reload the authorization table (these tables are automatically reloaded when the GRANT or REVOKE statement is used instead of directly modifying the authorization table ).

A difficult permission problem

The following is a case that occurs repeatedly in the MySQL email list: a new MySQL administrator adds an item to a user and uses the host name section, this part is specified in a mode. For example:

GRANT ALL ON samp_db.* TO fred@%.snake.net IDENTIFIEDBY "cocoa"

The intention here is to allow user fred to connect from all hosts in the snake.net domain and have all permissions on the samp_db database. In fact, fred can connect from those hosts (except the server host itself ). When fred tries to connect from the server host, the attempt fails with the message "access denied. This is true even if the user specifies the correct password.

This also happens if the authorization table contains the default items installed by the mysql_install_db installation script. The reason is that when the server verifies an attempt to connect to fred, an anonymous user item (anonymous-user entry) takes precedence over the fred item. The anonymous user item requires that the user do not need a password to connect and a password mismatch occurs.

Currently, it is enough to delete anonymous user items from the user table. we cannot use REVOKE because this command only deletes permissions. To completely get rid of these anonymous items, perform the following operations:

% mysql-uroot mysql mysql> DELETE FROM user where User=""; mysql>FLUSH PRIVILEGES;

Now, fred is successfully trying to connect from the local host.