There are so many threats to your security that it is essential to remain ever vigilant—without ending up with your server in a bunker of lead, wearing a tinfoil hat and protecting data by keeping it completely inaccessible to even your users. Business needs connectivity to customers, and customers need connectivity to their data. With that having been said, security is a very important task when setting up and deploying a new application, yet it is often overlooked and dealt with late in the application building process. Whether or not this is acceptable is generally up to your requirements and how your application will be built, but at one point or another, your application team must take the time to get serious about security. Over and over, news stories report data being stolen, and the theft is inevitably due to poor security. In an earlier edition of this book, I used the example of an election official’s stolen laptop in my home city of Nashville, Tennessee; names, addresses, and partial Social Security numbers were stolen. Since then, there has been a steady stream of such stories, and probably the highest profile since the last edition was the theft of a certain adultery-oriented website’s records. You hopefully could not have cared less about that data, but no matter what the focus of your company’s business, you store data that should never be released without the owner’s permission.

Every company these days has a privacy policy, and as a database designer/programmer, meeting that policy is going to be partially your responsibility. Sometimes you will be the only person who cares about the privacy policy, and your calls for strict security will make you sound like that tinfoil hat you’re wearing is not for aesthetic reasons only. There are also many laws that will govern how well you need to protect various types of data, and which data you can share with the customer, with other customers, with agencies, and even with people in the same company who work in offices in different locations. I will not even somewhat try to cover these broad privacy and legal topics, which are well beyond the scope of this book. Instead, I will cover the following topics related to securing your data so that you have the technical knowledge to meet any privacy policy or law that applies to your employer or client. If you implement these security techniques correctly, at least you will not end up being the cause of your customers’ passwords, credit card numbers, and even personal proclivities being shared on the Internet for all to know.

• Database access: We will cover some of the fundamentals that you need to understand about how a user gets access to a SQL Server instance and into a database.
• Database object securables: Once you are in the context of a database, you have a lot of built-in controls to manage what users can access. We will cover what they are, and how to use and test them.
• Row-level security: We will explore how to use SQL Server 2016’s row-level security tools to limit access to certain rows in a table, with limited if any changes to the underlying application.
• Controlling access to data via T-SQL coded objects: We will look beyond direct access to data, at how you can restrict access to data in more granular ways using T-SQL procedures, views, and so on.
• Crossing database lines: Databases ideally are independent containers, but on occasion, you will need to access data that is not stored within the confines of the database. We will cover some of the caveats when implementing cross-database access.
• Obfuscating data: While the only reason to store data is to be able to read it, you want the program to be able to decode some data only when needed. This is particularly important for personally identifiable data or financial data, so we encrypt the data to keep eyes out except where allowable.
• Auditing: Turning on a “security camera” to watch what people are doing with data is sometimes the only real way to verify that you can provide adequate security, and in many cases you will do this and the aforementioned items.

Overall, we will cover a deep sampling of what you need to do during database design and implementation to secure your data, but we won’t cover the complete security picture, especially if you start to use some of the features of SQL Server that we are not covering in this book (Service Broker to name one). The goal of this chapter is to help you architect a security solution by showing you what is available, demonstrating some of the implementation patterns you may use, and then letting you dig in for your exact needs.

One bit of terminology clarification is important to understand. When you think of SQL Server architecture, you should think of three layers, each involved in the security (and code execution) of data:

• Host server: The machine (physical or virtual) that the software runs on. SQL Server primarily runs on a Windows Server platform (Linux is in beta as of this writing, and it isn’t wrong to expect others to follow.). The host server can provide authentication of the identity trying to access SQL Server.
• Instance/SQL Server: The SQL Server installation (often referred to as just “server”). You can have multiple instances on a host server. The instance can provide authentication services of its own. SQL Server is basically an operating system of its own, using the host operating system to perform some of its tasks. Unfortunately, the term “instance” and “server” are used analogously in many contexts, likely due to historical naming that existed before the introduction of instances.
• Database: The container for data that the users will access. Databases can also contain authentication information.

Additionally, if you are using an Azure SQL Database, while you will primarily deal with the database for security, you can see in the configuration of a database that the concept of a server exists, which is very similar to the server/instance for the on-premises product. Security inside the database container will behave like that of the on-premises versions that the book is mostly centered on.

Not everyone will use all of the guidelines in this chapter in their security implementations. Often, the application layer is left to implement much of the security alone, by simply showing or hiding functionality from the user. This approach is common, but it can leave gaps in security, especially when you have to give users ad hoc access to the data or you have multiple user interfaces that have to implement different methods of security. My advice is to make use of the facilities in the database server as much as possible. However, having the application layer control security isn’t a tremendous hole in the security of the organization, as long as the passwords used are seriously complex, encrypted, and extremely well guarded and, ideally, the data is accessed using Windows Authentication from the middle tier.

Database Access

In this initial section, we are going to cover a few prerequisites that we will need for the rest of this chapter on database security, starting with connecting to the server and gaining access to a database. In this section I will cover the following topics:

• Guidelines for host server security configuration: Some considerations to make sure your server is configured to protect against outside harm.
• Principals and securables: All security in SQL Server is centered on principals (loosely, logins and users) and securables (stuff that you can limit access to).
• Connecting to the server: With changes in SQL Server 2012, there are now multiple ways to access the server. We will cover these.
• Impersonation: Using the EXECUTE AS statement, you can “pretend” you are a different security principal to use the other user’s security. It is a very important concept for testing security that we will use often in this chapter.

Guidelines for Host Server Security Configuration

It is very important to configure the host server to be as secure as possible based on how your server will be used. Very few servers these days are completely cut off from the Internet (and the scum-sucking hacker types who lurk there). As an application/data architect/programmer, I have generally only been an advisor on how to configure most of the server beyond the confines of the individual database, and deep details of on configuration is outside of the scope of this book anyhow. However, it definitely is worth a few pages to make it clear that the host server is the linchpin to the matter of securing your data. The following list contains some high-level characteristics you will want to use to validate the security of the server to protect your system from malicious hackers. It is not an exhaustive list, just a list of almost universally required settings for configuring the Windows server and the SQL Server instance that we will be using to house our databases.

• Strong passwords are applied to all accounts that can access the host server, and very strong passwords are applied to all universally known system accounts. Certainly, there are no blank passwords for any accounts! (The same will apply to accounts that can access SQL Server, both from Windows Authentication and standard accounts.)
• The host server isn’t sitting unguarded on the Web, with no firewall, standard ports used for access, and/or not logging failed login attempts.
• Application passwords are secured/encrypted and put where they can be seen only by people who need to see them (such as the DBA and the application programmers who use them in their code). The password is encrypted into application code modules when using application logins.
• Very few people have file-level access to the server where the data is stored and, probably more important, where the backups are stored. If one malicious user has access to your backups in whatever form you have them in, that person has access to your data by simply attaching that file to a different server, and you can’t stop him or her from accessing the data (even encryption isn’t 100% secure if the hacker has virtually unlimited time; just ask the FBI or Apple).
• Your host server is located in a very secure location. A Windows server, just like your laptop, is only as secure as the physical box. Just like on any spy TV show, if the bad guys can access your physical hardware, they could boot to a CD or USB device and gain access to your hard disks (note that using Transparent Data Encryption [TDE] can help in this case). This is even more important as virtualization is becoming the de facto standard. The files for the VM are a lot easier to smuggle out of the office than a 20-pound machine with disk arrays attached.
• All features that you are not using are turned off. This pertains both to the Windows Server components (if you are not using the web server services, turn them off) and to the SQL Server installations. Windows helps by not turning on everything by default, as does SQL Server. For example, remote administrator connections, Database Mail, CLR programming, and other features are all off by default. You can enable these features and others by using the sp_configure stored procedure.
• You have chosen proper protocols for accessing the server. Of greatest importance is to use an encrypted connection when your application transmits sensitive data (as defined by laws, privacy policies, and requirements).

The bottom line is that most of what you will do at the database level is intended to keep your mostly honest users from seeing and doing things that they shouldn’t (other than some forms of encryption, at least) and isn’t nearly as important as keeping the data safe from malicious outsiders. For most of your user community, you could leave all of the data unprotected and like Dorothy and her magical shoes, if they don’t know what they can do, they won’t take your data and go back to Kansas. So even if you just leave security to the application layer, it is essential that someone really locks down data access to allow only the people you expect to have access.

Principals and Securables

At the very core of security in SQL Server are the concepts of principals and securables. Principals are those identities that may be granted permission to access particular database objects, while securables are things to which access can be controlled. Principals can represent a specific user, a role that may be adopted by multiple users, or an application, certificate, and more. There are three sorts of SQL Server principals that you will deal with:

• Windows principals: These represent Windows user accounts or groups, authenticated using Windows security. SQL Server trusts Windows to determine who has connected to the server, and when Windows passes an identifier to SQL Server, it trusts that if the identifiers match, things are great.
• [SQL] Server principals: These are server-level logins or roles that are authenticated using SQL Server–based authentication, which is implemented through storage and algorithms located in the software of the SQL Server instance.
• Database principals: These include database users (usually mapped to Windows logins/groups), roles (groups of users and other roles to give access to en masse), and application roles (a special type of role that can be used to let an application have different rights than the user has normally).

Securables are the things to which you can control access on all parts of the instance and database and to which you can grant principals permissions. SQL Server distinguishes between three scopes at which different objects can be secured:

• Server scoped: Includes logins, HTTP endpoints, availability groups, and databases. These are objects that exist at the server level, outside of any individual database, and to which access is controlled on a server-wide basis.
• Database scoped: Securables with database scope are objects such as schemas, users, roles, CLR assemblies, DDL triggers, and so on, which exist inside a particular database but not within a schema.
• Schema scoped: This group includes those objects that reside within a schema in a database, such as tables, views, and stored procedures.

These concepts will come into play in the rest of the chapter as we walk through the different ways that you will need to secure the data in the database. You can then allow, or disallow, usage of these objects to the roles that have been created. SQL Server uses three different security statements to give rights to or take away rights from each of your roles:

• GRANT: Allows access to an object. A principal can be granted the same right multiple times, depending on whether they are a member of any role.
• DENY: Denies access to an object, regardless of whether the user has been granted the privilege from any other GRANT.
• REVOKE: Essentially a DELETE statement for security. Removes any GRANT or DENY permission that has been applied to an object.

Typically, you’ll simply use GRANT to give permissions to a principal to perform tasks that are specific to the principal. DENY is then used only in “extreme” cases, because no matter how many other times the principal has been granted privileges to an object, the principal won’t have access to it while there’s one DENY, which will often make for a confusing security scenario when you are trying to work out why user X can’t access object Y.

For a right that pertains to the entire database or server, you will use syntax like

GRANT <privilege> TO <principal> [WITH GRANT OPTION];

Including WITH GRANT OPTION will allow the principal to grant the privilege to another principal.

For the most part, this book will deal primarily with database object privileges, as database and server privileges are almost always an administrative consideration. They allow you to let principals create objects, drop objects, do backups, change settings, view metadata, and so on.

For database objects, there is a minor difference in the syntax, in that the securable that you will be granting rights to will be specified. For example, to grant a privilege on a securable in a database, the command would be as follows:

GRANT <privilege> ON <securable> TO <principal> [WITH GRANT OPTION];

Next, if you want to remove the privilege, use REVOKE the permission, which will delete the granted access:

REVOKE <privilege> ON <securable> FROM <principal>; --Can also be TO instead of FROM

If you want to prevent the principal from using the securable, no matter any role membership, you use DENY:

DENY <privilege> ON <securable> FROM <principal>; --Can also be TO instead of FROM

To remove DENY, you would again use the REVOKE command. Another bit of notation you will see quite often is to denote the type of securable before the securable where it is not the default. For objects that show up in sys.objects that have security granted to them (table, view, table-valued function, stored procedure, extended stored procedure, scalar function, aggregate function, service queue, or synonym), you can simply reference the name of the object:

GRANT <privilege> ON <securable> TO <database principal>;

Or you can use

GRANT OBJECT::<privilege> ON <securable> TO <database principal>;

For other types of objects, such as schemas, assemblies, and search property lists, to name a few, you will specify the type in the name. For example, for a GRANT on a schema securable, the syntax is

GRANT <privilege> ON SCHEMA::<schema securable> TO <database principal>;

Connecting to the Server

Before we finally get to database security, we need to cover accessing the server. Prior to SQL Server 2012, there was a single way to access a database. This method is still pretty much the normal way and is basically as follows: A login principal is defined that allows a principal to access the server using Windows credentials (Windows Authentication), a login that is managed in the SQL Server instance (SQL Server Authentication), or one of several other methods including a certificate or an asymmetric key. The login is then mapped to a user within the database to gain access.

The additional method in SQL Server 2012 uses the concept of a contained database (CDB). I will cover the broader picture and a bit of the management of CDBs as a whole later in the chapter when I cover cross-database security, but I do need to introduce the syntax and creation of the database here because it is, from a coding standpoint, largely a security question. Contained databases in SQL Server 2012 are the initial start of making databases essentially stand-alone containers that can be moved from server to server with little effort, and into an Azure Database as well.

In this section, I will provide two examples of connecting to the server:

• Using the classic approach of a login and database user to connect to the server first, and then the database
• Accessing the database directly using the containment model

Connecting to the Server Using a Login and a Database User

To access the server, we will create a server principal known as a login. There are two typical methods that you will use to create almost all logins. The first method is to map a login to a Windows Authentication principal. This is done using the CREATE LOGIN statement. The following example would create the login I have on my laptop for writing content:

CREATE LOGIN [DomainNameLouis] FROM WINDOWS --square brackets required for WinAuth login
    WITH DEFAULT_DATABASE=tempdb, DEFAULT_LANGUAGE=us_english;

The name of the login is the same as the name of the Windows principal, which is how they map together. So on my local virtual machine (the name of which I will replace with DomainName), I have a user named Louis. The Windows principal can be a single user or a Windows group. For a group, all users in the group will gain access to the server in the same way and have the exact same permission set. This is, generally speaking, the most convenient method of creating and giving users rights to SQL Server. If you are using Azure SQL DB, you can use Azure Active Directory Authentication to do very much the same thing (azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/).

The second way is to create a login with a password:

