Database Management System

(DBMS)

Avinash V. Gondal
email: [Link]@[Link]

1
 Module 5
Integrity and Security
in Database

2
 [Link] and Security in Database
Contents :
 Domain constraints
 Referential integrity
 Assertions
 Triggers
 Security
 Authorization
 Authorization in SQL

3
Introduction

4
 Introduction
• 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.
• We have already seen two forms of integrity constraints for
the E-R model:
– Key declarations—the stipulation that certain attributes form a
candidate key for a given entity set.
– Form of a relationship—many to many, one to many, one to one.
• In general, an integrity constraint can be an arbitrary
predicate pertaining to the database.
5
 Introduction
• However, arbitrary predicates may be costly to test. Thus,
we concentrate on integrity constraints that can be tested
with minimal overhead.

6
 Relational Integrity Constraints
• Constraints are conditions that must hold on all valid
relation states.
• There are three main types of constraints in the
relational model:
– Key constraints
– Entity integrity constraints
– Referential integrity constraints
• Another implicit constraint is the domain constraint.
– Every value in a tuple must be from the domain of its
attribute (or it could be null, if allowed for that attribute).

7
Domain Constraints

8
 Domain Constraints
• Domain constraint - Every value in a tuple must be
from the domain of its attribute (or it could be null, if
allowed for that attribute).
• Domain constraints are the most elementary form of
integrity constraint.
• They test values inserted in the database, and test
queries to ensure that the comparisons make sense.
• New domains can be created from existing data types
– E.g. create domain Dollars numeric(12, 2)
create domain Pounds numeric(12,2)

9
 Domain Constraints
• The check clause in SQL-92 permits domains to be
restricted:
– Use check clause to ensure that an hourly-wage
domain allows only values greater than a specified
value.
create domain hourly-wage numeric(5,2)
constraint value-test check(value > = 4.00)
– The domain has a constraint that ensures that the
hourly-wage is greater than 4.00.
– The clause constraint value-test is optional; useful
to indicate which constraint an update violated.
10
Types of Constraints

11
 Domain Constraints in SQL
• Required Data Constraint / Nullness Constraint :
– Null Constraints :
• Some attributes in table are not required data so such columns
can be defined as NULL column.
• By default when we CREATE TABLE, all attribute (columns)
in table are Null attributes i.e. Null value is allowed in that
field.
Ex : phone_no varchar (10)
• We can also explicitly define the attribute as NULL attribute
Ex : mobile_no varchar (10) NULL
– Not Null Constraints :
• For certain attributes Null values are not allowed in such cases
we can restrict the null entries with help of NOT NULL
constraint.
12
Ex : Dependent_Name varchar (15) NOT NULL
 Domain Constraints in SQL
• Check Constraint :
– Used to specify user-defined constraints.
– Assume that dept. numbers are from 0 to 99.
create table DEPARTMENT (
…
Dnumber INTEGER Default 0
CHECK (Dnumber>=0 AND Dumber<=99),
…);
– CHECK clause can also be used in conjunction with the
CREATE DOMAIN STATEMENT
CREATE DOMAIN D_NUM AS INTEGER
CHECK (D_NUM>0 AND D_NUM <21);
13
 Domain Constraints in SQL
• DEFAULT KEYWORD :
– It is also possible to define a default value for an attribute
by appending the clause DEFAULT <value> to an
attribute definition.
– The default value is included in any new tuple if an
explicit value is not provided for that attribute.
– Figure C, illustrates an example of specifying a default
manager for a new department and a default department
for a new employee.
– If no default clause is specified, the default default value
is NULL for attributes that do not have the NOT NULL
constraint.
14
 Domain Constraints in SQL
• DEFAULT KEYWORD :

15
 Domain Constraints in SQL
