This morning was a jammed filled session covering off a lot of changes made to Microsoft SQL Server 2005. Over the last few weeks we talk exclusively about Front End security issues such as Input trust and the creation of a Development and Design environment to better emulate your production environment. However, today we switched gears by examining the security enhancements made to SQL Server 2005.
A good place to start the talk on was with the Surface Configuration Tool. By default many features including xp_cmdshell, clr and even remote connections have been turned off in SQL Server 2005. Therefore, if you were to immediately start developing in SQL Server 2005 you might start experiences security errors when invoking some of these features. Thus, the Surface Configuration Tool is an excellent way to examine which features are disabled and also provides the ability to enable the particular features required. The SQL Server Surface Area Configuration tool is located under All Programs, Microsoft SQL Server 2005, Configuration Tools.
Authentication has changed in SQL Server 2005 with the support of Windows Password Policies enforcements when creating SQL Server login accounts. This is an excellent way to enforce strong passwords and an expiration policy on passwords depending upon your security needs. This can be configured on a per-login basis as demonstrated with the script below:
create login foo with password='@#Hkjsdf#$#VDSVSQ@!',
CHECK_EXPIRATION=ON,CHECK_POLICY=ON
go
select * from sys.sql_logins
GO
declare @name nchar(100)
SET @name ='foo'
SELECT LOGINPROPERTY( @name, 'PasswordLastSetTime' ) AS PasswordLastSetTime,
LOGINPROPERTY( @name, 'IsExpired' ) AS IsExpiried,
LOGINPROPERTY( @name, 'IsLocked' ) AS IsLocked,
LOGINPROPERTY( @name, 'IsMustChange' ) AS IsMustChange,
LOGINPROPERTY( @name, 'LockoutTime' ) AS LockoutTime,
LOGINPROPERTY( @name, 'BadPasswordCount' ) AS BadPasswordCount,
LOGINPROPERTY( @name, 'BadPasswordTime' ) AS BadPasswordTime,
LOGINPROPERTY( @name, 'HistoryLength' ) AS HistoryLength,
LOGINPROPERTY( @name, 'PasswordHash' ) AS PasswordHash
GO
--cleanup
drop login foo
Schemas have been added as an abstract between the database and the owner of the objects. Therfore, by assigning objects to schema it is possible to drop users without rewriting your applications as the name resolution is no longer depend upon the user or principals names. We can continue to use the default schema of dbo similar to what we are used to in SQL Server 2000. However, if your application creates objects in the database and you want those objects to be created under the dbo schema then you must grant your application dbo privileges when connecting to the database. This will increase the attack surface of your application as well increasing the severity if your application is vulnerably to SQL Injection attacks. Schemas are also a nice mechanism to scope your permissions. For example, you can grant select permission on a schema. This will grant select permission to all the tables in that schema alone but not the other tables in other schemas.
Execute Content provides an excellent mechanism to have your modules such as functions, proc and triggers to run under a different user context then the caller of the module. The permissions that one can assign in SQL Server 2005 is very granular especially compared with SQL Server 2000, however, if you are unable to provide a single permission attribute to some database users then you can use execute content. Therefore, the proc can run under a privilege account that has certain permission such as truncate table, and the caller only has to be granted execute permission on the proc itself and not truncate table. Highlighted in the demo script below:
--This example will create 3 users
--
-- User1 will have a table MyTable
-- User2 will have a stored proc that select's the table
-- User3 will have execute permissions on User 2's stored proc
--
--This demo show the use of EXECUTE AS functionality
--Create our users
create login Login1 with password='(*&sdf87786sdf'
go
create login Login2 with password='(*&sdf87786sdf'
go
create login Login3 with password='(*&sdf87786sdf'
go
create database ExampleDB
go
use ExampleDB
go
--User 1 will have a table
create user User1 for login Login1 with default_schema=User1
go
create schema User1 authorization User1
go
--User 2 will have SELECT access and write a proc to access
create user User2 for login Login2 with default_schema=User2
go
create schema User2 authorization User2
go
--User 3 will have the right to exec the proc
create user User3 for login Login3 with default_schema=User3
go
create schema User3 authorization User3
go
grant create table to User1
go
grant create proc to User2
go
execute as login='Login1'
go
create table User1.MyTable
(ANumber int)
go
insert into MyTable values (1)
insert into MyTable values (2)
insert into MyTable values (3)
go
grant select on MyTable to User2
go
revert
go
execute as login='Login2'
--create a stored proc that will return the rows in our table
create proc ViewMyNumbers
AS
BEGIN
select * from User1.MyTable
END
grant execute on ViewMyNumbers to User3
go
revert
go
execute as login='Login3'
--Can't access table directly
select * from User1.MyTable
--I can't execute the proc since I don't have permissions on the unlying table
exec User2.ViewMyNumbers
--What I can do is alter the proc and set it to "execute as owner"
revert
go
execute as login='Login2'
go
ALTER PROCEDURE ViewMyNumbers
WITH EXECUTE AS OWNER
AS
BEGIN
select * from User1.MyTable
END
revert
go
execute as login='Login3'
--Still can't access table directly
select * from User1.MyTable
--Now I can access it and I didn't have to give User1
--any permissions on MyTable
exec User2.ViewMyNumbers
go
revert
go
SQL Server now provides build DDL statements and functions for encrypting and decrypting data inside the database and not necessary in manage code anymore. Encryption has never really been difficult but rather the management of the keys becomes the overwhelming issue. Therefore, SQL Server 2005 supports key management solution including the ability to manage the protection of the keys through a password that must be supplied by an application or an user. Or the protection of all keys in a database can be rooted under the Database Master key which is protected by default of the Service Master Key using the DPAPI for the instance of the SQL Server. I would highly recommend that you visit this excellent blog on certificates and Keys in SQL Server 2005. Demo script is provided below:
--Encrypt content demo
USE Master
GO
--Create Database, Users, Schemas, Table Object--
Create Database AccountsDB
GO
Use AccountsDB
GO
--create two logins that will be used
--logins for two consultants in a investors office
Create login Sheila with password='Capucci4!'
Create login Jon with password='Capucci4!'
--create users with Default schema
Create user Sheila with DEFAULT_SCHEMA=Fin
Create User Jon with DEFAULT_SCHEMA=Fin
GO
Create SCHEMA Fin Authorization Sheila
--Create Table to client table
Create table Fin.Clients (Id int, clientname nvarchar(30),
investor varchar(20),
SIN varbinary(100), Portfolio varbinary(100))
--Assign permission to table
grant select, insert on Fin.Clients to Sheila
grant select, insert on Fin.Clients to Jon
--Now create a certificate for each consultant
Create certificate SheilaCert
authorization Sheila with subject='SheilaCert'
--Notice error, we need to create a database master key first
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Capucci4!'
--Now lets create the certificates
Create certificate SheilaCert
Authorization Sheila with subject = 'SheilaCert'
Create Certificate JonCert
Authorization Jon with subject = 'JonCert'
--create symmetric keys for each of the consultants
CREATE SYMMETRIC KEY SheilaKey AUTHORIZATION Sheila
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE SheilaCert;
CREATE SYMMETRIC KEY JonKey AUTHORIZATION JON
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE JonCert;
--View the list of the keys in the database
Select * from sys.symmetric_keys
--SIMULATE CONNECTING AS SHEILA--
--Login and Insert Client Data--
Execute As login='Sheila'
SELECT SUSER_NAME() as LoginName, USER_NAME() as DBUserName;
--open your symmetric key for usage
OPEN SYMMETRIC KEY SheilaKey
DECRYPTION BY CERTIFICATE SheilaCert;
--use the key to insert investor's client records
--including encrypted values into the table
insert into Fin.Clients values (1,'Neo','Sheila',
encryptByKey(Key_GUID('SheilaKey'),'111-111-111'),
encryptByKey(Key_GUID('SheilaKey'),'$150,000,000'))
insert into Fin.Clients values (2,'Smith','Sheila',
encryptByKey(Key_GUID('SheilaKey'),'222-222-222'),
encryptByKey(Key_GUID('SheilaKey'),'$200,000'))
--close all open keys
close all symmetric keys
--logging out of sheila's context
REVERT
--SIMULATE CONNECTING AS JON--
execute as login ='Jon'
--open your symmetric key for usage
OPEN SYMMETRIC KEY JonKey
DECRYPTION BY CERTIFICATE JonCert;
insert into Fin.Clients values (3,'WhiteRabbit','Jon',
encryptByKey(Key_GUID('JonKey'),'333-333-333'),
encryptByKey(Key_GUID('JonKey'),'$50,000'))
insert into Fin.Clients values (4,'Trinity','Jon',
encryptByKey(Key_GUID('JonKey'),'444-444-444'),
encryptByKey(Key_GUID('JonKey'),'$300'))
--close all open keys
close all symmetric keys
--simulate logging out of Jon's context
REVERT
--TEST THE RESULTS--
SELECT SUSER_NAME() as LoginName, USER_NAME() as DBUserName;
Select * from Fin.Clients
Execute As login='Sheila'
--open the relevant keys for usage
OPEN SYMMETRIC KEY SheilaKey
DECRYPTION BY CERTIFICATE SheilaCert;
--Select from the table including decrypting
select id, clientname, investor,
convert(varchar,decryptbykey(SIN)) as SIN,
convert(varchar,decryptbykey(Portfolio)) as Portfolio
from Fin.Clients
--simulate logging out
Close all symmetric keys
REVERT
Execute As login='Jon'
SELECT SUSER_NAME() as LoginName, USER_NAME() as DBUserName;
--open the relevant keys for usage
OPEN SYMMETRIC KEY JonKey
DECRYPTION BY CERTIFICATE JonCert;
--Select from the table including decrypting
select id, clientname, investor,
convert(varchar,decryptbykey(SIN)) as SIN,
convert(varchar,decryptbykey(Portfolio)) as Portfolio
from Fin.Clients
--simulate logging out
Close all symmetric keys
REVERT
Note: It is also possible to achieve row level security without encryption as depicted in this article. I also recommend you check out this blog entry as well for a tool to assist with row level security.
With impersonate capabilities, you can now flow a single SQL context to the middle tier in the connection object, however, as part of the Command object you can now pass the end user –ASP.NET Principal Object which is derived from the HttpContext.User—to the database and have your middle account impersonate the end user. This provides the ability to flow just a single context in the connection object to ensure connection pooling for performance and by impersonating the end user and having the middle tier account context switch to the end use in the database auditing now be achieved at the database level. As shown in the illustration below:
--*****IMPERSONALIZATION DEMO*******
--*****WHO AM I******
SELECT SUSER_NAME() as LoginName, USER_NAME() as DBUserName;
--******Create Login Account SQLUser and Dan******
USE Master
Go
Create login SQLUser with password='Capucci4!' --Middle Tier Account
Create login Dan with password='Capucci4!' --User to Impersonate
--*****Create Both Users in Database******
Use AdventureWorks
Go
Create user SQLUser with DEFAULT_SCHEMA=HumanResources
Create user Dan with DEFAULT_SCHEMA=HumanResources
--*****Grant Permission to Middle Tier Account*****
Grant Select on HumanResources.Employee to SQLUser
Grant Select on HumanResources.Employee to Dan
--*****Allow SQLUser to Impersonate Dan******
GRANT IMPERSONATE ON USER:: Dan TO SQLUser
--*****Login as MiddleTier Account*****
--*****Connect to AdventureWorks Database*****
--*****Through the SQLConnection Object*******
Use AdventureWorks
Go
Execute As Login='SQLUser'
SELECT SUSER_NAME() as LoginName, USER_NAME() as DBUserName;
--****Auditing Under SQLUser****************
Select EmployeeID, LoginID, SickLeaveHours
FROM HumanResources.Employee
Where SickLeaveHours > 75
--****Pass End User account ie.Dan--HttpContext.User—in
--****the command object***********
SELECT SUSER_NAME() as LoginName, USER_NAME() as DBUserName;
Declare @username varchar(25)
Set @username='Dan'
Execute as User=@username;
SELECT SUSER_NAME()