五、MySQL基础系列笔记之SQL DCL语句

作者: 温新

分类: 【MySql】

阅读: 1950

时间: 2020-10-09 15:00:54

DCL语句主要是DBA用来管理系统中对象权限时使用,开发人员一般用的少。

MySQL是一个多用户数据库,有着强大的访问控制系统,可以为不同的用户的指定允许的权限。MySQL中用户分为普通用户与root用户。root用户是超级管理员,拥有所有权限,普通用户只拥有被授予权限。

登录数据库详解

语法

mysql -h 主机名 -u 用户名 -p 密码 -P 端口 数据库名 -e 执行sql语句;

用户管理

1、创建普通用户

语法

create user 用户名[@主机名] [identified by '密码'];

注意:主机名默认值为%,表示这个用户可以从任何主机连接mysql服务器。

案例

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 案例一:不指定主机名</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">create</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">user</span> test01;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">select</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">user</span>,host <span style="box-sizing: border-box;color: rgb(219, 45, 32)">from</span> mysql<span style="box-sizing: border-box;color: rgb(1, 160, 228)">.user</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------------------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(219, 45, 32)">user</span>             | host      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------------------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| test01           | %         |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| mysql<span style="box-sizing: border-box;color: rgb(1, 160, 228)">.infoschema</span> | localhost |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| mysql<span style="box-sizing: border-box;color: rgb(1, 160, 228)">.session</span>    | localhost |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| mysql<span style="box-sizing: border-box;color: rgb(1, 160, 228)">.sys</span>        | localhost |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| root             | localhost |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------------------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(163, 78, 143)">5</span> rows <span style="box-sizing: border-box;color: rgb(219, 45, 32)">in</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">set</span> (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.00</span> sec)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 案例二:指定主机名</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">create</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">user</span> test02<span style="box-sizing: border-box;color: rgb(1, 160, 228)">@localhost</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.02</span> sec)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 案例三:指定密码</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">create</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">user</span> test03<span style="box-sizing: border-box;color: rgb(1, 160, 228)">@localhost</span> identified <span style="box-sizing: border-box;color: rgb(219, 45, 32)">by</span> <span style="box-sizing: border-box;color: rgb(242, 176, 29)">'123123'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.02</span> sec)</span>

2、修改用户密码

语法

alter 用户表 用户名@主机 identified with mysql_native_password by 密码;

案例

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> alter user test02@localhost identified with mysql_native_password by '123456';</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">Query OK, 0 rows affected (0.09 sec)</span>

3、删除用户

语法一

drop user 用户名@主机;

案例

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">drop</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">user</span> test04<span style="box-sizing: border-box;color: rgb(1, 160, 228)">@localhost</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.09</span> sec)</span>

语法二

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">delete</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">from</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">user</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">where</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">user</span> = <span style="box-sizing: border-box;color: rgb(242, 176, 29)">'用户名'</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">and</span> host = <span style="box-sizing: border-box;color: rgb(242, 176, 29)">'主机'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">flush</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">privileges</span>;</span>

案例

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">delete</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">from</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">user</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">where</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">user</span> = <span style="box-sizing: border-box;color: rgb(242, 176, 29)">'test03'</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">and</span> host = <span style="box-sizing: border-box;color: rgb(242, 176, 29)">'localhost'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">1</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">row</span> affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.03</span> sec)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">flush</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">privileges</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.00</span> sec)</span>

用户授权

4、查看所有可设置的权限

mysql> show privileges;

5、给用户授权

语法

grant privileges on 数据名.表 to 用户[@主机] [identified by 用户密码] [with grant option];

案例

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 为test02用户授权操作所有数据库的权限</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">grant</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">all</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">on</span> *.* <span style="box-sizing: border-box;color: rgb(219, 45, 32)">to</span> test02<span style="box-sizing: border-box;color: rgb(1, 160, 228)">@localhost</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.01</span> sec)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 授权test03用户有对demo数据库的操作权限</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">create</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">user</span> test03<span style="box-sizing: border-box;color: rgb(1, 160, 228)">@localhost</span> identified <span style="box-sizing: border-box;color: rgb(219, 45, 32)">by</span> <span style="box-sizing: border-box;color: rgb(242, 176, 29)">'123456'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.04</span> sec)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">grant</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">all</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">on</span> demo.* <span style="box-sizing: border-box;color: rgb(219, 45, 32)">to</span> test03<span style="box-sizing: border-box;color: rgb(1, 160, 228)">@localhost</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.01</span> sec)</span>

6、查看用户权限

语法

show grants for 用户名@主机;

案例

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">show</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">grants</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">for</span> test03<span style="box-sizing: border-box;color: rgb(1, 160, 228)">@localhost</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----------------------------------------------------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(219, 45, 32)">Grants</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">for</span> test03<span style="box-sizing: border-box;color: rgb(1, 160, 228)">@localhost</span>                              |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----------------------------------------------------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(219, 45, 32)">GRANT</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">USAGE</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">ON</span> *.* <span style="box-sizing: border-box;color: rgb(219, 45, 32)">TO</span> <span style="box-sizing: border-box;color: rgb(1, 160, 228)">`test03`@`localhost`</span>               |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(219, 45, 32)">GRANT</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">ALL</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">PRIVILEGES</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">ON</span> <span style="box-sizing: border-box;color: rgb(1, 160, 228)">`demo`</span>.* <span style="box-sizing: border-box;color: rgb(219, 45, 32)">TO</span> <span style="box-sizing: border-box;color: rgb(1, 160, 228)">`test03`@`localhost`</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----------------------------------------------------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(163, 78, 143)">2</span> rows <span style="box-sizing: border-box;color: rgb(219, 45, 32)">in</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">set</span> (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.00</span> sec)</span>

7、查看当前用户权限

语法

show grants;

8、撤销用户权限

语法

revoke privileges on 数据库.表 from 用户[@主机];

案例

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">revoke</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">all</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">on</span> demo.* <span style="box-sizing: border-box;color: rgb(219, 45, 32)">from</span> test03<span style="box-sizing: border-box;color: rgb(1, 160, 228)">@localhost</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.01</span> sec)</span>

我是小白,期待和优秀的你一起同行!

小白

2020年10月09日

请登录后再评论