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
3
There is actually a third type of users, perhaps that is causing some of your confusion.
-
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.
-
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.
-
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.
There are two classes of users here:
- users of the database
- 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.)
3