Download
securing sql server 2005 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Securing SQL Server 2005 PowerPoint Presentation
Download Presentation
Securing SQL Server 2005

Securing SQL Server 2005

179 Vues Download Presentation
Télécharger la présentation

Securing SQL Server 2005

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Securing SQL Server 2005 Anil Desai

  2. Speaker Information • Anil Desai • Independent consultant (Austin, TX) • Author of several SQL Server books • Instructor, “Implementing and Managing SQL Server 2005” (Keystone Learning) • Info: http://AnilDesai.net or Anil@AnilDesai.net

  3. Agenda and Outline • SQL Server Security Architecture • Configuring Service Accounts • Managing Logins and Server Permissions • Database Users and Roles • Managing Permissions • Other Security Features • Encryption • DDL Triggers (Auditing) • Monitoring Security with SQL Profiler

  4. SQL Server Security Overview • Layered Security Model: • Windows Level • SQL Server Level • Database • Schemas (for database objects) • Terminology: • Principals • Securables • Permissions • Scopes and Inheritance

  5. Security Overview • (from Microsoft SQL Server 2005 Books Online)

  6. Security Best Practices • Make security a part of your standard process • Use the principle of least privilege • Implement defense-in-depth (layered security) • Enable only required services and features • Regularly review security settings • Educate users about the importance of security • Define security roles based on business rules

  7. SQL Server Service Accounts • Local Service Account • Permissions of “Users” group (limited) • No network authentication • Network Service Account • Permissions of Users group • Network authentication with Computer account • Domain User Accounts • Adds network access for cross-server functionality

  8. Instance-Specific(one service per instance): SQL Server SQL Server Agent Analysis Services Reporting Services Full-Text Search Instance-unaware Notification Services Integration Services SQL Server Browser SQL Server Active Directory Helper SQL Writer SQL Server Services

  9. SQL Server Surface Area Configuration • Default installation: Minimal services • SAC for Services and Connections • Allow Remote Connections • Access to Reporting Services, SSIS, etc. • SAC for Features • Remote queries • .NET CLR Integration • Database Mail • xp_cmdshell

  10. Managing Logins • Windows Logins • Authentication/Policy managed by Windows • SQL Server Logins • Managed by SQL Server • Based on Windows policies • Password Policy Options: • HASHED (pw is already hashed) • MUST_CHANGE • CHECK_EXPIRATION • CHECK_POLICY

  11. Creating Logins • Transact-SQL • CREATE LOGIN statement • Replaces sp_AddLogin and sp_GrantLogin • SQL Server Logins • Windows Logins • SQL Server Management Studio • Setting server authentication options • Login Auditing • Managing Logins

  12. Managing Server Roles • Built-In Server-Level Roles: • SysAdmin • ServerAdmin • SetupAdmin • SecurityAdmin • ProcessAdmin • DiskAdmin • DBCreator • BulkAdmin

  13. Database Users and Roles • Database Users • Logins map to database users • Database Roles • Users can belong to multiple roles • Guest (does not require a user account) • dbo (Server sysadmin users) • Application Roles • Used to support application code

  14. Database Roles • Built-in Database Roles: • db_accessadmin • db_BackupOperation • db_DataReader • db_DataWriter • db_DDLAdmin • db_DenyDataReader • db_DenyDataWriter • db_Owner • db_SecurityAdmin • public

  15. Creating Database Users and Roles • CREATE USER • Replaces sp_AddUser and sp_GrantDBAccess • Can specify a default schema • Managed with ALTER USER and DROP USER • CREATE ROLE • Default owner is creator of the role • SQL Server Management Studio • Working with Users and Roles

  16. Understanding Database Schemas • Schemas • Logical collection of related database objects • Part of full object name: • Server.Database.Schema.Object • Default schema is “dbo” • Managing Schemas • CREATE, ALTER, DROP SCHEMA • SQL Server Management Studio • Can assign default schemes to database users: • WITH DEFAULT_SCHEMA ‘SchemaName’

  17. Configuring Permissions • Scopes of Securables • Server • Database • Schema • Objects • Permission Settings: • GRANT • REVOKE • DENY • Options • WITH GRANT OPTION • AS (Sets permissions using another user or role)

  18. Managing Execution Permissions • Transact-SQL Code can run under a specific execution context • By default, will execute as the caller • EXECUTE AS clause: • Defined when creating an object or procedure • Options: • CALLER (Default) • SELF: Object creator • Specified database username

  19. Getting Security Information • Procedures and Functions • sys.fn_builtin_permissions • Has_Perms_By_Name • CURRENT_USER • SETUSER • IS_MEMBER • SUSER_NAME / SUSER_ID • SCHEMA_NAME

  20. Security Catalog Views • Sys.Server_Permissions • Sys.Server_Principals • Sys.Database_Permissions • Sys.Database_Principals • Sys.Database_Role_Members

  21. Other Security Options • Encrypting Object Definitions • Use the WITH ENCRYPTION Clause • Stores the object definition in an encrypted format • SQL Server Agent • Proxies based on subsystems allow lock-down by job step types • Preventing SQL Injection attacks

  22. Understanding Encryption • Goals: • Authentication • Data Encryption • Symmetric Encryption • Uses a single key • Asymmetric Encryption • Uses a “key-pair” • Public key: Can be distributed • Private key: Stored securely • Certificates protect the public key

  23. Understanding SQL Server Certificates • Uses of Certificates • Data encryption • Service Broker endpoints • Digital signatures for objects • Web / HTTP connections • SQL Server Certificates • Stored within user databases • Notes: • Encryption overhead can be significant • Keys must be protected

  24. SQL Server Encryption • Encryption Hierarchy • Windows Level • Stores Service Master Key • SQL Server Level • Service Master Key • Database Master Key • Certificates • Asymmetric Keys • Encrypted Objects / Data (varbinary)

  25. Encryption Hierarchy • (from Microsoft SQL Server 2005 Books Online)

  26. Managing Certificates • Transact-SQL commands: • CREATE CERTIFICATE • Stored within the user database • Can use a password or a file • BACKUP CERTIFICATE • Exports to a file • DROP CERTIFICATE • Encrypting Data: • EncryptBy_____ (Cert, SymmetricKey, Passphrase, etc.) • DecryptBy_____()

  27. Getting Certificate Information • Information • Sys.Certificates • Sys.Asymmetric_Keys • Sys.Symmetric_Keys • Cert_ID returns the certificate identifier • CertProperty(Cert_ID, ‘PropertyName’) • Start_Date, Expiry_Date, etc.

  28. DDL Triggers • Respond to Data Definition Language (DDL) commands • Examples: • DROP_TABLE • ALTER_TABLE • CREATE_LOGIN • Purpose: • Preventing certain changes • Logging / sending notifications of schema changes • Can rollback changes

  29. DDL Triggers • DDL triggers do not generate inserted/deleted tables • Getting Details • EVENTDATA function returns details of the changes in XML format • Can query with XQUERY expressions • DDL Trigger Scope: • Database-Level (stored within the same database) • Server-Level (stored in the master database)

  30. Monitoring Security with SQL Profiler • Options: • Log to a trace file or to a table • Run programmatically using SQL Trace SP’s • “Security Auditing” Event Class • Audit Login / Audit Logoff • Audit Add DB User Event • Audit Addlogin Event

  31. SQL Profiler: Security Auditing

  32. For More Information • Resources from Anil Desai • Web Site (http://AnilDesai.net) • E-Mail: Anil@AnilDesai.net • Keystone Learning Course: “Microsoft SQL Server 2005: Implementation and Maintenance (Exam 70-431)” • The Rational Guide to Managing Microsoft Virtual Server 2005 • The Rational Guide to Scripting Microsoft Virtual Server 2005