Stored Procedures

Developers can use Stored Procedures to create specific views on queries to prevent sensitive information from being archived, rather than using normal queries.

By creating and limiting access to stored procedures, the developer is adding an interface that differentiates who can use a particular stored procedure from the type of information he/she can access. Using this, the developer makes the process easier to manage, especially when taking control over tables and columns from a security perspective, which comes in handy.

Let's take a look at an example...

Imagine you have a table with information regarding user passport IDs.

Using a query like:

SELECT * FROM tblUsers WHERE userId = $user_input

The problems of Input validation aside, the database user (for the example's sake, the user is called John) could access ALL information from the user ID.

What if John only has access to use this stored procedure:

CREATE PROCEDURE db.getName @userId int = NULL
AS
    SELECT name, lastname FROM tblUsers WHERE userId = @userId
GO

Which you can run just by using:

EXEC db.getName @userId = 14

This way, you know for sure that user John only sees name and lastname from the users he requests.

Stored procedures are not bulletproof, but they create a new layer of protection to your web application. They give database administrators a big advantage over controlling permissions (e.g. users can be limited to specific rows/data) and even better server performance.