1

I took up programming a couple of months ago (alongside my day job) and am struggling with a couple of basic concepts. Currently, I'm trying to learn how to build a database based PHP login/logout for a web app.

Can anyone help me understand the concept of database users? The way I see it, there is:

  • a database server (which serves all the databases created, sort of like an "OS serves all the apps installed")
  • all the databases, with their tables inside
  • users (? this is where it gets confusing for me - how many and which users do I need?, what are basic good practices?)
  • users from the table "users", which will get into this table by registering on the website are not the same as users that the app uses to connect to the database? Or are they?

I apologize, I understand that these are basic concepts but I find the available web tutorials lacking in good concept explanations. Everyone seems to want to jump right into the coding part asap whereas I find that programming is more about ideas and concepts and then about syntax and languages.

Many thanks for any reply! Alex

  • 1
    Are you talking about your application's users, or the actual principals that authenticate on the database and server? Or is that your confusion? – Thomas Stringer Jan 10 '15 at 13:26
  • That's the confusion Thomas :) yes, but after your Q it's a bit clearer. I am talking about the users that are visible in phpMyAdmin under the "Privileges" tab. Where can I read more about those, how do I set these up, when do I need to set up new ones, when to use existing ones? I see there is a "root" user which is (I assume) used by MySQL server to access ALL the databases inside it. For each app/web I need to create another user and grant it privileges just for the database of that particular app, am I right? – Alex Starbuck Jan 10 '15 at 14:09
  • @AlexStarbuck You've pretty much got the hang of it. MySQL users are internal to the database and allow you to enforce different permission levels. If you want to get more technical as time goes on, you can have multiple users per application to enforce different security/performance characteristics (like one user for OLTP and one for OLAP). – mgw854 Jan 10 '15 at 15:24
2

There is actually a third type of users, perhaps that is causing some of your confusion.

  1. There is the SQL User, aka login, in companies these may be Active Directory users or groups. This type of user has the right to connec to the SQL database engine and one or more databases. This user does not have to use the application to do whatever he can do.

  2. The database user. This is typically linked to a login, and is used as the basis for defining database rights. Are you denied from reading table B, allowed to Select from table F and update table U and delete rows in D? A typical public facing web application will have one or two database users. Watch out for the anti-pattern of making that user be the database owner. Multiple users/logins is more common for company reporting or desktop apps. Again, this user does not have to be using the applicaion to do whatever he can do. Other tools can be used.

  3. The registered user. This is the user that has registered with the application, and is then given permissions to do certain things inside the application. This user can not directly connect to the database engine, all interaction is through the application. Which is why the login/database user should not be dbo, but instead have restricted permissions. You have probably heard of little Bobby tables by now, if the user that the application uses to connected to the database wasn't dbo, the students table wouldn't be dropped.

3

There are two classes of users here:

  1. users of the database
  2. users of your web application

They are entirely different. (Or should be.)

The database users would be on the order of root (the database "superuser") and wordpress (if you were running WordPress, say, and chose that as the name to represent WordPress when connecting to the database). All of your application's requests on the database would funneled through just one (or a few) database user accounts.

Then your application would have its own table (myapp_users say, or wp_users for WordPress) that stores the application users.

I have seen applications that conflate these two kinds of users, and depend on the database access mechanisms entirely. That is a very, very, very bad idea, violating a number of least privilege, separation of concerns, and isolation of failure domains principles.

Your basic question answered, I'd like to also address an important unmentioned issue / "elephant in the room": Building user identification, authentication, and authorization is very difficult and tricky to get right. Simple implementations almost invariably fail to protect security and privacy. First-time and novice implementations, even more so. If you are developing code to be used in any production setting, please consider depending on an existing, proven library such as Laravel, Opauth, or uLogin to manage user login instead of writing your own. (I'm not trying to endorse any specific framework here; just the idea of not writing user management/login yourself.)

  • I'd say conflating the two actually makes sense in some limited scenarios when you can ensure that each user has access to exactly what they are supposed to, and each user may have different levels of access to different database reason (I've seen this done in a reporting context where it actually locked down certain reports/tables/columns based on user). But +1 for implementing authentication solutions. If you think you know what you're doing, that's when you're screwed. – mgw854 Jan 10 '15 at 16:51
  • 1
    When you say "Simple implementations almost invariably to protect security and privacy", do you mean "Simple implementations almost invariably *fail* to protect security and privacy" ? Verb is missing. – Tulains Córdova Jan 10 '15 at 17:44
  • +1 for stating you shouldn't build your own login-system. Other, dedicated, systems are probably batter at this as you can create yourself. – Jan_V Jan 10 '15 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.