Solved

MySQL Web Database

Posted on 2014-04-01
5
137 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…
Six Sigma Control Plans

632 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