数据库DCL语句归纳

DCL:
用来管理数据库用户、控制数据库的访问权限。

一、管理用户
1、查询用户

USE mysql；
select * from user；

2、创建用户
create user ‘用户名’@‘主机名’ identified by '密码'；

CREATE USER 'itcast'@'localhost' IDENTIFIED BY '123456';
SELECT * FROM `user`;
CREATE USER 'yixing'@'%' IDENTIFIED by '123456';

3、修改用户密码
alter user '用户名'@‘主机名’ identified with mysql_native_password by '新密码'；

ALTER user 'yixing'@'%' IDENTIFIED WITH mysql_native_password by '123456789';
ALTER user 'yixing'@'%' IDENTIFIED with mysql_native_password by '1234';

4、删除用户

drop user 'itcast'@'localhost';

二、权限控制
1、查询权限
show grants for '用户名'@'主机名';

show GRANTS for 'yixing'@'%';
show GRANTS for 'root'@'localhost';

2、授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

GRANT ALL on my1.* to 'yixing'@'%';
SHOW GRANTS for 'yixing'@'%';

3、撤销权限
revoke 权限列表  on 数据库名.表名 from '用户名'@'主机名';

revoke ALL on my1.* from 'yixing'@'%';
SHOW GRANTS for 'yixing'@'%';