• Unique Constraint :
– In case of unique constraint no two tuples can have equal
value for same attribute.
– The UNIQUE clause specifies alternate (secondary)
keys, as illustrated in the DEPARTMENT and
PROJECT table declarations in Figure B.
– The UNIQUE clause can also be specified directly for a
secondary key if the secondary key is a single attribute,
as in the following example:
Dname VARCHAR(15) UNIQUE;

16
 Domain Constraints in SQL
• Unique Constraint :

17
 Key Constraints
• If a relation has several candidate keys, one is chosen
arbitrarily to be the primary key.
– The primary key attributes are underlined.
• Example: Consider the CAR relation schema:
– CAR(State, Reg, SerialNo, Make, Model, Year)
– We chose SerialNo as the primary key.
• The primary key value is used to uniquely identify each
tuple in a relation.
– Provides the tuple identity.
• Also used to reference the tuple from another tuple.
– General rule: Choose as primary key the smallest of the
candidate keys (in terms of size).
– Not always applicable – choice is sometimes subjective.

18
 Key Constraints
• The PRIMARY KEY clause specifies one or more attributes
that make up the primary key of a relation. If a primary key has a
single attribute, the clause can follow the attribute directly.
• For example, the primary key of DEPARTMENT can be
specified as follows (instead of the way it is specified in Figure
B):
Dnumber INT PRIMARY KEY;
• The UNIQUE clause specifies alternate (secondary) keys, as
illustrated in the DEPARTMENT and PROJECT table
declarations in Figure B.
• The UNIQUE clause can also be specified directly for a
secondary key if the secondary key is a single attribute, as in the
following example:
Dname VARCHAR(15) UNIQUE; 19
 Entity Integrity
• Entity Integrity:
– The primary key attributes PK of each relation schema R
in S cannot have null values in any tuple of r(R).
• This is because primary key values are used to identify the
individual tuples.
• t[PK]  null for any tuple t in r(R)
• If PK has several attributes, null is not allowed in any of these
attributes
– Key constraints and entity integrity constraints are
specified on individual relations.
– Note: Other attributes of R may be constrained to
disallow null values, even though they are not members
of the primary key.
20
 Referential Integrity
• A constraint involving two relations
– The previous constraints involve a single relation.
• Used to specify a relationship among tuples in two
relations:
– The referencing relation and the referenced relation.

21
 Referential Integrity
• Tuples in the referencing relation R1 have attributes
FK (called foreign key attributes) that reference the
primary key attributes PK of the referenced relation
R2.
– A tuple t1 in R1 is said to reference a tuple t2 in R2
if t1[FK] = t2[PK].
• A referential integrity constraint can be displayed in a
relational database schema as a directed arc from
[Link] to R2.

22
 Referential Integrity
• Statement of the constraint
– The value in the foreign key column (or columns)
FK of the referencing relation R1 can be either:
• (1) a value of an existing primary key value of a
corresponding primary key PK in the referenced relation
R2, or
• (2) a null.
• In case (2), the FK in R1 should not be a part of its own
primary key.

23
 Referential Integrity Options
• Causes of referential integrity violation for a foreign
key FK (consider the Mgr_ssn of DEPARTMENT).
– ON DELETE: when deleting the foreign tuple
• What to do when deleting the manager tuple in EMPLOYEE ?
– ON UPDATE: when updating the foreign tuple
• What to do when updating/changing the SSN of the
manager tuple in EMPLOYEE is changed ?
• Actions when the above two causes occur.
– SET NULL: the Mgr_ssn is set to null.
– SET DEFAULT: the Mgr_ssn is set to the default
value.
– CASCADE: the Mgr_ssn is updated accordingly
• If the manager is deleted, the department is also deleted.
24
 Displaying a relational database
schema and its constraints
• Each relation schema can be displayed as a row of
attribute names.
• The name of the relation is written above the attribute
names.
• The primary key attribute (or attributes) will be
underlined.
• A foreign key (referential integrity) constraints is
displayed as a directed arc (arrow) from the foreign key
attributes to the referenced table.
– Can also point the primary key of the referenced relation for
clarity.
25
 Other Types of Constraints
