Database and Application Security

1. Database and Application Security Kimmy Szeto GSLIS, CUNY Queens College Database Security Lecture

2. Database Security Database Security - protection from malicious attempts to steal or modify data Database Security Lecture

3. Importance of data Patron accounts, personnel files, equipment inventories Salary data, tax data, social security numbers, insurance information Bank account numbers, credit card numbers Student grades, student registrations Ownership records, deeds, mortgages, licenses Data!!!! • Sony Play Station Network – April 19, 2011 • 77 million users affected worldwide • External attack • Network shut down for 3 days • Stolen: name, account name, password, birthday, addresses, possibly credit card details • Loss of business, damage to brand and reputation • Cost of lawsuits ($1.5 billion?) http://www.privacyrights.org/data-breach Database Security Lecture

4. Identity Theft Federal Trade Commission: Identity theft occurs when someone uses your personally identifying information, like your name, Social Security number, or credit card number, without your permission, to commit fraud or other crimes. More lucrative than blue-collar crime More difficult to catch Victims may be unaware for a long time Ruins credit history, faces debt collection, accounts denied, arrests Onus often placed on victims Database Security Lecture

5. Business Intelligence Competitive intelligence is often carried out by specialists (former government officials, intelligence agents, etc.) Companies seek to gather information – possibly unethical, possibly illegal From competitors, from potential objectors… Victims may be unaware for a long time Example: Greenpeace sues Dow and Sasol N.A. for racketeering to discourage and disrupt the organization’s work Database Security Lecture

6. Other risks Hackers – for notoriety Disgruntled employees / former employees Activist attacks – political, environmental, etc. Patrons stealing audio books / e-books, etc. Crazy people!? Database Security Lecture

7. Levels of Data Security Physical security Operating System Network/User Interface Database system Database application program Database Security Lecture

8. Physical Security • Locks • Natural disasters • e.g. flood, fire, electrical surges • Human error • e.g. accidentally blowing a fuse • Solutions • Remote backup for disaster recovery • Archival backup (e.g. on tapes) Database Security Lecture

9. Operating System Security • Virus • attaches to a program, a file, or an email attachment • infects executables as it travels • executes as program is launched • Worm • a type of virus that can travel without human action • e.g. spreads through email addressbook • consumes system memory and clogs up bandwidth • Trojan horse • does not self-replicate • creates a “backdoor” that allows unauthorized access • Solutions • keep OS updated • use real-time anti-virus filters • regular virus scans • use a firewall Database Security Lecture

