Day4 | 数据库操作DCL

怼烎@ 2023-02-20 10:41 30阅读 0赞

![format_png][]

# DCL    #

> **DCL（Data Control Language），数据库控制语言。**
> 
> 主要用途：对数据库访问权限进行控制的指令。
> 
> 操纵命令：GRANT、REVOKE
> 
> GRANT：对用户进行赋权。
> 
> REVOKE：对用户权限进行回收。

# 用户管理 #

MYSQL8.0后版本不支持grant语句直接创建用户，因此需要先创建用户，再进行赋权。

## 创建用户 ##

CREATE USER 'username'@'hostname' IDENTIFIED BY '密码';
    
    案例：
    create user 'csg1' identified by '123';

![format_png 1][]

用Create创建的用户仅有系统默认权限usage，无其他权限。

## 删除用户 ##

DROP USER 'username'@'hostname';
    
    案例：
    drop user csg1;

![format_png 2][]

# 权限管理 #

### 赋权 ###

GRANT priviledge_name[(list_name1,list?_name2)] ON dbname.table_name FROM 'username'@'hostname';
    
    案例：
    对账号'csg1'添加数据库'mysqlpractice'所有表的'select'权限。
    grant select on mysqlpractice.* to csg1;

![format_png 3][]

案例：
    对全库全表赋权所有权限。
    
    grant all on *.* to dba@localhost;

### 查看权限 ###

查看指定账号权限：
    SHOW GRANTS FOR 'username'@'hostname';
    
    查看当前用户自身权限：
    SHOW GRANTS;

### 回收权限 ###

REVOKE priviledge_name[(list_name1,list?_name2)] FROM dbname.table_name FROM 'username'@'hostname';
    
    案例：
    revoke select on mysqlpractice.* from csg1;

![format_png 4][]

![format_png 5][]

每一个在看都是一种支持![format_png 6][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi9xaWFTbDNSVDFSYzFWVTREQk8xanBxQjZCUGNvaHh0SkFZUVBBUGQ0YXJXSkdLdUNiTlBnN2cwSWlhTWJOdzhQZ2NjaWNqVUxRVk5hTDgzVVNLTmVrcFJpYWcvNjQw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzM1TWlhZmJ6V3U5eUswNXFaaDRWaEFGcEoxNzlyM0l3eWtrMmxpYlZLejBZc2ppY3d5NUJuejlacUNxMUVJZG9FOEtJczYxMnZadW9vSlEvNjQw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzM1TWlhZmJ6V3U5eUswNXFaaDRWaEFGSFpQZnlsOEdyTmoya0swZjJJYWljM2NzaGF0dEhpY0VjV2liZmI5enhwYmNPaWJaWW5HQnF5dnlEUS82NDA?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzM1TWlhZmJ6V3U5eUswNXFaaDRWaEFGandwRjZQNUdpY0N0SkJ3YXRidGljaWJPVEdVMFZ2aWMwUVFSYjdoVDhUVFlxOXBpYUQ4OTZicXhFVUEvNjQw?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzM1TWlhZmJ6V3U5eUswNXFaaDRWaEFGUktlek1uOGljZ2FkbWcxMGlhcGJrcVAySEV2UTNJdlgyY0FuaWJIUWliamFtSTI3cDZpYTduUmljOFpRLzY0MA?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzJWQ2oyRmhpY2ljYm9maWFoNGFpY2xQM291aWMydnM2UXdaWldKaHNvS2lhOVY1dkNGTWljV0hPa25jamRPZUZOWklYWEJpYVF6dmN4TmljV3ZSd3cvNjQw?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzM1TWlhZmJ6V3U5eUswNXFaaDRWaEFGSmljSlBxdzdCaDE3bUR6Z0p1RlpWU2liWHJpY3NMZ2lhWmR1ZnN4T2RDaWF1akluQUlJWnJ1YUZVM0EvNjQw?x-oss-process=image/format,png