How to implement table security without SUPER privilege?

by Alex R   Last Updated October 20, 2019 00:00 AM - source

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?



Answers 2


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.

Michael - sqlbot
Michael - sqlbot
March 11, 2015 22:13 PM

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.

Sam_Butler
Sam_Butler
October 19, 2019 23:49 PM

Related Questions



Are there measurable benefits to running XFS on EC2

Updated November 09, 2017 10:00 AM