CREATE LOGIN Fred WITH PASSWORD=N’password’ MUST_CHANGE, DEFAULT_DATABASE=tempdb,
     DEFAULT_LANGUAGE=us_english, CHECK_EXPIRATION=ON, CHECK_POLICY=ON;

If you set the CHECK_POLICY setting to ON, the password will need to follow the password complexity rules of the server it is created on, and CHECK_EXPIRATION, when set to ON, will require the password to be changed based on the policy of the Windows server as well, and ’password’ is not likely to pass, even on a simple machine you have created just for trying out this code, so pick something that will meet your requirements. Typical password requirements should be similar to

• Should be pretty long, not just one character from each of the following bullets (for tips, see, e.g., www.infoworld.com/article/2655121/security/password-size-does-matter.html)
• Should contain uppercase symbols (A-Z)
• Should contain lowercase symbols (a-z)
• Should contain numbers (0-9)
• Should contain at least one symbol from this list: _, @, *, ^, %, !, #, $, or &

Generally speaking, the most desirable method is to use Windows Authentication for the default access to the server where possible, since keeping the number of passwords a person has to a minimum makes it less likely they will tape a list of passwords to the wall for all to see. Of course, using Windows Authentication can be troublesome in some cases where SQL Server are located in a DMZ with no trust between domains so you have to resort to SQL Server Authentication, so use complex passwords and (ideally) change them often.

In both cases, I defaulted the database to tempdb, because it requires a conscious effort to go to a user database and start building, or even dropping, objects. However, any work done in tempdb is deleted when the server is stopped. This is actually one of those things that may save you more times than you might imagine. Often, a script gets executed and the database is not specified, and a bunch of objects gets created—usually in master (the default database if you haven’t set one explicitly…so the default default database). I have built more test objects on my local SQL Server in master over the years than I can count.

Once you have created the login, you will need to do something with it. If you want to make it a system administrator–level user, you could add it to the sysadmin server role, which is something that you will want to do on your local machine with your default user (though you probably already did this when you were installing the server and working though the previous chapters, probably during the installation process, possibly without even realizing that was what you were doing):

ALTER SERVER ROLE sysadmin ADD MEMBER [DomainUserLouis];

You can give users rights to do certain actions using server permissions. For example, if Fred works in support, you may want to give him read-only access to a server (without rights to change anything). First off, say you want Fred to be able to run DMVs (Dynamic Management Views, which are sometimes functions) to see the state of the server. You would grant the Fred user VIEW SERVER STATE permission using

GRANT VIEW SERVER STATE to Fred;

As of SQL Server 2012, you could create user-defined server roles. For example, say you want to set up a role to let the user view the server settings and data. You could give the following rights:

• VIEW SERVER STATE: Access DMVs (previously mentioned)
• VIEW ANY DATABASE: See the structure of all databases
• CONNECT ANY DATABASE: Connect to any existing and future database
• SELECT ALL USER SECURABLES: View all data in databases the login can connect to

To create a server role for these items, you could use

CREATE SERVER ROLE SupportViewServer;

Grant the role the rights desired as follows:

GRANT  VIEW SERVER STATE to SupportViewServer; --run DMVs
GRANT  VIEW ANY DATABASE to SupportViewServer; --see any database
GRANT  CONNECT ANY DATABASE to SupportViewServer; --set context to any database
GRANT  SELECT ALL USER SECURABLES to SupportViewServer; --see any data in databases

And add the login to the server role:

ALTER SERVER ROLE SupportViewServer ADD MEMBER Fred;

Once you have created your login, the next step is to access a database (unless you used sysadmin, in which case you have unfettered access to everything on the server). For the first examples in this chapter, create a database called ClassicSecurityExample, as shown next. (For the remainder of this chapter, I will expect that you are using a user who is a member of the sysadmin server role as the primary user, much as we have for the entire book, except when we are testing some code and I specify a different user in the text.)

CREATE DATABASE ClassicSecurityExample;

Next, create another login that uses SQL Server Authentication. Most logins we will create in the book will be SQL Server Authentication to make it easier to test. We will also keep the password simple (CHECK_POLICY) and not require it to be changed (CHECK_EXPIRATION) to make our examples easier:

CREATE LOGIN Barney WITH PASSWORD=N’password’, DEFAULT_DATABASE=[tempdb],
             DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

Log in using the user in Management Studio into a query window, as shown in Figure 9-1.

Next, try to execute a USE statement to change context to the ClassicSecurityExample database:

USE ClassicSecurityExample;

You will receive the following error:

Msg 916, Level 14, State 1, Line 1
The server principal "Barney" is not able to access the database "ClassicSecurityExample" under the current security context.

Your database context will remain in tempdb, since this is the default database you set up for the user. Going back to the window where you are in the sysadmin user context, you need to enable the user to access the database. There are two ways to do this, the first being to give the guest user rights to connect to the database (back as the sysadmin user, naturally). The guest user is a built-in user that every database has. It equates to “anyone who connects,” basically.

USE ClassicSecurityExample;
GO
GRANT CONNECT TO guest;

If you go back to the connection where the user Barney is logged in, you will find that Barney can now access the ClassicSecurityExample database—as can any other login in your system. You can apply this strategy if you have a database that you want all users to have access to, but it is generally not the best idea under most circumstances.

So, remove this right from the guest user using the REVOKE statement:

REVOKE CONNECT TO guest;

Going back to the window where you have connected to the database as Barney, you will find that executing a statement like SELECT ’hi’; is still allowed, but if you disconnect and reconnect, you will not be able to access the database. Finally, to give server principal Barney access to the database, create a user in the database linked to the login and grant it the right to connect:

USE ClassicSecurityExample;
GO
CREATE USER BarneyUser FROM LOGIN Barney;
GO
GRANT CONNECT to BarneyUser;

Going back to the query window in the context of Barney, you will find that you can connect to the database and, using a few system functions, see your server and database security contexts in each:

USE ClassicSecurityExample;
GO
SELECT SUSER_SNAME() AS server_principal_name, USER_NAME() AS database_principal_name;

This will return

server_principal_name    database_principal_name
------------------------ -------------------------------
Barney                   BarneyUser

Executing this in your system administrator connection, you will see something like (depending on what you used to log in to the server):

server_principal_name   database_principal_name
----------------------- -----------------------------
DOMAINNameLouis        dbo

The server principal will be the login you used, and the database principal will always be dbo (the database owner), as the system administrator user will always be mapped to the database owner. Now, this is the limit of what we are covering in this section, as you are now able to connect to the database. We will cover what you can do in the database after we cover connecting to the database with a contained database.

Using the Contained Database Model

A tremendous paradigm shift has occurred since I first started writing about database design and programming, and this is virtualization. Even as recently as SQL Server 2008, the advice would have been strongly against using any sort of virtualization technology with SQL Server, and now our company runs everything on virtualized Windows machines. One of the many tremendous benefits of virtualization is that you can move around your virtual computer and/or servers within your enterprise to allow optimum use of hardware.

Another paradigm shift is cloud computing. Azure DB databases behave like self-contained database servers. When you connect to them, you see a version of a master database, and a database container. Logins do not reside at the server level, but are contained in the database. In the on-premises product, we can do the same thing using the “containment” model. The idea behind containment is that everything your database needs (jobs, ETL, tempdb objects, etc.) will begin to be a part of the database directly. Where applicable, I will note some of the places where contained database security is different from the classic model, which is mostly in the context of accessing external objects.

Your first step is to create a new database in which you will set containment = partial. For SQL Server 2012 and later, there are two models: OFF, which I am referring to as the classic model, and partial, which will give you a few benefits (like temporary object collation defaulting to the partially contained databases rather than the server). Not much has changed in containment since 2012, but later versions of SQL Server may likely include a fully contained model that will be almost completely isolated from other databases in most ways.

The way you will connect to the database is a fundamental change, and just like filestream discussed in Chapter 8, this means a security point that is going to be turned off by default. Hence, the first thing you will do is configure the server to allow new connections using what is called contained database authentication using sp_configure:

EXECUTE sp_configure ’contained database authentication’, 1;
GO
RECONFIGURE WITH OVERRIDE;

You should get a message telling you that the value was changed, either from 0 to 1 or 1 to 1, depending on if the server is already set up for the contained authentication. Next, create the database. You can set the containment properties in the CREATE DATABASE statement:

CREATE DATABASE ContainedDBSecurityExample CONTAINMENT = PARTIAL;

Or you can set it using an ALTER DATABASE statement:

-- set the contained database to be partial
ALTER DATABASE ContainedDBSecurityExample SET CONTAINMENT = PARTIAL;

Next, you will create a user, which in this context is referred to as a “contained user.” Contained users are basically a hybrid of login and user, created using the CREATE USER statement, which is a bit regrettable, as the syntaxes are different (you will be warned if you try to use the wrong syntax). Books Online lists at least 11 variations of the CREATE USER syntax, so you should check it out if you need a different sort of user!

The first case you will use is a new SQL Server Authentication user that logs into the database directly with a password that exists in the system catalog tables in the database. You must be in the context of the database (which you set earlier), or you will get an error telling you that you can only create a user with a password in a contained database.

USE ContainedDBSecurityExample;
GO
CREATE USER WilmaContainedUser WITH PASSWORD = ’p@ssword1’;

You can also create a Windows Authentication user in the following manner (it could be a role as well) as long as a corresponding login does not exist. So the following syntax is correct, but on my computer, this fails because that user already has a login defined:

CREATE USER [DOMAINLouis];

Since that user already has a login, I get the following error:

Msg 15063, Level 16, State 1, Line 1
The login already has an account under a different user name.

presumably because it has the same security context, and it would default to using the server rights, with the default database set (as I will demonstrate in the next paragraph!). But again, during the testing phase, we will be using SQL Server Authentication to make the process easier.

Next, connect to the database in SSMS using the contained user you previously created named WilmaContainedUser with password p@ssword1. To do this, specify the server name, choose SQL Server Authentication, and set the username and password, as shown in Figure 9-2.

Next, click the Options button. Go to the Connection Properties tab, and enter the name of the contained database as shown in Figure 9-3.

You will need to know the name since the security criteria you are using will not have rights to the metadata of the server, so if you try to browse the database with the login you have supplied, it will give you the error you can see in Figure 9-4.

Now, as you will see in Object Explorer, the server seems like it is made up of a single database, as shown in Figure 9-5.

After this point in the process, you will be in the context of a database, and everything will be pretty much the same whether the database is partially contained or completely uncontained. The big difference is that in the drop-down list of databases, you will have the current database (ContainedDBSecurityExample) and master and tempdb. At this point, you are in the context of the database just like in the classic security model covered in the previous section.

You cannot create a contained user in an uncontained database, but you can still create a user linked to a login in a contained database. For example, you could create a new login:

CREATE LOGIN Pebbles WITH PASSWORD = ’BamBam01$’;

Then link that user to the login you have created:

CREATE USER PebblesUnContainedUser FROM LOGIN Pebbles;

Obviously, this begins to defeat the overarching value of a contained database, which is to make the database portable without the need to reconcile logins on one server to a login on another, but rather to be immediately usable with the same users (with the caveat that the Windows Authentication user will have to be able to connect to the authenticating server).

Note that you can switch a contained database back to not being contained but you cannot have any contained database principals in it. If you try to set the ContainedDbSecurityExample database back to uncontained:

ALTER DATABASE ContainedDbSecurityExample  SET CONTAINMENT = NONE;

This will fail. In SQL Server 2012, you get a confusing error message:

Msg 33233, Level 16, State 1, Line 1
You can only create a user with a password in a contained database.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

But in SQL Server 2016, the error message is very clear:

Msg 12809, Level 16, State 1, Line 103
You must remove all users with password before setting the containment property to NONE.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

If you need to make this database uncontained, you will need to drop the contained users, which you can identify with the following list:

SELECT name
FROM   ContainedDBSecurityExample.sys.database_principals --3 part name since you are outside
                                                         --of db to make this change.
WHERE  authentication_type_desc = ’DATABASE’;

In our example, this returns

Name
----------------------------
WilmaContainedUser

Drop this user, and you would then be able to turn containment off for this database. Later in this chapter, we will come back to the topic of containment when we cover cross-database access (and in the case of containment, working to protect against it to keep databases more portable).

Impersonation

The ability to pretend to be another user or login is fairly important when it comes to testing security. After some code has been migrated to production, it is not an uncommon occurrence to get a call from clients who claim that they cannot do something that you think they really ought to be able to do. Since all system problems are inevitably blamed on the database first, it is a useful trick to impersonate the user and then try the questioned code in Management Studio to see whether it is a security problem. If the code works in Management Studio, your job is almost certainly done from a database standpoint, and you can point your index finger at some other part of the system. You can do all of this without knowing their passwords, as you are either the sysadmin user or have been granted rights to impersonate the user. (There is a server-scoped permission IMPERSONATE ANY LOGIN that was added to SQL Server 2014 to make this easier for a semi-sysadmin type to do.)

To demonstrate security in a reasonable manner on a single SQL Server connection, I will use the EXECUTE AS command to impersonate different security principals, both database and server.

As an example of how powerful impersonation can be, I’ll show a way that you can have a user impersonating a member of the server-system sysadmin role. Using impersonation in such a way takes some getting used to, but it certainly makes it easier to have full sysadmin power only when it’s needed. As said previously, there are lots of server privileges, so you can mete out rights that are needed on a day-to-day basis and reserve the “dangerous” ones like DROP DATABASE only for logins that you have to impersonate.

In this example, I use a SQL Server Authentication login, but you could map it to a certificate, a key, a Windows user, or whatever. Standard logins make it much easier to test situations and learn from them because they’re self-contained (which is part of what makes them less secure for production use!). Then, I add the login to the sysadmin role. You probably also want to use a name that isn’t so obviously associated with system administration. If a hacker got into your list of users somehow, the name ’itchy’ wouldn’t so obviously be able to do serious damage to your database server, as would a name like ’Merlin’.

USE master;
GO
CREATE LOGIN SlateSystemAdmin WITH PASSWORD = ’tooHardToEnterAndNoOneKnowsIt’,CHECK_POLICY=OFF;
ALTER SERVER ROLE sysadmin ADD MEMBER SlateSystemAdmin;

