[ACCEPTED]-What security benefits are provided by using stored procedures to access data?-stored-procedures

Because by limiting all access to those 28 stored procs you have established a defined 27 interface to the database, through which 26 all access must occur... Since you will 25 have DENY'd Direct select, insert, update, and 24 delete operations against the tables and 23 views, noone can directly write sql of their 22 own design that does whatever they want 21 to... If you want to limit inserts into 20 the employee table where the employee is 19 assigned to more than three projects to 18 to only those employees that have a score 17 greater than 85 on a proficiency test, then 16 you can write that constraint intoi the 15 SaveEmployee sproc, and have it throw an 14 exception to any client code that attempts 13 to do that...

Sure you COULD do the same 12 thing using client-side code, but using 11 sProcs makes the process easier to design 10 and manage, cause it's all in one place, and 9 ALL applications that attempt to access 8 this database system HAVE to conform to 7 whatever constraints and/or security provisions 6 you define in the SProcs... No rogue developer 5 writing a new separate client app that hits 4 the database can ignore or work-around a 3 constraint or security provision in a SProc 2 if that SProc s the ONLY WAY to insert or 1 update a record...

You might not want to give Matt carte-blanc 12 to update certain tables or columns directly. What 11 if Matt decided to do this:

UPDATE Person.Address SET AddressLine1 = NULL

Whoops. Matt 10 forgot the WHERE clause and just hosed your 9 database. Or maybe Matt just got pissed 8 at his boss and has decided to quit at the 7 end of the day. Or maybe Matt's password 6 isn't as secure as it should have been and 5 now a hacker has it.

This is just one simple 4 example. Control over tables and columns 3 could become much more complex and might 2 be untenable through anything other than 1 stored procedures.

Stored procedures provide additional security 16 by allowing users to perform CRUD operations 15 (insert, update, delete) but only in a limited 14 fashion. For example allowing user Matt 13 to update the address of some rows but not 12 others.

It allows you to add data checks 11 to make sure that the data inserted is valid 10 data, not random garbage. For most things 9 you can use constraints and or triggers 8 to do some of this work, but there are limitations. Stored 7 procedures enhance security by ensuring 6 that operations being performed are allowed 5 by the user.

It's easier to track changes 4 to the database though a single point of 3 access, controlled by your applications, rather 2 than through any number of interfaces. And 1 the procedure can update an audit log.

In SQL Server you do not have to grant any 30 direct access to tables if you properly 29 use stored procs (that means no dynamic 28 SQl). This means your users can only do 27 thoses things defined by the procs. If you 26 have any financial data at all in your database 25 or data of a sensitive nature, only the 24 fewest possible number of people (generally 23 only dbas) should have direct access to 22 the tables. This seriously reduces the risk 21 of fraud or disgruntled employees trashing 20 your business critical data or employees 19 stealing personal inmformation to commit 18 identity theft. In accounting terms this 17 is a necessary internal control and developer 16 convenience or personal desires to do everything 15 dynamically from the user interface should 14 be trumped by the insecurity of of the data. Unfortunately 13 in all too few companies, it is not. Most 12 developers seem to only worry about outside 11 threats to their data, but internal ones 10 are often far more critical.

If you restrict 9 the user at the table level and then the 8 user fires off a query to do a legititmate 7 insert, it won't happen. If you give them 6 the rights to do inserts, then they can 5 do any adhoc insert they want and aren't 4 just limited to the ones coming from the 3 user interface. With stored procs, they 2 can only do the things specifically defined 1 by the proc.

In most (all?) RDBMS's you can 'GRANT' access 12 on specific tables to specific users. A 11 stored procedure can run as a different 10 user, one with greater access. But the 9 Stored procedure is not the same as giving 8 access to the whole table, rather it could 7 first check some things and only return 6 rows that match your particular security 5 concerns.

You might be able to do similar 4 checks with a view but stored procedures 3 are usually much more flexible since they 2 can run almost any SQL - compare the results 1 and decide what rows to return.

The stored procedure is better because the 10 security on the stored procedure (IIRC) will 9 trump security on the tables/columns.

For 8 single-table access, that's not a big deal. However, if 7 you have an operation that involves multiple 6 columns on multiple tables, having one access/deny 5 flag for a table/column might not work for 4 all situations.

However, a stored procedure 3 can perform the composite operation and 2 you can set the security appropriately on 1 that.

Simply put, it lets you define security 10 functionally rather than structurally. In 9 other words, it restricts what a user is 8 allowed to do (with a greater degree of 7 granularity) rather than what database objects 6 are accessible (at very coarse granularity.)

Note 5 that we're speaking of "security controlled 4 by the DBA", rather than by the site or 3 system administrator or software module, all 2 of which are useful and part of the overall 1 security infrastructure.

The first benefit, discussed at length here, is 20 better control of permissions - users can 19 be limited to specific rows, not just per 18 column (which btw is heck to manage in a 17 large system); SPs can enforce business 16 logic and transactional logic; data might 15 be only retrieved dependant on other data 14 (e.g. a join); updates might be limited 13 to single rows at a time; etc.

Second, this 12 can provide an additional layer of protection 11 against SQL Injection (albeit its not complete 10 and automatic). While this may be broken 9 be dynamic SQL inside the SP, or by bad 8 concatenation calls, the SP does enforce 7 parameter types and whatnot, separating 6 code from data.

Third, it comes down to control, at 5 the development phase - typically you'd 4 have trained DBAs writing the SPs, as opposed 3 to programmers (who are trained in code...)

This 2 is, not to mention, non-security benefits, such 1 as better performance.

In stored procedures, you can add logic 9 controls. You can return a error code if 8 something is not right instead of update 7 table data directly.

For example, you have 6 a feedback system. Feedback can only be 5 submitted after the administrat started 4 the feedback campaign. It is simply updating 3 a flag in some table. Then when user comes 2 to submit feedback, SP can check if the 1 flag is set.

Select @IsFeedbackDefined = IsFeedbackDefined From sometable where ID = @ID

IF @IsFeedbackDefined is Null or @IsFeedbackDefined = false 
Begin
    Return -2   --can not submit feedback
End