Transcript The Ins and Outs of Secure Data Access

The Ins and Outs of Secure
Data Access
Jørgen Thyme
Developer & Platform Strategy Group, Microsoft
[email protected]
www.rolighed.net/weblog
Today's Agenda
Best practices for building well-designed, secure,
data-driven, smart client applications
 Session 1: Designing and building smart clients
Patterns and practices for smart clients, IssueVision
 Session 2: Securing smart client applications
Tips for secure data, CAS, encryption, and more
Session 3: The ins and outs of secure data
access
Best practices for smart client data, offline data
Session 4: Deploying and maintaining smart
clients
Tips for deploying and updating apps to avoid “DLL
Hell”
Session Agenda
A look at data In IssueVision
Drill-Down: Design choices for secure data
Summary: Best practices for secure data
Drill Down:
Design Choices for Secure
Data
Stored procedures
Database credentials
DataSet vs. custom objects
Concurrency
Input validation
Retrieve data via
Web services
Offline Data
Stored Procedures
Many benefits to stored procedures
Higher performance for complex queries
More secure
Can reduce network traffic
Batch SQL commands, encapsulate transactions
Easier to maintain than prepared queries
Encapsulates DB design
Security Tips:
Parameterize queries and stored procedures
Helps avoid SQL injection attacks
Grant access only to stored procedures, not the
data tables
Application or User Credentials
Application-level credentials
+ Better performance
Takes advantage of ADO.NET connection pooling
+ Easier to implement
– No per-user permissions or auditing in database
Per-user credentials
+ Fine-grained audit and permissions in database
– Can’t use connection pooling
– Impersonation must be relayed through each
application tier
DataSet vs. Custom Object
Custom data objects
Developer has full control over implementation
Can be more lightweight than DataSet
Ideal for single-instance, non-tabular data
<Serializable()> Public Class UserSettings
Private m_username As String
Private m_password As String
Public Property Username() As String
Get
Return m_username
End Get
Set(ByVal Value As String)
m_username = Value
End Set
End Property ...
DataSets and ADO.NET
ADO.NET: Separates data access from
working with data
DataSet
DataTable
•Relational (tabular) model
•Highest performance
•Fully exposes database functionality
•Can pass to between tiers
SQLXML in
ADO.NET
•Interop format for the Web – B2B, A2A
•Sparse (semi-structured) data
•XML Services e.g. XQuery, XSD
ObjectSpaces
•Coming in Whidbey timeframe!
•Business objects with relational mapping
Drill Down:
Design Choices for Secure
Data
 Stored procedures
 Database credentials
 DataSet vs. custom objects
Concurrency
Input validation
Retrieve data via
Web services
Offline Data
Managing Concurrency
Concurrency is the policy by which updates from
multiple users to the same data are managed
Optimistic concurrency
Looks for changes to original data before updating
Best choice for most Smart Client applications
“Last In Wins”
Last update overwrites all previous updates
Scales well, but provides no assurance of data integrity
Pessimistic concurrency
Locks the record for a single user
Highest assurance of data integrity
Scales poorly, negatively impacts performance
Tools for Optimistic Concurrency
Data Adapter Configuration Wizard
Saves a lot of coding effort when using DataSet
Generates DAL component and optimistically
concurrent CRUD stored procedures
Optionally, a typed dataset for the selected
tables
Reconciling Offline Changes
DataAdapter.Update() methods
HasChanges(), GetChanges(), DiffGram
Supports ADO.NET transactions
Best for:
Single database applications, Web services data
SQL Merge Replication
Replicate local MSDE data to a central server
Uses resolver logic to handle conflicts
Best for:
Reconciling large numbers of conflicts
Batch updates of “branch office” data
Tip: Offline application block
Provide loose coupling between components.
Abstract the management of connection state
from the application.
Provide the same programming model for the
application in both online and offline
Provide extensible interfaces for capabilities
such as connection detection, and queuing.
Incorporate design patterns.
Optimistic Concurrency &
Handling Conflicts
Part 1 – Concurrency Wizard / Connect to data
Optimistic Concurrency &
Handling Conflicts
Part 2 – Updating data
Optimistic Concurrency &
Handling Conflicts
Part 3 – Detecting and Resolving Conflicts
Drill Down:
Design Choices for Secure
Data




Stored procedures
Database credentials
DataSet vs. custom objects
Concurrency
Input validation
Retrieve data via
Web services
Offline Data
Ensuring Valid User Input
Client validation
Handle OnRowChanging, OnColumnChanging
Encapsulates validation with the data
Good for cross-field validation within one row of
data
Server validation
Always re-validate client input on the server
Tip: validate input in business logic tier
Alternative: SQL triggers and SQL-DDL
Reduces DB traffic, but requires special skills
Retrieve Data Via a Web Service
Batch data operations on client
Subject class does the work
DataSet excels as data + state
format
Data access and business logic
reside in the app server tier
Tips for service-based data:
Limit transaction boundaries to the
app server and database
Simpler, more maintainable logic
Invoke services asynchronously for
best client UI responsiveness
Retrieve Data From a
Web Service
Drill Down:
Design Choices for Secure
Data






Stored procedures
Database credentials
DataSet vs. custom objects
Concurrency
Input validation
Retrieve data via
Web services
Offline Data
When Offline Data Makes Sense
Offline data makes sense for…
Truly offline work
In the field, or on an airplane–not wireless!
Low-contention, easily-partitioned data
Example: my email—vs. all the email on the server
Modifying small amounts of data
Tip:
Limit the size of in-memory datasets for good
client performance
Under 2MB for most applications
Where to Store Offline Data
Environment.SpecialFolder.LocalApplicationData
Best choice for non-document data
Company Name, Product Name, Version
Isolated Storage
Viable alternative for partial-trust applications
Secure from other internet apps
{userfiles}\My Documents
Only document data
Tip:
Never store user data to Program Files!
Forces application to run with Admin privileges
Large Amounts of Offline Data
Use MSDE
Royalty-free desktop SQL engine
Downside: additional app to deploy to client
Best for…
Reference data, like product catalog
Working with large amounts of data
“Branch Office” databases
SQL Server Merge Replication replicates local
MSDE data to a central SQL Server
Requires LAN or VPN connection
Best Practices for Secure Data
Use stored procedures, if possible
Grant access to the stored procedures only
Use optimistic concurrency
For disconnected data, consider using
DataSet
Includes nice support for reconciling offline
changes
Store offline data in LocalApplicationData
Consider MSDE for large amounts of offline data
Resources
Data Patterns and Practices
msdn.microsoft.com/practices/type/Patterns/Data
Data Access Overview
msdn.microsoft.com/vbasic/using/understandin
g/data
© 2003-2004 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.