FAQs about MySQL naming (2) _ MySQL

FAQ encountered during MySQL naming (2) permissions for all tables in the bitsCN.com p_db database; Article 2 create a user ro_user (read-only user) with strict access restrictions ), 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@localhostINDETIFIED 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 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 ).

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.comINDETIFIED 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->WHbitsCN.com

The above is a frequently asked question (2) _ MySQL during the naming process of MySQL. For more information, see The PHP Chinese website (www.php1.cn )!