Database NEEDS

Tie in with
Software Engineering:
	Available reliable safe secure resilient
	Network security OS system administration

Transaction Processing
	Transactions are units (logical and indivisible	
	Single user versus multiple user
		Multiprogramming
		Concurrency
		Interleaved
		Multiple or single CPU
	Insert delete modify retrieve
	Read versus read-write
	Buffer
	X=X+1
Problems:
	Lost update problem
	Dirty read
	Incorrect summary
	Unrepeatable read
Failure and recovery
	Computer failure
	Transaction error
	System error
	Local error
	Exception
	Concurrency enforcement
	Disk failure
	Catastrophe
System log
	Track everything
	Sequential append only (magnetic tape)
	Non-volatile
	Used for rollback
	Used for redo
	Also useful for audits
COMMIT point
	Is done and no longer tentative
	Begin-transaction end-transaction
Transactions should be
	Atomic –need recovery
	Consistency preservation
	Isolation
	Durability

Recovery
	Total ordering of all transactions
	Conflicts
		Different transactions can:
		Access the same data
		At least one is a write
		Changing the ordering changes the result
	Complete schedule
		Every operation ends with commit or abort
		Order in schedule same as order in appearance
		For conflict, one must appear before the other
		Has no active operations at the end of the schedule
		Committed projection—only operations that are committed or aborted
			(current but not pending)
	Disaster
		Off site backup
		Catch up with log
	Minor disruptions
		Return to most recent consistent state
		Sometimes use backup
		May undo
	   Deferred update
		Before update, put in log
		After commit put in DB
		Ideally never need an UNDO
	   Immediate update
		Go ahead and put in DB
		If rollback, MUST UNDO
	Common to cache disk blocks
		Dirty bit, pinned bit, in place updating
		Shadowing—keep extra copy of any record not committed
			Takes space
		Write ahead logging
	Steal versus no steal
		Write before change committed
	Force/no force
		Rollback can be cascading
		Most DB avoid cascading rollback
		READ does not change

Security
Issues of rights to access
Policy about public information
Level of security
Database is PART of a larger system with security

Goals of security:
Integrity: no one changes data (without permission)
Availability: available to use (denial of service attacks)
Confidentiality: keeps private information secure

DBMS have security systems
	Mandatory
	Discretionary

Access control
	Passwords and accounts
	Can track access
Inference control
	Statistical
	Infer information about individuals
Flow control
	Avoid covert channels
	Http https
	Firewall
DBA Data Base Administrator
	Super user
		Has authority
		Grant
		Revoke
		Assign levels
		(target for hackers)
	Account and passwords
		Need access
		Track logins
		System log
		Database audit trail
		Two factor authentication
			Text to phone
			Email
			Biometrics

Sensitivity of data
	Inherently sensitive
	From a sensitive source
	Declared sensitive
	Sensitive attribute
	Sensitive record
	Sensitive in relation to previous data
Hillary Clinton: government email system
	Private email
	ANY email from the Secretary of State is INHERENTLY sensitive
TRUMP:
	Put declared sensitive data in public access areas
BIDEN:
	Left sensitive data in a government office

Consider
	Data availability
	 (concurrency—not available during update)
	Accessibility—allowable?
	Authenticity—
		During working hours
		On campus
		Does it combine with other info to reveal private infor?
Security versus precision
	Safe from corruption, access control
	Make available as much (non-sensitive) data as possible
	Trade off of accessible versus protected

Grant and Revoke
	Account
	Relation (table)
	Accounts can have limited views
	Select/retrieve
	Modification/change
	Reference
Propagation
	Horizontal—how many others can you grant
	Vertical propagation—how many levels deep can you go
Levels of security
	Top secret, secret, confidential, sensitive, unclassified
	“need to know”
Questions:
	Can KEY attributes be classified?
	APPARENT key
	At different security levels you might see different tables 
	What info do you need to do your duty?
	Some roles might be mutually exclusive
	Discretionary/flexible ---vulnerable
Label based security
	Label each attribute, or row, or table…
	There is overhead to lots of tracking!
Publicly accessible databases are a challenge to security
	Some info is private
	Businesses that sell web security certificates (https)
	e-commerce has public data AND have credit cards
SQL Injection
	DANGER
	[user] to get the value for a query
	Whatever they type is inserted in your SQL query
Select * from table where username==’jake’ 
AND( (Password=’jakepassword’) OR (‘x’=’x’))

Elements of security:
Availability, integrity, authenticity, confidentiality, non-repudiation, utility
https://www.w3schools.in/cyber-security/elements-of-security/

HTML to not show passwords
https://www.w3schools.com/Tags/att_input_type_password.asp

https://www.w3schools.in/php-script/php-mysql-login-script/

https://www.w3schools.com/php/php_form_validation.asp
https://www.w3schools.com/php/php_form_required.asp
(validation.php)

The htmlspecialchars() function converts special characters to HTML entities. 
It will replace HTML characters like < and > with &lt; and &gt;. 
This prevents attackers from exploiting the code by injecting HTML or 
Javascript code (Cross-site Scripting attacks) in forms. 

Remove backslashes (\) from the user input data (with the PHP stripslashes() function)

Encryption

Plain text
Cipher text
	Substitute one character for another
	Captain crunch decoder ring
	Easy to code
	Easy to break
	Cryptoquote
Encryption
	Readable to encoded
Decryption
	Encoded to readable

XOR
	You take a series of bits, and XOR it with plain text, you get encrypted text
	Repeat the process, it is readable again
Symmetric key encryption
	Code and decode using same algorithm
	Must keep the key secret
One time only code pad
	A really long sequence that does not repeat
	But must get the code pad to the correspondent

Asymmetric key encryption
	Do not use the same key to code and decode
	Based on a mathematical trap door function
	Most famous depends on it being hard to factor a 100 digit number into two 50 digit primes
	RSA encryption algorithm
	Factoring is exponential relative to the number of digits
	(AS FAR AS WE KNOW:
	Never been proven; might be a million dollar prize if you can prove or disprove)

DECODER knows P and Q
ENCODER only needs to know P*Q

Which means:
You can digitally sign something
If you ‘decode’ on plain text;
Then someone who ‘encodes’ it, sees the plain text
This is how you can prove your identity in some circumstances
(including https certificates)

Government established a DES Data Encryption Standard 56 key
(The reason they like it is, the government can crack it)
Some use Triple DES 
AES 128,192, 256 byte keys

Many businesses developed their own scheme
	Apple phone (apple declined to help crack the code)

HASHING
	Make symbol tables in compilers
	Give a fast storage and retrieval without needed any order

Binary search is O(Lg(2)N)
1000 requires 10 searches

Interpret your first name as a base 36 number
Then take the result modulus (%) 2000.
Store 1000 names in a table, using hash to get the spot.

MOST names can be found with ONE look after you have the hash.
O(1)

Digital signatures
Digital certificate

Encryption is not enough to give you security
Fake web pages
Phishing
Physical stealing of passwords

Storage and Backup

A DB has too much info to store in CPU
Hard drive, external disk solid-state, optical, magnetic tape , flash drive, …..
SEQUENTIAL access does not work well for Databases (except for backups)

Main, cache, disk, ….
Database wants non-volatile but random-access fast memory

SPEED CAPACITY COST

Disks are GENRALLY sequential access
(spinning head, arm)

BUFFERING
	Often read into a buffer
Organize data logically
	Contiguous blocks
	Often read LARGE blocks
	Advance reading
	Schedule I/O requests
	Often hold material to be written until it is convenient to update
Buffer replacement strategies
	Which records are held in memory and for how long?
	LRU least recently used
	FIFO first in first out 
	Clock policy
Files of UNORDERED records (heap files)
	Keeping track can be fun!
	Hash methods can be used
Sorted files
	Sort by primary key
	(operations NOT by primary key are slower)
Indexed files
	For each attribute by which you might want to search
	You have a structure to allow search
	Faster to search (if indexed)
	Overhead to do and store indexing
	Searches that are not through index are slower
HASH
	Some variations of HASHING can make retrieval faster
	External hashing (to a disk)
	Extendable hashing
RAID
	(no data loss on disk failure)
	Can also have more reading take simultaneously
	Can cut disk access time relative to number of drives

How many pieces of data are in your database?
(number of tables * attributes in a table * number of rows in a table)
Binary search:
1000 10 disk
1000000 20 disk

Binary tree is nice, but there are other options
(To be discussed in a lab)