Module 6: Securing SQL Server Yukon

1
1.4 ???????
2
Overview

• SQL Server 2005 Security Overview
• Managing SQL Server 2005 Security
• Managing Permissions
• Managing Certificates

3
Lesson SQL Server 2005 Security Overview

• Security Enhancements in SQL Server 2005
• What Are Principals?
• What Are Securables?
• What Are Permissions?
• Multimedia SQL Server 2005 Security Architecture

4
Security Enhancements in SQL Server 2005

• Password policy for SQL Server logins

• Hierarchical security scopes

• Separation of user and schema

• Limited metadata visibility

• Declarative execution context

5
What Are Principals?
Securables
Permissions
Principals
Windows Group
Domain User Account
Local User Account
Windows
SQL Server Login
Server Role
SQL Server
User
Database Role
Application Role
Group
Database
6
What Are Securables?
Securables
Permissions
Principals
Windows Group
Files
Domain User Account
Registry Keys
Local User Account
Windows
SQL Server Login
Server
Server Role
SQL Server
Database
User
Schema
Database Role
Application Role
Group
Database
7
What Are Permissions?
Securables
Permissions
Principals
Windows Group
Files
ACL
Domain User Account
Registry Keys
Local User Account
Windows
GRANT/REVOKE/DENY
CREATE ALTER DROP CONTROL CONNECT SELECT EXECUTE U
PDATE DELETE INSERT TAKE OWNERSHIP VIEW
DEFINITION BACKUP
SQL Server Login
Server
Server Role
SQL Server
Database
User
Schema
Database Role
Application Role
Group
Database
8
Lesson Managing SQL Server 2005 Security

• How to Manage Logins
• Demonstration Managing Logins
• How to Manage Users
• Demonstration Managing Users
• What Are Schemas?
• How to Manage Schemas
• How to Set a Users Default Schema
• Demonstration Managing Schemas
• How to Control Execution Context
• Demonstration Controlling Execution Context

9
How to Manage Logins

10
Demonstration Managing Logins

• In this demonstration, you will see how to
• Create Windows logins
• Create SQL Server logins
• Enforce password policy

11
How to Manage Users

12
Demonstration Managing Users

• In this demonstration, you will see how to create
  users

13
What Are Schemas?
Namespaces for database objects
dbo
Products
(Server1.AdventureWorks.dbo.Products)
SalesData
Orders
(Server1.AdventureWorks.SalesData.Orders)
14
How to Manage Schemas

15
How to Set a Users Default Schema
Name Resolution
dbo
SELECT FROM Orders
Products
SELECT FROM Products
sales
SELECT FROM Orders
SELECT FROM Products
Orders
Assigning a default schema
ALTER USER Ted WITH DEFAULT_SCHEMA sales
16
Demonstration Managing Schemas

• In this demonstration, you will see how to
• Create a schema
• Assign a default schema

17
How to Control Execution Context

CREATE PROCEDURE GetOrders WITH EXECUTE AS
CALLER AS SELECT FROM sales.orders
CREATE PROCEDURE GetOrders WITH EXECUTE AS
SELF AS SELECT FROM sales.orders
CREATE PROCEDURE GetOrders WITH EXECUTE AS
'Rose' AS SELECT FROM sales.orders
18
Demonstration Controlling Execution Context

• In this demonstration, you will see how to use
  the EXECUTE AS clause

19
Lesson Managing Permissions

• Understanding Permissions
• How to Manage Server Permissions
• How to Manage Server-Scope Securable Permissions
• How to Manage Database Permissions
• How to Manage Database-Scope Securable
  Permissions
• How to Manage Schema-Scope Securable Permissions
• Demonstration Managing Permissions

20
Lesson Managing Certificates

• What Are Certificates?
• How Does SQL Server Use Certificates?
• How to Manage Certificates
• Demonstration Managing Certificates

21
What Are Certificates?

• Digitally signed document containing a
  public/private key pair
• Obtained from
• Certificate authority
• Certificate server
• Used for
• Authentication a message signed by the private
  key is guaranteed to be sent by the owner of the
  certificate
• Encryption a message encrypted with a public
  key can only be decrypted with the matching
  private key, and vice versa

22
How Does SQL Server Use Certificates?

• Certificate server new Transact-SQL statements
• Service Broker
• Authenticated communications
• Encrypted messages
• Web synchronization for merge replication
• Replication over HTTP
• Uses SSL and digital certificates

23
How to Manage Certificates

• Creating certificates

CREATE CERTIFICATE AWCert WITH SUBJECT
'CertificateForAdventureWorks' ,
ENCRYPTION_PASSWORD 'P_at_ssw0rd'

• Exporting certificates

DUMP CERTIFICATE AWCert TO FILE 'C\MyCert.cer'

• Deleting certificates

DROP CERTIFICATE AWCert