5

I am building a site where people set up small, private, social networks. For ease of administration and portability, I would like each network to be stored in a different MySQL database.

I understand that there is no artificial limit on the number of databases a MySQL server can handle, and in my basic tests there is no measurable performance impact. What I would like to know is: if I allow these databases to be set up without my intervention, what are the security implications?

My thoughts so far:

  • For general usage, the app would connect to MySQL as a user with limited "SELECT", "INSERT", "UPDATE", "DELETE" rights. This user would be granted rights to read/write into each created database.

  • When a new network is created, the app would connect to MySQL as a special user with the "CREATE" privelege, so it can create a new database.

  • I would only allow a new database to be created if I am convinced the person is a legitimate user (email validation, captcha etc).

  • If illegitimate databases are created, I will notice and delete them, and hopefully figure out how to prevent more being created.

With a setup like this, is there any security implications I have not thought of? Is there any danger, other than the possible creation of databases by illegitimate users?

  • 1
    I'm not aware of any security issues regarding your setup (given that you already took care of injection/input validation issues). Even the creation of "illegitimate" databases isn't really a new problem as normal social networking sites also suffer from the creation of illegitimate accounts. However, I see no point of what you're to do. Why not just keep everything in one database? You can restrict access and assign permissions using users and views. – Adi Dec 16 '13 at 10:12
  • I do hope you mean "CREATE TABLE" privilege as opposed to "CREATE DATABASE" privilege... – Shadur Dec 16 '13 at 12:52
  • @Shadur - I meant "CREATE DATABASE", my question is specifically about if there are inherent insecurities in this approach. If resource usage is the main drawback then creating tables is probably just as bad. If you know any major -security- flaws in this approach it would be valuable to share them. – Jason O'Neil Dec 17 '13 at 6:39
  • 2
    Because by default a new database would have no user permissions yet, so you'd have to include GRANT privilege as well. If you restrict yourself to CREATE TABLE then the non-priviliged user will already have all the access it needs and the privileged one won't need GRANT privileges. – Shadur Dec 17 '13 at 7:42
  • @Shadur if I do it I would GRANT priveleges to database names following a certain wildcard, so that GRANT permission wouldn't have to be exposed to user code. Thanks for pointing that out. – Jason O'Neil Dec 20 '13 at 4:01
7

There's no inherent security issues with this architecture. Any risks will come from the implementation errors or vulnerabilities in MySQL.

It sounds like you are considering this method in order to provide separation between different networks' data, however it does sound a bit excessive. I know you tested for performance, however in a large scale this might use more resources, that's a different forum.

Remember, it's all about implementation, you could have all network data in one table and it would be secure as long as you design and code it well.

  • Thanks for the input. My main thought regarding splitting the databases was to make it stupidly easy to provide backups of a network, or to migrate a network from one DB server to another. This can still be achieved with everything in one table, it just requires slightly more work. I'll think some more about it. Cheers for the good answer! – Jason O'Neil Dec 17 '13 at 2:52

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.