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 on

Solution 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:

GRANTINSERTONTABLE dbtable
  TO'privileged_user'@'localhost';

CREATE
    DEFINER ='privileged_user'@'localhost'PROCEDURE seed_database()
    BEGININSERTINTO `dbtable` VALUES (1,'data');

    END;

GRANTEXECUTEONPROCEDURE 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

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