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';
CREATE PROCEDURE seed_database()
INSERT INTO `dbtable` VALUES (1,'data');
ERROR 1142 (42000) at line 1: INSERT command denied to user 'username'@'localhost' for table 'dbtable'
GRANT INSERT ON dbname.dbtable TO 'username'@'localhost';
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.
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 ...
SECURITY DEFINER without a
DEFINER = clause causes the definer to default to the person actually executing the