Introduction to DB Security

1. 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. 1

2. 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 2

3. 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 3

4. 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 4

5. GRANT 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 5

6. 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 non-default 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 6

7. REVOKE 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 7

8. Examples From [EN] EMPLOYEE • Suppose that A1 creates the two base relations EMPLOYEE and DEPARTMENT; A1 is then owner of these two relations and hence all the relation privileges on each of them. DEPARTMENT 8

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

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

11. Revoke Example From [EN] A1 decides to revoke the SELECT privilege on the EMPLOYEE relation from A3; A1 can issue: REVOKE SELECT ON EMPLOYEE FROM A3 CASCADE; • DBMS will automatically revoke the SELECT privilege on EMPLOYEE from A4 also • because A3 granted that privilege to A4 • A3 does not have the privilege any more • If RESTRICT : then REVOKE rejected 11

12. Grant Example From [EN] A1 wants to give back to A3 a limited capability to SELECT from the EMPLOYEE relation and wants to allow A3 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. 12

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

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

15. Update Example From [EN] A1 wants to allow A4 to update only the SALARY attribute of EMPLOYEE. A1 can issue: GRANT UPDATE ON EMPLOYEE (SALARY) TO A4; 15

16. Authorization graphs If user has privilege in multiple ways and loses the privilege in one way? Suppose B1 owns EMP : B1 : GRANT SELECT ON EMP TO B2 WITH GRANT OPTION; B1 : GRANT SELECT ON EMP TO B3; B2 : GRANT SELECT ON EMP TO B3, B4; B1 : REVOKE SELECT ON EMP FROM B2 CASCADE; Will B3 or B4 still have SELECT privileges ? Authorization Graphs 16

17. REFERENCE Why is REFERENCE privilege different from SELECT privilege ? Suppose B1 owns Employee table and gives B2 REFERENCE privilege B2 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 B1 tries to delete row with SSN 123. Result ? Can’t delete since would lead to FK violation B2 has some control over B1’s Employee table 17

18. 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 ≥ S ≥ C ≥ U User with Secret classification can read Secret, Classified, Unclassified documents, but not Top Secret Not part of SQL standard 18

19. 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. 19

20. DAC Trojan horse problem : modified [RG] Eg • Justin has table J, doesn’t want Dick to see • Dick: create Horsie table, • give INSERT privileges on Horsie to Justin • Justin doesn’t know about this • Dick modifies code of application program used by Justin to access the table J • 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. 20

21. 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 21

22. Bell-LaPadula 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 ≥ S ≥ 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 22

23. MAC: Restrictions Tworestrictions 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? 23

24. 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 24

25. Multilevel Tables: Filtering [RG] Eg bid bname color class 101 Salsa Red S 102 Pinto Brown 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 2nd row. What should U user see ? • a user with U will see no rows i.e. empty table 25

26. Multilevel Tables: Polyinstantiation [RG] Eg bid bname color class 101 Salsa Red S 102 Pinto Brown C • C user is seeing only the 2nd 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 26

27. 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 27

28. 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. 28

29. 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 DoD Trusted Computer System Evaluation Criteria (Orange book) includes DAC and MAC 29

30. Updates on Views CREATE VIEW REDPARTS AS SELECT * FROM P WHERE COLOR = ‘RED’; • Suppose make a change toREDPARTS UPDATE REDPARTS SET WEIGHT = 30 WHERE P# = ‘P1’; • What effect will this have on the P table ? • Will change P1’s weight to 30 • What about DELETE FROM REDPARTS WHERE P# = ‘P1’; • Will delete P1 from P

31. Updates on Views • We create the following view: CREATE VIEW PARTQUANT (PNO, TOTALQT) AS SELECT P#, SUM (QTY) FROM SP GROUP BY P# ; • Suppose we execute UPDATE PARTQUANT SET TOTALQT = 339 WHERE P# = ‘P1’; • Will not work – why ? • TOTALQT is a derived attribute

32. Updatable Views • Many subtleties but broadly speaking, views which are derived from a single base table with a subset of rows and columns are updatable • With aggregate operators : not updatable • If primary key from base table is missing in view, not updatable • Some views which are derived from multiple tables are updatable • Views which we would regard as read-only can sometimes be modified by using INSTEAD OF triggers; will study later.

33. CHECK OPTION CREATE VIEW REDPARTS AS SELECT * FROM P WHERE COLOR = ‘Red’ [WITH CHECK OPTION] • P1 will be in REDPARTS since it is red • Now we do the following: UPDATE REDPARTS SET COLOR = ‘Green’ WHERE P# = ‘P1’; • What will happen to P1 in REDPARTS ?

34. CHECK OPTION • P1 is now GREEN so … • Does not satisfy the view condition SELECT * FROM REDPARTS • P1 will not show up • This is a strange situation • Didn’t delete row with P1, but P1 not showing up • should this be allowed ? • SQL gives you a choice of allowing or not allowing this to happen

35. CHECK OPTION • If WITH CHECK OPTION specified then UPDATE REDPARTS SET COLOR = ‘Green’ WHERE P# = ‘P1’; • UPDATE will not be allowed • Can still modify base table directly • Just can’t do via modifying view • If CHECK OPTIONnot specified then allowed: • Similar situation with INSERT INTO REDPARTS(P#,COLOR) VALUES (‘P8’, ‘Blue’);

