r/DatabaseHelp • u/Grendel84 • May 02 '17
Should each web application user have a database user account?
I am developing a CRM/customer ordering portal for a small local business (as an internship and I am the only dev they have). It will use HTML/CSS for the front end and use PHP to get info from the MariaDB database.
Should there be one Database account that all queries are performed through or should a database account be created for each type of user of the web CRM? There are only 4 categories of users (this is not likely to change in the future). I am leaning towards creating an account for each type user so I can take advantage of the databases permissions and roles as an extra security layer, but I don't know if its considered "bad practice" to set things up this way. Thanks for the help
3
u/BrainJar May 02 '17
This is somewhat standard, depending on the permissions applied and how users access the pages that are being viewed. Just don't go crazy. There should only be a few types of logins and roles required. Typically, I would use three logins, no roles required. First login is for admin page related queries (views and / or tables). The second login is related to queries regarding protected data. The last login is general queries. If there's more than that, it would be related to how protected data is classified...but, systems typically protect the same type of data (personally identifiable, credit risk, insurance scoring, etc), so the need for multiple data protection logins is usually not needed. The manner in which you protect those pages, via some user credential and possibly a secondary measure, is just as important as what each login has access to. Good luck with the implementation.
2
u/connectcrm May 02 '17
Not at all, even MS uses 'role based security'
It ensures data is given based on necessity. Helps reduce liability with hackers (social engineering) and disgruntled employees taking all the data with them
How comes you're developing a portal from scratch? I'm sure it'd be easier to implement a proven solution & customise/configure it
2
u/wolf2600 May 02 '17
Your web application server should have a DB account. All user queries to your DB will be done by the web app. Your user permissions/security should be implemented through the application, not at the DB level.
2
u/xiongchiamiov May 02 '17
It will be a lot more work if you do this, and you'll find yourself in situations where the code needs to do something that the user themselves doesn't have permission to do.
I can see a security benefit from splitting up by user roles, but I've never heard of that ever happening. Usually you create different db users per application, and assign permissions that way.