Constraints and Assertions Use queries to capture constraints

Constraints and Assertions • Use queries to capture constraints – Useful when more general Integrity Constraints than keys are involved. • Constraints – Attribute-based CHECK – Tuple-based CHECK • Assertions – Extended Assertions • Commercial DBMSs generally support tuplebased check, not assertions, though assertions part of SQL standard 1

Attribute-Based Checks • Easy way to introduce more powerful tuple-based checks. • Want to specify that every employee must have a salary of at least 20, 000 • Attrribute based check: When we specify attribute in CREATE TABLE – Follow this by a condition that must hold. CREATE TABLE EMPLOYEE … SALARY INTEGER CHECK (SALARY >= 20000) • Checked only when associated attribute changes – An insert occurs : new employee inserted – An update occurs: when SALARY is changed 2

Attribute-Based Checks • Condition can also refer to (in subquery) – other columns – other tables • However, condition is only checked when the “main” attribute is changed – not when other attributes changed. 3

Attribute-Based Checks • Eg: in SP table, want to make S# foreign key to S. CREATE TABLE SP … S# CHAR(5) CHECK (S# IN (SELECT S# FROM S)) • • If try to insert row with S 8 into SP ? rejected If try to change S 1 in SP to S 8 ? rejected If delete S 2 from S ? Not rejected. Why ? Won’t check when changes to S 4

Tuple-Based Checks • Similar to attribute-based check – but done on whole table • In S table, we want to enforce : – all London suppliers have STATUS = 20 • Conceptually we would think of this as – If a supplier is in London, then the STATUS = 20 – CITY = ‘London’ => STATUS = 20 • Problem : no “=>” in SQL – How to do ? 5

