3

I'm new to using MySQL. I'd like to use stored procedures to help secure my database when accessing it from a web service (i.e., grant only proc execute to the web service account).

What permissions do I need to grant so the web service account does not have table access but the stored procedure the service executes does?

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Jay
  • 133
  • 1
  • 5

2 Answers2

2

You need to grant the EXECUTE privilege, but there is something else you need to know.

When you create a Stored Procedure (SP), make sure the SQL SECURITY of the SP is DEFINER and not INVOKER. Why?

  • When you call a Stored Procedure that has DEFINER for SQL SECURITY, the caller is allowed to have the same grants as the DEFINER for the duration of the call. The GRANT EXECUTE for the specified Stored Procedure is necessary.
  • When you call a Stored Procedure that has INVOKER for SQL SECURITY, the caller is expected to have the needed grants. If any of the needed grants are missing, the call will fail at the earliest point where the needed grant was missing.

In plain English, if SP has INVOKER for SQL SECURITY, you have to give the table privileges away to the user permanently before the call is made. When SP has DEFINER for SQL SECURITY, the table privileges are given away temporarily (as long as the SP is running).

I discussed this before

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • Thank you! It's difficult to find an answer when you're not sure what to search for. – Jay Jun 12 '13 at 16:26
0

For multiple web servers accessing a single DB server, I find I have to define multiple users like user@ipaddr1, user@ipaddr2 etc. for the data - i.e. tables.

But in this case, for stored procedures, I think INVOKER is better than DEFINER because I know that these are trusted users, and I do not want to make an open user@% account that might get accessed from some unknown IP.

Or is there a better way using SQL SECURITY CONTEXT as DEFINER?

The manual itself says this: https://dev.mysql.com/doc/refman/5.5/en/stored-programs-security.html

To minimize the risk potential for stored program and view creation and use, follow these guidelines:

For a stored routine or view, use SQL SECURITY INVOKER in the object definition when possible so that it can be used only by users with permissions appropriate for the operations performed by the object.

Further down:

Definer-context objects should be written keeping in mind that they may be able to access data for which the invoking user has no privileges.

I think DEFINER context is made for these cases - the case you describe in the question - no access to data normally, but only to SP - which has superior full access to data, but will only give you an output and not access to the data.

In short, INVOKER should be used rather than DEFINER, if only few privileged users must be allowed to run SPs, while DEFINER should be used if anyone can run the SP which operates on important data.

Community
  • 1
site80443
  • 119
  • 6