Database Security and Authorization

In any database system, protecting the data from unauthorized access, modification, or destruction is paramount. This area is broadly covered under Database Security. A crucial aspect of database security is ensuring the integrity of the data, which means maintaining its accuracy and consistency over its entire lifecycle. Authorization is the process of granting specific permissions to users or roles, defining what actions they can perform on the database.

Integrity Constraints

Integrity constraints are rules enforced on data columns to ensure the accuracy and consistency of data in a database. They prevent invalid data from being entered into the database, thereby protecting the integrity of the data. These constraints are defined at the table level and are checked by the database management system (DBMS) whenever data is inserted, updated, or deleted.

Types of Integrity Constraints

There are several types of integrity constraints, each serving a specific purpose in maintaining data quality.

1. Domain Constraints

Domain constraints ensure that the values entered into a column are of the correct data type and within a valid range or set of values. This is the most fundamental type of integrity constraint.

• Data Type: For example, a column designed to store age should only accept integer values, not text or floating-point numbers.
• Format: For date columns, a specific format like YYYY-MM-DD might be enforced.
• Range: A column for 'Percentage' might be restricted to values between 0 and 100.
• Set of Values: A 'Gender' column might only allow 'Male', 'Female', or 'Other'.

These constraints are typically enforced by specifying the data type of a column (e.g., INT, VARCHAR, DATE) and can be further refined using CHECK constraints.

2. Entity Integrity

Entity integrity ensures that each row in a table is uniquely identifiable. This is primarily achieved through the use of primary keys.

• A primary key column (or a set of columns) cannot contain NULL values.
• Each value in the primary key must be unique.

If a primary key column were allowed to be NULL, it would be impossible to uniquely identify that particular record, violating the principle of entity integrity.

3. Referential Integrity

Referential integrity deals with the relationships between tables. It ensures that foreign key values in one table correctly reference existing primary key values in another table. This prevents "orphan records" – records in a child table that point to non-existent records in a parent table.

A foreign key is a column (or a set of columns) in one table that refers to the primary key in another table. For referential integrity to hold:

• Every foreign key value must either be NULL or match an existing primary key value in the referenced table.
• A primary key value cannot be deleted or updated if there are any foreign key values referencing it, unless specific actions are defined (like CASCADE or SET NULL).

Example: Consider two tables: `Customers` (with `CustomerID` as primary key) and `Orders` (with `OrderID` as primary key and `CustomerID` as a foreign key referencing `Customers`). Referential integrity ensures that an order can only be associated with a valid customer. You cannot add an order with a `CustomerID` that does not exist in the `Customers` table. Similarly, you cannot delete a customer if they have existing orders, unless the system is configured to handle this scenario (e.g., by deleting the orders too, or setting the `CustomerID` in the `Orders` table to NULL).

4. Check Constraints

Check constraints are the most flexible type of integrity constraint. They allow you to define a condition that must be true for the data in a specific column or set of columns. If the condition evaluates to false, the data modification (INSERT or UPDATE) is rejected.

Check constraints can be used to enforce domain integrity beyond basic data types and ranges. They can involve multiple columns or more complex logical expressions.

Syntax Example (SQL):

To ensure an `Age` column is between 18 and 65:

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(100), Age INT, CHECK (Age >= 18 AND Age <= 65) );

To ensure a `Salary` is always positive and greater than `Bonus`:

CREATE TABLE Compensation ( EmpID INT, Salary DECIMAL(10, 2), Bonus DECIMAL(10, 2), CHECK (Salary > 0 AND Salary > Bonus) );

Check constraints are evaluated before an INSERT or UPDATE operation. If the condition is not met, the operation fails, and an error is returned.

Referential Constraints (Foreign Key Constraints)

Referential constraints are specifically implemented using foreign keys to enforce referential integrity. When you define a foreign key, you specify the column(s) in the child table and the referenced primary key column(s) in the parent table. You can also define actions to be taken when the referenced primary key is updated or deleted.

Actions on Update/Delete

When a record in the parent table (the one with the primary key) is modified or deleted, the DBMS needs to know how to handle the corresponding records in the child table (the one with the foreign key). The common actions are:

• NO ACTION / RESTRICT: This is often the default. If a parent record is about to be deleted or its primary key updated, and there are child records referencing it, the operation is rejected with an error.
• CASCADE: If the parent record is deleted, all corresponding child records are also deleted. If the parent record's primary key is updated, the foreign key values in the child records are updated to match.
• SET NULL: If the parent record is deleted or its primary key is updated, the foreign key values in the corresponding child records are set to NULL. This is only possible if the foreign key column(s) are nullable.
• SET DEFAULT: If the parent record is deleted or its primary key is updated, the foreign key values in the corresponding child records are set to their default value. This requires a default value to be defined for the foreign key column(s).

