MySQL database account authorization management (2) _ MySQL

MySQL database account authorization related management (2) the table above shows that the permissions specified in the first group apply to databases, tables, and columns, and the second group of 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"