Computer System Security and Management D0004E

1. Computer System Security andManagementD0004E Databasesecurity Jörgen S Öfjäll

2. DatabaseSecurity • Databases store data and provide information to theirusers. • Database Security: protection of sensitive data and mechanisms that allow users to retrieve information in a controlled manner. • Difference to Operating System Security: Database Security control access to information more than access to data. • Focus on principals requesting access to database.

3. Database Attack Goals • Exact data: the values stored in the database • Bounds: lower or upper bounds on a numerical value like a salary can already be useful information • Negative results: e.g. if a database contains numbers of criminal convictions, then the information that a particular person does not have zero convictions is sensitive. • Existence: the existence of data may already be sensitive information; • Probable value: being able to guess some information from the results of other queries.

4. Design Requirements • Precision: protect sensitive information while revealing as much non-sensitive information as possible. • Internal consistency: the entries in the database obey some prescribed rules. • E.g., stock levels cannot fall below zero. • or personal number must have correct control sum • External consistency: the entries in the database are correct. • E.g., stock levels given in the database match stock levels in the warehouse; however, the database management system (DBMS) alone cannot keep the database in a consistent state. • This property is also called accuracy.

5. Popular Database types • Relation database • Multidimensional databases (OLAP = Online analytical processing) • MOLAP (Multidimensional) • ROLAP (Relational) • HOLAP (Hybrid) • NoSQL • Column store • Document • Key-Value Store • Graph • In Memory database • Relational • NoSQL

6. Relational Databases • A relational database is a database that is perceived by its users as a collection of tables (and tables only). • A relation Ris a subset of D1x..xDnwhere D1, … , Dnare the domains on nattributes. • The elements in the relation are n-tuples (v1, … , vn) with vi # Di; the value of the i-th attribute has to be an element from Di. • Elements in a tuple are often called fields. • A special null value indicates that a field does not contain any value.

7. Relations

8. Relations • Relations are sets – no duplicates. • Every tuple must be uniquely identifiable. • Primary Keys!

9. Types of Relations • Base relations (real relations): named, autonomous relations; exist in their own right, are not derived from other relations, and have ‘their own’ stored data. • Views: named, derived relations, defined in terms of other named relations; no stored data of their own. • Snapshots: named, derived relations, defined in terms of other named relations; have stored data of their own. • Query results: may or may not have a name; no persistent existence in the database per se.

10. Relations • Foreign keys

11. Integrity Rules • Entity Integrity Rule: no component of the primary key of a base relation is allowed to accept nulls. • Referential Integrity Rule: the database must not contain unmatched foreign key values. • Application specific integrity rules: • Field checks: to prevent errors on data entry. • Scope checks. • Consistency checks.

12. SQL • Structured Query Language (SQL): standard language for describing how information in a relational database can be retrieved and updated. • SQL operations: • SELECT: retrieves data from a relation. • UPDATE: update fields in a relation. • DELETE: deletes tuples from a relation. • INSERT: adds tuples to a relation.

13. SQL Standard

14. SQL Query SELECT Which attributes? FROM Which table(s)? WHERE What is condition that the resulting tuples satisfy?

15. SQL Query Example

16. SQL Security Model • Discretionary access control using privileges and views, based on: • users: authenticated during logon; • actions: include SELECT, UPDATE, DELETE, and INSERT; • objects: tables, views, columns (attributes) of tables and views; • Users invoke actions on objects; the DBMS decides whether to permit the requested action. • When an object is created, it is assigned an owner; initially only the owner has access to the object; other users have to be issued with a privilege: (granter, grantee, object, action).

17. Granting & Revoking Privileges • Privileges managed with GRANT and REVOKE. GRANT SELECT, UPDATE (Day,Flight) ON TABLE Diary TO Art, Zoe • Selective revocation of privileges: REVOKE UPDATE ON TABLE Diary FROM Art • Right to delegate privileges given through GRANT option: GRANT SELECT ON TABLE Diary TO Art WITH GRANT OPTION

18. Roles • All Privileges that can be grated to users to can also be grated to roles. • A user can be given access to a role and then inherit all of its rights. • It is a way to group user to different functions. • A tool to handle grants to a lot of database objects.

