Chapter 8

Securing the Database Instance

WHAT’S IN THIS CHAPTER?

• SQL Server Authentication Types
• Windows Authentication Types
• Authorizing Object Level Security
• Maintaining Row Level Security

Security of a Microsoft SQL Server instance is probably one of the least sexy topics out there today. And unfortunately that will probably remain the case for many years to come, if not forever. However, properly securing the database instance is extremely important because without a properly secured instance, there is no way to guarantee that the data stored within the SQL Server instance is the data expected to be in the instance. Changes that an attacker could make to the data within an instance of SQL Server could be as small as simply changing names, to changing the prices that the customers are charged for products, to injecting Java script code or HTML, which is then served to customers or employees via their web browser and executes unexpected code on their machine. These changes could be minor; however more than likely they could install some sort of dangerous application on the user’s computer such as a Trojan horse or key logger. So in reality, anything can happen, and it is best to be prepared for all scenarios.

AUTHENTICATION TYPES

There are two ways to connect to the Microsoft SQL Server instance: via SQL Server authentication and Windows authentication. When SQL Server is installed, there is an option to select if the SQL Server instance should support Windows authentication only or if it should support both Windows and SQL Server authentication.

SQL Authentication

SQL Server authentication was the original authentication method supported by SQL Server when it was based on the Sybase code base. With SQL Server authentication, the application or the user specifies the username and password to be used to authenticate against the SQL Server instance. When specified, the username and password are put into the connection string, which the application then uses when it connects to the SQL Server instance. With SQL Server authentication, the actual username and password are stored in the master database within the database instance.

When you use SQL Server authentication, the account and password are passed to the database instance, which then hashes the password and compares the username and password hash against the list of SQL accounts stored within the master database. If the passed-in username and password hash match an account stored within the master database, authentication succeeds, you can connect, and the rights associated with the SQL account are granted to you. If no match is found, an authentication error is returned.

SQL Server logins can be configured to force them to follow the Windows Active Directory security policies. For the domain policies to be enforced the Windows domain must be a Windows 2003 or higher and the SQL Server must be installed on a Windows 2003 or higher version of the Windows operating system. Care should be taken when planning your SQL Server installations as SQL Server 2012 is the first version of Microsoft SQL Server which requires that it be installed on Windows Server 2008 or higher. There are two domain policy settings which can be enabled. The first is to follow the domain password policies that control password complexity. The second is to enforce password expiration.

Domain password policies are extremely complex and are outside the scope of this book.

Setting the password policies can be done via SQL Server Management Studio by editing the login and checking or unchecking the needed policies. The settings can also be enabled or disabled by using the ALTER LOGIN statement as shown in the following code snippet:

ALTER LOGIN chain_test
WITH CHECK_POLICY = ON, CHECK_EXPIRATION=ON

The domain policies are only verified when the account is created, or when the password is changed. This means that a SQL login could be created with a password that doesn’t meet the domain policy requirements. Then after it is created the check policy setting could be enabled and it would be assumed that the account met the domain policy requirements when in fact it doesn’t.

SQL Server 2012 introduces a new concept called the contained user, which is used within contained databases (for more information about contained databases see Chapter 4, “Managing and Troubleshooting the Database Engine”). A contained user exists only within a contained database and the password for the contained user exists within the database and not within the master database. As the contained user exists only within the database, no instance level rights can be granted to a contained user, and the contained user can only access objects within the database in which the contained user exists. Contained users are created by using the CREATE USER statement which is shown in the following code snippet by specifying the password parameter instead of the FROM LOGIN parameter.

CREATE USER MyContainedUser WITH PASSWORD='MySecurePassword'
GO

Within the context of the database in which the contained user is created, rights to objects and permission chains all work in exactly the same process as a traditional or non-contained user.

Contained users can be created based on local or domain Windows accounts, or they can be created as SQL Server users. A contained Windows user is simply a Windows account that doesn’t have a corresponding login at the server level. Contained SQL users do not have the option of being configured to follow domain policies like traditional SQL logins.

Windows Authentication

