一:用户的构成
用户名@白名单
例子:
root@'localhost'
root@'10.0.0.2'
root@'10.0.0.%'
root@'10.0.0.0/255.255.254.0'
root@'%'
root@'10.0.0.5%'
二:用户的操作
2.1 添加用户
mysql> create user cxy@'10.0.0.%' identified by '123456'
2.2 查看用户
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| cxy | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.01 sec)
2.3 修改用户密码
mysql>alter user root@'localhost' identified by '123';
2.4 删除用户
mysql>drop user cxy@'10.0.0.%';
三:权限管理
3.1 权限说明
格式:grant 权限 on 范围 to 用户 identified by 密码;
权限:
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：超级管理员才具备的，给别的用户授权的功能
应用用户：SELECT,INSERT, UPDATE, DELETE
权限作用范围:
*.* : 所有数据库
wordpress.* :某个数据库
wordpress.t1 :数据库里面的某个表
3.2 给用户授权
3.2.1 给某个用户授权所有权限
mysql> grant all on *.* to cxy@'10.0.0.%' identified by '123456';
3.2.2 给某个用户授权特定的权限
注意:给某个用户授权特定的权限,权限不会覆盖,只会累加,如果已经给了用户所有权限,在给用户某些特定的权限,这个用户它还是拥有所有权限
mysql>grant SELECT,INSERT, UPDATE, DELETE, CREATE, DROP on *.* to cxy@'10.0.0.%' identified by '123456';
3.3 查看用户权限
mysql> show grants for cxy@'10.0.0.%' ;
+-------------------------------------------------+
| Grants for cxy@10.0.0.% |
+-------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'cxy'@'10.0.0.%' |
+-------------------------------------------------+
1 row in set (0.01 sec)
3.4 回收权限
常规情况下只授权select/insert/update/delete 4个权限即可
mysql>revoke delete on *.* from cxy@'10.0.0.%';
四:忘记数据库本地root密码
4.1 先关闭数据库,并启动到安全模式
[root@mysql-51 ~]# systemctl stop mysqld.service
[root@mysql-51 ~]# mysqld_safe --skip-grant-tables --skip-networking &
4.2 登录数据库.修改管理员密码
mysql> flush privileges;
Mysql>alter user root@'localhost' identified by '123456';
4.3 重新启动数据库
root@db02-51 ~]# systemctl start mysqld.service