10. Network Security • Eavesdropping • unauthorized viewing of information • Masquerading • network traffic intercepted by a third party who impersonates the other party to either side (e.g. man-in-the-middle attacks) • Solutions • Encrypt all network traffic • Use secured protocols (e.g. https://) • Site authentication (digital certificates) Database Security Lecture

11. Database Security • Stolen disks, lost flash drives, etc. • Partial solution: • encrypt entire database at storage level • transparent to application • decryption key or password for database server Database Security Lecture

12. Database vs. Application Application authenticates/authorizes users Application authenticates itself to database Application Program Database Database Security Lecture

13. Application Security • Configuration management • misconfigured applications affect other database functions • Session management • session hijacking, session replay, etc. • Parameter manipulation • query string, form field, cookie, etc. • Solutions • authentication (next slide) and authorization (next section) management • incorporate extensive security testing into development process Database Security Lecture

14. Bill Gates User Authentication • Passwords too easy to guess • Passwords not stored in secured locations • Passwords shared with other people • Solutions • mandatory password validation • mandatory password change • identification cards / devices • biometric identification • additional pin • central authentication • password not shown to applications • single sign-on (??) • e.g. master password Database Security Lecture

15. Authorization Different authorizations for different users • each user is allowed access to only the required data and objects • application / “view” level authorization • especially for web users • drawback: authorization setups are not centralized Database Security Lecture

16. Insider Attack More people with access, more danger Some application programs might have database password Solutions • Careful allocation of authorizations on a need to use basis • multi-person approval policy • absence of any one user should not prevent database from functioning • digital signature for all transactions • paper trail for all transactions • programmers have no access to database password • small number of trusted administrators • independent monitoring of data and application corruption • cross-check with archival copies of database Database Security Lecture

17. Privacy Aggregate information about private information can be very valuable • mining for patterns can turn up consumer behaviors, spreading of epidemic, electoral behaviors, etc. Privacy concerns regarding data release • “anonymized” data: names, SS# removed • But zip code, gender and date of birth often uniquely identify people • There are sophisticated techniques out there that correlate multiple anonymous data sets • America Online, Aug 6, 2006 • Released 20 million search queries from 650,000 users • a random number replaces the userID • users could be easily identified in many cases • revealed personal info, medical info, illegal activities, plans to commit crimes, etc. Law suits pending, but legality uncertain Patron information? Patron web searches? Patron transactions? Database Security Lecture

18. Summary Data security and privacy is a great concern Requires security at many levels User training is essential Deploy technical solutions Conduct periodic independent audits Develop a security policy! Database Security Lecture

19. Extra Slides Database Security Lecture

20. Information Leakage So you thought only the query result matters? Database Security Lecture

21. σmyudf(E.salary) σmyudf(E.salary) σmyudf(E.salary) A1 myemployees employees A1 employees Information Leakage via UDFs • Auth view myemployee: only those employee whose dept_id is in A1 Query: select * from employee where myudf(salary) • Final query plan is not safe • UDF may be pushed down in plan, and executed on unauthorized intermediate result • As a side-effect, UDF may expose values passed to it [Litchfield] • Can be partly solved using sandboxing Database Security Lecture

22. Other channels of information leakage • Exceptions, Error Messages • Query: select * from employee where 1/(salary-100K) = 0.23 • Query plan: Selection condition in query gets pushed below authorization semi-join • Divide by zero exception if salary = 100K • Reveals that employee has salary = 100K • Timing Analysis • Sub-query can perform an expensive computation only if certain tuples are present in its input • To prevent leakage, treat all channels as unsafe operations Database Security Lecture

23. σmyudf(E.salary) σmyudf(E.salary) A1 employees employees A1 Preventing Information Leakage via UDFs • UDF on Top: Keep UDFs at the top of query plan • Definitely safe, no information leakage • Better plans possible if UDF is selective • Optimal Safe plan • When is a plan safe? • How to search for optimal safe plan? • For details, see: Kabra et al., SIGMOD 2006 Database Security Lecture

24. References • (Shameless advertisement!) Chapter 8 of Database System Concepts 5th Edition, Silberschatz, Korth and Sudarshan, McGraw-Hill • The Open Web Application Security Project • http://www.owasp.org • Web application security scanners • e.g. WebInspect (SPI Dynamics) • http://www.windowsecurity.com/software/Web-Application-Security/ • SQL Injection • http://www.cgisecurity.com/development/sql.shtml • 9 ways to hack a web app • http://developers.sun.com/learning/javaoneonline/2005/webtier/TS-5935.pdf • Related research papers • Kabra, Ramamurthy and Sudarshan, Redundancy and Information Leakage in Fine-Grained Access Control, SIGMOD 2006 • Rizvi, Mendelzon, Sudarshan and Roy, Extending Query Rewriting Techniques for Fine-Grained Access Control, SIGMOD 2004 Database Security Lecture

25. Authorization Forms of authorization on (parts of) the database: • Read authorization - allows reading, butnot modification of data. • Insert authorization - allows insertion of new data, but not modification of existing data. • Update authorization - allows modification, but not deletion of data. • Delete authorization - allows deletion of data Database Security Lecture

26. Security Specification in SQL • The grant statement is used to confer authorization grant <privilege list> on <relation name or view name> to <user list> • <user list> is: • a user-id • public, which allows all valid users the privilege granted • A role (more on this later) • Granting a privilege on a view does not imply granting any privileges on the underlying relations. • The grantor of the privilege must already hold the privilege on the specified item (or be the database administrator). Database Security Lecture

27. Privileges in SQL • select: allows read access to relation,or the ability to query using the view • Example: grant users U1, U2, and U3select authorization on the branch relation: grant select on branch to U1, U2, U3 • insert: the ability to insert tuples • update: the ability to update using the SQL update statement • delete: the ability to delete tuples. • references: ability to declare foreign keys when creating relations. • usage: In SQL-92; authorizes a user to use a specified domain • all privileges: used as a short form for all the allowable privileges Database Security Lecture

28. with grant option: allows a user who is granted a privilege to pass the privilege on to other users. Example: grant select on branch to U1with grant option gives U1 the select privileges on branch and allows U1 to grant this privilege to others Privilege To Grant Privileges Database Security Lecture

29. Roles • 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 • SQL:1999 supports roles create role tellercreate role manager grant select on branch to tellergrant update (balance) on account to tellergrant all privileges on account to managergrant teller to managergrantteller to alice, bobgrant manager to avi Database Security Lecture

30. Revoking Authorization in SQL • The revoke statement is used to revoke authorization. revoke<privilege list> on <relation name or view name> from <user list> [restrict|cascade] • Example: revoke select on branch from U1, U2, U3cascade • Revocation of a privilege from a user may cause other users also to lose that privilege; referred to as cascading of the revoke. • We can prevent cascading by specifying restrict: revoke select on branch from U1, U2, U3restrict With restrict, the revoke command fails if cascading revokes are required. Database Security Lecture

31. Revoking Authorization in SQL (Cont.) • <privilege-list> may be all to revoke all privileges the revokee may hold. • If <revokee-list> includes public all users lose the privilege except those granted it explicitly. • If the same privilege was granted twice to the same user by different grantees, the user may retain the privilege after the revocation. • All privileges that depend on the privilege being revoked are also revoked. Database Security Lecture

32. Secure Payment • Three-way communication between seller, buyer and credit-card company to make payment • Credit card company credits amount to seller • Credit card company consolidates all payments from a buyer and collects them together • E.g. via buyer’s bank through physical/electronic check payment • Several secure payment protocols • E.g. Secure Electronic Transaction (SET) Database Security Lecture