2

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.

  • If users never were able to create their own tables how did they ever managed to use "their" database? Or did you provide preconfigured applications and created the needed tables for the users? In this case the risk might be that users can now install arbitrary (and potentially insecure) applications instead of only the ones provided by you which increases the attack surface of your server. – Steffen Ullrich Jan 2 '18 at 21:41
  • You are correct, I provided pre-configured applications for each website so previously there was no need for a database user to have the create permission. – TroySteven Jan 2 '18 at 22:21
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?

  • Does Create give the user access to create new databases or just tables within that database? – TroySteven Jan 2 '18 at 22:24
  • Both. As the manual says, Databases, tables or indexes dev.mysql.com/doc/refman/5.7/en/privileges-provided.html – Konstantinoscs Jan 2 '18 at 23:33
  • Relating to your DOS the system using Create Statements, couldn't running a large amount of INSERT statements cause the same DOS issue if there was a SQL Injection attack? Is the CREATE permission really that much worse (related to DOS) than an INSERT statement. Also, a follow up, I did some tests on my environment, in my situation the CREATE permission only allows the user to CREATE tables not databases because they only have CREATE permissions specific to their DATABASE, they don't have global CREATE permissions for the entire SQL server. – TroySteven Jan 20 '18 at 21:22
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.