Database Security Chapter 2

Database Similarities

Read consistency: Depends on database ability to process and commit transactions in a timely manner, and also applies the locking mechanisms of Transactions, Concurrency, Locks, Commit, and Undo

Resource Manager

Receives request for memory initiated by an execution and looks to the buffer manager to fulfill request.

SMON

Recovers the database in case of failure by using redo logs as well as database files

Instance

Refers to the background processes and structured memory used during interaction with a database To create, user must connect to a database and establish a session

Object-Relational Database

Relational database with expanded group of data types, middle ground between relational and object-oriented database management systems.

Database Models

Representation of the way data is stored and determines how data is retrieved and manipulated

Storage Manager

Requests data from the secondary storage and passes to the buffer management

The relational engine

Responsible for query processing and data retrieval

Flat Model

Two-dimensional list of data entries, all data within a field are similar, all data within a record are related to one another, and similar to a sign in sheet at a doctor's office

Database buffer cache

Use to cache information read from the data files as well as recently used SQL queries

Library cache

Used to cache metadata information

Query Steps

User initiates query Query request received by MySQL server Query parser creates treelike structure of extracted SQL statements Data definition language provides access Memory cache stores recently requested queries

Buffer Management

accesses data pages and updates the database

Optimizing

Process of locating most efficient way to retrieve requested data

Data file

Contains actual database data and holds information for all logical structures in the database

Query caching

Queries cached into buffer area to increase speed of future query returns

Shared pool

Store the most recent executed SQL statements and data definitions. Contains the library cache and data dictionary cache

redo log buffer

Stores all changes that have been made to the database

java pool

Stores and caches java commands

Redo log

Contains information about all changes made to database data Can be used to restore lost data - Good practice to make a duplicate copy of this file

Control file

Contains location and credentialing information of other files Database will not run if control file fails

Relationships

Define association between entities and bind them together

Database buffer cache (Cont)

Stores blocks of table and database data that has been retrieved in the past

Isolation

Transaction runs separated from other transactions and are not view able until committed

Consistency

Transactions do not affect the state of the database

Durability

Transactions persist despite system failures

attribute

A characteristic or variable that describes or further identifies an entity

Database

A collection of data stored on a computer using a database management system

alternate key

A field with values that are not chosen as a primary key, but can be used in cases where the primary key is not available

buffer manager

A portion of the SQL server responsible for accessing data pages and updating the database

Buffer Manager

Allocates memory and determines the number of buffers to allot to resource manager, Uses storage manager to negotiated with secondary storage

Object-Oriented Databases

Allow storing and retrieving of objects and complex tables such as CAD files, Artificial intelligence objects, XML-compatible objects, and general multimedia.

Column

Also known as field, Contains a general category of information with a similar data types

Row

Also known as record or tuple, and holds distinct units of data

Parsing

Analyzing query construction for correct syntax and semantics

DBMS

Application that allows others to search stored data, To provide means to manipulate, analyze, store, and retrieve information

Database Management Systems

Applications that provides means to manipulate analyze, and query data, most are used for relational databases.

Query Engine

Architecture component that optimizes and manages queries and SQL statements Built to use resources efficiently

ACID

Atomicity, Consistency, Isolation, and Durability

The Physical Structure

Files required with every Oracle install - Datafile, control file, and redo log - Files interact with OS - Transparent to the user

Tables

Basic unit of storage within a database, represents unique and specific data objects, and composed of vertical columns and horizontal rows

Steps to retrieve data after query processing

Buffer manager accesses database files from hard disk and places in buffer cache pages - Data read from the cache - Page considered dirty if changes made to data while in buffer cache

Data dictionary cache

Caches recently used data dictionary information

System Global Area (SGA)

Central storage area for all shared data and processes - Holds control data for one single instance in Oracle - Oracle 11g uses dynamic SGA

Process Global Area (PGA)

Central storage area for background and server processes - Allocates space for each individual background process - Content varies depending on Oracle configuration

PMON

Cleans up after processes complete or processes fail

Relational Database

Common entities are stored within separate tables, tables given unique name and tables use unique key identifiers to build a relationships among entities.

The Storage Engine

Components that read and write data to and from the database Customization options available Administrators can choose which storage engines to use for certain tables or applications

Storage Management

Process of storing and retrieving data throughout the database, most work takes place within many memory, and three-tired process uses resource manager, buffer manager, and storage manager.

Other names for OLAP/DSS