Syntax Example (SQL):

Creating an `Orders` table with a foreign key referencing `Customers`, with `ON DELETE CASCADE` and `ON UPDATE NO ACTION`:

CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderDate DATE, CustomerID INT, -- Foreign Key definition FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE ON UPDATE NO ACTION );

In this example, if a customer is deleted from the `Customers` table, all their associated orders in the `Orders` table will also be deleted automatically. If a customer's `CustomerID` were to be updated (which is generally discouraged for primary keys), the `Orders` table would not automatically update, and the operation would likely fail if referential integrity is violated.

Views

A view is a virtual table based on the result-set of a SQL statement. It contains rows and columns that are derived from one or more underlying base tables. Views do not store data themselves; they dynamically retrieve data from the base tables when queried.

Views are useful for several reasons:

• Simplification: Complex queries involving multiple joins can be encapsulated into a view, making it easier for users to query the data.
• Security: Views can be used to restrict access to specific rows or columns of a table. A user can be granted permission to access a view, but not the underlying tables, thereby controlling what data they can see.
• Data Abstraction: Views provide a layer of abstraction, allowing the underlying table structures to be changed without affecting the applications that use the views (as long as the view definition remains compatible).

Syntax Example (SQL):

Creating a view to show only the names and emails of employees in the 'Sales' department:

CREATE VIEW SalesEmployees AS SELECT EmployeeName, Email FROM Employees WHERE Department = 'Sales';

Now, you can query this view as if it were a table:

SELECT * FROM SalesEmployees;

Updates on Views

While views are virtual, it is sometimes possible to modify the data in the underlying base tables through a view. This is known as updating a view. However, not all views are updatable. The ability to update a view depends on its complexity and how it is defined.

Conditions for Updatable Views

A view is generally considered updatable if it meets certain criteria:

• The view must be based on only one base table.
• The view must not use aggregate functions (like SUM, AVG, COUNT, MAX, MIN).
• The view must not use the DISTINCT keyword.
• The view must reference all NOT NULL columns from the base table that do not have default values. If a base table has a NOT NULL column without a default value, and that column is not included in the view, then inserting a new row through the view would fail because that column would have to be NULL, violating the constraint.
• The view must not involve calculations or expressions in the SELECT list that derive columns from the base table columns. For example, if a view selects `EmployeeName || ' ' || LastName` as `FullName`, you cannot update `FullName` directly to change the employee's name.
• The view must not use GROUP BY or HAVING clauses.

Performing Updates

If a view is updatable, you can use standard SQL `INSERT`, `UPDATE`, and `DELETE` statements on the view as if it were a table. The changes made to the view are then applied to the underlying base table(s).

Example: Using the `SalesEmployees` view created earlier (assuming it's based on a single table `Employees` and meets the updatability criteria):

Updating a record:

UPDATE SalesEmployees SET Email = 'new.sales@example.com' WHERE EmployeeName = 'Alice';

This statement would update the `Email` column for the employee named 'Alice' in the underlying `Employees` table.

Inserting a new record:

INSERT INTO SalesEmployees (EmployeeName, Email) VALUES ('Bob', 'bob.sales@example.com');

This would insert a new row into the `Employees` table. If the `Employees` table has other columns (like `Department`, `EmployeeID`) that are not part of the `SalesEmployees` view, these columns must either have default values defined or be NULLable, and the system might require you to provide values for them if they are NOT NULL and have no default.

Deleting a record:

DELETE FROM SalesEmployees WHERE EmployeeName = 'Alice';

This would delete the row for 'Alice' from the underlying `Employees` table.

Non-Updatable Views

If a view is not updatable (e.g., it involves joins, aggregations, or complex expressions), attempting to perform INSERT, UPDATE, or DELETE operations on it will result in an error.

For complex views that are not directly updatable, database designers often create stored procedures or triggers. These mechanisms can intercept the request to modify data through the view and translate it into the necessary operations on the underlying base tables, potentially performing additional validation or logic.

In some advanced scenarios, databases support updatable views that are based on multiple tables, provided that the update is unambiguous. For instance, if a view joins two tables on their primary keys, and you update a column in one of the tables that is part of the view's selection, the update might be allowed. However, these cases are more complex and depend heavily on the specific DBMS implementation.

Understanding the conditions under which views are updatable is crucial for both maintaining data integrity and designing efficient database applications. When designing a view, consider whether data modification through the view is a requirement. If it is, ensure the view definition adheres to the updatability rules.