Security Transaction Lecture 07 PDF

Security Transaction Lecture 07 Introduction to Transaction Processing Concepts and Theory Database Security and Privacy FUNDAMENTALS OF Database Systems- SIXTH EDITION – Chapter 24 Introduction to Transaction Processing Concepts and Theo...

Security Transaction Lecture 07 Introduction to Transaction Processing Concepts and Theory Database Security and Privacy FUNDAMENTALS OF Database Systems- SIXTH EDITION – Chapter 24 Introduction to Transaction Processing Concepts and Theory Threats to Databases A threat to a database can result in  Loss of integrity  Loss of availability  Loss of confidentiality Loss of integrity The loss of integrity can be caused by improper modification of data by an unauthorized action:  Insertion  Deletion  Modification  Creation The loss of integrity can cause data inaccuracy and inconsistency. Loss of availability The data should be available to the database users who are eligible to access or modify data. A loss of availability means data is out of access. Loss of confidentiality Loss of confidentiality refers to an unauthorized access to data. Data should be hidden from unauthorized users. Access Control Access control is one of the control measures used in database systems to provide data security. Access to the database can be restricted by the access control mechanism.  e.g. creating user accounts and passwords. A security policy defines who can access or modify data in a database. Database Security Mechanisms Types of database security mechanisms:  Discretionary Access Control (DAC)  Grant users to access specific data files, rows, and columns in a specific mode (select, insert, delete, and update)  GRANT and REVOKE privileges  Mandatory Access Control (MAC)  Classify the data and the users into different security classes. Various security policy is enforced to users in different classification level.  Role Based Access Control (RBAC)  Roles are assigned to the database.  Roles have certain permissions on specific data. Discretionary Security Mechanisms Grant privileges on a database objects: GRANT privileges ON object TO user; Different privileges:  Select  Insert  Update  Delete  Alter  All (all privileges) Revoke Privileges on database objects REVOKE privileges ON object FROM user; Mandatory Access Control Mandatory Access Control  Classifies data and users based on security classes.  Defines what database objects can be accessed by a user. Multilevel security exists in government, military, and intelligence applications Typical security classes  top secret (TS)  secret (S)  confidential  unclassified Privilages Privileges The privileges granted to an account determine which operations the account can perform. Privileges differ in the contexts in which they apply and at different levels of operation:  Administrative Privileges  The administrative privileges are global.  Database Privileges  The user has privileges for specific databases.  Database Objects Privileges  Defines the user privileges to database objects such as tables, indexes, views, and stored routines. It can be on all database objects or some specific objects. Grant Grant privileges to users GRANT privileges ON object TO user; Privileges: What type of operations granted to the user: CREATE ALTER DROP SELECT INSERT DELETE UPDATE ALL Grant Example For table test and user root, see following examples: GRANT SELECT ON test TO ‘root'@'localhost’; GRANT INSERT,UPDAE, DELETE ON test TO ‘root'@'localhost’; GRANT ALL ON test TO ‘root'@'localhost’; GRANT SELECT ON test TO ‘*'@'localhost’; Revoke Revoke privileges from users Revoke privileges ON object FROM user; Example: REVOKE DELETE ON users FROM ‘root'@localhost'; REVOKE INSERT,UPDATE ON users FROM ‘root'@localhost'; REVOKE ALL ON users FROM ‘root'@localhost'; Views Views can be created and used as an access control mechanism. Views restricts the user access to table rows and columns. A view can filter out some of the rows and columns to be displayed to users based on their privilege and access level. Database Transaction Introduction to Transaction Processing Concepts and Theory – Chapter 21 Single-use vs Multiuser DBMS A DBMS can be  single-user  At most one user can use the database at the time  Multiuser  Multiple users can concurrently access the database at the same time. Multiuser Systems:  Banks  Supermarkets  Insurance companies  Stock market systems  Transaction A transaction includes one or more database operations:  Insertion  Deletion  Modification  Selection Transactions:  Read only Transactions  The transaction does not include update operations.  Read-write Transactions  The transaction can include both modification and data retrieval operations. In an application program, a transaction can be specified by begin and end transaction statements. Read/Write Operations For simplicity assume the following basic operations in a database in a database item (fields, records,…)  read_item(X)  This operation you reads an item X from the database and store it into a variable or an object in an application program.  write_item(X)  This operation you writes a value into an item X from an application program. We can say a transaction contains a set of read_item and write_item operations in a database.  Concurrency Concurrency control and recovery mechanisms are main concerns when it comes to database transactions. In a database, multiple users may submit different transactions to access or modify the same database item at the same time. database item X User 1 User 2 10 X = 10 read_item(x) X = 10 read_item(x) X=X+2 12 write_item(X) X=X+4 14 write_item(X) The final value must be 16 not 14 Concurrency control Different transactions can concurrently be submitted to a database by multiple users. Various transactions may need to access and update the same database items. If concurrency is not properly managed, it may lead to a number of problems such as  Inconsistence and/or invalid data in the database. Concurrency control and recovery mechanisms are required in multiuser databases. Recovery A transaction  is a logical unit of database processing. When a transaction is submitted  Either all operation in the transaction are successfully completed.  All changes will become permanent in the database.  Or the transaction fails and no changes will be made in the database. Committed Transaction: A successful transaction Aborted Transaction: An unsuccessful transaction Failure If a transaction fails in any step, all changes and updates in previous steps (operations) are needed to be undone. Some types of failures:  A computer failure  Hardware problems  Software problems  Network problems  A transaction or system error  Logical programming errors  Variable overflow (divided by zero)  External interruption (canceling the transaction before its completion)  Local errors  Some conditions required to complete the transaction are not met.  Withdraw an amount from an account with zero balance.  Disk failure Transaction Operations BEGIN_TRANSACTION  specifies the beginning of a transaction where the transaction starts. END_TRANSACTION  Determines the end of the transaction where all read and write operations are executed.  At this point database decides to make changes permanent if all read and write operations are successful.  Or rollback, if there is any failure. READ or WRITE  Specifies read or/and write operations that are executed in a transaction on database items. COMMIT  Determines that the transaction is successful.  All changes on database items become permanent at this point.  The changes are not revisable after this point. ROLLBACK  It undoes all changes on database items i8f any part of the transaction failes. Properties of Transactions A transaction has some properties known as ACID properties. ACID properties are enforced to maintain the concurrency control and recovery methods.  Atomicity.  A transaction is considered an atomic process unit.  All or none of the operations inside a transaction must be successfully completed.  If an operation in a transaction fails, the whole transaction will fail.  Consistency preservation.  A transaction should change the database from one consistent state to another consistent state if the transaction is completed.  Isolation.  A transaction should be isolated from other transactions.  The execution of a transactions do not interfere with the execution of another transaction.  Durability or permanency.  All changes at the end of a transaction must become permanent after commit.  The data will not be lost as a result of a transaction failure. Atomicity Property The atomicity property specifies a transaction has to be completed.  If the transaction fails the recovery mechanism guarantees the transaction atomicity.  As a result of any failure, all changes will be undone by the recovery methods.  In a successfully executed transaction, all changes from transaction operations become permanent. Consistency Property Mostly database programmers are responsible to preserve the consistency. Database programs are supposed to be written in a way that maintain the integrity constraints. In an consistent state, all constraints defined in a database schema must ne satisfied. A database program should guarantee that if the database was in a consistence state before transaction, it remains is a consistent state after transaction. One way to avoid inconsistency in a database is to prevent any interference among database transactions. Isolation The concurrency control system is responsible to maintain the isolation property. An isolation level defines what transaction cannot have access to data items that are being processed by other transactions. Dirty Read – A Dirty read means read a data item while it is under process by another transaction before commit.  For example, transaction t2 reads a uncommitted data item updated in transaction t1. Transaction t1 may rollback at the end while the updated value has been read by transaction t2. Non Repeatable read – Non Repeatable read means a transaction reads the same database item more than once but receives different values.  For example, assume that transactions t1 and t2 are concurrently be executed and access the same database item.  Transaction t1 reads a database item. Then, another transaction t2 updates the item and commits. Due to the concurrency issue, if t1 reads the database item again, it sees a different value. Phantom Read – means the same query in two transactions t1 and t2 returns different results.  The database user can run the same query twice and gets different result. Isolation Levels level of isolation level 0 (Read Uncommitted)  Lowest level of isolation that transaction are not isolated from each other.  A transaction can read a dirty database item (an uncommitted changes in a database item) Level 1 (Read Committed)  This level of isolation guarantees that dirty reads are not allowed.  Transactions reads committed database item.  The transaction lock the database item for read and write to prevent other transactions to access the database item for any operation. level 2 (Repeatable Read)  This level of isolation specifies that transactions cannot read database items are being modified by another transaction and not yet committed. The database item will be available after the transaction commits or rollbacks. level 3 isolation (Serializable)  This is the Highest isolation level. This level defines concurrent transactions are serially executed.