• The preceding integrity constraints are included in the
data definition language because they occur in most
database applications.
• However, they do not include a large class of general
constraints, sometimes called semantic integrity
constraints, which may have to be specified and
enforced on a relational database.
• Examples of such constraints are
- the salary of an employee should not exceed the
salary of the employee’s supervisor .
- the maximum number of hours an employee can
work on all projects per week is 56. 26
 Other Types of Constraints
• Such constraints can be specified and enforced within
the application programs that update the database, or by
using a general-purpose constraint specification
language.
• Mechanisms called triggers and assertions can be
used. In SQL, CREATE ASSERTION and CREATE
TRIGGER statements can be used for this purpose.

27
 Other Types of Constraints
• Another type of constraint is the functional dependency
constraint, which establishes a functional relationship
among two sets of attributes X and Y.
• This constraint specifies that the value of X determines
a unique value of Y in all states of a relation; it is
denoted as a functional dependency X → Y.
• We use functional dependencies and other types of
dependencies in next chapter.

28
 Other Types of Constraints
• The types of constraints we discussed so far may be called
state constraints because they define the constraints that a
valid state of the database must satisfy.
• Another type of constraint, called transition constraints,
can be defined to deal with state changes in the database.
• An example of a transition constraint is: ―the salary of an
employee can only increase.‖
• Such constraints are typically enforced by the application
programs or specified using active rules and triggers.
• Note : State constraints are sometimes called static
constraints, and transition constraints are sometimes called
dynamic constraints.
29
Assertions

30
 Assertions
• Assertions 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) .
• These built-in constraints can be specified within the
CREATE TABLE statement of SQL.
• An assertion is a predicate expressing a condition that
we wish the database always to satisfy.
• An assertion in SQL takes the form:
create assertion <assertion-name> check <predicate>

31
 Constraints as Assertions
• Mechanism: CREAT ASSERTION
– Components include:
• a constraint name,
• followed by CHECK,
• followed by a condition

32
 Assertions: An Example
• ―The salary of an employee must not be greater than the
salary of the manager of the department that the employee
works for’’ Constraint
name,
CHECK,
CREAT ASSERTION SALARY_CONSTRAINT
condition
CHECK (NOT EXISTS (SELECT *
FROM EMPLOYEE E, EMPLOYEE M,
DEPARTMENT D
WHERE [Link] > [Link] AND
[Link]=[Link] AND
[Link]=[Link]))
 Using General Assertions
• The basic technique for writing such assertions is to specify
a query that selects any tuples that violate the desired
condition.
• By including this query inside a NOT EXISTS clause, the
assertion will specify that the result of this query must be
empty so that the condition will always be TRUE.
– The assertion is violated if the result of the query is not
empty.
• In the preceding example, the query selects all employees
whose salaries are greater than the salary of the manager of
their department. If the result of the query is not empty, the
assertion is violated.

34
 Creating an Assertion
• When an assertion is created, the system will test it for
validity.
• If the assertion is valid, then any future modification to
the database is allowed only if it does not cause the
assertion to be violated.
• But assertions can create a considerable amount of
overhead, especially if complex assertions have been
made.
• Therefore, assertions should only be used with great
care.

35
Difference between Check and Assertion
• A major difference between assertion and check on
attributes or domains is that check is executed only
when tuples are inserted or updated.
• Assertion should be used in cases where it is not
possible to use CHECK on attributes, domains or
tuples.

36
Triggers

37
 Triggers
• A trigger is a statement that the system executes
automatically as a side effect of a modification to the
database.
• To design a trigger mechanism, we must meet two
requirements:
1. Specify when a trigger is to be executed. This is
broken up into an event that causes the trigger to
be checked and a condition that must be satisfied
for trigger execution to proceed.
2. Specify the actions to be taken when the trigger
executes.
• The above model of triggers is referred to as the event-
condition-action model for triggers. 38
 Triggers
