Database Security and Recovery Concepts

1
Database Security and Recovery Concepts

• David Nelson
• CAT
• January 2004

2
Contents

• Database Security
• Countermeasures
• Security Controls
• Statistical Databases
• SQL Facilities
• Risk Analysis
• Data Protection and Privacy
• Web Database Security Issues and Solutions
• Transaction Processing and Recovery
• Transactions
• Concurrency
• Recovery Facilities and Techniques
• Advanced Transaction Models
• OO Aspects

3
Database Security

• Definition
• The protection of the database against
  intentional or unintentional threats using
  computer-based or non-computer-based controls
• Areas in which to reduce risk
• theft and fraud
• loss of confidentiality
• loss of privacy
• loss of integrity
• loss of availability

4
Countermeasures - Computer Based Controls

• Security of a DBMS is only as good as the OS
• Computer based Security controls available
• authorization and authentication
• views
• backup and recovery
• integrity
• encryption
• associated procedures
• e.g. backup, auditing, testing, upgrading, virus
  checking

5
Non-computer based Controls

• Include
• Security policy and contingency plan
• personnel controls
• secure positioning of equipment
• escrow agreements
• maintenance agreements
• physical access controls
• Both internal and external

6
Data Security

• Two broad approaches to data security
• Discretionary access control
• a given user has different access rights
  (privileges) on different objects
• Flexible, but limited to which rights users can
  have on an object
• Mandatory access control
• each data object is labelled with a certain
  classification level
• each user is given a certain clearance level
• rigid, hierarchic

7
Statistical Databases

• A database that permits queries that derive
  aggregated information (e.g. sums, averages)
• but not queries that derive individual
  information
• Tracking
• possible to make inferences from legal queries to
  deduce answers to illegal ones
• e.g. WITH (STATS WHERE SEXM AND OCCUPATION
  Programmer) AS X COUNT(X)
• WITH (STATS WHERE SEX M AND OCCUPATION
  Programmer AS X SUM(X,SALARY)

8
Statistical Databases

• Various strategies can be used to minimize
  problem
• prevent queries from operating on only a few
  database entries
• swap attribute values among tuples
• randomly add in additional entries
• use only a random sample
• maintain history of query results and reject
  queries that use a high number of records
  identical to previous queries

9
SQL Facilities

• SQL-92 supports discretionary access control
  using view mechanism and authorization system
• e.g. CREATE VIEW S_NINE_TO_FIVE AS
• SELECT S.S, S.SNAME, S.STATUS, S.CITY
• FROM S
• WHERE CURRENT_TIME gt TIME 090000
• AND CURRENT_TIME lt TIME 170000
• GRANT SELECT, UPDATE (STATUS)
• ON S_NINE_TO_FIVE
• TO Purchasing
• parameterised view

10
Data Protection and Privacy

• Privacy
• concerns the right of an individual not to have
  personal information collected, stored and
  disclosed either willfully or indiscriminately
• Data Protection Act
• the protection of personal data from unlawful
  acquisition, storage and disclosure, and the
  provision of the necessary safeguards to avoid
  the destruction or corruption of the legitimate
  data held
• New Freedom of Information Act

11
Web Database Security Issues

• Internet is an open network
• traffic can easily be monitored, e.g. credit card
  numbers
• Challenge is to ensure that information conforms
  to
• privacy, integrity, authenticity,
  non-fabrication, non-repudiation
• Information also needs protected on web server
• Also need to protect from executable content

12
Web Database Security Solutions

• Various methods can be used
• proxy servers
• improve performance and filter requests
• firewalls
• prevents unauthorised access to/from a private
  network
• digital certificates
• electronic message attachments to verify that
  user is authentic
• kerberos
• centralised security server for all data and
  resources on network

13
Web Database Security Solutions

• Secure Sockets Layer and Secure HTTP
• SSL - secure connection between client and server
• S-HTTP - individual messages transmitted securely
• Secure Electronic Transactions
• certificates which splits transactions so that
  only relevant information is provided to each
  user
• Java - Java Virtual Machine (JVM)
• class loader - checks applications do not violate
  system integrity by checking class hierarchies
• bytecode verifier - verify that code will not
  crash or violate system integrity
• ActiveX -
• uses digital signatures, user is responsible for
  security

14
Database Recovery

• Three mutually dependent functions that a DBMS
  must provide to ensure that database is reliable
  and remains in a consistent state
• concurrency control services
• prevent database accesses from interfering with
  each other
• recovery services
• restoring a database to a correct state following
  a failure
• transaction support
• central to concurrency control and recovery

15
Transaction Support

• An action or series of actions carried out by a
  single user or application program which accesses
  or changes contents of the database
• A logical unit of work on the database
• may be an entire program, part of a program, or a
  single command
• transforms the database from one consistent state
  to another
• one of two outcomes
• committed or aborted

16
ACID Transactions

• The properties that all transactions should
  possess
• Atomicity
• Consistency
• Isolation
• Durability

17
Concurrency Control

• Managing simultaneous operations on the database
  without interference
• Potential problems
• lost update
• uncommitted dependency
• inconsistent analysis
• Transactions must be serializable and recoverable

18
Concurrency Techniques

• Two basic concurrency control approaches
• Conservative (pessimistic)
• locking
• most widely used approach, best known is
  Two-phase locking
• timestamping
• ordering transactions such that older
  transactions get priority in the event of a
  conflict
• Optimistic
• conflict is rare
• allow transactions to proceed unsynchronized
• check for conflicts at end when transaction
  commits

19
Database Recovery

• Restoring a database to a correct state in the
  event of a failure
• Transactions are the basic unit of recovery
• transaction manager has to ensure that either all
  affects of a transaction are permanently
  recorded, or none
• If transaction fails then use
• rollforward (redo logs)
• rollback (undo logs)

20
Recovery Facilities

• DBMS should provide the following facilities
• backup mechanism
• logging facilities
• checkpoint facility
• recovery manager
• Techniques for recovery
• deferred update
• immediate update
• shadow paging

21
Advanced Transaction Models

• Traditional business applications characterised
  by
• simple nature of data
• short duration transactions
• More complex applications (e.g. CAD, CAM, CASE)
  have longer duration transactions accessing a
  larger number of data items

22
Nested Transaction Model (1981)

• A transaction is a
• hierarchy of sub-transactions
• only leaf-level transactions can perform database
  operations
• must commit bottom-up
• Closed nested transaction
• abort at one level does not affect a transaction
  in progress at a higher level
• savepoints emulate nested transactions
• identifiable point in a flat transaction
  representing some partially consistent state

T1
T2
T5
T6
T4
T3
23
Other Transaction Models

• Sagas
• A sequence of (flat) transactions which can be
  interleaved with other transactions
• Uses compensating transactions
• Open Nested Transaction

24
Object-Oriented Aspects

• Locking in commercial systems is at record level
• In OO, lock at object level
• Also need to lock class hierarchies
• when locking a class, do not allow locking of any
  superclasses
• queries require non-modification of any object in
  class hierarchy
• Support versioning for both objects and schema

25
Further Reading

• Connolly and Begg, chapters 18 and 19
• Date (7th edition), chapters 14 to 16
• both Connolly and Date have general introductions
  to recovery, transactions and security concepts,
  with mention of some advanced features
• Kim, Introduction to Object Databases, chapter 11
• describes some advanced transaction features for
  OO systems, mostly in relation to ORION system