1 / 39

Trustworthy Databases

Trustworthy Databases. Jim Gray (gray@microsoft.com) Distinguished Engineer Microsoft Corporation. Agenda. Trustworthy Computing and Databases Yukon Security Yukon Availability. Systems. People. Information. Devices. Pre-Internet Walled Information Society. Systems. People.

xandy
Download Presentation

Trustworthy Databases

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. Trustworthy Databases Jim Gray (gray@microsoft.com) Distinguished Engineer Microsoft Corporation

  2. Agenda • Trustworthy Computing and Databases • Yukon Security • Yukon Availability

  3. Systems People Information Devices Pre-InternetWalled Information Society

  4. Systems People Information Devices Digital Decade:Everything is Connected

  5. The Digital Decade Requires Trustworthy Computing • Y2K, Melissa, Nimda, Code Red…caused • Gate’s Trustworthy Computing memo. • The biggest challenge the industry faces. • Moved Trustworthy Computingfrom top 10 to top 1 priority. “Trustworthy Computing is the highest priority for all the work we are doing. We must lead the industry to a whole new level of Trustworthiness in computing. “

  6. Trustworthy Computing’s Four Pillars • SecurityDefend system against attacks • PrivacyControl personal data storage and use • ReliabilitySystem always works correctly • Business integrityBe clear, open, respectful, and responsive to customers and to public

  7. Trustworthy Database Pillars Security • Data fortresses within software and services • Bugs hit code and data • Simple security model Privacy • Clear and simple information use policies • Enable fine-grain control of data usage Availability • Online operation / evolution • Self-healing automatic-recovery

  8. Trustworthy Database Tenets • Secure by Design • Secure and robust code • Threat analysis and testing • Secure by Default • Default configuration is a secure system • Discourage insecure configurations • Minimize attack surface • install only necessary components • Secure by Deployment • Principle of least privilege • Grant minimal permission required to function • Low privileged service accounts • Security assessment / admin wizards • Automate / Assist software maintenance

  9. Agenda • Trustworthy Computing and Databases • Yukon Security • Yukon Availability

  10. Security Push3 Months Dedicated to Security • Preparation • Goal: Full team (800) productive from start • Identify Components • Complete threat models • Complete Education • Security plan • Reps from all teams • Set up tools • Infrastructure set up • Push • Review 5M+ LOC • Two releases in service • One release in dev • 100% team focus • Dev, Test, PM, & UE • No non-security work • Three pronged: • Targeted code reviews • Audit tools • Threat driven testing • Exit Criteria: • SQL2K: all security bugs fixed • Yukon: all sec bugs registered • Exit • New mentality • All security bugs fixed • Ship SP3 • Much larger test suite • Swat team continues • Black hat team • Post-mortem on all customer found security bugs • Testing: • Run at low privileged • Complete coverage in min privilege • Audit tools • Better test and deployment tools.

  11. Lockdown: Results and lessons learned • SQL Server 2000 Service Pack 3 • More secure code bases • Development process improvements • Security conscious designs and code • Mandatory security focused code reviews • Accountability of code/design • Development tools • Baked in to build process • Implementation lockdown won’t suffice • Need to help users secure systems (more later) • Need better documentation and tools (more later) • Yukon design changes

  12. Secure By Design: Yukon • Row Level Security • Can secure sets of rows • Generalizes view mechanism • Predicate Restricts table subset • Leverages SQL query optimizer • Leverages column/table permissions • Basis for privacy policy • Fine gain access control • Basis for catalog security

  13. Secure By Design: Yukon • Catalog and Metadata security • Minimal public permissions • Prevents information disclosure • System tables are catalog views • row level secured • Object metadata only visible to: • Owner • Principals with permission on object • User / Schema separation • Separation of principal and schema • App still works when owner changes

  14. Secure By Design: Yukon • Granular permissions • More permissions at multiple scopes • Principle of least privilege • e.g., No need to be sysadmin to run profiler • Password policy • Consistent policy across enterprise • Enforcement of • Password Strength • Password Expiration • Account lockout

  15. Secure By Default • SQL Server 2000 SP3 • Require strong SA password on upgrade • Tighter permissions on Stored Procedures • Cross DB Ownership Chaining lockdown • MSX account not auto-generated SQL login • MSDB off network by default • Yukon • Domain password policies enforced by default • Metadata secured by default • SA password required in all Auth modes • Stronger authentication protocol for SQL logins

  16. Secure Deployment: Helping Administrators Secure Systems • Secure Code samples and guidance • In all documentation and books • Doc team was part of security push • Best security practices in all documentation • Security Best Practices checklist • Security Tools • For assessing security • For vulnerability detection • For patching systems

  17. Secure Deployment: Security Tools: MBSA • Microsoft Baseline Security Analyzer • Verify current configuration security • Local and remote scans • Windows, • IIS, • Exchange, • SQL server • More in future • Graphical and scriptable

  18. Secure Deployment: The Slammer lesson: short-term response • Response Team working 24 X 7 • Tool improvements continue • SQL Critical Update • Geared towards easier application of patches • SQL Scan • Scans vulnerable instances in domain (or IP range) • Can optionally disable instances • SQL Check • Scans all vulnerable instances on local box • Can optionally disable bad services • Software Update Service (SUS)

  19. Secure Deployment: Moving forward • Increased focus on • deploymentand • vulnerability assessment tools • Tighter integration with MBSA • Microsoft update model • Allow publish / subscription model • Allow publish / distributor / subscribe model • Cover all software (not just OS) • Document knowledge and experience

  20. Trustworthy DatabaseSQL 2000 And Yukon • Secure by Design • Secure and robust code • Threat analysis and testing • Secure by Default • Default configuration is a secure system • Minimize attack surface • Secure by Deployment • Principle of least privilege • Automate / Assist software maintenance • Good tools for security assessment / admin

  21. Agenda • Trustworthy Computing and Databases • Yukon Security • Yukon Availability Features

  22. MTTF MTTR Availability • Available: Correctly services requests within specified time • Availability • Un-availability: • MTTF and MTTR equally important

  23. MTTF MTTR AvailabilityReducing outages • Quality: Design, code-read, test, … • Secure by design, default, deployment • Online operations: Many outages are operations tasks • Online password change • Online (re)-org • Online index build/drop/reorg • Partitions for bulk operation • Simpler operations: Many outages are mistakes • More wizards (pre-scripted) • Better scripting

  24. MTTF MTTR AvailabilityReducing outages • Online Index build • Build index in background (table always up)(also works for rebuild/drop) • No table lock – good for foreign keys too • Snapshot Isolation and Viewpoints • Snapshot Isolation: Read-consistent scans • Readers don’t wait for writers • Ephemeral • Viewpoint: Read-only copy of DB • Persistent

  25. change Old value How ViewPoints Work • Looks like a new Read-Only database • Very cheap to create • ViewPoint uses Copy On Write to make snapshot of the DB • Can be used for reporting • Can recover to ViewPoint – fat finger delete Data Base Viewpoint: Page Directory

  26. Table T Table T F A B C D E F Online bulk Data LoadPartitions • Partition Tables + Indices by hash / range • Transparent to applications • Can add, drop, split, merge partitionsin seconds. • Create new filegroup F • Create empty version of the table F.T (no keys) • Heap load F.T • Index F.T • Now add F.T to online table T (takes a second)

  27. Recovery Redo committed Undo incomplete xactions MTTF MTTR AvailabilityImproving repair • Backup improvements • All datatypes (full text indices, cubes, filestreams, ...) • Optional multiple copies and checking • Filegroup granularity for simple recovery • Restore/recovery improvements • Online: Page, file, filegroup granularity • “Instant” file format (must be admin) • Much shorter outage at software upgrade • FASTER: Just redo

  28. MTTF MTTR Availability: Clusters • Cluster Failover • Shipped with SQL 7 • Much improved in SQL 2000 • Yukon adds 8-pack support • Robust • Widely deployed • First-line-of-defense

  29. Availability: Mirrored Systems • How do you deal with catastrophe • Fire, flood, storm, earthquake, power/net breakdown? • Data center move • Sabotage, Gremlins? • Cluster failover can take minutes • What if I want 5 -9s? (5 minutes/year) • Mirrored SystemsTwo independent systems • Replicas of one another • Continuous SQL log shipping • Mirror tracks primary • Witness breaks ties • Easy to configure and admin Witness Primary Mirror MTTF MTTR

  30. Witness Primary Mirror Witness Witness FailedPrimary RepairedPrimary New Primary New Primary Witness Primary Mirror Database Mirroring • Database failover – Instant standby • Very fast … < 3 seconds • Automatic or manual • Automatic re-sync after failover • Automatic and transparent client redirect • No single point of failure • No special hardware; standard computers and storage • Minimal impact to transaction throughput

  31. SQL Server SQL Server How Database Mirroring Works • Transaction Log Shipping – 2-safe • Backup system in continuous redo • Database “up” when redo completes Primary Mirror Witness Application Log records Redo Recovery Ack! Log Log

  32. Database Mirroring Mark Wistrom Program Manager Microsoft Corporation

  33. Viewpoint2 @ 2PM Viewpoint1 @ Noon Reporting On Mirror • Use Database ViewPoint on Mirror OLTP Clients Witness Primary Mirror Reporting Client

  34. Spectrum Of Technologies Replication • Maximize availability for • Scale out • Offload primary server • Heavy Reporting • Mobile/disconnected users • Autonomous data sharing • Maximize availability • System-of-record databases • Zero data loss – current info • Mask downtime • Planned • Unplanned Replication Disaster Tolerant Rock Solid Site Mirror Cluster

  35. Mirror Spectrum Used In Combination • Can mix and match Cluster, Mirror, Replica Failover Cluster Replication Failover Cluster

  36. MTTF MTTR Availability Summary • Deep analysis of availability, holistic approach • Fewer outages • Online operations • Simpler operations • Snapshots, partitions, … • Faster repair • More complete backup/recovery • Finer grain (so faster) recovery • Mirrored systems

  37. Summary / Call To Action • Trustworthy Computing is more than security • Yukon improvesTrustworthy Database support • Secure by design / default / deployment • Deploy SQL Server 2000 today as the stepping stone to Yukon • Keep current and safe with the tools at www.microsoft.com/sqland www.microsoft.com/security

  38. © 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