Constraints, Triggers, Views, Application Development

1. Constraints, Triggers, Views, Application Development

2. Building Database Applications • Integrity Constraints • Primary Key, Foreign Key • Check • Domain • Assertion • Triggers • Views • Levels of Abstraction • Data Independence • Embedded SQL • Cursors

3. Integrity Constraints (ICs) • An IC describes conditions that every legal instance of a table must satisfy. • Inserts/deletes/updates that violate IC’s are typically disallowed. • ICs can be used to ensure application semantics (e.g., candid is a key), or prevent inconsistencies (e.g., candid has to be an integer, amount must be >= 200) • Types of IC’s: Primary key constraints, foreign key constraints, general (check) constraints, domain constraints.

4. Key Constraints – with Names • For efficiency one usually creates ICs seperately from CREATE TABLE statements, using ALTER TABLE. But it can be done in one statement, e.g: CREATE TABLE cand (… princomm char(9)… CONSTRAINT cand_fkey FOREIGN KEY (princomm)REFERENCES comm(commid) ON UPDATE… ); • The advantages of using named constraints (like cand_fkey) are • Error messages are more meaningful • missing value for constraint “cand_fkey” • You can drop or defer the constraint

5. General Constraints* • CHECK constraints can cover extremely general situations. They can use arbitrary queries, for example: CONSTRAINT limit_check CHECK(2300 >= SELECT MAX(totamt) FROM ( SELECT donorname, occup, zip, commid, SUM(amount) AS totamt FROM indiv GROUP BY donorname,occup,zip, commid) AS foo; ); • What does this accomplish?

6. Assertions: ICs over several tables* • Any logical expression involving an SQL statement can be used to constrain tables in the database. • What does this accomplish? Employee( id, name, address, mgr, salary ) CREATE ASSERTION salary_mgr CHECK (NOT EXISTS ( SELECT * FROM Employee E, Employee M WHERE E.salary > M.salary AND E.mgr = M.id ) )

7. Triggers (Not a constraint) • Trigger: procedure that executes if specified changes occur to the DBMS • Three parts: • Event (activates the trigger) • This will be an insert, delete and/or update to a table • Condition (tests whether the triggers should run) • A logical statement or a query • Action (what happens if the trigger runs) • Can execute queries, execute data-definition commands, transaction-oriented commands, and host-language procedures • When does the Action execute? • Specified with Event (BEFORE, AFTER)

8. Triggers: Example* • Assume one donation has been inserted to indiv, for simplicity CREATE TRIGGER overmax ON indiv AFTER INSERT AS IF 2300 < SELECT MAX(totamt) FROM (SELECT i.donorname, i.commid, SUM(i.amount) AS totamt FROM indiv i GROUP BY i.donorname, i.commid WHERE i.donorname = new.donorname) DELETE comm c WHERE c.commid = new.commid • What does this accomplish?

9. Triggers vs Constraints • Triggers are harder to understand • If multiple triggers apply, their order of execution is unspecified • One trigger can activate another, causing a chain of actions • A trigger can activate itself • Triggers are more powerful than constraints • They can make changes to tables • They can take action before or after a data modification

10. Views students(sid, name, address, gpa) completed( sid, course, grade) • This material is scattered in the first few chapters. • A view is a query stored in the database • Think of it as a table definition for future use • Example view definition: CREATE VIEW gstudents AS SELECT * FROM students WHERE gpa >= 2.5 Views can be used like base tables, in any query or in any other view. Like a Macro. Different from INTO.

11. Example view use: simpler queries • Suppose you want to retrieve good students who have completed CS386. SELECT S.name, S.phone FROM gstudents S JOIN completed C USING (sid) WHERE C.course = ‘CS386’; • It’s easier to write the query using the view.

12. Views for Security • This is the student table without the gpa field. CREATE VIEW sstudents AS SELECT sid, name, address FROM students • Can you think of some other security examples?

13. Views for Extensibility • An old company’s database includes a table: Part (PartID, Name, Weight) • Weight is stored in pounds • The company is purchased by a new firm that uses metric weights • The two databases, old and new, must be integrated and use Kg. • But there’s lots of old software using pounds. • Solution: views!

14. Views for extensibility • Solution: • Base table with kilograms becomes NewPart, for new integrated company. • CREATE VIEW Part AS SELECT PartID, Name, 2.2046*Weight FROM NewPart; • Old programs still call the table “Part”

