Guidelines for Server Security

Even as strictly a database architect/programmer, you may need to set up, or at least validate, the security of your SQL Server installation. The following bulleted 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, but it is a good start nonetheless:

• Strong passwords are applied to all accounts, both Windows Authentication and SQL Server authentication style (server and contained database style, new to SQL Server 2012)—certainly, all universally known system accounts have very strong passwords (such as sa, if you haven’t changed the name). Certainly, there are no blank passwords for any accounts!
• SQL Server isn’t sitting unguarded on the Web, with no firewall and no logging of failed login attempts.
• The guest user has been removed from all databases where it isn’t necessary.
• Care has been taken to guard against SQL injection by avoiding query strings whereby a user could simply inject SELECT name FROM sys.sql_logins and get a list of all your logins in a text box in your application that should display something like toothpaste brands. (Chapter 13 mentions SQL injection again; there I contrast ad hoc SQL with stored procedures.)
• Application passwords are secured/encrypted and put where they can be seen by only necessary people (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.
• You’ve made certain that 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 backup file (or tape if you are still living in the past), 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 percent secure if the hacker has virtually unlimited time).
• You have taken all necessary precautions to make sure that the physical computer where the data is stored cannot be taken away as a whole. Even things like encryption aren’t completely effective if the data needed to decrypt the values is available on one of the machines that has been stolen along with the machine with the encrypted values.
• Your SQL Server installation 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 have access to your hard disks (note that using transparent data encryption (TDE) can help in this case).
• All features that you are not using are turned off. To make SQL Server as secure as possible out of the box, many features are disabled by default and have to be enabled explicitly before you can use them. For example, remote administrator connections, Database Mail, CLR programming, and others are all off by default. You can enable these features and others using the sp_configure stored procedure.

Principals and Securables

At the very core of security in SQL Server are the concepts of principals and securables. Principals are those objects that may be granted permission to access particular database objects, while securables are those objects 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 principals: These are server-level logins or groups that are authenticated using SQL Server security.
• Database principals: These include database users, groups, and roles, as well as application roles.

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

• Server scope: Server-scoped securables include logins, HTTP endpoints, event notifications, 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 scope : Securables with database scope are objects such as schemas, users, roles, and CLR assemblies, DDL triggers, and so on, which exist inside a particular database but not within a schema.
• Schema scope : This group includes those objects that reside within a schema in a database, such as tables, views, and stored procedures. A SQL Server 2005 and later schema corresponds roughly to the owner of a set of objects (such as dbo) in SQL Server 2000.

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

• GRANT : Gives the privilege to use an object.
• DENY : Denies access to an object, regardless of whether the user has been granted the privilege from any other role.
• REVOKE : Used to remove any GRANT or DENY permissions statements that have been applied to an object. This behaves like a delete of an applied permission, one either granted or denied.

Typically, you’ll simply give permissions to a role to perform tasks that are specific to the role. DENY is then used only in “extreme” cases, because no matter how many other times the user has been granted privileges to an object, the user won’t have access to it while there’s one DENY.

For a database or server right, you will use syntax like

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

The 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, 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, you have two choices. You can either REVOKE the permission, which just deletes the granted permission, or you can DENY the permission. Execute the following:

REVOKE <privilege> FROM <securable> to <principal>;

I haven’t covered role membership yet (it’s covered later in this chapter), but if the user were a member of a role that had access to this object, the user would still have access. However, execute the following code:

DENY <privilege> ON <securable> to <principal>;

