Database Security and Privacy

1. Database Securityand Privacy Some slides were taken from 463.5.1 Database Access Control Tutorial, Lars Olson, UIUC CS463, Computer Security

2. Database Security • Protect Sensitive Data from • Unauthorized disclosure • Unauthorized modification • Denial of service attacks

3. Protection of Data Confidentiality • Access control – which data users can access • Information flow control – what users can do with the accessed data

4. Access Control • Subject: active entity that requests access to an object - e.g., user or program • Object: passive entity accessed by a subject - e.g., record, relation, file • Access right (privileges): how a subject is allowed to access an object - e.g., subject s can read object o

5. Access Control • Ensures that all direct accesses to object are authorized • Protects against accidental and malicious threats by regulating the read, write and execution of data and programs

6. Protection Object • Database • Relation • Record • Attribute • Element Advantages vs. disadvantages of supporting different granularity levels

7. Access Control Policies • Discretionary Access Control (DAC) • Mandatory Access Control (MAC) • Role-Based Access Control (RBAC)

8. Discretionary Access Control (DAC) • For each subject access right to the objects are defined • (subject, object, +/- access mode) • (Black, Employee-relation, read) • User based • Grant and Revoke • Problems: - Propagation of access rights - Revocation of propagated access rights

9. GRANT SELECT ON Employee TO Red GRANT SELECT ON Employee TO Black WITH GRANT OPTION ? Brown revokes grant given to Black ? • Brown does not want • Red to access the • Employee relation GRANT UPDATE(Salary) ON Employee TO White DAC by Grant and Revoke Black Red Brown (owner) White

10. Individuals Resources Server 1 Server 2 Server 3 DAC • Restricts access to objects based solely on the identity of users who are trying to access them. Application Access List LegacyApps Name Access Tom Yes John No Cindy Yes

11. Quick SQL Review • Creating tables: createtable table_name ( column1 type1, column2 type2, ... ); • Deleting tables: droptable table_name;

12. Quick SQL Review • Types: • int • float • date • char(size) • Always delimited by single quote (apostrophe) • Use two single quotes to represent the apostrophe character • varchar(size) (varchar2 in Oracle) • text (long in Oracle)

13. Quick SQL Review • Querying tables: select column1, column2 from table_name; or select * from table_name; • Conditions: select columns from table_name where condition;

14. Quick SQL Review • Inserting new rows: insertinto table_name values (value1, value2); or insertinto table_name set column1=value1, column2=value2, ...; • Updating rows: update table_name set column1=value1 where condition;

15. Quick SQL Review • Deleting rows: deletefrom table_name where condition; • Set values in conditions: select * from table_name where column in (select_statement); or select * from table_name where column in (value1, value2, ...);

16. Quick SQL Review • Creating functions: create [orreplace] function function_name (parameters) return return_type as [declare_local_variables] begin ... end; /

17. SQL grant Syntax grant privilege_list on resource to user_list; • Privileges include select, insert, etc. • Resource may be a table, a database, a function, etc. • User list may be individual users, or may be a user group Griffiths Wade 76

18. Example Application • Alice owns a database table of company employees: name varchar(50), ssn int, salary int, email varchar(50) • Some information (ssn, salary) should be confidential, others can be viewed by any employee.

19. Simple Access Control Rules • Suppose Bob needs access to the whole table (but doesn’t need to make changes): grant select on employee to bob; • Suppose Carol is another employee, who should only access public information: grant select(name,email) on employee to carol; • not implemented in PostgreSQL (see next slide) • not implemented for select in Oracle • implemented in MySQL

20. Creating Views • Careful with definitions! • A subset of the database to which a user has access, or: • A virtual table created as a “shortcut” query of other tables • View syntax: createview view_name as query_definition; • Querying views is nearly identical to querying regular tables

21. View-Based Access Control • Alternative method to grant Carol access to name and email columns: createview employee_public asselect name,email from employee; grantselecton employee_public to carol;

22. Row-Level Access Control • Suppose we also allow employees to view their own ssn, salary: createview employee_Carol asselect * from employee where name='Carol'; grantselecton employee_Carol to carol; • And we allow them to update their e-mail addresses: grantupdate(email) on employee_Carol to carol; • (Or create yet another new view…)

23. Delegating Policy Authority grant privilege_list on resource to user_list withgrantoption; • Allows other users to grant privileges, including “with grant option” privileges • “Copy right” from Access Control lecture • Can grant subset privileges too • Alice: grantselecton table1 to bob withgrantoption; • Bob: grantselect(column1) on table1 to carol withgrantoption;

