slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Trustworthy Databases PowerPoint Presentation
Download Presentation
Trustworthy Databases

Loading in 2 Seconds...

play fullscreen
1 / 39

Trustworthy Databases - PowerPoint PPT Presentation


  • 132 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Trustworthy Databases' - xandy


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
trustworthy databases

Trustworthy Databases

Jim Gray (gray@microsoft.com)

Distinguished Engineer

Microsoft Corporation

agenda
Agenda
  • Trustworthy Computing and Databases
  • Yukon Security
  • Yukon Availability
the digital decade requires trustworthy computing
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. “

trustworthy computing s four pillars
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
trustworthy database pillars
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
trustworthy database tenets
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
agenda1
Agenda
  • Trustworthy Computing and Databases
  • Yukon Security
  • Yukon Availability
security push 3 months dedicated to security
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.
lockdown results and lessons learned
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
secure by design yukon
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
secure by design yukon1
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
secure by design yukon2
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
secure by default
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
secure deployment helping administrators secure systems
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
secure deployment security tools mbsa
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
secure deployment the slammer lesson short term response
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)
secure deployment moving forward
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
trustworthy database sql 2000 and yukon
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
agenda2
Agenda
  • Trustworthy Computing and Databases
  • Yukon Security
  • Yukon Availability Features
availability

MTTF

MTTR

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

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
availability reducing outages1

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
how viewpoints work

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

online bulk data load partitions

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)
availability improving repair

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
availability clusters

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
availability mirrored systems
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

database mirroring

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
how database mirroring works

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

mark wistrom program manager microsoft corporation

Database Mirroring

Mark Wistrom

Program Manager

Microsoft Corporation

reporting on mirror

Viewpoint2

@ 2PM

Viewpoint1

@ Noon

Reporting On Mirror
  • Use Database ViewPoint on Mirror

OLTP Clients

Witness

Primary

Mirror

Reporting Client

spectrum of technologies
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

spectrum used in combination

Mirror

Spectrum Used In Combination
  • Can mix and match Cluster, Mirror, Replica

Failover Cluster

Replication

Failover Cluster

availability summary

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
summary call to action
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
slide39

© 2002 Microsoft Corporation. All rights reserved.

This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.