MySQL基本授权grant操作

本文介绍MySQL权限的基本操作。

授权

MySQL用户包括user和host两部分。

user与host是一起出现的，即权限指的是某个用户在某个主机或某些主机上的权限。

首先，创建用户：

mysql> CREATE USER 'root'@'%' IDENTIFIED  by 'mysql123456';

接着，授权权限：

mysql> GRANT ALL on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

具体权限，是指某个DB下某个table的权限。
这里授权'root'@'%' 操作所有DB所有tabe的权限。

查看授权

查看'root'@'%'的授权：

mysql> show grants for 'root'@'%';
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

或者

mysql> select password('mysql123456');
+-------------------------------------------+
| password('mysql123456')                   |
+-------------------------------------------+
| *CAAA3AD851D5FFA9E5BDB96CA06F73633A3BA831 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*CAAA3AD851D5FFA9E5BDB96CA06F73633A3BA831';

实际上，创建用户后，用户权限保存到用户权限表mysql.user中，在这个表中，密码是经过password()函数转化后的格式存储的。

查看授权用户

mysql> select * from mysql.user;

查看指定用户

mysql> select * from mysql.user where user='root'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
... ...

         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *xxx
      password_expired: N
 password_last_changed: 2018-10-10 14:42:12
     password_lifetime: NULL
        account_locked: N
*************************** 2. row ***************************
                  Host: %
                  User: root
... ...
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: xxx
      password_expired: N
 password_last_changed: 2018-10-10 14:47:32
     password_lifetime: NULL
        account_locked: N
2 rows in set (0.01 sec)

撤销权限

撤销某个用户的授权：

REVOKE ALL on orchestrator.* FROM 'orchestrator_server'@'10.23.211.199';

删除用户：

delete from mysql.user where user='orchestrator_server' and host='10.23.211.199' ;
flush privileges ;