24. SQL revoke Syntax revoke privilege_list on resource from user_list; • What happens when a user is granted access from two different sources, and one is revoked? • What happens when a “with grant option” privilege is revoked?

25. Griffiths-Wade Model • Sequences of grant/ revoke operations • When a privilege is revoked, the ACLs should be indistinguishable from a sequence in which the grant never occurred.

26. Grants from Multiple Sources • grant(Alice,Bob) • grant(Alice,Carol) • grant(Carol,Bob) • revoke(Alice,Bob) • grant(Alice,Bob) • grant(Alice,Carol) • grant(Carol,Bob) • revoke(Alice,Bob) Bob Alice Carol

27. Not as Easy as it Looks! • grant(Alice,Bob) • grant(Bob,Carol) • grant(Carol,Bob) • revoke(Alice,Bob) • grant(Alice,Bob) • grant(Bob,Carol) • grant(Carol,Bob) • revoke(Alice,Bob) Bob Alice Carol

28. Cascading Revocations • grant(Alice,Bob) • grant(Alice,Carol) • grant(Carol,David) • grant(Bob,Carol) • revoke(Alice,Carol) • grant(Alice,Bob) • grant(Alice,Carol) • grant(Carol,David) • grant(Bob,Carol) • revoke(Alice,Carol) Alice ? Carol David Bob

29. Meanwhile, in the Real World... • Account privileges get changed all the time • We don’t always want to redo everything • Tedious • Involves other users’ actions • SQL revoke command has two optional arguments: • cascade: undoes all dependent grant commands • restrict: exits with failure if there exist dependent grants Ramakrishnan Gehrke 03

30. User1: • creates Example_Table within Example_Schema. • grants SELECT WITH GRANT OPTION on Example_Table to User2. • User2 grants the SELECT WITH GRANT OPTION on Example_Table to User3 • User3 grants SELECT on Example_Table to the Reviewer role. • >REVOKE SELECT ON EXAMPLE_TABLE FROM USER2 CASCADE

31. User1: • creates Example_Table within Example_Schema. • grants SELECT WITH GRANT OPTION on Example_Table to User2. • User2 grants the SELECT WITH GRANT OPTION on Example_Table to User3 • User3 grants SELECT on Example_Table to the Reviewer role. • >REVOKE SELECT ON EXAMPLE_TABLE FROM USER2 CASCADE • When the superuser or User1 executes this statement, the SELECT privilege on Example_Table is revoked from User2, User3, and the Reviewer Role. (The GRANT privilege is also revoked from User2 and User3.)

32. User1: • creates Example_Table within Example_Schema. • grants SELECT WITH GRANT OPTION on Example_Table to User2. • User2 grants the SELECT WITH GRANT OPTION on Example_Table to User3 • User3 grants SELECT on Example_Table to the Reviewer role. • >REVOKE SELECT ON EXAMPLE_TABLE FROM USER2 RESTRICT • Since there are depending grants, the revoke fails.

33. DAC Overview • Advantages: • Intuitive • Easy to implement • Disadvantages: • Inherent vulnerability (look TH example) • Maintenance of ACL or Capability lists • Maintenance of Grant/Revoke • Limited power of negative authorization

34. RBAC (Role Based Access Control • Roles permit common privileges for a class of users can be specified just once by creating a corresponding “role” • Privileges can be granted to or revoked from roles • Roles can be assigned to users, and even to other roles

35. Role 1 Role 2 Role 3 RBAC Individuals Roles Resources Server 1 Server 2 Server 3 Users change frequently, Roles don’t

36. Mandatory Access Control (MAC) • Security label - Top-Secret, Secret, Public • Objects: security classification - File 1 is Secret, File 2 is Public • Subjects: security clearances - Brown is cleared to Secret, Black is cleared to Public • Dominance() - Top-Secret  Secret  Public

37. MAC • Access rights: defined by comparing the security classification of the requested objects with the security clearance of the subject • If access control rules are satisfied, access is permitted • Otherwise access is rejected • Granularity of access rights!

38. MAC SIPRNET LegacyApps Individuals Resources Server 1 “Top Secret” Server 2 “Secret” Server 3 “Classified”

39. MAC • Single security property: a subject S is allowed a read access to an object O only if label(S) dominates label(O) • Star-property: a subject S is allowed a write access to an object O only if label(O) dominates label(S) No direct flow of information from high security objects to low security objects!

40. Multilevel Security • Multilevel security  users at different security level, see different versions of the database • Problem: different versions need to be kept consistent and coherent without downward signaling channel (covert channel)

41. Multilevel Relation Example Top-secret user sees all data Secret user sees Secret-View: CSCE 790 - Farkas 41