Implementing SQL Server Security with Stored Procedures and Views

By: K. Brian Kelley   |   Updated: 2020-09-03   |   Comments   |   Related: > Security

Problem

I understand that, through the use of SQL Server ownership chaining, I can restrict access to the underlying tables with data while still allowing applications to query and modify data. How does this work? Are there any examples which I might be able to use in my own code?

Solution

Ownership chaining is a great way to prevent direct access to the base tables. If you're not familiar with ownership chaining, in SQL Server, when one object refers to another object, and both objects have the same owner, SQL Server will only look at the security on the first object. For instance, if a stored procedure references a table, SQL Server will only check security on the stored procedure and not the table, as long as both objects have the same owner.

This allows us to control access through stored procedures and views and never give users direct access to the base tables. This effectively allows us to hide columns, control how data is queried and modified, and even perform business rules checks or complex data integrity rules. Let's take a look at some examples where this comes in handy.

SQL Server Views - Hiding a Sensitive Data Column

Let's take the following T-SQL code:

CREATE DATABASE TestDB;
GO 
 
USE TestDB;
GO

CREATE SCHEMA HR;
GO 

CREATE TABLE HR.Employee
(
   EmployeeID INT,
   GivenName VARCHAR(50),
   Surname VARCHAR(50),
   SSN CHAR(9)
);
GO 

We have a table with an obviously sensitive column, SSN. If you're designing solutions, columns like this should never be stored unencrypted. There are plenty of ways both inside and outside of SQL Server to properly encrypt this type of data. However, let's make the assumption that you're working on an existing system and the fact that HR interns were able to run reports and retrieve Social Security Numbers was noted as an audit finding. How might you be able to remediate this without breaking any applications that use the underlying table? We can use a view.

CREATE VIEW HR.LookupEmployee
AS
SELECT 
   EmployeeID, GivenName, Surname
FROM HR.Employee;
GO

The view doesn't include the SSN column, meaning if a user queries the view (such as through a canned report), the user can't see the SSN column. So far as the view is concerned, it doesn't exist. As long as the application doesn't grant access to the table, we're in business. Let's assume that the application doesn't grant direct access to the tables. We can then grant access to the view and this will prevent retrieval of the SSN column. Let's also create a test user to prove out our security model.

USE TestDB;
GO 

CREATE ROLE HumanResourcesAnalyst;
GO 

GRANT SELECT ON HR.LookupEmployee TO HumanResourcesAnalyst;
GO 

CREATE USER JaneDoe WITHOUT LOGIN;
GO 

ALTER ROLE HumanResourcesAnalyst
ADD MEMBER JaneDoe;
GO 

And now let's run some tests. First, against the view.

USE TestDB;
GO 

-- This will work
-- JaneDoe has SELECT against the view
-- She does not have SELECT against the table
-- Ownership chaining makes this happen
EXECUTE AS USER = 'JaneDoe';
GO 

SELECT * FROM HR.LookupEmployee;
GO 

REVERT;
GO 

The query executes without error.

And now the table.

-- This will not work
-- Since JaneDoe doesn't have SELECT permission
-- She cannot query the table in this way
EXECUTE AS USER = 'JaneDoe';
GO 

SELECT * FROM HR.Employee;
GO 

REVERT;
GO

This time, as expected, the query fails. This means we've successfully protected the column from any HR interns.

Changing Data Through a SQL Server Stored Procedure

Our ability to restrict access to data is not just limited to read operations. Let's take a look at the case of using a stored procedure which permits an HR recruiter the ability to insert a new employee using a stored procedure, while still protecting the underlying table. First, we'll need to defined the stored procedure.

CREATE PROC HR.InsertNewEmployee
   @EmployeeID INT,
   @GivenName VARCHAR(50),
   @Surname VARCHAR(50),
   @SSN CHAR(9)
AS
BEGIN
   INSERT INTO HR.Employee
   ( EmployeeID, GivenName, Surname, SSN )
   VALUES
   ( @EmployeeID, @GivenName, @Surname, @SSN );
END;
GO 

We also want a new role, this one covering HR recruiters, who should have more permissions than an analyst. In this case, we're going to go ahead and give the recruiter the ability to execute any and every stored procedure in the HR schema.

CREATE ROLE HumanResourcesRecruiter;
GO 

GRANT EXECUTE ON SCHEMA::[HR] TO HumanResourcesRecruiter;
GO 

CREATE USER JohnSmith WITHOUT LOGIN;
GO 

ALTER ROLE HumanResourcesRecruiter
ADD MEMBER JohnSmith;
GO

We have our stored procedure, we have our security set up, now let's test JohnSmith. John Smith should fail when inserting directly to the table. We didn't give any permissions, so by default he has none.

-- This will fail as JohnSmith doesn't have the ability to
-- insert directly into the table.
EXECUTE AS USER = 'JohnSmith';
GO 

   INSERT INTO HR.Employee
   ( EmployeeID, GivenName, Surname, SSN )
   VALUES
   ( 557, 'Michael', 'Cooper', '3343343344' );
GO 

REVERT;
GO

Executing as JohnSmith, we get the error message we expected:

Let's try again, but this time using the stored procedure. JohnSmith has the ability to execute the stored procedure, but he doesn't have the permissions to insert directly against the table. This is where ownership chaining comes in as both objects are owned by the same owner and JohnSmith has the ability to execute the first object.

-- This will succeed because JohnSmith can execute any 
-- stored procedure in the HR schema. An ownership chain forms,
-- allowing the insert to happen.
EXECUTE AS USER = 'JohnSmith';
GO 

EXEC HR.InsertNewEmployee 
      @EmployeeID = 557, 
      @GivenName = 'Michael', 
      @Surname = 'Cooper', 
      @SSN = '3343343344';
GO 

REVERT;
GO

We don't get an error message. In fact, SQL Server tells us one row was inserted.

Let's verify that the data was inserted:

-- Verifying the insert
SELECT EmployeeID, GivenName, Surname, SSN 
FROM HR.Employee;
GO 

And, as expected, the row has been inserted into the table.

The Power of Ownership Chaining

In this tip we considered two simple examples: reading data through a view and inserting data through a stored procedure. We can use ownership chaining in any situation to view and manipulate data where one object refers to another. As long as the user has permissions on the initial object and both objects are owned by the same owner, an ownership chain is formed. With ownership chaining, we can build a secure data model and not give access to the underlying tables.

Beyond these two examples, views can be used for data manipulation. However, the catch there is the view doesn't refer to all the columns in the underlying table(s), it can't perform a data change on those columns. For cases like INSERT, that means those columns will need default values or be setup to allow NULL values. Stored procedures can be used to query data just like views. The big difference is stored procedures can perform other operations outside of the simple SELECT statement. Therefore, if you're looking to carefully control data access, ownership chaining can help greatly in this endeavor.

Next Steps

• Refresh your knowledge of how ownership chaining works.
• Learn why dynamic SQL breaks ownership chaining.
• Understand how ownership chaining across databases can be set up.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips