Noob question about user accounts

Question

I'm new to this whole database thing. Got a uni project which I was hoping y'all could help with.

So I'm creating a webapp that is essentially an online art gallery store. The database will contain an inventory of items and possibly a customer list (this is what I'm not sure about). I want to allow for various levels of manipulation to the inventory list.

I want user 1 let's call that "sales" to add new items, edit and delete. I also want sales to to the same for a customer list. I want user 2 "artists" to be able to add new, edit what they added and delete what they've added to the inventory list, along with their own info. I want user 3 "customer" to be able edit one field in the inventory list (quantity required), along with their own info.

So would this come under rules and privileges?? I want an admin account to oversee the db and all connections. Would I need to create separate rules for all others? Do i add type 1 "sales" as admin with certain rights? Can I contain new users type 2&3 in a customer list and have that list manipulate the inventory list with rules? E.g. So if customer list is type 2, user is an "artist" and can add new stock, but if they are type 3, they are "customer" and can only edit quantity.

Again I am a noob. I hope what I'm asking makes sense. Thanks for your help kind folks.

Answer 1

In a scenario like this, there are often two completely separate kinds of "user accounts".

First you have your database's user account - this is (or at least, should be) required for the webserver to access the database.

Then, you have your application's user accounts. Everyone using the application should have their own account.

While it may be possible to assign individual user accounts for all application users to the database itself, this is often not practical. In most cases, I would keep these two things separate.

So, your application would have its own user table, with passwords and other user information. I'd also have a role table, listing out the types of users you can have: sales, artist, or customer. You would add the id of the appropriate row in the role table into the user table, and would determine what options are provided on-screen based on those roles.

At the database level, you could use just one user. That user has sufficient access to the database to run any commands any user might need; control of which users can run which commands is based on the application-level user.

You could have 4 users. One, "login" (use better names, by the way) would only be able to access the information required to authenticate your application-level users. Once they're authenticated, you could have one user for each of the three roles, with their rights within the database limited by what's needed for that role.

Practically speaking most websites I've worked on (including e-commerce sites) have used a single account for all DB connections, and controlled access at the application level.

If this is for a class assignment, and the class isn't about security, then I'd keep things simple (at first at least - you can play around if you've finished the basics early - you don't want an over-complicated security system to prevent you from completing the basic course requirements). Worry more about SQL injection attacks and that sort of thing; even with the four DB user solution suggested, one artist could potentially use a SQL injection attack to destroy other artists' data.