Transcript Chapter 6: Integrity and Security

Chapter 6:
Integrity and Security
Thomas Nikl
19 October, 2004
CS157B
Integrity!

Integrity constraints
ensure that changes
made to the database
by authorized users
do not result in loss of
data consistency.
Domain Constraints
 A domain
of possible values must be
associated with every attribute in the
database.
 Declaring an attribute of a particular
domain acts as a restraint on the values it
can take.
 They are easily tested by the system
 EX1: cannot set an integer variable to
“cat”.
Creating New Domains
 The
‘create domain’ clause allows you to
create your own domain types.
 EX1: create domain Dollars numeric(12,2)

These create numerical domains with 12 total
digits, two of which are after the decimal
point.
Referential Integrity
 Ensuring
that a value that appears in one
relation for a given set of attributes also
appears for a certain set of attributes in
another relation.
 EX1: In a banking system, the attribute
branch-name in Account-Schema is a
foreign key referencing the primary key of
Branch-Schema.
Database Modification

Inserting, deleting and updating can cause
violations of referential integrity.
 Therefore, the system must check that
referential integrity is maintained when you
perform these operations.
 If referential integrity is violated during these
operations, the default action is to reject the
operation.
 However, you can define other actions (more
later).
Referential Integrity in SQL:
Foreign Keys

Foreign Keys are
specified as part of
the SQL ‘create table’
statement by using
the ‘foreign key’
clause.
 By default, a foreign
key references the
primary key attributes
of the referenced
table.
Foreign Key Declaration

EX1:
create table account
( account-number char(10),
branch –name char(15),
balance integer,
primary key (account-number),
foreign key (branch-name) references branch,
check (balance >= 0))
Cascading
 When
referential integrity is violated during
a modification, instead of just rejecting the
modification, you can cascade:


Delete cascade
Update cascade
Delete Cascade

In a delete cascade,
anything that has
references to the
deleted item is also
deleted.
Update Cascade

In an update cascade,
when the updated
item results in a
violation of referential
integrity, the system
will update
accordingly to fix the
problem.
Defining a Cascade Operation
 EX1:
create table account
(…
foreign key (branch-name) references branch
on delete cascade
on update cascade,
…)
Assertions

An assertion is a predicate expressing a
condition that we wish the database to always
satisfy.
 Domain constraints and referential integrity
constraints are special forms of assertions.
 But there are many constraints we cannot
express by using only these special forms.
 EX1: The sum of all loan amounts for each
branch must be less than the sum of all account
balances at the branch.
Creating an Assertion
 EX1:
create assertion sum-constraint check
(not exists (select * from branch
where (select sum (amount) from loan
where loan.branch-name = branch.branch-name)
>= (select sum (balance) from account
where account.branch-name = branch.branch-name)))
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.
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 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.
This is referred to as the event-condition-action model of
triggers
Triggers
 The
database stores triggers just as if they
were regular data.
 This way they are persistent and are
accessible to all database operations.
 Once a trigger is entered into the
database, the database system takes on
the responsibility of executing it whenever
the event occurs and the condition is
satisfied.
Need for Triggers
 EX1: A good
use for a trigger would be, for
instance, if you own a warehouse and you
sell out of a particular item, to
automatically re-order that item and
automatically generate the order invoice.
 So, triggers are very useful for automating
things in your database.
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.
Database Security
 Database
Security refers to protection
from malicious access.
 Absolute protection is impossible
 Therefore, make the cost to the
perpetrator so high it will deter most
attempts.
Malicious Access

Some forms of malicious
access:




Unauthorized reading
(theft) of data
Unauthorized modification
of data
Unauthorized destruction of
data
To protect a database, we
must take security
measures at several
levels.
Security Levels

Database System: Since some users may modify data
while some may only query, it is the job of the system to
enforce authorization rules.
 Operating System: No matter how secure the database
system is, the operating system may serve as another
means of unauthorized access.
 Network: Since most databases allow remote access,
hardware and software security is crucial.
 Physical: Sites with computer systems must be
physically secured against entry by intruders or
terrorists.
 Human: Users must be authorized carefully to reduce
the chance of a user giving access to an intruder.
Authorization

For security purposes, we may assign a user
several forms of authorization on parts of the
databases which allow:





Read: read tuples.
Insert: insert new tuple, not modify existing tuples.
Update: modification, not deletion, of tuples.
Delete: deletion of tuples.
We may assign the user all, none, or a
combination of these.
Authorization
 In
addition to the previously mentioned, we
may also assign a user rights to modify the
database schema:




Index: allows creation and modification of
indices.
Resource: allows creation of new relations.
Alteration: addition or deletion of attributes in
a tuple.
Drop: allows the deletion of relations.
Authorization in SQL

The SQL language
offers a fairly powerful
mechanism for
defining
authorizations by
using privileges.
Privileges in SQL
 SQL standard





includes the privileges:
Delete
Insert
Select
Update
References: permits declaration of foreign keys.
 SQL includes
commands to grant and
revoke privileges.
Privileges in SQL
 EX1:
grant <privilege list>
on <relation or view name>
to <user>
 EX2:
grant update (amount)
on loan
to U1, U3, U4
Privilege to Grant Privileges
 By
default, a user granted privileges is not
allowed to grant those privileges to other
users.
 To allow this, we append the term “with
grant option” clause to the appropriate
grant command.
 EX1:
grant select on branch to U1 with grant option
Revoking Privileges
 To
revoke a privilege we use the ‘revoke’
clause, which is used very much like
‘grant’.
 EX1:
revoke <privilege list>
on <relation or view name>
from <user list>
Integrity: Conclusion



It is essential to ensure
that the data in a
database is accurate.
It is also important to
protect the database from
domain and referential
integrity violations.
If the data is inaccurate or
lacks integrity then the
database loses
effectiveness!
Security: Conclusion

We must also ensure
that unauthorized
users are prevented
from accessing or
modifying our
database.
 To do this, we
implement
authorization rules for
users called
privileges.
Thank You for your Attention!