I'm very new into db development and currently working on my first production app.
I learned that I would need a business logic layer (BLL) to authenticate and authorize users, for example:
John can only query the database while Andrew can insert new records.
Following are my questions that required clarification:
- Does it mean the BLL would have to connect to the database with greatest privilege necessary, instead of least privilege needed for each user?
- Will the BLL need INSERT permission to provide service to Andrew, which is more than enough for John?
- Can we solve this potential flaw (except by securing BLL better, which I would of course do)?
For example, implement authorization in database layer (as described here)?
Best Answer
Database engines have no standardized access layer with enough resolution to allow direct use of the database from client apps.
The databases locking mechanism and indexing also have to be taken into account. For example a report query on SQL server or db2 will lock tables and delay all other queries on the same tables.
The purpose of the business layer is to mediate the access to the database and sharp those edges. Some people do that as stored procedures but that makes the application too tied on a specific vendor. I offer no criticism about that. But many others here will.
On my web applications, I put the logic in the server side and access the database directly from the PHP/j2ee webservers. My philosophy is the database has to have the constraints and triggers to ensure the data quality otherwise an rookie developer can break the data model easily.
But yes, mostly the end user will authenticate against the web layer while the servers will authenticate against the database using power users. The database users are focused on which tables, views and functions the server side app can use.