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

68次阅读
没有评论

共计 11835 个字符,预计需要花费 30 分钟才能阅读完成。

如何理解 MySQL 管理基础中的安全、访问控制和权限,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面丸趣 TV 小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。

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)

    使用 ggyy 用户查看 ggyy 数据库中的内容:

mysql select current_user();
+—————-+
| current_user() |
+—————-+
| ggyy@localhost |
+—————-+
1 row 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 processes1 limit 1;
+——+——-+——+
| pid  | pname | ppid |
+——+——-+——+
|    1 | init  |    0 |
+——+——-+——+
1 row in set (0.00 sec)

    接下来,再创建一个从远程主机 192.168.7.100 连接 MySQL 服务器的 ggyy 用户,授予查询 ggyy 数据库中 user1 表的权限。使用 GRANT 命令可以在授权时创建被授权的用户:

mysql select current_user();
+—————-+
| current_user() |
+—————-+
| root@localhost |
+—————-+
1 row in set (0.00 sec)

mysql grant select on ggyy.users1 to ggyy@192.168.7.100 identified by ggyy
Query OK, 0 rows affected (0.00 sec)

mysql show grants for ggyy@192.168.7.100;
+—————————————————————————————————————–+
| Grants for ggyy@192.168.7.100                                                                                   |
+—————————————————————————————————————–+
| GRANT USAGE ON *.* TO ggyy @ 192.168.7.100 IDENTIFIED BY PASSWORD *484FFAA42C12F40931C794D33A11B7F075B91467 |
| GRANT SELECT ON `ggyy`.`users1` TO ggyy @ 192.168.7.100                                                       |
+—————————————————————————————————————–+
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

看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注丸趣 TV 行业资讯频道,感谢您对丸趣 TV 的支持。

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-19发表,共计11835字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)