Transcript Secure Data Access with SQL Server 2005

Secure Data Access with
SQL Server 2005
Doug Rees
Associate Technologist, CM Group
[email protected]
Agenda
Introduction
Secure by Default
Password Policy and Authentication
User-Schema Separation and
Object-Name Resolution
Granular Permissions
Execution Context
CLR Integration
Encryption
Introduction – Why Worry?
Data is your most valuable asset
Attacks can come from both external and
internal sources
Insecure access of your data can lead to disaster
for your business:
Tampering with data
Information disclosure
SQL Server 2005 introduces several new
features to help keep your data secure
Secure By Default
Installation defaults to secure state if no setup
options are changed
Choose components for installation (none by default)
Select user account details for services
Windows authentication is default authentication
mode
Access to many resources must be explicitly
enabled or granted before being used
SQL Server Surface Area Configuration utility
Permissions
Password Policy And Authentication
SQL Server 2005 can inherit Windows Server 2003
Password Policy
Policy can be enabled or disabled on a per login basis
Logins can be enabled and disabled for temporary
removal
Login protocol uses secure channel for SQL accounts
Uses SQL Server generated certificate if necessary
No SSL certificate loading is required
Password Policy and
Login Management
User-Schema Separation
Objects are associated with a schema not a user
Allows removal of user without changing object
names in database or client applications
Marketing
HR
Category
Employee
(Server.AdventureWorks.Marketing.Category)
(Server.AdventureWorks.HR.Employee)
dbo
Audit
(Server.AdventureWorks.dbo.Audit)
Users can be assigned a default schema
Object-Name Resolution
Marketing
Dave
(Default schema = Marketing)
SELECT * FROM Category
Category
HR
SELECT * FROM Marketing.Category
SELECT * FROM Audit
dbo
SELECT * FROM Category
Jane
(Default schema = HR)
Audit
Granular Permissions
Permissions
Can be applied to three scopes
Can have one of three states
GRANT
DENY
REVOKE
Server
Database
Schema
Catalog security blocks access to system tables allowing
access only using views
User Schemas and
Permissions
Execution Context
EXECUTE AS changes execution context
CALLER
'USER'
SELF
OWNER
: based on the caller's context (default)
: a specific user account
: account that created/modified the module
: current owner of the module
Restricted to current database context by
default
Establish a trust relationship to extend impersonation
to other databases
How EXECUTE AS Works
Jane
(No permissions)
Dave
(SELECT permission)
Audit
(Owner: dbo)
GetAuditLog
Jane
(EXECUTE permission)
GetAuditLog
(Owner: Dave)
Dave
Audit
CREATE PROC GetAuditLog WITH EXECUTE AS 'Dave'
AS SELECT * FROM dbo.Audit
EXECUTE AS and
Trust Relationships
CLR Integration
SQL Server 2005 integrates managed assemblies
inside the database engine
Three security options:
SAFE: Default setting
EXTERNAL_ACCESS: access to external resources etc.
UNSAFE: unrestricted internal and external access!!!
SAFE
EXTERNAL_ACCESS
File
UNSAFE
Unmanaged Code
CLR Integration
Encryption
Built in support for
encryption and decryption
Allows secure storage of
data within the database
Keys can be secured within
or external to SQL Server
Supports:
Symmetric
Asymmetric
Encryption by paraphrase
Certificates
Service master key
Database master key
Asymmetric
key
Symmetric key
Certificate
Encryption
Summary
SQL Server 2005 is secure by default
Password policies can be enforced
Schemas allow user-object separation
Permissions allow strong control over
objects
EXECUTE AS changes execution context
Use .NET assemblies with care
In-built encryption protects your data
Additional Resources
SQL Server 2005 Security
http://msdn.microsoft.com/sql/learning/security/default.aspx
Microsoft SQL Server 2005 Upgrade Advisor
http://www.microsoft.com/downloads/details.aspx?familyid=45
1FBF81-AB07-4CCB-A18B-DA38F6BCF484&displaylang=en
SQL Nuggets
http://www.microsoft.com/uk/msdn/events/nuggets.aspx
SQL Server 2005 Webcasts
Introduction to Security in SQL Server 2005
Securing Your Data with SQL Server 2005 Encryption
Encryption and Key Management Using SQL Server 2005
Efficiently Using the SQL Server Execution Context in
Applications
http://www.microsoft.com/events
© 2006 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.