MySQL database account authorization management (3_MySQL

MySQL database account authorization related management (3. Generally, you want to authorize management permissions, because users with these permissions can affect your server operations.

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 ).

1.3 Should users be permitted to manage permissions?

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 ".

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 GRANT and REVOKE statements, the table is automatically reloaded, but you do not modify the authorization table directly.