如何只允许通过存储过程插入? [英] How to allow insert through stored procedure only?

问题描述

我有针对SQL Server的问题的MySQL版本

我正在使用readline 6.3为debian-linux-gnu(x86_64)运行mysql Ver 14.14 Distrib 5.5.49

I'm running mysql Ver 14.14 Distrib 5.5.49, for debian-linux-gnu (x86_64) using readline 6.3

我已经创建了一个只有此权限的用户:

I have created a user with only this permission:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'p@55w0rd';
GRANT EXECUTE ON dbname.* TO 'username'@'localhost';

我已经在dbname上创建了此过程:

I have created this procedure on dbname:

DELIMITER $$
CREATE PROCEDURE seed_database()
this_procedure:BEGIN

  INSERT INTO `dbtable` VALUES (1,'data');

END $$
DELIMITER ;

但是当我使用该用户登录并尝试CALL seed_database();时，我得到了:

But when I login with that user and try to CALL seed_database();, I get:

第1行的错误1142(42000):表'dbtable'的用户'username'@'localhost'的INSERT命令被拒绝

ERROR 1142 (42000) at line 1: INSERT command denied to user 'username'@'localhost' for table 'dbtable'

我试图通过仅允许用户执行最低特权原则执行存储过程.我不想给用户特权直接在带有明显GRANT INSERT ON dbname.dbtable TO 'username'@'localhost';的表上进行INSERT操作，以防凭据被泄露，并且因为我在存储过程中存在一些复杂的逻辑，这些逻辑会产生要存储的输入(用hard表示)我不希望用户直接生成并插入示例中的(1,'data')中的编码数据.但是我希望他们使用存储过程来实现相同的目标.

I'm trying to follow the principle of least privilege by only allowing a user to execute stored procedures. I don't want to give a user privilege to directly INSERT on a table with the obvious GRANT INSERT ON dbname.dbtable TO 'username'@'localhost';, in case the credentials are compromised and because I have some complex logic inside the stored procedure that produces inputs to be stored (represented by hard-coded data in the example (1,'data')) that I don't want the user generating and inserting directly. But I would like for them to use the stored procedure to accomplish the same objective.

推荐答案

我建议您阅读

I would suggest you read up on definer's rights procedures. You should be able to define your procedure to run as a privileged user, then GRANT EXECUTE to unprivileged users:

GRANT INSERT ON TABLE dbtable
  TO 'privileged_user'@'localhost';

CREATE
    DEFINER = 'privileged_user'@'localhost'
  PROCEDURE seed_database()
    BEGIN

      INSERT INTO `dbtable` VALUES (1,'data');

    END;

GRANT EXECUTE ON PROCEDURE dbname.seed_database
  TO 'unprivileged_user'@'localhost';

免责声明:我目前尚未对此进行测试，但它应该可以工作.

DISCLAIMER: I'm not set up to test this at the moment, but it should work.

默认情况下，MySQL执行具有定义者权限"的存储过程，即具有创建存储过程的人员的特权.这意味着该用户必须对过程访问的所有数据对象具有特权.当在CREATE FUNCTION / PROCEDURE中指定了DEFINER子句时，MySQL将改为使用在DEFINER子句中命名的用户的特权来执行该过程.在这两种情况下，只要 definer 对数据对象具有特权， invoker 仅需要过程本身的特权.

By default, MySQL executes stored procedures with "definer's rights," that is, with the privileges of the person who is creating the stored procedure. This means that this user must have privileges on all the data objects the procedure accesses. When the DEFINER clause is specified in the CREATE FUNCTION / PROCEDURE, MySQL will instead execute the procedure with the privileges of the user named in the DEFINER clause. In both cases, as long as the definer has privileges on the data objects, the invoker only needs privilege on the procedure itself.

也可以显式指定调用者或定义者的权利，如

Invoker's or definer's rights can also be specified explicitly, as in

CREATE PROCEDURE seed_database()
  SQL SECURITY DEFINER
  BEGIN
    ...

指定不带DEFINER =子句的SECURITY DEFINER会使定义器默认为实际执行CREATE语句的人员.这与不指定任何一个子句相同.

Specifying SECURITY DEFINER without a DEFINER = clause causes the definer to default to the person actually executing the CREATE statement. This is the same as not specifying either clause.

指定SECURITY INVOKER会使MySQL以使用存储过程的人 的特权执行.这意味着调用者必须对过程和拥有对该过程访问的所有数据对象的特权.例如，可以使用管理例程来完成此操作，以使不允许在系统表中乱搞的用户也无法使用在系统表中乱搞的程序意外授予该程序的访问权限.

Specifying SECURITY INVOKER causes MySQL to execute with the privileges of the person using the stored procedure. This means that the invoker must have privileges on the procedure and on all data objects the procedure accesses. This may be done, for example, with administrative routines so that a user who isn't allowed to muck about in the system tables also can't use a procedure that mucks about in the system tables even if accidentally granted access to that procedure.

这篇关于如何只允许通过存储过程插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！