The use of DENY will prohibit the principal from using the securable, even if they have also been granted access by means of another securable. To remove DENY, you again use the REVOKE command. This will all become clearer when I cover roles later in this chapter, but in my experience, DENY isn’t a typical thing to use on a principal’s privilege set. It’s punitive in nature and is confusing to the average user. More commonly, users are given rights and not denied access.

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 (from Books Online, these are 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>;

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 schema GRANT, the syntax is

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

Note that, for objects, you can also use the a prefix of OBJECT::, as in the following:

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

Connecting to the Server

Before we finally get the 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 norm and is basically as follows: A login principal is defined that allows a principal to access the server using Windows credentials, a login that is managed in the SQL Server instance (known as 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 a new concept of a contained database (CDB). I will cover the broader picture and a bit of the management of CDB 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 as it is, from a coding standpoint, largely a security question. Contained databases in SQL Server 2012 are the initial start of making databases essentially standalone containers that can be moved from server to server with little effort (and likely eventually to Azure as well).

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

• Using the classic approach of a login and user
• Access the database directly using the containment model

Using Login and 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 [DENALI-PCAlienDrsql] FROM WINDOWS

  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 named DENALI-PC, I have a user named AlienDrsql (I have an Alienware PC, hence the name; I am not a weirdo; I promise.) 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.

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 Windows 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. 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 for them to tape the password up on 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 very 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 data 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 group, 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 [DENALI-PCAlienDrsql];

You can give users rights to do certain actions using server permissions. For example, you might give the FRED user rights to VIEW SERVER STATE (which lets you run Dynamic Management Views, for example) using:

GRANT VIEW SERVER STATE to [Fred];

And new in SQL Server 2012, you can create user-defined server roles. For example, if you want to give support people rights to VIEW SERVER STATE and VIEW ANY DATABASE (letting them see the structure of all databases) rights, you could create a server role:

CREATE SERVER ROLE SupportViewServer;

Grant the role the rights desired:

GRANT VIEW SERVER STATE to SupportViewServer;

GRANT VIEW ANY DATABASE to SupportViewServer;

And add the login to the server role:

ALTER SERVER ROLE SupportViewServer ADD MEMBER Fred;

Once we have our login created, the next step is to set up access to a database (unless you used sysadmin, in which case you have unfettered access to everything on the server). For example, let’s create a simple database. For the remainder of this chapter, I will expect that you are a 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 it will be set up in the text as to what we are doing. So we create database ClassicSecurityExample:

CREATE DATABASE ClassicSecurityExample;

Next, we will create another login, using SQL Server Authentication. Most logins we will create in the book will be SQL Server Authentication to make it easier to test the user. 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;

Login using the user in Management Studio into a query window:

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

USE ClassicSecurityExample;

You will receive the following error:

Your database context will remain in tempdb, since this is the default database we set up for the user. Going back to the window where you are in the sysadmin user context, we 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:

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, let’s 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 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, we will create a user in the database 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 see that you can connect to the database, and using a few system functions, you can 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:

Executing this in your system administrator connection, you will see:

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 back with SQL Server 2008, the advice would have been strongly against using any sort of virtualization technology with SQL Server, and now, even at the nonprofit I work for, we have nearly everything running on virtualized Windows hardware. 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.

And since SQL 2008 was presented, another paradigm shift has begun with what is referred to as the cloud, where instead of housing your own hardware and servers, you put your database on a massive server such that, on average, their hardware is used quite constantly but the users don’t exactly feel it. I haven’t made too big of a deal about the cloud version of SQL Server (SQL Server Azure) in this book, largely because it is just (simplifying quite a bit, naturally), a relational database that you use over the WAN instead of the LAN. I expect that, although I will just touch a bit on the subject for this book, in the next edition of this book, the cloud will be far more prevalent.

To that end, Microsoft has added the beginnings of what is going to be a very important step in making databases easy to move from local to cloud with ease called contained databases. Where applicable, I will note some of the places where contained database security is different that the classic model, which is mostly concerning accessing external objects.

Our first step is to create a new database that we will set CONTAINMENT = PARTIAL. For SQL Server 2012, there are two models: OFF, which I am referring to as 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). Later versions of SQL Server will 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 we discussed in previous chapters, this means a security point that is going to be turned off by default. Hence, the first thing we 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 do not set the containment properties in the CREATE DATABASE statement, so you will create a database just like any other database:

CREATE DATABASE ContainedDBSecurityExample;

The next step is to set the containment model using an ALTER DATABASE statement, which you will do in the context of the database:

USE ContainedDBSecurityExample;

GO

-- set the contained database to be partial

ALTER DATABASE ContainedDBSecurityExample SET CONTAINMENT = PARTIAL;

Next, we will create a user, in this case referred to as a contained user. Contained users are basically a hybrid of login and user, and they are 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 11 variations of the CREATE USER syntax, so you should check it out if you need a different sort of user!

The first case we 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 we set earlier), or you will get an error telling you that you can only create a user with a password in a contained database.

CREATE USER WilmaContainedUser WITH PASSWORD = 'p@ssword1';

You can also create a Windows Authentication user in the following manner 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 [DENALI-PCAlienDrsql];

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

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, for demonstration purposes, we will be using SQL Server Authentication to make the process easier.

Next, we will connect to the database in SSMS using the contained user we previously created named WilmaContainedUser with password p@ssword1. To do this, you will specify the server name, choose SQL Server Authentication, and set the username and password:

Next, click the Options button. Go to the connection properties tab, and enter the name of the contained database as seen 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 previous section on the classic security model.

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;

You will get another excellent error message that (unless you have seen it before) will undoubtedly cause you to scratch your head:

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    sys.database_principals

WHERE   authentication_type_desc = 'DATABASE';

In our database, this returns

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. Impersonation is, in fact, one of the most important tools you will need when you are testing your security configuration. After some code has been migrated to production, it is common to get a call from clients who claims 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.

To demonstrate security in a reasonable manner on a single SQL Server connection, I will use a feature that was new to SQL Server 2005. In 2000 and earlier, if the person with administrator rights wanted to impersonate another user, he or she used SETUSER (I still see people use SETUSER, so I feel I still need to mention it here). Using SETUSER, you can impersonate any server or a database principal, and you get all rights that user has (and consequently lose the rights you previously had). You can go back to the previous security context by executing REVERT. The only downside is that when you try to impersonate a Windows Authentication–based principal, you cannot do this disconnected from the domain where the principal was created.

As an example, 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.

As an example (and this is the kind of example that I’ll have throughout this chapter), we first create a login that we never expect to be logged into directly. I use a standard 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. Then, we 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 system_admin WITH PASSWORD = 'tooHardToEnterAndNoOneKnowsIt',CHECK_POLICY=OFF;

EXEC sp_addsrvrolemember 'system_admin','sysadmin';

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

CREATE LOGIN louis with PASSWORD = 'reasonable', DEFAULT_DATABASE=tempdb,CHECK_POLICY=OFF;

--Must execute in master Database

GRANT IMPERSONATE ON LOGIN::system_admin TO louis;

We log in as louis 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:

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

EXECUTE AS LOGIN = 'system_admin';

