Chapter 6 Database Security Stallings Chp 5 10312020

Chapter 6 - Database Security Stallings Chp. 5 10/31/2020 Prof. Ehud Gudes Security Ch 6 1

Levels of a Database System END USER EXTERNAL LEVEL EXTERNAL VIEW … EXTERNAL VIEW External / conceptual mapping CONCEPTUAL LEVEL CONCEPTUAL SCHEMA Conceptual / internal mapping INTERNALL EVEL 10/31/2020 INTERNAL SCHEMA Prof. Ehud Gudes STORED DATABASE Security Ch 6 2

A Typical Architecture of a DBMS 10/31/2020 Prof. Ehud Gudes Security Ch 6 3

A typical DBMS Architecture

Introduction to DB Security § Secrecy: Users should not be able to see things they are not supposed to. ©e. g. , a student cannot see other student’s grades. § Integrity: Users should not be able to modify things they are not supposed to. ©e. g. , only instructors can assign grades. ©Also, the DBMS should protect the database from non -malicious integrity errors, i. e Concurrency & Recovery issues § Availability: users should be able to see and modify things they are allowed to. 10/31/2020 Prof. Ehud Gudes Security Ch 6 5

Integrity Problems in Databases §Checks on values – single record checks, before and after update checks, etc. §Various integrity constraints – primary keys, unique checks, foreign keys, etc. §General integrity constraints – Constraints, Asserts, Triggers. §Consistency problems as a result of concurrent execution of transactions – CC protocols such as Two-Phase Locking §Integrity problems as results of system failure – Log and Recovery 10/31/2020 Prof. Ehud Gudes Security Ch 6 6

Security - Policies and Mechanism Reminder § Policies – general guidelines on authorization in the system, examples: ©Students can see their grades ©Only instructors can change grades § Mechanisms – techniques to enforce the policies ©Access control ©Encryption Prof. Ehud Gudes Security Ch 6

Categories of Security Policies - Reminder § Mandatory vs. Discretionary (Need to Know). § Ownership vs. Administration § Centralized vs. Distributed § Close vs. Open § Name, Content or Context dependent § Individual, Group or Role based § Information Flow Control based Prof. Ehud Gudes Security Ch 6

DAC - The Access Matrix Model § Subjects - users, groups, applications, transactions § Objects - Files, programs, databases, relations, URLs § Access-types - Read, write, create, copy, delete, execute, kill § Authorization commands - enter, remove, transfer § Authorizers - Owners, users, administrators Prof. Ehud Gudes Security Ch 6

The Access Matrix Model OBJECTS Subjects S 1 SUBJECTS S 1 S 2 S 3 Files S 2 F 1 Call Read Write Send Devices F 2 D 2 Seek Read Kill D 1 Rewind Delete Compatibility Lists 10/31/2020 Access Lists Prof. Ehud Gudes Security Ch 6 10

Mandatory Policy - Bell and La. Padula Model Objective of the model: trying to keep secrets and avoid illegal flow Both subjects and objects are assigned security levels: Public, Confidential, Secret, Top Secret dominance relationship between security levels: ‚ ‘ • Simple Security Property: Successful read access: Clearance (S) Class (O) • *-Property: Successful write access: Class (O) Clearance (S) 10/31/2020 Prof. Ehud Gudes Security Ch 6 11

DBMS Security Policies § Usually DAC, sometimes Mandatory (BLP) § Ownership, Usually (SQL) Distributed § Close § Name, Content dependent (using Views) § Individual, or Role based § Inference control policies § Note, assumes security at lower layers, i. e: OS, Hardware and User authentication Prof. Ehud Gudes Security Ch 6

Database Access Control §DBMS provide access control for database §assume have authenticated user §DBMS provides specific access rights to portions of the database ©e. g. create, insert, delete, update, read, write ©to entire database, tables, selected rows or columns ©possibly dependent on contents of a table entry §can support a range of policies: ©centralized administration ©ownership-based administration ©decentralized administration

DBMS Discretionary Access Control §Based on the concepts of access rights or privileges for objects (tables and view), and mechanisms for giving users privileges (and revoking privileges). §Creator of a table or a view automatically gets all privileges on it. ©DBMS 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. 10/31/2020 Prof. Ehud Gudes Security Ch 6 14

History of Relational Systems §Codd’s paper – early 70 s §Two research systems: Berkeley Ingres and IBM System R – late 70 s §SQL was developed based on System R §All relational systems today are SQL (92, 99) compatible §Security: ©Ingres – DAC, centralized, Rule-based ©System R – DAC, Distributed, View based §SQL security follows System R security 10/31/2020 Prof. Ehud Gudes Security Ch 6 15

Security in INGRES §Centralized, DBA enters authorization rules §The query predicate is combined with the relevant rule predicates to derive a modified query §The concept of query modification – partial results are possible §Difficult to manage a large group of users and rules. 10/31/2020 Prof. Ehud Gudes Security Ch 6 16

Protection in Ingres DBA PROTECTION INTERACTIONS User QUEL query CONVERT TO INTERNAL FORM VALIDATE AND MODIFY SHARED RELATIONS 10/31/2020 Prof. Ehud Gudes Security Ch 6 PROTECTION 17

Protection in Ingres, cont. § (Rule 1) § (Rule 2) § (Rule 3) § (Rule 4) 10/31/2020 range of E is employee permit E to Jones for retrieve (E. name, E. mgr) where E. dept = ‘D 1’ range of E is employee permit E to Jones for retrieve (E. name, E. dept, E. mgr) where E. dept = ‘D 1’ permit E to jones for retrieve (E. name, E. sal) where E. mgr = ‘jones’ permit E to jones for retrieve (E. sal) where E. sal < 100000 Prof. Ehud Gudes Security Ch 6 18

Protection in Ingres, cont. § Query 1 range of E is employee retrieve (E. name, E. Sal) § Only Rule 3 matches, query is modified to; range of E is employee retrieve (E. name, E. Sal) where (E. mgr = ‘Jones’) § Query 2 range of E is employee retrieve (E. name) § Both Rules 1, 2 and 3 match (see Hovereth) 10/31/2020 Prof. Ehud Gudes Security Ch 6 19

The concept of Views – A Window over the database + + · Query simplicity Multiple table-queries may be expressed simply against a view - + · Structural simplicity Views can give a user a 'personalized' interpretation of the database + · Security – a user sees only the portion relevant to him 10/31/2020 · Performance Views may be precompiled and optimized · Update restrictions Many views are 'read-only' Prof. Ehud Gudes Security Ch 6 20

Example Database (1( manages 1 N date ssn function title name N Employee M Project subject dep Assignment salary 10/31/2020 ssn client title Prof. Ehud Gudes Security Ch 6 21

Example Database (2( Project 10/31/2020 Prof. Ehud Gudes Security Ch 6 22

Horizontal / Vertical View AS query CREATE VIEW view-name (-- column_name --) , earning_little emp 23

Mixed View (1( 10/31/2020 Prof. Ehud Gudes Security Ch 6 24

Mixed View (2( 10/31/2020 Prof. Ehud Gudes Security Ch 6 25

Views are ideal for security. . . Views are ideal for security… value independent controls value dependent controls (2) (1), (3), (4) statistical controls (5) context dependent controls (6) 10/31/2020 Prof. Ehud Gudes Security Ch 6 26

The View Update problem How to translate update on a view to unambiguous update on the Base tables? Examples: §Add an employee with salary >5 K into view 1 §Add an employee to View 2 (null values) §Update Salary in View 5. §Add a new row into View 4 Generally the solution is: allow updates only on single level views which include the primary key and all non-null attributes 10/31/2020 Prof. Ehud Gudes Security Ch 6 27

Views and Security §Enable convenient specification and enforcement of access to portions of the database, which include any horizontal, vertical or join on the Base tables using standard SQL §Once a view is defined, the access to it is binary, either yes or no §Access control is provided only if access is via the view §Distributed administration: users are owners of the views they define and can delegate access further §Views are problematic for update 10/31/2020 Prof. Ehud Gudes Security Ch 6 28

Authorization in SQL based systems – The GRANT command GRANT privileges ON object TO users [WITH GRANT OPTION] v The following privileges can be specified: SELECT: Can read all columns (including those added later via ALTER TABLE command). v INSERT(col-name): Can insert tuples with non-null or non-default values in this column. v. INSERT means same right with respect to all columns. v DELETE: Can delete tuples. v REFERENCES (col-name): Can define foreign keys (in other tables) that refer to this column. v 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). v Only owner can execute CREATE, ALTER, and DROP. Prof. Ehud Gudes 10/31/2020 Security Ch 6 29 v

GRANT-Statement GRANT SELECT INSERT DELETE UPDATE ( column-name ) , , ALL PRIVILEGES ON base relation view relation 10/31/2020 TO user-name PUBLIC Prof. Ehud Gudes Security Ch 6 · WITH GRANT OPTION 30

REVOKE Statement 10/31/2020 Prof. Ehud Gudes Security Ch 6 31

Access Privileges in different DBMSs 10/31/2020 Prof. Ehud Gudes Security Ch 6 32

GRANT and REVOKE of Privileges § GRANT © INSERT, SELECT ON Employees TO Horatio can query Employees or insert tuples into it. § GRANT DELETE ON OPTION © Employees TO Yuppy WITH GRANT Yuppy can delete tuples, and also authorize others to do so. § GRANT UPDATE Salary ON Employees © TO Dustin can update (only) the salary field of Employees tuples. 10/31/2020 Prof. Ehud Gudes Security Ch 6 33

Revoke options § Reject (SQL) § Non-recursive revocation (Fernandez) § Time based recursive revocation (System R) § System based recursive revocation (SQL) 10/31/2020 Prof. Ehud Gudes Security Ch 6 34

Protection in System R. A: GRANT READ ON EMP TO B WITH GRANT OPTION A: GRANT READ ON EMP TO C WITH GRANT OPTION B: GRANT READ ON EMP TO X C: GRANT READ ON EMP TO X 10/31/2020 Prof. Ehud Gudes Security Ch 6 35

The privilege dependency graph B 4 2 D 5 A 3 6 C 10/31/2020 Prof. Ehud Gudes Security Ch 6 E 8 G 7 F 37

REVOKE IN SYSTEM R B B 4 2 D 5 A 3 6 C E 8 2 G D A 3 7 F 6 7 F C Revoking a 4 (with CASCADE option) will succeed. Authorization a 7 will not be revoked, because it is supported by a 6, but a 5 and a 8 Will be revoked. The privilege dependency graph will change. 10/31/2020 Prof. Ehud Gudes Security Ch 6 38

REVOKE in System R REVOKE: procedure (grantee, privilege, table, grantor); comment turn off the grantee’s authorization for privilege obtained from granter ; set privilege = 0 in the (grantee, table, grantor) tuple in SYSAUTH; comment find the minimum timestamp for the grantee’s remaining grantable privilege on table ; m current timestamp; for each granter u such that (grantee, privilege, table, u, grantable) is in SYSAUTH do if privilege 0 and privilege < m then m privilege; comment revoke grantee’s grants of privilege on table which were made before time m; For each user u such that (u, privilege, table, grantee) is in SYSAUTH do if privilege < m then REVOKE (u, privilege, table, grantee) ; return end REVOKE 10/31/2020 Prof. Ehud Gudes Security Ch 6 39

Protection in System R, cont. Suppose that at time t=35, B issues the command REVOKE ALL RIGHTS ON EMPLOYEE FROM X. Clearly the (X, EMPLOYEE, B) tuple must be deleted from SYSAUTH. In order to determine which of X’s grants of EMPLOYEE must be revoked, we form a list of X’s remaining incoming grants: USERID TABLE GRANTOR READ INSERT DELETE X EMPLOYEE A 15 15 0 X EMPLOYEE B 20 0 20 Y EMPLOYEE X 25 25 25 X EMPLOYEE C 30 0 30 As well as a list of X’s grants to others: TABLE READ INSERT DELETE EMPLOYEE {15, 30} {15} {30} The grant of the DELETE privilege by X to Y at time t=25 must be revoked because his earliest remaining DELETE privilege was received at time t=30. But X’s grants of READ and INSERT are allowed to remain because they are still “supported” by incoming grants which occurred earlier in time. 10/31/2020 Prof. Ehud Gudes Security Ch 6 40

GRANT/REVOKE on Views §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! 10/31/2020 Prof. Ehud Gudes Security Ch 6 41

Revoking Access on Views - System R § REVOKE : § DROP : procedure (grantee, table, grantor) ; delete the (grantee, table, grantor) tuple in SYSAUTH; for each u such that (u, table, grantee) is in SYSAUTH do REVOKE (u, table, grantee) ; for each view such that (table, view, grantee) is in SYSUSAGE do DROP (view) ; return ; end REVOKE ; procedure (view) ; delete the view definition from the system ; for each u 1 and u 2 such that (u 1, view, u 2) is in SYSAUTH do REVOKE (u 1, view, u 2) ; for each v and u such that (view, v, u) is in SYSUSAGE do DROP (v) ; return ; end DROP ; Prof. Ehud Gudes 10/31/2020 Security Ch 6 42

REVOKE in SQL §RESTRICT – accept only if there are no privileges resulted SOLELY from the revoked command, otherwise reject §CASCADE – remove privileges recursively as in System R, but do not consider time!, that is, if a privilege was granted to B by A, and A’s rights were revoked, but LATER A was given these rights independently, then don’t revoke B’s rights - this is equivalent to saying that there is a path from the “System” node 10/31/2020 Prof. Ehud Gudes Security Ch 6 43

Protection in SQL GRANT SELECT ON Sailors TO Art WITH GRANT OPTION GRANT SELECT ON Sailors TO Bob WITH GRANT OPTION GRANT SELECT ON Sailors TO Art WITH GRANT OPTION GRANT SELECT ON Sailors TO Cal WITH GRANT OPTION GRANT SELECT ON Sailors TO Bob WITH GRANT OPTION REVOKE SELECT ON Sailors FROM Art CASCADE 10/31/2020 Prof. Ehud Gudes Security Ch 6 (executed (executed by Joe) by Art) by Bob) by Joe) by Cal) by Joe) 44

Protection in SQL, cont. System (System, Joe, Select on Sailors, Yes) 10/31/2020 Joe Art Cal Bob Prof. Ehud Gudes Security Ch 6 45

Protection in SQL, cont. What happens if Joe revokes access from Cal 10/31/2020 Prof. Ehud Gudes Security Ch 6 46

The privilege dependency graph B 4 2 D 5 A 3 6 C 10/31/2020 Prof. Ehud Gudes Security Ch 6 E 8 G 7 F 47

SQL - REVOKE with RESTRICT option B 4 2 D 5 A 3 E 8 G 7 F C Revoke of a 4 with RESTRICT option will fail! If we have added a 6, then it would not have failed! 10/31/2020 Prof. Ehud Gudes Security Ch 6 48

What may be useful and is not supported in SQL 92? • Negative authorizations • Non cascading revoke B B 4 2 D 5 A 3 6 C 10/31/2020 E 8 5 2 G A 7 E 8 G D 3 F 6 C Prof. Ehud Gudes Security Ch 6 7 F 49

Protection in SQL, cont. Why needs SELECT right with Integrity constraints? May infer values from non permitted table! CREATE TABLE Sneaky (maxsalary INTEGER, CHECK ( maxsalary >= ( SELECT MAX (S. salary ) FROM Employees S ))) 10/31/2020 Prof. Ehud Gudes Security Ch 6 50

Protection in SQL, cont. Why SELECT right is not sufficient? Since owner rights may be restricted (cannot delete rows)! Needs explicit REFERENCE right CREATE TABLE Assignments (project CHAR (10) NOTNULL, SSN INTEGER, day DATE, PRIMARY KEY (SSN, day), FOREIGN KEY (SSN) REFERENCES Employees ON DELETE NO ACTION Prof. Ehud Gudes 10/31/2020 Security Ch 6 51

Differences Between System R and SQL-92 §New privileges – REFERENCE, USAGE §CASCADE or RESTRICT on Revoke §CASCADE different then System R – not time-stamp based §Precise definitions for Rights on views E. g. the impact of adding/removing a right to/from the base tables Ehud Gudes Groups and Roles §Authorization-Ids (for. Prof. programs), 10/31/2020 Security Ch 6 52

Role-Based Access Control §role-based access control work well for DBMS ©eases admin burden, improves security §categories of database users: ©application owner ©end user ©administrator §DB RBAC must manage roles and their users ©cf. RBAC on Microsoft’s SQL Server §Table 5. 2 in [SB] lists a set of administrative roles for MSSQL server

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 (and in many current systems), 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. 10/31/2020 Prof. Ehud Gudes Security Ch 6 54

Roles and Permissions Medical_Staff: collectively, responsible for all aspects of direct patient care. Nurse: Direct involvement with patient care on a daily basis. Physician: Handle the medical needs (diagnosis, treatment, etc. ) for patients. Pharmacists: Control the supply and distribution of all drugs throughout the hospital. Technician: Provide a variety of medical testing support for Patients. Therapist: Evaluate patients and develop treatment plans for therapy. Staff_RN: Administer direct care to patients and implement the physician treatment plan. Prof. Ehud Gudes 10/31/2020 Security Ch 6 55

Roles and Permissions, cont. Discharge_Plug: Link between patients and outside agencies for care after discharge. Education: Educate both the nursing staff and patients regarding new treatment and self care. Manager: Responsible for the day-to-day operation of a nursing unit Director: (For Physician or Pharmacist) Responsible for the day-to- Private: Attending: the physician within his/her office/private–practice setting. A physician that hes privileges to admit and treat patients at a hospital. 10/31/2020 day operation of their respective department/medical service. Prof. Ehud Gudes Security Ch 6 56

The User-Role Definition Hierarchy Users Medical Staff Nurse Support Staff Physician Pharmacist Technician Therapist Prepare room Support Volunteer Other Patient Spouce Security User Types, User Classes and Selected User Roles 10/31/2020 Prof. Ehud Gudes Security Ch 6 57

Role-Based Models §RBAC 0 – Users, Roles, Permissions, Sessions §RBAC 1 – RBAC 0 + Role-hierarchies §RBAC 2 – RBAC 0 + Constraints §RBAC 3 – RBAC 0 + Role-hierarchies + Constraints 10/31/2020 Prof. Ehud Gudes Security Ch 6 58

RBAC 1. ים -Role §היררכיית 10/31/2020 Prof. Ehud Gudes Security Ch 6

RBAC 1. §הגבלת ירושה 10/31/2020 Prof. Ehud Gudes Security Ch 6

RBAC 2 §מודל האילוצים . ים מנוגדים -Role© 10/31/2020 Prof. Ehud Gudes Security Ch 6

RBAC 3 : §המודל המשולב . Roles ©אילוצים והיררכיית 10/31/2020 Prof. Ehud Gudes Security Ch 6

Constraints in RBAC – Separation of duties §Conflicts between Permissions – conflicting permissions cannot be in the same Role or in two roles with a common ancestor §Conflicts between Roles – the same user cannot be in two conflicting roles §Conflicting users §Static constraints – max. number of roles per user, permissions per role, etc §Dynamic constraints – session dependent 10/31/2020 Prof. Ehud Gudes Security Ch 6 65

Roles in SQL 99 § New in SQL 99; benefits: Simplifies definition of complex sets of privileges § Roles are created CREATE ROLE Auditor. General § Roles may be assigned to users & roles GRANT Auditor TO Auditor. General WITH ADMIN OPTION GRANTED BY CURRENT ROLE GRANT Auditor TO Smith § Controllable whether to grant as user or role 10/31/2020 Prof. Ehud Gudes Security Ch 6 66

Roles in SQL 99, cont. §Roles (like users) may own objects §As to users, privileges may be granted to roles Grant INSERT ON TABLE Budget TO Auditor This privilege also among privileges of Auditor. General §A role R identifies a set of privileges: Those directly granted to R Those of the roles granted to R 10/31/2020 Prof. Ehud Gudes Security Ch 6 67

Roles in SQL 99, cont. § At any time there is at least a valid current user or a valid current role. § Current user can be set SET SESSION AUTHORIZATION ‘JDOE’ § Current role can be set or invalidates SET ROLE Auditor § Operations (e. g. INSERT) determine the kind of required privileges Often: union of user’s and role’s privileges § Session context maintains stack of user and role identifier pairs New pair is pushed when externally invoked procedure is executed Temporarily makes client module identifier the current user Enables invoker’s rights in a limited fashion 10/31/2020 Prof. Ehud Gudes Security Ch 6 68

Advantages of RBAC §Convenient representation and mapping of the organization structure §Convenient distribution of the administration of Roles §Easier definition and understanding of the security policy of the organization §Role-hierarchy and inheritance of permissions eases administrator job §Changes in user roles are simple and controllable §Sessions allow the same user different roles in different contexts §Grouping multiple users to roles allow easier control of their permissions Prof. Ehud Gudes 10/31/2020 Security Ch 6 69

Security in Object-Oriented Databases §Composition hierarchy [K[ §Generalization hierarchy [K[ §Explicit and implicit authorization [K[ §Strong and weak authorization [K[ §Inheritance – which authorizations are inherited? [G[ §Algorithm for evaluation [G[ §Negative authorization [G[ 10/31/2020 Prof. Ehud Gudes Security Ch 6 70

Database Granularity Hierarchy System [MCC] database [Inventory] database [CAD] class [automobile] class [4 -wheel-vehicle] class [vehicle] instance [1] Attribute-value [Weight] 10/31/2020 instance [2] Database… Instance… class [Motor-Vehicle] instance [100] Attribute-value… Database Granularity Hierarchy Prof. Ehud Gudes Security Ch 6 71

Class Hierarchy Vehicle IS-A 4 -Wheel-Vehicle Motor-Vehicle IS-A Automobile 10/31/2020 Prof. Ehud Gudes Security Ch 6 72

Implicit Weak Authorization with Weak Exceptions 10/31/2020 Prof. Ehud Gudes Security Ch 6 73

Implicit Weak Authorization with Strong Exceptions 10/31/2020 Prof. Ehud Gudes Security Ch 7 74

Security in OO Databases 10/31/2020 Prof. Ehud Gudes Security Ch 6 75

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. 10/31/2020 Prof. Ehud Gudes Security Ch 6 76

Why Mandatory Control? §Discretionary control has some flaws, e. g. , the Trojan horse problem: © © © Dick creates Horsie and gives INSERT privileges to Justin (who doesn’t know about this). Dick modifes the code of an application program used by Justin to additionally write some secret data to table Horsie. Now, Dick can see the secret info. §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. 10/31/2020 Prof. Ehud Gudes Security Ch 6 77

Bell-La. Padula 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) Prof. Ehud Gudes (*-Property 6 10/31/2020 Security Ch 7 78

Intuition §Idea is to ensure that information can never flow from a higher to a lower security level. §E. g. , If Dick has security class C, Justin has class S, and the secret table has class S: © © © Dick’s table, Horsie, has Dick’s clearance, C. Justin’s application has his clearance, S. So, the program cannot write into table Horsie. §The mandatory access control rules are applied in addition to any discretionary controls that are in effect. 10/31/2020 Prof. Ehud Gudes Security Ch 6 79

Multilevel Relations bid 101 102 bname Salsa Pinto color Red Brown class S C §Users with S and TS clearance will see both rows; a user with C will only see the 2 nd 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. Prof. treating class field as part of key. Ehud Gudes 10/31/2020 Security Ch 6 80

MLS-Prototype systems Sea View (SRI International, Oracle, Gemsos) MLS relational datamodel, supports polinstentiation on db-, relation- , tupeland attribute levels. LDV (LOCK Data Views) (Honeywell SCTC, MITRE) Extended relational data model, polyinstatiation on tuple level. Supports application dependent and non application dependent integrity rules. 10/31/2020 Prof. Ehud Gudes Security Ch 6 81

Jajodia - Formal Integrity Rules §A Database D; §A relation R within the database D; §The primary key for a tuple r within the relation R; §The attribute i, identifying the element ri within the tuple r. To get through to the element ri, the following must hold: lab(D) lab(R) lab(ri) Otherwise, you could be barred access to an element you are entitled to see. In convention, a user who has access to an element of r must have access to its primary key. Therefore: lab(rk) lab(ri) 10/31/2020 Prof. Ehud Gudes Security Ch 6 82

Jajodia - Formal Integrity Rules, cont. Rule Multi-level entity integrity: no component of a primary key of a base relation may be null. All components of a primary key of a base relation have the same access class. In a base relation, the access class of all other data values in a tuple dominates the access class of the primary key of that tuple. 10/31/2020 Prof. Ehud Gudes Security Ch 6 83

Formal Integrity Rules, cont. Rule Multi-level reference integrity: a tuple referenced by a foreign key has to exist. The access class of the foreign key dominates the access class of the corresponding primary key. Rule The access class of a view dominates the access classes of all relations used in the definition of the view. Rule The access class of a tuple dominates the access classes of all attributes in the tuple. 10/31/2020 Prof. Ehud Gudes Security Ch 6 84

Multi-level Relation Flight [Fl_Class] Dest. [De_Class] Seats [Se_Class] [Tuple_Class] CA 909 [C] H. K. [C] 7 [C] AX 301 [U] K. L. [U] 2 [U] GR 555 [U] L. A. [C] 11 [C] Figure 15. 1 10/31/2020 The Relation Bookings with Primary Key Flight Prof. Ehud Gudes Security Ch 6 85

Multi-level Relation – view of C user Flight [Fl_Class] Dest. [De_Class] Seats [Se_Class] CA 909 [C] H. K. [C] 7 [C] AX 301 [U] K. L. [U] 2 [U] GR 555 [U] L. A. [C] 11 [C] Figure 15. 1 10/31/2020 The Relation Bookings with Primary Key Flight Prof. Ehud Gudes Security Ch 6 86

Multi-level relation – view of U user Flight Dest Seats AX 301 K. L. 2 GR 555 -- -- Figure 15. 2 10/31/2020 List of Non-Confidential Data from fig. 15. 1 Accessible to Unclassified User Prof. Ehud Gudes Security Ch 6 87

Polyinstatiation is necessary 10/31/2020 Prof. Ehud Gudes Security Ch 6 88

Why Poly-instantiation? §A low user tries to add information on flight CA 909. ©If refused – inference, if accepted - violation of primary key constraint §A low user tries to update information on flight GR 555. ©If refused – inference, if accepted - violation of functional dependency §A high user attempt to change destination of flight AX 301 © If refused – restricts access, if accepted - violation of functional dependency §Solution: accept and include tuple class as part of the primary key!. Prof. Ehud Gudes 10/31/2020 Security Ch 6 89

Polyinstatiated table after User U update Flight [Fl_Class] Dest. [De_Class] Seats [Se_Class] [Tuple_Class] CA 909 [C] H. K. [C] 7 [C] AX 301 [U] K. L. [U] 2 [U] GR 555 [U] L. A. [C] 11 [C] GR 555 [U] N. Y. [U] 0 [U] Figure 15. 4 10/31/2020 Updated Version of Table Data Given in Figure 15. 1. Prof. Ehud Gudes Security Ch 6 90

Polyinstatiation Cont. Flight Dest Seats CA 909 H. K. 7 AX 301 K. L. 2 GR 555 L. A. 11 GR 555 N. Y. 0 Figure 15. 5 10/31/2020 Data Accessible to Confidential User Prof. Ehud Gudes Security Ch 6 91

Subsumption If user view has a null in U attribute and User C update it with non-null value, the C tuple subsumes the U tuple and only one tuple is visible to C 10/31/2020 Prof. Ehud Gudes Security Ch 6 92

Polyinstantiation integrity If two tuples in a base relation have the same primary key and the respective entries for some attribute have the same access class, then also the data values for this attribute are the same. If two tuples of a base relation have the same primary key and if there are some attributes where the respective entries have different access classes, then the values for those attributes may differ and any combination of these values (and access classes) gives again a tuple in the relation. 10/31/2020 Prof. Ehud Gudes Security Ch 6 93

Polyinstantiation Integrity Assume query: Dest = NY and Seats = 11 by C user – need to add two rows over table from p. 105 Flight [Fl_Class] Dest. [De_Class] Seats [Se_Class] [Tuple_Class] CA 909 [C] H. K. [C] 7 [C] AX 301 [U] K. L. [U] 2 [U] GR 555 [U] L. A. [C] 11 [C] GR 555 [U] L. A. [C] 0 [U] [C] GR 555 [U] N. Y. [U] 11 [C] GR 555 [U] N. Y. [U] 0 [U] 10/31/2020 Prof. Ehud Gudes Security Ch 6 94

Implementation Options 1. Rely on ML-OS There is a separate single-level DBMS process running at each access class. Multi-level relations are stored as a collection of singlelevel operating systems. The DBMS has to use a partial ordering of access classes supported by the operating system. 2. DBMS is a Trusted Subject Proceed with the update and polyinstantiate the data, or Deny the update and record this event in an audit log. 10/31/2020 Prof. Ehud Gudes Security Ch 6 95

MLS Concurrecy Control Assume user S locked a record for write and now user U tries to read it. The existence of this lock is already secret information! If lock is at level S, user U (or U scheduler) should be unaware of it and will try to read the record. If lock is at level U then user S violated BLP! SOLUTION? 10/31/2020 Read papers! Prof. Ehud Gudes Security Ch 6 96

Inference

Inference Example

Statistical DB Security §Statistical DB: Contains information about individuals, but allows only aggregate queries (e. g. , average age, rather than Joe’s age). §New problem: It may be possible to infer some secret information! © E. g. , If I know Joe is the oldest sailor, I can ask “How many sailors are older than X? ” for different values of X until I get the answer 1; this allows me to infer Joe’s age. §Idea: Insist that each query must involve at least N rows, for some N. Will this work? (No!) 10/31/2020 Prof. Ehud Gudes Security Ch 6 99

Why Minimum N is Not Enough §By asking “How many sailors older than X? ” until the system rejects the query, can identify a set of N sailors, including Joe, that are older than X; let X=55 at this point. §Next, ask “What is the sum of ages of sailors older than X? ” Let result be S 1. §Next, ask “What is sum of ages of sailors other than Joe who are older than X, plus my age? ” Let result be S 2. § 10/31/2020 S 1 -S 2 is Joe’s age Prof. ! Ehud Gudes Security Ch 6 100

Types of Sensitive Data §Exact data – e. g. salary of John Doe §Bounds §Negative results – e. g. 0 is not the total number of felonies §Existence – e. g. of AIDS virus §Probable values 10/31/2020 Prof. Ehud Gudes Security Ch 6 101

Example Database NAME SEX (Not stored) LEV (job level) LOC (Work location) SAL (Salary) 0. 1 0. 2 0. 3 DIAZ M 60 SF 36 1 2 2 SMITH F 58 SF 24 3 2 1 JONES M 56 LA 26 4 2 3 KATZ M 57 LA 30 3 3 2 CLARK F 58 LA 28 5 1 4 WOND F 60 LA 34 1 1 1 WEBB M 58 SF 32 5 5 5 10/31/2020 Prof. Ehud Gudes Security Ch 6 102

The individual Tracker Assume C characterize the individual uniquely – then Q(C) or Q(C*S) is unanswerable, S is the searched field. Assume C=AB where both Q(A) and Q(AB^) are answerable. Then Q(C)=Q(A)-Q(AB^) or Q(C*S)=Q(A*S)-Q(AB^*S) 10/31/2020 Prof. Ehud Gudes Security Ch 6 105

The Individual Tracker - Example Unique Identifier Sex Dept Position Salary ($K) Political Contribution ($) Adams M CS Prof. 20 50 Baker M Math Prof. 15 100 Cook F Math Prof. 25 200 Dodd F CS Prof. 15 50 Engel M Stat Prof. 18 0 Flynn F Stat Prof. 22 150 Grady M CS Adm. 10 20 Hayes M Math Prof. 18 500 Irons F CS Stu. 3 10 Jones M Stat Adm. 20 15 Knapp F Math Prof. 25 100 Lord M CS Stu. 3 0 C=F*CS*Prof, A=F, B=CS*Prof 10/31/2020 Prof. Ehud Gudes Security Ch 6 106

The General Tracker Assume threshold is K. Find T such that 2 k <= count(T) <= n-2 k Then, k <= count(T+C) <= n-k and k <= count(T^+C) <= n-k So, both are answerable! And Q(C) = Q(T+C) + Q(T^+C) –n ! 10/31/2020 Prof. Ehud Gudes Security Ch 6 107

Some Defenses §Replacing precise values with range values §Use samples from the original database §Perturb data randomly §Perturb results randomly §Partition the database §Audit trail and query analysis 10/31/2020 Prof. Ehud Gudes Security Ch 6 108

Protecting Against Inference

Other Query Restrictions §query set overlap control ©limit overlap between new & previous queries ©has problems and overheads §partitioning ©cluster records into exclusive groups ©only allow queries on entire groups §query denial and information leakage ©denials can leak information ©to counter must track queries from user

Perturbation §add noise to statistics generated from data ©will result in differences in statistics §data perturbation techniques ©data swapping ©generate statistics from probability distribution §output perturbation techniques ©random-sample query ©statistic adjustment §must minimize loss of accuracy in results

The Inference Problem, cont. To show that a refusal to answer may reveal a secret, we now consider a system that only refuses if the answer would reveal a secret. Suppose the following integrity constraints apply to a database containing the fact that Mediocrates is an Athenian: Every man is an Athenian, a Boeotian, a Corinthian, or a Dorian; All Athenians and Corinthians are peaceable; All Boeotians and Dorians are violent; 10/31/2020 Prof. Ehud Gudes Security Ch 6 112

The Inference Problem, cont. Mediocrates does not wish it to be known that he is peaceable. Rhinologus, a public nuisance, tries to find out about Mediocrates: Rhinologus: System: 10/31/2020 Is Mediocrates an Athenian? I will not tell you. Is he a Boeotian? No. Is he a Corinthian? No. Is he a Dorian, then? I will not tell you. Prof. Ehud Gudes Security Ch 6 113

The Inference Problem – Answering Queries Without Revealing Secrets “is Mediocrates an Athenian? ” yes mum no k “is Mediocrates a Boeotian? ” yes mum no k “is Mediocrates a Corinthian? ” mum yes no k “is Mediocrates a Dorian? ” yes K ‘M is peaceable’ 10/31/2020 k Prof. Ehud Gudes Security Ch 6 mum no k 114

Database Encryption 10/31/2020 Prof. Ehud Gudes Security Ch 6 115

DBMS Data - The Attackers § Intruder © Tries to attack the confidentiality or integrity of the database. © Tries to take over the identity of a regular user (or DBA). © Most attacks are targeted at web applications. § The database service provider Administrator © Tries to obtain information beyond his own access rights. © Tries to change the content of the database for his own benefit (e. g. , change account balance). © Most attacks are targeted at outsourced databases. § The data owner Client © Has sufficient privileges to tamper with the access right definition – possibly unlimited privileges. 116

Database Encryption §databases typical a valuable info resource ©protected by multiple layers of security: firewalls, authentication, O/S access control systems, DB access control systems, and database encryption §can encrypt ©entire database - very inflexible and inefficient ©individual fields - simple but inflexible ©records (rows) or columns (attributes) - best also need attribute indexes to help data retrieval §varying trade-offs

Database Encryption

Database Encryption at the Table Level Client Secured Communication Decryption DBMS § Encrypting the entire table after every change. § Substitution and statistical attacks on the table values are eliminated. Encryption #$*#@#$%^ #$@!@#$% %$@!@#&$ &^*%$%^&$ Encrypted Database Intruder Insider Admin Ø Weakness: In order to execute a query the whole table needs to be decrypted. 10/31/2020 Prof. Ehud Gudes Security Ch 6 119

Database Encryption at the Cell Level 16 Client Secured Communication DBMS decrypted while performing a query. Val 0 %$ 1 !@ 2 %$ %$ Decryption § Encrypting each cell separately. § Only the data of interest needs to be Row Encryption Encrypted Database Intruder Insider Admin Ø Weakness: Substitution and statistical attacks are possible. 10/31/2020 Prof. Ehud Gudes Security Ch 6 120

Database Encryption at the Row Level § The database encryption scheme described by Davida(81) is based on the § § Chinese-Reminder theorem. Each row is encrypted using different sub-keys for different cells. Enables: encryption at the level of rows and decryption at the level of cells. Ø Weakness: Requires re-encrypting the entire row when a cell value is modified. Ø Weakness: The basic element in the database is a row and not a cell, thus the structure of the database needs to be changed. 10/31/2020 Prof. Ehud Gudes Security Ch 6 121

SPDE - A New Database Encryption Scheme 16 Client Secured Communication Val 0 &$ 1 @# 2 *^ &$ Decryption DBMS § Each cell is encrypted with its unique cell coordinates. § Only the data of interest needs to be decrypted while performing a query. § Substitution and statistical attacks Row Encryption Encrypted Database Intruder Insider Admin are eliminated. 10/31/2020 Prof. Ehud Gudes Security Ch 6 122

Cell Coordinates Characteristics §Stable cell coordinates: © Insert, update and delete operations do not change the coordinates of existing cells. © For example, this is the case in the Oracle database. §If a database reorganization process changes cell coordinates, all affected cells are to be re-encrypted with their new coordinates. 10/31/2020 Prof. Ehud Gudes Security Ch 7 123

Indexing Encrypted Data 10/31/2020 Prof. Ehud Gudes Security Ch 7 124

Building an Index on the Encrypted Data Decrypted Table Row-id Val AAA 16 AAB 26 AAC 16 Encrypted Table Row-id Val AAA &$ AAB @# AAC &$ Index @# &$ § Indexing the encrypted data using a hash index. § Equality queries are possible. Ø Weakness: Statistical attacks are possible by using the index. Ø Weakness: No range queries. 10/31/2020 Prof. Ehud Gudes Security Ch 6 125

Encrypting Each Index Value Separately Decrypted Table Row-id Val AAA 16 AAB 26 AAC 16 Encrypted Table Row-id Val AAA &$ AAB @# AAC &$ Index &$ @# Maximum &$ § Building the index on the plaintext values and encrypting each index value separately. § Range queries are possible. Ø Weakness: Statistical attacks are possible by using the index. Ø Weakness: The order of the ciphertext values is exposed. 10/31/2020 Prof. Ehud Gudes Security Ch 6 126

Encrypting Each Index Node Separately Decrypted Table Row-id Val AAA 16 AAB 26 AAC 16 Encrypted Table Row-id Val AAA #! AAB ^& AAC *# Index @# *! &$ § The indexing scheme provided by Damiani(03) suggests encrypting § § each node of the B+-Tree as a whole. References between the B+-Tree nodes are encrypted together with the index values. The index does not reveal the statistics or order of the database values Ø Weakness: The index structure is concealed. 10/31/2020 Prof. Ehud Gudes Security Ch 6 127

A New Database Indexing Scheme Decrypted Table Row-id Val AAA 16 AAB 26 AAC 16 Index Encrypted Table Row-id Val AAA #! AAB ^& AAC *# @# *! &$ § Each index value is the result of encrypting a plaintext value in the database with its row-id. § This ensures that the index does not reveal the statistics or order of the database values. § The new database indexing scheme preserves the index structure. 10/31/2020 Prof. Ehud Gudes Security Ch 6 128

SPDE - Conclusions § A new structure preserving scheme for database encryption has been presented. § In the new scheme, patterns matching and substitution attacks cannot succeed, thus guaranteeing information confidentiality and data integrity. § The new schemes do not impose any changes on the database structure. © A DBA is able to manage the encrypted database as any other non-encrypted database. © Implementing the new scheme in existing applications does not entail modifying the queries. 10/31/2020 Prof. Ehud Gudes Security Ch 6 129

SQL Injection Traditional DB • • Two-tier architecture Users are fixed and known Users’ number is limited All transactions are passed via the same connection and users connect directly to the DB

Traditional DB Access Control • DB can identify and authenticate its users • DB can authorize users by traditional user/role-based access control • It is quite easy to follow up single user transactions to seek signs of intrusion • Views can be used to determine for a user the only part of the database that interests her

Web Databases • Three-tier architecture • Users are casual and unknown • Users’ number is not limited • Users do not connect directly to the DB

Connection Pooling §Different web users can run their SQL statements on the same DB connection and one user can run her SQL statements on different connections ©This technique contributes to application efficiency since the time to open and close the connection is saved per each request ©It has serious implications on the database’s access control mechanism

Web DB Access Control §Web DB does not identify the real application user who accesses it §The only user accessing the database is the user of the web application server – most often with full access privileges (administrator or “super-user”) §No user-based access control can be applied §Only limited RBAC can be applied §The principle of minimal privilege is violated §No more fine-grained access control to the web DB exists

Web DB is vulnerable §There is no way to limit the web user privileges at the database level of the web databases. §There are still many secured web applications, but their security can be achieved only by application means (writing lines of code that implement security policies) and not by database access control means (GRANT/REVOKE and VIEWS) §This situation is very problematic: © Implementing access control by writing code is a time consuming task © Enterprise may have one DB but many applications, then access control is distributed and in many cases not consistent © Programmers must be security specialists §As a result: "70% of websites are at immediate risk of being hacked!" (http: //www. acunetix. com)

Attack example: SQL Injection • In many Web application the SQL sentence is structured as a string and the user’s parameter is concatenated to this string: str. SQL= "SELECT Salary FROM Salary_Table WHERE Employee_No = 123 AND Salary_Date = '" + date. Param + • "'" But the intruder can type `01. 2007' or '1' = '1` and retrieve the entire table: SELECT Salary FROM Salary_Table WHERE Employee_No = 123 AND Salary_Date = '01/2007' OR '1' = '1'

SQL Injection §SQL uses single and double quotes to switch between data and code. §Semi-colons separate SQL statements §Example query: ©"UPDATE users SET prefcolor='red' WHERE uid='joe'; " §This command could be sent from a web front-end to a database engine. §The database engine then interprets the command

Dynamic SQL Generation §Web applications dynamically generate the necessary database commands by manipulating strings § Example query generation: ©$q = "UPDATE users SET prefcolor='$INPUT[color]' WHERE uid='$auth_user'"; §Where the value of "$INPUT[color]" would be originating from the client web browser, through the web server. §And where the value for "$auth_user" would have been stored on the server and verified through some authentication scheme

Client Web Browser §Forms in client browsers return values to the web server through either the POST or GET methods ©"GET" results in a url with a "? " before the values of the form variables are specified: http: //www. example. com/script? color=red The value of "$INPUT[color]" is set to "red" in the script §"GET" urls are convenient to hack, but there isn't any significant difference in the security of either "GET" or "POST" methods because the data comes from the client web browser regardless and is under the control of the remote attacker

The SQL Table §Tables are used to store information in fields (columns) in relation to a key (e. g. , "uid") §What other fields could be of interest? §CREATE TABLE users ( prefcolor varchar(20), uid VARCHAR(20) NOT NULL, privilege ENUM('normal', 'administrator'), PRIMARY KEY (uid) );

A Malicious SQL Query §What if we could make the web server generate a query like: ©"UPDATE users SET prefcolor='red', privilege='administrator' WHERE uid='joe'; " §Can we engineer the value of "color" given to the web server so it generates this query? ©Note how code and data are mixed in the same channel Better database interfaces provide separate channels

Malicious HTTP Request §http: //www. example. com/script? color=red', p rivilege='administrator §The "color" input is then substituted to generate SQL: ©$q = "UPDATE users SET prefcolor='$INPUT[color]' WHERE uid='$auth_user'"; §It gives the query we wanted!

Results §Joe now has administrator privileges.

Adding Another SQL Query §Let's say Joe wants to run a completely different query: ©"DELETE * FROM users" This will delete all entries in the table! §How can the value of "color" be engineered?

Malicious HTTP Request §http: //www. example. com/script? color=red'%3 Bdelete+from+users%3 B ©%3 B is the url encoding for "; " §What happens when the "color" input is used to generate SQL? ©$q = "UPDATE users SET prefcolor='$INPUT[color]' WHERE uid='$auth_user'";

Result �UPDATE users �SET prefcolor='red'; �delete from users; �WHERE uid='$auth_user'"; §The last line generates an error, but it's already too late; all entries have been deleted. §The middle query could have been anything

FAQs §Couldn't the database have a separate account for "Joe" with only the privileges he needs (e. g. , no delete privilege)? ©In theory yes, but in practice the management of such accounts and privileges, and connecting to the database with the correct IDs, adds significant complexity Most often a database account is created for the entire web application, with appropriate limitations (e. g. , without privileges to create and drop tables) A good compromise is to create database accounts for each class of user or class of operation, so: • if Joe is a regular user he wouldn't have delete privileges for the user table • Changing user preferences, as an operation type, doesn't require delete privileges

FAQs §Doesn't SSL protect against this sort of attack? ©No §But what if you authenticate users with a username/password over SSL? Then, if the user does SQL injection, the server admins will know who perpetrated the crime, right? ©Not necessarily; logging. only if you have sufficient audit

What to do? §Careful writing of Web applications including strong input validation §More reliance on Database security including the use of Views and Roles §Our method of Prevention and Detection using Parameterized Views

Summary § Three main security objectives: secrecy, integrity, availability. § DB admin is responsible for overall security. © Designs security policy, maintains an audit trail, or history of users’ accesses to DB. § Two main approaches to DBMS security: discretionary and mandatory access control. Discretionary control based on notion of privileges. © Mandatory control based on notion of security classes. © § Statistical DBs try to protect individual data by supporting only aggregate queries, but often, individual information can be inferred. 10/31/2020 Prof. Ehud Gudes Security Ch 6 150