Multiple Database Accounts; Good practice or waste of time?

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Multiple Database Accounts; Good practice or waste of time?

Post by ReverendDexter »

I have an app that's accessing a MySQL database. In the database, I have created three users, one for someone not logged in, one for someone logged in, and one for someone logged in for admin. Each of these users have differing permissions on the tables, i.e., not logged in accounts cannot write to the login table, whereas admin can.

However, I'd like to create functionality such that a non-logged in user can "request" an account. The way I've thought of doing this is to have an account request add the row to the table, but this would require that they have write access to the login table, which I've been trying to avoid. The only other thing I've thought is to create a purgatory table, from which a trigger would fire on insert and add the row to the login table; this idea seems to me to be a lot of superfluous code that really doesn't get me anything beyond an extra table

Am I being overly protective of the wrong things? It would make my life a lot easier to just ditch the multiple users bit, and just have a single MySQL log in.

Is there somewhere I can read best practices for situations like this so I can stop bugging everyone?

-Dex
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

I handle permissions and such through PHP. The most the database has is the sessions which hold a serialized user object, a table of user credentials, and a table of permissions corresponding to user ids.

You check against the credentials to make the user object (which is either anonymous, valid user, or administrative user), and when they need to do things that require permissions, check for their user id (from their user object) in the permissions table.
User avatar
dbevfat
Forum Contributor
Posts: 126
Joined: Tue Jun 28, 2005 2:47 pm
Location: Ljubljana, Slovenia

Post by dbevfat »

Not an easy answer, but one thing is for sure: it can't replace the application-level security logic. You'll always have write access to some critical tables. "Application user can update only his own record" translates to "the appropriate database user has write-access to users table", which also means the application user can update other records as well.

On the other hand, having more types of users with different responsibilities, one can use different db-users that have access to completely different tables (apart from users-table and others alike), which is good as far as security is concerned.

When you think about it, all you're actually doing is laying a safety net for when your application logic fails -- i.e. user with wrong privileges executes an action that he shouldn't. If your application-level permission layer fails, the error will be caught at a database level (not always, as is the case with "users" table, but could work for "bills"). There is some additional security, but not enough to rely on it. The way I see it, the application should be as secure with one database user as with many anyway.

Also, if you test your permission logic thoroughly (which you should), it just may not be worth the additional work. I've rarely used more than two users (one for public access, one for back-end application), so I can't really tell, but it seems sufficient. If I wanted to secure the application as much as possible, I'd go for it, but to me it seems like premature optimization of security :), at least for the "classic web page". I can imagine an intranet application that has more use for this approach, though.

regards
Post Reply