五、MySQL基础系列笔记之SQL DCL语句
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日
请登录后再评论