IS 257 Fall 2006 - PowerPoint PPT Presentation

About This Presentation
Title:

IS 257 Fall 2006

Description:

University of California, Berkeley. School of Information. IS 257: ... Ticketmaster. Yahoo!. The US Census bureau. and many, many others. IS 257 - Fall 2006 ... – PowerPoint PPT presentation

Number of Views:86
Avg rating:3.0/5.0
Slides: 51
Provided by: ValuedGate70
Category:

less

Transcript and Presenter's Notes

Title: IS 257 Fall 2006


1
Database Administration Security and Integrity
  • University of California, Berkeley
  • School of Information
  • IS 257 Database Management

2
Security and Integrity Functions in Database
Administration
  • Review
  • MySQL Intro
  • Data Integrity
  • Security Management
  • Backup and Recovery

3
Security and Integrity Functions in Database
Administration
  • Review
  • MySQL Intro
  • Data Integrity
  • Security Management
  • Backup and Recovery

4
MySQL
  • The tag-line at http//www.mysql.com is
  • The world's most popular open source database
  • It is true, it is the most widely used open
    source database system with users and uses that
    range from individuals to major corporations and
    includes
  • Evite
  • Friend Finder Network
  • Friendster
  • Google (not for search though ?)
  • PriceGrabber.com
  • Ticketmaster
  • Yahoo!
  • The US Census bureau
  • and many, many others

5
MySQL myths
  • The MySQL.com web site contains a list of common
    myths and misconceptions about MySQL and refutes
    them
  • MYTH MySQL is a new, untested database
    management system
  • MYTH MySQL doesnt support transactions like
    other proprietary database engines (it is
    supposed to be in the version we use here)
  • MYTH MySQL is only for small, departmental, or
    web-based applications
  • MYTH MySQL doesnt offer enterprise-class
    features
  • MYTH MySQL doesnt have the type of support
    large corporations need
  • MYTH MySQL isnt open source any more

6
MySQL documentation
  • MySQL is available for download from MySQL.com
  • In addition that site has complete online
    documentation for the MySQL system and for the
    mysql client program in their Developer Zone
  • The online manuals are quite readable and have
    lot of examples to help you

7
MySQL Data Types
  • MySQL supports all of the standard SQL numeric
    data types. These types include the exact numeric
    data types (INTEGER, SMALLINT, DECIMAL, and
    NUMERIC), as well as the approximate numeric data
    types (FLOAT, REAL, and DOUBLE PRECISION). The
    keyword INT is a synonym for INTEGER, and the
    keyword DEC is a synonym for DECIMAL
  • Numeric (can also be declared as UNSIGNED)
  • TINYINT (1 byte)
  • SMALLINT (2 bytes)
  • MEDIUMINT (3 bytes)
  • INT (4 bytes)
  • BIGINT (8 bytes)
  • NUMERIC or DECIMAL
  • FLOAT
  • DOUBLE (or DOUBLE PRECISION)

8
MySQL Data Types
  • The date and time types for representing temporal
    values are DATETIME, DATE, TIMESTAMP, TIME, and
    YEAR. Each temporal type has a range of legal
    values, as well as a zero value that is used
    when you specify an illegal value that MySQL
    cannot represent
  • DATETIME '0000-00-00 000000'
  • DATE '0000-00-00'
  • TIMESTAMP (4.1 and up) '0000-00-00 000000'
  • TIMESTAMP (before 4.1) 00000000000000
  • TIME '000000'
  • YEAR 0000

9
MySQL Data Types
  • The string types are CHAR, VARCHAR, BINARY,
    VARBINARY, BLOB, TEXT, ENUM, and SET
  • Maximum length for CHAR and VARCHAR is 255
  • For longer things there is BLOB and TEXT

10
MySQL Data Types
  • A BLOB is a binary large object that can hold a
    variable amount of data.
  • The four BLOB types are TINYBLOB, BLOB,
    MEDIUMBLOB, and LONGBLOB. These differ only in
    the maximum length of the values they can hold
  • The four TEXT types are TINYTEXT, TEXT,
    MEDIUMTEXT, and LONGTEXT. These correspond to the
    four BLOB types and have the same maximum lengths
    and storage requirements
  • TINY1byte, BLOB and TEXT2bytes, MEDIUM3bytes,
    LONG4bytes

11
MySQL Data Types
  • BINARY and VARBINARY are like CHAR and VARCHAR
    but are intended for binary data of 255 bytes or
    less
  • ENUM is a list of values that are stored as their
    addresses in the list
  • For example, a column specified as ENUM('one',
    'two', 'three') can have any of the values shown
    here. The index of each value is also shown
  • Value Index
  • NULL NULL
  • 0
  • 'one 1
  • two 2
  • three 3
  • An enumeration can have a maximum of 65,535
    elements.

12
MySQL Data Types
  • The final string type (for this version) is a SET
  • A SET is a string object that can have zero or
    more values, each of which must be chosen from a
    list of allowed values specified when the table
    is created.
  • SET column values that consist of multiple set
    members are specified with members separated by
    commas (,)
  • For example, a column specified as SET('one',
    'two') NOT NULL can have any of these values
  • ''
  • 'one'
  • 'two'
  • 'one,two
  • A set can have up to 64 member values and is
    stored as an 8byte number