Windows authentication was introduced to the SQL Server database engine in SQL Server 6.0. With Windows authentication, the username and password for the account are stored within the Active Directory database and not the SQL Server instance. This enables the Windows Active Directory infrastructure to handle the actual authentication of the account. Rights to connect to the SQL Server instance and to databases within the instance can be granted to individual Windows accounts or to groups which are created and managed within the Windows Active Directory domain.

When you connect to the SQL Server database using Windows authentication, the SQL Server isn’t actually the one handling the Windows authentication. When you log into the Windows OS, an authentication token is generated by the domain controller, which is then passed to the client computer and stored within the computer’s memory. When you attempt to connect to the SQL Server using Windows authentication, this token is then passed from the client computer’s operating system to the SQL Server. The SQL Server then uses its operating system to verify with the domain controllers that the token is valid and verifies that your Security Identifier (SID) can access the SQL Server instance, and determines what level of permissions you should be granted.

The full Windows authentication process is complex and has been documented in a couple of different places. You can dig through the Microsoft TechNet website to find the various articles that make up the documentation, or you can look at Chapter 3 of Securing SQL Server by Denny Cherry (Syngress, 2010), in which the Windows authentication process is spelled out step by step.

SQL Versus Windows Authentication

Because of the differences in SQL Server and Windows authentication, SQL Server authentication is considered to be a much less secure method of authentication and should be disabled whenever possible. When SQL Server authentication is enabled, those who want to attack the SQL Server instance can use brute force attacks to attempt to break in. This is because the username and password are simply sent to the SQL Server instead of the Windows authentication process that passes in the Windows token.

AUTHORIZING SECURABLES

Proper object level security within the database is key to keeping data within the SQL Server instance safe from intruders. This object level security extends from instance level objects, such as availability groups, and the ability to view the instances’ server state objects to securing specific objects within the user databases.

Rights can be granted at both the server level, the database level, or to specific objects. Permissions can also be chained together, which simplifies the permissions both within the database, using permissions chains, as well as across databases by using the cross databases chaining.

Three statements are used when changing permissions in SQL Server.

• GRANT is used to assign rights.
• DENY is used to prevent access.
• REVOKE is used to remove either a GRANT or a DENY.

When granting permissions in SQL Server, you need to remember that DENY always overwrites a GRANT. If a user is a member of three different roles, and two of the roles have been granted rights to query from a table and the third role has been denied rights to query the table, then the user cannot query from the table.

Figuring out many of the object rights in this chapter can be hard to visualize when simply reading through descriptions of the rights. Microsoft has a visual diagram that can make this easier. You can download it from http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/5710.Permissions_5F00_Poster_5F00_2008_5F00_R2_5F00_Wiki.pdf.

The same applies if higher level sets of rights are granted. For example, if you have been granted rights to SELECT from a schema and denied the right to query a specific table, you cannot query from the table. The same applies if you have been granted rights to query from a table and denied rights to query at the schema level; you cannot query from any table within the schema, no matter what rights have been granted to the table.

You can use a second syntax when granting rights to users: WITH GRANT syntax. Adding WITH GRANT to the end of the GRANT statement, as shown in following code snippet, enables the user who has been granted the right the ability to grant the right to other users within the database.

GRANT SELECT, INSERT ON dbo.Users TO MyUser WITH GRANT

Server Securables

Dozens of instance-wide privileges can be granted at the instance level. These include connecting and managing the various endpoints within the SQL Server instance, managing the logins within the SQL Server instance, various instance-wide settings, the AlwaysOn Availability Groups, and the user-defined server roles that were introduced in SQL Server 2012.

The biggest difference between instance-wide privileges and database-wide privileges is that instance-wide privileges are granted directly to the login, whereas database-wide privileges are granted to users, and these users are then mapped to logins.

The terms logins and users get interchanged often, but within the scope of Microsoft SQL Server these are two very different things. Logins are used to log into the database instance, while users are mapped to a login from within the SQL Server databases.

Now take a look at the different endpoint privileges available to the various objects within the scope of the SQL Server instance.

Endpoints

You can manage five privileges for each endpoint within the SQL Server instance:

