50401A-ENU_Powerpnt_05x - Professional Data Management

fortnecessityusefulSoftware and s/w Development

Dec 14, 2013 (3 years and 7 months ago)

109 views

Module 5

Designing Security for SQL

Server 2008

Module Overview


Exploring Security in SQL Server 2008


Implementing Identity and Access Control


Guidelines for Secure Development in SQL Server 2008


Guidelines for Secure Deployment of SQL Server 2008


Guidelines for Secure Operations

Lesson 1: Exploring Security in SQL Server 2008


Overview of Securing SQL Server 2008


SQL Server 2008 Security Changes


SQL Injection Attacks


SQL Server 2008 Regulatory Compliance


Discussion: Preventing SQL Injection Attacks


Platform and

Network Security

Principals and
Database

Object Security

Application Security


Limit physical access to the server, hardware components and backup media


Apply all updates and upgrades to the operating system after you test them
with the database applications


Restrict access to operating system files


Reduce the SQL Server surface area by configuring set of permission of
securable


Enhance object and connection security by creating certificates



Write secure client applications

Overview of Securing SQL Server 2008


Platform and

Network Security

Principals and
Database

Object Security

Application Security

SQL Server 2008 Security Changes


Windows Local Group Changes


Surface Control Tools Changes


Kerberos Authentication







Major security changes in SQL Server 2008 are:

SQL Injection Attacks

To reduce the scope for injection attacks:


Accept only expected values of string variables



Avoid assumptions about the data
size, type, or content



Test the size and data type of input



Use stored procedures to validate user input



Validate XML data with the related XML schema



Avoid building T
-
SQL statements directly from user input



Validate all data in multitiered environments



Implement multiple layers of validation



Avoid concatenating unvalidated user input



Avoid accepting strings such as AUX and CLOCK$



Var ShipCity;

ShipCity =
Request.form
("ShipCity");

var sql =
SELECT * FROM
OrdersTable
WHERE ShipCity
= '
Redmond
'

SQL Server 2008 Regulatory Compliance


Sarbanes
-
Oxley Act (SOX)


Gramm
-
Leach
-
Bliley Act (GLBA)


Health Insurance Portability and Accountability Act (HIPAA)


FDA Title 21 CFR Part 11


California Senate Bill 1386


Common Criteria

You must adhere to the following acts, regulations, and certifications to
implement effective security mechanisms:

Discussion: Preventing SQL Injection Attacks


Describe SQL injection attack.


What measures can you take to minimize the risk of SQL injection
attacks?


Can you completely prevent SQL injection attacks?


Considerations for Using Principals


Considerations for Using Roles


Considerations for Using Securables


Considerations for Using Permissions


Discussion: Using a Loginless User Account

Lesson 2:
Implementing Identity and Access Control


Considerations for Using Principals




Every database includes INFORMATION_SCHEMA and sys entities


