搞定linux上MySQL编程（四）：mysql权限管理

        mysql中提供了比较完整的安全/权限管理系统，下面简单介绍权限的原理和使用。         mysql数据库安装完成启动之后，能看当前数据库列表可以看到一个mysql的数据库：

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

该数据库是MySQL用来存储所有授权信息，该数据库由若干张数据表组成，具体数据表如下：

mysql> use mysql
Database changed
mysql> 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)

在这些数据表中，涉及全县控制的有5张表，分别是columns_priv（为某一张表中单个列进行访问权限设定） 、db（对给定数据库的所有表设定访问权限）、host（对访问ip地址进行权限设定）、tables_priv（对用户的存储过程进行权限设定）、user（用于管理MySQL的用户）等。 在MySQL中采用两个阶段来进行权限的存取与控制，这两个阶段为： 1.当用户发起连接时，MySQL服务器先检查user表，检查方式是通过用户名、密码、主机的组合判断用户是否为授权用户，不是则直接拒绝。 2.若连接成功，对于提交的请求，MySQL将通过顺序检查db、host、tables_priv、columns_priv判断是否存在访问权限。
        在MySQL中尽量不要使用超级用户登录，因为这样很容易带来安全隐患，比较正确的方式是设置一个超级用户，同时设置几个普通用户，这样可以分层分级来实现数据的安全管理。         新增加用户可以使用cerate user命令、使用grand授权、或向用户表user中直接添加用户记录等方式来完成。

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

        查看当前所有用户权限表：

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> 

        删除用户可以使用drop语句完成，也可以使用revoke方式、或者使用delete语句删除user表中对于user记录来完成同样工作。

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)

使用grant语句为用户授予权限，其格式为： grant priv_set on dbname to username; 其中priv_set为权限集合，dbname是指数据库对象，username为用户。
现在来看看前面创建的用户：

[root@localhost ~]# mysql -u allen -p -h 172.27.35.8
Enter 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

发现用户allen无法使用db_users数据库，退出登陆后使用root用户给allen赋予所有权限。

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

再次使用allen登陆后：

[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 names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 

发现可以使用数据库了。         事实上对于新创建的用户，如果没有获得授权，是无法进行数据管理工作的。在实际应用中，grant可以执行用户创建，也可以对多用户在多个级别上进行权限管理，分别是，全局级、数据库级、数据表级以及字段级。下面分别从这4个层级对grant的使用进行介绍。 1.全局权限分配，可以为某个新建用户分配全部操作权限，这些权限存储在mysql.user表中， grant all privileges on *.* to username@ ‘%‘ 该语句能授权username用户在任意一台主机上对数据库服务器进行管理。虽然username拥有全部管理权限，但并没有为其自身分配再授予权限，所以不能为新创建的用户分配任何权限。以上面allen为例：

[root@localhost ~]# mysql -u allen -p -h 172.27.35.8
Enter 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> 

如果想让allen获得grant的操作权限，应该这样写： grant all privileges on *.* to allen@‘%‘ with grant option;
如下：

[root@localhost ~]# mysql -u root -p
Enter 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.8
Enter password: 
......
mysql> grant select on *.* to allen_test1@localhost;
Query OK, 0 rows affected (0.00 sec)

发现现在allen用户给allen_test授权成功。
2.数据库级权限范围是在给定的一个数据库中的所有目标的操作权限，这些权限会存储在mysql.db和mysql.host中。其语法为：

[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> quit
Bye

然后使用allen_test2用户登录：

[root@localhost ~]# mysql -u allen_test2 -p -h 192.168.65.30
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 names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 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> 

可以看到，如果执行db_users数据库内操作是可以的，但创建一个新的数据库就会出错。说明数据库级权限已经生效 。

3.数据表级权限范围是在给定的一个数据表中所有的目标的操作权限，这些权限会存储在mysql.tables_priv中，通常一个数据表所拥有的权限有select、insert、delete、update等。下面为一个用户创建一个只有select的权限：

[root@localhost ~]# mysql -uroot -p
Enter 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> quit
Bye

使用用户allen_test3用户登录：

[root@localhost ~]# mysql -u allen_test3 -p -h 192.168.65.30
Enter 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> 

可见执行查询操作是可以的，但是执行插入操作出错。
4.字段级是在字段一级对用户进行全线管理，设定用户只有若干字段的某些操作权限，字段的权限信息存储在mysql.columns_priv表中。下例对一个新用户赋予db_users数据库中tb_test表的sex字段查看和更新的权限。

[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> quit
Bye

allen_test4用户登录验证权限授予是否成功：

[root@localhost ~]# mysql -u allen_test4 -p -h 192.168.65.26
Enter password: 
......
mysql> use db_users;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 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: 0

mysql> 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>

可以看到select权限没有问题，也可以对sex字段进行更新操作。但是更新name字段报错，因为没有授予其权限。
使用show grants可以查看用户已经获得的权限，查看自己的操作权限使用show grants命令可以查看自身权限，使用show grants for username可以查看用户username的权限。下例为查看root用户自身和查看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)

和grant相反的操作时使用revoke操作，revoke作用是回收或者取消权限。
1.撤销全部权限，

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.撤销表级某类权限，例如用户allen_test3权限如下：

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> 

执行撤销后权限如下：

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.撤销某字段权限，例如allen_test4权限如下：

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)

可见用户allen_test4拥有select和update 字段sex的权限，现在撤销update的sex字段，如下：

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> 

最后附上查看所有用户的sql语句：

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user from user;
+-------------+
| user        |
+-------------+
| allen_test2 |
| allen_test3 |
| allen_test4 |
| root        |
|             |
| root        |
|             |
| root        |
+-------------+

权限管理就到此为止了。

搞定linux上MySQL编程（四）：mysql权限管理

标签：mysql   权限管理   grant