Database Integrity and Security - George Mason University

candlewhynotData Management

Jan 31, 2013 (4 years and 4 months ago)

183 views

Database Integrity and Security

HAP 709


Healthcare Databases

George Mason University

Janusz Wojtusiak, PhD

Fall, 2010


Goal


This lecture covers two important topics in
databases


How to ensure correctness of a database?


How to ensure security of a database?

Part 1: Integrity

Database Integrity


Constraints

require database to have specific
properties


Database is in
consistent state

if it satisfies all
constraints


Consistent database

is a database in
consistent state.

Example

ID

Name

Age

243

Smith

4

445

Jones

2345

7453

Brown

74

Patient table:

Visit ID

Patient

Date

1

243

11/20/09

2

500

1/1/86

3

7453

4

243

5/7/02

Visit table:

Example

PatientID

Name

DateOfBirth

Age

1

Smith

11/20/77

33

2

Brown

1/1/86

30

3

Lee

12

4

Smith

5/7/99

11

Patient table (as of 2010):

Example

ID

Company

Total

1

AAAA

LLC

$200

2

ABC

$40

3

SDS

$2000

Order table:

Item ID

Order ID

Cost

1

1

$200

2

1

$500

1

2

$40

1

3

$2000

Item table:

Reasons of Lost Integrity


Computer failure


Hardware error


Software error


Network error


Computation failure


Overflow, logical error, division by zero, etc.


User error


Concurrency control failure


Multiple users updating the same records at the same
time.

Recovery from Failure


Catastrophic failure


Data is lost due to storage error


Recover from backup


Non
-
catastrophic failure


Reverse/fix only changes that made database
inconsistent


Redo operations using log (very slow)


Sometimes requires checking what is the correct
state, checkpoints


Adding redundancy to database.

Preventing Inconsistency


Constraints supported by RDBMS


Most RDBMS allow database designers to create
constraints that prevent inserting incorrect data.


Transactions


Transactions are used to make sure that a set of
operations preserves integrity.

Preventing Inconsistency


Consider the following table

Patient ID

Measurement Date

Temperature

A2234564

May 1

9.2

A2234564

May 3

99.6

A2234564

May 4

37.7

A2234564

May 5

101.6


This type of error can be very easily detected by
checking reasonable range of temperature!

Constraints in SQL


NOT NULL


A field must contain a value


UNIQUE


Two or more records cannot have the same values
for specified fields


PRIMARY KEY


Combination of the above


FOREIGN KEY


Value of a field must match value in other table.


Constraints in SQL


CHECK


Enter specified conditions for validity of values


E.g. discounted price < price, Age > 0, …


It is possible to create a DOMAIN


Define a set of possible values


Use when there are several fields with the same
domain



Constraints may not capture full correctness of
database.

Transactions


Transaction is a set of operations that
preserve consistency of database







Transactions provide


Coherent sets of operations that can be used for
recovery


Isolation between concurrent accesses to
database.

Begin

Operation 1

Operation n

Commit

Rollback

Part 2: Security

Security in Databases


Users should be able to access or modify data
they are allowed to


Users should not be able to access or modify
data they are not allowed to.

Security in Databases


Security policy specifies what users are
allowed to do


Security mechanism enforces the policy


Mandatory Access Control


based on system
-
wide policies that cannot be changed by individual
users


Discretionary access control


creator of an object
gets full rights to it, and can assign rights to
others.

Security in SQL


Giving Permissions


GRANT command is used to provide others
with permissions

GRANT <privileges> ON <object> TO <users>
[WITH <options>]


Users can grant rights to select, update, insert,
delete, and so on


Users can also grant all privileges.


Security in SQL


Removing
Permissions


REVOKE command is used remove
permissions to database

REVOKE <permission> ON <object> FROM
<users>

Security in SQL
-

Views


Views are an easy way to provide users with
access to aggregated or some data, not entire
table(s)


Views are “stored select queries” that can have
their own permissions

CREATE VIEW <name> AS <query>


Example:

CREATE VIEW patients AS

SELECT * PERSON

WHERE
p_type

= ‘Patient’;

Examples in
PostgreSQL