Does Use of Stored Procedures Improve Security?

[cn4126]

Hi- is there any database security related (or other) benefits by utilizing stored procedures rather than queries in generating reports in Jasperreports? I am using MySQL as my data source

Thanks

[jgust]

My shop solely uses procedures for reporting 700+ reports.

Benefits:

• Data logic is not tied to the presentation object.
  This becomes important when/if you ever change the report engine.  In the last 10yrs, we went from Cognos, SAP BOBI, Crystal Report, and finally working on Jasper.  Having to rewrite 800+ reports all having their own tables/views would be a challenge.  It is difficult enough now having to deal with the presentation and scripting (formula vs expressions) differences.
• The procedure can address some presentation instead of having a formula do it.
  Consider having a UserFname and UserLName field.  You could combine that in the procedure as User_FullName so that you don't need to write another bit of code in the engine to combine them together.  It's easy enough to do it in the engine but when you have 800+ reports these easy things cost you time especially when/if you change the engine and have to do it all over again. 
• Security
  You can create a read-only report user that has permission to execute specified report procedures.  This moves a level of security to the db while you could also add another level on top of the engine.
• Performance Tuning
  Depending on the db engine you will have more control of how the SQL is executed then you would have if you let the engine make all of the decisions.  One scenario that comes to mind is we have a scaling issue.  Some of our reports take 5+hr to run due to the complexity and amount of data.  We were able to analyze the issue with our dba and identify that we could rewrite the query using CTE's and also add an Oracle HINT to make a suggestion to the optimizer for a better approach.  The report now takes under 2hrs.