Jeff Puckett II Jeff Puckett II - 6 months ago 19
MySQL Question

How to allow insert through stored procedure only?

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

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 "invoker's rights," that is, with the privileges of the person who is using the stored procedure. This means that the user must have privileges on the stored procedure and also 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. 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.

Comments