Call my on Skype Follow me on Twitter Join me on Facebook Connect with LinkedIn Hire me on UpWork
Richard Wallman - a MySQL® DBA (database administrator)

Fast track to failure: GRANT ALL ON schema.* TO...

Posted on 2016-01-06T00:00:00 @ 14:00:00

It's depressingly common to see (mainly web) applications that, as part of their installation procedure, prompt the user to enter a set of credentials in order to build or upgrade the database schema. On the face of it, this seems to be an essential requirement, but the problems start when these credentials are used for the normal operation of the application - when the installation credentials become the standard credentials.

So, what's so bad about that?

The operations required during an installation or upgrade of a system tend to be vastly different to those required for the standard running of the system. In order to make installation processes as simple as possible, users are encouraged to hand over all database administration operations to an installer script, which (once again, for the ease of installation) tend to ask for complete control over a schema through GRANT ALL privileges.

GRANT ALL is an extremely broad set of privileges for a single user to have - in MySQL 5.6, this is:

  • DROP

In my experience, GRANT ALL tends to be given on an entire schema rather than on a per-table basis, which raises some pretty terrifying possibilities - the application is potentially one SQL injection flaw away from total destruction. Sure, you could reload the data from one of the backups (you do have backups, right? And you've tested that you can restore from them?) but that's not going to help much if the attacker has used CREATE EVENT to make a scheduled task to empty the database every few minutes...

In addition, when users share a single schema amongst several applications, the possibility is raised that a vulnerability in one application could be used to abuse the data in other applications.

Even discounting malicious actions, if a programming mistake means that a table name prefix (a common technique for allowing multiple applications to share a single schema) is ignored then the wrong table might get referenced and potentially destroyed. This can lead to super happy fun times - spending hours trying to figure out why an application no longer works, how its tables managed to get mangled, checking for signs of system exploits, and then finding that it was all caused by a faulty upgrade script in a plugin for an entirely separate application.

Why do they do it then?

One of the strongest drivers for doing this is to allow for in-app installation/upgrades of plugins, or the entire application - in order to do the required database changes, the DDL privileges would be required. If the standard credentials have enough privileges to perform these modifications, it gives the appearance of being an extremely easy to manage system - once set up, a client can be left to manage their own service.

If we're being slightly less charitable, they do it because it's just easier for the application authors - especially if the web application can run on several RDBMS backends. GRANT ALL is pretty universally supported, so there's no need to keep track of the user's system configuration and maintain separate blocks of SQL for different systems (and versions of systems!) Grab all the privileges you can at the start, and you'll never have to bother the user again.

What's the alternative?

If you're the application author, identify which privileges your application requires for normal operations, and only ask for grants for those privileges. Don't ask for DROP unless your application regularly runs the DROP TABLE command. (does it really?)

If/when more privileges are required, prompt the user to provide the credentials for another MySQL user account which does have the required privileges. This is actually an extremely positive security measure - even if an attacker can somehow gain themselves administrative access to an application, they won't be able to install/replace plugins to further damage the system or increase their control (using the database, at least). This may sound like an inconvenience for the end user, but it's unlikely to make a significant impact unless they're expecting to be doing a lot of administration work.

I'm just a user, what can I do?

At the very least, separate each web application into its own schema, with its own credentials - it may not prevent an application from exploding, but at least the damage will be contained to just that application. Don't re-use credentials between applications, otherwise you may lose these benefits.

I don't use web applications, so I'm okay?

The same thing applies for any type of application that uses a database - if it is given broad privileges, there's the potential for trouble.