Data warehouse and Data repository

Oracle

Database Management System, Advantages: Portable, Can run on almost any operating system, and dominant role in providing business solutions

MySQL

Database Management System, Most popular for open source database server today Advantages: Speed, Open source, can be customized, and platform independent

Microsoft SQL

Database Management System, Primary query languages are T-SQL and ANSI SQL. Advantages: Scalability, meets needs of any Windows enviroment

Online transaction processing

Database created for real-time storage and manipulation of data within an organization, created to be used in an active environment, optimized to serve thousands of user simultaneously, stores data resulting from large volumes of short transactions

Network Model

Developed as a solution to one-to-many restrictive nature of hierarchical database model, tree like structure using tiers and parent-child-like entities to represent relationships. Parent refereed to as a set of which child entities are members.

One-to-many

Entity has a sole relationship with entity that has several relationships

Many-to-Many

Entity has one or more partnerships with another entity that also has one or many partnerships

Thread

Execution running independently from other processes

Primary Key

Field containing a unique label identifying a record or row in a table. each table has a least one of these, and key should be meaningful to the data being stored. EX: employee ID number, Social Security number

Foreign key

Field within a table containing a label used to build a relationship between two tables, and often refers to a unique entry or primary key in a different table

Streams pool

For advance queuing

Database portion of an Oracle server

Holds database files that environment needs to run Oracle database -Files help configure the instance, process SQL executions, and ensure alert and recovery from software and hardware failure

Process

Instruction set executed by OS to complete a task • Tasks required to complete an instance in Oracle - User runs application tool to request connection to Oracle server (user process) - Server handles user's request and runs process to create instance and complete the connection (server process) • Server processes can be shared or dedicated

Advantages of many-to-many relationship

Less resource intensive and easier to navigate

The Memory Structure

Main memory and cache - Quickest accessible storage in any system • In Oracle, nearly everything happens from main memory - Oracle can reliably service many users concurrently

Database Connection Manager

Manages connections to the MySQL server Virtually any client may connect

The storage engine

Manages files, memory, recovery, logging, and transactions

Application programming interfaces

Many programming languages are supported, also TCP/IP is most common type of connection

Point of sales system

Meant to handle cash register or sales transaction

Disadvantages of Flat Model

Multiple efforts, Redundant data, System allows a large margin of error. Entries must be made in exactly the same way or query will not return complete results

Transaction Manager

MySQL transaction Group of MySQL queries treated as a single process Transaction manager maintains concurrency throughout the database Ensures simultaneous data handling will not corrupt data

Online analytical processing or decision support system

OLAP/DSS, stores large volumes of historical data, used for report generating and analyzing, typically retrieves data from an OLTP, Data analyzed in a business environment to meet a specific need.

Atomicity

Operate together as one entity group or alone as one entity; a group passes and/or fails as one process

Entity

Person, place, or thing stored in a database table, has attributes and relationship

Worker processes

Pools of either threads or fibers for all user connections - Number of threads or fibers within one worker process available depends on network size

Hierarchical model

Popular in 1960's through the 1970's, Uses tiers and parent-to-child relationships to represent records and relationships. One-to-many approach greatly minimizes redundancy and model builds relationships within one stem Note: No direct relationships made across the tree

SQL query

SELECT title FROM songs, artists, WHERE songs.artists=groups.ID AND groups. Name= 'Madonna'

Queries

Searches initiated by users to retrieve information from the database, consists of sets of variables or keywords formatted in this language, and displays information in a report EX: SQL is a query language used in this text

Other Keys

Secondary or alternative key Candidate key Composite key Sort or control key Alternate key

Key

Single field or group of fields used to identify an entry in a table, and used to access or manipulate records or rows within a relational database

Query Management

Steps taken by a database management application to process a user query, which allows queries to be processed individually or in a parallel

Large pool

Store large jobs to avoid filling the shared pool

Virtual memory

Technique for extending memory availability - Units of storage from different memory devices appear as a single block of storage - Fixed units of storage referred to as pages

buffer pool (buffer cache)

The area where data pages from a database are stored to minimize the need to read and write from the database file located on the hard disk

DBWN (database writer)

Write changes from the database buffers to the database files

LGWR (log writer)

Write mods from the redo log buffer to the redo log files

CKPT

Writes to the control file's established commit points where recovery begins, if necessary

Relationship

defines association between two entities

Attribute

describing characteristics

One-to-one

most simple, rarely found in relational database