Blog

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/%5D [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 where is any legal query expression (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 can be deleted from ProfDept by: 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 check () 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%5D — 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 Ullman/Widom, Chapter 9