如何理解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) 使用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' 看完上述内容是否对您有帮助呢？ （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
