加入收藏 | 设为首页 | 会员中心 | 我要投稿 应用网_丽江站长网 （http://www.0888zz.com/）- 科技、建站、数据工具、云上网络、机器学习!
当前位置： 首页 > 站长学院 > MySql教程 > 正文

如何了解MySQL管理基础中的安全、访问控制和权限

发布时间：2021-12-16 20:21:38 所属栏目：MySql教程 来源：互联网
导读：如何理解MySQL管理基础中的安全、访问控制和权限，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。 MySQL权限系统确保所有的用户只执行允许的操作。当连接MySQL服务器时，
如何理解MySQL管理基础中的安全、访问控制和权限，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
 
MySQL权限系统确保所有的用户只执行允许的操作。当连接MySQL服务器时，用户身份由其所在的主机和使用的用户名来决定。当用户在连接后发出请求时，系统根据其身份和要进行的操作来授予权限。
 
1.MySQL授权表
 
 1)user表
 
mysql> select host, user, password from mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *25B0A985780CE19647D8E41C2B50E7FB300EA6E8 |
| %         | root | *25B0A985780CE19647D8E41C2B50E7FB300EA6E8 |
+-----------+------+-------------------------------------------+
2 rows in set (0.00 sec)
 
注：“%”用作通配符，这里表示允许从任何主机以root用户访问。除db表外的其它授权表中，空host值与“%”相同。
 
[@more@]MySQL授权表一般都包含两类字段：范围字段和权限字段。上面看到的user表的host、user、password属于范围字段，此外还有一些名称以“_priv”结尾的权限字段，用于指定用户拥有的权限。这里赋予用户的权限适用于系统的每个数据库，因此一般将它们都设置为N，而使用host和db表进行更为精细的权限设置。其余字段则与SSL加密以及用户资源限制有关。
 
 2)db和host表
 
   db表有三个范围字段host、db和user，指定该条记录是针对某用户从某主机连接某数据库而言的；其余的字段为权限字段。
   host有两个范围字段host、db，其余的字段为权限字段。如果db表中记录的host字段留空，MySQL服务器在验证用户权限时会从host表中获取相应的主机名。host表不受GRANT和REVOKE语句的影响。大多数MySQL安装根本不需要使用该表。
 
 3)tables_priv和columns_priv表
 
   这两个表可以分别限制对数据库中具体的表和表中具体的列的访问。
 
   当MySQL需要决定是否允许用户执行某一数据库操作时，首先要查看user表中该用户是否具有足够的权限，如果没有，再查看db和host表。一些管理操作，如RELOAD、PROCESS涉及整个系统，只有user表中有相应的列。
   可以手工修改授权表的内容来进行权限设置（但tables_priv和columns_priv表不建议这样做），修改后需要使用FLUSH PRIVILEGES语句、mysqladmin flush-privileges或mysqladmin reload命令重新装载授权表。
 
2.授予和回收权限
 
   通常使用GRANT和REVOKE命令授予和回收用户的权限，下面通过一些例子来演示它们的用法。
   首先，创建一个从本机连接MySQL服务器的ggyy用户：
 
mysql> create user ggyy@localhost identified by 'password';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select host, user from mysql.user;
+-----------+------+
| host      | user |
+-----------+------+
| %         | root |
| localhost | ggyy |
| localhost | root |
+-----------+------+
3 rows in set (0.00 sec)
 
   现在可以使用ggyy用户连接服务器，但还没有访问数据库的权限。
 
C:>mysql -u ggyy -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 18
Server version: 5.1.34-community MySQL Community Server (GPL)
 
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
 
   下面授予ggyy用户在ggyy数据库上的查询权限：
 
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
 
