Lesson 6

This module covered techniques for controlling transactions within PL/SQL block. You added transaction controls to a package and llearned about locking modes and levels. You studied a special variation on the cursor declaration that allows you to lock rows immediately to prevent others from using them before your procedure can.

Data integrity refers to the accuracy, consistency, and reliability of data stored in a database. In Oracle databases, PL/SQL is often the backbone for operations like data manipulation, procedure invocation, and business logic implementation. Despite its robustness, data integrity can be compromised in PL/SQL through various means. Understanding these vulnerabilities is crucial for safeguarding the integrity of the database. Lack of proper backup and recovery procedures can also lead to data integrity issues in case of a disaster.

SQL Injection is a type of attack in which malicious SQL code is inserted into a query. In PL/SQL, if user inputs are concatenated directly into a SQL query without proper validation or binding, the system becomes susceptible to SQL Injection. For instance: This occurs when malicious code is inserted into a SQL statement, allowing an attacker to access or modify data in the database.

EXECUTE IMMEDIATE 'SELECT * 
FROM employees WHERE name = ''' || user_input || '''';

The above code does not sanitize `user_input`, thereby posing a risk of SQL Injection, which can alter, delete, or corrupt the data.

Transactions must be handled carefully to preserve data integrity. Failure to properly implement ACID (Atomicity, Consistency, Isolation, Durability) properties can result in data anomalies. For example, if a PL/SQL block updates a record but fails to commit the transaction under certain conditions, it may lead to data inconsistency.

Improper implementation of access controls can also lead to compromised data integrity. Granting excessive privileges or failing to revoke outdated permissions can allow unauthorized users to manipulate data through PL/SQL programs. Unauthorized access may occur, if a user is able to gain access to the database without proper authentication, they may be able to view or modify data.

Unhandled exceptions can cause data integrity issues. If an error occurs and is not caught due to inadequate exception handling, the operations might leave the data in an inconsistent state.

Failing to validate data before it is processed or inserted into the database can introduce inconsistencies. Data types, ranges, or format checks must be enforced in PL/SQL code to prevent invalid data entries. If the data validation is not implemented properly, it can lead to data integrity issues. Data corruption can occur if data is corrupted due to hardware failure, software bugs, or other issues, leading to integrity issues.

Logical errors such as incorrect calculations, faulty decision trees, or erroneous data manipulations can inadvertently compromise data integrity. These errors may not manifest immediately and may require meticulous debugging to identify.

Failure to manage concurrent transactions effectively can lead to race conditions, thereby compromising data integrity. Oracle provides mechanisms like locks and isolation levels, which must be appropriately used in PL/SQL to manage concurrent operations. If multiple users are able to access and modify the same data simultaneously, it can lead to data inconsistencies and integrity issues.

Inadequate logging can mask data integrity issues, making it challenging to trace the root cause of the problem or to revert the database to a consistent state.

Storing PL/SQL code without adequate encryption or in a compromised environment can expose it to unauthorized alterations, subsequently impacting data integrity. Mitigating these risks requires implementing robust security measures such as input validation, parameterized queries, and comprehensive logging, along with adhering to best practices in transaction and concurrency control. Regular audits, code reviews, and vulnerability assessments can further fortify the data integrity in Oracle databases where PL/SQL is deployed. It is important to implement security and validation measures, such as using prepared statements and parameterized queries, to prevent these types of issues and maintain data integrity in PL/SQL.

1. Describe how data integrity is compromised
2. Identify when each type of locking is used
3. Incorporate COMMIT, ROLLBACK, and SAVEPOINT commands within PL/SQL
4. Describe how to create explicit locks with a cursor or a table lock

By adding transaction and locking controls in your PL/SQL, you have complete control over what parts of your transactions are saved to the database and when they are saved. The next module covers another important area of control, user security.

Click the Quiz link below to answer a few questions about concepts and syntax for locks.
PL/SQL Lock Timer Quiz