时间:2021-07-01 10:21:17 帮助过:7人阅读
创建一个简单的实例 我是在root@localhost 下面创建的
- <code class="hljs">mysql> delimiter #
- mysql> CREATE DEFINER=<span class="hljs-code">`hee`@<span class="hljs-code">`localhost` PROCEDURE <span class="hljs-code">`simpleproc`(OUT param1 INT)
- <span class="hljs-code"> begin select count(*) INTO param1 from `categories`;
- end #
- mysql> delimiter ;
- #<span class="zh-hans">调用的时候直接
- mysql> call simpleproc(@a);
- <span class="hljs-section">mysql> select @a;
- +------+
- <span class="hljs-section">| @a |
- +------+
- <span class="hljs-section">| 6 |
- +------+
- 1 row in set (0.00 sec)
- </span></span></span></span></span></span></span></span></code>
现在我切换到 hee@localhost
本应该 我执行 simpleproc 就可以的 因为当前用户就是hee@localhost 但是仍然失败 代码如下
- <code class="hljs">#<span class="zh-hans">我先创建了 hee@localhost <span class="zh-hans">用户 <span class="zh-hans">【在root@localhost <span class="zh-hans">下面 <span class="zh-hans">创建】
- mysql> create user hee@localhost identified by "abc";
- #<span class="zh-hans">在给了一部分权限给hee@localhost
- grant insert,update,select on <span class="hljs-code">`api_db`.<span class="hljs-code">`categories` to hee@localhost;
- # <span class="zh-hans">为什么我没有直接给ALL PRIVILEGES <span class="zh-hans">给 hee@localhost <span class="zh-hans">是因为 <span class="zh-hans">不是所有的情况都可以给all privileges <span class="zh-hans">的 <span class="zh-hans">我旨在说明 <span class="zh-hans">执行 procedure <span class="zh-hans">的权限
- # <span class="zh-hans">切换到 hee@localhost
- mysql> call simpleproc(@a);
- ERROR 1370 (42000): execute command denied to user <span class="hljs-emphasis">‘hee‘@<span class="hljs-emphasis">‘localhost‘ for routine <span class="hljs-emphasis">‘api_db.simpleproc‘
- <span class="hljs-section">mysql> select CURRENT_USER;
- +---------------+
- <span class="hljs-section">| CURRENT_USER |
- +---------------+
- <span class="hljs-section">| hee@localhost |
- +---------------+
- 1 row in set (0.00 sec)
- </span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>
因为在还需要另外的权限
参考 grant 权限列表 https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_execute
摘抄
The EXECUTE privilege is required to execute stored routines (procedures and functions). 要执行 procedure 必须拥有execute 权限 这个可以再 mysql.user 表格中查看
EXECUTE 是加载在一个database 上面的 所以 要授权使用
- <code class="hljs">mysql> grant EXECUTE <span class="hljs-keyword">on <span class="hljs-string">`api_db`.* <span class="hljs-keyword">to hee@localhost;
- Query OK, <span class="hljs-number">0 <span class="hljs-keyword">rows affected (<span class="hljs-number">0.00 sec)
- mysql> flush privileges;
- Query OK, <span class="hljs-number">0 <span class="hljs-keyword">rows affected (<span class="hljs-number">0.00 sec)
- </span></span></span></span></span></span></span></span></span></code>
但是 还是在另外一个终端 (hee@localhost 登陆的终端) 还是执行 call simpleproc(@a) 失败 只要重新登录mysql一下就可以了
- <code class="hljs">mysql> call simpleproc1(@a) ;
- Query OK, 1 row affected (0.00 sec)
- mysql> select @a
- <span class="hljs-section"> -> ;
- +------+
- <span class="hljs-section">| @a |
- +------+
- <span class="hljs-section">| 1 |
- +------+
- 1 row in set (0.00 sec)</span></span></span></code>
mysql stored routine (存储例程) 中 definer 的作用 和实例
标签:def tps grant htm ring space mysql cte res