IS6172 Lecture 12 Term 2

1. IS6172 Lecture 12 Term 2 24/3/14

2. Formulate Database Security Strategy Discovery and Classification Auditing Encryption Authentication, authorization and access control Monitoring Data Masking Patch Management Vulnerability Assessment Database Firewall DET ECTION FOUNDAT ION PREVENTION Figure Pillars of Database Security

3. Foundation • The foundation pillar stresses on discovery and classification of sensitive data and having a robust authentication, authorization, and access control framework • All critical databases should be patched on a regular basis to eliminate known vulnerabilities • Understanding which databases contain sensitive data is a key requirement for any database security strategy. Enterprises should take a complete and ongoing inventory of all databases, including production and nonproduction, and ensure authentication, authorization, and access control is enabled for all critical databases.

4. Detection • All changes to sensitive data should be logged to provide the ability to answer audit questions. Auditing and monitoring offer compensating controls when preventive measures are not enabled. • To support regulatory compliance standards, such as PCI, HIPAA, SOX, and EU, and improve data security, enterprises should track all access and changes to sensitive data • Data and metadata in databases can be accessed, changed, or even deleted in seconds. Detection pillar provides a detailed audit trail of database activities and provides details on vulnerabilities

5. Prevention • This pillar focuses on preventing unauthorized access and protecting against attacks • Preventive security measures 1) network and data-at-rest encryption; 2) data masking for nonproduction databases to prevent data exposure to non-production users; 3) database firewall to prevent threats such as SQL injection attacks or privilege escalation from even reaching databases; and 4) change management to enable a formal procedure to manage changes in production.

6. Encryption • Transparent data encryption (TDE) enables you to encrypt individual table columns or an entire tablespace. • When a user inserts data into an encrypted column, transparent data encryption automatically encrypts the data. • When users select the column, the data is automatically decrypted. After the selection, the data is reencrypted. • Transparent data encryption helps protect data stored on media in the event that the storage media or data file gets stolen, because it stores the encryption keys in a security module (that is, a wallet) external to the database. • Protecting data from this type of theft is required for most compliance regulations. The benefit to using transparent data encryption is that it requires little coding and is quick and easy to implement.

7. Data Masking • Sensitive information, such as credit card or social security numbers, can be replaced with realistic values • Production data can be safely used for development, testing, or sharing with out-source or off-shore partners • Uses a template library and format rules, consistently transforming data in order to maintain referential integrity for applications • Extensive search capabilities scan enterprise databases for sensitive data and rank results based on probability of match • Helps comply with data privacy mandates such as Sarbanes-Oxley, Payment Card Industry (PCI) Data Security Standard (DSS) and Health Insurance Portability and Accountability Act (HIPAA)

8. Database Firewall • Oracle Database Firewall prevents illegal access to databases, stops SQL injection, enforces security policies accurately, and generates real-time alerts. • It prevents internal data policy breaches - even by trusted insiders and privileged users. • You can enforce how activity will be controlled by allowing the SQL to pass through to the database, block and substitute the SQL so no database results are returned to the user, or alert on the activity.

9. SQL Injection • SQL injection is a basic attack used to either gain unauthorized access to a database or to retrieve information directly from the database • The principles behind a SQL injection are simple and these types of attacks are easy to execute and master • Vulnerability of application development - Applications can be developed using many methods for connecting to an Oracle database – some of these methods are more vulnerable to SQL Injection attacks than others

10. More on Locks - Exclusive Locking • The resources have been locked only allow accessing the locking operation, other operations will not be accepted. • Implementing the command of update data, that is, INSERT, UPDATE or DELETE command • However, when the object has other locks, we cannot use exclusive lock. Exclusive lock can be released until the end of transaction.

11. Locking • Optimistic locking assumes that multiple transactions can complete without affecting each other, and that therefore transactions can proceed without locking the data resources that they affect. • Before committing, each transaction verifies that no other transaction has modified its data. If the check reveals conflicting modifications, the committing transaction rolls back

12. Optimistic locking • When your application uses long transactions or conversations that span several database transactions, you can store versioning data, so that if the same entity is updated by two conversations, the last to commit changes is informed of the conflict, and does not override the other conversation's work. • This approach guarantees some isolation, but scales well and works particularly well in Read-Often Write-Sometimes situations.

13. Optimistic locking • Timestamps are a less reliable way of optimistic locking than version numbers, but can be used by applications for other purposes as well. • Timestampingis automatically used if you the @Version annotation on a Date or Calendar.

14. Pessimistic locking • Assumes that concurrent transactions will conflict with each other, and requires resources to be locked after they are read and only unlocked after the application has finished using the data. • Hibernate provides mechanisms for implementing both types of locking in your applications.

15. Database Deadlock • One of the classical reason we have a database deadlock is when two transactions are inserting \updating tables in a different order. • e.g. Transaction A inserts in Table A then Table B • and Transaction B inserts in Table B followed by A • Such a scenario is always at risk of a database deadlock (assuming you are not using serializable isolation level) • With a lock-based concurrency control DBMS implementation, serializability requires read and write locks (acquired on selected data) to be released at the end of the transaction.