13
MySQL Demo
  • MySQL is on Dream, like ORACLE
  • Setup via My.SIMS
  • Unix command for interactive use is mysql which
    needs to include -p to be prompted for the
    password, and optionally includes your database
    name, e.g.
  • mysql ray p
  • Note that the version on Dream is not the latest
    it is currently V. 3.23.58, latest is 5.1

14
MySQL Demo
  • Since we ran out of time last week we will look
    at MySQL online today

15
Security and Integrity Functions in Database
Administration
  • Data Integrity (review)
  • Security Management
  • Backup and Recovery

16
Data Integrity
  • Intrarecord integrity (enforcing constraints on
    contents of fields, etc.)
  • Referential Integrity (enforcing the validity of
    references between records in the database)
  • Concurrency control (ensuring the validity of
    database updates in a shared multiuser
    environment)

17
Integrity Constraints (review)
  • The constraints we wish to impose in order to
    protect the database from becoming inconsistent.
  • Five types
  • Required data
  • attribute domain constraints
  • entity integrity
  • referential integrity
  • enterprise constraints

18
Required Data
  • Some attributes must always contain a value --
    they cannot have a NULL value
  • For example
  • Every employee must have a job title.
  • Every diveshop diveitem must have an order
    number and an item number

19
Attribute Domain Constraints
  • Every attribute has a domain, that is a set of
    values that are legal for it to use
  • For example
  • The domain of sex in the employee relation is M
    or F
  • Domain ranges can be used to validate input to
    the database

20
Entity Integrity
  • The primary key of any entity
  • Must be Unique
  • Cannot be NULL

21
Referential Integrity
  • A foreign key links each occurrence in a
    relation representing a child entity to the
    occurrence of the parent entity containing the
    matching candidate (usually primary) key
  • Referential Integrity means that if the foreign
    key contains a value, that value must refer to an
    existing occurrence in the parent entity
  • For example
  • Since the Order ID in the diveitem relation
    refers to a particular diveords item, that item
    must exist for referential integrity to be
    satisfied.

22
Referential Integrity
  • Referential integrity options are declared when
    tables are defined (in most systems)
  • There are many issues having to do with how
    particular referential integrity constraints are
    to be implemented to deal with insertions and
    deletions of data from the parent and child
    tables.

23
Insertion rules
  • A row should not be inserted in the referencing
    (child) table unless there already exists a
    matching entry in the referenced table
  • Inserting into the parent table should not cause
    referential integrity problems
  • Sometimes a special NULL value may be used to
    create child entries without a parent or with a
    dummy parent

24
Deletion rules
  • A row should not be deleted from the referenced
    table (parent) if there are matching rows in the
    referencing table (child)
  • Three ways to handle this
  • Restrict -- disallow the delete
  • Nullify -- reset the foreign keys in the child to
    some NULL or dummy value
  • Cascade -- Delete all rows in the child where
    there is a foreign key matching the key in the
    parent row being deleted

25
Referential Integrity
  • This can be implemented using external programs
    that access the database
  • newer databases implement executable rules or
    built-in integrity constraints (e.g. Access and
    Oracle)

26
Enterprise Constraints
  • These are business rule that may affect the
    database and the data in it
  • for example, if a manager is only permitted to
    manage 10 employees then it would violate an
    enterprise constraint to manage more

