微信客服：xiaoxionga100
微信客服：ITCS521
选择代写-ISYS2120
时间：2021-10-20
ISYS2120: Data & Information Management Week 7: Database Security
and Integrity Presented by Dr. Matloob Khushi School of Computer
Science Cf. Kifer/Bernstein/Lewis – Chapter 3.2-3.3 Ramakrishnan/Gehrke –
Chapter 5.7-5.9; Silberschatz/Korth/Sudarshan – 4.2, 4.4, 5.3
Ullman/Widom – Chapter 7 06-2 Last Week: Recap Keys: Super Key Super
Key Candidate Key Primary Key Database Normalisation Functional
Dependencies Full Partial Trivial Transitive Dependency
Attribute Closure Closure of function dependencies 07-3 Outline
Database Security Views Static Integrity Constraints Domain
Constraints Key / Referential Constraints Semantic Integrity
Constraints Dynamic Integrity Constraints Triggers Based on slides
from Kifer/Bernstein/Lewis (2006) “Database Systems” and from
Ramakrishnan/Gehrke (2003) “Database Management Systems”, and also
including material from Fekete and Röhm. 07-4 Small Selection of IT News
“Houston – we have a problem…” CWE’s Top 25 Most Dangerous Software
Errors (2011) 07-5[Source: http://cwe.mitre.org/top25/] [39] CWE-209:
Information Exposure Through an Error Message 07-6 Database Security
Databases might contain sensitive information Need mechanisms to
guarantee: Secrecy: Users should not be able to see things they are
not supposed to. E.g., A student can’t see other students’ grades.
Integrity: Users should not be able to modify things they are not
supposed to. E.g., Only instructors can assign grades. Availability:
Users should be able to see and modify things they are allowed to. 07-7
Database Access Control Two main security mechanisms at the DBMS
level: Mandatory access control (Authentification) Every connection
must login with login and password CREATE USER or CREATE LOGIN
commands etc. Discretionary access control (Authorization) Based on
the concept of access rights or privileges for objects (tables and
views), and mechanisms for giving users privileges (and revoking
privileges). Creator of a table or a view automatically gets all
privileges on it. DMBS keeps track of who subsequently gains and loses
privileges, and ensures that only requests from users who have the
necessary privileges (at the time the request is issued) are allowed.
Note: SQL:92 provides tools for specifying an authorization policy but
does not support authentication (vendor specific) 07-8 Access Control in
SQL GRANT privilege_list ON table (any schema object) TO user_list
[WITH GRANT OPTION] privileges: SELECT, INSERT, DELETE, UPDATE,
REFERENCES REVOKE privilege_list ON table (any schema object) FROM
user_list REVOKE: When a privilege is revoked from X, it is also
revoked from all users who got it solely from X. 07-9 Access Control in
SQL - Examples Examples: GRANT SELECT ON Enrolled TO jason Grants
select (read) access to the Enrolled table to user 'jason' Individual
columns cannot be specified for SELECT access (SQL standard) – all
columns of Enrolled can be read (including any added later via ALTER
TABLE command). SELECT access control to individual columns can be
simulated through views GRANT UPDATE(grade) ON Enrolled TO roehm
Only the grade column can be updated by user ‘roehm’ 07-10 Grant and
Revoke Privileges If a user has a privilege with the GRANT OPTION, can
pass privilege on to other users (with or without passing on the GRANT
OPTION). Only owner can execute CREATE, ALTER, and DROP. Examples:
GRANT INSERT,SELECT ON Students TO Uwe Uwe can query students or
insert tuples into it. GRANT DELETE ON Students TO Jon WITH GRANT OPTION
Jon can delete tuples, and also authorize others to do so. GRANT
UPDATE(title) ON UnitofStudy TO Dustin Dustin can update (only) the
title field of Courses tuples. GRANT SELECT ON FemaleStudents TO Guppy,
Yuppy This does NOT allow the ‘uppies to query Student table
directly!This is a vi w on Students - what can the ‘uppy’s now see?
07-11 Views and Security Views can be used to present necessary
information (or a summary), while hiding details in underlying
relation(s). Given view CREATE VIEW MyStud AS SELECT sid,uos_code FROM
Enrolled we can find students who have enrolled in courses, but not the
grades they have achieved. Creator of view has a privilege on the
view if (s)he has the privilege on all underlying tables. Granting a
privilege on a view does not imply granting any privileges on the
underlying relations. If creator of base tables revokes SELECT right,
view is automatically dropped. Together with GRANT/REVOKE commands,
views are a very powerful access control tool. 03-12 Relational Views A
view is a virtual relation, but we store a definition, rather than a
set of tuples. The contents of a view is computed when it is used
within an SQL statement Mechanism to hide data from the view of
certain users. Views can be used to present necessary information (or a
summary), while hiding details in underlying relation(s). Users can
operate on the view as if it were a stored table DBMS calculates the
value whenever it is needed Syntax: CREATE VIEW name AS
(can even combine multiple relations)
03-13
View Examples
A view on the students showing their age.
CREATE VIEW ageStudents AS
SELECT sid, name, extract(year from sysdate) -
extract(year from birthdate) AS age
FROM Student
A view on the female students enrolled in 2020sem2
CREATE VIEW FemaleStudents (name, grade)
AS SELECT S.name, E.grade
FROM Student S, Enrolled E
WHERE S.sid = E.sid AND
S.gender = ‘F’ AND
E.semester = ‘2020sem2’
03-14
Updates on Views
Create a view of the enrolled relation, hiding the grade attribute
CREATE VIEW Enrolled_Students AS
SELECT sid AS student, uos_code
FROM Enrolled
Add a new tuple to view enrolled_students
INSERT INTO Enrolled_Students VALUES (200421567,‘INFO2120’)
This insertion means the insertion of the tuple
(200421567, ‘INFO2120’, null, null) into the enrolled relation
Updates on more complex views are difficult or impossible to translate,
and hence are disallowed.
Updatable Views: SQL-92 allows updates only on simple views (SELECT-
FROM-WHERE without aggregates or distinct) defined on a single relation
SQL:1999 allows more, but most implementations don’t support this yet
03-15
CREATE VIEW CsReg (StudId, CrsCode, Semester) AS
SELECT T.StudId, T. CrsCode, T.Semester
FROM Transcript T
WHERE T.CrsCode LIKE ‘CS%’ AND T.Semester=‘S2000’
Updating Views
Question: Since views look like tables to users, can they be updated?
Answer: Yes – a view update changes the underlying base table to
produce the requested change to the view
03-16
Updating Views - Problem 1
Question: What value should be placed in attributes of
underlying table that have been projected out (e.g.,
Grade)?
Answer: NULL (assuming null allowed in the missing
attribute) or DEFAULT
INSERT INTO CsReg (StudId, CrsCode, Semester)
VALUES (1111, ‘CSE305’, ‘S2000’)
03-17
Updating Views - Problem 2
Problem: New tuple will not be visible in view
Solution: Create View WITH CHECK OPTION ensures the
that the new rows satisfy the view-defining condition.
INSERT INTO CsReg (StudId, CrsCode, Semester)
VALUES (1111, ‘ECO105’, ‘S2000’)
03-18
Updating Views - Problem 3
Update to a view might not uniquely specify the change to the
base table(s) that results in the desired modification of the
view (ambiguity)
Example:
Can we do the following?
DELETE FROM ProfDept WHERE PrName=‘Smith’ AND DeName=‘CS’
CREATE VIEW ProfDept (PrName, DeName) AS
SELECT P.Name, D.Name
FROM Professor P, Department D
WHERE P.DeptId = D.DeptId
03-19
Updating Views - Problem 3 (cont’d)
Tuple
Deleting row for Smith from Professor (but this is inappropriate if he
is still at the University)
Deleting row for CS from Department (not what is intended)
Updating row for Smith in Professor by setting DeptId to null (seems
like a good idea, but how would the computer know?)
Demo Example: GRANTs and VIEWs
User A: CREATE TABLE Student (sid INT, … );
GRANT SELECT ON Student TO B WITH GRANT OPTION;
/* note: without GRANT OPTION, B cannot pass SELECT privilege on its view on to C */
User B: CREATE VIEW MyStud AS
SELECT sid, name FROM A.Student;
GRANT SELECT ON MyStud TO C;
User C: SELECT * FROM B.MyStud; -- works
SELECT * FROM A.Student; -- does not work
User A: REVOKE SELECT ON Student FROM B;
-- what happens now?
07-20
07-21
Authorization Mode REFERENCES
Foreign key constraint enforces relationships between tables;
those could be exploited to
control access: can prevent deletion of rows
CREATE TABLE DontDismissMe (
id INTEGER,
FOREIGN KEY (id) REFERENCES Student
ON DELETE NO ACTION )
reveal information: successful insertion into DontDissmissMe means
a row with a foreign key value exists in Student
Example:
INSERT INTO DontDismissMe VALUES (11111111);
REFERENCES access mode allows to prevent this by only
allowing authorized users to use foreign keys to a table
GRANT REFERENCES ON Student TO flexsis
07-22
Role-based Authorization
In SQL-92, privileges are actually assigned to authorisation
ids, which can denote a single user or a group of users.
In SQL:1999 (and in many current systems), privileges are
assigned to roles.
Roles can then be granted to users and to other roles.
Reflects how real organisations work.
Much more flexible and less error-prone, especially on large schemas
=> use role-based authorization whenever possible
Example:
CREATE ROLE manager
GRANT select,insert ON students TO manager
GRANT manager TO shari
REVOKE manager FROM shari
07-23
Limitations of SQL Authorization
SQL does not support authorization at a tuple level
eg. we cannot restrict students to see only (the tuples storing) their own grades
can be simulated to a certain degree using Views, but VERY cumbersome
With the growth in Web access to databases, database accesses come
primarily from application servers.
End users don't have database user ids, they are all mapped to the same
database user id
All end-users of an application (such as a web application) may be mapped to a single
database user
The task of authorisation in above cases falls on the application program,
with no support from SQL
Benefit: fine grained authorisations, such as to individual tuples, can be
implemented by the application.
Drawback: Authorisation must be done in application code, and may be
dispersed all over an application
Checking for absence of authorisation loopholes becomes very difficult since it
requires reading large amounts of application code
Data Minimalism
The best protection against unauthorized access to data in
your database is to consider very carefully what you store in
the first place!
A database should only store information that is absolutely
necessary for the operation of your application.
Some data is even not allowed to be stored
For example: Sensitive authentication data such as the security code
of a credit card
Cf. https://www.pcisecuritystandards.org/documents/pa-dss_v2.pdf
In Australia, the Tax File Number or the Medicare numbers is
specifically protected from being used outside government
Any personal health information
07-24
Data Privacy
Some information is specifically protected and requires
specific standards and auditing procedures
especially for governmental organisations or large businesses
In Australia, the Privacy Act 1988 (Cth) (the Privacy Act)
governs the protection rules regarding personal information
Personal information: information where an individual is reasonably
identifiable, i.e. information that identifies/could identify an individual
regulates e.g. what and how to collect, disclosure rules, requirement
to ensure information quality, when to delete
cf. Australian Privacy Principles (APP)
https://www.oaic.gov.au/agencies-and-organisations/app-guidelines
http://www.privacy.gov.au/
07-25
07-26
Outline
Database Security
Views
Static Integrity Constraints
Domain Constraints
Key / Referential Constraints
Semantic Integrity Constraints
Dynamic Integrity Constraints
Triggers
Based on slides from Kifer/Bernstein/Lewis (2006) “Database Systems”
and from Ramakrishnan/Gehrke (2003) “Database Management Systems”,
and also including material from Fekete and Röhm.
07-27
Semantic Integrity Constraints
Objective:
capture semantics of the miniworld in the database
ensuring that authorized changes to the database do not result in a
loss of data consistency
guard against accidental damage to the database (avoid data entry
errors)
Advantages of a centralized, automatic mechanism to
ensures semantic integrity constraints:
More effective integrity control
Stored data is more faithful to real-world meaning
Easier application development, better maintainability
Note: DBMS allow to capture more ICs than, e.g., ERM
07-28
Examples of Integrity Constraints
Each student ID must be unique.
For every student, a name must be given.
The only possible grades are either ‘F’, ‘P’, ‘C’, ‘D’, or ‘H’.
Valid lecturer titles are ‘Lecturer’, ‘Senior Lecturer’ or
‘Professor’
Students can only enrol in actually offered unit of studies.
Students must be assessed by the lecturer who actually gave
the course and the mark they achieve is between 0 and 100.
The sum of all marks in a course cannot be higher than 100.
Student always advance to more senior levels, but never get
demoted.
07-29
Integrity Constraint (IC)
Integrity Constraint (IC):
condition that must be true for every instance of a database
A legal instance of a relation is one that satisfies all specified ICs
DBMS should never allow illegal instances….
ICs are specified in the database schema
The database designer is responsible to ensure that the integrity
constraints are not contradicting each other!
ICs are checked when the database is modified
With one degree of freedom:
After a SQL statement, or at the end of a transaction?
Possible reactions if an IC is violated:
Undoing of a database operation
Abort of the transaction
Execution of “maintenance” operations to make db legal again
07-30
Types of Integrity Constraints
Static Integrity Constraints
describe conditions that every legal instance of a database
must satisfy
Inserts / deletes / updates that violate ICs are disallowed
Three kinds:
Domain Constraints
Key Constraints & Referential Integrity
Semantic Integrity Constraints; Assertions
Dynamic Integrity Constraints
are predicates on database state changes
Triggers
07-31
Domain Constraints
The most elementary form of an integrity constraint:
Fields must be of right data domain
always enforced for values inserted in the database
Also: queries are tested to ensure that the comparisons make sense.
SQL DDL allows domains of attributes to be restricted in the
create table definition with the following clauses:
DEFAULT default-value
default value for an attribute if its value is omitted in an insert stmnt.
NOT NULL
attribute is not allowed to become NULL
NULL (note: not part of the SQL standard)
the values for an attribute may be NULL (which is the default)
07-32
Example of Domain Constraints
Semantic:
sid is primary key of Student
name and gender must not be NULL
level will be 1 if not specified by an insert
all other attributes can be NULL (birthday and country)
Example:
INSERT INTO Student(sid,name,gender) VALUES (123,'James','M');
CREATE TABLE Student
(
sid INTEGER PRIMARY KEY,
name VARCHAR(20) NOT NULL,
gender CHAR NOT NULL,
birthday DATE NULL,
country VARCHAR(20),
level INTEGER DEFAULT 1
);
07-33
User-Defined Domains
New domains can be created from existing data domains
CREATE DOMAIN domain-name sql-data-type
Example:
create domain Dollars numeric(12,2)
create domain Pounds numeric(12,2)
Domains can be further restricted,e.g. with the check clause
E.g.: create domain Grade char check(value in (‘F’,’P’,’C’,’D’,’H’))
User-defined types with SQL:1999:
CREATE [DISTINCT] TYPE type-name AS sql-base-type
Will most probably replace the create domain mechanism
CREATE DOMAIN: Currently only Sybase and PostgreSQL
CREATE DISTINCT TYPE: so far, only supported by IBM DB2
(SQL Server has an add_type() procedure)
cannot assign or compare
a value of Dollars
to a value of Pounds.
07-34
Primary Key Constraints
Recall definition from week 3:
A set of fields is a key for a relation if :
1. No two distinct tuples can have same values in all key attributes,
and
2. This is not true for any subset of the key.
In SQL, we specify a primary key constraint using the
PRIMARY KEY clause:
A primary key is automatically unique and NOT NULL
Complex keys: separate clause at end of create table
CREATE TABLE Student
(
sid INTEGER PRIMARY KEY,
name VARCHAR(20)
);
Student
sid
name
07-35
Foreign Keys & Referential Integrity
Foreign key :
Set of attributes in a relation that is used to `refer’ to a tuple in a
parent relation.
Must refer to a candidate key of the parent relation
Like a `logical pointer’
Referential Integrity: for each tuple in the referring relation
whose foreign key value is α, there must be a tuple in the
referred relation whose primary key value is also α
e.g. sid is a foreign key referring to Student:
Enrolled(sid: integer, ucode: string, semester: string)
If all foreign key constraints are enforced, referential integrity is
achieved, i.e., no dangling references
07-36
Foreign Keys in SQL
Only students listed in the Students relation should be
allowed to enroll for courses.
CREATE TABLE Enrolled
( sid CHAR(10), uos CHAR(8), grade CHAR(2),
PRIMARY KEY (sid,uos),
FOREIGN KEY (sid) REFERENCES Student )
??? Dangling reference
Student
sid name
53666 Jones
53650 Smith
54541 Ha Tschi
54672 Loman
age
19
21
20
20
country
AUS
AUS
CHN
AUS
Enrolled
sid uos
53666 COMP5138
53666 INFO4990
53650 COMP5138
53666 SOFT4200
grade
CR
CR
P
D
54221 INFO4990 F
07-37
Enforcing Referential Integrity in SQL
SQL/92 and SQL:1999
support four options on
deletes and updates
at the parent table.
Default is NO ACTION
(delete/update is rejected)
CASCADE (also delete all
tuples that refer to deleted
tuple)
SET NULL
(resets foreign key to NULL)
SET DEFAULT
(sets foreign key value of
referencing tuple to a default
value)
Cf. Example in Tutorial
CREATE TABLE Enrolled
( sid CHAR(10),
uos CHAR(8),
grade CHAR(2),
PRIMARY KEY (sid,uos),
FOREIGN KEY (sid)
REFERENCES Student
ON DELETE CASCADE
ON UPDATE NO ACTION )
refers to
modifications
at the parent
table (Student)
07-39
Semantic Integrity Constraints
Integrity constraints on more than one attribute?
Also, a name for integrity constraint would be very useful for
administration / maintenance…
SQL:
CONSTRAINT name CHECK ( semantic-condition )
One can use subqueries to express constraint (SQL-92
standard)
Note: subqueries in CHECKs are NOT SUPPORTED by either
PostgreSQL or Oracle (Sybase is one example that does this)
07-40
Semantic Constraints Example
CREATE TABLE Assessment
(
sid INTEGER REFERENCES Student,
uos VARCHAR(8) REFERENCES UnitOfStudy,
empid INTEGER REFERENCES Lecturer,
mark INTEGER,
CONSTRAINT maxMarks CHECK (mark between 0 and 100),
CONSTRAINT rightLecturer
CHECK ( empid = (SELECT u.lecturer
FROM UnitOfStudy u
WHERE u.uos_code=uos) )
);
Note: The second constraint with a subquery is not supported by PostgreSQL
07-41
SQL: Naming Integrity Constraints
The CONSTRAINT clause can be used to name all kinds of
integrity constraints
Example:
CREATE TABLE Enrolled
(
sid INTEGER,
uos VARCHAR(8),
grade CHAR(2),
CONSTRAINT FK_sid_enrolled FOREIGN KEY (sid)
REFERENCES Student
ON DELETE CASCADE,
CONSTRAINT FK_cid_enrolled FOREIGN KEY (uos)
REFERENCES UnitOfStudy
ON DELETE CASCADE,
CONSTRAINT CK_grade_enrolled CHECK(grade in (‘F’,…)),
CONSTRAINT PK_enrolled PRIMARY KEY (sid,uos)
);
07-42
Deferring Constraint Checking
Any constraint - domain, key, foreign-key, semantic - may
be declared:
NOT DEFERRABLE
The default. It means that every time a database modification occurs,
the constraint is checked immediately afterwards.
DEFERRABLE
Gives the option to wait until a transaction is complete before
checking the constraint.
INITIALLY DEFERRED wait until transaction end,
but allow to dynamically change later
INITIALLY IMMEDIATE check immediate,
but allow to dynamically change later
07-43
Example: Deferring Constraints
CREATE TABLE UnitOfStudy
(
uos_code VARCHAR(8),
title VARCHAR(220),
lecturer INTEGER,
credit_points INTEGER,
CONSTRAINT UnitOfStudy_PK PRIMARY KEY (uos_code),
CONSTRAINT UnitOfStudy_FK FOREIGN KEY (lecturer)
REFERENCES Lecturer DEFERABBLE INITIALLY DEFERRED
);
Allows to insert a new course referencing a lecturer which is not present
at that time, but who will be added later in the same transaction.
Behaviour can be dynamically changed within a transaction
with the SQL statement
SET CONSTRAINT UnitOfStudy_FK IMMEDIATE;
07-44
ALTER TABLE Statement
Integrity constraints can be added, modified (only domain constraints),
and removed from an existing schema using ALTER TABLE statements
ALTER TABLE table-name constraint-modification
where constraint-modification is one of:
ADD CONSTRAINT constraint-name new-constraint
DROP CONSTRAINT constraint-name
RENAME CONSTRAINT old-name TO new-name
ALTER COLUMN attribute-name domain-constraint
(Oracle Syntax for last one: MODIFY attribute-name domain-constraint )
Example (PostgreSQL syntax):
ALTER TABLE Enrolled ALTER COLUMN grade SET NOT NULL;
What happens if the existing data in a table does not fulfil a newly added
constraint?
Then constraint gets not created!
e.g. "ORA-02293: cannot validate (DAMAGECHECK) - check constraint violated"
07-45
Assertions
The integrity constraints seen so far are associated with a single table
Plus: they are required to hold only if the associated table is nonempty!
Need for a more general integrity constraints
E.g. integrity constraints over several tables
Always checked, independent if one table is empty
Assertion: a predicate expressing a condition that we wish the
database always to satisfy.
SQL-92 syntax:
create assertion
Assertions are schema objects (like tables or views)
When an assertion is made, the system tests it for validity, and tests it
again on every update that may violate it
This testing may introduce a significant amount of overhead; hence
assertions should be used with great care.
07-46
Assertion Example
The number of boats plus the number of sailors should be
less than 100.
CREATE TABLE Sailors (
sid INTEGER
sname CHAR (10)
rating INTEGER
PRIMARY KEY (sid)
CHECK (rating >=1 AND rating <=10)
CHECK ((SELECT count(s.sid) FROM Sailors s
+ (SELECT count(b.bid) FROM Boats b) < 100))
CREATE ASSERTION smallclub CHECK
( (SELECT COUNT(s.sid) FROM Sailors s)
+ (SELECT COUNT(b.bid) FROM Boats b) < 100) )
07-47
Assertion Example II
Asserting ∀ X : P(X) is achieved in a round-about
fashion using "not exists X such that not P(X)"
Example: For all students, the sum of all marks for a course
must be less or equal than 100.
CREATE ASSERTION mark-constraint CHECK
(
not exists ( select sid
from Assessment
group by sid,uos_code
having sum(mark) > 100 )
)
Note: Although generalizing nicely the semantic constraints,
assertions are not supported by any DBMS at the moment…
07-48
Comparison of Constraints
Principle differences among integrity constraints types
Type of
constraint Where declared When activated
Guaranteed to
hold?
Supported by
DBMS
DEFAULT
NOT NULL/NULL
CREATE TABLE
on attribute insert or updates Yes All
CREATE
DOMAIN
Own schema
object n.a. n.a. Sybase; Postgres
Referential
integrity CREATE TABLE
Any table
modification Yes
All* (MySql since
v4.x with InnoDB)
Attribute-based
CHECK
CREATE TABLE
on attribute
On insertion to
relation or attribute
update
Not if subquery All except MySQL
Tuple-based
CHECK
At end of CREATE
TABLE
On insertion to
relation or attribute
update Not if subquery
All except MySQL
but subqueries
only with Sybase
Assertion Own schema object
On any change to
any mentioned
relation
Yes none
07-51
Today’s Agenda
Database Security
Views
Static Integrity Constraints
Domain Constraints
Key / Referential Constraints
Semantic Integrity Constraints
Dynamic Integrity Constraints
Tiggers
07-52
Triggers
A trigger is a statement that is executed automatically if
specified modifications occur to the DBMS.
A trigger specification consists of three parts:
ON event IF precondition THEN action
Event ( what activates the trigger? )
Precondition ( guard / test whether the trigger shall be executed)
Action ( what happens if the trigger is run)
Triggers introduced to SQL standard in SQL:1999, but supported even
earlier using non-standard syntax by most databases.
07-53
Why Triggers?
Constraint maintenance
Triggers can be used to maintain foreign-key and semantic
constraints; commonly used with ON DELETE and ON UPDATE
Business rules
Some dynamic business rules can be encoded as triggers
Assertions can be implemented using two triggers
Monitoring
E.g. to react on the insertion of some kind of sensor reading into db
Maintenance of auxiliary cached data
Careful! Many systems now support materialized views which should
be preferred against such maintenance triggers
Simplified application design
E.g. exceptions modelled as update operations on a database
(if applicable)
07-54
Trigger Example (SQL:1999)
CREATE TRIGGER gradeUpgrade
AFTER INSERT OR UPDATE ON Assessment
BEGIN
UPDATE Enrolled E
SET grade=‘P’
WHERE grade IS NULL
AND ( SELECT SUM(mark)
FROM Assessment A
WHERE A.sid=E.sid AND
A.uos=E.uosCode ) >= 50;
END;
07-55
Triggers – PostgreSQL Syntax
CREATE TRIGGER trigger-name
INSERT
DELETE ON relation-name
UPDATE
FOR EACH ROW
WHEN ( condition )
EXECUTE PROCEDURE stored-procedure-name();
BEFORE
AFTER
-- optional; otherwise a statement trigger
-- PL/pgSQL can be used to define trigger procedures
-– needs to be specified with no arguments
-- When a PL/pgSQL function is called as a trigger, several special variables
-- are created automatically in the top-level block:
NEW
OLD
TG_WHEN ('BEFORE' or 'AFTER')
TG_OP ('INSERT', 'DELETE, 'UPDATE', 'TRUNCATE')
…
[cf. http://www.postgresql.org/docs/8.4/static/plpgsql-trigger.html]
-- optional; only for row-triggers
-- optional
-- needs to be defined 1st
07-56
Trigger Events and Granularity
Triggering event can be insert, delete or update
Triggers on update can be restricted to specific attributes
CREATE TRIGGER overdraft-trigger AFTER UPDATE OF balance
ON account
Granularity
Row-level granularity: change of a single row is an event (a single
UPDATE statement might result in multiple events)
Statement-level granularity: events are statements (a single
UPDATE statement that changes multiple rows is a single event).
Can be more efficient when dealing with SQL statements
that update a large number of rows…
07-57
Statement vs. Row Level Trigger
Example: Assume the following schema
Employee ( name, salary )
with 1000 tuples and an ON UPDATE trigger on salary…
Now let’s give employees a pay rise:
UPDATE Employee SET salary=salary*1.025;
Update Costs:
How many rows are updated?
How often is a row-level trigger executed?
How often is a statement-level trigger executed?
1000
1000
1
07-58
Trigger Granularity - Syntax
Instead of executing a separate action for each affected row,
a single action can be executed for all rows affected by a
transaction
Use FOR EACH STATEMENT instead of FOR EACH ROW
(actually the default)
Some systems (e.g. Oracle, but NOT PostgreSQL) allow to use
REFERENCING OLD TABLE
or REFERENCING NEW TABLE
to refer to temporary tables (called transition tables) containing the
affected rows
Can be more efficient when dealing with SQL statements
that update a large number of rows…
07-59
CREATE FUNCTION AbortEnrolment() RETURNS trigger AS $$
BEGIN
RAISE EXCEPTION ‘unit is full’; -- aborts transaction
END
$$ LANGUAGE pgplsql;
CREATE TRIGGER Max_EnrollCheck
BEFORE INSERT ON Transcript
FOR EACH ROW
WHEN ((SELECT COUNT (T.studId)
FROM Transcript T
WHERE T.uosCode = NEW.uosCode AND
T.semester = NEW.semester)
>= (SELECT U.maxEnroll
FROM UnitOfStudy U
WHERE U.uosCode = NEW.uosCode ))
EXECUTE PROCEDURE AbortEnrolment();
Check that
enrollment ≤ limit
Before Trigger Example
(row granularity, PostgreSQL syntax)
(1) In PostgreSQL, you
first need to define
a trigger function…
(2) … before you can
declare the actual
trigger, that uses it
07-60
CREATE TABLE Log ( … );
CREATE FUNCTION SalaryLogger() RETURNS trigger AS $$
BEGIN
INSERT INTO Log
VALUES (CURRENT_DATE, SELECT AVG(Salary)
FROM Employee );
RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER RecordNewAverage
AFTER UPDATE OF Salary ON Employee
FOR EACH STATEMENT
EXECUTE SalaryLogger();
Keep track of salary
averages in the log
After Trigger Example
(statement granularity, PostgreSQL syntax)
07-61
Some Tips on Triggers
Use BEFORE triggers
For checking integrity constraints
Use AFTER triggers
For integrity maintenance and update propagation
In Oracle, triggers cannot access “mutating” tables
e.g. AFTER trigger on the same table which just updates
Good overviews:
Kifer/Bernstein/Lewis: “Database Systems - An Application-oriented
Approach”, 2nd edition, Chapter 7.
Michael v.Mannino: “Database - Design, Application Development
and Administration”
Oracle Application Developer’s Guide, Chapter 15
07-62
When Not to Use Triggers
Triggers were used earlier for tasks such as
maintaining summary data (e.g. total salary of each department)
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
There are better ways of doing these now:
Databases today provide built-in materialized view facilities to
maintain summary data
Databases provide built-in support for replication
07-63
You should now be able to:
Capture Integrity Constraints in an SQL Schema
Including key constraints, referential integrity, domain constraints
and semantic constraints
And simple triggers for dynamic constraints
Formulate complex semantic constraints using Assertions
Know when to use Assertions, when triggers, and when
CHECK constraints
Know the semantic of deferring integrity constraints
Be able to formulate simple triggers
Know the difference between row-level & statement-level
triggers
07-64
References
Kifer/Bernstein/Lewis (2nd edition)
Sections 3.2.2-3.3 and Chapter 7
Integrity constraints are covered as part of the relational model, but a good
dedicated chapter (Chap 7) on triggers
Ramakrishnan/Gehrke (3rd edition - the ‘Cow’ book)
Sections 3.2-3.3 and Sections 5.7-5.9
Integrity constraints are covered in different parts of the SQL discussion; only
brief on triggers
Ullman/Widom (3rd edition)
Chapter 7
Has a complete chapter dedicated to both integrity constraints&triggers. Good.
Michael v.Mannino: “Database - Design, Application Development and
Administration”
Include a good introduction to triggers.
Oracle Application Developer’s Guide, Chapter 15
The technical details on the specific Oracle syntax and capabilities.
07-65
Next Topic
Database Application Development
Embedded SQL in Client Code
Call-level Database APIs
Server-Side Application Development with Stored Procedures
Readings:
Kifer/Bernstein/Lewis book, Chapter 8
or alternatively (if you prefer those books):
Ramakrishnan/Gehrke (Cow book), Chapter 6