Then, I create a regular login and give rights to impersonate the system_admin user:

CREATE LOGIN Slate with PASSWORD = ’reasonable’, DEFAULT_DATABASE=tempdb,CHECK_POLICY=OFF;
--Must execute in master Database
GRANT IMPERSONATE ON LOGIN::SlateSystemAdmin TO Slate;

I log in as Slate and try to run the following code (in Management Studio, you can just right-click in the query window to use the Connection/Change Connection context menu and use a standard login):

USE ClassicSecurityExample;

The following error is raised:

Msg 916, Level 14, State 1, Line 1
The server principal "Slate" is not able to access the database "ClassicSecurityExample" under the current security context.

Now, I change security context to the system_admin level user (note that you cannot use EXECUTE AS LOGIN when you are in the context of a contained database user):

EXECUTE AS LOGIN = ’SlateSystemAdmin’;

I now have control of the server in that window as the system_admin user! To look at the security context, I can use several variables/functions:

USE    ClassicSecurityExample;
GO
SELECT USER AS [user], SYSTEM_USER AS [system_user],
       ORIGINAL_LOGIN() AS [original_login];

This returns the following result:

user          system_user          original_login
------------- -------------------- ------------------------
dbo           SlateSystemAdmin     Slate

The columns mean the following:

• user: The database principal name of context for the user in the database.
• system_user: The server principal name of context for the login.
• original_login(): The login name of the server principal who actually logged in to start the connection. (This is an important function that you should use when logging which login performed an action.)

Then, I execute the following code:

REVERT; --go back to previous security context

I see the following result:

Msg 15199, Level 16, State 1, Line 1
The current security context cannot be reverted. Please switch to the original database where ’Execute As’ was called and try it again.

I started in tempdb, so I use the following code:

USE tempdb;
REVERT;
SELECT USER AS [user], SYSTEM_USER AS [system_user],
       ORIGINAL_LOGIN() AS [original_login];

This now returns the following result:

user          system_user          original_login
------------- -------------------- ------------------------
guest         Slate                Slate

Impersonation gives you a lot of control over what a user can do and allows you to situationally play one role or another, such as creating a new database.

Using impersonation, you can execute your code as a member of the sysadmin server role or db_owner database role and then test your code as a typical user without opening multiple connections (and this technique makes the sample code considerably easier to follow). Note that I have only demonstrated impersonating a login, but you can also impersonate users, which we will use along with impersonating a login throughout the rest of this chapter. Note that there are limitations on what you can do when using impersonation. For a full treatment of the subject, check in Books Online under the “EXECUTE AS” topic.

Database Object Securables

Now that we have covered access to the server and/or database, your users are going to need the ability to do something in the database they now have access to. In this section, we are going to cover the different ways you can now use the database principals you have created.

I’ll cover the basics of database permissions for a foundation of best practices. Taken to the extreme, the set of things considered securable is extensively large, especially at the server level, but over 90% of security activity for the average database programmer/architect (and certainly a data architect, as is the main focus of this book) is securing tables, views, functions, and procedures, and this is what’s primarily interesting from a database-design standpoint. Everything else is very similar in any case.

At the database level, there are two main types of principals: the user and the role. We covered the user in the previous section and whether you use Windows Authentication or standard authentication, or if you use the classic or the containment model, the database implementation will be essentially the same.

The other principal we will start to use is a role, which is a way to set up different functional roles and then assign a user or another role to it. The very best practice for assigning security to database principals is to nearly always use roles, even if you have only a single user in a role. This practice may sound like more work, but in the end, it helps keep rights straight between your development and production environments (and all environments in between) and helps avoid users who end up with god-like powers from getting one permission here, another there, and so on. The roles will be the same in all areas; allowing most of your security code to look the same (and hence be checked into source control and testable) and the different users who are associated with the roles are then different in production, test, and so on can be different, if perhaps similar to make things clearer.

I’ll cover the following topics, which revolve around giving users permissions to use securables:

• Grantable permissions: You’ll learn about the different sorts of database permissions and how to grant and revoke permission on securables.
• Roles and schemas: You’ll learn how to use roles and schemas to grant rights efficiently to database securables.

These two topics will give you most of the information you need to know about setting up your database-level security.

Grantable Permissions

You can control rights to almost every object type, and in SQL Server, you can secure a tremendous number of object types. For our purposes here, I’ll cover data-oriented security specifically, limited to the objects and the actions you can give or take away access to (see Table 9-1). There are also rights you can use to allow a user to make changes to a table (ALTER) or do anything that is available with CONTROL. If you want to give all of the listed permissions to a principal, use ALL instead of the permission name.

Most of these are straightforward and probably are familiar if you’ve done any SQL Server administration, although perhaps REFERENCES isn’t familiar as it is not used very often. Briefly, SELECT allows you to read data using a SELECT statement; INSERT allows you to add data, UPDATE to modify data, and DELETE to remove data. EXECUTE lets you execute coded objects, and REFERENCES allows objects that one user owns to reference another object owned by another via a foreign key. For 99.5% of databases, all objects should be owned by the same user. For the other .49% of them, the objects might be owned by different users, but you would probably not want to implement foreign keys between the tables. Hence we will largely ignore the REFERENCES right.

As briefly mentioned earlier in this chapter for server and database permissions, you will use one of the three different statements to give rights to or take away rights from each of your roles:

• GRANT: Gives a right
• DENY: Disallows access to an object, regardless of any other associated grants
• REVOKE: Deletes a previously applied GRANT or DENY permission

To see the user’s rights in a database, you can use the sys.database_permissions catalog view. For example, use the following code to see all the rights that have been granted in the database:

SELECT  class_desc AS permission_type,
        OBJECT_SCHEMA_NAME(major_id) + ’.’ + OBJECT_NAME(major_id) AS object_name,
        permission_name, state_desc, USER_NAME(grantee_principal_id) AS grantee
FROM   sys.database_permissions;

Using that query in the master database, you will be able to see users that have CONNECT rights, as well as the different stored procedures and tables that you have access to.

Table Security

As already mentioned, for tables at an object level, you can grant a principal rights to INSERT, UPDATE, DELETE, or SELECT data from a table (you can also grant the REFERENCES right, which allows the user to reference the object in a foreign key, but this is rare). This is the most basic form of security when dealing with data. The goal when using table-based security is to keep users looking at, or modifying, the entire set of data, rather than specific rows. We’ll progress to the specific security types as we move through the chapter.

As an example of table security, I will create a new table, and demonstrate, through the use of a new user, what the user can and cannot do:

USE ClassicSecurityExample;
GO
--start with a new schema for this test and create a table for our demonstrations
CREATE SCHEMA TestPerms;
GO
CREATE TABLE TestPerms.TableExample
(
    TableExampleId int IDENTITY(1,1)
                   CONSTRAINT PKTableExample PRIMARY KEY,
    Value   varchar(10)
);

Next, I create a new user, without associating it with a login. You won’t need a login for many of the examples, because you’ll use impersonation to pretend to be the user without logging in.

CREATE USER Tony WITHOUT LOGIN;

I impersonate the user Tony and try to create a new row:

EXECUTE AS USER = ’Tony’;
INSERT INTO TestPerms.TableExample(Value)
VALUES (’a row’);

Well, as you would (or, at least, will come to) expect, here’s the result:

Msg 229, Level 14, State 5, Line 154
The INSERT permission was denied on the object ’TableExample’, database ’ClassicSecurityExample’, schema ’TestPerms’.

Now, I go back to being the dbo using the REVERT command, give the user rights, return to being Tony, and try to insert again:

REVERT; --return to admin user context
GRANT INSERT ON TestPerms.TableExample TO Tony;
GO

Then, I try to execute the insert statement again as Tony; I should now be able to execute the insert statement:

EXECUTE AS USER = ’Tony’;
INSERT INTO TestPerms.TableExample(Value)
VALUES (’a row’);

No errors here. Now, because Tony just created the row, the user should be able to select the row, right?

SELECT TableExampleId, Value
FROM   TestPerms.TableExample;

No, the user had rights only to INSERT data, not to view it:

Msg 229, Level 14, State 5, Line 168
The SELECT permission was denied on the object ’TableExample’, database ’ClassicSecurityExample’, schema ’TestPerms’.

Now, I can give the user Tony rights to SELECT data from the table using the following GRANT statement:

REVERT;
GRANT SELECT ON TestPerms.TableExample TO Tony;

Now that Tony has rights, I can successfully run the following:

EXECUTE AS USER = ’Tony’;
SELECT TableExampleId, Value
FROM   TestPerms.TableExample;
REVERT;

The SELECT statement works and does return the row the user created. At the table level, you can do this individually for each of the four DML statement permission types INSERT, UPDATE, DELETE, and SELECT (or you can use GRANT ALL ON <objectName> TO <principal> to give all rights to the <objectName> to the <principal>). The goal is to give the users only what they need. For example, if the user happened to represent a device that was inserting readings, it wouldn’t need to be able to read, modify, or destroy data, just create it.

Column-Level Security

For the most part, it’s enough simply to limit a user’s access at the level of either being able to use (or not use) the entire table or view, but as the next two major sections of the chapter will discuss, sometimes the security needs to be more granular. Sometimes you need to restrict users to using merely part of a table. In this section, I’ll present the security syntax that SQL Server provides at a basic level to grant rights at a column level. Later in this chapter, I’ll present other methods that use views or stored procedures.

For our example, we’ll create a couple of database users:

CREATE USER Employee WITHOUT LOGIN;
CREATE USER Manager WITHOUT LOGIN;

Then, we’ll create a table to use for our column-level security examples for a Product table. This Product table has the company’s products, including the current price and the cost to produce this product:

CREATE SCHEMA Products;
GO
CREATE TABLE Products.Product
(
    ProductId   int NOT NULL IDENTITY CONSTRAINT PKProduct PRIMARY KEY,
    ProductCode varchar(10) NOT NULL CONSTRAINT AKProduct_ProductCode UNIQUE,
    Description varchar(20) NOT NULL,
    UnitPrice   decimal(10,4) NOT NULL,
    ActualCost  decimal(10,4) NOT NULL
);
INSERT INTO Products.Product(ProductCode, Description, UnitPrice, ActualCost)
VALUES (’widget12’,’widget number 12’,10.50,8.50),
       (’snurf98’,’Snurfulator’,99.99,2.50);

Now, we want our employees to be able to see all the products, but we don’t want them to see what each product costs to manufacture. The syntax is the same as using GRANT on a table, but we include in parentheses a comma-delimited list of the columns to which the user is being denied access. In the next code block, we grant SELECT rights to both users but take away these rights on the ActualCost column:

GRANT SELECT on Products.Product to employee,manager;
DENY SELECT on Products.Product (ActualCost) to employee;

To test our security, we impersonate the user manager:

EXECUTE AS USER = ’manager’;
SELECT  *
FROM    Products.Product;

This returns all columns with no errors:

ProductId   ProductCode Description          UnitPrice      ActualCost
----------- ----------- -------------------- -------------- ----------------
1           widget12    widget number 12     10.5000        8.5000
2           snurf98     Snurfulator          99.9900        2.5000

The user manager worked fine; what about the user employee?

REVERT;--revert back to SA level user or you will get an error that the
       --user cannot do this operation because the manager user doesn’t
       --have rights to impersonate the employee
GO
EXECUTE AS USER = ’employee’;
GO
SELECT *
FROM   Products.Product;

This returns the following result:

Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column ’ActualCost’ of the object ’Product’, database ’ClassicSecurityExample’, schema ’Products’.

“Why did I get this error?” the user first asks, then (and this is harder to explain), “How do I correct it?” You might try to explain to the user, “Well, just list all the columns you do have access to, without the columns you cannot see, like this:”

SELECT ProductId, ProductCode, Description, UnitPrice
FROM   Products.Product;
REVERT;

This returns the following results for the user employee:

ProductId   ProductCode Description          UnitPrice
----------- ----------- -------------------- ---------------------------------------
1           widget12    widget number 12     10.5000
2           snurf98     Snurfulator          99.9900

The answer, although technically correct, isn’t even vaguely what the user wants to hear. “So every time I want to build an ad hoc query (this is an advanced user!) on the Product table (which has 87 columns instead of the 5 I’ve generously mocked up for your learning ease), I have to type out all the columns? And if I use some form of tooling, I have to remember what columns I don’t have access to?”

This is why, for the most part, column-level security is rarely used as a primary security mechanism. You don’t want users getting error messages when they try to run a fairly simple query on a table. You might add column-level security to the table “just in case,” but for the most part, use coded objects such as stored procedures or views to control access to certain columns. I’ll discuss these solutions in the next section.

Here’s one last tidbit about column security syntax: once you’ve applied the DENY option on a column, to give the user rights, you need to REVOKE the DENY to restore the ability to access the column and then GRANT access to the entire table. Using REVOKE alone would only delete the DENY.

Roles

Core to the process of granting rights is determining who to grant rights to. The user is the lowest level of security principal in the database and can be mapped to logins, certificates, and asymmetrical keys, or even not mapped to a login at all (either a user created with the WITHOUT LOGIN option specifically for impersonation, or they can be orphaned by dropped users). In this section, I will expand a bit more on just what a role is.

Roles are groups of users and other roles that allow you to grant object access to multiple users at once. Every user in a database is a member of at least the public role, which will be mentioned again in the “Built-in Database Roles” section, but may be a member of multiple roles. In fact, roles may be members of other roles. I’ll discuss the following types of roles:

• Built-in database roles: Roles that are provided by Microsoft as part of the system
• User-defined database roles: Roles, defined by you, that group Windows users together in a user-defined package of rights
• Application roles: Roles that are used to give an application specific rights, rather than giving rights to a group or individual user

Each of these types of roles is used to give rights to objects in a more convenient manner than granting them directly to an individual user. Many of these possible ways to implement roles (and all security, really) are based on the politics of how you get to set up security in your organization. There are many different ways to get it done, and a lot of it is determined by who will do the actual work. End users may need to give another user rights to do some things, as a security team, network administrators, DBAs, and so on, also dole out rights. The whole idea of setting up roles to group users is to lower the amount of work required to get things done and managed right.

Built-in Database Roles

As part of the basic structure of the database, Microsoft provides a set of nine built-in roles that give a user a special set of rights at a database level:

