游标在Mysql有其他权限比用户？ [英] Cursor in Mysql has other rights than user?

问题描述

我有两个数据库db1和db2。

用户man1拥有db1的所有权限，但只执行db2中所有函数和过程的特权。

如果调用db2.getValue嵌套在存储过程中，则从db1调用函数db2.getValue成功并返回正确的值。

现在我创建一个存储过程proc1，在此过程中，我使用一个游标从db1中的表中获取值（_val1，_val2， - ），并调用db2.getValue（_val1）。

现在有一些奇怪的事情发生：

游标的第一个循环结束，但db2.getValue的值为空。在这个循环结束时，循环结束和停止。

任何人都有一个提示问题可能在这里？调用db1.getValue（_val1）而不是db2.getValue（_val1）可以正常工作。

因此我的想法是，在游标中，db2的权限可能不是同样？

种类Solicate

解决方案

如果由调用该过程的用户执行， SELECT 查询可能会表现不同。

< blockquote>

存储的程序和视图在使用之前定义，并且在被引用时，在确定其特权的安全上下文中执行。这些特权由它们的 DEFINER 属性控制，如果有的话，它们的 SQL SECURITY 特性。 >

^{— http://dev.mysql.com/doc/refman /5.6/en/stored-programs-security.html}

如果您未指定 DEFINER 在创建存储的程序（proc，函数，触发器或事件）或视图时，该对象在访问时，

您有三个选项，其中：

• 验证或可能修改当前 DEFINER 用户的权限（如果适用）或


• 在定义存储的程序或视图时指定不同的 DEFINER 用户...只要您创建对象的人）具有 SUPER 特权，并且调用（访问）对象的用户将暂时具有 DEFINER user;或


• 在过程，函数和视图（尽管不是触发器或事件）的定义中添加 SQL SECURITY INVOKER

要查看的对象，请使用调用它的用户的权限，而不是默认行为。例如，如果你看到DEFINER =`someguy` @`localhost`：

  mysql>显示'someguy'@'localhost'的赠款
  

您可以在过程的定义中找到当前定义器， SHOW CREATE PROCEDURE procedure_name; 。

i have two databases, db1 and db2.

User man1 had all privileges to db1 but only executing provileges for all functions and procedures in db2.

Calling function db2.getValue from db1 is successful and returns correct values, also if call for db2.getValue is nested in a stored procedure.

Now i create a stored Procedure proc1 and within this procedure i use a cursor to fetch values (_val1, _val2,--) from a table in db1 and call db2.getValue(_val1).

Now somthing really strange happens:

The first loop from the cursor finish, but the value from db2.getValue is empty. At the end of this loop, the loop ends and stops.

Does anyone have a hint where the problem could be here? Calling db1.getValue(_val1) instead of db2.getValue(_val1) works fine.

Therefore my idea was that within the cursor, the privileges for db2 might be not the same?

Kind Regards Solick

解决方案

Yes, by design, a cursor can behave differently than the same SELECT query might behave if it were executed by the user who called the procedure.

Stored programs and views are defined prior to use and, when referenced, execute within a security context that determines their privileges. These privileges are controlled by their DEFINER attribute, and, if there is one, their SQL SECURITY characteristic.

^{— http://dev.mysql.com/doc/refman/5.6/en/stored-programs-security.html}

If you don't specify a DEFINER when you create a stored program (proc, function, trigger, or event) or a view, then the object, when accessed, runs with the privileges of the user who originally defined it, not the user who invoked it.

You have three options, here:

• Verify or possibly modify the permissions of the current DEFINER user if appropriate; or,
• Specify a different DEFINER user when defining the stored program or view... you can do this as long as you (the person creating the object) have the SUPER privilege, and users invoking (accessing) the object will temporarily have the rights of that DEFINER user instead; or,
• Add SQL SECURITY INVOKER to the definition of procedures, functions, and views (though not triggers or events), causing the object to run with the privileges of the user who invoked it, instead of the definer, which is the default behavior.

To see the permissions the existing definer has, for example if you see DEFINER=`someguy`@`localhost`:

mysql> SHOW GRANTS FOR 'someguy'@'localhost';

You can find the current definer in the definition of the procedure, with SHOW CREATE PROCEDURE procedure_name;.

这篇关于游标在Mysql有其他权限比用户？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！