1 / 50

SQL Server 2005 Security Enhancements

SQL Server 2005 Security Enhancements. Dr Greg Low Senior Consultant Readify greg.low@readify.net. Original version of this material was DAT330 TechEd 2004 presented by Girish Chander. Agenda. Yukon Security Features Authentication Endpoint Based Authentication

tracy
Download Presentation

SQL Server 2005 Security Enhancements

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. SQL Server 2005 Security Enhancements Dr Greg Low Senior Consultant Readify greg.low@readify.net

  2. Original version of this material was DAT330 TechEd 2004 presented by Girish Chander

  3. Agenda • Yukon Security Features • Authentication • Endpoint Based Authentication • Password Policy Enforcement • Strengthening SQL Authentication • Authorization • User-Schema Separation • Module Execution Context • Granular Permissions Control • Catalog Security • Crypto support • Data Encryption

  4. Payloads TDS SOAP SSB Database Mirroring Endpoint-Based Authentication • Endpoint: • Point of entry into an instance • Binds transport protocol to payload Transport Protocol Named pipes Shared Memory TCP HTTP VIA • For HTTP • Off by default, Endpoints need to be explicitly created • No permissions on endpoint by default

  5. Endpoint-Based Authentication (Cont) • For Other transports • Default endpoint created for every enabled transport at start up • CONNECT permissions granted to authenticated logins • Permissions can be denied on a per endpoint basis • Advantages • Transport/payload based access management • Controlled within SQL Server • Ability to start/stop/disable individual endpoints

  6. Connect to the SQL Server computer Establish login credentials; Authorize against EP Establish a database context Verify permissions for all actions SQL Server Security Model Network connection request Login authentication request to SQL Server Switch to a database and authorize access Attempt to perform some action

  7. Password Policy Enforcement • Enforcement of the following on logins • Password strength • Password expiration • Account lockouts • Follows local Windows password policy • Supports consistent enterprise wide policy • Approach • New password policy check API in Windows Server 2003 • Called during Authentication, password set and reset • On Win2K server • API not available • Only supports SQL server native password complexity

  8. Password Policy For Logins • Secure by default • Policy checked for all logins by default • Can be configured on a per login basis • Admin options on logins • Turn ON/OFF Password policy checks • Turn ON/OFF Password expiration checks • Requires Password Policy to be ON • Gives ‘password never expires’ ability • Ability to force password change on first login • Present in the sys.sql_logins catalog view ***

  9. Password Policy For Logins • During Authentication • Account lockout in case of brute force attack • Password age restrictions – if expiration set • Password Set/Change • Password complexity enforced and history checked • Client side support • Allows password change on login, similar to Windows • Ability to disable logins quickly

  10. Password Policy Enforcements

  11. Strengthening SQL Authentication • Stronger channel for authentication protocol for standard SQL logins • Channel is encrypted using SQL generated certificates • No SSL certificate loading required • Similar to current SSL implementation • Default mechanism for standard SQL logins for Yukon clients talking to Yukon server • Seamless to applications; No application change required • Support for old style authentication for down level clients

  12. Agenda • Yukon Security Features • Authentication • Endpoint Based Authentication • Password Policy Enforcement • Strengthening SQL Authentication • Authorization • User-Schema Separation • Module Execution Context • Granular Permissions Control • Catalog Security • Crypto support • Data Encryption

  13. User-Schema Separation • Separation of principals and schemas • Principal • Entities against whom objects are secured • Live in the sys.database_principals view • Schema • Container of objects; 3rd part of 4 part name • Live in the sys.schemas view

  14. User-Schema Separation (Cont) • Notion of default schema • Property of User or Application role • Used in name resolution; Look up algorithm for objects • Maintained in sys.database_principals • New DDL for user and schemas • CREATE/ALTER/DROP for USER, ROLE, and SCHEMA • Dropping user does not require application re-write

  15. Table Function View Stored Proc User-Schema SeparationThe Solution User 2 Owned by contained in Schema

  16. Select * from foo • S1. foo • Dbo.foo User-Schema SeparationThe Solution User1 Default Schema S1 User2 User3

  17. Default Schema • Used for name resolution purposes • Not all users need to own schemas • Gives the ability to share out name resolution look up across many users • DBO schema need not be the only shared schema, from name lookup perspective

  18. Default Schema (Cont) • Why is this useful? • To create objects guaranteed to hit name lookups from every context, objects need not be created in DBO schema • By having these contexts point to another schema as the default schema • Allowing creation of objects in DBO schema could pose security risks through ownership chaining • This can be mitigated using ‘lower privileged’ schemas as the default schema • ‘lower privileged’: Owned by a lower privileged account

  19. Approle1 User1 Role1 Schema1 Schema2 SP1 Fn1 Tab1 User-Schema Separation Database • Database can contain multiple schemas • Each schema has an owning principal – user or role • Each user has a default schema for name resolution • Most database objects live in schemas • Object creation inside schema requires CREATE permission and ALTER or CONTROL permission on the schema • Example: Creation of table in schema requires CREATE TABLE permission and ownership of schema or ALTER or CONTROL on schema • Ownership chaining still based on owners not schemas Hasdefaultschema Owns Owns Owns Schema3

  20. User-Schema Separation

  21. User2.Proc1 User1.T1 User 3 User1.T1 User2.Proc1 Execution Context SQL 2000 Execute Perms checked for User3 Select Perms checked for User3 User 3 User1.T1 User1.Proc1 Execute Perms checked for User3 NO Perms checked for User3 Yukon ‘Execute AS ‘X’ ’ Execute Perms checked for User3 Select Perms checked for‘X’. Not for user3

  22. Module Execution Context • Ability to choose execution context of modules • Module: Stored procs, functions, triggers • No need to rely on ownership chaining to get ‘upfront permission check’ behavior • Ownership chaining rules still apply however

  23. Module Execution Context (Cont) • Permissions checked against current execution context • Unlike Ownership chaining, applies to DDL as well • Option available for dynamic SQL as well • Alternative to the absence of ownership chaining • Execution context maintained in the sys.sql_modules catalog view

  24. Contexts • Execute AS CALLER • Statements execute as immediate calling context • Default behavior, Similar to SQL Server 2000 • Use when caller’s permission needs to be checked, Or ownership chaining will suffice • Execute AS ‘UserName’ • Statements execute as the username specified • Impersonate permission required on user specified

  25. Contexts • Execute AS SELF • Statements execute as the person specifying the execute as clause for the module • May be useful in application scenarios where calling context may change • Execute AS OWNER • Statements execute as the current owner of the module • Impersonate privileges on owner required, at setting time • On ownership change, context is new owner

  26. Using Execute As To Create Permission Buckets • Scenario • Database Admin wants to delegate the ability to truncate a set of tables each night • Problem • Truncate is not a grantable permission • Closest covering permission is ALTER, but that is too high and gives other ability

  27. Using Execute As To Create Permission Buckets (Solution) • Solution: Execute As to the rescue • Create a Proc that truncates the table • Mark it to execute as a user with ALTER permissions • Grant execute permission to the target user • Result • You’ve just made Truncate a grantable permission!

  28. Module Execution Context

  29. Granular Permissions Control • More permissions – at multiple scopes • Server, Database, Schema, Object, Principal • Principle of least privileges • Assign only required permissions to perform an action • Granular permissions enable that • Many new permission verbs added for granular control • SQL 2000 fixed roles still supported • Catalog Views • Database permissions live in sys.database_permissions view • Server permissions live in sys.server_permissions view

  30. Permission States • Three permission states • Grant gives a right • Deny explicitly denies a right • Revoke takes away an existing grant or deny Grant - Revoke Revoke [deny] + DENY Grant Deny

  31. General Permissions Scheme • Grantee • Server level permissions grantable to logins • Database level permissions grantable to users/db roles/application roles • Securable • Entity to be secured • Example: Tables, assemblies, databases, server, etc… • Same permission can be at multiple scopes • Example CONTROL on schema level and CONTROL on table within a schema • DENY at any level always take precedence

  32. General Permissions Scheme • Most securables have the following permissions • CONTROL: Owner like permissions • ALTER: Ability to change the properties of the securable. Also grants the ability to CREATE/DROP/ALTER sub entities • Example: ALTER permission on a schema allows altering the name of the schema; And also altering tables, views etc. within schema

  33. General Permissions Scheme (Cont) • ALTER ANY ‘X’: Ability to alter any object of type X • Example: ALTER ANY ASSEMBLY allows altering any assembly in the database • Take Ownership: Grants the ability to take ownership of an object

  34. New permissionsFurther Examples • Concern: I want to delegate auditing power without giving access to the entire server • Solution: ALTER TRACE • New server level permission. Sysadmin no longer required to run profiler • Concern: There is db_datareader and db_datawriter, but I want a db_procexecutor equivalent • Solution: EXECUTE (higher scope--database or schema) • Can execute any proc/function/assembly in database, or schema (if schema scoped) • Equivalent SELECT, INSERT, UPDATE, DELETE permissions introduced as well

  35. New permissionsFurther Examples • Concern: I want to be able to selectively impersonate another user, without having full database, or instance privileges • Solution: IMPERSONATE (for login and user) • No longer require sysadmin or DBO for impersonating user/login

  36. Catalog Security • System tables implemented as views: Catalog views • Metadata is secured by default • Minimal permissions to public • Catalog views are row level secured • Need to be owner or have some permission on object to see it in catalog view • SA can see everything in server • DBO can see everything in database • New permission to allow viewing of metadata • VIEW DEFINITON • Applicable at object level, schema level, database, and server level

  37. Agenda • Yukon Security Features • Authentication • Endpoint Based Authentication • Password Policy Enforcement • Strengthening SQL Authentication • Authorization • User-Schema Separation • Module Execution Context • Granular Permissions Control • Catalog Security • Crypto support • Data Encryption

  38. Encryption Support Inside DB • Encrypt and Decrypt built-ins for encryption support • Encryption built-insEncrypts cleartext and returns ciphertext *** • EncryptByKey() • EncryptByCert() • EncryptByPassphrase() • Decryption built-insDoes the reverse • Symmetric Keys and Certificates • Used for encryption/decryption • Entities inside SQL Server • Support for creation and storage in SQL • Symmetric keys and private keys always stored encrypted in SQL

  39. Symmetric Keys • Entities inside the database • CREATE SYMMETRIC KEY DDL • Used in the built-ins for encryption and decryption • Keys are always stored encrypted or in ‘closed’ state • Usage of Key requires ‘opening’ the key • OPEN KEY DDL • Involves decrypting the key • Knowledge of mechanism used to encrypt the key required • Once ‘open’, keys can be used until they are ‘closed’ or sessions is terminated • All keys are encrypted in memory • Catalog views • Sys.symmetric_keys • Sys.Open_keys

  40. Certificates • Entities inside the database • CREATE CERTIFICATE DDL • Can be loaded into SQL Server • Private key is optional • Required if signing, decryption, or authentication is needed • Can be created in SQL Server • SQL server generates the certificates • Support for ‘dumping’ certificates and private keys • Private keys always stored encrypted • Required for • Service Broker: Authentication and message integrity and secrecy • Encryption support • Securing SQL authentication natively • Module Signing

  41. Encryption Layers

  42. Database Master Key • A Key that is specific to the database • Used to secure certificate private keys inside the database • Explicitly created by owner of the database • Support for regeneration and recovery • ALTER MASTER KEY DDL • DUMP/LOAD MASTER KEY DDL • Always secured using user password • Stored in the database • Can also be secured using Service Master Key • Used when SQL server does key management • Does not rely on user password to get to key • Stored in the sysdatabases table

  43. Service Master Key • Key that is specific to the instance • Used to secure system data • Linked server passwords • Connection strings • Database Master keys • Mapped account credentials • Created upon install • Support for regeneration and recovery • ALTER SERVICE MASTER KEY DDL • DUMP/LOAD SERVICE MASTER KEY DDL • Secured using DPAPI • The service accounts credentials

  44. Encrypting Data

  45. The SQL Server 2005 University Masters • Fully accredited, masters level degree from Charles Sturt University • SQL Server 2005 MCDBA certification included as an integral component • Graduates eligible for full professional level membership of the Australian Computing Society • Part time study delivered via Distance Education • Qualifies for Government FEE-HELP program • Places available for experienced applicants without previous qualifications First intake commences September 2005Register your interest atwww.itmasters.info

  46. Technical Readiness Resources • Microsoft SQL Server 2005 Workshops • 1 or 2 day hands on workshops on each of : • Database Infrastructure • Database Development • Business Intelligence • Tour 1 Starts Mid May • Tour 2 has been added due to popular demand – starts late August

  47. Event: Microsoft SQL Server 2005 Workshops • Microsoft SQL Server 2005 Database Infrastructure & ScalabilityPresented by Brent Challis from DDLS this workshop covers Installation and upgrade options, Management tools, Architecture, Security enhancements, Scalability and performance enhancements and Maintenance enhancements. • Microsoft SQL Server 2005 DevelopmentThis intensive workshop led by Greg Low from Readify, this workshop covers the developer-related enhancements in SQL Server 2005, with a focus on T-SQL enhancements, CLR Integration, Security enhancements and ADO.NET enhancements. • Microsoft SQL Server 2005 Business IntelligencePresented by Peter Myers from Tenix Connections, this workshop covers the building of Analysis Services 2005 databases, cubes, dimensions, & aggregations, enhancements made to MDX, the new Integration Services 2005 (formerly DTS), the new data mining algorithms of Analysis Services 2005 and creating reports from Analysis Services cubes. http://www.microsoft.com/australia/events/sql2005/

  48. Microsoft SQL Server 2005 Workshops - Dates http://www.microsoft.com/australia/events/sql2005/

  49. Thanks for listening! greg.low@readify.net

  50. © 2002 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

More Related