• db_owner: Users associated with this role can perform any activity in the database.
• db_accessadmin: Users associated with this role can add or remove users from the database.
• db_backupoperator: Users associated with this role are allowed to back up the database.
• db_datareader: Users associated with this role are allowed to read any data in any table.
• db_datawriter: Users associated with this role are allowed to write any data in any table.
• db_ddladmin: Users associated with this role are allowed to add, modify, or drop any objects in the database (in other words, execute any DDL statements).
• db_denydatareader: Users associated with this role are denied the ability to see any data in the database, though they may still see the data through stored procedures.
• db_denydatawriter: Much like the db_denydatareader role, users associated with this role are denied the ability to modify any data in the database, though they still may modify data through stored procedures.
• db_securityadmin: Users associated with this role can modify and change permissions and roles in the database.

Of particular interest in these groups to many DBAs and developers are the db_datareader and db_datawriter roles. All too often these roles (or, unfortunately, the db_owner role) are the only permissions ever used in the database. For most any database, this should rarely be the case. Even when the bulk of the security is being managed by the user interface, there are going to be objects that you may not want users, or even the application, to be able to access. As an example, in my databases, I almost always have a utility schema that I place objects in to implement certain database-level utility tasks. If I wanted to keep up with the counts of rows in tables on a daily basis, I would create a row in the table each day with the row count of each table. If I wanted a procedure to drop all of the constraints on a database for a given process, I would have a procedure in the utility schema as well. If users accidentally execute that procedure instead of the benign query procedure they were trying to click, it is your fault, not theirs.

The point is that security should be well planned out and managed in a thoughtful manner, not just managed by giving full access and hoping for the best from the user interface standpoint. As I will introduce in the “Schemas” section, instead of using the db_datareader fixed role, consider granting SELECT permissions at the schema level. If you do, any new schema added for some purpose will not automatically be accessible to everyone by the db_datareader membership, but all of the objects in that schema (even new ones) will automatically get the existing schema permission. My goal is to limit fixed role use to utility users, perhaps an admin type, or an ETL program’s access that will not be doing any ad hoc queries that could be in error (after considerable testing, naturally).

User-Defined Database Roles

Just like at the server level, you can create your own database roles to grant rights to database objects. To a role, you can give or deny rights to use tables and code in the database, as well as database-level rights such as ALTER, ALTER ANY USER, DELETE (from any table), CREATE ROLE, and so on. You can control rights to database management and data usage together in the same package, rather than needing to grant users ownership of the database where they have unlimited power to make your day busy restoring from backups and fixing the database.

Roles should be used to create a set of database rights for a job description, or perhaps an aspect of a job description. Take, for example, any typical human resources system that has employee information such as name, address, position, manager, pay grade, and so on. We’ll likely need several roles, such as the following to cover all the common roles that individuals and some processes need to do their job:

• Administrators: Should be able to do any task with data, including ad hoc access; will also need rights to back up and restore the database on occasion (using a user interface, naturally).
• HRManagers: Should be able to do any task in the system with data.
• HRWorkers: Can maintain any attribute in the system, but approval rows are required to modify salary information.
• Managers: All managers in the company might be in a role like this, which might give them view rights to high-level corporate information. You can then limit them to only the ability to see the details for their own workers, using further techniques I’ll present in the section “Implementing Configurable Row-Level Security with Views” later in this chapter.
• Employees: Can see only their own information and can modify only their own personal address information.

Each of the roles would then be granted access to all the resources that they need. A member of the Managers role would likely also be a member of the Employees role. That way, the managers could see the information for their employees and also for themselves. Users can be members of multiple roles, and roles can be members of other roles. Permissions are additive, so if a user is a member of three roles, the user has an effective set of permissions that’s the union of all permissions of the groups, for example:

• Managers: Can view the Employees table
• Employees: Can view the Product table
• HRWorkers: Can see employment history

If the Managers role were a member of the Employees role, a member of the Managers role could do activities that were enabled by either role. If a user were a member of the HRWorkers role and the Employees role, the user could see employment history and the Product table (it might seem logical that users could see the Employees table, but this hasn’t been explicitly set in our tiny example). If a manager decides that making the lives of others miserable is no longer any fun, as part of the demotion, that user would be removed from the Managers role.

I won’t do it in this example, but you may then want to create a role titled TypeOfManager that is a member of all three roles. This would allow you to define the roles in the company to the level of employee A is a member of TypeOfManager, and it covers all that you need. A SecondTypeOfManager may have several of the same role memberships, but also have a few more. Crafting a security configuration is complex, but once it is created and tested, it is a wonderful thing.

Programmatically, you can determine some basic information about a user’s security information in the database with the following functions:

• IS_MEMBER(’<role>’): Tells you whether the current user is the member of a given role. This is useful for building security-based views. You can also pass in a Windows Group to see if the user is a member of that group.
• USER: Tells you the current user’s name in the database.
• HAS_PERMS_BY_NAME: Lets you interrogate the security system to see what rights a user has. This function has a complex public interface, but it’s powerful and useful.

You can use these functions in applications and T-SQL code to determine at runtime what the user can do. For example, if you wanted only HRManager members to execute a procedure, you could check this:

SELECT IS_MEMBER(’HRManager’);

A return value of 1 means the user is a member of the role (0 means not a member, and NULL means the role doesn’t exist). A procedure might start out like the following:

IF (SELECT IS_MEMBER(’HRManager’)) = 0 or (SELECT IS_MEMBER(’HRManager’)) IS NULL
       SELECT ’I..DON’’T THINK SO!’;

This prevents even the database owner from executing the procedure, though dbo users can obviously get the code for the procedure and execute it if they’re desirous enough (the “Monitoring and Auditing” section of this chapter covers some security precautions to handle nosy DBA types), though this is generally a hard task to make bulletproof enough.

For example, in our HR system, if you wanted to remove access to the salaryHistory table just from the Employees role, you wouldn’t deny access to the Employees role, because managers are employees also and would need to have rights to the SalaryHistory table. To deal with this sort of change, you might have to revoke rights to the Employees role and then give rights to the other groups, rather than deny rights to a group that has lots of members.

As an example, consider that you have three users in the database:

CREATE USER Frank WITHOUT LOGIN;
CREATE USER Julie WITHOUT LOGIN;
CREATE USER Rie WITHOUT LOGIN;

Julie and Rie are members of the HRWorkers role, so add

CREATE ROLE HRWorkers;
ALTER ROLE HRWorkers ADD MEMBER Julie;
ALTER ROLE HRWorkers ADD MEMBER Rie;

Next, you have a Payroll schema, and in this is (at the least) an EmployeeSalary table:

CREATE SCHEMA Payroll;
GO
CREATE TABLE Payroll.EmployeeSalary
(
    EmployeeId  int NOT NULL CONSTRAINT PKEmployeeSalary PRIMARY KEY,
    SalaryAmount decimal(12,2) NOT NULL
);
GRANT SELECT ON Payroll.EmployeeSalary to HRWorkers;

Next, test the users:

EXECUTE AS USER = ’Frank’;
SELECT *
FROM   Payroll.EmployeeSalary;

This returns the following error, because Frank isn’t a member of this group:

Msg 229, Level 14, State 5, Line 253
The SELECT permission was denied on the object ’EmployeeSalary’, database ’ClassicSecurityExample’, schema ’Payroll’.

However, change over to Julie

REVERT;
EXECUTE AS USER = ’Julie’;
SELECT *
FROM   Payroll.EmployeeSalary;

and you find that Julie can view the data of tables in the Payroll schema because Julie is a member of the role that was granted SELECT permissions to the table:

EmployeeId  SalaryAmount
----------- ---------------------------------------

Roles are almost always the best way to apply security in a database. Instead of giving individual users specific rights, develop roles that match job positions. Granting rights to an individual is not necessarily bad. To keep this section reasonable, I won’t extend the example to include multiple roles, but a user can be a member of many roles, and the user gets the cumulative effect of the chosen rights. So if there is an HRManagers role and Julie is a member of this group as well as the HRWorkers role, the rights of the two groups would effectively be UNIONed. The result would be the user’s rights.

There’s one notable exception: one DENY operation prevents another’s GRANT operations from applying. Say Rie has had rights to the EmployeeSalary table denied:

REVERT;
DENY SELECT ON Payroll.EmployeeSalary TO Rie;

If Rie now tried to select from the table

EXECUTE AS USER = ’Rie’;
SELECT *
FROM   Payroll.EmployeeSalary;

access would be denied:

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object ’EmployeeSalary’, database ’ClassicSecurityExample’, schema ’Payroll’.

This denial of access is true even though Rie was granted rights via the HRWorkers group. This is why DENY is generally not used much. Rarely will you punish users via rights, if for no other reason than keeping up with the rights can be too difficult. You might apply DENY to a sensitive table or procedure to be certain it wasn’t used, but only in limited cases.

If you want to know from which tables the user can SELECT, you can use a query such as the following while in the context of the user. Reverting to your sysadmin login–based user, executing this query will return the three tables we have created so far in this database. A bit more interesting is what happens when I check the permissions when the user is Julie:

REVERT ;
EXECUTE AS USER = ’Julie’;
--note, this query only returns rows for tables where the user has SOME rights
SELECT  TABLE_SCHEMA + ’.’ + TABLE_NAME AS tableName,
        HAS_PERMS_BY_NAME(TABLE_SCHEMA + ’.’ + TABLE_NAME, ’OBJECT’, ’SELECT’)
                                                                 AS allowSelect,
        HAS_PERMS_BY_NAME(TABLE_SCHEMA + ’.’ + TABLE_NAME, ’OBJECT’, ’INSERT’)
                                                                 AS allowInsert
FROM    INFORMATION_SCHEMA.TABLES;
REVERT ; --so you will be back to sysadmin rights for next code

This returns

tableName                allowSelect allowInsert
------------------------ ----------- -----------
Payroll.EmployeeSalary   1           0

User Julie has rights to see only one of the tables we have created, and has only select rights. Applications that use direct access to the tables can use a query such as this to determine what actions users can do and adjust the user interface to match their rights. Finally, you will need to use REVERT to go back to the security context of the power user to continue to the next examples.

Application Roles

Developers commonly like to set up applications using a single login and then manage security in the application. This can be an adequate way to implement security, but it requires you to re-create all the login stuff, when you could use simple Windows Authentication to check whether a user can execute an application. Application roles let you use the SQL Server login facilities to manage who a person is and if that person has rights to the database and then let the application perform the finer points of security.

To be honest, this can be a nice mix, because the hardest part of implementing security isn’t restricting a person’s ability to do an activity; it’s nicely letting them know by hiding actions they cannot do. I’ve shown you a few of the security catalog views already, and there are more in Books Online. Using them, you can query the database to see what a user can do to help facilitate this process. However, it isn’t a trivial task and is often considered too much trouble, especially for homegrown applications.

An application role is almost analogous to using EXECUTE AS to set rights to another user, but instead of a person, the user is an application. You change to the context of the application role using sp_setapprole. You grant the application role permissions just like any other role, by using the GRANT statement.

As an example of using an application role, create both a user named Bob and an application role and give them totally different rights. The TestPerms schema was created earlier, so if you didn’t create it before, go ahead and do so.

CREATE TABLE TestPerms.BobCan
(
    BobCanId int NOT NULL IDENTITY(1,1) CONSTRAINT PKBobCan PRIMARY KEY,
    Value varchar(10) NOT NULL
);
CREATE TABLE TestPerms.AppCan
(
    AppCanId int NOT NULL IDENTITY(1,1) CONSTRAINT PKAppCan PRIMARY KEY,
    Value varchar(10) NOT NULL
);

Now, create the user Bob to correspond to the BobCan table:

CREATE USER Bob WITHOUT LOGIN;

Next, give Bob SELECT rights to the BobCan table:

GRANT SELECT on TestPerms.BobCan to Bob;
GO

Finally, create an application role, and give it rights to its table:

CREATE APPLICATION ROLE AppCan_application with password = ’39292LjAsll2$3’;
GO
GRANT SELECT on TestPerms.AppCan to AppCan_application;

One of the drawbacks to using an application role is that it requires a password. This password is passed in clear text to the SQL Server, so make sure that, first, the password is complex and, second, you encrypt any connections that might be using these when there’s a threat of impropriety. There is an encryption option that will obfuscate the password, but it is only available with an ODBC or OLE DB client. For more security, use an encrypted connection.

Next, set the user you’re working as to Bob and try to retrieve data from the BobCan table:

EXECUTE AS USER = ’Bob’;
SELECT * FROM TestPerms.BobCan;

It works with no error:

BobCanId    Value
----------- ----------

However, try retrieving data from the AppCan table:

SELECT * FROM TestPerms.AppCan;

The following error is returned:

Msg 229, Level 14, State 5, Line 315
The SELECT permission was denied on the object ’AppCan’, database ’ClassicSecurityExample’, schema ’TestPerms’.

This isn’t surprising, because Bob has no permissions on the AppCan table. Next, still in the context of user Bob, use the sp_setapprole procedure to change the security context of the user to the application role, and the security is reversed:

EXECUTE sp_setapprole ’AppCan_application’, ’39292LjAsll2$3’;
GO
SELECT * FROM TestPerms.BobCan;

This returns the following error:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ’BobCan’, database ’ClassicSecurityExample’, schema ’TestPerms’.

That’s because you’re now in context of the application role, and the application role doesn’t have rights to the table. Finally, the application role can read from the AppCan table:

SELECT * from TestPerms.AppCan;

This doesn’t return an error:

AppCanId    Value
----------- ----------

When you’re in the application role context, you look to the database as if you’re the application, not your user, as evidenced by the following code:

SELECT USER AS userName;

This returns the following result:

userName             
--------------------
AppCan_application   

Once you’ve executed sp_setapprole, the security stays as this role until you disconnect from the SQL Server server or execute sp_unsetapprole. However, sp_unsetapprole doesn’t work nearly as elegantly as REVERT, because you need to have a “cookie” value stored to be able to go back to your previous database security context.

To demonstrate, log back in as your sysadmin role user:

--Note that this must be executed as a single batch because of the variable
--for the cookie
DECLARE @cookie varbinary(8000);
EXECUTE sp_setapprole ’AppCan_application’, ’39292LjAsll2$3’
              , @fCreateCookie = true, @cookie = @cookie OUTPUT;
