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)?