Server principals with names within double hash (##) marks are only
for internal use


Every database user belongs to the public database role


The guest user account cannot be dropped, but can be disabled by
revoking its CONNECT permission


The SQL Server sa login is a server
-
level principal


Application roles help control user access to specific data

Considerations for Using Roles

Every database user belongs to the public database role



Members of the db_ssisadmin role and the dc_admin role may be
able to elevate their privileges to sysadmin



䉹 摥晡畬琬 瑨攠⁂啉U呉T
\
䅤浩湩獴牡瑯爠杲潵瀠楳潴i楮i汵摥l 楮⁴桥h
卑S 卥牶敲S㈰〸 獹獡摭楮d晩f敤⁳e牶敲r牯汥



周攠坩湤潷猠杲潵灳 捲敡瑥搠景爠畳攠批b瑨攠卑S 卥牶敲S獥牶楣攠
慲攠湯琠楮i汵摥l 楮⁴桥h獹獡摭楮d晩f敤⁳e牶敲r牯汥



䙬數F扬攠摡瑡扡d攠牯汥l 慲攠湯琠慤摥搠慳 浥浢敲猠†††††††††
晩f敤⁲潬敳



Considerations

for Using Securables

Securables are the resources to which the SQL Server Database Engine authorization
system regulates access.

Ownership chaining enables managing
access to multiple objects by setting
permissions on one object.

Ownership Chains

File access permissions are set during
operations, such as creating, attaching,
modifying to add a new file, backing up,
or restoring databases.

Data Security

Schemas hold objects, such as tables
and views.

Every securable in a specific schema
must have a unique name.

Schema

Considerations for Using Permissions



Permissions for Fixed
Database Roles



Permissions for Fixed

Server Roles



Catalog Views

sys.server_permissions


sys.database_permissions



Principals and Securables

Windows Level

Server Level

Database Level

Discussion: Using a Loginless User Account


Describe a loginless user account.


What are the benefits of having a loginless user?

Lesson 3:
Guidelines for Secure Development in SQL
Server 2008


Guidelines for Execution Context


Using Module Signing


Guidelines for Context Switching


Demonstration: How To Control Execution Context by Using the
EXECUTE AS Clause


Guidelines for Impersonation

Guidelines for Execution Context



Valid only for a specific database


Contains the primary and secondary
identities against which database
-
level
permissions are checked

User Security Token

Execution context is represented by security tokens. A security token contains:


One server or database principal as the primary identity


One or more principals as secondary identities


Zero or more authenticators


Privileges and permissions of the primary and secondary identities



Valid across the instance of SQL
Server


Contains the primary and secondary
identities against which server
-
level
permissions and database
-
level
permissions are checked

Login Security Token

Verifying data

Using Module Signing

</1010>

Signer

Private key

</1010>

</1010>

Verifier

Public key

Signing data

</1010>

Guidelines for Context Switching


Explicit

Server
-
Level

Explicit

Database
-
Level

Implicit


The EXECUTE AS LOGIN = 'login_name' statement is used to switch
execution context at the server level


The login name must be visible as a principal in sys.server_principals


The statement caller must have IMPERSONATE permission on the

specified login name


The EXECUTE AS USER = '
user_name
' statement is used to switch execution
context at the
databse

level


The user name must exist as a principal in
sys.database_principals


The caller must have IMPERSONATE permissions on the specified user name


The execution context of a module can be implicitly changed by specifying a
user or login name in an EXECUTE AS clause in the module definition


The user that the module is impersonating needs to have permissions on the
objects accessed by the module


Explicit

Server
-
Level

Explicit

Database
-
Level

Implicit

Demonstration: How To Control Execution Context by

Using the EXECUTE AS Clause

In this demonstration, you will see how to:


Use EXECUTE AS to switch execution context


Server or database principals other than sa or dbo can call EXECUTE AS


The scope of impersonation is explicitly defined in the EXECUTE AS statement


With EXECUTE AS, the impersonation remains in effect until the session is dropped


You can create an execution context stack by calling the EXECUTE AS statement
multiple times across multiple principals

Windows User

SQL Server Instance

SQL Service Provider

Guidelines for Impersonation

SQL Server Impersonation Within a Domain

Advantages of the EXECUTE AS statement vs. SETUSER statement

Lesson 4:
Guidelines for Secure Deployment of SQL
Server 2008


Guidelines for Using Authentication Modes


Features of SQL Server Configuration Manager


Discussion: Using Policy
-
Based Management





Windows authentication is the default
authentication mode


Windows Authentication mode enables
Windows authentication and disables SQL
Server authentication


SQL Server validates user credentials by
using a Windows principal token


A connection made by using Windows
authentication is called a trusted
connection


Kerberos security protocol

in Windows authentication

enforces password policy



Mixed mode authentication enables both
Windows authentication and SQL Server
authentication


It requires a strong password for the built
-
in SQL Server system administrator
account named sa. The sa account
connects by using SQL Server
authentication

Guidelines for Using Authentication Modes

During setup, you must select an authentication mode for the Database Engine

Mixed Mode Authentication

Windows Authentication Mode

Features of SQL Server Configuration Manager

Protocols, Connection, and Startup Options:



Use the SQL Server Services area to start components and configure the

automatic starting options


Use the SQL Server Network Configuration area to enable connection protocols and

connection options

Command
-
prompt Options:


Use the Invoke
-
PolicyEvaluation SQL Server PowerShell cmdlet to invoke Surface Area
Configuration Policies

SOAP and Service Broker Endpoints:


Use Policy
-
based Management to turn endpoints off


Use CREATE ENDPOINT and ALTER ENDPOINT to create and alter the properties of
endpoints

Enabling and Disabling Features:


Use facets in SQL Server Management Studio to configure enabling and disabling features


Use Policy
-
based Management to check the configuration of a facet

Discussion: Using Policy
-
Based Management

Discuss how policy
-
based management can be used to regularly
inspect and verify that the specific security requirements are actively
in place.




Lesson 5: Guidelines for Secure Operations


Guidelines for Creating a Password Policy


SQL Server Cryptography Architecture


Using SQL Certificates and Asymmetric Keys


Guidelines for Using SQL Server Encryption


Cell
-
Level Encryption


Demonstration: How To Enable TDE


Discussion: Securing Data by Using Encryption




Guidelines for Creating a Password Policy

Password expiration

Use password expiration
to manage the lifespan of
a password

Policy enforcement

Configure password
policy enforcement
separately for each SQL
Server login

Password complexity

Use password complexity
to prevent brute force
attacks

SQL Server Cryptography Architecture

Service Master Key

Database Master Key

Symmetric Key

Asymmetric Key

Certificate

Using SQL Certificates and Asymmetric Keys


Use certificates and asymmetric

keys for asymmetric encryption




You can use certificates as
containers for asymmetric keys
and to sign code modules


You can use externally generated
certificates or certificates
generated by SQL Server


You can specify properties for the
certificates while creating them





Asymmetric keys are used for
securing symmetric keys, limited
data encryption, and digitally
signing database objects


Asymmetric keys can be imported
from strong name key files, but
they cannot be exported


Asymmetric keys do not have
expiry options


Asymmetric keys cannot encrypt
connections


Asymmetric

Keys


SQL


Certificates

Demonstration: How To Enable TDE

In this demonstration, you will see how to:


Enable TDE by using the ALTER DATABASE statement

Discussion: Securing Data by Using Encryption


How do you decide about what data to be secured by encryption?


Why would you secure an entire database by encryption?


What is data at rest?

Lab 5: Designing Security for SQL Server 2008


Exercise 1: Designing Secure Development


Exercise 2: Implementing Secure Operations


Exercise 3: Enabling Database Encryption

Estimated time:
60

minutes

Logon Information

Virtual machine

User name

Password

NYC
-
SQL1

Administrator

Pa$$w0rd

Lab Scenario

You are a lead database designer at QuantamCorp. You are working on the Human
Resources Vacation and Sick Leave Enhancement (HR VASE) project that is designed
to enhance the current HR system of your organization. This system is based on the
QuantamCorp sample database in SQL Server 2008.

The main goals of the HR VASE project are as follows:

• Provide managers with current and historical information about employee vacation
and sick leave.

• Grant view rights to individual employees to view their vacation and sick leave
balances.

• Provide permission to selected employees in the HR department to view and update
the vacation and sick leave details of employees.

• Grant the HR manager with the view and update rights to all the data.

• Ensure that the application compiles with local regulations by verifying that the
sensitive information is protected and unauthorized access to the application is
prohibited.

You are required to design a security access control policy and examine the various
aspects of secure development and operation in the SQL Server database system.

Lab Review


How can schema help to improve management of database
security?


Does configuration of TDE for a database ensure encryption of the
associated FILESTREAM data?


How do you restore an encrypted database if the certificate that
protects the database encryption key is unavailable?


How will you configure the client application to utilize TDE?

Module Review and Takeaways


Review Questions


Real
-
World Issues and Scenarios