We now have control of the server in that window as the system_admin user! To look at the security context, you 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:

The columns mean

• 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, you execute the following code:

REVERT --go back to previous security context

We see the following result:

You started in tempdb, so you 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:

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. I’ll use impersonation to change security context to demonstrate security concepts.

Using impersonation, you can execute your code as a member of the sysadmin server 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.

Grantable Permissions

Using SQL Server security, you can easily build a security plan that prevents unwanted usage of your objects by any user. 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).

Most of these are straightforward and probably are familiar if you’ve done any SQL Server administration, although perhaps REFERENCES isn’t familiar. 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 one via a foreign key. This is for the situation in which tables are owned by schemas that are then owned by different database principals. If you wanted to apply a foreign key between the tables, you’d be required to give the child table REFERENCES permissions.

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

• GRANT: Gives the privilege to use an object
• DENY: Denies access to an object, regardless of whether the user has been granted the privilege from any other role
• REVOKE: Used to remove any GRANT or DENY permissions statements that have been applied to an object (This behaves like a delete of an applied permission.)

To see the user’s rights in the 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.

Controlling Access to Objects

One of the worst things that can happen in an organization is for a user to see data out of context and start a worry- or gossip-fest. Creating roles and associating users with them is a fairly easy task and is usually worth the effort. Once you’ve set up the security for a database using sufficient object groupings (as specific as need be, of course), management can be relatively straightforward.

Your goal is to allow the users to perform whatever tasks they need to but to prohibit any other tasks and not to let them see any data that they shouldn’t. You can control access at several levels. At a high level, you might want to grant (or deny) a principal access to all invoices—in which case, you might control access at the level of a table. At a more granular level, you might want to control access to certain columns or rows within that table. In a more functional approach, you might give rights only to use stored procedures to access data. All these approaches are commonly used in the same database in some way, shape, or form.

In this section, I’ll cover the built-in security for tables and columns as it’s done using the built-in vanilla security, before moving on to the more complex strategies using coded objects like views and stored procedures.

Roles

Core to the process of granting rights is who to grant rights to. I’ve introduced the database user, commonly referred to as just user. 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 a couple 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 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.

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 one of they uses. 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 and (seldomly) individual columns
• 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 AdventureWorks2012 database, there’s a HumanResources schema. Use the following query of the sys.objects catalog view (which reflects schema-scoped objects):

USE AdventureWorks2012;

GO

SELECT type_desc, count(*)

FROM sys.objects

WHERE schema_name(schema_id) = 'HumanResources'

 AND 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 type_desc;

GO

USE ClassicSecurityExample;

This query shows how many of each object can be found in the version of the HumanResources schema I have on my laptop. As mentioned 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 has access to the 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.

Stored Procedures and Scalar Functions

Security in stored procedures and functions is always at the object level. 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 it’s 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 “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 on 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, we will to limit our discussion to the security aspects.)

Take, for example, the case where a screen 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 screen 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, 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

(

    Value varchar(20),

    Value2 varchar(20)

);

GO

INSERT  INTO procTest.misc

VALUES  ('somevalue','secret'),

        ('anothervalue','secret'),

Next, we will create a stored procedure to return the values from the value column in the table, not the value2 column; then, we grant rights to the procUser to execute the procedure:

CREATE PROCEDURE procTest.misc$select

AS

    SELECT Value

    FROM procTest.misc;

GO

GRANT EXECUTE on procTest.misc$select to procUser;

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

EXECUTE AS USER = 'procUser';

GO

SELECT Value, Value2

FROM   procTest.misc;

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

However, execute the following procedure:

EXECUTE procTest.misc$select;

The user does have access to execute the procedure, so you get the results expected:

This is the best way to architect a database solution. 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.

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 a procedure or function 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 code, you can change the security context of a procedure to a different server or database principal when the execution begins:

CREATE PROCEDURE <schemaName>.<procedureName>

WITH EXECUTE AS <'loginName' | caller | self | owner>;

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

• 'userName': A specific user 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.
• 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 ownership chaining come into play. The following statements go along with the EXECUTE AS clause:

• 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 how to build a situation where one schema owner has a table and where the next schema owner has a table and a procedure that the schema owner wants to use to access the first user’s table. Finally, you have an average user who wants to execute the stored procedure.

First, you 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 primary schema where one of the objects resides.
• procedureOwner: This user owns the owner of an object 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, you 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 constraint PKtestAccess_Person primary key,

        FirstName varchar(20),

        LastName varchar(20)

);

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, you 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. Here I step back to the db_owner user you have

       --used throughout the chapter

GO

EXECUTE AS USER = 'procedureOwner';

Then, you 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 constraint PKtestAccess_person primary key,

        FirstName varchar(20),

        LastName varchar(20)

);

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:

Next, you create two procedures as the procedureOwner user, one for the WITH EXECUTE AS 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, you grant rights on the proc to the aveSchlub user:

GRANT EXECUTE ON procedureOwnerSchema.person$asCaller TO aveSchlub;

GRANT EXECUTE ON procedureOwnerSchema.person$asSelf TO aveSchlub;

Then, you change to the context of the aveSchlub:

REVERT; EXECUTE AS USER = 'aveSchlub';

Finally, you 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 schemaOwnerSchema:

Next, you execute the asSelf variant:

--procedureOwner, so it works

EXECUTE procedureOwnerSchema.person$asSelf;

