tailieunhanh - Lecture Database Systems - Lecture 17

After completing this chapter, students will be able to: Introduction to database security, discretionary access control (DAC), mandatory access control (MAC), role-based access control (RBAC), encryption & public key infrastructure (PKI), common attacks on databases, SQL injection, challenges of database security. | CSC271 Database Systems Lecture # 17 Summary: Previous Lecture View updatability Advantages and disadvantages of views View materialization Transactions SQL defines transaction model based on COMMIT and ROLLBACK Transaction is logical unit of work with one or more SQL statements guaranteed to be atomic with respect to recovery An SQL transaction automatically begins with a transaction-initiating SQL statement (., SELECT, INSERT) Changes made by transaction are not visible to other concurrently executing transactions until transaction completes Transactions Transaction can complete in one of four ways: COMMIT ends transaction successfully, making changes permanent ROLLBACK aborts transaction, backing out any changes made by transaction For programmatic SQL, successful program termination ends final transaction successfully, even if COMMIT has not been executed For programmatic SQL, abnormal program aborts the transaction Transactions New transaction starts with next transaction-initiating statement SQL transactions cannot be nested SET TRANSACTION configures transaction: SET TRANSACTION [READ ONLY | READ WRITE] | [ISOLATION LEVEL READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE ] READ ONLY/ READ WRITE The READ ONLY and READ WRITE qualifiers indicate whether the transaction is read only or involves both read and write operations The default is READ WRITE Confusingly, READ ONLY allows a transaction to issue INSERT, UPDATE, and DELETE statements against temporary tables (but only temporary tables) Isolation Level The isolation level indicates the degree of interaction that is allowed from other transactions during the execution of the transaction Dirty read: A transaction reads data that has been written by another as yet uncommitted transaction Non-repeatable read: A transaction rereads data it has previously read but another committed transaction has modified or deleted the data in the intervening period Phantom read: A transaction executes a . | CSC271 Database Systems Lecture # 17 Summary: Previous Lecture View updatability Advantages and disadvantages of views View materialization Transactions SQL defines transaction model based on COMMIT and ROLLBACK Transaction is logical unit of work with one or more SQL statements guaranteed to be atomic with respect to recovery An SQL transaction automatically begins with a transaction-initiating SQL statement (., SELECT, INSERT) Changes made by transaction are not visible to other concurrently executing transactions until transaction completes Transactions Transaction can complete in one of four ways: COMMIT ends transaction successfully, making changes permanent ROLLBACK aborts transaction, backing out any changes made by transaction For programmatic SQL, successful program termination ends final transaction successfully, even if COMMIT has not been executed For programmatic SQL, abnormal program aborts the transaction Transactions New transaction starts with next .