mysql> grant select on ggyy.* to ggyy@localhost;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show grants for ggyy@localhost;
+-------------------------------------------------------------------------------------------------------------+
| Grants for ggyy@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ggyy'@'localhost' IDENTIFIED BY PASSWORD '*484FFAA42C12F40931C794D33A11B7F075B91467' |
| GRANT SELECT ON `ggyy`.* TO 'ggyy'@'localhost'                                                              |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> select host, db, user, select_priv, insert_priv, update_priv, delete_priv from mysql.db;
+-----------+------+------+-------------+-------------+-------------+-------------+
| host      | db   | user | select_priv | insert_priv | update_priv | delete_priv |
+-----------+------+------+-------------+-------------+-------------+-------------+
| localhost | ggyy | ggyy | Y           | N           | N           | N           |
+-----------+------+------+-------------+-------------+-------------+-------------+
1 row in set (0.00 sec)
 
mysql> select * from mysql.tables_priv;
Empty set (0.00 sec)
 
 
2 rows in set (0.00 sec)
 
mysql> select host, user from mysql.user;
+---------------+------+
| host          | user |
+---------------+------+
| %             | root |
| 192.168.7.100 | ggyy |
| localhost     | ggyy |
| localhost     | root |
+---------------+------+
4 rows in set (0.00 sec)
 
mysql> select host, db, user, select_priv, insert_priv, update_priv, delete_priv from mysql.db where host = '192.168.7.1
00';
Empty set (0.00 sec)
 
mysql> select * from mysql.tables_priv where host = '192.168.7.100';
+---------------+------+------+------------+----------------+---------------------+------------+-------------+
| Host          | Db   | User | Table_name | Grantor        | Timestamp           | Table_priv | Column_priv |
+---------------+------+------+------------+----------------+---------------------+------------+-------------+
| 192.168.7.100 | ggyy | ggyy | users1     | root@localhost | 2010-01-17 14:02:42 | Select     |             |
+---------------+------+------+------------+----------------+---------------------+------------+-------------+
1 row in set (0.00 sec)
 
 
   可以看到，由于授予的是表的权限，host表中没有相应记录，需要到tables_priv表中查看。
   在远程主机上访问数据库：
 
