MySQL programming on linux (V): MySQL permission management and mysql permission management

MySQL programming on linux (V): MySQL permission management and mysql permission management
[Copyright statement: respect originality. For reprinted content, Please retain the Source: blog.csdn.net/shallnet. this document is only intended for study and exchange purposes. Do not use it for commercial purposes]
Mysql provides a complete security/permission management system. The following describes the principle and usage of permissions. After the mysql database is installed and started, you can see a mysql database in the current database list:

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || test               |+--------------------+

This database is used by MySQL to store all authorization information. It consists of several data tables. The specific data tables are as follows:

mysql> use mysqlDatabase changedmysql> show tables;+---------------------------+| Tables_in_mysql           |+---------------------------+| columns_priv              || db                        || event                     || func                      || general_log               || help_category             || help_keyword              || help_relation             || help_topic                || host                      || ndb_binlog_index          || plugin                    || proc                      || procs_priv                || servers                   || slow_log                  || tables_priv               || time_zone                 || time_zone_leap_second     || time_zone_name            || time_zone_transition      || time_zone_transition_type || user                      |+---------------------------+23 rows in set (0.00 sec)

In these data tables, five tables controlled by the county are involved, namely columns_priv (set the access permission for a single column in a table), db (Set access permissions for all tables in the given database), host (Set access permissions for the access IP address), and tables_priv (Set permissions for the user's stored procedure) and user (the user used to manage MySQL. In MySQL, two stages are used to access and control permissions. The two stages are: 1. when a user initiates a connection, the MySQL server first checks the user table by using a combination of user names, passwords, and hosts to determine whether the user is an authorized user. If not, the user is rejected directly. 2. If the connection is successful, MySQL checks db, host, tables_priv, and columns_priv in sequence to determine whether the access permission exists.
In MySQL, try not to use superuser logon, because this can easily bring security risks. The correct method is to set a Super User and set several common users at the same time, in this way, data security management can be implemented hierarchically and hierarchically. Newly Added users can use the cerate user command, grand authorization, or directly adding user records to user tables.

Mysql> create user allen identified by 'allen '; Query OK, 0 rows affected (0.39 sec) <span style = "font-family: ;"> </span>

View all current user permission tables:

mysql> select user,host,super_priv from user;+------+-----------------------+------------+| user | host                  | super_priv |+------+-----------------------+------------+| root | localhost             | Y          || root | localhost.localdomain | Y          || root | 127.0.0.1             | Y          ||      | localhost             | N          ||      | localhost.localdomain | N          || allen | %                     | N          |+------+-----------------------+------------+6 rows in set (0.00 sec)mysql> 

You can use the drop statement or the revoke statement or the delete statement to delete user records in the user table.

mysql> drop user allen;Query OK, 0 rows affected (0.00 sec)mysql> select user,host,super_priv from user;+------+-----------------------+------------+| user | host                  | super_priv |+------+-----------------------+------------+| root | localhost             | Y          || root | localhost.localdomain | Y          || root | 127.0.0.1             | Y          ||      | localhost             | N          ||      | localhost.localdomain | N          |+------+-----------------------+------------+5 rows in set (0.00 sec)

Use the grant statement to grant permissions to users in the format of grant priv_set on dbname to username. priv_set indicates the permission set, dbname indicates the database object, and username indicates the user.
Now let's take a look at the user created earlier:

[root@localhost ~]# mysql -u allen -p -h 172.27.35.8Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.......mysql>mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || db_users           || mysql              || test               |+--------------------+4 rows in set (0.00 sec)mysql> usse db_users;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'usse db_users' at line 1

It is found that user allen cannot use the db_users database. After logging out, use the root user to grant all permissions to allen.

mysql> grant all privileges on *.* to allen@localhost; 

After logging on to allen again:

[root@localhost ~]# mysql -u allen -p -h 172.27.35.8 Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.......mysql> use db_users;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 

The database can be used. In fact, data management cannot be performed for newly created users without authorization. In practical applications, grant can be used to create a user or manage permissions of multiple users at multiple levels: global, database, data table, and field. The following describes how to use grant at these four levels. 1. you can assign all operation permissions to a new user. These permissions are stored in mysql. grant all privileges on *. * to username @ '%' This statement authorizes the username user to manage the database server on any host. Although username has all management permissions, it does not assign any permissions to it. Therefore, it cannot assign any permissions to new users. For example, allen:

[root@localhost ~]# mysql -u allen -p -h 172.27.35.8Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.......mysql> create user allen_test1 identified by 'allen_test';Query OK, 0 rows affected (0.00 sec)mysql> grant select on *.* to allen_test1@localhost;ERROR 1045 (28000): Access denied for user 'allen'@'%' (using password: YES)mysql> 

If you want allen to obtain the operation permission of grant, write as follows: Grant all privileges on *. * to allen @ '%' with grant option;
As follows:

[root@localhost ~]# mysql -u root -pEnter password: ......mysql> grant all privileges on *.* to allen@'%' with grant option;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> quit[root@localhost ~]# mysql -u allen -p -h 172.27.35.8Enter password: ......mysql> grant select on *.* to allen_test1@localhost;Query OK, 0 rows affected (0.00 sec)

It is found that allen has successfully authorized allen_test.
2. Database-level permissions are the operation permissions of all targets in a given database. These permissions are stored in mysql. db and mysql. host. Its syntax is:

[root@localhost ~]# mysql -u root -p Enter password: ......mysql> grant all privileges on db_users.* to allen_test2@'%'  identified by 'allen'; Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> quitBye

Then use the allen_test2 user to log on:

[root@localhost ~]# mysql -u allen_test2 -p -h 192.168.65.30Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.......mysql> use db_users;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table tb_test(name varchar(32), sex bool);Query OK, 0 rows affected (0.06 sec)mysql> insert into tb_test values ('allen', 1);Query OK, 1 row affected (0.00 sec)mysql> create database db_test;ERROR 1044 (42000): Access denied for user 'allen_test2'@'%' to database 'db_test'mysql> 

It can be seen that db_users database operations can be performed, but an error occurs when a new database is created. This indicates that the database-level permissions have taken effect.

3. data Table-level permission ranges are the operation permissions of all the targets in a given data table. These permissions are stored in mysql. in tables_priv, the permissions of a data table include select, insert, delete, and update. Create a select-only permission for a user:

[root@localhost ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.mysql> grant select on db_users.* to allen_test3@'%' identified by 'allen';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> quitBye

Use user allen_test3 to log on:

[root@localhost ~]# mysql -u allen_test3 -p -h 192.168.65.30Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.mysql> select * from tb_test;+-------+------+| name  | sex  |+-------+------+| allen |    1 |+-------+------+1 row in set (0.00 sec)mysql> insert into tb_test values ('Lily', 0);ERROR 1142 (42000): INSERT command denied to user 'allen_test3'@'192.168.65.30' for table 'tb_test'mysql> 

It can be seen that the query operation is acceptable, but an error occurred while executing the insert operation.
4. The field level is used to manage the entire line of the user at the field level. It sets that the user has only certain operation permissions for several fields. The field permission information is stored in the mysql. columns_priv table. In the following example, a new user is granted the permission to view and update the sex field of the tb_test table in the db_users database.

[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.mysql> grant select,update(sex) on db_users.tb_test to allen_test4@'%' identified by 'allen';Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)mysql> quitBye

Allen_test4: whether the logon verification permission is granted successfully:

[root@localhost ~]# mysql -u allen_test4 -p -h 192.168.65.26Enter password: ......mysql> use db_users;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from tb_test;+-------+------+| name  | sex  |+-------+------+| allen |    1 |+-------+------+1 row in set (0.00 sec)mysql> update tb_test set sex=0 where name='allen';Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from tb_test;+-------+------+| name  | sex  |+-------+------+| allen |    0 |+-------+------+1 row in set (0.00 sec)mysql> update tb_test set name='allen_new';ERROR 1143 (42000): UPDATE command denied to user 'allen_test4'@'192.168.65.26' for column 'name' in table 'tb_test'mysql>

You can see that the select permission is correct, or you can update the sex field. However, an error is reported when the name field is updated because no permission is granted to the field.
You can use show grants to view the permissions you have obtained, view your operation permissions, use the show grants command to view your permissions, and use show grants for username to view the permissions of your username. The following example shows the operation permissions for viewing the root user and allen_test4:

[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.......mysql> show grants;+----------------------------------------------------------------------------------------------------------------------------------------+| Grants for root@localhost                                                                                                              |+----------------------------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |+----------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> show grants for allen_test4;+------------------------------------------------------------------------------------------------------------+| Grants for allen_test4@%                                                                                   |+------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'allen_test4'@'%' IDENTIFIED BY PASSWORD '*C94FD2FCBF408CBBFAAB9C07FF4221D265AFB18F' || GRANT SELECT, UPDATE (sex) ON `db_users`.`tb_test` TO 'allen_test4'@'%'                                    |+------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

In contrast to grant, revoke is used to revoke or revoke permissions.
1. Revoke all permissions,

mysql> show  grants for allen_test2;+------------------------------------------------------------------------------------------------------------+| Grants for allen_test2@%                                                                                   |+------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'allen_test2'@'%' IDENTIFIED BY PASSWORD '*C94FD2FCBF408CBBFAAB9C07FF4221D265AFB18F' || GRANT ALL PRIVILEGES ON `db_users`.* TO 'allen_test2'@'%'                                                  |+------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> revoke all privileges, grant option from allen_test2;Query OK, 0 rows affected (0.00 sec)mysql> show  grants for allen_test2;+------------------------------------------------------------------------------------------------------------+| Grants for allen_test2@%                                                                                   |+------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'allen_test2'@'%' IDENTIFIED BY PASSWORD '*C94FD2FCBF408CBBFAAB9C07FF4221D265AFB18F' |+------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)mysql> 

2. revoke a table-level permission. For example, the permission of allen_test3 is as follows:

mysql> show  grants for allen_test3;+------------------------------------------------------------------------------------------------------------+| Grants for allen_test3@%                                                                                   |+------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'allen_test3'@'%' IDENTIFIED BY PASSWORD '*C94FD2FCBF408CBBFAAB9C07FF4221D265AFB18F' || GRANT SELECT ON `db_users`.* TO 'allen_test3'@'%'                                                          |+------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> 

The permission after the execution is revoked is as follows:

mysql> revoke select on db_users.* from allen_test3;Query OK, 0 rows affected (0.00 sec)mysql> show  grants for allen_test3;+------------------------------------------------------------------------------------------------------------+| Grants for allen_test3@%                                                                                   |+------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'allen_test3'@'%' IDENTIFIED BY PASSWORD '*C94FD2FCBF408CBBFAAB9C07FF4221D265AFB18F' |+------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)mysql> 

3. revoke a field permission. For example, the allen_test4 permission is as follows:

mysql> show  grants for allen_test4;+------------------------------------------------------------------------------------------------------------+| Grants for allen_test4@%                                                                                   |+------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'allen_test4'@'%' IDENTIFIED BY PASSWORD '*C94FD2FCBF408CBBFAAB9C07FF4221D265AFB18F' || GRANT SELECT, UPDATE (sex) ON `db_users`.`tb_test` TO 'allen_test4'@'%'                                    |+------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

It can be seen that the user allen_test4 has the select and update field sex permissions. Now, the update sex field is revoked as follows:

mysql> revoke update(sex) on db_users.tb_test from allen_test4;Query OK, 0 rows affected (0.00 sec)mysql> show  grants for allen_test4;+------------------------------------------------------------------------------------------------------------+| Grants for allen_test4@%                                                                                   |+------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'allen_test4'@'%' IDENTIFIED BY PASSWORD '*C94FD2FCBF408CBBFAAB9C07FF4221D265AFB18F' || GRANT SELECT ON `db_users`.`tb_test` TO 'allen_test4'@'%'                                                  |+------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> 

Finally, the SQL statement for viewing all users is attached:

mysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select user from user;+-------------+| user        |+-------------+| allen_test2 || allen_test3 || allen_test4 || root        ||             || root        ||             || root        |+-------------+

This is the end of permission management.