• Alter: The Alter right enables the login that has the privilege to make configuration changes to the endpoint.
• Connect: The Connect privilege enables the user that has the privilege to connect to the endpoint; by default all logins are granted the privilege to connect to the default endpoints.
• Control: The Control privilege grants the other four privileges.
• Take Ownership: The Take Ownership privilege grants the login the ability to become the owner of the endpoint.
• View Definition: The View Definition privilege grants the login the ability to view the configuration of the endpoint without being able to make changes to the endpoint.

Logins

You can manage four privileges for each login within the SQL Server instance:

• Alter: The Alter privilege enables the login that has been given that right to make changes to the second login that the right was granted to. For example, if there were two logins named login1 and login2, login1 can be granted the ability to alter login2. Altering a login gives the granted login the ability to change the password, default database, default language, and so on of the grantee login.
• Control: The Control privilege grants the granted user the other three privileges to the grantee login. For example, if there were two logins named login1 and login2, login1 can be granted the ability to control login2.
• Impersonate: The Impersonate privilege grants the granted user the ability to use the EXECUTE AS syntax specifying the grantee login the ability to execute code as the grantee login.
• View Definition: The View Definition privilege grants the granted user the ability to view the configuration of the grantee login.

Instance-Wide Settings

Thirty-one privileges can be granted to a specific login. Table 8-1 shows these privileges and their meaning.

TABLE 8-1: Instance Privileges and Meanings

The “Control server” right can be granted to users who need a higher permission level without needing the full blown set of administrative rights which come with being a member of the sysadmin fixed server role. Heed the warning that a user with this right has elevated permissions to the SQL Server instance so this right should not be given out often.

Availability Groups

Availability Groups have four rights that can be granted to user-defined server roles:

• Alter: The Alter privilege enables the user that has been assigned the privilege to make changes to the AlwaysOn Availability Group.
• Control: The Control privilege grants the other three privileges to the user.
• Take Ownership: The Take Ownership privilege enables the user who has been assigned the privilege the ability to change the ownership of the availability group.
• View Definition: The View Definition privilege enables the user who has been granted the right the ability to view the definition of the availability group.

User Defined Server Roles

SQL Server 2012 introduces user defined server roles. User defined server roles are similar to fixed server roles except that they are created by the SQL Server administrator and not by Microsoft. The user defined server roles can be made members of any other server role, either fixed or user defined. Any server-wide right (shown earlier in this chapter) that can be granted to a login can be granted to a user defined server role.

Four privileges can be granted to a login for each user defined server role:

• Alter: The Alter privilege grants the login the privilege to alter the user-defined server role. This includes adding other logins as members of the fixed server roles.
• Control: The Control privilege grants the other three privileges.
• Take Ownership: The Take Ownership privilege grants the login the ability to set himself as the owner of the user-defined server role.
• View Definition: The View Definition privilege grants the login the ability to view the user-defined server role without having the ability to alter the user-defined server role.

Fixed Server Roles

SQL Server has nine fixed server roles that are pre-defined by Microsoft and cannot be changed. Eight of these fixed server roles have existed since at least SQL Server 7, and the ninth role was added in SQL Server 2005. The newer role is the bulkadmin fixed server role. This role gives the members of the role the right to bulk insert data into the database. In other previous versions of SQL Server, bulk loading data into the database required being a member of the most powerful fixed server role, the sysadmin, a role that provides the ability to perform any action against any database without restriction. Other fixed server roles grant various rights to the members of the roles and are discussed in the following list:

• The dbcreator fixed server role grants the user the right to create databases.
• The diskadmin fixed server role grants the user the rights to manage the physical database files.
• The setupadmin fixed server role grants the user the rights to add and remove linked servers.
• The processadmin grants the rights to kill other users’ processes within the SQL Server instance.
• The securityadmin fixed server role enables the members of the role to GRANT, DENY, and REVOKE all server-level permissions as well as any database-level permissions for the databases which they have rights to.
• The serveradmin fixed server role enables the members to change any server-wide configuration option as well as use the SHUTDOWN command to shut down the SQL Server instance.
• The final fixed server role is the public role, which grants no rights; all logins on the instance are members of the public role.

