443 Chapter6.Designing Database Security

Designing Database Security
Lesson 1:  Designing Instance-level Security
1. Configuring Service Account
(1) In SQL Server 2005, when selecting the service account for SQL server instance, you should use an account with minimal permissions to restrict the access to system on your network. Because service accounts are low-privileged accounts, additional configuration is required to ensure that the SQL Server instance can function. To run a SQL Server instance, the account need permissions to several resources, such as the following
 Read/Write permissions to the SQL Server registry keys
 Run as Service tights
 Read/Write permissions to the directories containing database files
(2) Group membership
(3) Service Account Permissions
DBA do not need to change the default permissions assigned to default SQL service group
DBA should understand the security context of each command you will execute. (For example, backup)
DBA should grant any additional permission needed for processes to execute to the Windows group that contains the service account.
2. Instance-level principal
(1) Principal are entities that are granted permissions to a securable. At an instance level, principals are implemented as logins, which can be of five types:
 Standard SQL Server login
 Windows login
 Windows group
 Certificate
 Asymmetric key
 
You can map a SQL Server login to a Windows user account or Windows group.
Create a Windows user account, specify the user to run IIS, and then you can use this account to connect to SQL Server.
(2) sample
3. Authentication mode
(1) Windows authentication
(2) SQL Server and Windows authentication (Mixed Mode)
4. Configuring the attack surface
(1) SQL Server installs and configures under three principles:
 Secure by design
 Secure by default
 Secure by deployment
(2) SQL Server Configuration Manager. It enables you to stop, pause, and start SQL Server services. The most function of the SSCM is to change service accounts and service account passwords.
 

(3) SQL Server Surface Area Configuration. It enables you to turn features and behaviors on or off.

MSDE, new called SQL Server Express Edition, is installed by many applications to e used as a local data store. Each of these installations is another instance of SQL Server.

The default connection for different SQL Server Edition
Edition Default setting
Express, Evaluation, Developer Local connection
Workgroup, Standard, Enterprise Both local and remote connection

 Some description for surface configuration features
Feature Description
Ad Hoc Remote Queries If you have any queries that use OPENROWSET or OPENDATASOURCE command
CLR If you intend to use triggers, functions, stored procedures, custom aggregates, or CLR Data types written in .NET language, you must enable the CLR capability.
Database Mail 
Xp_cmdshell You commonly use xp_cmdshell in administrative procedures to execute ad hoc SQL commands as well as operating system commands.

5. Server role
name principal_id sid type type_desc
bulkadmin 10 0x0A R SERVER_ROLE
dbcreator 9 0x09 R SERVER_ROLE
diskadmin 8 0x08 R SERVER_ROLE
processadmin 7 0x07 R SERVER_ROLE
public 2 0x02 R SERVER_ROLE
securityadmin 4 0x04 R SERVER_ROLE
serveradmin 5 0x05 R SERVER_ROLE
setupadmin 6 0x06 R SERVER_ROLE
sysadmin 3 0x03 R SERVER_ROLE

6. SQL Server Agent proxy accounts
Job steps created within SQL Server Agent can access external subsystems such as SSIS or operating system commands. You can create proxy accounts for these jobs to provide the appropriate permissions. This provides more granular control than granting permissions to the SQL Server Agent service account because every job step would then have elevated permissions
7. Designing Security for .NET Assemblies
(1) .NET assemblies use Code Access Security (CAS) to restrict the allowable set of operations that can be performed. SQL Server also uses CAS to lock down the managed code and ensure the integrity of the database server and operating system.
(2) When creating the assembly, the sysadmin specifies the level of CAS that is applied to the assembly. The access permissions available for assemblies are SAFE, EXTERNAL_ACCESS, and UNSAFE.
type Description
SAFE It is the default permission set and does not allow access to resources external to the SQL Server instance or any machine resources.
EXTERNAL_ACCESS It enables an assembly to access resource external to the SQL Server instance such as files, shares, and network resources.
UNSAFE It enables to perform any operation and access any resource.
8. Practice: Managing Accounts

Lesson 2:  Designing Database Security
1. Database Users and Roles
To grant access to the database, you add the login as a user in the database by executing CREATE USER command. When adding a user to a database, the user is normally mapped to a login, certificate, or asymmetric key. You can add each user in a database to one or more database roles.
Role Description
db_accessadmin Adds or removes user in the database
db_backupoperator Backs up the database
db_datareader Issues select operations against all tables within the database.
db_datawriter Issues insert, update, and delete operations against all tables within the database
db_ddladmin Execute DDK commands
db_denydatareader Denies select operations against all tables within the database
db_denydatawriter Denies insert, update, and delete operations against all tables
db_owner Owner of the database with full control over all objects
db_securityadmin Manages role membership and permissions
public Default group that every user belongs to
2. Designing Schemas
(1) Schemas are new features in SQL Server 2005 that provide a means to group objects within a database together.
 
(2) A schema is a securable object that enables you to group multiple objects into a schema and then grant permissions to a principal to the scheme.
(3) The most powerful capability of schemas is to manage permissions. Users with the authority to read related to employees can be granted permissions in two ways.
 You can grant select permission to each object within the HumanResources schema
 Or you can issue a single grant statement on the HumanResource schema.
