Helping ordinary people create extraordinary websites!
| |
Helping ordinary people create extraordinary websites! |
Stored Procedures are EVILBy Tony Marston2006-09-07 
Stored procedures are more secure
This is a common argument that many people echo without realising that it became defunct when role-based security was made available. A good DBA defines user-roles in the database, and users are added to those roles and rights are defined per role, not per user. This way, it is easy to control which users can insert / update and which users can for example select or delete or have access to views in an easy way. With a view it is possible to control which data is accessed on a column basis or row basis. This means that if you want user U to select only 2 or so columns from a table, you can give that user access to a view, not the underlying table. The same goes for rows in one or more tables. Create a view which shows those rows, filtering out others. Give access rights to the view, not the table, obviously using user-roles. This way you can limit access to sensitive data without having to compromise your programming model because you have to move to stored procedures. It is also said that stored procedures are more secure because they prevent SQL injection attacks. This argument is false for the simple reason that it is possible to have a stored procedure which concatenates strings together and therefore open itself up to sql injection attacks (generally seen in systems which use procedures and have to offer some sort of general search routine), while the use of parameterized queries removes this vulnerability as no value can end up as being part of the actually query string. Tutorial Pages: » Stored Procedures are EVIL » Stored procedures are not as brittle as dynamic SQL » Stored procedures are more secure » Stored procedures are more efficient » The company has paid for them, so why not use them? » Application code or database code - it's still code, isn't it? » It mangles the 3 Tier structure » Stored procedures are a maintenance problem » Stored procedures take longer to test » BL in stored procedures does not scale » Stored procedures are not customisable » Database triggers are hidden from the application » Version Control » Vendor lock-in » References
|
 |