MySQL管理与优化(21):MySQL权限与安全

原创
2014/10/13 22:28
阅读数 298

MySQL权限与安全

MySQL权限管理:

权限系统的工作原理:

MySQL权限系统通过下面两个阶段进行认证:

  • 对连接的用户进行身份认证,合法的用户通过认证,不合法的用户拒绝连接。

  • 对用户通过身份认证用户赋予相应的权限,用户可以在这些权限范围内对数据库做相应的操作。

MySQL身份认证是通过IP地址和用户名联合确认的,如root@localhost表示root只能在本地登录mysql。

权限表的存取:

  • MySQL中权限表的定义主要是user, host, db表决定:

       

  • 用户进行连接时,权限表的存取过程有以下两个阶段:

       

帐号管理:

  • 创建帐号:

-- 创建用户david在localhost上,拥有所有表权限
mysql> grant all privileges on *.* to david@localhost;
Query OK, 0 rows affected (0.32 sec)

mysql> select * from user where user='david' and host='localhost'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: david
              Password:
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N  -- 这里没有授权权限
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string:
      password_expired: N
1 row in set (0.00 sec)

在我们授予其授权权限后:

mysql> grant all privileges on *.* to david@localhost with grant option;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from user where user='david' and host='localhost'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: david
              Password:
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string:
      password_expired: N
1 row in set (0.00 sec)

为其设置密码:

mysql> grant all privileges on *.* to david@localhost identified by '123456' with grant option;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user where user='david' and host='localhost'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: david
              Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string:
      password_expired: N
1 row in set (0.00 sec)

给用户赋予所有ip连接的权限:

-- 给xiaoming用户授予test数据库表的CRUD操作权限
mysql> grant select, insert, update, delete on test.* to 'xiaoming'@'%' identified by '123456';
Query OK, 0 rows affected (0.07 sec)

mysql> select * from user where user='xiaoming' and host='%'\G
*************************** 1. row ***************************
                  Host: %
                  User: xiaoming
              Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string:
      password_expired: N
1 row in set (0.00 sec)

mysql> select * from db where user='xiaoming' and host='%'\G
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User: xiaoming
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
1 row in set (0.02 sec)

有关 host的设置:

一些host和user组合的例子:

NOTE:


查看和更改帐号权限:

  • 查看权限:

-- 查看user@host上的权限, 默认host为%
mysql> show grants for xiaoming@'%';
+---------------------------------------------------------------------------------------------------------+
| Grants for xiaoming@%                                                                                   |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaoming'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'xiaoming'@'%'                                      |
+---------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

-- MySQL5.0可以查看schema
mysql> use information_schema;
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 SCHEMA_PRIVILEGES where grantee="'xiaoming'@'%'";
+----------------+---------------+--------------+----------------+--------------+
| GRANTEE        | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------+---------------+--------------+----------------+--------------+
| 'xiaoming'@'%' | def           | test         | SELECT         | NO           |
| 'xiaoming'@'%' | def           | test         | INSERT         | NO           |
| 'xiaoming'@'%' | def           | test         | UPDATE         | NO           |
| 'xiaoming'@'%' | def           | test         | DELETE         | NO           |
+----------------+---------------+--------------+----------------+--------------+
4 rows in set (0.05 sec)
  • 更改权限
-- 回收权限
mysql> show grants for xiaoming@'%';
+---------------------------------------------------------------------------------------------------------+
| Grants for xiaoming@%                                                                                   |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaoming'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'xiaoming'@'%'                                      |
+---------------------------------------------------------------------------------------------------------+
2 rows in set (0.02 sec)

mysql> revoke DELETE on test.* from 'xiaoming'@'%';
Query OK, 0 rows affected (0.33 sec)

mysql> show grants for xiaoming@'%';
+---------------------------------------------------------------------------------------------------------+
| Grants for xiaoming@%                                                                                   |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaoming'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'xiaoming'@'%'                                              |
+---------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
  • 修改密码
-- 通过mysqladmin修改
mysqladmin -u username -h host_name -p "new_passwd"

-- SET PASSWORD
SET PASSWORD FOR 'xiaoming'@'%' = PASSWORD('new_password')

-- 修改自己的密码
SET PASSWORD = PASSWORD('new_password')

-- 直接更改数据库
INSERT INTO user (Host, User, Password) VALUES ('%', 'daming', PASSWORD('123456'))
UPDATE user SET password = PASSWORD('new_password') WHERE Host = '%' AND user = 'daming'
NOTE: 更改密码记得加上 PASSWORD(mysqladmin, GRANT不用加,会自动加上)。
  • 删除用户
DROP user [, user1, ...]

MySQL安全问题:

操作系统相关的安全问题:

  • 严格控制操作系统帐号和权限

       

  • 尽量避免以root权限运行MySQL,防止任何有FILE权限的用户都可以在任意目录写出文件。
  • 防止DNS欺骗,防止域名对应的IP被修改。

数据库相关的安全问题:

  • 删除匿名帐号
-- Mysql默认会有一个匿名用户,其对test数据库有所有权限,应该将其删除。
mysql> select * from db\G
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
3 rows in set (0.02 sec)

-- 删除之
drop user ''@'%';
  • 给root帐号设置命令,MySQL安装完毕后, root默认指令为空。
  • 设置安全密码。
  • 只授予帐号必须的权限。
  • 除root外,任何用户不应有mysql库user表的存取权限。
  • 不要把FILE, PROCESSSUPER权限授予管理员以外的帐号: 

       

       FILE权限主要体现为:

       

       PROCESS权限即可以查询当前所有用户执行的查询的明文文本:

             

       SUPER权限能执行kill命令,终止其他用户进程。

  • LOAD DATA LOCAL带来的安全隐患: 

       

       我们可以通过--local-infile=0选项禁止所有LOAD DATA LOCAL命令。

  • 使用MERGE存储引擎潜藏的安全漏洞,其可能存在的隐患:

        

  • DROP TABLE命令并不收回以前的相关访问授权。
  • 使用SSL,通过--ssl选项可开启MySQL使用SSL进行安全传输,除了-ssl外,还需指定:

        

  • 如果可能,给所有用户加上访问IP限制。
  • REVOKE命令的漏洞,revoke并没有如所想将所有权限收回。

       

其他安全设置选项:

  • safe-user-create选项,使用户不能使用GRANT语句创建用户,除非用户有mysql.user的INSERT权限。
  • secure-auth选项,防止MySQL4.1之前的客户端连接服务器。
  • skip-grant-tables选项,是服务器不使用权限系统,给每个用户完全访问所有数据库的权力。
  • skip-network选项,禁止TCP/IP连接,所有数据库的连接必须经由命名管道或共享内存或UNIX套接字文件进行,适合应用和数据库在一台服务器上的情景。
  • skip-show-database选项,只允许有show databases权限的用户执行show databases语句。

不吝指正。

展开阅读全文
加载中

作者的其它热门文章

打赏
0
3 收藏
分享
打赏
0 评论
3 收藏
0
分享
返回顶部
顶部