SELECT @cookie as cookie;
SELECT USER as beforeUnsetApprole;
EXEC sp_unsetapprole @cookie;
SELECT USER as afterUnsetApprole;
REVERT; --done with this user

This returns the following results:

Cookie
-------------------------------------------------------------------------------------------
0x39881A28E9FB46A0A002ABA31C11B7F4C149D8CB2BCF99B7863FFF729E2BE48F13C0F83BAD62CF0B221A863B83
beforeUnsetApprole
--------------------------------------------------------------------------AppCan_application
afterUnsetApprole
-----------------------------------------------------------------------------------------dbo

The cookie is an interesting value, much larger than a GUID—it is declared as varbinary(8000) in Books Online, even though the current value is considerably less wide. It does change for each execution of the batch. The fact is, it is fairly unlikely to want to unset the application role for most usages.

Schemas

Schemas were introduced and used heavily in the previous chapters, and up to this point, they’ve been used merely as a method to group like objects. Logical grouping is an important usage of schemas, but it is only the first step. Using these logical groups to apply security is where they really pay off. A user owns a schema, and a user can also own multiple schemas. For most any database that you’ll develop for a system, the best practice is to let all schemas be owned by the dbo system user. You might remember from versions before 2005 that dbo owned all objects, and although this hasn’t technically changed, it is the schema that is owned by dbo, and the table in the schema. Hence, instead of the reasonably useless dbo prefix being attached to all objects representing the owner, you can nicely group together objects of a common higher purpose and then (because this is a security chapter) grant rights to users at a schema level, rather than at an individual object level.

For our database-design purposes, we will assign rights for users to use the following:

• Tables
• Views
• Synonyms (which can represent any of these things and more)
• Functions
• Procedures

You can grant rights to other types of objects, including user-defined aggregates, queues, and XML schema collections, but I won’t cover them here. As an example, in the WideWorldImporters database, use the following query of the sys.objects catalog view (which reflects schema-scoped objects):

USE WideWorldImporters; --or whatever name you have given it
GO
SELECT  SCHEMA_NAME(schema_id) AS schema_name, type_desc, COUNT(*)
FROM    sys.objects
WHERE   type_desc IN (’SQL_STORED_PROCEDURE’,’CLR_STORED_PROCEDURE’,
                      ’SQL_SCALAR_FUNCTION’,’CLR_SCALAR_FUNCTION’,
                      ’CLR_TABLE_VALUED_FUNCTION’,’SYNONYM’,
                      ’SQL_INLINE_TABLE_VALUED_FUNCTION’,
                      ’SQL_TABLE_VALUED_FUNCTION’,’USER_TABLE’,’VIEW’)
GROUP BY  SCHEMA_NAME(schema_id), type_desc
ORDER BY schema_name;
GO
USE ClassicSecurityExample;

This query shows how many of each object can be found in the version of the WideWorldImporters database in each schema I have on your development machine. As shown in syntax previously in this chapter, to grant privileges to a schema to a role or user, you prefix the schema name with SCHEMA:: to indicate the type of object you are granting to. To give the users full usage rights to all these, you can use the following command:

GRANT EXECUTE, SELECT, INSERT, UPDATE, DELETE ON
                                         SCHEMA::<schemaname> to <database_principal>;

By using schemas and roles liberally, the complexity of granting rights to users on database objects can be pretty straightforward. That’s because, instead of having to make sure rights are granted to 10 or even 100 stored procedures to support your application’s Customer section, you need just a single line of code:

GRANT EXECUTE on SCHEMA::Customer to CustomerSupport;

Bam! Every user in the CustomerSupport role now can execute all stored procedures in this schema. Nicer still is that even new objects added to the schema at a later date will be automatically accessible to people with rights at the schema level. For example, create a user named Tom; then, grant Tom SELECT rights on the TestPerms schema created in a previous section:

USE ClassicSecurityExample;
GO
CREATE USER Tom WITHOUT LOGIN;
GRANT SELECT ON SCHEMA::TestPerms TO Tom;

Immediately, Tom has rights to select from the tables that have been created:

EXECUTE AS USER = ’Tom’;
GO
SELECT * FROM TestPerms.AppCan;
GO
REVERT;

But also, Tom gets rights to the new table that we create here:

CREATE TABLE TestPerms.SchemaGrant
(
    SchemaGrantId int primary key
);
GO
EXECUTE AS USER = ’Tom’;
GO
SELECT * FROM TestPerms.SchemaGrant;
GO
REVERT;

Essentially, a statement like GRANT SELECT ON SCHEMA:: is a much better way to give a user read rights to the database than using the db_datareader fixed database role, especially if you use schemas. This ensures that if a new schema is created and some users shouldn’t have access, they will not automatically get access, but it also ensures that users get access to all new tables that they should get access to.

Row-Level Security

So far, we have discussed only methods of securing data at the object level. If you have access to Table A, you have access to all of the rows of Table A. In this section, we will look at several methods of securing the rows of the table, letting a user see only part of the rows in the table. In SQL Server 2016, there is a new feature called Row-Level Security, but row-level security is something that I have needed for 20+ years, and the methods previously used are still interesting for now and years to follow.

We will look at three methods for performing row-level security, including the new feature:

• Specific-purpose views: Creating a view that represents a specific subset of rows in a table.
• Row-Level Security feature: There are several ways this feature will work, allowing you to show a subset to a user with very few changes to your application architecture.
• Data-driven security views: Basically the precursor to the Row-Level Security feature.

For the examples in this section, we will continue using the Products.Product table. To remind you of the structure, it is repeated here:

CREATE TABLE Products.Product
(
    ProductId   int NOT NULL IDENTITY CONSTRAINT PKProduct PRIMARY KEY,
    ProductCode varchar(10) NOT NULL CONSTRAINT AKProduct_ProductCode UNIQUE,
    Description varchar(20) NOT NULL,
    UnitPrice   decimal(10,4) NOT NULL,
    ActualCost  decimal(10,4) NOT NULL
);

To keep our examples fairly simple, I am going to add a categorization column that our security examples will use to partition on and set the values to our usual generally silly values:

ALTER TABLE Products.Product
   ADD ProductType varchar(20) NOT NULL
                        CONSTRAINT DFLTProduct_ProductType DEFAULT (’not set’);
GO
UPDATE Products.Product
SET    ProductType = ’widget’
WHERE  ProductCode = ’widget12’;
GO
UPDATE Products.Product
SET    ProductType = ’snurf’
WHERE  ProductCode = ’snurf98’;

Looking at the data in the table, you can see the following contents that we will work with, giving a database principal access to a given product type:

ProductId   ProductCode Description          UnitPrice    ActualCost    ProductType
----------- ----------- -------------------- ------------ ------------- ------------------- 1           widget12    widget number 12     10.5000      8.5000        widget
2           snurf98     Snurfulator          99.9900      2.5000        snurf

One concept that we need to introduce at this point is ownership chaining. Controlling security with coded objects requires an understanding of how ownership affects rights to objects. For example, if a user owns a stored procedure and that stored procedure uses other objects it owns, the user who executes the procedure doesn’t need direct rights to the other objects. The name for the way rights are allowed on owned objects in coded objects is

Just because a user can use a stored procedure or function doesn’t necessarily mean that he or she will need to have rights to every object to which the stored procedure refers. As long as the owner or the object owns all the schemas for all the objects that are referenced, the ownership chain isn’t broken, and any user granted rights to use the object can see any referenced data. If you break the ownership chain and reference data in a schema not owned by the same user, the user will require rights granted directly to the object, instead of the object being created. This concept of the ownership chain is at the heart of why controlling object access via coded objects is so nice.

We will see more of ownership chaining later in the chapter when we discuss stored procedures, but it is important at this point because it affects row-level security. Say we have table S.T, and a view that is defined as CREATE VIEW S.V AS SELECT C FROM S.T;. If a user has rights to view S.V, as long as the tables referenced in the view (in this case S.T) are owned by the same database principal, no rights are needed on the objects used. This allows us to use views (and later functions and procedures) to control access to objects situationally. In this section we will use views to limit access to a table, and the user will have rights to the view only.

Using Specific-Purpose Views to Provide Row-Level Security

The absolute simplest version of row-level security is just building views to partition the data in some form, such as all products of a given type, employees in Division X, etc. Whether or not this makes sense for your usage is really up to how the data will be used. Usually this is not going to be flexible enough for your needs, but it really depends on how deep the domain is you are working with.

As an example, our products table is a case where, assuming the range of values supported by ProductType isn’t large, simple row-level security may make sense. So let’s configure a view that allows you to just see one particular type of product. You can build the following view:

CREATE VIEW Products.WidgetProduct
AS
SELECT ProductId, ProductCode, Description, UnitPrice, ActualCost, ProductType
FROM   Products.Product
WHERE  ProductType = ’widget’
WITH   CHECK OPTION; --This prevents the user from INSERTING/UPDATING data that would not
                     --match the view’s criteria

Note a few important things about this view. First, I named it like it was a table. Usually when we use a view for row-level security, we want it to seem like a table to the user. Second, I included the ProductType column. We would need that column if we wanted to allow the user to INSERT new rows into the table using the view. Using an INSTEAD OF trigger, we could get around the need to include missing columns, if desired, but this is a lot easier to build. Finally, the CHECK OPTION says that if someone inserts data using the view, the ProductType would have to be set to ’widget’ or it would fail.

To test this, I will create a user chrissy who I will give access to the view:

CREATE USER chrissy WITHOUT LOGIN;
GO
GRANT SELECT ON Products.WidgetProduct TO chrissy;

Now the user comes in, does their query, and only sees the subset of rows:

EXECUTE AS USER = ’chrissy’;
SELECT *
FROM   Products.WidgetProduct;

This returns the following result:

ProductId   ProductCode Description          UnitPrice    ActualCost    ProductType
----------- ----------- -------------------- ------------ ------------- ------------------1           widget12    widget number 12     10.5000      8.5000        widget

However, just to make sure, try to SELECT rows from the Product table:

SELECT *
FROM   Products.Product;
GO
REVERT;

This returns the following error message:

Msg 229, Level 14, State 5, Line 423
The SELECT permission was denied on the object ’Product’, database ’ClassicSecurityExample’, schema ’Products’.

You can grant INSERT, UPDATE, and DELETE rights to the user to modify the view as well, because it’s based on one table and we set the WITH CHECK OPTION. This view can then have permissions granted to let only certain people use it. This is a decent technique when you have an easily described set, or possibly few types to work with, but can become a maintenance headache. I won’t demonstrate it in the text, but there is code in the downloads that shows modification rights.

In the next step, let’s build in some more flexible security to a view, allowing everyone to see any ProductType other than ’snurf’, and only members of a certain group (’snurfViewer’, naturally) to view the snurf products:

CREATE VIEW Products.ProductSelective
AS
SELECT ProductId, ProductCode, Description, UnitPrice, ActualCost, ProductType
FROM   Products.Product
WHERE  ProductType <> ’snurf’
   or  (IS_MEMBER(’snurfViewer’) = 1)
   or  (IS_MEMBER(’db_owner’) = 1) --can’t add db_owner to a role
WITH CHECK OPTION;

I called this ProductSelective, but here is where the naming gets tricky. If this is a reporting view, I might call it ProductView. In this case, I am treating it like it is meant to be used like a normal table. I will set the view to be usable by anyone who has access to the database (good for demo, perhaps less good for production work!). Note that you are not limited to such a simple predicate for the view, but the more complex the need, the more taxing it might be on performance.

GRANT SELECT ON Products.ProductSelective to public;

Next, add a new snurfViewer role. Note that you don’t add this user to the group yet; you’ll do that later in the example.

CREATE ROLE snurfViewer;

Then, change security context to chrissy and select from the view:

EXECUTE AS USER = ’chrissy’;
SELECT * from Products.ProductSelective;
REVERT;

This returns the one row to which chrissy has access:

ProductId   ProductCode Description          UnitPrice    ActualCost    ProductType
----------- ----------- -------------------- ------------ ------------- ------------------1           widget12    widget number 12     10.5000      8.5000        widget

Next, add chrissy to the snurfViewer group, go back to context as this user, and run the statement again:

ALTER ROLE snurfViewer ADD MEMBER chrissy;
GO
EXECUTE AS USER = ’chrissy’;
SELECT *
FROM Products.ProductsSelective;
REVERT;

Now, you see all the rows:

ProductId   ProductCode Description          UnitPrice    ActualCost    ProductType
----------- ----------- -------------------- ------------ ------------- -------------------1           widget12    widget      number 12            10.5000      8.5000        widget
2           snurf98     Snurfulator          99.9900      2.5000        snurf

This technique of using specific, hard-coded views is the simplest to configure, but lacks some level of elegance, and ends up being very difficult to manage. The next methods will take this concept to a deeper level.

Using the Row-Level Security Feature

Row-Level Security (RLS) is a new feature in SQL Server 2016 that gives you highly configurable methods of securing rows in a table in a manner that may not require any changes to an application. Basically, you configure one or more predicate functions (a simple table-valued user-defined function that returns either 1 or nothing) to indicate that a user can or cannot see or modify a row in the table. I will note too that, depending on how much access a user has to a server, there are a few side-channel ways a user might be able to infer the data in the table. RLS is designed to block these side channels. For example, when using RLS, a user will not be able to see the plan of the query (since that could show estimated row counts). For more information, check the Row-Level Security page in Books Online: https://msdn.microsoft.com/en-us/library/dn765131.aspx.

In the following example, we will continue with the same scenario from the previous section, but instead of creating a view, we will apply the security to the table itself (you can apply Row-Level Security to a schema-bound view just like you can a full table).

To start with, we will reset things from the previous scenario by giving a user rights to read and modify the rows of the Products.Product table. In order to make security easier to manage, we will put all of the row-level security objects in a separate schema. Unlike the separate schemas we created for the temporal feature in Chapter 8, no users will need access to the objects in this schema other than when we are testing the functionality, so we will just create one schema:

CREATE SCHEMA RowLevelSecurity;

Next we will create a user-defined function that provides the exact same security configuration as we used in the previous section for a view. Every user can see all rows except the ones of product type ’snurf’, and the db_owner role members can see everything. The database principal will also need SELECT rights to the table we will apply this to.

