security & auditing on sql server 2008 r2
DESCRIPTION
Security & Auditing on SQL Server 2008 R2. Antonios Chatzipavlis Software Architect Evangelist, IT Consultant MCT, MCITP, MCPD, MCSD, MCDBA, MCSA, MCTS, MCAD, MCP, OCA MVP on SQL SERVER. Objectives. Overview of SQL Server Security Protecting the Server Scope Protecting the Database Scope - PowerPoint PPT PresentationTRANSCRIPT
Security & Auditing on SQL Server 2008 R2
Antonios ChatzipavlisSoftware Architect Evangelist, IT ConsultantMCT, MCITP, MCPD, MCSD, MCDBA, MCSA, MCTS, MCAD, MCP, OCAMVP on SQL SERVER
2
• Overview of SQL Server Security• Protecting the Server Scope• Protecting the Database Scope• Managing Keys and Certificates• Auditing Security
Objectives
3
Overview of SQL Server Security
Security & Auditing on SQL Server 2008 R2
4
• SQL Server Security Framework• What Are Principals?• What Are Securables?• SQL Server Permissions
Overview of SQL Server Security
5
Overview of SQL Server Security
6
SQL Server Security Framework
7
What Are Principals?
Server RoleSQL Server Login
Windows GroupDomain User AccountLocal User Account
SQL Server
Database
Windows
SecurablesPermissions
Principals
UserDatabase RoleApplication Role
8
What Are Securables?
Server RoleSQL Server Login
Windows GroupDomain User AccountLocal User Account
SQL Server
Database
Windows
FilesRegistry Keys
Server
Schema
Database
SecurablesPermissions
Principals
UserDatabase RoleApplication Role
9
• Server-Level Permissions• Logins• Credentials• Server-Level Roles
• Database-Level Permissions• Users• Schemas• Database Level Roles
SQL Server Permissions
10
Protecting the Server ScopeSecurity & Auditing on SQL Server 2008 R2
12
• What Are SQL Server Authentication Methods?• Password Policies• Server-Level Roles• Managing SQL Server Logins• Server-Scope Permissions
Protecting the Server Scope
13
What Are SQL Server Authentication Methods?
Windows Authentication
Mixed SQL and Windows Authentication
14
Password Policies
Group Policy Object (GPO)
Pa$$w0rd
SQL Server Can Leverage Windows Server 2003/2008 Password Policy Mechanism
SQL Server Can Manage:• Password Complexity• Password Expiration• Policy Enforcement
15
Server-Level Roles
Role Descriptionsysadmin Perform any activitydbcreator Create and alter databasesdiskadmin Manage disk filesserveradmin Configure server-wide settingssecurityadmin Manage and audit server logins
processadmin Manage SQL Server processes
bulkadmin Run the BULK INSERT statementsetupadmin Configure replication and linked servers
16
Managing SQL Server Logins
CREATE LOGIN [SERVERX\SalesDBUsers]FROM WINDOWSWITH DEFAULT_DATABASE = AdventureWorks2008
CREATE LOGIN AliceWITH Password = 'Pa$$w0rd'CREATE LOGIN login_name
{ WITH SQL_login_options | FROM WINDOWS [ WITH
windows_login_options ] }
19
Server-Scope Permissions
Server permissions
Server-scope securable permissions
USE masterGRANT ALTER ANY DATABASETO [AdventureWorks2008\Holly]
USE masterGRANT ALTERON LOGIN :: AWWebAppTO [AdventureWorks2008\Holly]
21
Protecting the Database Scope
Security & Auditing on SQL Server 2008 R2
22
• What Are Database Roles?• What Are Application Roles?• Managing Users• Special Users• Database-Scope Permissions• Schema-Scope Permissions
Protecting the Database Scope
24
What Are Database Roles?Database-Level Roles
Application-Level Roles
Users
25
What Are Application Roles?User runs
appApp connects
to db as userApp authenticates
using sp_setapproleApp assumes
app role
26
• Create a login• Create a database scope user• Assign permissions to the user
Managing Users
Steps to Manage Users
27
Special Users
DBOThe sa login and members of sysadmin role are mapped to dbo account
GuestThis user account allows logins without user accounts to access a database
28
Database-Scope Permissions
Database permissions
Database-scope securable permissions
USE AdventureWorks2008GRANT ALTER ANY USERTO HRManager
USE AdventureWorks2008GRANT SELECTON SCHEMA :: SalesTO SalesUser
29
Schema-Scope Permissions
User-defined type permissions
All other schema-scope permissions
USE AdventureWorks2008GRANT EXECUTEON TYPE :: Person.addressTypeTO SalesUser
USE AdventureWorks2008GRANT SELECTON Sales.OrderTO SalesUser
33
Managing Keys and Certificates
Security & Auditing on SQL Server 2008 R2
34
• What Are Keys?• What Are Certificates?• SQL Server Cryptography Architecture • When to Use Keys and Certificates• Transparent Data Encryption
Managing Keys and Certificates
35
What Are Keys?
• Symmetric Same key used to encrypt and decrypt
• Asymmetric Pair of values: public key and private key One encrypts, the other decrypts
Encrypt
Decrypt
36
What Are Certificates?
• Associates a public key with entity that holds that key• Contents:
The public key of the subject The identifier information of the subject The validity period Issuer identifier information The digital signature of the issuer
37
SQL Server Cryptography Architecture
38
When to Use Keys and Certificates• When to use Certificates
• To secure communication in database mirroring
• To sign packets• To encrypt data or connections
• When to use Keys• To help secure data• To sign plaintext• To secure symmetric keys
39
Transparent Data EncryptionTransparent data encryption performs real-time I/O encryption and decryption of the data and log files
• Create a master key• Create or obtain a certificate protected by the
master key• Create a database encryption key and protect it
by the Certificate• Set the database to use encryption
Steps to use Transparent Data Encryption
40
demoTransparent data encryption
41
• Entire database is protected• Applications do not need to explicitly
encrypt/decrypt data!• No restrictions with indexes or data types
(except FILESTREAM)• Performance cost is small• Backups are unusable without key• Can be used with Extensible Key Management
Transparent Database Encryption: More Benefits
42
• Very simple:• Database pages are encrypted before being written to
disk• Page protection (e.g. checksums) applied after
encryption• Page protection (e.g. checksums) checked before
decryption• Database pages are decrypted when read into memory
• When TDE is enabled, initial encryption of existing pages happens as a background process• Similar mechanism for disabling TDE• The process can be monitored using the
encryption_state column of sys.dm_database_encryption_keys
• Encryption state 2 means the background process has not completed
• Encryption state 3 means the database is fully encrypted
Transparent Data Encryption: Mechanism
43
• Create a master key• CREATE MASTER KEY ENCRYPTION BY PASSWORD =
'<UseStrongPwdHere>';• Create or obtain a certificate protected by the master key
• CREATE CERTIFICATE MyDEKCert WITH SUBJECT = 'My DEK Certificate';
• Create a database encryption key and protect it by the certificate• CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM
= AES_128 ENCRYPTION BY SERVER CERTIFICATE MyDEKCert;
• Set the database to use encryption• ALTER DATABASE MyDatabase SET ENCRYPTION ON;
Transparent Data Encryption: Enabling
44
• A backup of a TDE encrypted database is also encrypted using the database encryption key
• To restore the backup OR attach the database, the DEK must be available!• There is no way around this – if you lose the DEK, you
lose the ability to restore the backup (that’s the point!)• Maintain backups of server certificates too
Transparent Data Encryption: Backups
45
• Database | Tasks | Manage Database Encryption
Transparent Data Encryption: Tools Support
46
Auditing SecuritySecurity & Auditing on SQL Server 2008 R2
47
• What Is Auditing?• Security Auditing with Profiler• Auditing with DDL Triggers• Introducing SQL Server Audit• SQL Server Audit Action Groups and Actions
Auditing Security
48
• What is Auditing?• What auditing options are available in SQL
Server?• Have you ever had to audit SQL Server?• If so, how did you do it?• If not, what do you think is the best use of
auditing?
What Is Auditing?
49
Security Auditing with Profiler• Using SQL Server Profiler, you can do the
following:• Create a trace that is based on a reusable
template• Watch the trace results as the trace runs• Store the trace results in a table• Start, stop, pause and modify the trace
results• Replay the trace results
50
Auditing with DDL Triggers• Use DDL triggers when you want to do the
following:• Prevent certain changes in your database
schema• You want something to occur in the database
in response to a change in your database schema
• You want to record changes or events in the database schema
• Start, stop, pause and modify the trace results
• Replay the trace results
51
Introducing SQL Server Audit• SQL Server Auditing
• Tracks and logs events that occur on the system
• Can track changes on the server or database level
• Can be managed with Transact-SQL
52
demoUsing SQL Server Audit
53
Thank you!
54
Q & A
55
• For SQL Server and Databases• www.autoexec.gr/blogs/antonch
• For .NET & Visual Studio• www.dotnetzone.gr/cs/blogs/antonch
My Blogs
56