36. View Implementation through Query Modification • Query modification: DBMS changes a query on a view to a query on underlying table. SELECT * FROM REDPARTS WHERE WEIGHT > 15; is implemented as SELECT * FROM P WHERE COLOR = ‘RED’ AND WEIGHT > 15; • Disadvantage ?

37. View Implementation through Query Modification • Suppose base table is not updated, but view being queried repeatedly • With query modification, how will repeated queries on the view be handled ? • If view is being queried repeatedly then view query has to be executed every time • Even if base table is not being updated • Worse if complex query • Eg : aggregate function with large table • Alternative ?

38. View Implementation through View materialization • View materialization : physically create and store the view as a temporary table. • To user: same as query modification. Upside? • Does not have to be calculated from scratch each time view queried. Downside? • Have to update view when table changed • Used in Oracle • Uses both query modification and view materialization • Optimizer decides which is likely to be better

39. View maintenance • View maintenance: need to change view to reflect changes to underlying base table CREATE VIEW REDPARTS AS SELECT * FROM P WHERE COLOR = ‘Red’; • When to do view maintenance ? • Immediate view maintenance:as soon as table changes, refresh the view. Downside? • Lazy view maintenance:refresh view only when there is a query on it. Downside? • Deferred view maintenance:refresh views after some fixed period of time. Eg: every 3 hours. Downside?

40. Data Warehousing Systems Used for analysis, not transaction processing Since no transaction processing, consequence ? No updates. Impact of this ? Data is denormalized and stored together and materialized views are used Advantage of denormalized ? Data in fewer tables Fewer joins. Why do we normalize? Does that logic apply here ? No updates so no modification anomalies Advantage of materialized views? 40

41. Data Warehousing Systems Don’t have to go back and recalculate views every time a view is referred to What is the problem with materialized views? Have to change on updates Does it apply here ? Can create lots of indexes (indexes on most columns) What is the problem with having lots of indexex? Cost of maintaining lots of indexes. Does this apply here ? No updates 41

42. Active Databases and Triggers • Motivation : suppose want to do the following: • When Date of birth entered/changed, automatically enter/change age of employee • If employee becomes a manager, automatically increase his salary by 10% • If employee is working on less than 3 projects, notify his supervisor • How to do this ? • Want some kind of procedure that should run automatically when some condition is specified

43. Active Databases and Triggers • Trigger: procedure that starts automatically if specified changes occur. Three parts: • Event: activates the trigger • INSERT, UPDATE, DELETE • Can also be DML like CREATE TABLE etc • Condition: optional true/false condition • Trigger should run only if condition true • Action: what happens if the trigger runs • One or more SQL statements that will be automatically executed

44. SQL standard Trigger Format CREATE TRIGGER triggername [BEFORE|AFTER|INSTEAD OF] [INSERT|DELETE|UPDATE[ATTRIBUTE]] [REFERENCING] clause [WHEN] condition action statements [FOR EACH ROW|FOR EACH STATEMENT]

45. When is Action Executed • AFTER • Executes after the event • BEFORE • Executes before the event • INSTEAD OF • Executes insteadof the event • Note that event does not execute in this case • E.g., to update views which otherwise we think of as not being directly updatable

46. Statement-level vs Row-Level • SQL statement : INSERT/UPDATE/DELETE multiple rows. Does trigger get executed one time in total or once for each row ? • Statement-level triggers • FOR EACH STATEMENT : statement-level trigger • Execute once for the SQL statement • Default • Row-level triggers • FOR EACH ROW : a row-level trigger • When the EVENT happens (Eg: statement executed) • trigger executed separately for each affected row

47. Condition, Action • Any true/false condition to control whether a trigger is activated on not • Absence of condition means that the trigger will always execute for the event • Otherwise, condition is evaluated. When? • Before the event for BEFORE trigger • After the event for AFTER trigger • Action specifies relevant modifications • A sequence of one or more SQL statements

48. Trigger example • Eg: If emp salary is reduced by more than 10%, reduce it by only 10% - be kind ! CREATE TRIGGER UNHTRIGGER AFTER UPDATE OF SALARY ON EMP REFERENCING OLD AS O, NEW AS N WHEN (N. SALARY < 0.9* O. SALARY ) UPDATE EMP SET SALARY = 0.9 * O.SALARY WHERE SSN = N.SSN FOR EACH ROW;

49. Elmasri Oracle Trigger Example • No triggers in MS Access • Oracle Trigger syntax is similar to SQL standard • Details in Elmasri • Eg: If TotalSal attribute in Dept, want to keep it updated • Eg: When new employee added to a Dept, modify Total_sal of the Department to include the new employees salary • DNO attribute in Emp allowed to be null • This trigger will execute AFTER INSERT ON Employee table • It will do the following FOR EACH ROW • WHEN NEW.Dno is NOT NULL • The trigger will UPDATE the DEPARTMENT table • By Setting the new Total_sal to be the sum of • old Total_sal and NEW. Salary • WHERE the Dno matches the NEW.Dno;

50. Elmasri Oracle Trigger Example CREATE TRIGGER Total_sal1 AFTER INSERT ON Employee FOR EACH ROW WHEN (NEW.Dno is NOT NULL) UPDATE DEPARTMENT SET Total_sal = Total_sal + NEW. Sal WHERE Dno = NEW.Dno; Can be CREATE or ALTER Can be FOR, AFTER, INSTEAD OF Can be INSERT, UPDATE, DELETE The condition The action • Why for each row ? Suppose we were adding 5 new employees, would want to do this for each employee • For what other conditions could Total Sal be changed?