CREATE FUNCTION RowLevelSecurity.Products_Product$SecurityPredicate
                                                (@ProductType AS varchar(20))
    RETURNS TABLE
WITH SCHEMABINDING --not required, but a good idea nevertheless
AS
    RETURN (SELECT 1 AS Products_Product$SecurityPredicate  
            WHERE  @ProductType <> ’snurf’
                           OR  (IS_MEMBER(’snurfViewer’) = 1)
                           OR (IS_MEMBER(’db_owner’) = 1));

Unlike with most security features in SQL Server, sa and dbo users are subject to the row-level security predicates that are configured. Most of the time, for supportability, you will want to not make that the case, but it can be useful. Another difference between this and other security features is how they behave in coded objects. By default, even when using the base table in an object like a procedure, rows will be filtered by row-level security. I will show this later in the chapter when I discuss impersonation in objects.

While you will not generally want to grant rights to the security function, when testing it is useful to make sure how the function will behave on its own. So we start by creating and granting a new user rights to select from the function:

CREATE USER valerie WITHOUT LOGIN;
GO
GRANT SELECT ON RowLevelSecurity.Products_Product$SecurityPredicate TO valerie;

Now we test to see what the output will be:

EXECUTE AS USER = ’valerie’;
GO
SELECT ’snurf’ AS ProductType,*
FROM   rowLevelSecurity.Products_Product$SecurityPredicate(’snurf’)
UNION ALL
SELECT ’widget’ AS ProductType,*
FROM   rowLevelSecurity.Products_Product$SecurityPredicate(’widget’);
REVERT;

This returns just the one row for the widget type of row:

ProductType Products_Product$SecurityPredicate
----------- ----------------------------------
widget      1

When we feel confident that the function is correct, we remove user access from the row-level security function:

REVOKE SELECT ON RowLevelSecurity.Products_Product$SecurityPredicate TO valerie;

Now that we have the function created, we apply it using the CREATE SECURITY POLICY statement. You can create two kinds of predicates: FILTER,  which filters out viewing rows, and BLOCK, which disallows certain operations. We will start with a simple filter. The user will need to have access to SELECT from the table already, and then this security policy will take away access to certain rows within.

The following command adds a filter predicate that will pass in the ProductType value to the function and see if it returns anything. Because this is row-level security, this function will be executed once per row, so keep the function as simple as possible to limit the overhead. Since it is a simple table-valued function, it is much more difficult to make things slow, but it is always possible.

CREATE SECURITY POLICY RowLevelSecurity.Products_Product_SecurityPolicy
    ADD FILTER PREDICATE rowLevelSecurity.Products_Product$SecurityPredicate(ProductType)
    ON Products.Product
    WITH (STATE = ON, SCHEMABINDING = ON);

State=ON turns on the policy to be checked. SCHEMABINDING=ON says that the predicate functions must all be schema bound. Not having the function schema bound would allow you to access objects in a different database if you needed to, but generally it is considered a bad idea. You can only have one enabled filter predicate per table. If you try to put another in the same policy, you get either message, depending on if you try to create two in the same policy, or different policies. For example:

CREATE SECURITY POLICY rowLevelSecurity.Products_Product_SecurityPolicy2
    ADD FILTER PREDICATE rowLevelSecurity.Products_Product$SecurityPredicate(ProductType)
    ON Products.Product
    WITH (STATE = ON, SCHEMABINDING= ON);

This will cause the following message:

Msg 33264, Level 16, State 1, Line 607
The security policy ’rowLevelSecurity.Products_Product_SecurityPolicy2’ cannot be enabled with a predicate on table ’Products.Product’. Table ’Products.Product’ is already referenced by the enabled security policy ’rowLevelSecurity.Products_Product_SecurityPolicy’

We start by giving the user all rights on the table, to enable the rest of the demos. We will limit the user’s ability to perform these tasks using row-level security.

GRANT SELECT, INSERT, UPDATE, DELETE ON Products.Product TO valerie;

Now, with the policy in place, let’s see what the user can see:

EXECUTE AS USER = ’valerie’;
SELECT *
FROM   Products.Product;
REVERT;
ProductId   ProductCode Description          UnitPrice    ActualCost    ProductType
----------- ----------- -------------------- ------------ ------------- ------------------
1           widget12    widget number 12     10.5000      8.5000        widget

We have filtered the rows down to the non-snurf rows. The filter works not just for SELECT, but for DELETE and UPDATE too. For example, we know that there are snurf rows, but executing as user valerie, they can’t be modified:

EXECUTE AS USER = ’valerie’;
DELETE Products.Product
WHERE  ProductType = ’snurf’;
REVERT;
--back as dbo user
SELECT *
FROM   Products.Product
WHERE  ProductType = ’snurf’;
ProductId   ProductCode Description          UnitPrice    ActualCost    ProductType
----------- ----------- -------------------- ------------ ------------- ------------------
2           snurf98     Snurfulator          99.9900      2.5000        snurf

However, just because we can’t see a row doesn’t mean we can’t create a row of that type:

EXECUTE AS USER = ’valerie’;
INSERT INTO Products.Product (ProductCode, Description, UnitPrice, ActualCost,ProductType)
VALUES  (’Test’ , ’Test’ , 100 , 100  , ’snurf’);
SELECT *
FROM   Products.Product
WHERE  ProductType = ’snurf’;
REVERT;
SELECT *
FROM   Products.Product
WHERE  ProductType = ’snurf’;

We get back:

ProductId   ProductCode Description          UnitPrice    ActualCost    ProductType
----------- ----------- -------------------- ------------ ------------- -------------------
2           snurf98     Snurfulator          99.9900      2.5000        snurf
ProductId   ProductCode Description          UnitPrice    ActualCost    ProductType
----------- ----------- -------------------- ------------ ------------- -----------------
2           snurf98     Snurfulator          99.9900      2.5000        snurf
5           Test        Test                 100.0000     100.0000      snurf

To block actions from occurring, we will use a BLOCK predicate. There are two block types:

• AFTER: If the row does not match the security predicate after the operation, it will fail. So if you can see A, but not B, you could not change A to B. But you could change B to A, if you could see it.
• BEFORE: If the row does not match the security predicate before the operation, you cannot perform the operation. So if you can modify A, but not B, you could update A to B, but no longer be able to modify the row again.

We are going to set one of the seemingly obvious set of row-level security predicates that one might set in a realistic scenario for a “managed by user type” column: BLOCK AFTER INSERT. If you can’t see the row, then you can’t create a new row. We will leave the FILTER predicate on for now:

--Note that you can alter a security policy, but it seems easier
--to drop and recreate in most cases.
DROP SECURITY POLICY rowLevelSecurity.Products_Product_SecurityPolicy;
CREATE SECURITY POLICY rowLevelSecurity.Products_Product_SecurityPolicy
    ADD FILTER PREDICATE rowLevelSecurity.Products_Product$SecurityPredicate(ProductType)
    ON Products.Product,
    ADD BLOCK PREDICATE rowLevelSecurity.Products_Product$SecurityPredicate(ProductType)
    ON Products.Product AFTER INSERT
    WITH (STATE = ON, SCHEMABINDING = ON);

Next we will test this by trying to create a new row of ProductType of ’snurf’:

EXECUTE AS USER = ’valerie’;
INSERT INTO Products.Product (ProductCode, Description, UnitPrice, ActualCost,ProductType)
VALUES  (’Test2’ , ’Test2’ , 100 , 100  , ’snurf’);
REVERT;
Msg 33504, Level 16, State 1, Line 696
The attempted operation failed because the target object ’ClassicSecurityExample.Products.Product’ has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.

One last configuration to demonstrate. In cases where the principal can see the row, we use the BEFORE type of predicate. In the following configuration, we will let the user see all rows, but only INSERT, UPDATE, or DELETE rows that meet the predicates:

DROP SECURITY POLICY rowLevelSecurity.Products_Product_SecurityPolicy;
CREATE SECURITY POLICY rowLevelSecurity.Products_Product_SecurityPolicy
    ADD BLOCK PREDICATE rowLevelSecurity.Products_Product$SecurityPredicate(ProductType)
      ON Products.Product AFTER INSERT,
    ADD BLOCK PREDICATE rowLevelSecurity.Products_Product$SecurityPredicate(ProductType)
      ON Products.Product BEFORE UPDATE,
    ADD BLOCK PREDICATE rowLevelSecurity.Products_Product$SecurityPredicate(ProductType)
      ON Products.Product BEFORE DELETE
    WITH (STATE = ON, SCHEMABINDING = ON);

First, let’s see what the data looks like to us now:

EXECUTE AS USER = ’valerie’;
GO
SELECT *
FROM   Products.Product;
ProductId   ProductCode Description          UnitPrice    ActualCost    ProductType
----------- ----------- -------------------- ------------ ------------- ------------------
1           widget12    widget number 12     10.5000      8.5000        widget
2           snurf98     Snurfulator          99.9900      2.5000        snurf
5           Test        Test                 100.0000     100.0000      snurf

Without changing back to the previous security context, try to DELETE the last row created:

DELETE Products.Product
WHERE    ProductCode = ’Test’;

This fails:

Msg 33504, Level 16, State 1, Line 731
The attempted operation failed because the target object ’ClassicSecurityExample.Products.Product’ has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.

Now, since we used BEFORE UPDATE in our predicate, we can update a row to a value that we may not be able to see after the update:

UPDATE Products.Product
SET    ProductType = ’snurf’
WHERE  ProductType = ’widget’;
--We cannot update the row back, even though we can see it:
UPDATE Products.Product
SET    ProductType = ’widget’
WHERE  ProductType = ’snurf’;
REVERT;

This returns

Msg 33504, Level 16, State 1, Line 731
The attempted operation failed because the target object ’ClassicSecurityExample.Products.Product’ has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.

Obviously we have just scratched the surface with the Row-Level Security feature, but you should be able to see that from here you could create a very tight layer of row-level security if needed.

EXEC sys.sp_set_session_context @key = N’SecurityGroup’, @value = ’Management’;

SELECT SESSION_CONTEXT(N’SecurityGroup’);

Using Data-Driven Row-Level Security

In this section, I want to expand on the predicate possibilities for both the view driven and row-level security feature driven row-level security implementation. Instead of embedding access through the code, we create a table that maps database role principals with some data in the table. In the case of our example scenarios, different types of products can be expanded and configured over time without any code needing to be released, giving total control to the process.

To start the process, we will create the following table that will hold the set of product types that a database role can see:

CREATE TABLE Products.ProductSecurity
(
    ProductType varchar(20), --at this point you probably will create a
                             --ProductType domain table, but this keeps the
                             --example a bit simpler
    DatabaseRole    sysname,
    CONSTRAINT PKProductsSecurity PRIMARY KEY(ProductType, DatabaseRole)
);

Then, we insert a row that will be used to give everyone with database rights the ability to see widget-type products:

INSERT INTO Products.ProductSecurity(ProductType, DatabaseRole)
VALUES (’widget’,’public’);

Next, we create a view named ProductsSelective view to show only rows to which the user has rights, based on row security:

ALTER VIEW Products.ProductSelective
AS
SELECT Product.ProductId, Product.ProductCode, Product.Description,
       Product.UnitPrice, Product.ActualCost, Product.ProductType
FROM   Products.Product as Product
         JOIN Products.ProductSecurity as ProductSecurity
            ON  (Product.ProductType = ProductSecurity.ProductType
                AND IS_MEMBER(ProductSecurity.DatabaseRole) = 1)
                OR IS_MEMBER(’db_owner’) = 1; --don’t leave out the dbo!

This exact same thing is available to the Row-Level Security feature as well. The predicate function can access tables as well. So you could create the function as follows (after dropping the policy that is using it, or you will get an error stating that you can’t alter this function):

ALTER FUNCTION RowLevelSecurity.Products_Product$SecurityPredicate
                                                (@ProductType AS varchar(20))
    RETURNS TABLE
WITH SCHEMABINDING --not required, but a good idea nevertheless
AS
    RETURN (SELECT 1 AS Products_Product$SecurityPredicate  
            WHERE is_member(’db_owner’) = 1
               OR  EXISTS (SELECT 1
                           FROM   Products.ProductSecurity
                           WHERE  ProductType = @ProductType
                             AND  IS_MEMBER(DatabaseRole) = 1));

The primary limitations are performance. Even with this being a new feature, I have heard of people using this feature with lots of rows in their row-level security tables, even using hierarchical data. While the sky is the limit, it is highly recommended before endeavoring to make heavy use of any row-level technique that you test heavily and with multiple users.

Controlling Access to Data via T-SQL–Coded Objects

Just using the database-level security in SQL Server allows you to give a user rights to access only certain objects, but it does not always give you a great amount of fine-grained control. For example, if you want to let a user join to a table to get a value but not to browse the entire table using a SELECT statement, this would be very difficult using table/object-level security alone. However, by using T-SQL coded objects in a very similar manner as we have already done using views for row-level security, it is very possible.

Now, we get down to the business of taking complete control over database access by using the following types of objects:

• Stored procedures and scalar functions: These objects give users an API to the database, and then, the DBA can control security based on what the procedure does.
• Views and table-valued functions: In cases where the tools being used can’t use stored procedures, you can still use views to present an interface to the data that appears to the user as a normal table would. In terms of security, views and table-valued functions can be used for partitioning data vertically by hiding columns or, as seen earlier, horizontally by providing row-level security.

I put stored procedures together with views and functions in this section because whichever option you choose, you will still have accomplished the separation of interface from implementation. As long as the contract between the stored procedure or view is what the developer or application is coding or being coded to, the decision of which option to select will offer different sorts of benefits.

Stored Procedures and Scalar Functions

Security in stored procedures and functions is always at the object level (though the row-level security policies will still be enforced). Using stored procedures and functions to apply security is quite nice because you can give the user rights to do many operations without the user having rights to do the same operations on their own (or even knowing how they’re done).

In some companies, stored procedures are used as the primary security mechanism, by requiring that all access to the server be done without executing a single ad hoc or “raw” DML statement against the tables. By building code that encapsulates all functionality, you then can apply permissions to the stored procedures to restrict what the user can do.

