深入浅出MySQL阅读笔记-MySQL权限与安全

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

1）对连接的用户进行身份认证，合法的用户通过认证，不合法的用户拒绝连接；

2）对通过认证的合法用户赋予相应的权限，用户可以在这些权限范围内对数据库做相应的操作；

权限表：数据库"mysql"中的user、host、db权限表

当用户进行连接时，权限表的存取过程有以下两个阶段。

1）先从user表中的host、user和password这3个字段中判断连接的IP、用户名和密码是否存在于表中，如果存在，则通过身份验证，否则拒绝连接。

2）如果通过身份验证，则按照以下权限表的顺序得到数据库权限：user-->db--> tables_priv-->columns_priv。这几个权限表，权限范围依次递减，全局权限覆盖局部权限。

mysql> grant select on *.* to z1@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where user='z1' and host='localhost'\G;
*************************** 1. row ***************************
                  Host: localhost
                  User: z1
              Password: 
           Select_priv: Y
           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: 
 authentication_string: NULL
1 row in set (0.00 sec)

ERROR: 
No query specified

View Code

user表中的每个权限都代表了对所有数据库都有的权限。

当用户通过权限认证，进行权限分配时，将按照user-->db-->tables_priv-->columns_priv的顺序进行权限分配，即先检查全局权限表user,如果user中对应权限为"Y",则此用户对所有的数据库的权限都为"Y",将不再检查db、tables_priv和columns_priv；如果为"N"，则到db表中检查此用户对应的具体数据库，并得到db中为"Y"的权限；如果tables_priv中相应权限为"N",则检查colunms_priv中此表对应的具体列，取得列中卫"Y"的权限。

关于用户登录的实验：

[root@vhost1 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.10.71 vhost1.us.oracle.com
192.168.10.72 vhost2.us.oracle.com

登陆MySQL有两种方式TCP/IP和socket

[root@vhost1 ~]# mysql -uroot -p --socket=/data/mysql5.6/mysql.sock
[root@vhost1 ~]# mysql -uroot -p -hlocalhost -P33061

首先要搞清楚这个 -h 参数是什么。
-h, --host=name Connect to host.
-h是要连的主机名，比如有一个MySQL数据库在us.oracle.com,连接数据库mysql -u root -h us.oracle.com，这和自己的机器没有关系，你的登录账号还是 root@yourPCname

TCP/IP的访问方式：

[root@vhost1 ~]# mysql -uroot -p -hlocalhost -P33061
mysql> \s

Current user: root@localhost

[root@vhost1 ~]# mysql -uroot -p -h127.0.0.1 -P33061

mysql> \s

Current user: root@localhost

[root@vhost1 ~]# mysql -uroot -p -h::1 -P33061

mysql> \s

Current user: root@localhost

user表中有三个host：localhost、127.0.0.1、::1，这三个都是一个意思，都是表示本地机。

[root@vhost1 ~]# mysql -uroot -p -hvhost1.us.oracle.com -P33061

mysql> \s

Current user: root@vhost1.us.oracle.com

[root@vhost1 ~]# mysql -uroot -p -h192.168.10.71 -P33061

mysql> \s
--------------

Current user: root@vhost1.us.oracle.com

[root@vhost1 ~]# mysql -uroot -p -h192.168.10.71 -P33061
mysql> \s

--------------

Current user: root@vhost1.us.oracle.com

使用socet连接数据库授权，并创建用户时，重新使用新用户登陆提示使用socket才可以进入MySQL

[root@vhost1 ~]# mysql -uroot -p --socket=/data/mysql5.6/mysql.sock
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'local' IDENTIFIED BY 'root' WITH GRANT OPTION;

mysql> flush privilges;

[root@vhost1 ~]# mysql -uroot -p -hlocal -P33061
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

[root@vhost1 ~]# mysql -uroot -p -hlocal -P33061 --socket=/data/mysql5.6/mysql.sock
mysql>

连接vhost2数据库服务器的MySQL实验

[root@vhost1 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.10.71 vhost1.us.oracle.com
192.168.10.72 vhost2.us.oracle.com

host文件中配置了域名 ip

[root@vhost1 ~]#  mysql -uroot -p -h192.168.10.72  -P33061
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.37, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:          16
Current database:
Current user:           root@vhost1.us.oracle.com
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.19-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             192.168.10.72 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               33061
Uptime:                 2 hours 35 min 14 sec

Threads: 3  Questions: 49  Slow queries: 0  Opens: 109  Flush tables: 1  Open tables: 102  Queries per second avg: 0.005
--------------

mysql>

通过IP连接

[root@vhost1 ~]#  mysql -uroot -p -hvhost2.us.oracle.com  -P33061
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.37, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:          17
Current database:
Current user:           root@vhost1.us.oracle.com
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.19-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             vhost2.us.oracle.com via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               33061
Uptime:                 2 hours 36 min 46 sec

Threads: 3  Questions: 54  Slow queries: 0  Opens: 109  Flush tables: 1  Open tables: 102  Queries per second avg: 0.005
--------------

mysql>

通过域名连接

vhost2的host文件及用户信息：

[root@vhost2 ~]# cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

[root@vhost2 ~]# mysql -uroot -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.19-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

可见：本地客户端连接远端MySQL数据库,需要配置host文件；通过远端MySQL数据库实例提供的'root'@'%'进行访问。

posted on 2017-08-28 20:44 HelonTian 阅读(215) 评论(0) 编辑收藏举报