How to make a MySQL app using only stored procedur

Question

I've read about some application that reforce app security by deniying all mysql user permission except execution. I need a scheme like this because my app needs to do some checks before returning values and triggers are not an option because are not allowed in SELECT queries.

So, the question is: if you are programming an app that just has access to Stored Procedures, how do you struct your app? I've never seen this kind of app and I would like to see what others did before reinvent the wheel.

Answer 1

Just quietly, you will eventually go mental. I've been there (having a stores proc driven app), and it has many downsides, just a few are:

• "Code changes" are actually database changes, which leads to special issues in production, because you have to drop/create your procedures and involve the DBA and you can't do "binary" releases or "hot" releases.
• The expertise you need to do your programming is harder to find (db stored proc vs perl/java/php etc)
• Testing and debugging is much harder - few dbs offer line-stepping debuggers
• You actually have to have a database to run your code - harder to mock/unit test etc
• You are strongly tied to a database vendor - there is no "industry standard" PL/SQL language, so porting is difficult (porting is basically a re-write)
• PL/SQL can only handle the simplest of logic applications - it just isn't suited to business logic

On the up side:

• Typically, db operations can be made atomic and are faster
• Access can be controlled on a fine-grained "action basis", rather than a "data basis"

EDIT: Fine-grained access is usually (and easily) controlled in the app layer.

IMHO, I would never implement using stored procedures at all. There may be a couple of very database-centric issues that can be tackled better using a stored procedure, but the reasons would have to be compelling indeed to justify going there.