LECTURE TEN DATABASE INTEGRITY AND SECURITY Database Integrity
LECTURE TEN DATABASE INTEGRITY AND SECURITY
Database Integrity • Database integrity ensures that data entered into the database is accurate, valid, and consistent. Any applicable integrity constraints and data validation rules must be satisfied before permitting a change to the database. Business applications have several similar problems such as: • Multiple users trying to change the same data • Multiple changes need to be made concurrently
Database Integrity…. • For example: A customer uses the ATM and instructs it to transfer 20, 000 shillings from the savings account to the current account. This transaction require two steps – 1) subtracting money from the savings account 2) adding money to the current account These are two updates or SQL statements. If the system crashes in between, the customer could loose their money.
Database Integrity…. . • How does the computer know that both operations must be completed at the same time? As an application developer, you must tell the computer system what operations belong to a transaction. • You do this by marking the start and the end of all transactions inside the code. This would ensure that all the updates complete together or fail together
Database Integrity…. . CREATE FUNCTION Transfer. Money(Amount Currency, Account. From Number, Account. To Number) RETURNS NUMBER cur. Balance Currency; BEGIN DECLARE HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; Return -2; -- flag for completion error END; START TRANSACTION; -- optional SELECT Current. Balance INTO cur. Balance FROM Accounts WHERE (Account. ID = Account. From); IF (cur. Balance < Amount) THEN RETURN -1; -- flag for insufficient funds END IF UPDATE Accounts SET Current. Balance = Current. Balance – Amount WHERE Account. ID = Account. From; UPDATE Accounts SET Current. Balance = Current. Balance + Amount WHERE Account. ID = Account. To; COMMIT; RETURN 0; -- flag for success END;
Database Integrity…… • Concurrent access can also be problematic. An example is if two people try to change the same data at the same time. Some data could be overwritten and lost. One solution is to prevent concurrent access by forcing transactions to be completely isolated.
Concurrent Access • Two processes – Multiple users or – Receive payment ($200) processes changing the – Place new order ($150) same data at the same • Initial balance $800 time. – Result should be $800 -200 – Final data will be wrong! + 150 = $750 • Force sequential – Interference result is either – Locking $600 or $950 – Delayed, batch updates Customers Receive Payment 1) Read balance 2) Subtract pmt 4) Save new bal. 800 -200 600 ID Jones Balance $800 $600 $950 Place New Order 3) Read balance 5) Add order 6) Write balance 800 150 950
Pessimistic Locks: Serialization One answer to concurrent access is to prevent it. When a transaction needs to alter data, it places a SERIALIZABLE lock on the data used, so no other transactions can even read the data until the first transaction is completed. SET TRANSACTION SERIALIZABLE, READ WRITE Customers Receive Payment 1) Read balance 2) Subtract pmt 4) Save new bal. 800 -200 600 ID Jones Balance $800 $600 Place New Order 3) Read balance Receive error message that it is locked.
Database Integrity • The concept of integrity is fundamental to databases. One of the strengths of the database approach is that the DBMS has tools to handle the common problems. In terms of transactions, many of these concepts can be summarized in the acronym ACID. The following figure shows the meaning of the term.
ACID Transactions • Atomicity: all changes succeed or fail together. • Consistency: all data remain internally consistent (when committed) and can be validated by application checks. • Isolation: The system gives each transaction the perception that it is running in isolation. There are no concurrent access issues. • Durability: When a transaction is committed, all changes are permanently saved even if there is a hardware or system failure.
Referential Integrity • Referential integrity is a property of data that applies (or fails to apply) to a database as a whole. In this sense, referential integrity means that in the database as a whole, things are set up in such a way that if a column exists in two or more tables in the database (typically as a primary key in one table and as a foreign key in one or more other tables), then any change to a value in that column in any one table will be reflected in corresponding changes to that value where it occurs in other tables. This means that the RDBMS must be set up so as to take appropriate actions to spread a change —in one table—from that table to the other tables where the change must also occur.
Database Security • The major technical areas of computer security are usually represented by the initials CIA: confidentiality, integrity, and authentication or availability. Confidentiality means that information cannot be access by unauthorized parties. Confidentiality is also known as secrecy or privacy. Integrity means that information is protected against unauthorized changes that are not detectable to authorized users; Authentication means that users are who they claim to be. Availability means that resources are accessible by authorized parties.
Database Security • Database security is the system, processes, and procedures that protect a database from unintended activity. Unintended activity can be categorized as authenticated misuse, malicious attacks or inadvertent mistakes made by authorized individuals or processes. Database security is also a specialty within the broader discipline of computer security
Database Security cont…. • Traditionally databases have been protected from external connections by firewalls on the network perimeter with the database environment existing on the internal network. Additional network security devices that detect and alert on malicious database protocol traffic include network intrusion detection systems along with host-based intrusion detection systems. • Database security is more critical as networks have become more open.
Firewalls • firewall is a part of a computer system or network that is designed to block unauthorized access while permitting authorized communications. It is a device or set of devices that is configured to permit or deny network transmissions based upon a set of rules and other criteria.
Firewall Cont…. • Firewalls can be implemented in either hardware or software, or a combination of both. Firewalls are frequently used to prevent unauthorized Internet users from accessing private networks connected to the Internet, especially intranets. All messages entering or leaving the intranet pass through the firewall, which inspects each message and blocks those that do not meet the specified security criteria.
Firewall
Vulnerability Assessments • An important procedure when evaluating database security is performing vulnerability assessments against the database. A vulnerability assessment attempts to find vulnerability holes that could be used to break into the database. Database administrators or information security administrators run vulnerability scans on databases to discover a breach of controls, along with known vulnerabilities within the database software. The results of the scans should be used to harden the database in order to mitigate threat of compromise by intruders.
Database Security Cont… • A database security program should include the regular review of permissions granted to individually owned accounts and accounts used by automated processes. The accounts used by automated processes should have appropriate controls around password storage such as sufficient encryption and access controls to reduce the risk of compromise
Database Security cont… • In conjunction with a sound database security program, an appropriate disaster recovery program should exist to ensure that service is not interrupted during a security incident or any other incident that results in an outage of the primary database environment. An example is that of replication for the primary databases to sites located in different geographical regions.
Database Security cont… • Native database audit capabilities are also available for many database platforms. The native audit trails are extracted on a regular basis and transferred to a designated security system where the database administrators do not have access. This ensures a certain level of segregation of duties that may provide evidence that the native audit trails were not modified by authenticated administrators.
Database Forensics • A forensic examination of a database may relate to the timestamps that apply to the update time of a row in a relational table being inspected and tested for validity in order to verify the actions of a database user. Alternatively, a forensic examination may focus on identifying transactions within a database system or application that indicate evidence of wrong doing, such as fraud. The forensic study of relational databases requires a knowledge of the standard used to encode data on the computer disk.
Physical Security • Hardware – Preventing problems • Fire prevention • Site considerations • Building design – Hardware backup facilities • Continuous backup (mirror sites) • Hot sites • Shell sites • “Sister” agreements • Data and software – Backups – Off-site backups – Personal computers • Policies and procedures • Network backup • Disaster planning – – Write it down Train all new employees Test it once a year Telecommunications • Allowable time – Telecommunication systems between disaster and business survival limits. – Personal computers
Threats • The primary threat to any company comes from insiders. Employees must be trusted, because in order for them to do their jobs they need access to the computers and the database. Once they are granted access it becomes more difficult to control what they do. • Another threat comes from programmers.
Threats… • One technique used by programmers is to insert a time bomb in a program. A time bom requires a programmer to enter a secret code every day. I f the programmer is sacked or leaves work and cannot enter the code, the program starts deleting files. • In other cases programmers have deliberately created programs that alter data or transfer money to their accounts.
Physical Security Provisions • • Backup data. Backup hardware. Disaster planning and testing. Prevention. – Location. – Fire monitoring and control. – Control physical access.
Managerial Controls • “Insiders” – Hiring – Termination – Monitoring – Job segmentation – Physical access limitations • Locks • Guards and video monitoring • Badges and tracking
Managerial Controls…. . • Consultants and Business alliances –Limited data access –Limited physical access –Paired with employees
Logical Security • Unauthorized disclosure. • Unauthorized modification. • Unauthorized withholding. • Disclosure example – Letting a competitor see the strategic marketing plans. • Modification example – Letting employees change their salary numbers. • Withholding example – Preventing a finance officer from retrieving data needed to get a bank loan.
User Identification • User identification • Accounts – Individual – Groups • Passwords – Do not use “real” words. – Do not use personal (or pet) names. – Include non-alphabetic characters. – Use at least 6 (8) characters. – Change it often. – Too many passwords! • Alternative identification – – Finger / hand print readers Voice Retina (blood vessel) scans DNA typing • Hardware passwords – The one-minute password. – Card matched to computer. – Best method for open networks / Internet.
Basic Security Ideas • Limit access to hardware – – – Physical locks. Video monitoring. Fire and environment monitors. Employee logs / cards. Dial-back modems 3 5 2 Jones 1111 Smith 2222 Olsen 3333 Araha 4444 phone company • Monitor usage – Hardware logs. • Dialback modem – User calls modem – Access from network nodes. – Modem gets name, – Software and data usage. • Background checks – Employees – Consultants 1 password – Modem hangs up phone – Modem calls back user – Machine gets final password 4
Separation of Duties Supplier. ID Name … 673 772 983 Acme Supply Basic Tools Common X Purchasing manager can add new suppliers, but cannot add new orders. Referential integrity Purchase. Order. ID Supplier. ID 8882 8893 8895 772 673 009 Clerk must use Supplier. ID from the Supplier table, and cannot add a new supplier.
Encryption • Protection for open transmissions AES – Networks – The Internet – Weak operating systems • Single key (AES) • Dual key Plain text message Key: 9837362 Encrypted text Single key: e. g. , AES – Protection – Authentication • Trap doors / escrow keys • U. S. export limits – 64 bit key limit – Breakable by brute force • Typical hardware: 2 weeks • Special hardware: minutes Encrypted text Key: 9837362 AES Plain text message
Dual Key Encryption Message Transmission Message Encrypt+T+M Alice Private Key 13 Use Alice’s Private key Encrypt+M Encrypt+T Public Keys Alice 29 Bob 17 Use Bob’s Public key Use Alice’s Public key Bob Private Key 37 Use Bob’s Private key • Using Bob’s private key ensures it came from him. • Using Alice’s public key means only she can read it.
Backup and Recovery • Backups are crucial! • Offsite storage! • Scheduled backup. – Regular intervals. – Record time. Snapshot – Track backups. • Journals / logs • Checkpoint • Rollback / Roll Journal/Log forward Changes Ord. ID Odate Amount. . . 192 2/2/01 252. 35 … 193 2/2/01 998. 34 … Ord. ID 192 193 194 Odate Amount. . . 2/2/01 252. 35 … 2/2/01 998. 34 … 2/2/01 77. 23. . . Ord. ID 192 193 194 195 Odate Amount. . . 2/2/01 252. 35 … 2/2/01 998. 34 … 2/2/01 77. 23 … 2/2/01 101. 52 …
Database Security • Authorization, Access Control: – protect intranet from hordes: Firewalls • Confidentiality, Data Integrity: – protect contents against snoopers: Encryption • Authentication: – both parties prove identity before starting transaction: Digital certificates • Non-repudiation: – proof that the document originated by you & you only: Digital signature
What can go wrong? Security issues • • Intruders – Casual prying (read other peoples e-mail, documents, etc. ) – Snooping by insiders – Determined attempt to make money – Commercial or military espionage – Simply for fun or to prove it can be done How to deal with intruders – Identify every user – Advise users to log off when they leave their desk – Limit the privileges of users – Log files to monitor users activity – Encryption – Etc.
Insiders • What could some of the employees do? – Read other people’s emails – Attempt to read documents and access information that is NOT intended for their eyes – Commercial espionage – Install unauthorised software
Insiders…. . • How to prevent all of the above? – Each employee should log in the system using a unique username / password – Advice all employees not to disclose their password to anyone – Advice all employees to log off when they leave their desk – Advice all employees to change their password regularly
Insiders…. . – Put in place a system that tracks employees actions and network resources accessed – Limit privileges of employees allowing them to perform only authorised tasks and obtain only authorised information – Encrypt or password protect confidential documents / data – Any other measures? all
Outsiders • What could they do? – As a hobby, prove that “it can be done” – Commercial and military espionage – Access bank accounts – Access and use other people’s credit card details – Shut down systems, etc.
Outsiders…. • How to prevent outsiders gaining access to resources – Identify every user of the system – Put in place a system that tracks users actions and network resources accessed – Encrypt confidential documents / data – Put firewalls in place to protect the network – Keep all software and operating systems up to date to prevent hackers exploit security holes
Have a security policy in place and ENFORCE it • Have clear guidelines as how security should be implemented • Management has to make sure that all IT technicians apply all the security measures • Management has to make sure that all employees are aware of the security measures and apply them • Technology used to implement security guidelines – Sophisticated tools used to analyse, interpret, configure and monitor the state of the network security
Identify each user • Clearly identify all network users • Technologies used to assure identity – Username and passwords Advice employees to : –use alphanumeric passwords –to keep them private –to change them regularly – Biometrics
Identify each user…. – Install access control programs and physical security devices on all systems. Access control programs run extra checks on users before allowing access. Physical security devices include biometric scanning devices fitted to a computer which check a user’s face, retina, fingerprint, hand, voice, typing rhythm, signature and so on against a set of stored data for all legitimate users. • Make sure to delete the accounts of employees no longer working for the company
Monitor the network • Security monitor – Test and monitor the state of the network security • Technology used to monitor the network – Network log files that record • Who logged in, for how long, from which computer, what resources they have accessed, etc.
Monitor the network…. . – Network vulnerability scanners – Antivirus software – Disaster recovery backup technology • Check security logs and audit trails regularly • Conduct regularly a through risk analysis of the network • Have a disaster recovery plan
Monitor and restrict access from outside into the network • Monitor remote access into the network by – Allowing only a limited number of attempts to log in – Block the account if all attempts to log in are unsuccessful – Use log files to monitor the resources accessed by remote users • Put firewalls in place before allowing Internet access
Maintain data privacy • Data privacy – Information must be protected from eavesdropping – Data must be communicated in confidentiality • Technologies used to assure data privacy – Password protect confidential documents – Encryption – Use secure protocols • ssh (secure shell) • https (http scheme) = http with encryption
Encryption • Computer encryption is based on the science of cryptography • Encryption systems –Symmetric key encryption • A computer uses a key to encrypt a message before sending it over the network • The destination computer uses the same key to decode it • The same key has to be installed on both computers
Encryption…. • Public key encryption – A computer uses a combination of private key and public key to encrypt a message. The private key is known only to the computer, while the public key is given to any computer that wants to communicate securely with. – The destination computer decodes the message using the public key provided by the sending computer and its own private key.
Where is encryption used? • Digital signatures – A way to ensure that an electronic document (a, word document, excel spreadsheet, etc. ) is authentic – Standard used - Digital Signature Standard which is based on a public-key encryption
Where is encryption used? . . – If anything is changed in the document after the signature is attached to it, the value the digital signature compares with changes and therefore it will be obvious that changes have been made • Electronic payment
Electronic payment • E-business and electronic payment go hand in hand • What are the benefits of electronic payment? • One could pay: – On the spot by providing credit card information – On the spot using e-check (account number and bank number) – Via specialised companies like Pay. Pal
Electronic payment…. • Concerns about electronic payment – By direct debit using credit card or bank account – Identity theft • To prevent fraud, confidential information has to be transmitted and stored encrypted
Secure methods of payment • SSL – Stands for Secure Sockets Layer – Uses public-key encription – SSL is an Internet Security Protocol used by browsers and web servers to transmit sensitive information – SSL is part of an overall security protocol known as Transport Layer Security
How can a customer know his/her payment information is securely transmitted? • Look for the s after http in the web address before making the payment. In other words, the web address should read: https// • Look for the padlock symbol in the status bar, at the bottom of the browser window
Encryption…. Encryption is the process of transforming information (referred to as plaintext) using an algorithm (called cipher) to make it unreadable to anyone except those possessing special knowledge, usually referred to as a key. The result of the process is encrypted information (in cryptography, referred to as cipher text).
Encryption…. • In many contexts, the word encryption also implicitly refers to the reverse process, decryption (e. g. “software for encryption” can typically also perform decryption), to make the encrypted information readable again (i. e. to make it unencrypted).
Areas of Uses Encryption can be used to protect data "at rest", such as files on computers and storage devices (e. g. USB flash drives). Digital rights management systems which prevent unauthorized use or reproduction of copyrighted material and protect software against reverse engineering are another somewhat different example of using encryption on data at rest. `
Areas of Uses…. Encryption is also used to protect data in transit, for example data being transferred via networks (e. g. the Internet, e-commerce), mobile telephones, wireless microphones, wireless intercom systems, Bluetooth devices and bank automatic teller machines.
How it Works Public and Private Key Encryption Systems Private and public keys are used in two main encryption systems: Symmetric and Asymmetric. Symmetric Encryption This system uses only private keys. This requires the private key (code) to be installed on specific computers that will be used for exchanging messages between certain users.
How it Works…. The system works pretty much like two best friends using a decoder ring to send secret messages to each other. Both friends know which code they are using and thus, only they will have the key to crack and encode secret messages.
Asymmetric Encryption The Asymmetric Encryption system uses both the private and public keys. The private key is for yourself and the public key is published on line for others to see. They use the public key to access the encryption code that corresponds to your private key. So, if you are sending an encrypted message to Susan which you do not want others to see, you would use her public key to encrypt it.
Asymmetric Encryption… She will be able to decrypt it with her own corresponding private key. Likewise, if she sends a message to you, she uses your public key to encrypt the message and you would use your private key to decrypt it. Digital IDs are ideal because they can't be easily forged. They allow for a more electronically secured communication line because they enable you to make use of these encryption systems. By applying them to your PDF files, you are ensure their security.
Reliability of encryption The strength of an encryption technique describes how difficult it is to ‘break’ it (decrypt the information without knowing the decryption algorithm, decryption key or passphrase, see Box 1). Information security experts agree that there already algorithms which are very strong and if used correctly are effectively unbreakable.
Reliability of encryption…. Choosing appropriate encryption depends on: • what kind of information needs to be secured; • how long it needs to be protected; • who the potential interceptors are; • what resources they might have.
Issues The benefits of encryption in helping to secure electronic commerce and safeguard privacy are clear. However, it also provides a way of concealing unlawful activity. The policy debate about encryption centre's on how to strike a balance between its risks and benefits. Encryption and criminality
THE END THANK YOU
- Slides: 69