MySQL篇(四)--DCL数据控制语言

mysql操作语句之数据控制语言DCL example:设置或更改数据库用户或角色权限，包含 GRANT,DENY,REVOKE等。

• 查看root用户可以在哪台机器登录

select user,host from mysql.user where user='root';

• 修改root用户登录的机器

update mysql.user set host = 'localhost' where user = 'root';

• 刷新权限

flush privileges;

• 修改用户密码

#方法一: set password for '用户'@'host' = '密码';
set password for 'root'@'%' = '123456';
#方法二: alter user '用户'@'host' identified by '密码';
alter user 'root'@'%' identified by '123456';
#方法三: alter user '用户'@'host' identified with mysql_native_password by '密码';
alter user 'root'@'%' identified with mysql_native_password by 'root';
#方法四：./mysqladmin -u用户 -p旧密码 password，好处：修改的密码不会暴露
    [root@XXXX bin]# ./mysqladmin -uroot -proot password
    mysqladmin: [Warning] Using a password on the command line interface can be insecure.
    New password: 
    Confirm new password: 
    Warning: Since password will be sent to server in plain text, use ssl connection to 	ensure password safety.

• 忘记密码操作

1)修改/etc/my.cnf文件，在[mysqld]下面加上 skip-grant-tables （跳过权限的意思）
2)重启mysql服务
3)mysql -uroot -p无密码直接登录
4)修改密码，退出，将/etc/my.cnf文件，在[mysqld]下面skip-grant-tables注释掉，重启服务即可

• 创建用户

语法： create user '用户'@'host' identified by ‘密码’;
#host:指定该用户可以在哪台主机上登录，如果是本地用户可以使用localhost,如果是远程登录可以使用通配符 %
example: 
#指定domgo用户可以在任意机器上登录，密码为domgo
create user 'domgo'@'%' identified by 'domgo';
#指定domgo用户可以在192网段的机器上登录，密码为domgo
create user 'domgo'@'192.%.%.%' identified by 'domgo';

• 查看权限

语法： show grants for '用户'@'host';
show grants for 'domgo'@'localhost';
+-------------------------------------------+
| Grants for domgo@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `domgo`@`localhost` |
+-------------------------------------------+
USAGE:表示无权限的意思

mysql> show grants for 'root'@'%'\G
*************************** 1. row ***************************
Grants for root@%: GRANT 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, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION
WITH GRANT OPTION：表示当前用户拥有grant权限，即可以对其他用户授权

• 删除用户

drop user '用户'@'host';
delete from mysql.user where user = '用户';

• 权限授予 — 权限操作后记得刷新 flush privileges

语法： grant 权限1,权限2 on 数据库对象 to '用户';
	  grant 权限1,权限2 on 数据库对象 to '用户'@'host' identified by '密码';	(mysql 8不可用)
#example 授予domgo所有库所有表的所有权限
grant all privileges on *.* to 'domgo'@'localhost';
#授予domgo用户test库的SELECT,UPDATE,DELETE权限
grant SELECT,UPDATE,DELETE on test.* to 'domgo'@'%';

• 权限回收 — 权限操作后记得刷新 flush privileges

语法： revoke 权限1,权限2 on 数据库对象 from '用户'@'host';
#example 收回domgo用户的delete权限
revoke DELETE on test.* from 'domgo'@'%';