Solved

MySQL Web Database

Posted on 2014-04-01
5
128 Views
Last Modified: 2015-05-01
I am working on a web based application for parents of students to register their students online using Perl/Javascript/HTML.  I have everything setup except for best practice for accounts for our users.  I originally thought I'd create a salt for each user and have each user be a database user in MySQL with simple select, insert, and update rights.  The MySQL server's only role is for this one single database.

But I am seeing others talk about having one master MySQL user that has select rights for the database to look up user account info and compare the Password field for a user account (which is salted) with what is provided by the user at the time of login.

However, if I do that route, I would have to store the master password plain text outside the web root.  Am I off my rocker, or am I missing something.  My Database will have about 1,500 users.

What is the better method of implimentation?  Each user with their own simple MySQL account, or a master select only User that the script pulls the master user/pass from a file outside the web directory so not to be seen, and if the salted user/pass provided by the user, matches the salt for that user in the DB table, allow them access?
0
Comment
Question by:NoodlesWIU
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 39970475
However, if I do that route, I would have to store the master password plain text outside the web root.
It wouldn't have to be plain text--you could encrypt it--but for security reasons it would make sense to keep it at a level higher than the website root.

This is a common practice in IIS on Windows:  Database connection strings are stored in a configuration file, and then encrypted. The encryption key is in a configuration file that is in another directory outside of the web root.
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 39970660
Giving every user (parents/students) rights to the database is probably the worst thing you can do.

Your web app should use one user account to access the database and its password should be stored outside of the webroot.

Any page that sends private data such as passwords should be encrypted (i.e., running over https).
0
 
LVL 3

Author Comment

by:NoodlesWIU
ID: 39973294
@fishmonger-  please explain why it is the worst thing you can do.  I appreciate your help.
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 39975020
If access rights are not setup perfectly, it can open up security holes which can allow the general public (the parents and/or students) read and write access to not only this DB but other DB's on that server.

It puts the user accounts in a completely separate and unrelated DB for no good reason.

It needlessly clutters up the mysql db users table with hundreds or more likely thousands of users which makes maintaining mysql security much more difficult, time consuming, and error prone.

If you ever need to move your web app database to a new server, extracting those users becomes more difficult and error prone.

I'm sure that there are a number of other "gotchas" that I've missed.

The users should be in a table of the web app database and be authenticated when they login, but their account should not have direct logins to the mysql database.

You only need one user account in the mysql db with rights to read and write the web app db.  If you wish, you could have no more than 2 or 3 users accounts with different access rights.
0
 
LVL 3

Author Closing Comment

by:NoodlesWIU
ID: 40754596
Using this recommendation I created a configuration file outside the web directory so only the server had access to it with the username/password stored obfiscated.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question