Securing SQL Server 2005

1
Securing SQL Server 2005
2
Overview

• 1. SQL Server 2005 Security Overview
• 2. Managing SQL Server 2005 Security
• 3. Managing Permissions
• 4. Managing Certificates

3
1. SQL Server 2005 Security Overview

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

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
2. Managing SQL Server 2005 Security

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

9
How to Manage Logins

10
How to Manage Users

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

13
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
14
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
15
3. Managing Permissions

• Understanding Permissions
• Server Permissions
• Server-Scope Securable Permissions
• Database Permissions
• Database-Scope Securable Permissions
• Schema-Scope Securable Permissions

16
Understanding Permissions

• Assign permissions using
• Object Explorer in SQL Server Management Studio
• Transact-SQL
• GRANTREVOKEDENY
• Can perform action if
• Permission granted to the principal or a
  collection containing principal
• - AND -
• Permission not denied to the principal or a
  collection containing principal

17
Server Permissions
In Object Explorer
USE master GRANT ALTER ANY DATABASE TO
SERVERX\Bill
18
Server-Scope Securable Permissions

• Server-scope securables
• HTTP endpoints
• Certificates

USE master GRANT CONNECT ON HTTP ENDPOINT
AWWebService TO SERVERX\Bill
19
Database Permissions
In Object Explorer
Connect to server
1
Right-click database and click Properties
2
Permissions page
3
Assign permissions
4
USE AdventureWorks GRANT ALTER ANY USER TO Ted
20
Database-Scope Securable Permissions
In Object Explorer
Connect to server
1
Expand to object
2
Right-click object and click Properties
3
Permissions page
4
Assign permissions
5
USE AdventureWorks GRANT SELECT ON SCHEMA
sales TO Ted
21
Schema-Scope Securable Permissions
In Object Explorer
Connect to server
1
Expand to object
2
Right-click object and click Properties
3
Permissions page
4
Assign permissions
5
USE AdventureWorks GRANT EXECUTE ON TYPE
addressType TO Ted
USE AdventureWorks GRANT SELECT ON
sales.orders TO Ted
22
4. Managing Certificates

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

23
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

24
How Does SQL Server Use Certificates?

• Certificate server new Transact-SQL statements
• Service Broker
• Authenticated communications
• Encrypted messages
• Code authentication
• Sign SQL Server objects (such as stored
  procedures)
• Data encryption
• Encrypt and decrypt data with a certificate

25
Managing Certificates

• Creating certificates

CREATE CERTIFICATE AWCert ENCRYPTION BY
PASSWORD 'P_at_ssw0rd' WITH SUBJECT
'CertificateForAdventureWorks'

• Exporting certificates

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

• Deleting certificates

DROP CERTIFICATE AWCert