1 / 41

C20.0046: Database Management Systems Lecture #18

C20.0046: Database Management Systems Lecture #18. M.P. Johnson Stern School of Business, NYU Spring, 2008. Agenda. Security Secrecy Integrity Availability Web issues Transactions Stored procedures? Implementation?. Goals: after today. After Today:

delilah-contreras
Download Presentation

C20.0046: Database Management Systems Lecture #18

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. C20.0046: Database Management SystemsLecture #18 M.P. Johnson Stern School of Business, NYU Spring, 2008 M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  2. Agenda • Security • Secrecy • Integrity • Availability • Web issues • Transactions • Stored procedures? • Implementation? M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  3. Goals: after today • After Today: • Know how to make your PHP-based sites (somewhat more) secure M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  4. New topic: Security issues • Secrecy • E.g.: You can see only your own grades • Integrity • E.g.: Only an instructor can assign grades, and only to his students • Web issues • E.g.: injection attacks M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  5. Why security is hard • It’s a “negative deliverable” • It’s an asymmetric threat • It’s open-ended • Tolstoy: “Happy families are all alike; every unhappy family is unhappy in its own way.” • Analogs: “homeland” security, jails, debugging, proofreading, Popperian science, fishing, MC algs M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  6. DB users have privileges • SELECT: read access to all columns • INSERT(col-name): can insert rows with non-default values in this column • INSERT: can insert rows with non-default values in all columns • DELETE • REFERENCES(col-name): can define foreign keys that refer to (or other constraints that mention) this column • TRIGGER: triggers can reference table • EXECUTE: can run function/SP M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  7. Granting privileges (Oracle) • Usual of setting access levels • Creator of object automatically gets all privileges to it • Possible objects: tables, whole databases, stored functions/procedures, etc. • <DB-name>.* - all tables in DB • A privileged user can grant privileges to other users or groups GRANTSELECTONmytableTOsomeoneWITH GRANT OPTION; GRANTprivilegesON object TO user <WITH GRANT OPTION> M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  8. Granting and revoking • Privileged user has privileges • Privileged-WGO user can grant them, w/wo GO • Granter can revoke privileges or GO • Revocation cascades by default • To prevent, use RESTRICT (at end of cmd) • If would cascade, command fails • Can change owner: ALTER TABLE my-tbl OWNER TO new-owner; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  9. Granting and revoking • What we giveth, we may taketh away • mjohnson: (effects?) • george: (effects?) • mjohnson: (effects?) GRANTSELECT, INSERTON my-table TO george WITH GRANT OPTION; GRANTSELECTON my-table TO laura; REVOKESELECTON my-table FROM laura; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  10. Role-based authorization • In SQL-1999, privileges assigned with roles • Not yet supported in MySql • For example: • Student role • Instructor role • Admin role • Each role gets to do same (sorts of) things • Privileges assigned by assigning role to users GRANTSELECTON my-table TOemployee; GRANTemployeeTO billg; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  11. Issue: Passwords • DBMS recognizes your privileges because it recognizes you • how? • Storing passwords in the DB is a bad idea M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  12. Hashed or digested passwords • One-way hash function: • computing f(x) is easy; • Computing f-1(y) is hard/impossible; • Finding some x2 s.t. f(x2) = f(x) is hard/imposs • “collisions” • Intuitively: seeing f(x) gives little (useful) info on x • x “looks random” • PRNGs • MD5, SHA-1 • RFID for cars: http://www.rfidanalysis.org/ M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  13. Issue: Built-in accounts • Many DBMSs (and OSs) have built-in demo accounts by default • In some versions, must “opt out” • MySQL: root/(blank) (closed on sales) • http://lists.seifried.org/pipermail/security/2004-February/001782.html • Oracle: scott/tiger (was open on sales last year) • SQLServer: sa/(blank/null) • http://support.microsoft.com/default.aspx?scid=kb;EN-US;313418 M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  14. New topic: Security on the web • Authentication • If the website user wants to pay with George’s credit card, how do we know it’s George? • If the website asks George for his credit card, how does he know it’s our site? • Maybe it’s a phishing site… • Secrecy • When George enters his credit card, will an eavesdropper be able to see it? • Protecting against user input • Is it safe to run SQL queries based on user input? M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  15. Security on the web • Obvious soln: passwords • What’s the problem? • Slightly less obvious soln: passwords + encryption • Traditional encryption: “symmetric” / “private key” • DES, AES – fast – solves problem? • “Newer” kind: “asymmetric” / “public key” • Public key is published somewhere • Private key is top secret • RSA – slow – solves problem? M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  16. Hybrid protocols (SSH,SSL/HTTPS, etc.) • Neither private- nor public-key alone suffices • They each only solve half of each problem • But together they solve almost everything • Recurring strategy: • We do private-key crypto • Where do we get the key? • You send it (encrypted) to me M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  17. SSH-like authentication (intuition) • sales has a public-key • When you connect to sales, • You pick a random number • Encrypt it (with the cert) and send it to them • They decrypt it (with their private key) • Now, they send it back to you • Since they decrypted it, you trust they’re sales M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  18. HTTPS-like authentication (intuition) • Amazon has a public-key certificate • Encrypted with, say, Verisign’s private key • When you log in to Amazon, • They send you the their Verisign-encrypted cert • You decrypt it (with Verisign’s public key), and check that it’s a cert for amazon.com • Since the decrypt worked, the cert must have been encrypted by Verisign • So this must really be Amazon M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  19. Authentication on the web • Now George trusts that it’s really Amazon • Assuming Amazon’s private key is secure • And excluding man-in-the-middle… • But: What if, say, Dick guessed George’s password? • Another way: What if George claims Dick guessed his password? • Soln: same process, but in reverse • But now you need to get your own cert… M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  20. Hybrid protocol for encryption • Amazon just sent you their public-key cert • When you log in to Amazon, • You pick a random number (“session key”) • You encrypt it (with the cert) and send it to them • They decrypt it (with their private key) • Now, you both share a secret key • can now encrypt passwords, credit cards, etc. M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  21. New topic: Security and CGI • CGI has two parameter methods: • GET • POST • For secret information, GET is obviously insecure • Displays in browser • Written into server log • Either way, data can still be sniffed • Soln: encryption M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  22. CGI & security • Imagine scenario: • You’re Amazon • Users can search for books • Users can put books in the cart • A couple pages to pay • You need to • Charge P (the book’s price) at the end • Display P on each page • Don’t want to query of price for every single page • One bad idea: each page after first takes P as a (hidden) get var from prior M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  23. CGI & security • Attack: type in false data in GET request • Very insecure! • Soln 1: Use POST, not GET http://amazon.com/cart.cgi?title=Database+Systems&price=.01 M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  24. Send price, etc., by POST • This is more secure • Fewer users will know how to break POST than GET • But some do! • Attack: hand-code the POST request sales% telnet amazon.com 80 POST http://amazon.com/cart.cgi HTTP/1.0 Content-Type:application/x-www-form-urlencoded Content-Length: 32 title=Database+Systems&price=.01 M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  25. Hand-written POST example • POST version of my input page: • http://pages.stern.nyu.edu/~mjohnson/dbms/php/post.php • Not obvious to web user how to hand submit • And get around any client-side validation • But possible: • http://pages.stern.nyu.edu/~mjohnson/dbms/eg/postbyhand.txt sales% telnet pages.stern.nyu.edu 80 POST http://pages.stern.nyu.edu/~mjohnson/dbms/php/post.php HTTP/1.0 Content-Type: application/x-www-form-urlencoded Content-Length: 15 val=6&submit=OK M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  26. Query-related: Injection attacks • Here’s a situation: • Prompt for user/pass • Do lookup: • If found, user gets in • test.user table in MySQL • http://pages.stern.nyu.edu/~mjohnson/dbms/php/login.php / txt • http://pages.stern.nyu.edu/~mjohnson/dbms/php/users.php / txt • Modulo the no hashing, is this a good idea? SELECT * FROM users WHERE user=u AND password=p; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  27. Injection attacks • We expect to get input of something like: • user: mjohnson • pass: topsecret  SELECT * FROM users WHERE user = u AND password = p; SELECT * FROM users WHERE user= 'mjohnson' AND password = 'topsecret'; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  28. Injection attacks – MySQL/Perl/PHP • Consider another input: • user: ' OR 1=1 OR user = ' • pass: ' OR 1=1 OR pass = '  SELECT * FROM users WHERE user = u AND password = p; SELECT * FROM users WHERE user = '' OR 1=1 OR user = '' AND password = '' OR 1=1 OR pass = ''; http://pages.stern.nyu.edu/~mjohnson/dbms/php/login.php http://pages.stern.nyu.edu/~mjohnson/dbms/eg/injection.txt SELECT * FROM users WHERE user = '' OR 1=1 OR user = '' AND password = '' OR 1=1 OR pass = ''; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  29. Injection attacks – MySQL/Perl/PHP • Consider this one: • user: your-boss' OR 1=1 # • pass: abc  SELECT * FROM users WHERE user = u AND password = p; http://pages.stern.nyu.edu/~mjohnson/dbms/php/login.php SELECT * FROM users WHERE user = 'your-boss' OR 1=1 #' AND password = 'abc'; SELECT * FROM users WHERE user = 'your-boss' OR 1=1 #' AND password = 'abc'; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  30. Injection attacks – MySQL/Perl/PHP • Consider another input: • user: your-boss • pass: ' OR 1=1 OR pass = '  SELECT * FROM users WHERE user = u AND password = p; http://pages.stern.nyu.edu/~mjohnson/dbms/php/login.php SELECT * FROM users WHERE user = 'your-boss' AND password = '' OR 1=1 OR pass = ''; SELECT * FROM users WHERE user = 'your-boss' AND password = '' OR 1=1 OR pass = ''; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  31. Multi-command inj. attacks (other DBs) • Consider another input: • user: '; DELETE FROM users WHERE user = 'abc'; SELECT FROM users WHERE password = ' • pass: abc  SELECT * FROM users WHERE user = u AND password = p; SELECT * FROM users WHERE user = ''; DELETE FROM users WHERE user = 'abc'; SELECT FROM users WHERE password = '' AND password = 'abc'; SELECT * FROM users WHERE user = ''; DELETE FROM users WHERE user = 'abc'; SELECT FROM users WHERE password = '' AND password = 'abc'; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  32. Multi-command inj. attacks (other DBs) • Consider another input: • user: '; DROP TABLE users; SELECT FROM users WHERE password = ' • pass: abc  SELECT * FROM users WHERE user = u AND password = p; SELECT * FROM users WHERE user = ''; DROP TABLE users; SELECT FROM users WHERE password = '' AND password = 'abc'; SELECT * FROM users WHERE user = ''; DROP TABLE users; SELECT FROM users WHERE password = '' AND password = 'abc'; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  33. Multi-command inj. attacks (other DBs) • Consider another input: • user: '; SHUTDOWN WITH NOWAIT; SELECT FROM users WHERE password = ' • pass: abc  SELECT * FROM users WHERE user = u AND password = p; SELECT * FROM users WHERE user = ''; SHUTDOWN WITH NOWAIT; SELECT FROM users WHERE password = '' AND password = 'abc'; SELECT * FROM users WHERE user = ''; SHUTDOWN WITH NOWAIT; SELECT FROM users WHERE password = '' AND password = 'abc'; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  34. Injection attacks – MySQL/Perl/PHP • Consider another input: • user: your-boss • pass: ' OR 1=1 AND user = 'your-boss  Delete your boss! DELETE FROM users WHERE user = u AND password = p; http://pages.stern.nyu.edu/~mjohnson/dbms/php/users.php DELETE FROM users WHERE user = 'your-boss' AND pass = '' OR 1=1 AND user = 'your-boss'; DELETE FROM users WHERE user = 'your-boss' AND pass = ' ' OR 1=1 AND user = 'your-boss'; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  35. Injection attacks – MySQL/Perl/PHP • Consider another input: • user: ' OR 1=1 OR user = ' • pass: ' OR 1=1 OR user = '  Delete everyone! DELETE FROM users WHERE user = u AND pass = p; DELETE FROM users WHERE user = '' OR 1=1 OR user = '' AND pass = '' OR 1=1 OR user = ''; http://pages.stern.nyu.edu/~mjohnson/dbms/php/users.php DELETE FROM users WHERE user = '' OR 1=1 OR user = '' AND pass = '' OR 1=1 OR user = ''; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  36. Preventing injection attacks • Ultimate source of problem: quotes • Soln 1: don’t allow quotes! • Reject any entered data containing single quotes • Q: Is this satisfactory? • Does Amazon need to sell O’Reilly books? • Soln 2: escape any single quotes • Replace any ' with a '' or \' • In Perl, use taint mode – won’t show • In PHP, turn on magic_quotes_gpc flag in .htaccess • show both PHP versions M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  37. Preventing injection attacks • Soln 3: use prepare parameter-based queries • Supported in JDBC, Perl DBI, PHP ext/mysqli • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/loginsafe.cgi • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/userssafe.cgi • Even more dangerous: using tainted data to run commands at the Unix command prompt • Semi-colons, prime char, etc. • Safest: define set if legal chars, not illegal ones M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  38. Preventing injection attacks • When to do security-checking for quotes, etc.? • Temping choice: in client-side data validation • But not enough! • As saw earlier: can submit GET and POST params manually •  Must do security checking on server • Even if you do it on client-side too • Same with data-validation • Example of constraints M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  39. More Info • phpGB MySQL Injection Vulnerability • http://www.securiteam.com/unixfocus/6X00O1P5PY.html • "How I hacked PacketStorm“ • http://www.wiretrip.net/rfp/txt/rfp2k01.txt M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  40. Now there’s “Google hacking”… • inurl:"ViewerFrame?Mode=" • intitle:"Live View / - AXIS" | inurl:view/view.sht • intitle:"toshiba network camera - User Login" • http://200.71.42.48/ViewerFrame?Mode=Motion&Language=0 • http://141.211.44.254/view/index.shtml • http://66.186.226.189/view/index.shtml M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  41. Security Conclusion • Not an exhaustive list of issues • Big, serious, difficult problems… • Each DBMS/product/tech has its own issues • Do your hw, or you/your company can look ridiculous or worse M.P. Johnson, DBMS, Stern/NYU, Spring 2008

More Related