Help me help users identify themselves properly to mysql

"Access denied for user 'Oscar'@'localhost' (using password: YES)." Means that you are failing to log into the database at all. Are you hashing your MySQL password before you send it to the database? If you're doing that, MySQL will re-hash it to compare it to the stored value for that user, and it will fail. Again, this isn't on your login script, this is on your mysql_connect() details. Make sure you have the right password, and that you're not hashing it before passing it.

Interesting. The book lists connection checks like if (!($connection = @ mysql_connect("localhost", "fred", "shhh"))) showerror();, but I can't leave that hard-coded, else every user is authenticated as, say, "Oscar," but actually interacting with the database as "fred." How do you pass the plaintext password back to all pages after login without storing it as a session variable in plaintext? I'd gotten the impression from a metatalk thread awhile back that plaintext passwords was a bad bad bad idea.

How do you pass the plaintext password back to all pages after login without storing it as a session variable in plaintext? I'd gotten the impression from a metatalk thread awhile back that plaintext passwords was a bad bad bad idea. That's the point of generating the MD5 hash. You're only passing a temporary session authentication token to the application, which--if written correctly--will always cross check to see that the token you pass to it corresponds to the hash which was generated from the original password authentication stage. In other words, you need to store the temporary session auth hash in a table, in order to be able to do this crosschecking.

I may be misunderstanding what you're asking, but I think you're having trouble abstracting the actual MySQL connection with any application-level authentication system. For example, I have a web site where users can log in to edit their profile. Their user names and passwords are stored, the passwords are md5 hashed and that's that. But in order to retrieve any data from the database, I have to log in through mysql_connect with my MySQL user name and password; different from any application-level authentication schemes I may have built. You have no reason to pull your password back to the pages. The script will run, the MySQL connection will be established and authenticated and that will be that. Typically, you store this in a configuration file of sorts that is outside of the web root, built into a PHP file that returns nothing and is only plaintext there. Moral of the story: When creating a user system, you only EVER have one single MySQL user. The database has its own set of users, permissions and authentication and your application should have its own separate layer. The MySQL user is only ever interacted with on the backend, and should never be visible to the user at all. Now, if your application is being built to interact directly with MySQL, ala phpMyAdmin, then you'll use MySQL's tables for user management. But those are already built natively...

I think I'm definitely not understanding. Fred, Barney, and Wilma all login with their own credentials but every page interacting with the database passes the hardcoded credentials "Betty" and "Bettyspassword" instead? Is that right? What are the distinct users in MySQL for, then? What condition would prompt someone to grant or revoke update, select, and so forth?

Yes, almost certainly you want to be having authentication and access control in the app itself, not faffing about making MySQL users for people. Note, you should always http://en.wikipedia.org/wiki/Salt_(cryptography) your passwords, ala: salt = randomstring(); hashpw = md5(salt + password); Then store the hash and salt. This ensures that 100 users all using "password" don't get the same stored hash, and makes brute force and http://en.wikipedia.org/wiki/Rainbow_tables attacks much more difficult.

MySQL users are access control for talking to MySQL directly; e.g. a hosting company might give each account on a shared server a seperate MySQL user with access to their own database, or a single user might allocate different applications their own accounts to keep them isolated from each other. This probably isn't the system you want to be interacting with when providing accounts to people on a web application -- the web application authenticates itself with the database, and from there it has its own authentication system for the application's users, based on tables it manipulates in its own database.

Wow, this sounds much easier than what I'd had in mind, and also makes me wonder what other assumptions I'm making which are fundamentally flawed. The book did mention salting the hash, but I hadn't yet understood how to reproduce a random salt on login (I'd thought that there wouldn't be much point in storing the salt with the hashed password if the table itself is vulnerable). Thanks for the clarifications, everyone.

What are the distinct users in MySQL for, then? What condition would prompt someone to grant or revoke update, select, and so forth? The "distinct users" (i.e, the Users table in the "mysql" database), for all intents and purposes, work under the same concept as a Unix password file. There is always at least one superuser, who can do anything with the system, but you can also delegate other users who can still have access, albeit in a limited way that you, as the superuser, define. So, for example, you can have a "distinct" MySQL user that only has select privledges, but no update privledges. What I think is happening here is that you're confusing "MySQL users", with "People who need to access data which is stored in the MySQL database". The former is a backend, MySQL system level user, and the latter is a front-end, application-level user (i.e. the data that your PHP/Perl/Rails etc etc app needs to work). The PHP mysql_connect() directive is referring to the MySQL system level user...in other words, a user defined in the "user" table, and NOT a front-end "user" for your web-app. It's a bit confusing, i know, but maybe http://www.databasejournal.com/features/mysql/article.php/3311731 will do a better job than I can explaining it.