3

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?

Alex R
  • 1,063
  • 3
  • 14
  • 29
  • No reason you can't run your own MySQL install on an EC2 box. If you need this sort of more-complex-than-normal implementation, that's the way to go. – ceejayoz Mar 10 '15 at 13:17
  • That could solve my specific problem but doesn't answer my question :-) – Alex R Mar 13 '15 at 12:02

2 Answers2

2

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
  • 431
  • 4
  • 5
  • Interesting solution, though I'm not entirely sure this differs in a meaningful way from simply defining the privileged procedures as the admin user. – Michael - sqlbot Oct 20 '19 at 21:38
  • ...and anyway only available on MariaDB 10.4.2, but in MySQL 5.7. I do understand your point, I guess there was an earlier attempt to create a minimally-privileged but still privileged user to run those procedures that would be called by less-privileged users, but that wasn't the admin user. I need to think through the ramifications, but I think you're right, aside from logging, it doesn't seem *meaningful*. – Sam_Butler Oct 20 '19 at 21:48
  • Still, I think this answer is worthy of an upvote, so +1. – Michael - sqlbot Oct 20 '19 at 22:51
0

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.

Alex R
  • 1,063
  • 3
  • 14
  • 29
Michael - sqlbot
  • 22,658
  • 2
  • 63
  • 86
  • 1
    Excuse me for reviving a years-old question, but it's still relevant. The specific case I have on a migration to RDS is that one such privileged user was only allowed to log in from localhost, which can't be done on RDS, meaning you are forced to open access from at least one other IP and then you have network-level tight coupling in your model! It's always user@host in MySQL/MariaDB as user@% is a different set of privileges. – Sam_Butler Oct 19 '19 at 23:34