• Post author:
  • Post category:mysql
  • Post comments:0评论
Like
Like Love Haha Wow Sad Angry

一、用户管理

1、查询当前登录用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

2、创建用户

mysql> create user cp@'10.0.0.%' identified by '000000';

 注:用户的格式为:用户名@'白名单'。
    白名单一般有以下几种写法:
      'localhost' 
      '10.0.0.52'
      '10.0.0.%'
      '10.0.0.5%'
      '10.0.0.0/255.255.255.0'
      '%'

3、查询用户信息

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| cp            | 10.0.0.%  | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
+---------------+-----------+-------------------------------------------+

4、修改用户密码

mysql> alter user cp@'10.0.0.%' identified by '123456';
mysql> select user,host,authentication_string from mysql.user where user='cp';
+------+----------+-------------------------------------------+
| user | host     | authentication_string                     |
+------+----------+-------------------------------------------+
| cp   | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+----------+-------------------------------------------+

5、删除用户

mysql> drop user cp@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,authentication_string from mysql.user where user='cp';
Empty set (0.00 sec)

二、权限管理

1、查看所有权限

mysql> show privileges;
+----------------+--------------------------+-----------------------------------------------+
| Privilege      | Context                  | Comment                                       |
+----------------+--------------------------+-----------------------------------------------+
| Create         | Databases,Tables,Indexes | To create new databases and tables            |
| Drop           | Databases,Tables         | To drop databases, tables, and views          |
| Select         | Tables                   | To retrieve rows from table                   |
| Insert         | Tables                   | To insert data into tables                    |
| Update         | Tables                   | To update existing rows                       |
| Delete         | Tables                   | To delete existing rows                       |
| Usage          | Server Admin             | No privileges - allow connect only            |

| Grant option   | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
......其余权限省略

  注:展示的几乎所有权限都包含在ALL中,除了Usage 和 Grant option权限

2、授权

# 以下命令表示授予cp@'10.0.0.%'用户对所有库的所有表拥有ALL包含的所有权限
mysql> grant all on *.* to cp@'10.0.0.%';

  格式:GRANT 权限 ON 库.表 TO 用户

# 以下命令表示给cp@'localhost'用户授予mysql数据库的所有表的查询、插入、更新、删除权限,如果用户不存在会自动创建。
mysql> grant select,insert,update,delete on mysql.* to cp@'localhost' identified by '000000';

3、查看用户权限信息

mysql> show grants for root@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

mysql> show grants for cp@'10.0.0.%';
+------------------------------------------------+
| Grants for cp@10.0.0.%                         |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'cp'@'10.0.0.%' |
+------------------------------------------------+

通过以上我们可以发现,前面我们给cp用户赋予了ALL权限,而它对比root用户,没有Grant option权限(即给其它人赋予权限的权限)。

4、移除授权

mysql> revoke delete on mysql.* from cp@'localhost';

mysql> show grants for cp@'localhost';
+---------------------------------------------------------------+
| Grants for cp@localhost                                       |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cp'@'localhost'                        |
| GRANT SELECT, INSERT, UPDATE ON `mysql`.* TO 'cp'@'localhost' |
+---------------------------------------------------------------+

三、8.0版本后用户和权限管理相关的变化

1、用户加密套件的变化

8.0后加密套件不在采用mysql_native_password,而是使用caching_sha2_password 。

# 5.7.28
mysql> select user,host,plugin from mysql.user;
+---------------+-----------+-----------------------+
| user          | host      | plugin                |
+---------------+-----------+-----------------------+
| root          | localhost | mysql_native_password |
| mysql.session | localhost | mysql_native_password |
| mysql.sys     | localhost | mysql_native_password |
| cp            | localhost | mysql_native_password |
+---------------+-----------+-----------------------+

# 8.0.20
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| cp               | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+

# 更改加密套件
mysql> alter user cp@'localhost' identified with mysql_native_password by '000000';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,authentication_string,plugin from mysql.user where user='cp';
+------+-----------+-------------------------------------------+-----------------------+
| user | host      | authentication_string                     | plugin                |
+------+-----------+-------------------------------------------+-----------------------+
| cp   | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 | mysql_native_password |
+------+-----------+-------------------------------------------+-----------------------+

2、grant命令不再支持自动创建用户

必须先创建好用户,再授予权限。

mysql> grant all on mysql.* to cp@'localhost' identified by '000000';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '000000'' at line 1

mysql> create user cp@'%' identified by '000000';
Query OK, 0 rows affected (0.00 sec)

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

mysql> show grants for cp@'%';
+---------------------------------+
| Grants for cp@%                 |
+---------------------------------+
| GRANT SELECT ON *.* TO `cp`@`%` |
+---------------------------------+
Like
Like Love Haha Wow Sad Angry

发表评论