Chapter 6: Integrity and Security
n Domain Constraints
n Referential Integrity
n Assertions
n Triggers
n Security
n Authorization
n Authorization in SQL
Domain Constraints
n Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency.
n Domain constraints are the most elementary form of integrity constraint.
n They test values inserted in the database, and test queries to ensure that the comparisons make sense.
n New domains can be created from existing data types
H E.g. create domain Dollars numeric(12, 2)
create domain Pounds numeric(12,2)
n We cannot assign or compare a value of type Dollars to a value of type Pounds.
H However, we can convert type as below
(cast r.A as Pounds)
(Should also multiply by the dollar-to-pound conversion-rate)
n The check clause in SQL-92 permits domains to be restricted:
H 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)
H The domain has a constraint that ensures that the hourly-wage is greater than 4.00
H The clause constraint value-test is optional; useful to indicate which constraint an update violated.
n Can have complex conditions in domain check
H create domain AccountType char(10)
constraint account-type-test
check (value in (‘Checking’, ‘Saving’))
H check (branch-name in (select branch-name from branch))
Referential Integrity
n Ensures 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.
H Example: If “Perryridge” is a branch name appearing in one of the tuples in the account relation, then there exists a tuple in the branch relation for branch “Perryridge”.
n Formal Definition
H Let r1(R1) and r2(R2) be relations with primary keys K1 and K2 respectively.
H The subset a of R2 is a foreign key referencing K1 in relation r1, if for every t2 in r2 there must be a tuple t1 in r1 such that t1[K1] = t2[a].
H Referential integrity constraint also called subset dependency since its can be written as
Õa (r2) Í ÕK1 (r1)
Referential Integrity in the E-R Model
n Consider relationship set R between entity sets E1 and E2. The relational schema for R includes the primary keys K1 of E1 and K2 of E2.
Then K1 and K2 form foreign keys on the relational schemas for E1 and E2 respectively.
n Weak entity sets are also a source of referential integrity constraints.
H For the relation schema for a weak entity set must include the primary key attributes of the entity set on which it depends
Checking Referential Integrity on Database Modification
n The following tests must be made in order to preserve the following referential integrity constraint:
Õa (r2) Í ÕK (r1)
n Insert. If a tuple t2 is inserted into r2, the system must ensure that there is a tuple t1 in r1 such that t1[K] = t2[a]. That is
t2 [a] Î ÕK (r1)
n Delete. If a tuple, t1 is deleted from r1, the system must compute the set of tuples in r2 that reference t1:
sa = t1[K] (r2)
If this set is not empty
H either the delete command is rejected as an error, or
H the tuples that reference t1 must themselves be deleted
(cascading deletions are possible).
Database Modification (Cont.)
n Update. There are two cases:
H If a tuple t2 is updated in relation r2 and the update modifies values for foreign key a, then a test similar to the insert case is made:
4 Let t2’ denote the new value of tuple t2. The system must ensure that
t2’[a] Î ÕK(r1)
H If a tuple t1 is updated in r1, and the update modifies values for the primary key (K), then a test similar to the delete case is made:
4 The system must compute
sa = t1[K] (r2)
using the old value of t1 (the value before the update is applied).
4 If this set is not empty
– the update may be rejected as an error, or
– the update may be cascaded to the tuples in the set, or
– the tuples in the set may be deleted.
Referential Integrity in SQL
n Primary and candidate keys and foreign keys can be specified as part of the SQL create table statement:
H The primary key clause lists attributes that comprise the primary key.
H The unique key clause lists attributes that comprise a candidate key.
H The foreign key clause lists the attributes that comprise the foreign key and the name of the relation referenced by the foreign key.
n By default, a foreign key references the primary key attributes of the referenced table
foreign key (account-number) references account
n Short form for specifying a single column as foreign key
account-number char (10) references account
n Reference columns in the referenced table can be explicitly specified
H but must be declared as primary/candidate keys
foreign key (account-number) references account(account-number)
Referential Integrity in SQL – Example
create table customer
(customer-name char(20),
customer-street char(30),
customer-city char(30),
primary key (customer-name))
create table branch
(branch-name char(15),
branch-city char(30),
assets integer,
primary key (branch-name))
Referential Integrity in SQL – Example (Cont.)
create table account
(account-number char(10),
branch-name char(15),
balance integer,
primary key (account-number),
foreign key (branch-name) references branch)
create table depositor
(customer-name char(20),
account-number char(10),
primary key (customer-name, account-number),
foreign key (account-number) references account,
foreign key (customer-name) references customer)
Cascading Actions in SQL
create table account
. . .
foreign key(branch-name) references branch
on delete cascade
on update cascade
. . . )
n Due to the on delete cascade clauses, if a delete of a tuple in branch results in referential-integrity constraint violation, the delete “cascades” to the account relation, deleting the tuple that refers to the branch that was deleted.
n Cascading updates are similar.
n If there is a chain of foreign-key dependencies across multiple relations, with on delete cascade specified for each dependency, a deletion or update at one end of the chain can propagate across the entire chain.
n If a cascading update to delete causes a constraint violation that cannot be handled by a further cascading operation, the system aborts the transaction.
H As a result, all the changes caused by the transaction and its cascading actions are undone.
n Referential integrity is only checked at the end of a transaction
H Intermediate steps are allowed to violate referential integrity provided later steps remove the violation
H Otherwise it would be impossible to create some database states, e.g. insert two tuples whose foreign keys point to each other
4 E.g. spouse attribute of relation
marriedperson(name, address, spouse)
Referential Integrity in SQL (Cont.)
n Alternative to cascading:
H on delete set null
H on delete set default
n Null values in foreign key attributes complicate SQL referential integrity semantics, and are best prevented using not null
H if any attribute of a foreign key is null, the tuple is defined to satisfy the foreign key constraint!
Assertions
n An assertion is a predicate expressing a condition that we wish the database always to satisfy.
n An assertion in SQL takes the form
create assertion <assertion-name> check <predicate>
n When an assertion is made, the system tests it for validity, and tests it again on every update that may violate the assertion
H This testing may introduce a significant amount of overhead; hence assertions should be used with great care.
n Asserting
for all X, P(X)
is achieved in a round-about fashion using
not exists X such that not P(X)
Assertion Example
n The sum of all loan amounts for each branch must be less than the sum of all account balances at the branch.
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(amount) from account
where loan.branch-name =
branch.branch-name)))
Assertion Example
n Every loan has at least one borrower who maintains an account with a minimum balance or $1000.00
create assertion balance-constraint check
(not exists (
select * from loan
where not exists (
select *
from borrower, depositor, account
where loan.loan-number = borrower.loan-number
and borrower.customer-name = depositor.customer-name
and depositor.account-number = account.account-number
and account.balance >= 1000)))
Triggers
n A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database.
n To design a trigger mechanism, we must:
H Specify the conditions under which the trigger is to be executed.
H Specify the actions to be taken when the trigger executes.
n Triggers introduced to SQL standard in SQL:1999, but supported even earlier using non-standard syntax by most databases.
n
Trigger Example
n Suppose that instead of allowing negative account balances, the bank deals with overdrafts by
H setting the account balance to zero
H creating a loan in the amount of the overdraft
H giving this loan a loan number identical to the account number of the overdrawn account
n The condition for executing the trigger is an update to the account relation that results in a negative balance value.
Trigger Example in SQL:1999
create trigger overdraft-trigger after update on account
referencing new row as nrow for each row
when nrow.balance < 0
begin atomic
insert into borrower
(select customer-name, account-number
from depositor
where nrow.account-number =
depositor.account-number);
insert into loan values
(n.row.account-number, nrow.branch-name,
– nrow.balance);
update account set balance = 0
where account.account-number = nrow.account-number
end
Triggering Events and Actions in SQL
n Triggering event can be insert, delete or update
n Triggers on update can be restricted to specific attributes
H E.g. create trigger overdraft-trigger after update of balance on account
n Values of attributes before and after an update can be referenced
H referencing old row as : for deletes and updates
H referencing new row as : for inserts and updates
n Triggers can be activated before an event, which can serve as extra constraints. E.g. convert blanks to null.
create trigger setnull-trigger before update on r
referencing new row as nrow
for each row
when nrow.phone-number = ‘ ‘
set nrow.phone-number = null
Statement Level Triggers
n Instead of executing a separate action for each affected row, a single action can be executed for all rows affected by a transaction
H Use for each statement instead of for each row
H Use referencing old table or referencing new table to refer to temporary tables (called transition tables) containing the affected rows
H Can be more efficient when dealing with SQL statements that update a large number of rows
External World Actions
n We sometimes require external world actions to be triggered on a database update
H E.g. re-ordering an item whose quantity in a warehouse has become small, or turning on an alarm light,
n Triggers cannot be used to directly implement external-world actions, BUT
H Triggers can be used to record actions-to-be-taken in a separate table
H Have an external process that repeatedly scans the table, carries out external-world actions and deletes action from table
n E.g. Suppose a warehouse has the following tables
H inventory(item, level): How much of each item is in the warehouse
H minlevel(item, level) : What is the minimum desired level of each item
H reorder(item, amount): What quantity should we re-order at a time
H orders(item, amount) : Orders to be placed (read by external process)
create trigger reorder-trigger after update of amount on inventory
referencing old row as orow, new row as nrow
for each row
when nrow.level < = (select level
from minlevel
where minlevel.item = orow.item)
and orow.level > (select level
from minlevel
where minlevel.item = orow.item)
begin
insert into orders
(select item, amount
from reorder
where reorder.item = orow.item)
end
Triggers in MS-SQLServer Syntax
create trigger overdraft-trigger on account
for update
as
if inserted.balance < 0
begin
insert into borrower
(select customer-name,account-number
from depositor, inserted
where inserted.account-number =
depositor.account-number)
insert into loan values
(inserted.account-number, inserted.branch-name,
– inserted.balance)
update account set balance = 0
from account, inserted
where account.account-number = inserted.account-number
end
When Not To Use Triggers
n Triggers were used earlier for tasks such as
H maintaining summary data (e.g. total salary of each department)
H Replicating databases by recording changes to special relations (called change or delta relations) and having a separate process that applies the changes over to a replica
n There are better ways of doing these now:
H Databases today provide built in materialized view facilities to maintain summary data
H Databases provide built-in support for replication
n Encapsulation facilities can be used instead of triggers in many cases
H Define methods to update fields
H Carry out actions as part of the update methods instead of
through a trigger
Security
n Security - protection from malicious attempts to steal or modify data.
H Database system level
4 Authentication and authorization mechanisms to allow specific users access only to required data
4 We concentrate on authorization in the rest of this chapter
H Operating system level
4 Operating system super-users can do anything they want to the database! Good operating system level security is required.
H Network level: must use encryption to prevent
4 Eavesdropping (unauthorized reading of messages)
4 Masquerading (pretending to be an authorized user or sending messages supposedly from authorized users)
H Physical level
4 Physical access to computers allows destruction of data by intruders; traditional lock-and-key security is needed
4 Computers must also be protected from floods, fire, etc.
– More in Chapter 17 (Recovery)
H Human level
4 Users must be screened to ensure that an authorized users do not give access to intruders
4 Users should be trained on password selection and secrecy
Authorization
Forms of authorization on parts of the database:
n Read authorization - allows reading, but not modification of data.
n Insert authorization - allows insertion of new data, but not modification of existing data.
n Update authorization - allows modification, but not deletion of data.
n Delete authorization - allows deletion of data
Forms of authorization to modify the database schema:
n Index authorization - allows creation and deletion of indices.
n Resources authorization - allows creation of new relations.
n Alteration authorization - allows addition or deletion of attributes in a relation.
n Drop authorization - allows deletion of relations.
Authorization and Views
n Users can be given authorization on views, without being given any authorization on the relations used in the view definition
n Ability of views to hide data serves both to simplify usage of the system and to enhance security by allowing users access only to data they need for their job
n A combination or relational-level security and view-level security can be used to limit a user’s access to precisely the data that user needs.
View Example
n Suppose a bank clerk needs to know the names of the customers of each branch, but is not authorized to see specific loan information.
H Approach: Deny direct access to the loan relation, but grant access to the view cust-loan, which consists only of the names of customers and the branches at which they have a loan.
H The cust-loan view is defined in SQL as follows:
create view cust-loan as
select branchname, customer-name
from borrower, loan
where borrower.loan-number = loan.loan-number
n The clerk is authorized to see the result of the query:
select *
from cust-loan
n When the query processor translates the result into a query on the actual relations in the database, we obtain a query on borrower and loan.
n Authorization must be checked on the clerk’s query before query processing replaces a view by the definition of the view.
Authorization on Views
n Creation of view does not require resources authorization since no real relation is being created
n The creator of a view gets only those privileges that provide no additional authorization beyond that he already had.
n E.g. if creator of view cust-loan had only read authorization on borrower and loan, he gets only read authorization on cust-loan
Granting of Privileges
n The passage of authorization from one user to another may be represented by an authorization graph.
n The nodes of this graph are the users.
n The root of the graph is the database administrator.
n Consider graph for update authorization on loan.
n An edge Ui ®Uj indicates that user Ui has granted update authorization on loan to Uj.
![]() |
0 comments:
Post a Comment