This returns two result sets:

What makes this different is that when the ownership chain is broken, the security context you’re in is the secondaryUser, not the context of the caller, aveSchlub. Using EXECUTE AS to change security context is a cool 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 by 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 (I will discuss more in Chapter 13 when I discuss code-level design), 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, 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 role db_owner’s role:

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 Chapter 13, a rather large section discusses the value of ad hoc SQL versus stored procedures, and we use the EXECUTE AS functionality to provide security when it comes to executing dynamic SQL in stored procedures. This is a great new feature that will bring stored procedure development—including stored procedures that are CLR-based—to new heights, because dynamic SQL-based stored procedures were a security issue in earlier versions of SQL Server.

Views and Table-Valued Functions

In this section, I’ll talk about using views and table-valued functions to encapsulate the views of the data in ways that leave 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. 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. 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 13, I will give some basic coverage of application architecture.)

In this section, I’ll look at the following:

• General usage: I’ll cover basic use of views to implement security.
• Configurable row-level security: You can use views to implement security at the row level, and this can be extended to provide user-manageable security.

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.

Using Cross-Database Chaining

The cross-database chaining solution is to tell the database to recognize that indeed the owners of database1 and database2 are the same. Then, if you, as system administrator, want to allow users to use your objects seamlessly across databases, then it’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 example, we have several databases that make up a complete reporting 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). Users have access to the views in the reporting database, but not rights to the base database tables.

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, consider the following scenario where I’ll create two databases with a table in each database and then a procedure. First, I’ll create the new database and add a simple table. I won’t 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. We will start with this database in an uncontained model and will 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 smurf WITH PASSWORD = 'La la, la la la la, la, la la la la';

CREATE USER smurf FROM LOGIN smurf;

CREATE TABLE dbo.table1 ( value int );

Next, you 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 smurf FROM LOGIN smurf;

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. We do this with the ALTER AUTHROIZATION 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 (if not, you will want to make sure of what you may have missed, as it is essential that the databases are owned by the same system principal for the upcoming examples):

Next, you 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 our new user:

CREATE PROCEDURE dbo.externalDb$testCrossDatabase

AS

SELECT value

FROM   externalDb.dbo.table1;

GO

GRANT EXECUTE ON dbo.externalDb$testCrossDatabase TO smurf;

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 smurf that is in the localDb:

EXECUTE AS USER = 'smurf';

GO

EXECUTE dbo.externalDb$testCrossDatabase;

GO

REVERT;

This will give you the following error:

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

  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 we set up with the ALTER AUTHORIZATION statements.