• The database stores triggers just as if they were regular
data, so that they are persistent and are accessible to all
database operations.
• Once we enter a trigger into the database, the database
system takes on the responsibility of executing it
whenever the specified event occurs and the
corresponding condition is satisfied.

39
Event-Condition-Action (ECA) Model
• Triggers follow an Event-condition-action
(ECA) model.
– Event:
• Database modification
– E.g., insert, delete, update
– Condition:
• Any true/false expression
– Optional: If no condition is specified then condition is
always true
– Action:
• Sequence of SQL statements that will be
automatically executed 40
 Trigger Example
• When a new employees is added to a department,
modify the Total_sal of the Department to include the
new employees salary Condition
– Logically this means that we will CREATE a TRIGGER,
let us call the trigger Total_sal1
• This trigger will execute AFTER INSERT ON Employee
table
• It will do the following FOR EACH ROW
– WHEN [Link] is NOT NULL
– The trigger will UPDATE DEPARTMENT
– By SETting the new Total_sal to be the sum of
• old Total_sal and NEW. Salary
• WHERE the Dno matches the [Link];

41
 Example: Trigger Definition
Can be CREATE or ALTER

CREATE TRIGGER Total_sal1 Can be FOR, AFTER,

BEFORE, INSTEAD OF
AFTER INSERT ON Employee
Can be INSERT,
FOR EACH ROW
UPDATE, DELETE
WHEN ([Link] is NOT NULL)
The condition
UPDATE DEPARTMENT
SET Total_sal = Total_sal + NEW. Salary
WHERE Dno = [Link];

The action
 CREATE or ALTER TRIGGER
• CREATE TRIGGER <name>
– Creates a trigger
• ALTER TRIGGER <name>
– Alters a trigger (assuming one exists)
• CREATE OR ALTER TRIGGER <name>
– Creates a trigger if one does not exist
– Alters a trigger if one does exist
• Works in both cases, whether a trigger exists or not

43
 Conditions
• AFTER
– Executes after the event
• BEFORE
– Executes before the event
• INSTEAD OF
– Executes instead of the event
• Note that event does not execute in this case
– E.g., used for modifying views

44
 Row-Level versus Statement-level
• Triggers can be
– Row-level
• FOR EACH ROW specifies a row-level trigger
– Statement-level
• Default (when FOR EACH ROW is not specified)
• Row level triggers
– Executed separately for each affected row
• Statement-level triggers
– Execute once for the SQL statement,
• Note : The keywords NEW and OLD can only be
used with row-level triggers.
45
 When Not To Use Triggers
• Triggers should be written with great care, since a
trigger error detected at run time causes the failure of
the insert/delete/update statement that set off the trigger.
• Furthermore, the action of one trigger can set off
another trigger. In the worst case, this could even lead
to an infinite chain of triggering.
• For example, it is quite difficult to verify that a set of
rules is consistent, meaning that two or more rules in
the set do not contradict one another.
• It is also difficult to guarantee termination of a set of
rules under all circumstances. To illustrate the
termination problem briefly, consider the rules
46
 When Not To Use Triggers
• R1: CREATE TRIGGER T1
AFTER INSERT ON TABLE1
FOR EACH ROW
UPDATE TABLE2
SET Attribute1 = ... ;
• R2: CREATE TRIGGER T2
AFTER UPDATE OF Attribute1 ON TABLE2
FOR EACH ROW
INSERT INTO TABLE1 VALUES ( ... );
• Here, rule R1 is triggered by an INSERT event on TABLE1 and
its action includes an update event on Attribute1 of TABLE2.
• However, rule R2’s triggering event is an UPDATE event on
Attribute1 of TABLE2, and its action includes an INSERT event
on TABLE1. 47
 When Not To Use Triggers
• In this example, it is easy to see that these two rules can
trigger one another indefinitely, leading to non-
termination.
• However, if dozens of rules are written, it is very
difficult to determine whether termination is guaranteed
or not.
• If active rules (triggers ) are to reach their potential, it is
necessary to develop tools for the design, debugging,
and monitoring of active rules that can help users
design and debug their rules.

48
Security

49
 Security
• The information in your database is important.
• Therefore, you need a way to protect it against unauthorized
access, malicious destruction or alteration, and accidental
introduction of data inconsistency.
• Security Goals: confidentiality, integrity and availability.
– Confidentiality : Data in database should be given to only
authorized users.
• Ex- In HR department employee’s personal data should be accessible
to that particular employee and HR person only.
– Integrity : Only authorized users should be allowed to modify the
data.
• Ex- Only account department can change financial details of company.
– Availability : Authorized users can be able to access the data at any
time they wants.
• Ex – Employee should be able to access own salary at any time.
50
 Security
• Database security refers to protection from malicious
access.
• To protect the database, we must take security measures
at several levels:
– Database system level
• Authentication and authorization mechanisms to allow specific users
access only to required data.
– Operating system level
• Operating system super-users can do anything they want to the
database. Good operating system level security is required.
– Network level: must use encryption to prevent
• Eavesdropping (unauthorized reading of messages).
• Masquerading (pretending to be an authorized user or sending
messages supposedly from authorized users).
51
 Security
– Physical level
• Physical access to computers allows destruction of data by intruders;
traditional lock-and-key security is needed.
• Computers must also be protected from floods, fire, etc.
– Human level
• Users must be screened to ensure that an authorized users do not give
access to intruders.
• Users should be trained on password selection and secrecy.

52
Security Mechanisms (Control Measures)
• Following main control measures are used to provide
security of data in databases:
– Access Control (Authorization) :
• The security mechanism of a DBMS must include provisions
for restricting access to the database as a whole.
– This function is called access control and is handled by creating
user accounts and passwords to control login process by the
DBMS.
– Ex – ROLE function in SQL.
– Authenticate the User
• Which identify the valid users who may have any access to the
data in the database.
• Restrict each user’s view of the data in the database.
• This may be done with the help of concept of views in relational
databases.
53
Security Mechanisms (Control Measures)
– Inference Control :
• The security problem associated with databases is that of
controlling the access to a statistical database, which is used to
provide statistical information or summaries of values based on
various criteria.
– The countermeasures to statistical database security problem is
called inference control measures.
– Security for statistical databases must ensure that information
about individuals cannot be accessed.
– Flow Control or Physical Protection :
• Another security is that of flow control, which prevents
information from flowing in such a way that it reaches
unauthorized users.
– Prevents the copying of information by unauthorized users.
– Computer systems must be physically secured against any
unauthorized entry.
54
Security Mechanisms (Control Measures)
– Cryptographic Control (Data Encryption) :
• Data encryption is used to protect sensitive data (such as credit
card numbers) that is being transmitted via some type
communication network.
• The data is encoded using some encoding algorithm.
– An unauthorized user who access encoded data will have
difficulty in deciphering it, but authorized users are given
decoding or decrypting algorithms (or keys) to decipher data.
– User Defined Control :
• Define additional constraints or limitations on the use of
database.
• This allow programmers or developers to incorporate their own
security procedures in addition to above security mechanisms.

55
Authorization

56
 Authorization
• We may assign a user several forms of authorization on
parts of the database. For example,
– Read authorization - allows reading, but not
modification, of data.
– Insert authorization - allows insertion of new data, but
not modification of existing data.
– Update authorization - allows modification, but not
deletion, of data.
– Delete authorization - allows deletion of data.
• We may assign the user all, none, or a combination of
these types of authorization.

57
 Authorization
• In addition to the previously mentioned forms of
authorization , we may grant a user authorization to
modify the database schema:
– Index authorization - allows the creation and deletion of
indices.
– Resource authorization - allows the creation of new
relations.
– Alteration authorization - allows the addition or deletion
of attributes in a relation.
– Drop authorization - allows the deletion of relations.

58
Authorization in
SQL
59
 Authorization in SQL
• The SQL language offers a fairly powerful
mechanism for defining authorizations by using
privileges.

60
 Security Specification in SQL
• The grant statement is used to confer authorization
grant <privilege list>
on <relation name or view name> to <user list>
• <user list> is:
– a user-id
– public, which allows all valid users the privilege granted.
– A role (more on this later).
• Granting a privilege on a view does not imply granting
any privileges on the underlying relations.
• The grantor of the privilege must already hold the
privilege on the specified item (or be the database
administrator). 61
 Privileges in SQL
• select: allows read access to relation, or the ability to query using
the view.
– Example: grant users U1, U2, and U3 select authorization on
the branch relation:
grant select on branch to U1, U2, U3
• insert: the ability to insert tuples.
• update: the ability to update using the SQL update statement.
• delete: the ability to delete tuples.
• references: ability to declare foreign keys when creating
relations.
• usage: In SQL-92; authorizes a user to use a specified domain.
• all privileges: used as a short form for all the allowable
privileges.
62
 Privilege To Grant Privileges
• with grant option: allows a user who is granted a
privilege to pass the privilege on to other users.
– Example:
grant select on branch to U1 with grant option
gives U1 the select privileges on branch and allows U1 to
grant this privilege to others

63
 Roles
• Roles permit common privileges for a class of users can be
specified just once by creating a corresponding ―role‖.
• Privileges can be granted to or revoked from roles, just like user.
• Roles can be assigned to users, and even to other roles.
• SQL:1999 supports roles.
create role teller
create role manager

grant select on branch to teller

grant update (balance) on account to teller
grant all privileges on account to manager

grant teller to manager

grant teller to alice, bob

grant manager to avi
64
 Revoking Authorization in SQL
• The revoke statement is used to revoke authorization.
revoke<privilege list>
on <relation name or view name> from <user list> [restrict | cascade]
• Example:
revoke select on branch from U1, U2, U3 cascade
• Revocation of a privilege from a user may cause other users also to
lose that privilege; referred to as cascading of the revoke.
• We can prevent cascading by specifying restrict:
revoke select on branch from U1, U2, U3 restrict
With restrict, the revoke command fails if cascading revokes are
required.

65
Authentication
in SQL
66
 Authentication
• Authentication refers to the task of verifying the identity of a
person/software connecting to a database.
• The simplest form of authentication consists of a secret password
which must be presented when a connection is opened to a
database.
• Password based authentication is widely used, but is susceptible to
sniffing on a network.
• Challenge-response systems avoid transmission of passwords.
– DB sends a (randomly generated) challenge string to user.
– User encrypts string and returns result.
– DB verifies identity by decrypting result.
– Can use public-key encryption system by DB sending a message
encrypted using user’s public key, and user decrypting and sending
the message back.
67
 Authentication
• Digital signatures are used to verify authenticity of data.
– E.g. use private key (in reverse) to encrypt data, and anyone can
verify authenticity by using public key (in reverse) to decrypt
data. Only holder of private key could have created the
encrypted data.
– Digital signatures also help ensure nonrepudiation: sender
cannot later claim to have not created the data.

68
 University Questions
1. Discuss different security and authorization mechanism in
Database Management System [3T-10M]
2. Write Short notes [5 marks each]
(i) Triggers [7T]
(ii) Security and Authorization in DBMS [2T]
(iii) Authorization in SQL [1T]
(iv) Integrity Constraints [2T]
(v) Foreign key and referential integrity [2T]
(vi) Security Mechanism [2T]
(vii) Assertion [3T]
3. Define Entity integrity and Referential integrity with examples.
[1T-5M]
4. Explain various types of constraints with example [2T-10M]
69
 University Questions
5. What are triggers? Give an example. Illustrate the cases
when triggers must not be used. [3T-10M]
6. What do you mean by authorization and authentication in
DBMS? Explain how it is implemented in SQL with suitable
example. [2T-10M]

70