Integrity, Security and Relational Database

Design
 Integrity Constraints
• Integrity constraints ensure that changes made to the database by authorized users do not result in a
loss of data consistency.

• Thus, integrity constraints guard against accidental damage to the database.

• This is in contrast to security constraints, which guard against access to the database by unauthorized
users.

• Examples of integrity constraints are:

– An instructor name cannot be null.
– No two instructors can have the same instructor ID.
– Every department name in the course relation must have a matching department name in the department
relation.
– The budget of a department must be greater than $0.00.
 Constraints on a Single Relation
• The create table command may also include integrity-constraint statements. The allowed integrity constraints include
– not null
– unique
– check(<predicate>)

• Not Null Constraint

– The not null constraint prohibits the insertion of a null value for the attribute, and is an example of a domain constraint.
– Any database modification that would cause a null to be inserted in an attribute declared to be not null generates an error diagnostic.

• Unique Constraint
– SQL also supports an integrity constraint:
– The unique specification says that attributes A j1 , A j2 , … , Ajm form a superkey; that is, no two tuples in the relation can be equal on all the
listed attributes.
– However, attributes declared as unique are permitted to be null unless they have explicitly been declared to be not null.

• The Check Clause

– A common use of the check clause is to ensure that attribute values satisfy specified conditions, in effect creating a powerful type system.
• For instance, a clause check (budget > 0) in the create table command for relation department would ensure that the value of budget is nonnegative.
 Referential Integrity
• Often, we wish to ensure that a value that appears in one relation (the referencing relation) for a
given set of attributes also appears for a certain set of attributes in another relation (the referenced
relation).

• Such conditions are called referential integrity constraints, and foreign keys are a form of a
referential integrity constraint where the referenced attributes form a primary key of the referenced
relation.
 Assertions
• CREATE ASSERTION, which can be used to specify additional types of constraints that are outside the scope
of the built-in relational model constraints (primary and unique keys, entity integrity, and referential integrity)

• In SQL, users can specify general constraints—via declarative assertions, using the CREATE ASSERTION
statement of the DDL.

• Each assertion is given a constraint name and is specified via a condition similar to the WHERE clause of an
SQL query.

• For example, to specify the constraint that the salary of an employee must not be greater than the salary of the
manager of the department that the employee works for in SQL, we can write the following assertion:
 Assertions
• An assertion is a predicate expressing a condition that we wish the database
always to satisfy.
 Triggers in SQL
• Another important statement in SQL is CREATE TRIGGER.
• In many cases it is convenient to specify the type of action to be taken
when certain events occur and when certain conditions are satisfied.
• Suppose we want to check whenever an employee’s salary is greater than
the salary of his or her direct supervisor in the COMPANY database.
 Authorization
• We may assign a user several forms of authorizations on parts of the database.

• Authorizations on data include:

– Authorization to read data.
– Authorization to insert new data.
– Authorization to update data.
– Authorization to delete data.

• Each of these types of authorizations is called a privilege.

• We may authorize the user all, none, or a combination of these types of privileges on specified parts of a database, such as a
relation or a view.

• When a user submits a query or an update, the SQL implementation first checks if the query or update is authorized, based on the
authorizations that the user has been granted. If the query or update is not authorized, it is rejected.

• In addition to authorizations on data, users may also be granted authorizations on the database schema, allowing them, for
example, to create, modify, or drop relations.

• The ultimate form of authority is that given to the database administrator.

 Granting and Revoking of Privileges
• The SQL standard includes the privileges select, insert,
update, and delete.
• The SQL data-definition language includes commands to
grant and revoke privileges.
• The SQL authorization mechanism grants privileges on an
entire relation, or on specified attributes of a relation.
However, it does not permit authorizations on specific
tuples of a relation.
• To revoke an authorization, we use the revoke statement. It
takes a form almost identical to that of grant:
 Roles
• A set of roles is created in the database.

• Authorizations can be granted to roles, in exactly the same fashion as they are granted to individual
users.

• Each database user is granted a set of roles (which may be empty) that she is authorized to perform.

• In our university database, examples of roles could include instructor, teaching assistant, student,
dean, and department chair.

• Roles can be created in SQL as follows:

– create role instructor;

• Roles can then be granted privileges just as the users can, as illustrated in this statement:
– grant select on takes to instructor;
 Authentication
• Authentication refers to the task of verifying the identity of a
person/software connecting to an application.

• The simplest form of authentication consists of a secret password

that must be presented when a user connects to the application.

• Unfortunately, passwords are easily compromised, for example, by

guessing, or by sniffing of packets on the network if the passwords
are not sent encrypted.
 Audit Trails
• An audit trail is a log of all changes (inserts, deletes, and updates) to the
application data, along with information such as which user performed the change
and when the change was performed.
• If application security is breached, or even if security was not breached, but some
update was carried out erroneously, an audit trail can
– help find out what happened, and who may have carried out the actions, and
– aid in fixing the damage caused by the security breach or erroneous update.

• For example, if a student’s grade is found to be incorrect, the audit log can be
examined to locate when and how the grade was updated, as well as to find which
user carried out the updates.
 Encryption and Decryption
• Encryption refers to the process of transforming data into a form that is
unreadable, unless the reverse process of decryption is applied. Encryption
algorithms use an encryption key to perform encryption,
• and they require a decryption key (which could be the same as the encryption key,
depending on the encryption algorithm used) to perform decryption.
• In a symmetric-key encryption technique, the encryption key is also used to
decrypt data.
• In contrast, in public-key (also known as asymmetric-key) encryption techniques,
there are two different keys, the public key and the private key, used to encrypt
and decrypt the data.