• The user has access to connect to the external database, which was why we created the user when we set up the externalDb database. (You can also use the guest login 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 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:

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 in this very same manner, 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 let’s see how this will be affected by setting the database to use the containment model:

ALTER DATABASE localDB SET CONTAINMENT = PARTIAL;

Then, connect to the server as user: smurf using SSMS and default database to localDb and try to run the procedure. You will notice that the connection isn’t to the contained database it is to the server and you are in the context of the contained database. Using EXECUTE AS will give you the same effect:

EXECUTE AS USER = 'smurf';

go

EXECUTE dbo.externalDb$testCrossDatabase;

GO

REVERT;

GO

You will see that it behaves the exact same way and gives you a result to the query. However, connect with a contained user is a different challenge. First, let’s create a contained user, and then give it rights to execute the procedure.

CREATE USER Gargy WITH PASSWORD = 'Nasty1$';

GO

GRANT EXECUTE ON dbo.externalDb$testCrossDatabase to Gargy;

Next, change to the database security context of the new contained user and try to change context to the externalDb:

EXECUTE AS USER = 'Gargy';

GO

USE externalDb;

This will give the following error:

Obviously the “server principal” part of the error message could be confusing, but it is also true because in this case, the database will behave as a server to that user. Executing the following code will give you the exact same error:

EXECUTE dbo.externalDb$testCrossDatabase;

GO

REVERT;

GO

When turning on containment, you will note that since the maximum containment level is PARTIAL, some code you have written may not be containment safe. To check, you can use the sys.dm_db_uncontained_entities dynamic management view. Even if you aren't using containment, you can use the following containment DMVs query to see code referencing outside data:

SELECT OBJECT_NAME(major_id) AS object_name,statement_line_number,

       statement_type, feature_name, feature_type_name

FROM   sys.dm_db_uncontained_entities AS e

WHERE  class_desc = 'OBJECT_OR_COLUMN';

For our database, it will return the following, which corresponds to the procedure we created and the query that used a cross-database reference:

The object will also return uncontained users:

SELECT USER_NAME(major_id) AS USER_NAME,*

FROM   sys.dm_db_uncontained_entities AS e

WHERE  class_desc = 'DATABASE_PRINCIPAL'

  and  USER_NAME(major_id) <> 'dbo';

And we created one already in this chapter:

Finally, we need to do a bit of housekeeping to remove containment from the database. To do this, we will delete the contained user we created and turn off containment (we have to drop the user, or as previously mentioned, you would receive an error stating that uncontained databases cannot have contained users):

DROP USER Gargy;

GO

USE Master;

GO

ALTER DATABASE localDB SET CONTAINMENT = NONE;

GO

USE LocalDb;

GO

Using Impersonation to Cross Database Lines

Impersonation can be an alternative to using the DB_CHAINING setting. Now, you no longer need to set the chaining to ON; all you need is to set it to TRUSTWORTHY:

ALTER DATABASE localDb

  SET DB_CHAINING OFF;

ALTER DATABASE localDb

  SET TRUSTWORTHY ON;

ALTER DATABASE externalDb

  SET DB_CHAINING OFF;

Now, you can rewrite the procedure like this, which lets the person execute in the context of the owner of the schema that the procedure is in:

CREATE PROCEDURE dbo.externalDb$testCrossDatabase_Impersonation

WITH EXECUTE AS SELF --as procedure creator, who is the same as the db owner

AS

SELECT  value

FROM    externalDb.dbo.table1;

GO

GRANT   execute on dbo.externalDb$testCrossDatabase_impersonation to smurf;

If the login of the owner of the dbo schema (in this sa, because I set the owner of both databases to sa) has access to the other database, you can impersonate dbo in this manner. In fact, you can access the external resources seamlessly. This is probably the simplest method of handling cross-database chaining for most corporate needs. Of course, impersonation should be used very carefully and raise a humongous flag if you’re working on a database server that’s shared among many different companies.

Now, when I execute the procedure as smurf user, it works:

EXECUTE AS USER = 'smurf';

GO

EXECUTE dbo.externalDb$testCrossDatabase_impersonation;

GO

REVERT;

If you toggle off TRUSTWORTHY and try to execute the procedure

ALTER DATABASE localDb SET TRUSTWORTHY OFF;

GO

EXECUTE dbo.externalDb$testCrossDatabase_impersonation;

no matter what user you execute as, you’ll receive the following error:

This is clearly another of the confusing sorts of error messages you get on occasion, since the server principal sa ought to be able to do anything, but it is what it is. Next, we go back to the containment method. Turn back on TRUSTWORTHY, set the containment, and re-create the Gargy user, giving rights to the impersonation procedure:

ALTER DATABASE localDb SET TRUSTWORTHY ON;

GO

ALTER DATABASE localDB SET CONTAINMENT = PARTIAL;

GO

CREATE USER Gargy WITH PASSWORD = 'Nasty1$';

GO

GRANT EXECUTE ON externalDb$testCrossDatabase_Impersonation to Gargy;

Now execute the procedure in the context of the contained user:

EXECUTE AS USER = 'Gargy';

GO

EXECUTE dbo.externalDb$testCrossDatabase_Impersonation;

GO

REVERT;

This time, you will see that there no error is raised, because the procedure is in the context of the owner of the procedure and is mapped to a server principal that is the same that owns the database and the object you are using. Note that this breaks (or really, violates) containment because you are using external data, but it will give you the rights you need in the (hopefully) rare requirement to use cross-database access.

Finally, we clean up the users and containment as we have done before:

DROP USER Gargy;

GO

USE Master;

GO

ALTER DATABASE localDB SET CONTAINMENT = NONE;

GO

USE LocalDb;

Using a Certificate-Based Trust

The final thing I’ll demonstrate around cross-database access is using a single certificate installed in both databases to let the code access data across database boundaries. We’ll use it to sign the stored procedure and map a user to this certificate in the target database. This is a straightforward technique and is the best way to do cross-database security chaining when the system isn’t a dedicated corporate resource. It takes a bit of setup, but it isn’t overwhelmingly difficult. What makes using a certificate nice is that you don’t need to open the hole left in the system’s security by setting the database to TRUSTWORTHY. This is because the user who will be executing the procedure is a user in the database, just as if the target login or user were given rights in the externalDB. Because the certificate matches, SQL Server knows that this cross-database access is acceptable.

First, turn off the TRUSTWORTHY setting:

REVERT;

GO

USE localDb;

GO

ALTER DATABASE localDb

    SET TRUSTWORTHY OFF;

Check the status of your databases as follows:

SELECT name,

      suser_sname(owner_sid) as owner,

      is_trustworthy_on, is_db_chaining_on

FROM  sys.databases where name in ('localdb','externaldb'),

This should return the following results (if not, go back and turn off TRUSTWORTHY and chaining for the databases where necessary):

Now, we will create another procedure and give the user smurf rights to execute it, just like the others (which won’t work now because TRUSTWORTHY is turned off):

CREATE  PROCEDURE dbo.externalDb$testCrossDatabase_Certificate

AS

SELECT  Value

FROM    externalDb.dbo.table1;

GO

GRANT  EXECUTE on dbo.externalDb$testCrossDatabase_Certificate to smurf;

Then, create a certificate:

CREATE CERTIFICATE procedureExecution ENCRYPTION BY PASSWORD = 'jsaflajOIo9jcCMd;SdpSljc'

  WITH SUBJECT =

    'Used to sign procedure:externalDb$testCrossDatabase_Certificate';

Add this certificate as a signature on the procedure:

ADD SIGNATURE TO dbo.externalDb$testCrossDatabase_Certificate

  BY CERTIFICATE procedureExecution WITH PASSWORD = 'jsaflajOIo9jcCMd;SdpSljc';

Finally, make an OS file out of the certificate, so a certificate object can be created in the externalDb based on the same certificate (choose a directory that works best for you):

BACKUP CERTIFICATE procedureExecution TO FILE = 'c: empprocedureExecution.cer';

This completes the setup of the localDb. Next, you have to apply the certificate to the externalDb:

USE externalDb;

GO

CREATE CERTIFICATE procedureExecution FROM FILE = 'c: empprocedureExecution.cer';

After that, map the certificate to a user, and give this user rights to the table1 that the user in the other database is trying to access:

 CREATE  USER procCertificate FOR CERTIFICATE procedureExecution;

 GO

 GRANT  SELECT on dbo.table1 TO procCertificate;

Now, you’re good to go. Change back to the localDb and execute the procedure:

 USE localDb;

 GO

 EXECUTE AS LOGIN = 'smurf';

 EXECUTE dbo.externalDb$testCrossDatabase_Certificate;

The stored procedure has a signature that identifies it with the certificate, and in the external database, it connects with this certificate to get the rights of the certificate-based user. So, since the certificate user can view data in the table, your procedure can use the data.

The certificate-based approach isn’t as simple as the other possibilities, but it’s more secure, for certain. Pretty much the major downside to this is that it works only with procedures and not with views, and I didn’t really cover any of the intricacies of using certificates. However, now you have a safe way of crossing database boundaries that doesn’t require giving the user direct object access and doesn’t open up a hole in your security. Hence, you could use this solution on any server in any situation. Make sure to secure or destroy the certificate file once you’ve used it, so no other user can use it to gain access to your system. Then you clean up the databases used for the example.

Of the methods shown, this would be the least desirable to use with containment, because you now have even more baggage to set up after moving the database, so we will simply leave it as “it could be done, but shouldn’t be.”

Finally, we clean up the databases used for the examples and move back to the ClassicSecurityExample database we have used throughout this chapter:

REVERT;

GO

USE MASTER;

GO

DROP DATABASE externalDb;

DROP DATABASE localDb;

GO

USE ClassicSecurityExample;

Different Server (Distributed Queries)

I want to make brief mention of distributed queries and introduce the functions that can be used to establish a relationship between two SQL Server instances, or a SQL Server instance and an OLE DB or ODBC data source. (Note that OLEDB is being deprecated and will not be supported for long. For more details check http://msdn.microsoft.com/en-us/library/ms810810.aspx , which outlines Microsoft’s “Data Access Technologies Road Map.”)

You can use either of these two methods:

• Linked servers: You can build a connection between two servers by registering a “server” name that you then access via a four-part name (<linkedServerName>.<database>.<schema>.<objectName>) or through the OPENQUERY interface. The linked server name is the name you specify using sp_addlinkedserver. This could be a SQL Server server or anything that can be connected to via OLE DB.
• Ad hoc connections: Using the OPENROWSET or OPENDATASOURCE interfaces, you can return a table of data from any OLE DB source.

In either case, the security chain will be broken when crossing SQL Server instance connections and certainly when using any data source that isn’t SQL Server–based. Whether or not this is a problem is based on the connection properties and/or connection string used to create the linked server or ad hoc connection. Using linked servers, you could be in the context of the Windows login you are logged in with, a SQL Server standard login on the target machine, or even a single login that everyone uses to “cross over” to the other server. The best practice is to use the Windows login where possible.

As I mentioned briefly in the previous section, one use for EXECUTE AS could be to deal with the case where you’re working with distributed databases. One user might be delegated to have rights to access the distributed server, and then, you execute the procedure as this user to give access to the linked server objects.

Using linked servers or ad-hoc connections will both break the containment model. Linked servers are defined in the master database.

Server and Database Audit

For the 2008 release, Microsoft added a very nice feature for auditing the activities of your users. It is called SQL Server Audit , and it allows you to define server- and database-level audits that you can use to monitor almost everything your logins and users do to your server and/or databases. For SQL Server 2008, it was a feature that is included only in the Enterprise Edition. For SQL Server 2012, database-level audits are still Enterprise only, but server-level audits are available in all versions to monitor server configuration changes.

In versions before SQL Server 2008, most monitoring would have required a web of DML and DDL triggers, plus the use of Profiler to watch everyone’s moves. Now, you can implement detailed monitoring in a declarative manner. SQL Server Audit is a tremendously cool feature and will make the process of meeting auditing requirements much easier than ever before. Instead of lots of code to pore through, you can just print the audits that you are enforcing, and you are done. Note that SQL Server Audit does not obviate the use of DML or DDL triggers as a tool for watching what users are doing, mostly because in a trigger you can react to what a user is doing and alter their path. Using SQL Server Audit, you will simply be able to watch what the user is doing.

The auditing is file based, in that you don’t do your logging to a database; rather, you specify a directory on your server (or off your server if you so desire). You will want to make sure that the directory is a very fast access location to write to, because writing to it will be part of the transactions you execute. When auditing is turned on, each operation will be audited or not executed. However, it doesn’t write directly to the file; rather, for maximum performance, SQL Server Audit uses Service Broker queues under the covers, so it doesn’t have to write audit data to the file as part of each transaction. Instead, queue mechanisms make sure that the data is written asynchronously (there is a setting to force an audit trail to be written in some amount of time or synchronously if you need it to be guaranteed 100 percent up to date).

The audit structures consist of three basic objects:

• Server audit: Top-level object that defines where the audit file will be written to and other essential settings
• Server audit specification: Defines the actions at the server level that will be audited
• Database audit specification: Defines the actions at the database level that will be audited

In the following sections, we will go through the steps to define an audit specification, enable the audit, and then view the audit results.

Watching Table History Using DML Triggers

Even in addition to the kind of logging you can do with SQL Server Audit, you will still find uses for trigger-based logging of table history. And in previous versions of SQL Server, trigger-based logging is very much the way to watch what your users do with your data.

As discussed in Chapter 6, you can run code whenever a user executes an INSERT, UPDATE, or DELETE DML statement on a table or view. We already constructed an auditing trigger in Chapter 6 in the section “DML Triggers” that audited change, and we’ll create another one here. When finished, a history of the previous values for a column in a table will be maintained, in case some user changes the data improperly.

The scenario is that we have a slice of the Sales and Inventory sections of the database for products and invoices (see Figure 9-6).

On each invoice line item, there’s a cost and a discount percentage. If the cost value doesn’t match the current value in the Product table and the discount percentage isn’t zero, we want to log the difference. A report of differences will be built and sent to the manager to let the values be checked to make sure everything is within reason.

First we build the tables, going back to the ClassicSecurityExample database:

USE ClassicSecurityExample;

GO

CREATE SCHEMA Sales;

GO

CREATE SCHEMA Inventory;

GO

 CREATE TABLE Sales.Invoice

 (

   InvoiceId int not null identity(1,1) CONSTRAINT PKInvoice PRIMARY KEY,

   InvoiceNumber char(10) not null

        CONSTRAINT AKInvoice_InvoiceNumber UNIQUE,

   CustomerName varchar(60) not null , --should be normalized in real database

   InvoiceDate smalldatetime not null

 );

 CREATE TABLE Inventory.Product

 (

   ProductId int not null identity(1,1) CONSTRAINT PKProduct PRIMARY KEY,

   name varchar(30) not null CONSTRAINT AKProduct_name UNIQUE,

   Description varchar(60) not null ,

   Cost numeric(12,4) not null

 );

 CREATE TABLE Sales.InvoiceLineItem

 (

   InvoiceLineItemId int not null identity(1,1)

             CONSTRAINT PKInvoiceLineItem PRIMARY KEY,

   InvoiceId int not null,

   ProductId int not null,

   Quantity numeric(6,2) not null,

   Cost numeric(12,4) not null,

   discount numeric(3,2) not null,

   discountExplanation varchar(200) not null,

   CONSTRAINT AKInvoiceLineItem_InvoiceAndProduct

             UNIQUE (InvoiceId, ProductId),

   CONSTRAINT FKSales_Invoice$listsSoldProductsIn$Sales_InvoiceLineItem

             FOREIGN KEY (InvoiceId) REFERENCES Sales.Invoice(InvoiceId),

   CONSTRAINT FKSales_Product$isSoldVia$Sales_InvoiceLineItem

             FOREIGN KEY (InvoiceId) REFERENCES Sales.Invoice(InvoiceId)

   --more constraints should be in place for full implementation

 );

Now, we create another table to hold the audit of the line item of an invoice:

 CREATE TABLE Sales.InvoiceLineItemDiscountAudit

 (

   InvoiceLineItemDiscountAudit int not null identity(1,1)

       CONSTRAINT PKInvoiceLineItemDiscountAudit PRIMARY KEY,

   InvoiceId int NOT NULL,

   InvoiceLineItemId int not null,

   AuditTime datetime not null,

   SetByUserId sysname not null,

   Quantity numeric(6,2) not null,

   Cost numeric(12,4) not null,

   Discount numeric(3,2) not null,

   DiscountExplanation varchar(300) not null

 );

I used a surrogate primary key with no other uniqueness criteria here for the audit table, because you just cannot predict whether two users could change the same row at the same point in time (heck, even the same user could change the row twice at the same time on two different connections!). In this case, we settle on the surrogate to simply represent the order of events, rather than their existing logical uniqueness.

Then, we code a trigger, using the same trigger template as in the previous chapters (and defined in Appendix B). The trigger will cascade the change from the primary table to the audit table behind the scenes:

 CREATE TRIGGER Sales.InvoiceLineItem$insertAndUpdateAuditTrail

 ON Sales.InvoiceLineItem

 AFTER INSERT,UPDATE AS

 BEGIN

   SET NOCOUNT ON;

   SET ROWCOUNT 0; --in case the client has modified the rowcount

   --use inserted for insert or update trigger, deleted for update or delete trigger

   --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

   --that is equal to number of merged rows, not rows being checked in trigger

   DECLARE @msg varchar(2000), --used to hold the error message

   --use inserted for insert or update trigger, deleted for update or delete trigger

   --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

   --that is equal to number of merged rows, not rows being checked in trigger

         @rowsAffected int = (SELECT COUNT(*) FROM inserted);

   --    @rowsAffected int = (SELECT COUNT(*) FROM deleted);

   --no need to continue on if no rows affected

   IF @rowsAffected = 0 RETURN;

   BEGIN TRY

     --[validation blocks]

     --[modification blocks]

     IF UPDATE(Cost)

   INSERT INTO Sales.InvoiceLineItemDiscountAudit (InvoiceId,

           InvoiceLineItemId, AuditTime, SetByUserId, Quantity,

           Cost, Discount, DiscountExplanation)

   SELECT inserted.InvoiceId, inserted.InvoiceLineItemId,

           current_timestamp, suser_sname(), inserted.Quantity,

           inserted.Cost, inserted.Discount,

           inserted.DiscountExplanation

   FROM inserted

           JOIN Inventory.Product as Product

              ON inserted.ProductId = Product.ProductId

   --if the Discount is more than 0, or the cost supplied is less than the

   --current value

   WHERE inserted.Discount > 0

      OR inserted.Cost < Product.Cost;

           -- if it was the same or greater, that is good!

           -- this keeps us from logging if the cost didn't actually

           -- change

 END TRY

 BEGIN CATCH

   IF @@trancount > 0

     ROLLBACK TRANSACTION;

   THROW;

 END CATCH

END

We then test the code by creating a few products:

 INSERT INTO Inventory.Product(name, Description,Cost)

 VALUES  ('Duck Picture','Picture on the wall in my hotelRoom',200.00),

         ('Cow Picture','Picture on the other wall in my hotelRoom',150.00);

Then, we start an invoice:

 INSERT INTO Sales.Invoice(InvoiceNumber, CustomerName, InvoiceDate)

 VALUES  ('IE00000001','The Hotel Picture Company','2o12-01-01'),

Next, we add an InvoiceLineItem that’s clean, has the same price, and has no discount:

 INSERT INTO Sales.InvoiceLineItem(InvoiceId, ProductId, Quantity,

                                  Cost, Discount, DiscountExplanation)

 SELECT  (SELECT InvoiceId

         FROM Sales.Invoice

         WHERE InvoiceNumber = 'IE00000001'),

         (SELECT ProductId

         FROM Inventory.Product

         WHERE Name = 'Duck Picture'), 1,200,0,'';

We check our log:

SELECT * FROM Sales.InvoiceLineItemDiscountAudit;

Nothing is returned on insert:

Then, we create a row with a discount percentage:

INSERT INTO Sales.InvoiceLineItem(InvoiceId, ProductId, Quantity,

                                  Cost, Discount, DiscountExplanation)

 SELECT  (SELECT InvoiceId

         FROM Sales.Invoice

         WHERE InvoiceNumber = 'IE00000001'),

         (SELECT ProductId

         FROM Inventory.Product

         WHERE name = 'Cow Picture'),

         1,150,.45,'Customer purchased two, so I gave 45% off';

Checking the audit log this time:

SELECT * FROM Sales.InvoiceLineItemDiscountAudit;

Now, we see that a result has been logged:

DML triggers make wonderful security devices for keeping an eye on what users do, because those triggers can be completely transparent to users and to programmers. The only catch is when an application layer isn’t passing the security context through to the application. Sometimes, the application uses one common login, and there isn’t any automatic way for the database code to determine which user is actually doing the DML operation. Most of the time when that is the case, you’ll have already dealt with the problem using some method (such as just passing the username to the stored procedure, adding a column to the table to hold the user that is doing the operation, and so on).

DDL Triggers

DDL triggers let you watch what users do, but instead of watching what they do to data, you can watch what they do to the system. They let us protect and monitor changes to the server or database structure by firing when a user executes any DDL statement. The list of DDL statements you can monitor is quite long. (There are server-level events, such as creating and altering logins, as well as for the database, including creating and modifying tables, indexes, views, procedures, and so on. For a full list, check SQL Server Books Online in the “DDL Events Groups” topic.)

DDL triggers are of no value in protecting data values, because they don’t fire for operations where data is changed or manipulated. They are, however, good for monitoring and preventing changes to the system, even by users who have the rights to do so. For example, consider the all-too-frequent case where the manager of the IT group has system administration powers on the database, though he or she can barely spell “SQL” (if this power wasn’t granted, it would seem like a slight to the abilities and power of this manager). Now, let’s assume that this manager is just pointing and clicking around the UI, and one click is to the wrong place, and all of a sudden, your customer table joins the choir invisible. Now, you have to restore from a backup and waste a day cleaning up the mess, while trying to figure out who dropped the table. (OK, so if you have constraints on your table, you can’t actually drop it that easily. And yes, to be honest, most every DBA has dropped some object in a production database. That ends the honest part of this section.)

Logging with Profiler

The last line of defense is the “security camera” approach, particularly if you are using a version that doesn’t support database auditing. Just watch the activity on your server and make sure it looks legit. This is probably the only approach that has a chance to work with malicious (or stupid) programmers and DBAs. I include DBAs in here, because there’s often no way to avoid giving a few of them system-administration powers. Hence, they might have access to parts of the data that they should never go to, unless there’s a problem. They might stumble into data they shouldn’t see, though as we discussed previously, we can use encryption to obfuscate this data. Unfortunately, even encryption won’t necessarily stop a user with sys_admin rights.

Using Profiler, we can set up filters to look at certain events that we know shouldn’t be happening, even when a DBA has access. For example, think back to our encryption example, where we stored the encryption password in the table. We could formulate a Profiler task to log only usage of this object. This log might be pretty small, especially if we filter out users who should be regularly accessing encrypted data. None of this is perfect, because any users who have admin rights to the Windows server and the database server could hide their activity with enough effort.

I won’t demonstrate building a server side trace, but you can use sp_trace_create and sp_trace_setevent to create a trace to watch for certain actions. Of course, profiler is way more than a security tool, and it’s a tool you really need to learn to be a great SQL programmer. For a deep look at profiler, check out my good friend Brad McGehee’s book Mastering SQL Server Profiler, published by Simple-Talk.