Are there potentential risks to allowing a PHP website MySQL User to have Create, Alter, and Drop Permissions

Question

Let me prefix my question with some information on our environment. We have a web server with 100+ websites. The web server has a corresponding MySQL Server version 5.7. Each PHP website has MySQL Database with a MySQL User attached to it that only has permissions set for it to be able to access to it's database. Previously we have only given that website user, SELECT, UPDATE, DELETE, and INSERT permissions. We are now running into a situation where we must now give the user Create, Alter, and Drop permissions. I'm wondering what potential security risks that puts us in by giving these website users these additional permissions.

The first obvious one I can think of is with the DROP permission, someone could obviously drop all tables and all data would be lost. I'm thinking we may be able to restructure our code to not require DROP. If that's the case and we only need to add CREATE & Alter to the user's then what is a worst case scenario situation? The database are also locked down by IP to only be accessed within our network so the only way a user could be accessing the database directly would be through a SQL Injection through the website.

Answer 1

Unfortunately I'm not very familiar with PHP so I cannot provide any hint of how this could be specifically exploited from the code but I want to point out some other details. From a security engineering point of view, you open a serious amount of attack opportunities. First of all, the reasons to avoid DROP are obvious. Apart from that, if an SQL injection does happen the user could have made a script that creates an infinite amount of databases and potentially DOS your system, a scripted attack using ALTER may also cause chaos in your database. You can check some of those things in the backend but still granting too much power to a user

---

It's difficult to propose something without the source but why don't you try to isolate the "dangerous" actions inside the server by passing the request to a backend verification and then have root or another privileged user alter the database?

Answer 2

I don't think you have significantly reduced the security of the system.

Previously, somebody could write a query like:

DELETE FROM importantTable where year(now()) <> 49

This means it elopes the "DELETE ALL" protection MySQL has, but at the end, the statement will remove all your data, assuming you don't live in Julius Cesar's time.

My point is that if there is malevolent will to delete your data, your change does not not improve the attacker's odds.

My concern is more of safety. Assuming that a programmer writes a "James Bond style ALTER TABLE": will drop the table and then recreate it. This is fine as long as the table doesn't contain any data, but will cause data loss.

The fact that your database is accessed from PHP or from other system is irrelevant. It is however relevant to understand the acumen of the people deploying the code. How many times do you record in your logs a denied DROP? This "shouldn't have happened", and by the fact you denied DROP, you may have preserved the data.