15. But there’s one problem with views: update • Views cannot always be updated unambiguously. Consider emp(empid, ename, address, deptid) dept(deptid, dname) CREATE VIEW empdept AS SELECT ename, dname FROM emp JOIN dept USING (deptid) EMPDEPT ename dname jim shoe joe suit • I want to delete (jim, shoe) from EMPDEPT. • How can I do that?

16. The good news • A view can be updated if • It is defined on a single base table • Using only selection and projection • No aggregates • No DISTINCT

17. ES 1 ES 2 ES 3 Conceptual Schema Physical Schema Levels of Abstraction External view; user and data designer Logical storage; data designer Physical storage; DBA

18. Physical Schema The physical schema is a description of how the data is physically stored in the database. It includes • Where the data is located • File structures • Access methods • Indexes The physical schema is managed by the DBA.

19. Conceptual Schema The conceptual schema is a logical description of how the data is stored. It consists of the schemas we have described with CREATE TABLE statements. It is managed by the data designer.

20. External Schemas Each external schema is a combinaton of base tables and views, tailored to the needs of a single user. It is managed by the data designer and the user.

21. Data Independence • A database model possesses data independence if application programs are immune to changes in the conceptual and physical schemas. • Why is this important? Everything changes. • How does the relational model achieve logical (conceptual) data independence? • Through views • If the conceptual schema changes, a view can be defined to preserve existing applications

22. Data Independence • How does the relational model achieve physical data independence? • Conceptual level contains no physical info • SQL can program against the conceptual level • Earlier DBMSs (network, hierarchical) did not have these properties. • Their languages had physical properties embedded in them. • That is the primary reason for the success of the relational model

23. Views: Summary • A view is a stored query definition • Views can be very useful • Easier query writing, security, extensibility • But views cannot be unambiguously updated • Three levels of abstraction in a relational DBMS • Yields data independence: logical and physical

24. Embedded SQL • SQL is not enough! Needs to be embedded in a general purpose language to get • GUI • Flow of control • Generate SQL dynamically based on user input • SQL commands can be called from within a host language (e.g., C/C++, Basic, .NET or Java) program or scripting language (e.g., PHP, Ruby). • A query answer is a bag of records - with arbitrarily many rows! No such data structure in most languages. Called impedance mismatch. • The SQL standard supports a cursor to handle this

25. Cursors • You can declare a cursor on a table or query statement (which generates a table). • You can open a cursor, and repeatedly fetch a tuple then move the cursor, until all tuples have been retrieved. • You can modify/delete a tuple pointed to by a cursor. • SQL must be able to report data-generated errors.

26. Cursor that gets names of candidates who have a principal committee, in alphabetical order EXEC SQL DECLARE cinfo CURSOR FOR SELECT N.candname FROM cand N JOIN commM ON (N.princomm = M.commid) ORDER BY N.candname; OPEN cinfo; FETCH cinfo INTO :c-name; (probably in a loop in your program) CLOSE cinfo; Notice the colon in :cname – it refers to a variable that has been declared in the surrounding program

