Updated: March 2014

Preamble

This workbench shows, as simply as possible, how to tighten security a great deal in a SQL Server database by implementing a schema-based security system. SQL Server Security is sometimes rather a blind spot tapplication developers. This has been widely illustrated by the success of very simple attacks on database-driven websites, which would have been prevented by even moderate security measures.

In this workbench we will show how to do simple schema-based security and show how it can, to a great extent, prevent SQL Injection. We’ll also show how, with careless code within a stored procedure, you can still accidentally introduce a vulnerability.

It is impossible, purely from the database layer of an application alone, to prevent SQL injection attacks happening, but you can, with care, render them harmless. If you are accustomed to give your application logins DBO rights in your database, then it is time to tighten security.

We’ll illustrate schema-based security by creating a simple database, creating an example of all the common database objects

Preventing any damage through SQL Injection is a relatively simple matter. You should prevent any direct access to the base tables by the application. All database access should be through a set of stored procedures, views and functions. This can make things trickier for programmers in the short-term, but the result will be a secure database, and one that is easier to refactor. The neatest way of doing this is by Schema-based security. In previous versions of SQL Server, it had to be done by assigning permissions to the application-interface explicitly, but now you can do this just by placing the routines (aka Modules) that make up the application interface into a schema that is made for the purpose.

Firstly create a database called SecurityWorkbench for the workbench just to try things out. Then…

For this part of the exercise, log in as database owner and execute the following section. This builds a couple of base tables and puts some pretty sensitive information in one of them that you wouldn’t want anyone but the sysadmin to see. Then we’ll create various modules that will each access the data and extract just safe parts of it. We’ll create a chain to show ownership chaining. We’ll also show how to use dynamic SQL safely, and how to do it recklessly and stupidly in a way that allows access to the base tables even when you have no direct permissions to SELECT from it.

Building the database

Delete the table we create if it exists, so we can do it over and again.

The customer table in the dbo schema. In reality, we probably wouldn’t do this, of course but it serves for a demo.

And we’ll now add some data to it.

Now we’ll add an address table in dbo schema. First we delete it if it exists, so we can do this test over and again.

Create the address table in dbo schema.

SQL Injection

SQL Injection usually comes through the failure of the application programmer to filter the input from the user to ‘escape’ or otherwise change SQL string-delimiters in input from the user, or pass numbers unvalidated. So, when a user types in CM5 4RS for his postcode, then the application programmer might take it into his or her head to construct, on the fly, the SQL string…

Instead of using parameters, the progreammer does it simply by concatenating the components of the SQL String together. If so then it is the work of a moment for a hacker to type the following into the postcode field of the application.

Instead of:

He types …

… which is then automatically converted into:

With obvious results, and no apparent error.

Let’s simulate this entirely in SQL just to show you how easily this happens. Firstly we’ll put in innocent parameters and then some malicious ones.

Now we are going to show how you can avoid this sort of thing happenening, and a lot more besides, but implementing schema-based security. Firstly, we’ll add a schema, put some objects in it, and show how one can prevent sensitive data being accessed from the table.

In our database we create a database user. We will log in as WebsiteUser with what we hope will be the ability to access just the data that he or she is entitled to but no more. (it is worth doing a windows user as well, to test that too.).

And in reality, there will be a lot of them so we’d want to create a database role that we will assign to our schema by default, with SELECT and EXECUTE permissions.

Now, the most important component, is a schema that acts as an application interface.

Now we create a stored procedure in the WidgetShopSite schema that checks the application’s User ID and password for their website users, so it is unnecessary to expose the information outside the database. We wont give it any permission to the table but because of ownership chaining, the code can access the table. The user has no permission to alter the procedure so it is safe.

Now it is likely that we will want to allow the application access to a view of the customer information without the sensitive information.
Here is a simple view in the WidgetShopSite schema to illustrate such a view….

We’ll also create a procedure in the WidgetShopSite schema that returns a table that would provide a result. This will just serve to prove to you that it works.

And now we do a stored procedure in the WidgetShopSite schema which uses ‘Dynamic’ SQL. We’ll do it to show that this won’t access the table even though the role we’ve created has permission to execute this.

And now we do a second stored procedure in the WidgetShopSite schema which uses ‘Dynamic’ SQL. This time, we do it to show that this will successfully access the table even though the role we’ve created has permission to execute this. This may look dangerous, but the attacker can’t alter the code and there are no parameters anyway that he could use. Yes, it is safe.

And, we do a function the WidgetShopSite schema just to see if that provides the right access successfully.

And a view that encapsulates a function just to test out a short chain:

Now we create a function that is vulnerable to SQL Injection.

Note that our postcode should not be 80 characters. 16 chars internationally is safe. Obviously, in reality, you’d always validate all your parameters.

To inject, try…

… but you could do it by injecting in any parameter – if you have the patience.

But it is very rare that you’d need to do this. Dynamic pivots, maybe, but not for the general run.

Trying out the Test harness

Now the test database is constructed, we can use it to try out different security models.

You have two approaches to testing various ideas out. You can use two query windows or just one.

For the two-window approach…

Firstly, open up a new window in SSMS or Query Manager, but using the workbench login ID and mypassword password (right-click, press ‘connection…’ in the pop-up and then ‘Change Connection …’).

To execute this following code, you must be logged in as WorkBench (password: mypassword). Don’t execute it whilst logged in as DBO!

You can try out the following – paste it all into the new window. Don’t execute it in this window!

For the single-query window approach use this….

We’ll use a loginless user to test out security. These login-less users are strange characters that exist as automata either to provide a convenient owner of a schema and objects used in any given application in parts of a database, break or create chains, or to mark modules with EXECUTE AS and granting only permissions to these principals.

Here is a simple example.

And here is the snag. We are still vulnerable to SQL Injection at the database level if we try to assemble dynamic SQL rather than use parameters properly.

Ownership-chaining bypasses permission checks entirely, and even takes precedence over DENY ACCESS for all links but the first. If you wish to deny a principal usage of a chain, then you must DENY ACCESS at the start of the chain.

So let’s demonstrate this by creating a schema.