In security terms only, this allows you to have situational control over access to a table. This means that you might have two different procedures that functionally do the same operation, but giving a user rights to one procedure doesn’t imply that he or she has rights to the other. (I will discuss more about the pros and cons of different access methods in Chapter 13, but in this chapter, I will limit the discussion to the security aspects.)

Take, for example, the case where a form is built using one procedure. The user might be able to do an action, such as deleting a row from a specific table, but when the user goes to a different application window that allows deleting 100 rows, that ability might be denied. What makes this even nicer is that with decent naming of your objects, you can give end users or managers rights to dole out security based on actions they want their employees to have, without needing the IT staff to handle it.

As an example, let’s create a new user for the demonstration:

CREATE USER ProcUser WITHOUT LOGIN;

Then (as dbo), create a new schema and table:

CREATE SCHEMA ProcTest;
GO
CREATE TABLE ProcTest.Misc
(
    GeneralValue varchar(20),
    SecretValue varchar(20)
);
GO
INSERT INTO ProcTest.Misc (GeneralValue, SecretValue)
VALUES (’somevalue’,’secret’),
       (’anothervalue’,’secret’);

Next, we will create a stored procedure to return the values from the GeneralValue column in the table, not the SecretValue column, and then grant rights to the procUser to execute the procedure:

CREATE PROCEDURE ProcTest.Misc$Select
AS
    SELECT GeneralValue
    FROM   ProcTest.Misc;
GO
GRANT EXECUTE on ProcTest.Misc$Select to ProcUser;

After that, we change the context to the procUser user and try to SELECT from the table:

EXECUTE AS USER = ’ProcUser’;
GO
SELECT GeneralValue , SecretValue
FROM   ProcTest.Misc;

We get the following error message, because the user hasn’t been given rights to access this table:

Msg 229, Level 14, State 5, Line 768
The SELECT permission was denied on the object ’Misc’, database ’ClassicSecurityExample’, schema ’ProcTest’.

However, when we execute the following procedure:

EXECUTE ProcTest.Misc$Select;

we get the expected result, that the user does have access to execute the procedure:

GeneralValue
--------------------
somevalue
anothervalue

This is one of the best ways to architect a database solution, both for security and for performance. It leaves a manageable surface area, gives you a lot of control over what SQL is executed in the database, and lets you control data security nicely. (For performance it allows caching of complex plans and having a known set of queries to tune, but more on that in Chapter 13.)

You can see what kinds of access a user has to stored procedures by executing the following statement:

SELECT SCHEMA_NAME(schema_id) +’.’ + name AS procedure_name
FROM   sys.procedures;
REVERT;

While in the context of the procUser, you will see the one row for the procTest.misc$select procedure returned. If you were using only stored procedures to access the data, this query could be executed by the application programmer to know everything the user can do in the database.

Impersonation Within Objects

I already talked about the EXECUTE AS statement, and it has some great applications, but using the WITH EXECUTE clause on an object declaration can give you some incredible flexibility to give the executor greater powers than might have been possible otherwise, certainly not without granting additional rights. Instead of changing context before an operation, you can change context while executing a stored procedure, function, or DML trigger (plus queues for Service Broker, but I won’t be covering that topic). Unfortunately, the WITH EXECUTE clause is not available for views, because they are not technically executable objects (hence the reason why you grant SELECT rights and not EXECUTE ones).

By adding the following clause to the object, you can change the security context of a procedure to a different server or database principal when the execution begins:

CREATE <objectType> <schemaName>.<objectName>
WITH EXECUTE AS <’userName’ | caller | self | owner>; --adding this

The different options for whom to execute as are as follows:

• ’userName’: A specific principal in the database.
• caller: The context of the user who called the procedure. This is the default security context you get when executing an object.
• self: It’s in the context of the user who created the procedure. You can see who self will represent by looking in sys.sql_modules at the execute_as_principal_id column.
• owner: It’s executed in the context of the owner of the module or schema.

Note that using EXECUTE AS doesn’t affect the ownership chaining of the call. The security of the statements in the object is still based on the security of the schema owner. Only when the ownership chain is broken will the EXECUTE AS setting come into play. The following statements go along with the EXECUTE AS clause:

• EXECUTE AS CALLER: If you are using a security context other than EXECUTE AS CALLER, you can execute this in your code to go back to the default, where access is as the user who actually executed the object.
• REVERT: This reverts security to the security specified in the WITH EXECUTE AS clause.

As an example, I’ll show you how to build a scenario where one schema owner contains a table and another schema owner has a table and a procedure that the schema owner wants to use to access the first user’s table. Finally, the scenario has an average user who wants to execute the stored procedure.

First, create a few users and give them rights to create objects in the database. The three users are named as follows:

• SchemaOwner: This user owns the schema where one of the objects resides.
• ProcedureOwner: This user is owner of a table and a stored procedure.
• AveSchlub: This is the average user who finally wants to use procedureOwner’s stored procedure.

So, now create these users and grant them rights:

--this will be the owner of the primary schema
CREATE USER SchemaOwner WITHOUT LOGIN;
GRANT CREATE SCHEMA TO SchemaOwner;
GRANT CREATE TABLE TO SchemaOwner;
--this will be the procedure creator
CREATE USER ProcedureOwner WITHOUT LOGIN;
GRANT CREATE SCHEMA TO ProcedureOwner;
GRANT CREATE PROCEDURE TO ProcedureOwner;
GRANT CREATE TABLE TO ProcedureOwner;
GO
--this will be the average user who needs to access data
CREATE USER AveSchlub WITHOUT LOGIN;

Then, change to the context of the main object owner, create a new schema, and create a table with some rows:

EXECUTE AS USER = ’SchemaOwner’;
GO
CREATE SCHEMA SchemaOwnersSchema;
GO
CREATE TABLE SchemaOwnersSchema.Person
(
    PersonId    int NOT NULL CONSTRAINT PKPerson PRIMARY KEY,
    FirstName   varchar(20) NOT NULL,
    LastName    varchar(20) NOT NULL
);
GO
INSERT INTO SchemaOwnersSchema.Person
VALUES (1, ’Phil’,’Mutayblin’),
       (2, ’Del’,’Eets’);

Next, this user gives SELECT permissions to the ProcedureOwner user:

GRANT SELECT ON SchemaOwnersSchema.Person TO ProcedureOwner;

After that, set context to the secondary user to create the procedure:

REVERT --we can step back on the stack of principals, but we can’t change directly        
       --to procedureOwner without giving ShemaOwner impersonation rights. Here I
       --step back to the db_owner user you have used throughout the chapter
GO
EXECUTE AS USER = ’ProcedureOwner’;

Then, create a schema and another table, owned by the procedureOwner user, and add some simple data for the demonstration:

CREATE SCHEMA ProcedureOwnerSchema;
GO
CREATE TABLE ProcedureOwnerSchema.OtherPerson
(
    PersonId    int NOT NULL CONSTRAINT PKOtherPerson PRIMARY KEY,
    FirstName   varchar(20) NOT NULL,
    LastName    varchar(20) NOT NULL
);
GO
INSERT INTO ProcedureOwnerSchema.OtherPerson
VALUES (1, ’DB’,’Smith’);
INSERT INTO ProcedureOwnerSchema.OtherPerson
VALUES (2, ’Dee’,’Leater’);

You can see the owners of the objects and their schema using the following query of the catalog views:

REVERT;
SELECT tables.name AS [table], schemas.name AS [schema],
       database_principals.name AS [owner]
FROM   sys.tables
         JOIN sys.schemas
            ON tables.schema_id = schemas.schema_id
         JOIN sys.database_principals
            ON database_principals.principal_id = schemas.principal_id
WHERE  tables.name IN (’Person’,’OtherPerson’);

This returns the following:

table               schema                  owner
------------------- ----------------------- -------------------------------
OtherPerson         ProcedureOwnerSchema    ProcedureOwner
Person              SchemaOwnersSchema      SchemaOwner

Next, create two procedures as the procedureOwner user, one for the WITH EXECUTE AS CALLER, which is the default, and then SELF, which puts it in the context of the creator, in this case procedureOwner:

EXECUTE AS USER = ’ProcedureOwner’;
GO
CREATE PROCEDURE ProcedureOwnerSchema.Person$asCaller
WITH EXECUTE AS CALLER --this is the default
AS
BEGIN
   SELECT  PersonId, FirstName, LastName
   FROM    ProcedureOwnerSchema.OtherPerson; --<-- ownership same as proc
   SELECT  PersonId, FirstName, LastName
   FROM    SchemaOwnersSchema.Person;  --<-- breaks ownership chain
END;
GO
CREATE PROCEDURE ProcedureOwnerSchema.Person$asSelf
WITH EXECUTE AS SELF --now this runs in context of procedureOwner,
                     --since it created it
AS
BEGIN
   SELECT  PersonId, FirstName, LastName
   FROM    ProcedureOwnerSchema.OtherPerson; --<-- ownership same as proc
   SELECT  PersonId, FirstName, LastName
   FROM    SchemaOwnersSchema.Person;  --<-- breaks ownership chain
END;

Next, grant rights on the procedure to the AveSchlub user:

GRANT EXECUTE ON ProcedureOwnerSchema.Person$asCaller TO AveSchlub;
GRANT EXECUTE ON ProcedureOwnerSchema.Person$asSelf TO AveSchlub;

Then, change to the context of the AveSchlub:

REVERT; EXECUTE AS USER = ’AveSchlub’; --If you receive error about not being able to                   --impersonate another user, it means you are not executing as dbo..

Finally, execute the procedure:

--this proc is in context of the caller, in this case, AveSchlub
EXECUTE ProcedureOwnerSchema.Person$asCaller;

This produces the following output, because the ownership chain is fine for the ProcedureOwnerSchema object, but not for the SchemaOwnersSchema:

personId    FirstName            LastName
----------- -------------------- --------------------
1           DB                   Smith
2           Dee                  Leater
Msg 229, Level 14, State 5, Procedure person$asCaller, Line 7
The SELECT permission was denied on the object ’Person’, database ’ClassicSecurityExample’, schema ’SchemaOwnersSchema’.

Next, execute the asSelf variant:

    --procedureOwner, so it works
EXECUTE ProcedureOwnerSchema.Person$asSelf;

This returns two result sets:

personId    FirstName            LastName
----------- -------------------- --------------------
1           DB                   Smith
2           Dee                  Leater
personId    FirstName            LastName
----------- -------------------- --------------------
1           Phil                 Mutayblin
2           Del                  Eets

What makes this different is that when the ownership chain is broken, the security context you’re in is the ProcedureOwner, not the context of the caller, aveSchlub. Using EXECUTE AS to change security context is a cool, powerful feature. Now, you can give users temporary rights that won’t even be apparent to them and won’t require granting any permissions.

However, EXECUTE AS isn’t a feature that should be overused, and its use should definitely be monitored during code reviews! It can be all too easy just to build your procedures in the context of the dbo and forget about decent security altogether. And that is the “nice” reason for taking care in using the feature. Another reason to take care is that a malicious programmer could (if they were devious or stupid) include dangerous code that would run as if it were the database owner, which could certainly cause undesired effects.

For example, using impersonation is a great way to implement dynamic SQL calls without having to worry about ownership chaining (I will discuss this more in Chapter 13 when I discuss code-level design, but generally these are calls that are formed as textual queries in your stored procedures, rather than being compiled), but if you aren’t careful to secure your code against an injection attack, the attack might just be in the context of the database owner rather than the basic application user that should have only limited rights if you have listened to anything I have said in the rest of this chapter.

One thing that you can do with this EXECUTE AS technique is to give a user super rights temporarily in a database. For example, consider the following procedure:

REVERT;
GO
CREATE PROCEDURE dbo.TestDboRights
AS
 BEGIN
    CREATE TABLE dbo.test
    (
        testId int
    );
 END;

This procedure isn’t executable by any users other than one who has db_owner level rights in the database (or rights to create a table), even if they have rights to execute the procedure. Say we have the following user and give him rights (presuming “Leroy” is a male name and not just some horrible naming humor that a female had to live with) to execute the procedure:

CREATE USER Leroy WITHOUT LOGIN;
GRANT EXECUTE on dbo.TestDboRights to Leroy;

Note that you grant only rights to the dbo.testDboRights procedure. The user Leroy can execute the one stored procedure in the database:

EXECUTE AS USER = ’Leroy’;
EXECUTE dbo.TestDboRights;

The result is as follows, because creating a table is a database permission that Leroy doesn’t have, either explicitly granted or as a member of a db_owner’s role:

Msg 262, Level 14, State 1, Procedure testDboRights, Line 5
CREATE TABLE permission denied in database ’ClassicSecurityExample’.

If you alter the procedure with EXECUTE AS ’dbo’, the result is that the table is created, if there isn’t already a table with that name that someone else has created (like if the dbo has executed this procedure previously):

REVERT;
GO
ALTER PROCEDURE dbo.TestDboRights
WITH EXECUTE AS ’dbo’
AS
 BEGIN
    CREATE TABLE dbo.test
    (
        testId int
    );
 END;

Now, you can execute this procedure and have it create the table. Run the procedure twice, and you will get an error about already having a table called dbo.test in the database. For more detailed information about EXECUTE AS, check the “EXECUTE AS” topic in Books Online.

In 2016, the Row-Level Security feature throws a bit of a spanner into how this all works. Usually, when you execute a coded object (or use a view), you get access to everything the owner of the object provided you. However, for Row-Level Security, when executing as the caller, the caller’s information is provided to the predicate function. When executing as a different user, that user’s information is used.

As an example, create the following table, with a few rows:

CREATE TABLE dbo.TestRowLevelChaining
(
        Value    int CONSTRAINT PKTestRowLevelChaining PRIMARY KEY
)
INSERT dbo.TestRowLevelChaining (Value)
VALUES  (1),(2),(3),(4),(5);

Then set up a filtering predicate that returns values > 3 if you are not the dbo, and apply it:

CREATE FUNCTION RowLevelSecurity.dbo_TestRowLevelChaining$SecurityPredicate
                                        (@Value AS int)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN (SELECT 1 AS dbo_TestRowLevelChaining$SecurityPredicate
            WHERE  @Value > 3 OR  USER_NAME() = ’dbo’);
