Solved

MySQL Web Database

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
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…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

919 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

18 Experts available now in Live!

Get 1:1 Help Now