19. Access Control through Views • Views: derived relations, created by CREATE VIEW view_name [ ( column [, column ] ... ) ] AS subquery [ WITH CHECK OPTION ] • Many security policies better expressed by privileges on views than by privileges on base relations. • Access conditions described through subquery in the view definition: CREATE VIEW business_trips AS SELECT * FROM Diary WHERE Status = `business' WITH CHECK OPTION;

20. Views Advantages • Views are flexible and allow access control policies to be defined at a level of description that is close to the application requirements. • Views can enforce context-dependent and datadependent security policies. • Views can implement controlled invocation.

21. More Examples displays students whose grade average is less than that of the person using the view CREATE VIEW Top_of_the_Class AS SELECT * FROM Students WHERE Grade < (SELECT Grade FROM Students WHERE Name = current_user()); display journeys booked by the customer using the view. • CREATE VIEW My_Journeys AS • SELECT * FROM Diary • WHERE Customer = current_user());

22. CHECK Option • INSERT and UPDATE can interfere with view-based access control. • Views may not be updatable because they do not contain the information that is needed to maintain the integrity of the corresponding base relation. • E.g., a view that does not contain the primary key of an underlying base relation cannot be used for updates. • Blind writes: updates that overwrite an existing entry. • For views defined WITH CHECK OPTION, UPDATE and INSERT can only write entries to the database that meet the definition of the view. • Blind writes possible if CHECK option is omitted.

23. Views Disadvantages • Access checking may become complicated and slow. Lead to bad query plans • Views need to be checked for ‘correctness’; do they really capture the security policy? • Completeness and consistency are not achieved automatically, views may overlap or may fail to capture the entire database. • Are views more suitable for user- or data-focused policies?

24. Statistical Database Security • Statistical database: information retrieved by means of statistical (aggregate) queries on attributes (columns) of a table. • Aggregate functions in SQL: • COUNT: the number of values in a column, • SUM: the sum of the values in a column, • AVG: the average of the values in a column, • MAX: the largest value in a column, • MIN: the smallest value in a column. • Query predicate of a statistical query: specifies the tuples used for computing the aggregate, • Query set: tuples matching the query predicate.

25. Security Challenge • The database contains data that are individually sensitive; direct access -> not permitted. • Statistical queries to the database are permitted, but these queries will read individual data items. • It thus becomes possible to infer information; • In a statistical database, there must be some information flow from the data to their aggregate

26. Attacks • Aggregation: sensitivity level of an aggregate computed over a group of values may differ from the sensitivity levels of the individual elements; e.g., an aggregate may be sensitive information derived from a collection of less sensitive business data. • Inference problem: derivation of sensitive information from non-sensitive data: • Direct Attack: aggregate computed over a small sample so that information about individual data items is leaked. • Indirect Attack: combine information relating to several aggregates; • Tracker Attack: a particularly effective type of indirect attack; • Linear System Vulnerability: use algebraic relations between query sets to construct equations which yield the desired information.

27. Example Relation

28. Direct Attack Q1 : SELECT COUNT(*) FROM Students WHERE Sex = 'F' AND Program = 'CS‘ Q2 : SELECT AVG(Grade Ave.) FROM Students WHERE Sex = 'F' AND Program = 'CS' Returns count 1 Returns 70: average for a single student

29. Tracker Attack Q3 : SELECT COUNT(*) FROM Students WHERE Programme = 'CS‘ Q4 : SELECT COUNT(*) FROM Students WHERE Programme = 'CS' AND Sex = 'M‘ Q5 : SELECT AVG(Grade Ave.) FROM Students WHERE Program = 'CS‘ Q6 : SELECT AVG(Grade Ave.) FROM Students WHERE Program = 'CS' AND Sex = 'M' Returns count 4 Returns count 3 Returns average 61 Carol’s grade average: 4 x 61 – 3 x 58 = 70 Returns average 58

30. Countermeasures • Suppress obviously sensitive information. • Disguise the data: • Randomly swap entries in the database so that an individual query will give a wrong result although the statistical queries still would be correct; • Add small random perturbations to query result so that the value returned is close to the real value but not quite correct. • Drawback: reduced precision and usability. • Better design of the database schema. • Check logs for suspicious patterns of queries. • Audit • (OLAP database engine)

31. SQL Injection • SQL injection attack involves the alteration of SQL statements that are used within a application through the use of attacker-supplied data. • Insufficient input validation and improper construction of SQL statements in applications can expose them to SQL injection attacks. XKCD– Exploits of a Mom (http://xkcd.com/327/ )

32. Ramifications of Successful SQL Injection Attacks • Authentication Bypass: This attack allows an attacker to log on to an application, potentially with administrative privileges, without supplying a valid username and password. • Information Disclosure: This attack allows an attacker to obtain, either directly or indirectly, sensitive information in a database. • Compromised Data Integrity: This attack involves the alteration of the contents of a database. • Compromised Availability of Data: This attack allows an attacker to delete information with the intent to cause harm or delete log or audit information in a database. • Remote Command Execution: Performing command execution through a database can allow an attacker to compromise the host operating system.

33. SQL Injection Example <form action="/cgi-bin/login" method=post> Username: <input type=text name=username> Password: <input type=password name=password> <input type=submit value=Login>

34. SQL Injection Example HTTP POST request is sent: username=submittedUser&password=submittedPassword Submitted information is used as part of the following SQL statement: SELECT * FROM Users WHERE (username = 'submittedUser‘ AND password = 'submittedPassword');

35. SQL Injection Example What happens if following HTTP POST request is sent? username=admin%27%29+--+&password=+

36. SQL Injection Example SELECT * FROM Users WHERE (username = 'admin') -- and password = ' '); The string of two dash characters (--) that appears in the crafted input is very important; it indicates to the database server that the remaining characters in the SQL statement are a comment and should be ignored. End result:Attacker is able to login to application with admin right and bypassing password check.

37. Defending Against SQL Injection Attacks • Comprehensive data sanitization – filter ALL user input • Blacklisting • Whitelisting • Fortifying SQL Statements • Use Stored procedures • Parameterized queries • Hide SQL generated errors for the end user / Attacker • Use a web application firewall. • Example: ModSecurityopensourcemodulefor Apache, Microsoft IIS, and nginx web servers

38. SQL Injection Parameterized query example String sql = "select * from Users where (username = ? and password = ?)"; PreparedStatementpreparedStmt = connection.prepareStatement(sql); preparedStmt.setString(1, submittedUsername); preparedStmt.setString(2, submittedPassword); results = preparedStmt.executeQuery();

39. SQL Injection Hide SQL Errors for users com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'sqlInjectionTest.test' doesn't exist at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723) at com.mysql.jdbc.Connection.execSQL(Connection.java:3277) at com.mysql.jdbc.Connection.execSQL(Connection.java:3206) at com.mysql.jdbc.Statement.executeQuery(Statement.java:1232) at sqlInjectionBefore.main(before.java:28)

40. SQL Injectionmore reading • Bobby Tables: A guide to preventing SQL injectionhttp://bobby-tables.com/ • OWASP: SQL Injectionhttp://www.owasp.org/index.php/SQL_injection • How To: Protect From SQL Injection in ASP.NEThttp://msdn.microsoft.com/en-us/library/ms998271.aspx • Using Prepared Statements in Javahttp://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html • Cisco TAC Security Podcast Episode #16 - Mitigating a SQL attack with ASA, IPS and IOS Firewallhttps://supportforums.cisco.com/docs/DOC-14890

41. Securing the house DatabaseSystem SecurityChecklist • Install only what is required • Lock and Expire Default User Accounts • Change Default UserPasswords • Change default passwords of administrative users • Change default passwords of all users • Enforcepasswordmanagement • Enable Data Dictionary Protection • Restrict access (as much as possible) to regular users to system views

42. Securing the house Database System SecurityChecklist • Practicing the principle of least privilege • Grant necessary privileges only • Revoke unnecessary privileges from the public user group • Grant a role to users only if they need all privileges of the role • Restrict permissions on run-time facilitiesDo not assign all permissions to any database server run-time facility such as the Java Virtual Machine or .NET runtime • Enforce access controls effectively and authenticate clients stringently • Authenticate client properly • Don’t trust the clientsExample: Do not allow REMOTE_OS_AUTHENT =TRUE

43. Securing the house Database System SecurityChecklist • Restrict Operating System Access • Limit the number of operating system users. • Limit the privileges of the operating system accounts to the least privileges needed for the user’s tasks.(administrative, root-privileged or DBA) • Restrict Network Access • Use a firewall • Never poke a hole through a firewall • Monitor who accesses your systems • Check network IP addresses (use blacklisting or whitelisting) • Encrypt network traffic

44. Securing the house Database System SecurityChecklist • Harden the operating system • Disabling all unnecessary operating system services • Close all default UDP and TCP ports for the disabled services • Apply all securitypatches • And install new securitypatches as soon as possible

45. Default Oracle Password Statistics