Database Security

1. Database Security An Overview

2. Why is database security important? • Databases often store sensitive data • Incorrect data or loss of data could negatively affect business operations • Databases can be used as bases to attack other systems • Data is a valuable resource!

3. Database Vulnerability Exploitation A decade ago, attacks were • Broad based • Launched by disaffected “Hackers” • Intended to disrupt, gain respect / notoriety in the community Now, attacks are • Targeted against specific resources • Launched by sophisticated professionals • Intended to bring monetary gain to the attacker

4. Meet a Hacker • Kevin Mitnick went on a 2 ½ year coast-to-coast hacking spree • He hacked into computers, stole corporate secrets, scrambled phone networks and broke into the national defense warning system • Mitnick was caught and convicted in 1995; He served five years, 8 months of it in solitary confinement • He is now a computer security consultant, author and speaker.

5. Problems with Hacking • Invasion of privacy • Theft of information • Trespassing/unauthorized entry • Potential for damage • Mitigation cost

6. Hacking Legality • It is illegal to break and enter a private or public network without permission • Technology makes it easier for companies to identify hackers • Penalties • Fines from $500-10,000 • Up to 5 years in prison

7. Causes of Data Breach http://www.symantec.com/content/en/us/about/media/pdfs/b-ponemon-2011-cost-of-data-breach-us.en-us.pdf?om_ext_cid=biz_socmed_twitter_facebook_marketwire_linkedin_2012Mar_worldwide__CODB_US

8. Attack Analysis http://www.symantec.com/content/en/us/about/media/pdfs/b-ponemon-2011-cost-of-data-breach-us.en-us.pdf?om_ext_cid=biz_socmed_twitter_facebook_marketwire_linkedin_2012Mar_worldwide__CODB_US

9. Organizational Cost http://www.symantec.com/content/en/us/about/media/pdfs/b-ponemon-2011-cost-of-data-breach-us.en-us.pdf?om_ext_cid=biz_socmed_twitter_facebook_marketwire_linkedin_2012Mar_worldwide__CODB_US

10. Why is Security Difficult? • No system can be 100% secure • Difficult to prove good security • Bad security gets proven for us • Good security and no security can look the same • Many things to secure • People • Equipment • Operating System • Network • Applications • Databases • Application Servers

11. Balancing the Business

12. Hardening Databases • Follow the principle of least privilege • Manage user accounts • Create views • Use firewalls/access control • Enforce password security • Protect physical security • Be wary of SQL injection

13. Principle of Least Privilege • Giving a user only those privileges which are essential to do his/her work • Databases and tables • Columns and rows • Actions • INSERT, UPDATE, DELETE • CREATE/DROP/ALTER • GRANT option • Connections • Allow Joe to login from any campus IP address • GRANT […] ON somedb.sometable TO joe@’128.255.0.0/255.255.0.0’;

14. GRANT SELECT ON Employee TO Red GRANT SELECT ON Employee TO Black WITH GRANT OPTION GRANT UPDATE(Salary) ON Employee TO White Grant and Revoke Black Red Brown (owner) White

15. Example • Create a user that has read-only access to the presidents table. CREATE USER 'black'@'localhost' IDENTIFIED BY 'black123'; GRANT SELECT ON sampdb.president TO black; SELECT * FROM president; SELECT * FROM student; DELETE FROM president WHERE first_name = ‘Calvin’

16. Revoking Privileges GRANT SELECT ON sampdb.president TO black;REVOKE SELECT on sampdb.presidentFROM black; GRANT SELECT, UPDATE ON sampdb.studentTO red;REVOKE UPDATE on sampdb.studentFROM red; You can only revoke privileges that have been granted!

17. Password Security • Strong passwords are a must • Default passwords are widely known & publicized • MySQL Defaults • Account: root / Password: null • Account: admin / Password: admin • Password policy control • Limit # of failed login attempts • Limit # of times password can be reused • Expire passwords after a certain length of time • Encrypt passwords