Tuple-Based Checks • We assert: CITY < > ‘London’ OR STATUS = 20 – why is the same as • CITY = ‘London’ => STATUS = 20 • If CITY = ‘London’ then the first condition (CITY < > ‘London’ ) is false, so ? • secondition (STATUS = 20) has to be true CREATE TABLE S( S# … CITY … STATUS … CHECK (CITY < > ‘London’ OR STATUS = 20) 6

Tuple-Based Checks • Stronger than attribute based check since checked when tuple inserted (but not deleted) or any attribute updated – With attribute based check, only when associated attribute is update. • Suppose done as attribute based check • STATUS INTEGER CHECK (CITY < > ‘London’ OR STATUS = 20) • Suppose CITY changes ? Problem ? • Won’t check • How about with tuple-based ? Problem ? 7

Tuple-Based Checks • Condition being checked can be anything that could follow a WHERE • Can refer to other tables – No checking done when other tables changed CREATE TABLE SP( S# … CHECK (S# IN (SELECT S# FROM S) • Will this work ? • No : not when S# changes/gets deleted in S. 8

SQL Assertions • Database-schema constraint – Not associated with any table • Used when we want to do a check over multiple tables • Checked when any involved tables changes. • Part of SQL standard, not currently supported by most commercial DBMS (eg: Oracle, SQL Server) because of performance penalty • Syntax: CREATE ASSERTION name CHECK (condition); 9

Assertion Example • Want to assert that there at least 4 employees. • • Is it OK if we do as tuple-based check ? No: why not ? Won’t check in case of DELETION Do as assertion CREATE ASSERTION atleastfouremp CHECK( (SELECT COUNT (ssn) FROM EMPLOYEE) >= 4); 10

Assertion Example • Condition: If A then B specified as – not exists (A and not B) • Any supplier whose STATUS >= 20 should be supplying at least 3 different parts CREATE ASSERTION highstat CHECK( NOT EXISTS ( SELECT * FROM S WHERE STATUS > = 20 AND (SELECT COUNT(DISTINCT P#) FROM SP WHERE SP. S# = S. S#) < 3 )); 11

SQL 3 Extended Assertions [UW] • SQL 2: system decides when a constraint might be violated, decides when to perform check. • SQL 3: programmer specifies when to check. • The salary of an employee who works in accounting must be at least 30000. In SQL 2 with assertions CREATE ASSERTION acctsal. SQL 2 assert CHECK (NOT EXISTS ( SELECT * FROM EMPLOYEE, DEPT WHERE EMPLOYEE. DNO = DEPT. DNUMBER AND DNAME = ‘ACCOUNTING’ AND EMPLOYEE. SALARY < 30000 )); 12

SQL 3 Extended Assertions [UW] • SQL 3: with extended assertions – when should check be performed ? CREATE ASSERTION acctsal. SQL 3 extassert INSERT ON EMPLOYEE UPDATE ON DEPARTMENT UPDATE OF SALARY, DNO ON EMPLOYEE CHECK (NOT EXISTS ( EMPLOYEE. DNO = DEPT. DNUMBER AND DNAME = ‘ACCOUNTING’ AND EMPLOYEE. SALARY < 30000 )); 13

SQL 3 Extended Assertions [UW] • Advantages of SQL 3 extended assertions ? • Programmer has more control. – Eg: for efficiency reason, may choose not to perform check when DNO is changed • Makes it easier for system implementers – They don’t have to figure out when violation can occur • Disadvantages of SQL 3 extended assertions ? • Programmer must discover when violation might possibly occur – o/w database could go into inconsistent state 14

Introduction to DB Security • Confidentiality: • 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. 15

Access Controls • Security Policy: specifies who is authorized to do what. • Security Mechanism: allows us to enforce a chosen security policy. • Two main mechanisms at the DBMS level: – – Discretionary access control • Part of SQL standard : we will study. Mandatory access control • Not part of SQL standard • We will study, but not in great detail • [EN] has more detail 16

Discretionary Access Control • Each user is given specific priveleges to access particular pieces of data. – Eg: Mike can look at (not modify) EMP – Eg: Alice can modify address in EMP • DBA: is a privileged user – Superuser, root – Can do things ordinary users can’t – Creates accounts: login names, passwords 17

Discretionary Access Control • Owner: user who creates table or view, automatically gets all privileges on it – Privileges can’t be revoked – Owner can grant these privileges to others – Only owner can do CREATE, ALTER and DROP • DBMS: keeps track of privileges – ensures only legitimate ops performed. • If privileges not granted, not present – default: no privilege 18

DCL: Data Control Language GRANT {ALL PRIVILEGES, privilege list} ON tablename | viewname TO {PUBLIC, user names} [WITH GRANT OPTION] • WITH GRANT OPTION: if this is included, can pass some or all of the privileges (with or without grant option) to other users. • No WHERE statement in GRANT. – How to give access to some rows, some columns ? • done through views 19

Privileges • SELECT: Can read all columns • INSERT: Can insert tuples with values in any column. – INSERT (col-name): Can insert tuples with non-null or nondefault values in this column. Other columns: null or default. • UPDATE: all columns – UPDATE (col-name): • DELETE: rows • REFERENCES : can create a foreign key that refers to to all columns – REFERENCES (col-name): to this column 20

REVOKE [GRANT OPTION FOR] {ALL PRIVELEGES, privelege list} ON tablename | viewname FROM {PUBLIC, user names} CASCADE|RESTRICT • GRANT OPTION FOR: only lose right to propagate this privilege. • CASCADE: if they had propagated, others lose • RESTRICT: if they had propagated, and if others lose, REVOKE is rejected 21

Examples From [EN] • Suppose that A 1 creates the two base relations EMPLOYEE and DEPARTMENT; A 1 is then owner of these two relations and hence all the relation privileges on each of them. EMPLOYEE NAME SSN BDATE ADDRESS SEX SALARY DNO DEPARTMENT DNUMBER DNAME MGRSSN 22

Grant Example From [EN] • A 1 wants to grant A 2 the privilege to insert and delete tuples in both of these relations, but A 1 does not want A 2 to be able to propagate these privileges to additional accounts: GRANT INSERT, DELETE ON EMPLOYEE, DEPARTMENT TO A 2; 23

Grant Example From [EN] • A 1 wants to allow A 3 to retrieve information from either of the two tables and also to be able to propagate the SELECT privilege to other accounts. A 1 can issue the command: GRANT SELECT ON EMPLOYEE, DEPARTMENT TO A 3 WITH GRANT OPTION; • A 3 can grant the SELECT privilege on the EMPLOYEE relation to A 4 by issuing: GRANT SELECT ON EMPLOYEE TO A 4; • Notice that A 4 can not propagate the SELECT privilege because GRANT OPTION was not given to A 4. 24

Revoke Example From [EN] • A 1 decides to revoke the SELECT privilege on the EMPLOYEE relation from A 3; A 1 can issue: REVOKE SELECT ON EMPLOYEE FROM A 3 CASCADE; • DBMS will automatically revoke the SELECT privilege on EMPLOYEE from A 4 also – because A 3 granted that privilege to A 4 – A 3 does not have the privilege any more • If RESTRICT : then REVOKE rejected 25

Grant Example From [EN] • A 1 wants to give back to A 3 a limited capability to SELECT from the EMPLOYEE relation and wants to allow A 3 to be able to propagate the privilege. • The limitation is to retrieve only the NAME, BDATE, and ADDRESS attributes and only for the tuples with DNO=5. • Can’t do with WHERE because WHERE not allowed in GRANT statement. – How to do ? • Do with views. 26

Priveleges on Views [EN] Eg • A 1 creates the view: CREATE VIEW A 3 EMPLOYEE AS SELECT NAME, BDATE, ADDRESS FROM EMPLOYEE WHERE DNO = 5; • After the view is created, A 1 can grant SELECT on the view A 3 EMPLOYEE to A 3 as follows: GRANT SELECT ON A 3 EMPLOYEE TO A 3 WITH GRANT OPTION; 27

Creating Views • What kind of privilege do you need to create a view. Eg: – suppose A 1 owns Emp – A 2 wants to execute • CREATE VIEW TEMPVIEW ON Emp … – What kind of privilege will A 1 have to give A 2 ? • SELECT - why ? • A 2 has to read values from Emp 28

Update Example From [EN] • A 1 wants to allow A 4 to update only the SALARY attribute of EMPLOYEE. A 1 can issue: GRANT UPDATE ON EMPLOYEE (SALARY) TO A 4; 29

Authorization graphs • If user has privilege in multiple ways and loses the privilege in one way? • Suppose B 1 owns EMP : B 1 : GRANT SELECT ON EMP TO B 2 WITH GRANT OPTION; B 1 : GRANT SELECT ON EMP TO B 3; B 2 : GRANT SELECT ON EMP TO B 3, B 4; B 1 : REVOKE SELECT ON EMP FROM B 2 CASCADE; • Will B 3 or B 4 still have SELECT privileges ? • Authorization Graphs 30

REFERENCE • Why is REFERENCE privilege different from SELECT privilege ? – Suppose B 1 owns Employee table and gives B 2 REFERENCE privilege – B 2 creates Junk table with ESSN foreign key (RESTRICT/NO ACTION) to SSN in Employee • In Employee there is an employee with SSN 123 • In Junk there is a row with ESSN 123 – B 1 tries to delete row with SSN 123. Result ? • Can’t delete since would lead to FK violation • B 2 has some control over B 1’s Employee table 31

Mandatory Access Control • System wide policy – not decided by particular user. • Users, documents have security classification. – Typical security classes: • Top secret (TS), secret (S), confidential (C), unclassified (U): TS ≥ C ≥ U – User with Secret classification can read Secret, Classified, Unclassified documents, but not Top Secret • Not part of SQL standard 32

Discretionary Access Control (DAC) and Mandatory Access Control (MAC)[Wikipedia] • DAC allows users the ability to make decisions – Eg : Bob: Alice allowed to UPDATE Employee table • With MAC, security policy is centrally controlled – users cannot override or modify this policy • either accidentally or intentionally. – Eg: user can’t grant access to table that would otherwise be restricted. – MAC-enabled systems allow policy administrators to implement organization-wide security policies – This allows security administrators to define a central policy that is guaranteed to be enforced for all users. 33

• • DAC Trojan horse problem : modified [RG] Eg Justin has table J, doesn’t want Dick to see Dick: create Horsie table, – – • Dick modifies code of application program used by Justin to access the table J – • • give INSERT privileges on Horsie to Justin doesn’t know about this to additionally write some secret data to table Horsie. When Justin accesses J, what will happen ? Modified application program will write information into Horsie – Now Dick can see the secret info. 34

Mandatory Access Control [RG] • MAC security policies can stop the previous Trojan horse problem – Trojan horse is a piece of software which appears to perform a certain action but in fact performs another • MAC: 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. Not part of SQL standard 35

Bell-La. Padula model • Objects (e. g. , tables, views, tuples, columns, particular values) • Subjects (e. g. , users, user programs) • Typical security classes: – • Top secret (TS), secret (S), confidential (C), unclassified (U): TS ≥ C ≥ U Subjects and objects each have one of the security classifications – – – Eg: user Bob has classification TS Eg: EMPLOYEE table has classification U Eg: 123’s salary has classification S 36

MAC: Restrictions • Two restrictions enforced on data access based on subject/object classifications: • Simple security property : A subject S is not allowed read access to an object O unless class(S) ≥ class(O). – – – • Eg: user Alice has classification C Eg: 123’s salary has classification S Alice will not be allowed to read 123’s salary Can you guess what the other property will be? 37

MAC: Restrictions • star property (or * property) : A subject S is not allowed to write an object O unless class(S) ≤ class(O). – – – Eg: user Bob has classification TS Eg: 123’s salary has classification S Bob will not be allowed to write 123’s salary What’s the idea ? • Idea is to ensure that information can never flow from a higher to a lower security level • – Intentionally or inadvertantly – o/w Bob could take TS info, write it into U table and U user would be able to read 38

Multilevel Tables: Filtering [RG] Eg bid 101 102 bname Salsa Pinto color Red Brown class S C • [EN] has more complex Eg, we study simpler [RG] Egs • only tuples (rows) have classification; not attributes • • • What should user with S clearance see? will see both rows. TS user will see same. What should a C user see ? will only see 2 nd row. What should U user see ? a user with U will see no rows i. e. empty table 39

Multilevel Tables: Polyinstantiation [RG] Eg bid 101 102 bname Salsa Pinto color Red Brown class S C • C user is seeing only the 2 nd row. Now suppose C user tries to insert <101, Pasta, Blue, C> – what should happen ? • If we don’t allow insertion ? • Disallowing insertion tells user that there is another object with key 101 that has a class > C – Not supposed to know this 40

Multilevel Tables: Polyinstantiation [RG] Eg • So we allow C to insert. Now diff. problem • S user is entitled to S info <101, Salsa, Red, S> Don’t want to tell C user that there is S info Polyinstantiation : users can see different things • • – – • • C user sees <101, Pasta, Blue, C> S user sees <101, Salsa, Red, S> bid is key, but “ 101” repeated. How to fix ? Treat classification field as part of key – (bid, classification) is key 41

Comparing Discretionary Access Control (DAC) and Mandatory Access Control (MAC) • DAC offers flexibility – makes it suitable for a large variety of application domains. • The main drawback of DAC model is vulnerability to malicious attacks – such as Trojan horses embedded in application programs. • MAC ensure a high degree of protection – because prevents any illegal flow of information. • But MAC has drawback of being rigid – only applicable in limited environments. 42

Comparing Discretionary Access Control (DAC) and Mandatory Access Control (MAC) • In many practical situations, DAC preferred – better trade-off between security and applicability. • Most commercial systems don’t support MAC – When supported, typically combined with DAC mechanisms • MAC rules applied in addition to DAC controls • MAC used for specialized applications – Eg: military – Do. D Trusted Computer System Evaluation Criteria (Orange book) includes DAC and MAC 43