FAQs about MySQL naming

This article uses the Q & A function to answer the frequently asked questions during mysql naming.

Who can 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 to separate the user name and host name from each other ).

Grant all on samp_db.president TO "my friend" @ "boa.snke.net"

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, such as FILE, PROCESS, RELOAD, and SHUTDOWN, are administrative permissions and can only be authorized with a "ON *. *" Global permission identifier. 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 read-only user ro_user who strictly restricts access). Only all tables in the samp_db database can be accessed, but only tables can be 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

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 GRANT statement: grant all on tmp. * TO "" @ mars.net.

After you have done this, you can create an anonymous user by referencing the table in tmp in the form of tmp. tbl_name in the user-specified "", and any user matches the blank user name ).

Should users be allowed to manage permissions?

Step 1:

You can allow a database owner to control database access by granting all database owner permissions. During authorization, specify with grant option. For example, if you want alicia to connect to any host in the big.corp.com domain and have the Administrator permission for all tables in the sales database, you can use the following GRANT statement:

Grant all on sales. * TO alicia @ % .big.corp.com

Indetified by "applejuice" WITH GRANT OPTION

In effect, the with grant option clause allows you to GRANT the Access Authorization right to another user. Note that two users with the GRANT permission can authorize each other. If you only grant select permission to the first user and grant select permission to the other user, the second user can be the first user more powerful ".

Step 2:

REVOKE permissions and delete users. to cancel a user's permissions, use the REVOKE statement. The syntax of REVOKE is very similar TO the GRANT statement, except that it is replaced by from without the indetifed by and with grant option clauses:

REVOKE privileges (columns) ON what FROM user

The user part must match the user part of the user you want to revoke permission from the original GRANT statement. Privileges does not need to be matched. You can use the GRANT statement to GRANT permissions, and then use the REVOKE statement to REVOKE only some permissions.

The REVOKE statement only deletes permissions, but does not delete users. Even if you revoke all permissions, the user records in the user table are retained, which means that the user can still connect to the server. To completely DELETE a user, you must use a DELETE statement to explicitly DELETE user records from the user table:

% Mysql-u root mysqlmysql> delete from user

-> WHERE User = "user_name" and Host = "host_name ";

Mysql> flush privileges;

The DELETE statement deletes user records, while the FLUSH statement tells the server to overload the authorization table. When you use the GRANT and REVOKE statements, the table is automatically reloaded, but you do not modify the authorization table directly .)

I will summarize this for you here. I will continue to summarize more experiences and hope to help you in the future.