How to allow insert through stored procedure only?

Question

I have the MySQL version of this question for SQL-Server

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';

I have created this procedure on dbname:

DELIMITER $$
CREATE PROCEDURE seed_database()
this_procedure:BEGIN

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

END $$
DELIMITER ;

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

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

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.

Answer 1

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.

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
    ...

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.

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.