GO
CREATE SECURITY POLICY RowLevelSecurity.dbo_TestRowLevelChaining_SecurityPolicy
    ADD FILTER PREDICATE RowLevelSecurity.dbo_TestRowLevelChaining$SecurityPredicate (Value)
    ON dbo.TestRowLevelChaining WITH (STATE = ON, SCHEMABINDING = ON);

Then set up two procedures, one that executes as caller, and the other that executes as the dbo user:

CREATE PROCEDURE dbo.TestRowLevelChaining_asCaller
AS
SELECT * FROM dbo.TestRowLevelChaining;
GO
CREATE PROCEDURE dbo.TestRowLevelChaining_asDbo
WITH EXECUTE AS  ’dbo’
AS
SELECT * FROM dbo.TestRowLevelChaining;

Create a new user, and grant it rights to execute both procedures:

CREATE USER Bobby WITHOUT LOGIN;
GRANT EXECUTE ON dbo.TestRowLevelChaining_asCaller TO Bobby;
GRANT EXECUTE ON dbo.TestRowLevelChaining_asDbo TO Bobby;

Now, executing the new user, you will see that the asCaller variant only returns the two rows that are greater than 3:

EXECUTE AS USER = ’Bobby’
GO
EXECUTE  dbo.TestRowLevelChaining_asCaller;
Value
-----------
4
5

But the asDbo variant is now executing as if the dbo user is the one executing it, even for the row-level security access:

EXECUTE  dbo.TestRowLevelChaining_asDbo;

This returns all of the data in the table:

Value
-----------
1
2
3
4
5

This is a very useful thing, first that the normal case allows you to largely ignore row-level security when writing your objects, and second that you can apply it to objects that already exist. It is essential to understand how row-level security applies to cases when you are using impersonation, so as to not expect to be giving access to an object and accidentally grant rights to a lot more data than you are expecting.

Views and Table-Valued Functions

Views and table-valued functions share security aspects with executable coded objects, but behave in most contexts like tables. Views, as discussed in previous chapters, allow you to form pseudotables from other table sources, sometimes by adding tables together and sometimes by splitting a table up into smaller chunks. You can use views to provide an encapsulation layer that looks like tables to the user (and using triggers like we discussed in Chapter 7, can almost always be programmed to behave like actual tables with enough work). Table-valued functions are very similar to views, but the data they return cannot be modified even when they are of the simple, single-statement variety.

In this section, I’ll briefly discuss a few ways for views and table-valued functions to encapsulate data in a manner that leaves the data in table-like structures. You might use views and table-valued functions in concert with, or in lieu of, a full stored procedure approach to application architecture. In this section, the goal is to “hide” data, like a column, from users or hide certain rows in a table, providing data security by keeping the data out of the view of the user in question.

For an overall architecture, I will always suggest that you should use stored procedures to access data in your applications, if for no other reason than you can encapsulate many data-oriented tasks in the background, giving you easy access to tweak the performance of an activity with practically zero impact to the application. (In Chapter 11, I will give some basic coverage of application architecture.)

Instead of the more up-front programming-heavy stored procedure methods from the previous section, simply accessing views and table-valued functions allows for more straightforward usage. Sometimes, because of the oft-repeated mantra of “just get it done,” the concept of stored procedures is an impossible sale. You will lose a bit of control, and certain concepts won’t be available to you (for example, you cannot use EXECUTE AS in a view definition), but it will be better in some cases when you don’t want to dole out access to the tables directly.

We’ll use two properties of views to build a more secure database. The first is to assign privileges to users such that they can use a view, though not the underlying tables. For example, let’s go back to the Products.Product table used earlier in this chapter. As a reminder, execute this statement (after executing REVERT, if you haven’t already, from the previous example):

SELECT *
FROM   Products.Product;

The following data is returned (if you have additional rows from testing, delete them now; a statement to do so is provided in the download):

ProductId   ProductCode Description          UnitPrice    ActualCost    ProductType
----------- ----------- -------------------- ------------ ------------- ------------------1                       widget12    widget number 12     10.5000      8.5000        widget
2           snurf98     Snurfulator          99.9900      2.5000        snurf

We could construct a view on this:

CREATE VIEW Products.AllProducts
AS
SELECT ProductId,ProductCode, Description, UnitPrice, ActualCost, ProductType
FROM   Products.Product;

Selecting data from either the table or the view returns the same data. However, they’re two separate structures to which you can separately assign access privileges, and you can deal with each separately. If you need to tweak the table, you might not have to modify the view. Of course, in practice, the view won’t usually include the same columns and rows as the base table, but as an example, it is interesting to realize that if you build the view in this manner, there would be little, if any, difference with using the two objects, other than how the security was set up. As the view is made up of one table, it is updateable as well.

One of the most important things that make views useful as a security mechanism is the ability to partition a table structure, by limiting the rows or columns visible to the user. First, you’ll look at using views to implement column-level security, which is also known as projection or vertical partitioning of the data, because you’ll be dividing the view’s columns. (Earlier we used horizontal partitioning for row-level security.) For example, consider that the users in a WarehouseUsers role need only to see a list of products, not how much they cost and certainly not how much they cost to produce. You might create a view like the following to partition the columns accordingly:

CREATE VIEW Products.WarehouseProducts
AS
SELECT ProductId,ProductCode, Description
FROM   Products.Product;

In the same manner, you can use table-valued functions in much the same way, though you can do more using them, including forcing some form of filter on the results. For example, you might code the following function to list all products that are less than some price:

CREATE FUNCTION Products.ProductsLessThanPrice
(
    @UnitPrice  decimal(10,4)
)
RETURNS table
AS
     RETURN ( SELECT ProductId, ProductCode, Description, UnitPrice
              FROM   Products.Product
              WHERE  UnitPrice <= @UnitPrice);

This can be executed like the following:

SELECT * FROM Products.ProductsLessThanPrice(20);

This returns the following result:

ProductId   ProductCode Description          UnitPrice
----------- ----------- -------------------- ---------------
1           widget12    widget number 12     10.5000

Now for each of these views and functions, you can simply GRANT SELECT rights to a user to use them, and almost all tools would be able to use them just like tables.

Crossing Database Lines

So far, most of the code and issues we’ve discussed have been concerned with everything owned by a single owner and everything has been within a single database. This will almost always be the desired pattern of development, but sometimes it is not possible to achieve. When our code and/or relationships must go outside the database limits, the complexity is greatly increased. This is because in SQL Server architecture, databases are generally designed to be thought of as independent containers of data, and this is becoming more and more the expectation with Azure SQL DB, contained databases, and even the developer tools that are shipped with SQL Server. (Azure SQL DB does allow some cross-database queries with its elastic database query features, which you can read about here: azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/.) Sometimes, however, you need to share data from one database to another, often for some object that’s located in a third-party system your company has purchased. This can be a real annoyance for the following reasons:

• Foreign key constraints cannot be used to handle referential integrity needs (I covered in Chapter 7 how you implement relationships using triggers to support this).
• Backups must be coordinated or your data could be out of sync with a restore. You lose some of the protection from a single database-backup scenario. This is because when, heaven forbid, a database restore is needed, it isn’t possible to make certain that the data in the two databases is in sync.

A typical example of cross-database access might be linking an off-the-shelf system into a homegrown system. The off-the shelf package may have requirements that you not make any changes or additions to its database schema. So you create a database to bolt on functionality. A second scenario could be a hosted server with databases from many clients, but clients need access to more than one database without gaining access to any other client’s data.

Beyond the coding and maintenance aspects of cross-database access, which aren’t necessarily trivial, the most complex consideration is security. As mentioned in the first paragraph of this section, databases are generally considered independent in the security theme of how SQL Server works. This causes issues when you need to include data outside the database, because users are scoped to a database. UserA in database1 is not exactly the same as UserA in database2, even in an uncontained database mapped to the same login.

The ownership chain inside the boundaries of a database is relatively simple. If the owner of the object refers only to other objects owned by that user, then the chain isn’t broken. Any user to whom the object’s owner grants rights can use the object. However, when leaving the confines of a single database, things get murky. Even if a database is owned by the same system login, the ownership chain is (by default) broken when an object references data outside the database owned by the same login. So, not only does the object creator need to have access to the objects outside the database, the caller needs rights also.

This section demonstrates four different concepts for the on-premises version of SQL Server when dealing with accessing data outside of a single database.:

• Using cross-database chaining
• Using impersonation to implement cross-database connections
• Using a certificate-based trust
• Accessing data outside of the server

Ideally, you will seldom, if ever, need to access data that is not within the boundaries of a single database, but when you do, you will want to choose the method that is the most secure for your needs.

Using Cross-Database Chaining

The cross-database chaining solution is to tell the database to recognize that if the owners of database1 and database2 are the same, it should not let ownership chaining be broken when crossing database boundaries. Then, if you, as system administrator, want to allow users to use your objects seamlessly across databases, that’s fine. However, a few steps and requirements need to be met:

• Each database that participates in the chaining relationship must be owned by the same system login.
• The DB_CHAINING database option (set using ALTER DATABASE) must be set to ON for each database involved in the relationship. It’s OFF by default.
• The database where the object uses external resources must have the TRUSTWORTHY database option set to ON; it’s OFF by default. (Again, set this using ALTER DATABASE.)
• The users who use the objects need to have a user in the database where the external resources reside.

I often will use the database chaining approach to support a reporting solution for an internal server. For example, we have several databases that make up a complete solution in our production system. We have a single database with views of each system to provide a single database for reporting from the OLTP databases (for real-time reporting needs only; other reporting comes from an integrated copy of the database and a data warehouse, as will be described in more detail in Chapter 14).

Note that if you need to turn chaining on or off for all databases, you can use sp_configure to set ’Cross DB Ownership Chaining’ to ’1’, but this is not considered a best practice. Use ALTER DATABASE to set chaining only where absolutely required.

As an example, the following scenario creates two databases with a table in each database and then a procedure. First, create the new database and add a simple table. You don’t need to add any rows or keys, because this isn’t important to this demonstration. Note that you have to create a login for this demonstration, because the user must be based on the same login in both databases. You will start with this database in an uncontained model and then switch to a contained model to see how it affects the cross-database access:

CREATE DATABASE ExternalDb;
GO
USE ExternalDb;
GO
                                       --smurf theme song :)
CREATE LOGIN PapaSmurf WITH PASSWORD = ’La la, la la la la, la, la la la la’;
CREATE USER  PapaSmurf FROM LOGIN PapaSmurf;
CREATE TABLE dbo.Table1 ( Value int );

Next, create a local database, the one where you’ll be executing your queries. You add the login you created as a new user and again create a table:

CREATE DATABASE LocalDb;
GO
USE LocalDb;
GO
CREATE USER PapaSmurf FROM LOGIN PapaSmurf;

Another step that’s generally preferred is to have all databases owned by the same server_principal, usually the sysadmin’s account. I will use the sa account here. Having the databases owned by sa prevents issues if the Windows Authentication account is deleted or disabled. If you are using these techniques on a shared server, using sa would not be the preferred method, and you could use different database owners for different users. Do this with the ALTER AUTHORIZATION DDL statement:

ALTER AUTHORIZATION ON DATABASE::ExternalDb TO sa;
ALTER AUTHORIZATION ON DATABASE::LocalDb TO sa;

To check the owner of the database, use the sys.databases catalog view:

SELECT name,SUSER_SNAME (owner_sid) AS owner
FROM   sys.databases
WHERE  name IN (’ExternalDb’,’LocalDb’);

This should return the following, as it is essential that the databases are owned by the same server principal for the upcoming examples:

name              owner
----------------- ------------------
ExternalDb        sa
LocalDb           sa

Next, create a simple procedure, still in the localDb context, selecting data from the external database, with the objects being owned by the same dbo owner. You then give rights to your new user:

CREATE PROCEDURE dbo.ExternalDb$TestCrossDatabase
AS
SELECT Value
FROM   ExternalDb.dbo.Table1;
GO
GRANT EXECUTE ON dbo.ExternalDb$TestCrossDatabase TO PapaSmurf;

Now, try it as the sysadmin user:

EXECUTE dbo.ExternalDb$TestCrossDatabase;

And it works fine, because the sysadmin user is basically implemented to ignore all security. Execute as the user PapaSmurf that is in the localDb:

EXECUTE AS USER = ’PapaSmurf’;
GO
EXECUTE dbo.ExternalDb$TestCrossDatabase;
GO
REVERT;

This will give you the following error:

Msg 916, Level 14, State 1, Procedure externalDb$testCrossDatabase, Line 3
The server principal "PapaSmurf" is not able to access the database "ExternalDb" under the current security context.

You then set the chaining and trustworthy attributes for the localDb and chaining for the externalDb (making these settings requires sysadmin rights):

ALTER DATABASE localDb
   SET DB_CHAINING ON;
ALTER DATABASE localDb
   SET TRUSTWORTHY ON;
ALTER DATABASE externalDb --It does not need to be trustworthy since it is not reaching out
   SET DB_CHAINING ON;

Now, if you execute the procedure, you will see that it returns a valid result. This is because

• The owner of the objects and databases is the same, which you set up with the ALTER AUTHORIZATION statements.
• The user has access to connect to the external database, which is why you created the user when you set up the externalDb database. (You can also use the guest user to allow any user to access the database as well, though as mentioned, this is not a best practice.)

You can validate the metadata for these databases by using the sys.databases catalog view:

SELECT name, is_trustworthy_on, is_db_chaining_on
FROM   sys.databases
WHERE  name IN (’ExternalDb’,’LocalDb’);

This returns the following results:

name         is_trustworthy_on is_db_chaining_on
------------ ----------------- -----------------
externalDb   0                 1
localDb      1                 1

I find that the biggest issue when setting up cross-database chaining is the question of ownership of the databases involved. The owner changes sometimes because users create databases and leave them owned by their security principals. Note that this is the only method I will demonstrate that doesn’t require stored procedures to work. You can also use basic queries and views using this method, as they are simply stored queries that you use as the basis of a SELECT statement. Stored procedures are executable code modules that allow them a few additional properties, which I will demonstrate in the next two sections.

Now check how this will be affected by setting the database to use the containment model:

ALTER DATABASE LocalDB  SET CONTAINMENT = PARTIAL;