Database Securables

Objects of various kinds exist within each SQL Server database, all of which have their own permissions. These permissions grant specific users rights to those objects so that the users can perform the functions needed to complete their tasks. It is a best practice to grant the users who will be using the database the minimum permissions needed to complete her job. This is done so that the users don’t have rights to objects or data within the database that they don’t need. An added benefit of this practice is to prevent someone who breaks into the database from gaining access to more secure data.

Database Permissions

Permissions can be granted against the database itself. Some of these rights are specific to the database level while some cascade down to objects within the database such as tables, views, and stored procedures.

Tables and Views

When dealing with tables and views, ten different rights can be granted to specific users or user-defined database roles. These are listed in Table 8-2.

TABLE 8-2: Rights for Tables and Views

Stored Procedures and Functions

Stored procedures, functions, and most other database objects within SQL Server contain only five permissions that can be granted to users or roles, and are listed in Table 8-3.

TABLE 8-3: Rights for Stored Procedures, Functions, and Most Other Database Objects

Permission Chains

Database permissions are chained from higher level code objects to lower level objects referenced within the object. If a table and stored procedure existed, as shown in the following code snippet, and a user were granted rights to execute the stored procedure, the permissions chain would enable the user the right to query the table, but only from within the context of the stored procedures. Any queries run from outside the stored procedure do not work unless the user has been granted specific rights to the table.

CREATE USER SampleUser WITHOUT LOGIN 
GO
CREATE TABLE dbo.Users
(UserId INT IDENTITY (1,1) PRIMARY KEY,
UserName varchar(100),
Password varchar(100))
go
CREATE PROCEDURE dbo.SignIn
     @UserName varchar(100),
     @Password varchar(100)
AS
SELECT UserId
FROM Users
WHERE UserName = @UserName
     and Password = @Password
GO
GRANT EXEC ON dbo.SignIn to SampleUser
GO

Permissions chains work only within the context of the same execution of the parent object. This is a fancy way to say that the permission chain does not work with dynamic SQL. When using dynamic SQL, the user must be granted rights to the specific objects called within the dynamic SQL.

Permissions chaining works within all native SQL Server objects including stored procedures; scalar functions; table valued functions and views and chains down to other stored procedures; scalar functions, table valued functions, views, tables, and service broker objects. Permission chaining is enabled by default and cannot be disabled. Permission chaining is what enables the best practice of only granting the minimum set of permissions needed because it only requires execution rights on the stored procedures that are called and not the base objects.

Cross Database Permission Chains

Cross database permission chaining is a newer feature of SQL Server, introduced in SQL Server 2000 Service Pack 3a. Cross database permission chaining is an extension of traditional permission chaining, but it enables the permissions chaining to apply between databases. Without cross database permission chaining, accessing objects in a second database using the three-part name of the object would require that the user have the necessary rights on the object. Looking at the script shown in the following code snippet, for the stored procedure to work as written, the user would need to have the SELECT privilege.

USE master
GO
CREATE DATABASE Sample1
GO
CREATE DATABASE Sample2
GO
USE Sample1
GO
CREATE TABLE dbo.Users
(UserId INT IDENTITY (1,1) PRIMARY KEY,
UserName varchar(100),
Password varchar(100))
go
USE Sample2
GO 
CREATE TABLE dbo.Users
(UserId INT IDENTITY (1,1) PRIMARY KEY,
UserName varchar(100),
Password varchar(100))
go
CREATE PROCEDURE dbo.VerifyUsers
AS
SELECT *
FROM dbo.Users b
WHERE NOT EXISTS (SELECT * FROM Sample1.dbo.Users a WHERE a.UserId = b.UserId)
GO

Cross database chaining is a database level setting which is disabled by default on all databases. To enable it, the cross database setting needs to be enabled on both the database that the procedure exists in and the database that the table exists in. You can see if cross database chaining is enabled in a couple ways. The easiest is to query the sys.databases catalog view, specifically looking at the is_db_chaining_on column that returns a 0 when cross database chaining is disabled and a 1 when cross database chaining is enabled, as shown in Figure 8-1.

