MySQL用户管理及权限管理

1.初始用户管理及权限管理

• 1.1MySQL超级管理员用户密码设置（root@'locahost'）

[root@db01 ~]# mysqladmin -uroot -p password 123      # -u用户名 -p旧密码 password密码
Enter password: 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

• 1.2MySQL数据库登录

[root@db01 ~]#  mysql -uroot -p123

• 1.3MySQL用户修改密码

[root@db01 ~]# mysqladmin -uroot -p password 123456
Enter password:               #输入旧密码
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

2.MySQL用户管理

• 2.1用户定义

用户名@'白名单'

• 用户管理的操作（8.0版本之后）

8.0版本

mysql> create user root@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (1.52 sec)

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

2.2.1创建用户

mysql> create user zfs@'10.0.0.%';                        #创建用户没有密码
mysql> create user oldboy@'10.0.0.%' identified by '123'; #创建用户同时设置密码

2.2.2查询用户

mysql> select user,host ,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| zfs           | 10.0.0.%  |                                           |
| oldboy        | 10.0.0.%  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

2.2.3修改用户密码

mysql>  alter user zfs@'10.0.0.%'  identified by '123456';

2.2.4删除用户

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

3.MySQL用户权限管理

• 3.1grant命令使用

格式：grant 权限 on 权限范围 to用户 identified by 密码

3.1.1权限：
ALL:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能

3.1.2权限范围：

3.1.3命令使用
需求：让zfs用户管理WordPress库的所有的表

grant all on wordpress.* to zfs@'10.0.0.%' identified by '123456';

需求：创建一个app用户，对WordPress库只有增删改查的权限
(8.0版本之后只能先创建用户在授权)

mysql> grant select,update,delete,insert on wordpress.*  to  app@'10.0.0.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

3.1.4查看用户权限

mysql>  show grants for app@'10.0.0.%';
+---------------------------------------------------------------------------+
| Grants for app@10.0.0.%                                                   |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app'@'10.0.0.%'                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `wordpress`.* TO 'app'@'10.0.0.%' |
+---------------------------------------------------------------------------+
2 rows in set (0.01 sec)

3.1.5用户权限回收

mysql>  revoke delete on wordpress.* from app@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for app@'10.0.0.%';
+-------------------------------------------------------------------+
| Grants for app@10.0.0.%                                           |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app'@'10.0.0.%'                            |
| GRANT SELECT, INSERT, UPDATE ON `wordpress`.* TO 'app'@'10.0.0.%' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)