18. Password Encryption CREATETABLEusers( username VARCHAR( 30 ) NOTNULL ,access_levelTINYINT( 1 ) ,passwordVARCHAR( 40 ) ,PRIMARYKEY ( username ) ); SELECT access_levelFROM usersWHERE username = 'jshmo'AND PASSWORD =  SHA1( 'shmo123' ); INSERT INTO users VALUES  ('jshmo', 1, SHA1( 'shmo123' )),  ('ssmith', 2, SHA1( 'bubb13s' ));

19. Views • A view is essentially a saved query • A view is a “virtual table” that can be queried just like a real table • Any query can be saved as a view, but not all views are updateable • The view data is not stored, only the view definition

20. Create Views Employee Table CREATE VIEW toy_dept AS SELECT Name, Salary, Manager FROM Employee WHERE Dept=“Toy”; SELECT * FROM toy_dept;

21. Practice Using sampdb: • Create a view called quiz_view that displays the student ID, student name, event ID, event date and score for all Quizzes • Query the view • Display all of the rows in the view • Display the rows in the view where the score is greater than 15 • Change the score for student_id 1, event_id 1 to 19

22. Why use views? • Security • Hide information from certain users • Simplicity • Simplify complex queries • Flexibility • Change your database structure without changing the applications that access that database • Usability • Table columns can be renamed in the viewCREATE VIEW studentView(StudentName, StudentSID) AS SELECT name, sid FROM student;

23. WITH CHECK OPTION • WITH CHECK OPTION specifies that changes cannot be made to the view unless they are visible to the view CREATE VIEW cust AS SELECT * FROM customers WHERE balance > 1000 WITH CHECK OPTION UPDATE cust SET balance = 500WHERE custID = 99; will FAIL!

24. Protect Physical Security • Database servers should be physically secure • Limit access to only authorized personnel • Protect against fire, power failure, water, and heat • Dispose properly of broken disk drives • Protect backup media

25. Backup and Recovery • Common causes of database failure • Hardware failure • Program bug • Human error • Malicious actions • Recovery • Fallback procedures • Restore from backup • Replay database activities since backup http://dev.mysql.com/doc/refman/5.0/en/backup-and-recovery.html

26. SQL Injection • SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. • Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you an SQL statement that you will unknowingly run on your database.

27. How it Works $name = $_POST[‘name’]; $query = "SELECT * FROM customers WHERE username = '$name'"; echo $query . "<br />"; Name: Timmy SELECT * FROM customers WHERE username = 'timmy' Name: ' OR 'x'='x SELECT * FROM customers WHERE username = '' OR ‘x’=‘x’

28. How it Works $name = $_POST[‘name’]; $query = "SELECT * FROM customers WHERE username = '$name'"; echo $query . "<br />"; Name: '; DELETE FROM customers WHERE 1 or username = ' SELECT * FROM customers WHERE username = ' '; DELETE FROM customers WHERE 1 or username = ' '

29. Preventing SQL injection • Filter data using mysql_real_escape_string() • Check that each piece of data is the right type • Use prepared statements SELECT * FROM customers WHERE username = ‘\' OR \‘x\’=\‘x’

30. Prepared Statements <?php$stmt = $dbh-> prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");$stmt->bindParam(':name', $name);$stmt->bindParam(':value', $value);// insert one row$name = 'one';$value = 1;$stmt->execute();// insert another row with different values$name = 'two';$value = 2;$stmt->execute();?>

32. Establishing a Security Mindset • Be Curious • Be Proactive • Be Paranoid • Be Cautious • Be Educated • Be a Minimalist • Be Vigilant

33. Resources • D.Litchfield, C.Anley, J. Heasman, B. Grindlay, The Database Hacker’s Handbook – Defending Database Servers, Indianapolis: Wiley Publishing Inc., 2005. • http://databasesecurity.com • http://blogs.msdn.com/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx • http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx • http://www.cgisecurity.com