SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Mysql Paranoia

  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Mysql Paranoia

    Hello!

    I have a question about database security. I'm building an application that will have about 100 "admin_users". Each of the 100 users will be tied to a database; my scripts then interact with each of the databases, depending on the admin_user, and retrieve information specific to them. I thought that it would be a good measure of security to set up 2 different username/passwords for each of the databases (one for reading the database and one for writing to the database). Further, I have my database config file outside of my webroot, and away from prying eyes. By having a different password for each database, I thought that if one database got "compromised", none of the others would be affected. On the other hand, since my config file is outside of the web root, is it not true that the ONLY way to get this info would be by gaining access to that file? And, if this is the case, it would seem that I'm only "thinking" that it's more secure by creating 100 different passwords; if they gain access to my config file, then they have access to everything.

    So, should I just keep things simple with one username/password for reading and one for writing or are the 100 versions of this worth it?

    Thank you,

    Eric

  2. #2
    Founder of Primal Skill Ltd. feketegy's Avatar
    Join Date
    Aug 2006
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to think of multiple layers of security here for different scenarios.

    1. If the hacker gains access to your list of 100 admins with usernames / password (btw, encrypt + salt your passwords) would it be enough to hack all 100 DBs?

    2. If a hacker can hack your server and get the db config file, what else does he need to do to hack those databases? Does he also need to know about the db servers IPs or something else?

    3. If a hacker can hack your application and get access to the source code. Is that alone is enough to gain access to all those databases?


    Always try to implement multiple layers of security, so hacking one layer (application, db, server, etc.) alone is not enough to gain full control.

    For ex.

    Your users passwords are encrypted + salted, let's say by a common salt (which is not that secure). The password hashes are stored in the users db table, but the salt token is stored in a config file in your source code. That's 2 layers of security, because if the hackers get's access to your db directly downloading your users table that's not enough to hack the password hashes without the salt which is in your source code.

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you for raising questions for me to think about, which will lead me to implement appropriate actions for my site.

  4. #4
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Are you saying you will create a separate database for each of those 100 users? This looks like a maintenance nightmare.

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Can I ask specifically what maintenance issues you foresee? The only one that I've come up is if I need to change something (a table or column) on all databases: however, to get around this, I can just loop through via PHP.

  6. #6
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kreut View Post
    Can I ask specifically what maintenance issues you foresee? The only one that I've come up is if I need to change something (a table or column) on all databases: however, to get around this, I can just loop through via PHP.
    That is one of the issues I had in mind. But what about other data, how are you going to access data in other tables which are not connected with any user (universal)? If you want to change any of those data how are you going to synchronise it for all other users? If the logged in user has access only to one database then to keep the security your application should not be allowed to access other tables.

    Another one would be adding/removing users - you would need to create and drop databases for this operation, which require CREATE/DROP DATABASE priviliges - and recreate all tables on the new database. Are you going to do this manually?

    Also, things like listing all users to see which ones have been logged on lately, or for any other reason like statistics, getting their data, etc. That's all awkward if they are scattered accross databases.

    I'm not really saying this approach is wrong as I'm not a security expert, I just can't wrap my head around such an extreme solution. And I'm not sure how much security you can really gain from this. I don't think that banking systems ever create a separate database for every one of their millions of users - there must be better ways for securing the database. I'd be glad to read what people who are more expert on security than me have to say.

  7. #7
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i appreciate all of your comments! I'm actually going to be the "super admin user" (it's my site) and will take care of all the super admin stuff such as creating/dropping databases (with php scripts in place to make life easy). Statistics are actually going to only be important on a per database need and I have tools in place to get that going. Without going into all of the gory details, I set up the system the way that I need because conceptually it was easiest for me to see what was going on with the different admin users.

    And, to carry forth your bank analogy, for what my site is accomplishing, I'm not actually setting up a database for each user, rather I'm setting up databases for 100 banks in the country, and each of those databases then has their own users (potentially 1000's per "bank").

    However, it seems that the 100 password approach is in fact a bit extreme and awkward to deal with!

  8. #8
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kreut View Post
    And, to carry forth your bank analogy, for what my site is accomplishing, I'm not actually setting up a database for each user, rather I'm setting up databases for 100 banks in the country, and each of those databases then has their own users (potentially 1000's per "bank").
    Well, that different then - if you think that complete separation of data for each of your 'banks' is a good idea then it may well be so!

    However, it seems that the 100 password approach is in fact a bit extreme and awkward to deal with!
    I'd rather go with 1 password and change it often. I think there are other more important things for you to do as the super admin like securing your server, setting up proper permissions, secure connections, etc. in other words not letting anyone to gain access to your server, if he gets it then even a million passwords may not help you

  9. #9
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    True enough! Enjoy the rest of your week...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •