1、Module 10Auditing Data Access and Encrypting DataModule OverviewAuditing Data Access in SQL ServerImplementing SQL Server AuditEncrypting DatabasesLesson 1: Auditing Data Access in SQL ServerDiscussion: Auditing Data AccessCommon Criteria AuditingSQL TraceDML TriggersDemonstration: Using DML Trigger

2、s for AuditingSQL Server AuditDiscussion: Auditing Data AccessWhy is auditing required?What methods have you used for auditing?What are the limitations of the methods you have used?Which standards that require auditing does your organization need to comply with?Common Criteria AuditingCommon Criteri

3、a Compliance:Ratified as an international standard in 1999Supersedes C2 ratingISO standard 15408Enable common criteria compliance enabled configuration option by using sp_configure:Residual Information Protection (RIP)Ability to view login statisticsColumn GRANT does not override table DENYSQL Trace

4、SQL Server Profiler is used to trace commands sent to the server and errors returned:Can be heavy on resourcesIs run interactivelyCan trace command executionsSQL Trace:A set of system stored procedures that enable you to create tracesCan be used from within applicationsRelatively lightweight when we

5、ll-filteredDML TriggersTriggers can provide part of an auditing solution:DML triggers for data modificationLogon triggers for tracking logonsLimitations:Performance impactAbility to disable triggersLack of SELECT triggersTrigger nesting issuesComplexities around trigger firing orderDemonstration: Us

6、ing DML Triggers for AuditingIn this demonstration, you will see how to:Create a DML trigger for auditingSQL Server AuditEvent tracking and logging system based on Extended EventsComprised of:AuditsAudit specificationsActions and action groupsTargetsLesson 2: Implementing SQL Server AuditSQL Server

7、Audit OverviewCreating an AuditCreating a Server Audit SpecificationCreating Database Audit SpecificationsUser-Defined Audit ActionsReading Audited EventsManaging SQL Server AuditDemonstration: Using SQL Server AuditSQL Server Audit OverviewAudit: Where and how events are loggedAudit Specification:

8、A set of events to be logged in an auditActions and Action Groups: Events that can be included in an audit specificationAction Groups/ ActionsAudit SpecificationAuditCreating an AuditSpecify:TargetQueue delayAction on failureSet STATE = ON to enableCREATE SERVER AUDIT SecurityAuditTO FILE (FILEPATH

9、= MIA-SQLAuditFiles ,MAXSIZE = 0 MB ,MAX_ROLLOVER_FILES = 2147483647 ,RESERVE_DISK_SPACE = OFF)WITH(QUEUE_DELAY = 1000 ,ON_FAILURE = FAIL_OPERATION);GOALTER SERVER AUDIT SecurityAuditWITH (STATE = ON);Creating a Server Audit SpecificationSpecify:AuditAction groups to be includedStateCREATE SERVER AU

10、DIT SPECIFICATION AuditLoginsFOR SERVER AUDIT SecurityAuditADD (FAILED_LOGIN_GROUP),ADD (SUCCESSFUL_LOGIN_GROUP)WITH (STATE = ON);Creating Database Audit SpecificationsSpecify:AuditAction GroupsActions on specific securable by specific principalsStateUSE AdventureWorks;CREATE DATABASE AUDIT SPECIFIC

11、ATION AdventureWorks_DBSecurityFOR SERVER AUDIT SecurityAuditADD (DATABASE_PRINCIPAL_CHANGE_GROUP),ADD (SELECT ON SCHEMA:HumanResources BY db_datareader)WITH (STATE = ON);User-Defined Audit ActionsEnable you to audit custom events:Add USER_DEFINED_AUDIT_GROUP to an audit specificationCall sp_audit_w

12、rite from a stored procedure or triggerCREATE TRIGGER HR.BonusChecker ON HR.EmployeeBonusAFTER INSERTASDECLARE bonus money, empid integer, msg nvarchar(4000) select bonus = i.Bonus, empid = i.EmployeeIDfrom inserted i IF bonus 1000BEGIN SET msg = Employee + CAST(empid as varchar(50) + bonus is over

13、$1000 EXEC sp_audit_write user_defined_event_id = 12, succeeded = 1, user_defined_information = msg;ENDReading Audited EventsUse Event Viewer to view Windows event logsRetrieve file-based audits by using the sys.fn_get_audit_file functionSELECT event_time, object_id, server_principal_name, database_

14、name, schema_name, object_name, statement FROMsys.fn_get_audit_file(MIA-SQLAuditFiles*, default, default);Managing SQL Server AuditEnable or disable audits by setting STATEView audit configuration details in DMVsAudit considerations include:Restoring or attaching a database may result in a mismatche

15、d GUIDAttaching a database to a different edition of SQL Server may result in the audit not runningMirrored servers may result in mismatched GUIDsAuditing a large number of events can cause performance issuesFailure during audit can cause SQL Server to fail to startDemonstration: Using SQL Server Au

16、ditIn this demonstration, you will see how to:Create an auditCreate a server audit specificationCreate a database audit specificationView audited eventsLesson 3: Encrypting DatabasesTransparent Data Encryption OverviewConfiguring Transparent Data EncryptionMoving Encrypted DatabasesDemonstration: Im

17、plementing Transparent Data EncryptionExtensible Key ManagementTransparent Data Encryption OverviewService Master Key (SMK)Created during SQL Server installationEncrypted by Windows DPAPIDatabase Master Key (DMK)Created in master databaseEncrypted by SMKServer CertificateCreated in master databaseEn

18、crypted by DMKDatabase Encryption Key (DEK)Created in user databaseEncrypted by server certificateDPAPI (Windows)masterUser DBSMKmaster DMKServercertificateDEKConfiguring Transparent Data EncryptionCreate a DMK in master databaseCREATE MASTER KEY ENCRYPTION BY PASSWORD = Pa$w0rd;Create a Server Cert

19、ificate in master databaseCREATE CERTIFICATE Security_CertificateWITH SUBJECT = DEK_Certificate;Create a DEK in the user databaseCREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128ENCRYPTION BY SERVER CERTIFICATE Security_Certificate;Enable encryption for the user databaseALTER DATABASE Adventur

20、eWorks SET ENCRYPTION ON;Moving Encrypted DatabasesDetach the source databaseCopy/move the database filesCreate new DMK in the master databaseGenerate a new server certificate from a backup of the original server certificate and its private keyAttach the databaseDemonstration: Implementing Transpare

21、nt Data EncryptionIn this demonstration, you will see how to:Create a database master keyCreate a server certificateCreate a database encryption keyEnable database encryptionExtensible Key ManagementEKM enables encryption keys to be stored securely in third-party hardware security modules (HSMs)Requires additional SQL Server configuration:The EKM provider enabled option must be onCredentials must be created to enable SQL Server to access keys in the HSMLab: Auditing D