27
Data and Domain Integrity
  • This is now increasing handled by the database.
    In Oracle, for example, when defining a table you
    can specify
  • CREATE TABLE table-name (
  • attr2 attr-type NOT NULL, forbids NULL values
  • attrN attr-type CHECK (attrN UPPER(attrN)
    verifies that the data meets certain criteria
  • attrO attr-type DEFAULT default_value)
    Supplies default values

28
Referential Integrity
  • Ensures that dependent relationships in the data
    are maintained. In Oracle, for example
  • CREATE TABLE table-name (
  • attr1 attr-type PRIMARY KEY,
  • attr2 attr-type NOT NULL,
  • , attrM attr-type REFERENCES
    owner.tablename(attrname) ON DELETE CASCADE,

29
Concurrency Control
  • The goal is to support access by multiple users
    to the same data, at the same time
  • It must assure that the transactions are
    serializable and that they are isolated
  • It is intended to handle several problems in an
    uncontrolled system
  • Specifically
  • Lost updates
  • Inconsistent data states during access
  • Uncompleted (or committed) changes to data

30
No Concurrency Control Lost updates
John
Marsha
  • Read account balance (balance 1000)
  • Withdraw 200 (balance 800)
  • Write account balance (balance 800)
  • Read account balance (balance 1000)
  • Withdraw 300 (balance 700)
  • Write account balance (balance 700)

ERROR!
31
Concurrency Control Locking
  • Locking levels
  • Database
  • Table
  • Block or page
  • Record
  • Field
  • Types
  • Shared (S locks)
  • Exclusive (X locks)

32
Concurrency Control Updates with X locking
John
Marsha
  • Lock account balance
  • Read account balance (balance 1000)
  • Withdraw 200 (balance 800)
  • Write account balance (balance 800)
  • Unlock account balance
  • Read account balance (DENIED)
  • Lock account balance
  • Read account balance (balance 800)
  • etc...

33
Concurrency Control Deadlocks
John
Marsha
  • Place S lock
  • Read account balance (balance 1000)
  • Request X lock (denied)
  • wait ...
  • Place S lock
  • Read account balance (balance 1000)
  • Request X lock (denied)
  • wait...

Deadlock!
34
Concurrency Control
  • Avoiding deadlocks by maintaining tables of
    potential deadlocks and backing out one side of
    a conflicting transaction
  • Normally strict Two-Phase locking (TPL or 2PL) is
    used. It has the characteristics that
  • Strict 2PL prevents transactions from reading
    uncommitted data, overwriting uncommitted data,
    and unrepeatable reads
  • It prevents cascading rollbacks (i.e. having to
    roll back multiple transactions), since eXclusive
    locks (for write privileges) must be held until a
    transaction commits

35
Transaction Control in ORACLE
  • Transactions are sequences of SQL statements that
    ORACLE treats as a unit
  • From the users point of view a private copy of
    the database is created for the duration of the
    transaction
  • Transactions are started with SET TRANSACTION,
    followed by the SQL statements
  • Any changes made by the SQL are made permanent by
    COMMIT
  • Part or all of a transaction can be undone using
    ROLLBACK

36
Transactions in ORACLE
  • COMMIT (I.e., confirm previous transaction)
  • SET TRANSACTION READ ONLY
  • SELECT NAME, ADDRESS FROM WORKERS
  • SELECT MANAGER, ADDRESS FROM PLACES
  • COMMIT
  • Freezes the data for the user in both tables
    before either select retrieves any rows, so that
    changes that occur concurrently will not show up
  • Commits before and after ensure any uncompleted
    transactions are finish, and then release the
    frozen data when done

37
Transactions in ORACLE
  • Savepoints are places in a transaction that you
    may ROLLBACK to (called checkpoints in other
    DBMS)
  • SET TRANACTION
  • SAVEPOINT ALPHA
  • SQL STATEMENTS
  • IF (CONDITION) THEN ROLLBACK TO SAVEPOINT ALPHA
  • SAVEPOINT BETA
  • SQL STATEMENTS
  • IF
  • COMMIT

38
Security and Integrity Functions in Database
Administration
  • Data Integrity
  • Security Management
  • Backup and Recovery

39
Database Security
  • Views or restricted subschemas
  • Authorization rules to identify users and the
    actions they can perform
  • User-defined procedures (with rule systems or
    triggers) to define additional constraints or
    limitations in using the database
  • Encryption to encode sensitive data
  • Authentication schemes to positively identify a
    person attempting to gain access to the database

40
Views
  • A subset of the database presented to some set of
    users
  • SQL
  • CREATE VIEW viewname AS SELECT field1,
    field2, field3,, FROM table1, table2 WHERE
  • Note queries in Access function as views

41
Restricted Views
  • Main relation has the form

U unclassified S Secret TS Top Secret
42
Restricted Views
S-view of the data
U-view of the data
43
Authorization Rules
  • Most current DBMS permit the DBA to define
    access permissions on a table by table basis
    (at least) using the GRANT and REVOKE SQL
    commands
  • Some systems permit finer grained authorization
    (most use GRANT and REVOKE on variant views

44
Security and Integrity Functions in Database
Administration
  • Data Integrity
  • Security Management
  • Backup and Recovery

45
Database Backup and Recovery
  • Backup
  • Journaling (audit trail)
  • Checkpoint facility
  • Recovery manager

46
Disaster Recovery Planning
From Toigo Disaster Recovery Planning
47
Threats to Assets and Functions
  • Water
  • Fire
  • Power Failure
  • Mechanical breakdown or software failure
  • Accidental or deliberate destruction of hardware
    or software
  • By hackers, disgruntled employees, industrial
    saboteurs, terrorists, or others

48
Threats
  • Between 1967 and 1978 fire and water damage
    accounted for 62 of all data processing
    disasters in the U.S.
  • The water damage was sometimes caused by fighting
    fires
  • More recently improvements in fire suppression
    (e.g., Halon) for DP centers has meant that water
    is the primary danger to DP centers

49
Kinds of Records
  • Class I VITAL
  • Essential, irreplaceable or necessary to recovery
  • Class II IMPORTANT
  • Essential or important, but reproducible with
    difficulty or at extra expense
  • Class III USEFUL
  • Records whose loss would be inconvenient, but
    which are replaceable
  • Class IV NONESSENTIAL
  • Records which upon examination are found to be no
    longer necessary

50
Offsite Storage of Data
  • Early offsite storage facilities were often
    intended to survive atomic explosions
  • PRISM International directory
  • PRISM Professional Records and Information
    Services Management
  • http//www.prismintl.org/
  • Mirror sites (Hot sites)
Write a Comment
User Comments (0)
About PowerShow.com