Passwords for MySQL; a conundrum??
Posted by admin (Graham Ellis), 22 July 2002Scenario. An on line ordering system, with stock levels held in a MySQL database. Well password protected, with no access to mysqld unless a password is given. BUT users must be able to indirectly reduce the stock level when they submit an order.
Solution: Provide a mysql login that's used by the web server's CGI script / PHP page / Servlet / JSP when it connects as a client to mysqld.
The question then arises "where do I store the mysql password for this mysql client?". For initial testing purposes (and knowing you'll change the password later), you can simply include it in the source code of the client; in PHP you might write:
If you're running a web server for a single site, and the web server tself is secure, you may need to go no further. The source code of your application should never reach your visitor via the web (if it did, you would have a serious web server security problem).
If the web server computer is shared with other users, you'll want to look and see how those users are kept apart (by user no, by group no, etc) and consider the security of your script from reading by others as you decide whether to include a password directly in it.
You may want to consider separating the password out into a separate file, and keeping that in a directory elsewhere on the web server (e.g. having a "private" directory as well as an html anda cgi-bin) which is secured more tightly than the directory that includes the script. Remember that this file needs to be readable by the webserver user (or whatever user name the web server runs under) rather than via the account from which the developer updates the web site. Of course, these are the same security issues that apply to all the data that's held in files on your web site and not just to the password.
We're often asked about schemes for encoding the password rather than holding it as plain text. Certainly, applying some algorithm to the password that makes it non-obvious is cheap, so why not, but such an algorithm needs to be reversible and all the elements needed for that reversal have to be present on the web server if the client program is to access the data without the end user client at his browser having to supply the password. You can certainly provide obfurscation ...
You have a different situation where your site is accessed by users who are required to remember / enter their passwords, and where those passwords are (or form part of) a password to give them individual access to the mysql database. In such a situation, encoding algorithms such as the one supported by "crypt" in many languages, or others, should be used to encode the user's password. When the user re-enters his password, you re-encode it and see if you get the same results. This is known as a "trapdoor" mechanism - there's no way of getting the password back from the encryped string unless you want to keep throwing all possible passwords at the encoding algorithm until you get a match.
In this latter scenario, you are still likely to need to store the password in a transient file somewhere if you have a whole session (number of pages) to run and don't want your user to have to enter the password at every page. Consider also using https (secure http) for the transfers. Finally, you'll also need an "admin" function to reset the passwords of user who forget theirs, itself protected by a password.
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: email@example.com • WEB: http://www.wellho.net • SKYPE: wellho