Module 10: Auditing Data Access and Encrypting Data

Sep 26, 2016 - Organization (. Note: mode; ... a row in the dbo.Employee table. You can access the deleted tables in the trigger. .... on a periodic basis.
16MB taille 3 téléchargements 201 vues
Module 10: Auditing Data Access and Encrypting Data

1 of 44

Module

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Data

Contents:

Lesson 1: Lesson 2: Lesson 3: Lab:

Module When configuring that you meet

to ensure protection.

Organizations

which mandate

auditing of all

range of

options for implementing encryption of database files

the access to the by providing

transparent data This module

use and

manage the SQL

Objectives After completing

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

2 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

• Describe the • Implement • Manage SQL • Implement

Lesson SQL Server provides one tool provides

general, no features often needs

to be used. In this lesson,

Lesson After completing • Describe the • Describe the • Use triggers • Uses SQL

Discussion:

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

3 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

During this discussion, • Why is auditing • What methods • What are • Which standards

with?

Common

Common Criteria

nations in

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

4 of 44

1999 and has

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

is now

maintained by Organization (

Note: however, Criteria

mode; the Common

Enabling Common SQL Server provides

can be set by

using the sp_configure

Enterprise

edition for production

editions for

non-production

enabled

option, you must

Server to

comply with

can download

this script from When the option

Server

operates: • Residual Information

known bit

pattern before • Ability to • Column GRANT

behavior of the

permission

Note: performance

impact the

SQL Trace

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

5 of 44

Many users attempt commands to Profiler can have

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

tracing Server interactively on

production systems. An alternative Server Profiler lightweight method then have a role

that SQL much more Trace can the server, you

can use it to SQL Trace uses

lost, as long

as there is space

fills or write

errors occur,

enabled. The

possibility of

SQL Trace for

auditing purposes.

DML Triggers

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

6 of 44

Triggers can including data and logon triggers

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

types, modifies data logons, based on

business or administrative You can create the trigger should

specifying whether trigger

should take. The following

a row in the

dbo.Employee

table. You

can access the

deleted tables

in the trigger. Creating a DML

CREATE TRIGGER ON dbo.Employee FOR UPDATE AS BEGIN SET NOCOUNT

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

7 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

IF UPDATE( INSERT NewSalary, SELECT getdate() FROM INNER ON i. END; END; GO

Triggers do have • System performance

alongside the usual

load on the • Users with

significant issue

for auditing • You cannot • Triggers have • Only limited

that it captures

all the changes

last trigger

that fires,

procedure.

Demonstration: In this demonstration,

Demonstration Create a DML

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

8 of 44

1.

Ensure and log

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

are running, the

password 2.

In the D:

3.

Start SQL

database engine

instance 4.

Open the

5.

Select the

This creates a

table named AdventureWorks 6.

Select the trigger

This creates a Rate column

is updated, 7.

Select the

updates a

rate in 8.

Select the

This

retrieves 9.

Keep SQL

SQL Server

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

9 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

SQL Server Audit

to track

server-level and

events to

audit files or

and

Enterprise edition, database-level

Object Server Audit Server Audit Specification

One per audit.

Database Audit

per database

Actions

Action Groups

example,

For example, restore commands.

Target

Security event

Extended Events SQL Server

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

10 of 44

A wide variety user executes permissions

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

example, when a memory or check Extended Events

feature that enables

events occur.

When SQL Server

an action

that should be sends details

event and memory-based ring

buffers, or Windows® Extended Events

impact on

the database

purposes

where you may Extended Events

Extended Events

infrastructure.

particular

types of events—the

of event.

Configurations unit of deployment part of a particular

Packages are the objects that are package

within Extended Extended Events

described in the

following table:

Object Events Targets Actions

plans to

Types Predicates

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

11 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Object Maps

Lesson Preparing SQL you can create

objects before will learn how

to configure

Lesson After completing • Configure • Detail the • Define audit • Create audits. • Create server • Create database

SQL Server

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

12 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

SQL Server Audit • Audits. An target (such written, and

defines a must be space.

• Audit Specifications in an audit.

be included database audit

specifications • Actions and

specification are

based on pre-defined

Server provides a

comprehensive

groups, and you

can audit user-defined

audit

specification groups that

audit-level action when an

administrator

Creating

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

13 of 44

You can create Management

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

SQL Server key ones being

listed in the following

Option Name Queue delay

On failure

flushing them to the

fail auditable

Audit destination Maximum file Reserve disk space

Note: security avoids in a significant

between quickly and but can result

Audit Targets

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

14 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Audits can be • A file. File • Windows Application administrators logs. Do not

configure. network of the event can view the

log. • Windows Security to add the

but you need before using

it.

You should review

on a periodic

basis. The following

file as the

target: Creating a Server

CREATE SERVER TO FILE ( WITH ( GO ALTER SERVER GO

Note: server and stores

creating a automatically

Creating

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

15 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

After you create

specification by

using Transact-SQL

specification, it is

automatically

for it to start.

A server audit

grouped into

pre-defined action • BACKUP_RESTORE_GROUP: • DATABASE_CHANGE_GROUP:

database.

• FAILED_LOGIN_GROUP: • SUCCESSFUL_LOGIN_GROUP: • SERVER_OPERATION_GROUP:

Note: Action

The following

Audit

and successful

login attempts: Creating a Server

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

16 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

CREATE SERVER FOR SERVER ADD (FAILED_LOGIN_GROUP), ADD (SUCCESSFUL_LOGIN_GROUP) WITH (STATE

Creating

Creating a database

specification. The

database audit

database level.

You can audit • SELECT: • INSERT: • DELETE: • UPDATE: • EXECUTE:

Additionally, 26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

17 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

• APPLICATION_ROLE_CHANGE_PASSWORD_GROUP:

application

role password • DATABASE_OBJECT_ACCESS_GROUP:

objects.

• DATABASE_PRINCIPAL_CHANGE_GROUP:

database-level

principal is • SCHEMA_OBJECT_ACCESS_GROUP:

specified

schema. • SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP:

changes to object

permissions.

Note: Server

“SQL

The following

all database

principal changes

schema by

members of Creating a Database

USE AdventureWorks; CREATE DATABASE

AdventureWorks_DBSecurity

FOR SERVER ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), ADD (SELECT WITH (STATE

User-Defined

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

18 of 44

The server-level enable you to audit application-specific

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

provides they do not than a

specific threshold To audit custom action group

USER_DEFINED_AUDIT_GROUP procedure in your

application logic The sp_audit_write smallint to identify

log a succeeds,

and a string The following

from an insert

trigger. Calling sp_audit_write

CREATE TRIGGER AFTER INSERT AS DECLARE

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

19 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

  select @bonus from inserted   IF @bonus BEGIN     SET

bonus

is over     EXEC

@succeeded

= 1,                                       @user_defined_informat ion = @msg; END

Note: been granted the master on sys.

actions have procedure in permission

Reading

After an audit

have been

audited.

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

20 of 44

You can use

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

from audits that

target Windows

the

sys.fn_get_audit_file

file-based logs

in a specified The sys.fn_get_audit_file initial_file_name

of three

formats: • \* which • \LoginsAudit_{GUID}

specified name

and GUID • \LoginsAudit_{GUID}_00_29384.

The structure

audit file.

detailed and you

rarely need to This example Selecting Specific

SELECT event_time, database_name, FROM sys. \*',default,

Additional (Transact-SQL)

The audit records logs, as well

fn_get_audit_file

system event in size by the

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

21 of 44

rules related chunks that may generate multiple

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

000-character event can the order of

multiple row

Managing

After you have

audit

configuration Enabling and You can use

individual audit

specifications.

SERVER

AUDIT SPECIFICATION,

statements to

set the STATE The following Disabling an

Use master; ALTER SERVER

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

22 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

WITH (STATE

Viewing Audit SQL Server provides

system views

that can help

View sys.dm_server_audit_status

current state of the

sys.dm_audit_actions sys.dm_audit_class_type_map sys.server_audits

descriptions. instance.

sys.server_file_audits

a SQL Server

sys.server_audit_specifications

in a SQL

sys.server_audit_specification_details

details

sys.database_audit_specifications

specifications in a SQL

sys.database_audit_specification_details

specifications in a SQL

Considerations There are several • Each audit

server, SQL

Server attempts

on the

server. If no

executing

the CREATE

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

23 of 44

• If databases

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

same level of

audit capability, • Mirrored have a server SERVER

partner must CREATE on the

primary server. • You should

need to

minimize • If disk space entry to it

to force startup

parameter.

Demonstration: In this demonstration, • Create an • Create a server • Create a database • View audited

Demonstration Create an Audit 1.

If you did

20462C-MIA-DC and

20462C-MIA-SQL ADVENTUREWORKS\Student

Setup.cmd in

the D:\Demofiles\Mod10

Management

Studio,

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

24 of 44

2.

In SQL

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

\Demofiles

\Mod10 3.

Select the

This creates an

audit that 4.

In Object

expandable,

refresh 5.

Double-click

click

Cancel

Create a Server 1.

In SQL comment specification

2.

In Object

under the creates an audit attempts. expand it. Then

double-click

Create a Database 1.

In SQL comment audit specification principals

2.

In Object expand

under the creates an individual database. Security, and is not

expandable, 3.

Double-click

Cancel.

View Audited 1.

Open a

as 26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

25 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

ADVENTUREWORKS\ChadCorbitt ADVENTUREWORKS\Personnel

member of the

ADVENTUREWORKS\HumanResources_Users runas

2.

When prompted

3.

In the SQLCMD

sqlcmd

HumanResources. SELECT GO

4.

Close the

5.

In the D:

created.

6.

In SQL

under the

comment

the audit

folder and

events logged for

the Student 7.

Note that ADVENTUREWORKS\ChadCorbitt Server

8.

excluded).

accesses SQL individual login.

Keep SQL

Lesson With the focus often overlooked and backup tapes. compliance policies

engineers, an such as disks security two ways of 26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

26 of 44

encrypting data:

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Management

(EKM). This lesson describes

Lesson After completing • Describe the • Configure • Move an encrypted • Describe how

Transparent

Transparent Data

files

unreadable without

protection to

your database

need to

reconfigure client Server instance transparent to

because the SQL process is they are

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

27 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

encrypted; when

Note: may increase

Server

Transparent TDE uses the • Service Master

installation of the SQL

Server instance

Key for the

master database.

system Data

Protection • Database

generate a

certificate

that you

specify to

can use a

password • Server Certificate

and is used to

encrypt an • Database

encrypt the

entire database.

Configuring

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

28 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

To enable TDE, 1.

Create

2.

Create

3.

Create

4.

Enable

Creating a You create the

statement and

supplying a password. The code example Creating a DMK

USE master; CREATE MASTER

Creating a

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

29 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

After you have CERTIFICATE DMK to generate

Server uses the master

database. The code example Creating a Certificate

Use master; CREATE CERTIFICATE 'DEK_Certificate';

You should back create them.

soon as you encrypted a database

and then lost The following

private key.

Backing up

BACKUP CERTIFICATE

\backups

\security_certificate. WITH PRIVATE (FILE ENCRYPTION

Creating a The DEK is that can perform database that

two-way keys in the ENCRYPTION KEY

Transact-SQL 26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

30 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

The code example

AES_128 algorithm

in the AdventureWorks

certificate created in

the previous Creating a Database

USE AdventureWorks; CREATE DATABASE WITH ALGORITHM ENCRYPTION

Enabling Encryption After you have database by using

encryption for a SET

ENCRYPTION The Transact-SQL

database.

Enabling Encryption

ALTER DATABASE SET ENCRYPTION

To check whether

of 0 in the

is_encrypted

the

is_encrypted The code example

all databases

on the server Querying sys.

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

31 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

USE master; SELECT name,

Moving

The primary contains in the

the data it attach the files

from an encrypted If you need to associated keys

the moving a

TDE-enabled 1.

On the

2.

Copy or

3.

Create

4.

Use a CREATE on the destination

server.

server certificate and its private

key. 5.

Attach

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

32 of 44

Demonstration:

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Encryption

In this demonstration, • Create a database • Create a server • Create a database • Enable database

Demonstration Create a Database 1.

If you did

20462C-MIA-DC and

20462C-MIA-SQL ADVENTUREWORKS\Student

Setup.cmd in

the D:\Demofiles\Mod10

Management

Studio, 2.

In SQL

\Demofiles

\Mod10 3.

Select the

creates a

database

Create a Server 1.

In the TDE.

certificate and

click Execute

and its private

key.

Create a Database 1.

In the TDE.

click

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

33 of 44

Execute

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

database.

Enable Database 1.

In the TDE. Execute database

2.

Review

encryption and click retrieves database. ConfidentialDB is

1. 3.

Close SQL

Extensible

In an enterprise

managing

encryption keys

Many

organizations keys and certificates securely. Extensible register modules

encryption store keys possible to use the

encryption keys To use keys from

perform the 26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

34 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

following tasks: 1.

Enable configuration options

advanced advanced example.

sp_configure GO RECONFIGURE ; GO sp_configure GO RECONFIGURE ; GO

2.

Create

provider.

CREATE FROM

3.

Create CREATE WITH SECRET FOR

4.

Add the ALTER ADD

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

35 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

ALTER DATABASE SET ENCRYPTION

Lab: Auditing Scenario You are a database must implement must protect

reasons, you database; and you database.

Objectives After completing • Implement • Implement

Lab Setup Estimated Time: Virtual machine: User name: ADVENTUREWORKS Password: Pa$$w0rd

Exercise

Scenario The InternetSales to customer data

commitment to customer

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

36 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

data is audited. The main tasks 1.

Prepare

2.

Create

3.

Create

4.

Create

5.

Implement

6.

View Audited

Task 1: Prepare

1.

Ensure then log

running, and password

Pa$$w0rd 2.

Run Setup.

Task 2: Create

1.

Create an

2.

Enable the

Task 3: Create

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

37 of 44

1.

Create a

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

specification

should:

o

Write

o

Audit

o

Be

Task 4: Create

1.

Create a

specification

should:

o

Write

o

Audit

o

Audit

database

role. o

Audit

schema by the

sales_admin o

Be

Task 5: Implement

1.

Use the

table in the

InternetSales

in the

D:\Labfiles\Lab10\Starter CREATE FOR UPDATE AS BEGIN IF BEGIN DECLARE SET i.EmailAddress FROM

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

38 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

JOIN FOR EXEC

1,

@user_defined_information END; END; GO

2.

Grant EXECUTE

to the public

database

Task 6: View

1.

In a command ADVENTUREWORKS\VictoriaGray ADVENTUREWORKS\Sales_Europe

the

ADVENTUREWORKS\InternetSales_Users runas

2.

When prompted

3.

In sqlcmd, SELECT GO

4.

In sqlcmd,

role and update

the Customers. EXEC UPDATE

WHERE

CustomerID GO

5.

In the D:

created.

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

39 of 44

6.

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

SQL Server

the files in the

audit folder

and the service

account -- View SELECT user_defined_information, object_name FROM \*',default, WHERE 'ADVENTUREWORKS\ServiceAcct');

7.

Note that ADVENTUREWORKS\VictoriaGray through audited

Result: After and a database

Exercise

Server This identity is role.

specification,

Encryption

Scenario You want to data it contains

access to the this, you will

configure transparent The main tasks 1.

Create

2.

Enable

3.

Move the

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

40 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Task 1: Create

1.

Create a

of SQL

Server. 2.

3.

Create a

o

Name

o

Assign

Back up

o

Back

\Labfiles\Lab10

\Starter o

Back

\Labfiles\Lab10

\Starter o

4.

Encrypt

Create a

o

Use

o

Encrypt

Task 2: Enable

1.

Enable encryption

2.

Query the

HumanResources

database.

Task 3: Move

1.

Detach

2.

Attempt

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

41 of 44

o

The

o

Attaching

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

folder. database

encryption

3.

Create a

4.

Create a

instance. MIA-SQL\SQL2

instance 5.

Attach the

that you can

access the

Result: After encrypted HumanResource

moved the

Review

Module Best Practice: practices: • Choose point are • Make and

Best Practice: following • Use database. • Ensure the

following best usually no occur but environments. free disk space

the master

TDE, and store

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

42 of 44

• If you organization,

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

keys.

Review Question(

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

43 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

26/09/2016 15:52

Module 10: Auditing Data Access and Encrypting Data

44 of 44

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

26/09/2016 15:52