27. Embedding SQL in C: An Example Void ListAges( int minzip) { char SQLSTATE[6]; EXEC SQL BEGIN DECLARE SECTION char c_name[20]; char c_party[3]; integer minzip; EXEC SQL END DECLARE SECTION • SQLSTATE holds SQL error codes • EXEC SQL denotes embedded SQL section • DECLARE SECTION binds variables into SQL

28. Embedding in C: An Example EXEC SQL DECLARE cinfo CURSOR FOR SELECT N. candname, N.party FROM cand N WHERE zip > :minzip ORDER BY N.candname; • DECLARE cinfo CURSOR defines a name for the cursor • SELECT … is the SQL whose results the cursor will point to • :minzip – Note the colon referring to a C variable declared previously

29. Embedding in C: An Example EXEC SQL OPEN cinfo; EXEC SQL FETCH cinfo INTO :c_name; While(SQLSTATE != “02000”) { printf(“Candidate name is %s\n”, candname); EXEC SQL FETCH cinfo INTO :c_name; } ; EXEC SQL CLOSE cinfo; • OPEN cinfo - Executes the query and positions the cursor before the first row of the output • FETCH cinfo INTO – assigns the data of the first row (if it exists) into C program variables • CLOSE cinfo – Free the cursor’s resources

30. Security, PhP • Getting started in UNIX • Overview of PHP and HTML • PHP Basics • PHP and Databases • Postgres and MySQL • Database Abstractions • Connecting via Java or Perl • Security • References • Security and Authorization • Discretionary Access Control • Grant, Revoke, Authorization Graph, Views • Mandatory Access Control • Internet-oriented Security • Statistical Database Security

31. Security and Authorization • Objectives: • Secrecy: Users should not be able to see things they are not supposed to see. • E.g., A student can’t see other students’ grades. • Integrity: Users should not be able to modify things they are not supposed to modify. • E.g., Only instructors can assign grades. • Availability: Users should be able to see and modify things they are allowed to see and modify. • OS, Network security issues also relevant • Only database issues discussed here

32. Access Controls • A security policy specifies who is authorized to do what. • A security mechanism allows us to enforce a chosen security policy. • Two main mechanisms at the DBMS level: • Discretionary access control • Mandatory access control

33. Discretionary Access Control • 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 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.

34. GRANT Command* GRANT privilegesON object TO auth [WITH GRANT OPTION] • The following privileges can be specified: • SELECT: Can read all columns (including those added later via ALTER TABLE command). • SELECT(col-name(s)) means can read these columns. • INSERT : Can insert tuples. • INSERT(col-name(s)) means can insert non-null values into these columns. • DELETE: Can delete tuples. • REFERENCES (col-name): Can define foreign keys (in other tables) that refer to this column. • Auth is a group of users. • 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.

35. REVOKE Command REVOKE privilegesON object FROM auth [RESTRICT|CASCADE] • Must be issued by a user who granted “privileges” on “object” to “auth”. • CASCADE: • abandon anyone granted this privilege by auth, and so on recursively. • “abandon”: If an auth’s privilege is abandoned and this is the last possible abandonment, then revoke auth’s privilege • “and so on recursively”: Abandon anyone granted the privilege by the auth just abandoned. • RESTRICT: If CASCADE would cause anyone to be abandoned, then cancel the command.

36. Examples of Grant, Revoke • Suppose Len has created the author table, then these commands are executed in this order • Len: GRANT UPDATE, SELECT ON author TO Yingjin • Len: GRANT UPDATE ON author TO Parisa WITH GRANT OPTION • Parisa: GRANT UPDATE ON author TO Yingjin • Len: REVOKE UPDATE ONauthor FROM Parisa CASCADE • Len: REVOKE SELECT ONauthor FROM Yingjin CASCADE • What privileges does Parisa have on author? • What privileges does Yingjin have on author? • Can Yingjin issue UPDATE author A SET A.age=25? • Can Yingjin issue UPDATE author A SET A.age=A.age+1?

37. REVOKE more precisely • In some cases we need a more precise definition of REVOKE. • Steps to be taken after “REVOKE P ON O FROM A” • First consider the case CASCADE • Draw an authorization graph of current state (cf. next page) • Delete the arc corresponding to “GRANT P ON O TO A” • Also delete any arc not accessible from SYSTEM via grants of the privilege P on O • If an auth has no incoming arcs granting P on O, then auth loses the privilege P on O. • RESTRICT is the same as before: If CASCADE would cause any arc other than “GRANT ON P TO A” to be deleted, then abort the command.

38. GRANT/REVOKE on Views • The creator of a view only has a privilege on the view if s/he has it on every component of the view. • In particular, must have SELECT on all components to create the view • If the creator of a view loses the SELECT privilege on an underlying table, the view is dropped! • If the creator of a view loses a privilege held with the grant option on an underlying table, (s)he loses the privilege on the view as well; so do users who were granted that privilege on the view!

39. View Example • Len is the creator of author. Suppose these statements are executed. • Len: GRANT SELECT ON author TO Vijay WITH GRANT OPTION • Len: GRANT SELECT ON author TO Pratibha WITH GRANT OPTION • Pratibha: GRANT SELECT ON author TO Vijay WITH GRANT OPTION • Vijay: CREATE VIEW ta AS SELECT * FROM AUTHOR A WHERE A.age < 30 • Len: REVOKE SELECT ON author FROM Vijay CASCADE • Len: REVOKE SELECT ON author FROM Pratibha RESTRICT • What privileges does Vijay have on author, ta? • What privileges does Pratibha have on author, ta?

40. Role-Based Authorization • In SQL-92, privileges are actually assigned to authorization ids, which can denote a single user or a group of users. • In SQL:1999 privileges are assigned to roles. • Roles can then be granted to users and to other roles. • Reflects how real organizations work. • Illustrates how standards often catch up with “de facto” standards embodied in popular systems.

41. Security to the Level of a Field! • Can create a view that only returns one field of one tuple. (How?) • Then grant access to that view accordingly. • Allows for arbitrary granularity of control, but: • Clumsy to specify, though this can be hidden under a good UI • Performance is unacceptable if we need to define field-granularity access frequently. (Too many view creations and look-ups.)

42. Mandatory Access Control • Based on system-wide policies that cannot be changed by individual users. • Each DB object is assigned a security class. • Each subject (user or user program) is assigned a clearance for a security class. • Rules based on security classes and clearances govern who can read/write which objects. • Most commercial systems do not support mandatory access control. Versions of some DBMSs do support it; used for specialized (e.g., military) applications.

43. Why Mandatory Control? • Discretionary control has some flaws, e.g., the Trojan horse problem: • Justin has access to secret information that Dick wants to know. • Dick creates a table called Secret and gives INSERT privileges to Justin (who doesn’t know about this). • Dick modifes the code of a database application program used by Justin to additionally write some secret data to table Secret. • This requires hacking the OS security system a bit. • Now, Dick can see the secret data. • The modification of the code is beyond the DBMSs control, but it can try and prevent the use of the database as a channel for secret information.

44. Bell-LaPadula Model • Objects (e.g., tables, views, tuples) • Subjects (e.g., users, user programs) • Security classes: • Top secret (TS), secret (S), confidential (C), unclassified (U): TS > S> C > U • Each object and subject is assigned a class. • Subject S can read object O only if class(S) >= class(O) (Simple Security Property) • Subject S can write object O only if class(S) <= class(O) (*-Property)

45. Intuition • Idea is to ensure that information can never flow from a higher to a lower security level. • E.g., If Dick is cleared for security class C, Justin for class S, and the secret information has class S: • Dick’s table, Secret, has Dick’s clearance, C. • Justin’s application has his clearance, S. • So, the database application cannot write into table Secret. • The mandatory access control rules are applied in addition to any discretionary controls that are in effect.

46. bid bname color class 101 Salsa Red S 102 Pinto Brown C Multilevel Relations • Users with S and TS clearance will see both rows; a user with C will only see the 2nd row; a user with U will see no rows. • If user with C tries to insert <101,Pasta,Blue,C>: • Allowing insertion violates key constraint • Disallowing insertion tells user that there is another object with key 101 that has a class > C! • Problem resolved by treating class field as part of key.

47. Polyinstantiation • Consider previous table with <101,Pasta,Blue,C> inserted. • What is the color of boat with bid=101? • Only the highest visible security level exists. • Both exist • The proper policy is chosen according to the semantics of the application • Notice that the answer to the question depends on your clearances. This is called polyinstantiation.

48. Internet-Oriented Security • Key Issues: User authentication and trust. • When DB must be accessed from a secure location, password-based schemes are usually adequate. • For access over an external network, trust is hard to achieve. • If someone with Sam’s credit card wants to buy from you, how can you be sure it is not someone who stole his card? • How can Sam be sure that the screen for entering his credit card information is indeed yours, and not some rogue site spoofing you (to steal such information)? How can he be sure that sensitive information is not “sniffed” while it is being sent over the network to you? • Encryption is a technique used to address these issues.

49. Encryption • “Masks” data for secure transmission or storage • Encrypt(data, encryption key) = encrypted data • Decrypt(encrypted data, decryption key) = original data • Without decryption key, the encrypted data is meaningless gibberish • Symmetric Encryption: • Encryption key = decryption key; all authorized users know decryption key (a weakness). • DES, used since 1977, has 56-bit key; AES has 128-bit (optionally, 192-bit or 256-bit) key • Public-Key Encryption: Each user has two keys: • User’s public encryption key: Known to all • Decryption key: Known only to this user • Used in RSA scheme (Turing Award!)

50. Certifying Servers • If Amazon distributes their public key, Sam’s browser will encrypt his credit card information using it. • So, only Amazon can decipher the credit card information, since no one else has Amazon’s private key. • But how can Sam (or his browser) know that the public key for Amazon is genuine? • Amazon contracts with, say, Verisign, to issue a certificate <Verisign, Amazon, amazon.com, public-key> • This certificate is stored in encrypted form, encrypted with Verisign’s private key, known only to Verisign. • Verisign’s public key is known to all browsers, which can therefore decrypt the certificate and obtain Amazon’s public key, and be confident that it is genuine. • Your browser then generates a temporary session key, encodes it using Amazon’s public key, and sends it to Amazon. • All subsequent msgs between the browser and Amazon are encoded using symmetric encryption (e.g., DES), which is more efficient than public-key encryption. • This solves the problem of you trusting Amazon, but not the problem of Amazon trusting you.