Home / server / Questions / 674300
Accepted
Alex R
Asked: 2015-03-11 05:07:21 +0800 CST

How to implement table security without SUPER privilege?

  • 772

A MAJOR provider of cloud-based MySQL does not grant the SUPER privilege to the master user. The provider is Amazon RDS, but my question isn't about Amazon RDS specifically, it's for the general case of the database owner/administrator not having SUPER privilege.

Lacking SUPER privilege means you cannot use the DEFINER clause when creating Stored Procedures.

That in turn, means, you cannot lock-down your tables to be inaccessible to a given user while at the same time granting that same user indirect access via a Stored Procedure.

Is there an alternative way of accomplishing the same "no direct table access" security strategy without SUPER?

mysql

2 Answers

  1. As originally answered, the SUPER privilege simply allows a highly privileged user to set the definer in stored routines to a user other than themselves. You can log in as the definer in order to create a stored procedure, trigger or view under a different security context, but will need to permit authentication as that user so that you can log in when first creating.

    On Amazon RDS, you can't lock a user down to localhost only, as you have no shell access to the host. As such, you either lock the user down to a host or range, but will have to keep this in your routine, or use the wildcard host and expose the user to a potential security weakness.

    For MariaDB, I found the account lock feature. Although a locked account can't be logged in as, it can still execute stored routines. As such, the wildcard host could be used to grant access from anywhere while logging in to create the routine(s), and then that user can be locked, allowing it to execute stored routines only. The highly privileged user that performs the locking will of course need to be otherwise secured.

    • 2
  2. Best Answer

    Non-sequitur.

    You can't explicitly declare a DEFINER other than yourself, which is pointless of course, since you're the already the definer... but you can still use SQL SECURITY DEFINER | INVOKER to specify the security context the procedure uses at runtime. This part, and its security aspects, isn't any different than when you have the SUPER privilege. The only difference is that if you want the definer to be a specific (privileged) user, so that the procedure can run in definer context as that user... you have to actually log in as the DEFINER in order to declare the procedure.

    • 0