16. Dealing with Deadlocks A deadlock occurs when two or more session are waiting for data locked by each other, resulting in all the sessions being blocked. Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock. Typically, deadlocks are caused by poorly implemented locking in application code. • Prevention • Detection

17. Overview of Database Locks • Used to implement concurrency by all commercial databases • Interference between concurrent transactions can be controlled by enabling transactions to lock parts of the database while it is being amended • Granularity • Table level • Row/record level • Page level (whole physical blocks) • Optimistic - assumes transactions will NOT conflict • Pessimistic – assumes transaction conflict will occur • Shared/S Lock - only required to be read • Exclusive/X Lock – when data is to be updated

18. ACID Properties • The ACID model is one of the oldest and most important concepts of database theory. • It sets forward four goals that every database management system must strive to achieve: atomicity, consistency, isolation and durability. • The characteristics of database transaction are often expressed using the ACID acronymn.

19. ACID Properties • Atomicity states that database modifications must follow an “all or nothing” rule. • Each transaction is said to be “atomic.” • It is critical that the database management system maintain the atomic nature of transactions in spite of any DBMS, operating system or hardware failure. • Consistency states that only valid data will be written to the database. • If a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules. • If a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules.

20. Revisit ACID Properties • Isolation requires that multiple transactions occurring at the same time not impact each other’s execution. • For example, if Joe issues a transaction against a database at the same time that Mary issues a different transaction, both transactions should operate on the database in an isolated manner. • The database should either perform Joe’s entire transaction before executing Mary’s or vice-versa. • This prevents Joe’s transaction from reading intermediate data produced as a side effect of part of Mary’s transaction that will not eventually be committed to the database. • Note that the isolation property does not ensure which transaction will execute first, merely that they will not interfere with each other. • Durability ensures that any transaction committed to the database will not be lost. • Durability is ensured through the use of database backups and transaction logs that facilitate the restoration of committed transactions in spite of any subsequent software or hardware failures

21. Distributed Databases • Logical next steps in geographically dispersed organisations • Objective is to provide location transparency • A set of decentralised DBs located in differed places, developed for the specific information needs of each site • Appears to a user as a single database but is, in fact, a set of databases stored on multiple computers.

22. Distributed Databases • Improves system efficiency due to reduced network traffic and faster access to local data • Improves availability: if one site fails, the rest of the system can continue to operate (at least to the extent that the services of the failed site are not required) • Increased reliability of systems • By subdividing the work the system capacity is improved • Local control of data preserved at each site • Modular growth possible at each site and at new sites • Optimised communication costs • Faster response time

23. Overview IS6172 • Intro to Modeling – why build a model? • Systems development Lifecycle • Use Case Modeling • Data Modeling (building ERDs) • Primary Key • Foreign key • Attributes • Relationship Cardinality

24. Continued.. • Introduction to database management systems (DBMS) • Introduction to SQL – Structured Query Language • Data Definition Language (DDL) i.e. CREATE, DROP ALTER • Data Manipulation Language (DML) i.e. SELECT , INSERT , UPDATE, DELETE

25. Overview SQL • Select * from table name; • Order by • Where • Group by (aggregate functions) • Having • Distinct • Comparison Operators • Wildcard like ‘%’ • Create table • Insert • Insert all • Update • Delete • Alter • Dual • Sysdate • Table alias • Column alias • Contraints

26. Overview SQL • Character Functions • Upper/Lower/Initcap/Concat/lpad/rpad/instr/ltrim/ • rtrim/soundex/length/ • replace • Number functions • Round/trunc/sign/ceil/floor/power • Date functions • Months_betwee/add_months/next_day/last_day/round • Group functions e.g. avg, max, min, count, sum • Joins - equijoins, inner joins, outer joins • All, Any, some, Exists • Sub-queries (query within a query) • Correlated sub query (where the outer query provides a value to the inner query) • Index

27. IS6172 Continued.. • Normalization (Lecture 5 Term 2) • What is normalization • Anomaly Avoidance • Update • Insert • Delete • First normal form – fifth normal form • Functional Dependency • Partial Functional Dependency • Transitive Dependency

28. IS6172 Continued.. • Distributed Databases • Full Replication • Partitioning • Vertical • Horizontal • Advantage • Industry Examples

29. Continued • Functions of a Database Management Systems • ACID Properties • Transactions • Locking Mechanisms Maintaining Data Integrity • Data Validation techniques • Back-up and recovery • Database privileges and permissions • Role of Database Administrator • Database Development Lifecycle • Database Security • Threats • Detective and Preventative Mechanism

30. Summer Exam 2014 • Six questions • Must complete question 1 • Must complete 3 questions from section B • Question 2 (5 PARTS) • Question 3 a and b • Question 4 a and b • Question 5 a (i,ii,iii) and b • Question 6 a and b