C:>mysql -h 192.168.7.101 -u ggyy -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 20
Server version: 5.1.34-community MySQL Community Server (GPL)
 
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 
mysql> select current_user();
+--------------------+
| current_user()     |
+--------------------+
| ggyy@192.168.7.100 |
+--------------------+
1 row in set (0.00 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ggyy               |
+--------------------+
2 rows in set (0.00 sec)
 
mysql> use ggyy
Database changed
mysql> show tables;
+----------------+
| Tables_in_ggyy |
+----------------+
| users1         |
+----------------+
1 row in set (0.00 sec)
 
mysql> select * from users1 limit 1;
+------+----------+------+
| uid  | uname    | gid  |
+------+----------+------+
|  202 | fengsong |  200 |
+------+----------+------+
1 row in set (0.00 sec)
 
 
   要回收权限使用REVOKE命令，例如：
 
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
 
mysql> revoke select on ggyy.* from ggyy@localhost;
Query OK, 0 rows affected (0.00 sec)
 
mysql> revoke select on ggyy.users1 from ggyy@192.168.7.100;
Query OK, 0 rows affected (0.00 sec)
 
 
   也可以采用db和host表相结合的方式管理数据库的权限，例如：
 
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
 
mysql> insert into mysql.db (db, user, select_priv, delete_priv) values ('ggyy', 'ggyy', 'Y', 'Y');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into mysql.host (db, host, select_priv, insert_priv) values ('ggyy', 'localhost', 'Y', 'Y');
Query OK, 1 row affected (0.00 sec)
 
mysql> select host, db, user, select_priv, insert_priv, delete_priv from mysql.db where db = 'ggyy';
+------+------+------+-------------+-------------+-------------+
| host | db   | user | select_priv | insert_priv | delete_priv |
+------+------+------+-------------+-------------+-------------+
|      | ggyy | ggyy | Y           | N           | Y           |
+------+------+------+-------------+-------------+-------------+
1 row in set (0.00 sec)
 
mysql> select host, db, select_priv, insert_priv, delete_priv from mysql.host where db = 'ggyy';
+-----------+------+-------------+-------------+-------------+
| host      | db   | select_priv | insert_priv | delete_priv |
+-----------+------+-------------+-------------+-------------+
| localhost | ggyy | Y           | Y           | N           |
+-----------+------+-------------+-------------+-------------+
1 row in set (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
 
   使用ggyy用户在本机上访问数据库：
 
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| ggyy@localhost |
+----------------+
1 row in set (0.00 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ggyy               |
+--------------------+
2 rows in set (0.00 sec)
 
mysql> use ggyy
Database changed
mysql> show tables;
+----------------+
| Tables_in_ggyy |
+----------------+
| blob_text_test |
| char_test      |
| contact        |
| date_time_test |
| float_test     |
| groups1        |
| groups2        |
| int_test       |
| members        |
| members_temp   |
| processes1     |
| test_crttb     |
| test_crttb2    |
| test_crttb3    |
| test_crttb4    |
| test_crttb5    |
| ts_dt_test     |
| users1         |
+----------------+
18 rows in set (0.00 sec)
 
mysql> select * from groups1 limit 1;
+------+-------+
| gid  | gname |
+------+-------+
|    0 | root  |
+------+-------+
1 row in set (0.00 sec)
 
mysql> insert into groups1 values (255, 'test');
ERROR 1142 (42000): INSERT command denied to user 'ggyy'@'localhost' for table 'groups1'
mysql> delete from groups1 where gname = 'root';
ERROR 1142 (42000): DELETE command denied to user 'ggyy'@'localhost' for table 'groups1'
 
   db表中的权限列相当于“总开关”，host表中的权限列相当于“分开关”，只有两个“开关”都打开了，用户才拥有相应操作的权限。
 
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
 
mysql> update mysql.db set insert_priv = 'Y' where db = 'ggyy';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> update mysql.host set delete_priv = 'Y' where db = 'ggyy';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select host, db, user, select_priv, insert_priv, delete_priv from mysql.db where db = 'ggyy';
+------+------+------+-------------+-------------+-------------+
| host | db   | user | select_priv | insert_priv | delete_priv |
+------+------+------+-------------+-------------+-------------+
|      | ggyy | ggyy | Y           | Y           | Y           |
+------+------+------+-------------+-------------+-------------+
1 row in set (0.00 sec)
 
mysql> select host, db, select_priv, insert_priv, delete_priv from mysql.host where db = 'ggyy';
+-----------+------+-------------+-------------+-------------+
| host      | db   | select_priv | insert_priv | delete_priv |
+-----------+------+-------------+-------------+-------------+
| localhost | ggyy | Y           | Y           | Y           |
+-----------+------+-------------+-------------+-------------+
1 row in set (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
   再次使用ggyy用户尝试插入和删除操作：
 
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| ggyy@localhost |
+----------------+
1 row in set (0.00 sec)
 
mysql> insert into groups1 values (255, 'test');
Query OK, 1 row affected (0.00 sec)
 
mysql> delete from groups1 where gname = 'root';
Query OK, 1 rows affected (0.00 sec)
 
   这种由db表和host表一起确定的权限，使用SHOW GRANTS命令看不到，也无法使用REVOKE命令回收。
 
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
 
mysql> show grants for ggyy@localhost;
+-------------------------------------------------------------------------------------------------------------+
| Grants for ggyy@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ggyy'@'localhost' IDENTIFIED BY PASSWORD '*484FFAA42C12F40931C794D33A11B7F075B91467' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> revoke select on ggyy.* from ggyy@localhost;
ERROR 1141 (42000): There is no such grant defined for user 'ggyy' on host 'localhost'
 
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。

（编辑：应用网_丽江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
      站长推荐
      热点阅读

        【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

        建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的优质浏览效果

        Copygight © 2013-2023 http://www.0888zz.com/ All Rights Reserved. 应用网_丽江站长网

        ICP备案：浙ICP备12044117号 浙公网安备 33038102330468号