1

I am new to Databases. I wanted that updates in the database happen only through a stored procedure. The end-user does not have GRANT UPDATE but has a GRANT EXECUTE ON PROCEDURE.

I tried it this way but figured out that the end-user needed to have a UPDATE permission. Is there any other way in which this can be achieved?

Nitin Chhajer
  • 2,299
  • 1
  • 24
  • 35
  • As long as (a) your procedure's `DEFINER` has the update permissions, (b) the procedure has `SQL SECURITY DEFINER` and (c) the user calling the procedure has `EXECUTE`, what you're trying to do should work. See @AlainCollins below. – grossvogel Sep 11 '12 at 16:46

1 Answers1

2

According to the doc:

Any user who has the EXECUTE privilege for p1 can invoke it with a CALL statement. However, when p1 executes, it does so in DEFINER security context and thus executes with the privileges of 'admin'@'localhost', the account named in the DEFINER attribute. This account must have the EXECUTE privilege for p1 as well as the UPDATE privilege for the table t1. Otherwise, the procedure fails.

So you should be able to set the DEFINER to the authorized user account and only give your end users the ability to execute the procedure.

Alain Collins
  • 16,268
  • 2
  • 32
  • 55
  • 1
    So the usual way to implement this model is to use a highly privileged account to create your procedures, which automatically sets `DEFINER` to that user. Note there's also the [`SQL SECURITY`](http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html) characteristic, but it defaults to `DEFINER`, which is what you want. Here's a [question about changing the definer](http://stackoverflow.com/questions/3206820/mysql-change-the-stored-procedure-definer). – grossvogel Sep 11 '12 at 16:43