Solved

MySQL Web Database

Posted on 2014-04-01
5
106 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
  • 2
  • 2
5 Comments
 
LVL 74

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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 …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now