(4) Schema provides more powerful security assignments.
3. Defining Encryption
(1) Encryption is accomplished by using a flexible, multilayered series that start at the instance level and goes through data within the database.
(2) Service Master Key. The root of the encryption hierarchy is the service master key, which is automatically generated the first time a credential needs to be encrypted. Service master keys are derived from the Windows credentials of the SQL Server service account and encrypted using either the local machine key or the Windows Data Protection API.
(3) Database Maser key.
 The next layer in the encryption hierarchy is the database master key, which must be explicitly generated using the following command. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
 Each database has a different master key.
 The database master key is used to protect any certificates, symmetric keys, or asymmetric keys that are stored within the database.
 The database master key is encrypted by using Triple Data Encryption Standards (DES) and the user-supplied password.
 When you make a request to decrypt data, the service master key is used to decrypt the database master key; decrypt a certificate, symmetric key, or asymmetric key; decrypt the data.
 TSQL Command: OPEN | BACKUP | RESOTER | CLOSE MASTER KEY
(4)  Asymmetric Keys. Asymmetric keys use a public and private key system.
(5) Certificates. A public key certificate is digital signed instrument that binds the public key to an identity. It can be a person, organization, or machine that controls the corresponding private key. A certificate is normally issued by a certificate authority that certifies the identity holding the certificate.
(6) Symmetric keys. Symmetric keys use a single key for both encryption and decryption.
4. Designing DDL Triggers
(1) The main purpose of DDL triggers is to audit as well as regulate actions performed on a database. This enables DDL operation to be restricted even if a user might normally have the permission to execute the DDL command.
5. Practice: Encryption
6. Practice
(1) Creating a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
GO

SELECT * FROM sys.symmetric_keys

(2) Encrypting Data with a Passphrase
DECLARE @plaintext NVARCHAR(50)
DECLARE @key NVARCHAR(50)
DECLARE @decrypted nvarchar(50)
DECLARE @encrypted varbinary(52)

SET @plaintext = 'SQL Server 2005'
SET @key = 'MyKey'
SET @encrypted = EncryptByPassPhrase(@key, @plaintext)
SET @decrypted = DecryptByPassPhrase(@key, @encrypted)

SELECT @encrypted Encrypted, @decrypted Decrypted

(3) Creating s self-signed Certificate
DECLARE @encrypted varbinary(500)
DECLARE @decrypted varchar(50)
SET @encrypted = EncryptByCert(Cert_id('MyCert'), 'SQL Server 2005')
SET @decrypted = DecryptByCert(Cert_id('MyCert'), @encrypted)

SELECT @encrypted Encrypted, @decrypted Decrypted

(4) Creating an Asymmetric key
CREATE ASYMMETRIC KEY MyAsymmetricKey WITH ALGORITHM = RSA_2048
GO

SELECT * FROM sys.asymmetric_keys
GO

DECLARE @encrypted varbinary(500)
DECLARE @decrypted varchar(50)

SET @encrypted = EncryptByAsymKey(AsymKey_id('MyAsymmetricKey'), 'SQL Server 2005')
SET @decrypted = DecryptByAsymKey(AsymKey_id('MyAsymmetricKey'), @encrypted)

SELECT @encrypted Encrypted, @decrypted Decrypted

(5) Create a DDL Trigger
CREATE TRIGGER tddl_table_preventdropalter
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
 PRINT 'You are attempting to drop or alter tables in production!'
 ROLLBACK ;

DROP TABLE XmlTransfer

Lesson 3:  Securing Database Object
1. Permission Scope
(1) Permissions are granted on a securable that can be a database, a schema, or an object. This creates a hierarchical structure of permissions within a database.
(2) The first layer of security that you will want to plan within a database is a schema. Objects are then created within each schema. After objects are created in schemas, permission are granted on the schemas to provide security access to an application.
2. Execution Context
(1) An execution can be specified for code. Specifying an execution context enables code to be run under a specific security context.
(2) Module execution context
Context Description
CALLER Default behavior. The permissions of the user are evaluated based on the permissions granted by the schema owner.
<user name> Execute the code under another user’s credentials
SELF Executes under the security credentials of the user specifying the execution context
OWNER Executes under the security credentials of the owner of the schema that the object belongs to.
3. Encrypting Columns
4. Practice: Granting Permission
(1) Grant permission to a database.
(2) Grant permission to a schema.
(3) Encrypt and decrypt column
create table staff
(
staff_no nvarchar(5) not null,
first_name nvarchar(30) not null,
last_name nvarchar(10) not null,
salary varbinary(256)
)
go

insert into staff
(staff_no, first_name, last_name, salary)
values
('10000','Bill', 'Zhang',
ENCRYPTBYPASSPHRASE('P@$$w0rd', convert(nvarchar(20),'8000.00'),
1, convert(varbinary, '10000')))

GO

select
staff_no, first_name, last_name,
CONVERT(nvarchar(20), DecryptByPassphrase('P@$$w0rd', salary, 1, CONVERT(varbinary, '10000')))
    AS salary
 from staff