What security benefits are provided by using stored procedures to access data?

Question

I have seen some guidance which recommends that you secure a database by layering all data access through stored procedures.

I know that for SQL Server, you can secure tables, and even columns against CRUD operations.

For example:

 --// Logged in as 'sa'
 USE AdventureWorks;
 GRANT SELECT ON Person.Address(AddressID, AddressLine1) to Matt;
 GRANT UPDATE ON Person.Address(AddressLine1) to Matt;

 --// Logged in as 'Matt'
 SELECT * from Person.Address;                       --// Fail
 SELECT AddressID, AddressLine1 from Person.Address; --// Succeed
 UPDATE Person.Address SET AddressLine1 = '#____ 2700 Production Way' 
        WHERE AddressID = 497;                       --// Succeed

Given that you can secure tables and even columns against CRUD operations, how does using a stored procedure provide additional security, or management of security?

Answer 1

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

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

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

Answer 2

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

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

Answer 3

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

UPDATE Person.Address SET AddressLine1 = NULL

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

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

Answer 4

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

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

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

Answer 5

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

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

Answer 6

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

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

Answer 7

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

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

Answer 8

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

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

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

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

Answer 9

Don't use them

Answer 10

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

For example, you have a feedback system. Feedback can only be submitted after the administrat started the feedback campaign. It is simply updating a flag in some table. Then when user comes to submit feedback, SP can check if the 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