FIGURE 8-1

The status of the database chaining can also be viewed by looking at the properties window of the database. This can be done by connecting to the database instance in the object explorer.

1. Navigate to Databases; then select the database in question.

2. Right-click the database and select Properties from the context menu that opens.

3. Select the options page, and scroll down in the Other Options section looking at the Miscellaneous section, as shown in Figure 8-2.

FIGURE 8-2

4. Within the Miscellaneous section, the Cross-Database Ownership Chaining Enabled field is visible, although grayed out. View the setting from the screen shown in Figure 8-2; although, you must use T-SQL to change this setting using the ALTER DATABASE statement.

As stated, enabling cross database chaining requires using the T-SQL ALTER DATABASE statement so that the DB_CHAINING option is enabled, as shown in the following code snippet. The ALTER DATABASE statement must be used against both databases as shown in the following code snippet so that the stored procedure shown in the previous code snippet works as written.

ALTER DATABASE Sample1 SET DB_CHAINING ON
GO 
ALTER DATABASE Sample2 SET DB_CHAINING ON
GO

One additional right must be granted for cross database chaining to correctly work. The user must be added as a user within the second database. The user does not need any additional rights within the database except to exist as a user within the second database either by creating a user that maps to the same login that the first database user is mapped to or by enabling the guest user within the second database. Enabling the guest user is not recommended because that would grant other users who may not need rights to the database rights that they do not need.

ROW LEVEL SECURITY

Row level security is something people are always asking about because Oracle has the concept for virtual private databases that enables the DBA to specify which rows the user can access.

The easiest way to do this is to create a view for each user who needs access, or a single view for each group of users that needs rights to specific rows within the database table. The user is then granted rights to the view and not to the table, and the user can then view only the rows that match the WHERE clause within the view.

Another technique that can be used is to design row-level security into the database schema. For example, if there were a department table and all managers needed to query the table and access only their direct reports information, this could be built into a view so that a single view could be used for all managers within the system. This can be done via some system functions such as the suser_sname() or current_user() functions. Listing 8-1 shows the suser_sname() function.

LISTING 8-1: Using the suser_sname() Function.sql

CREATE TABLE dbo.Employee
(EmployeeId INT IDENTITY(1,1) PRIMARY KEY,
LastName varchar(100),
FirstName varchar(100),
Emailaddress varchar(255),
ManagerId INT,
Username varchar(100))
GO
INSERT INTO dbo.Employee
(LastName, FirstName, EmailAddress, ManagerId, UserName)
VALUES
('Smith', 'John', '[email protected]', 0, 'CONTOSOjsmith'), 
     ('Gates', 'Fred', '[email protected]', 1, 'CONTOSOfgates'), 
     ('Jones', 'Bob', '[email protected]', 1, 'CONTOSOjones'), 
     ('Erickson', 'Paula', '[email protected]', 1, 'CONTOSOperickson')
GO
CREATE VIEW dbo.EmployeeView
AS
SELECT *
FROM dbo.Employee
WHERE ManagerId = (SELECT EmployeeId FROM Employee WHERE UserName = suser_sname())

GO

Microsoft has released an excellent whitepaper that discusses row level security in more detail. More information about the whitepaper can be found on the Public Sector blog hosted on MSDN at http://blogs.msdn.com/b/publicsector/archive/2011/08/23/row-level-security-for-sql-server-2008.aspx.

SUMMARY

Security must be done correctly because without database security all the information can be accessed by people who shouldn’t have access to the data; this can leave the database susceptible to internal and external threats. Security shouldn’t be limited to just the database and instance. A variety of levels of security need to be implemented both outside as well as inside the database. This includes properly securing the network, the Windows operating system that hosts the database, the instance, the database permissions, and the application that needs to be secured to ensure that SQL Inject attacks are not be successful.

With each release of Microsoft SQL Server, the face of security within the platform changes and administrators must adapt. With the release of SQL Server 2012, a powerful new tool has been added in the user defined server roles that enables a much more flexible security model.