Loading... <img class=" ls-is-cached lazyloaded" title="1480776820928760.jpg" src="https://www.zsythink.net/wp-content/uploads/ueditor/php/upload/image/20161203/1480776820928760.jpg" alt="mysql.jpg" data-src="https://www.zsythink.net/wp-content/uploads/ueditor/php/upload/image/20161203/1480776820928760.jpg" style=""> 这篇文章总结了mysql中授权相关的常用语句。 在本博客中,”mysql”是一个系列文章,这些文章主要对mysql/mariadb的常用知识点进行了总结,每一篇博客总结的知识点有所不同,具体内容可参考mysql文章列表。 mysql文章列表直达链接:<a href="https://www.zsythink.net/archives/tag/mysql/" target="_blank" rel="noopener">mysql知识点总结</a> <h1 id="wznav_0">授权命令</h1> 常用授权语句的语法如下: <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">GRANT ALL </span><span class="enlighter-g1">[</span><span class="enlighter-text">PRIVILEGES</span><span class="enlighter-g1">]</span><span class="enlighter-text"> ON db.</span><span class="enlighter-m3">tbl</span><span class="enlighter-text"> TO </span><span class="enlighter-s0">'username'</span><span class="enlighter-text">@</span><span class="enlighter-s0">'host'</span><span class="enlighter-text"> IDENTIFIED BY </span><span class="enlighter-s0">'password'</span><span class="enlighter-text">;</span></div> </div> </div> </div> 上述语法中,db表示数据库的名字,可以使用*通配,tbl为表的名称,可以使用*通配 示例语句如下: 给本地用户授权某个数据库的所有权限 grant 权限 on 数据库名.表名 to 用户名@”本地回环地址或localhost” identified by “密码” <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">grant all privileges on zsythink.* to zsy@localhost identified by </span><span class="enlighter-s0">'zsythink'</span><span class="enlighter-text">;</span></div> </div> </div> </div> <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">grant all privileges on zsythink.* to zsy@</span><span class="enlighter-n0">127.0</span><span class="enlighter-text">.</span><span class="enlighter-n0">0.1</span><span class="enlighter-text"> identified by </span><span class="enlighter-s0">'zsythink'</span><span class="enlighter-text">;</span></div> </div> </div> </div> 注意:上述两条命令都表示对zsy用户开放zsythink数据库的所有权限,但是上述两条命令的针对的zsy用户是不一样的,一个是zsy@localhost用户,一个是zsy@127.0.0.1用户,mysql会认为这是两个用户。 授权时privileges关键字可省,示例如下 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">grant all on zsythink.* to zsy@</span><span class="enlighter-n0">127.0</span><span class="enlighter-text">.</span><span class="enlighter-n0">0.1</span><span class="enlighter-text"> identified by </span><span class="enlighter-s0">'zsythink'</span><span class="enlighter-text">;</span></div> </div> </div> </div> 给远程用户授权 grant 权限 on 数据库名.* to 用户名@’远程主机地址或对应的主机名’ identified by “密码” <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">grant all privileges on zsythink.* to zsy@</span><span class="enlighter-s0">'%'</span><span class="enlighter-text"> identified by </span><span class="enlighter-s0">'zsythink'</span><span class="enlighter-text">;</span></div> </div> </div> </div> 上述命令比较危险,表示zsy用户可以通过任意主机连接到zsythink数据库,并且拥有zsythink数据库的所有权限。 我们可以稍微缩小一下zsy用户能够连接数据库的IP地址范围,比如,只允许zsy用户通过192.168网段的地址连接zsythink数据库,示例语句如下 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">grant all privileges on zsythink.* to zsy@</span><span class="enlighter-s0">'192.168.%.%'</span><span class="enlighter-text"> identified by </span><span class="enlighter-s0">'zsythink'</span><span class="enlighter-text">;</span></div> </div> </div> </div> 使用上述命令授权后,需要使用如下命令刷新。 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">FLUSH PRIVILEGES</span></div> </div> </div> </div> 当然,我们也可以授权用户某个数据库的某个权限,比如,只授权用户对于某个数据库的查询权限,示例如下。 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">grant select privileges on zsythink.* to zsy@</span><span class="enlighter-s0">'192.168.%.%'</span><span class="enlighter-text">;</span></div> </div> </div> </div> 也可以授权用户某个数据库的多个权限,比如,授权用户 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">grant insert,delete,update,select on zsythink.* to zsy@</span><span class="enlighter-s0">'192.168.%.%'</span><span class="enlighter-text">;</span></div> </div> </div> </div> 还可以将同样的权限同时授权于多个用户,示例如下 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">grant select on hellodb.* to zsy@localhost,zsythink@localhost;</span></div> </div> </div> </div> 如果想要只对某张表的某个字段授权,可以使用如下语句 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">grant </span><span class="enlighter-m0">select</span> <span class="enlighter-g1">(</span><span class="enlighter-text">name,age</span><span class="enlighter-g1">)</span><span class="enlighter-text"> on zsythink.</span><span class="enlighter-m3">students</span><span class="enlighter-text"> to zsy@localhost;</span></div> </div> </div> </div> 上述示例表示只授予zsy用户对zsythink库中students表的name字段和age字段的查询权限,虽然students表中还有stuid字段、gender字段、weight字段,但是zsy用户无法看到这三个字段,也不能查询这三个字段中的内容,因为zsy没有查询这三个字段的权限,当zsy用户使用desc命令查看students表结构时,只能看到name字段和age字段,如果zsy用户使用select * from students这样的查询语句查询students表,也会出现报错信息,提示对某些字段没有操作权限。 如果zsythink数据库中有一张表的名称为test,同时,zsythink数据库中有一个函数也叫test,那么,此时如果管理员只想将test函数的权限授予zsy用户,而不是想将表的权限授予zsy用户,该怎么办呢?我们可以通过function关键字指明被操作的对象为函数,而不是表,示例如下: <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">grant execute on </span><span class="enlighter-k1">function</span><span class="enlighter-text"> zsythink.</span><span class="enlighter-m3">test</span><span class="enlighter-text"> to zsy@</span><span class="enlighter-s0">'192.168.%.%'</span><span class="enlighter-text">;</span></div> </div> </div> </div> 上述语句表示授权zsy用户对zsythink数据库中test函数拥有执行权限。 同理,也可以使用procedure关键字,指明被操作的对象是存储过程,比如,如下语句表示授权zsy用户对zsythink数据库中的test存储过程拥有执行权限。 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">grant execute on procedure zsythink.</span><span class="enlighter-m3">test</span><span class="enlighter-text"> to zsy@</span><span class="enlighter-s0">'192.168.%.%'</span><span class="enlighter-text">;</span></div> </div> </div> </div> 小结:使用function关键字和procedure关键字可以对函数或存储过程授权,同理,使用view关键字即可对视图进行授权。 当一个用户被创建时,mysql会自动授予其usage权限。usage权限只能用于登录数据,不能执行其他操作。 如果用户有可能会跨越不安全的网络连接到数据库,我们可以强制用户使用ssl建立会话,命令如下。 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">grant usage on *.* to </span><span class="enlighter-s0">'zsy'</span><span class="enlighter-text">@</span><span class="enlighter-s0">'222.222.222.222'</span><span class="enlighter-text"> require ssl;</span></div> </div> </div> </div> 上述示例表示’zsy’@’222.222.222.222’用户连接当前mysql中的所有数据库时都必须使用ssl建立会话。 如果想要取消上述的ssl连接限制,可以使用如下命令,撤销强制使用ssl建立会话的限制。 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">grant usage on *.* to </span><span class="enlighter-s0">'zsy'</span><span class="enlighter-text">@</span><span class="enlighter-s0">'222.222.222.222'</span><span class="enlighter-text"> require none;</span></div> </div> </div> </div> 假设,root用户授权了zsy用户某些权限,那么zsy用户是否能将已经拥有的权限授予别的用户吗?zsy用户能否将已有权限授权于其他用户取决于zsy用户是否拥有grant选项。 如果在授权zsy用户时,搭配了grant选项,则zsy用户有权将已拥有的权限授予其他用户,但是这样做比较危险,一般情况下应由管理员同一授权,但是此处用于演示,给出如下示例。 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">grant select on zsythink.* to zsy@</span><span class="enlighter-s0">'192.168.%.%'</span><span class="enlighter-text"> with grant option;</span></div> </div> </div> </div> 上述命令表示zsy用户被授予了zsythink数据库的select的权限,同时zsy用户也能将此权限授予其他用户,而且,zsy用户也能在授予其他用户select权限时使用with grant option,所以这很危险,请勿随意使用此选项。 除了上面提到的grant option,管理员还可以通过如下选项对用户进行一些其他的限制 MAX_QUERIES_PER_HOUR:限制用户每小时执行的查询语句数量; MAX_UPDATES_PER_HOUR:限制用户每小时执行的更新语句数量; MAX_CONNECTIONS_PER_HOUR:限制用户每小时连接数据库的次数; MAX_USER_CONNECTIONS:限制用户使用当前账号同时连接服务器的连接数量; 上述各限制选项的示例如下。 grant select on *.* to zsy@’192.168.%.%’ identified by ‘123456’ with max_queries_per_hour 20; grant select on *.* to zsy@’192.168.%.%’ identified by ‘123456’ with max_updates_per_hour 10; grant select on *.* to zsy@’192.168.%.%’ identified by ‘123456’ with max_connections_per_hour 15; grant select on *.* to zsy@’192.168.%.%’ identified by ‘123456’ with max_user_connections 2; 如果将上述限制对应的数字改为0,则表示不限制。 <h1 id="wznav_1">查看授权</h1> 查看授权可以从两个方面查看,从用户的角度查看授权,或者从数据库的角度查看授权,从用户的角度查看权限表示查看对应用户都能操作哪些数据库,从数据库的角度查看权限表示查看指定数据库都对哪些用户开放了哪些权限,我们会给出示例 从数据库用户的角度查看授权的语句如下 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">show grants </span><span class="enlighter-k1">for</span><span class="enlighter-text"> 用户名;</span></div> </div> </div> </div> 比如,当我们需要查看zsy@localhost这个用户对那些库都有哪些权限,可以使用如下语句。 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">show grants </span><span class="enlighter-k1">for</span><span class="enlighter-text"> zsy@localhost;</span></div> </div> </div> </div> <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2016/12/121616_0557_1.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2016/12/121616_0557_1.png" style=""> 可以看到,zsy@localhost用户对 word、hellodb这两个数据拥有所有的操作权限。 同理,如果想要查看’zsy’@’www.zsythink.net’用户对哪些库拥有哪些权限可以使用如下语句 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2016/12/121616_0557_2.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2016/12/121616_0557_2.png" style=""> 从数据库的角度查看授权,可以使用如下语句 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from mysql.</span><span class="enlighter-m3">db</span><span class="enlighter-text"> where Db=</span><span class="enlighter-s0">"你要查看的数据库"</span></div> </div> </div> </div> 示例如下 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2016/12/121616_0557_3.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2016/12/121616_0557_3.png" style=""> <h1 id="wznav_2">删除授权</h1> 删除授权/撤销授权的常用语句如下: <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">revoke </span><span class="enlighter-s0">"要移除的权限"</span><span class="enlighter-text"> on 数据库.表 from 用户@host;</span></div> </div> </div> </div> 比如删除zsy@www.zsythink.net用户对于word数据库的所有操作权限,语句如下。 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">revoke all on word.* from zsy@www.</span><span class="enlighter-m3">zsythink</span><span class="enlighter-text">.</span><span class="enlighter-m3">net</span><span class="enlighter-text">;</span></div> </div> </div> </div> 也可以删除指定的权限,示例如下 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2016/12/121616_0557_4.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2016/12/121616_0557_4.png" style=""> 上图中,对于word数据库来说,针对zsy@www.zsythink.net用户,移除了上图中红框中的权限。 转载自朱双印日志https://www.zsythink.net/archives/431 Last modification:May 29, 2024 © Allow specification reprint Like 如果觉得我的文章对你有用,请随意赞赏