1 / 109

OCL4 Oracle 10 g : SQL & PL/SQL Session #8

OCL4 Oracle 10 g : SQL & PL/SQL Session #8. Matthew P. Johnson CISDD, CUNY January, 2005. Agenda. Security & web apps RegEx support in 10g Oracle & XML Data warehousing More on the PL/SQL labs Any more lab?. Review: Why security is hard. It’s a “negative deliverable”

mills
Download Presentation

OCL4 Oracle 10 g : SQL & PL/SQL Session #8

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. OCL4 Oracle 10g:SQL & PL/SQLSession #8 Matthew P. Johnson CISDD, CUNY January, 2005 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  2. Agenda • Security & web apps • RegEx support in 10g • Oracle & XML • Data warehousing • More on the PL/SQL labs • Any more lab? Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  3. Review: Why security is hard • It’s a “negative deliverable” • It’s an asymmetric threat • Tolstoy: “Happy families are all alike; every unhappy family is unhappy in its own way.” • Analogs: “homeland”, jails, debugging, proof-reading, Popperian science, fishing, MC algs • So: fix biggest problems first Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  4. 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 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  5. Granting privileges (Oracle) • One method 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> Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  6. 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; Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  7. 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; Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  8. Role-based authorization • In SQL-1999, privileges assigned with roles • 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; Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  9. Passwords • DBMS recognizes your privileges because it recognizes you • how? • Storing passwords in the DB is a bad idea Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  10. 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/ Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  11. 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 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  12. 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/loginphp.txt • http://pages.stern.nyu.edu/~mjohnson/dbms/php/login.php • Apart from no hashing, is this safe? SELECT * FROM users WHERE user=u AND password=p; Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  13. Injection attacks • We expect to get input of something like: • user: mjohnson • pass: secret  SELECT * FROM users WHERE user = u AND password = p; SELECT * FROM users WHERE user= 'mjohnson' AND password = 'secret'; Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  14. 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 = ''; Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  15. 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'; Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  16. 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 = ''; Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  17. 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'; Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  18. 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'; Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  19. 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'; Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  20. 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'; Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  21. 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 = ''; Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  22. 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 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  23. 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 • Very 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 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  24. Preventing injection attacks • When to do security checking for quotes, etc.? • Natural 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 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  25. 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 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  26. SQL*Plus settings SQL> SET RECSEP OFF SQL> COLUMN text FORMAT A60 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  27. New topic: Regular Expressions • In automata theory, Finite Automata are the simplest weakest of computer, Turing Machines the strongest • Chomsky’s Hierarchy • FA are equivalent to a regular expression • Expressions that specify a pattern • Can check whether a string matches the pattern Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  28. RegEx matching • Use REGEX_LIKE • Metachar for any char is . • First, get employee_comment table: • http://pages.stern.nyu.edu/~mjohnson/oracle/empcomm.sql • Now do search: • So far, like LIKE SELECT emp_id, text FROM employee_comment WHERE REGEXP_LIKE(text,'...-....'); Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  29. RegEx matching • Can also pull out the matching text with REGEXP_SUBSTR: • If want only numbers, can specify a set of chars rather than a dot: SELECT emp_id, REGEXP_SUBSTR(text,'...-....') text FROM employee_comment WHERE REGEXP_LIKE(text,'...-....'); SELECT emp_id, REGEXP_SUBSTR(text, '[0123456789]..-...[0123456789]') text FROM employee_comment WHERE REGEXP_LIKE(text, '[0123456789]..-...[0123456789]'); Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  30. RegEx matching • Or can specify a range of chars: • Or, finally, can state how many copies to match: SELECT emp_id, REGEXP_SUBSTR(text, '[0-9]..-....') text FROM employee_comment WHERE REGEXP_LIKE(text,'...-....'); SELECT emp_id, REGEXP_SUBSTR(text, '[0-9]{3}-[0-9]{4}') text FROM employee_comment WHERE REGEXP_LIKE(text,'[0-9]{3}-[0-9]{4}'); Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  31. RegExp matching • Other operators: • * - 0 or more matches • + - 1 or more matches • ? - 0 or 1 match • Also, can OR options together with | op • Here: some phone nums have area codes, some not, so want to match both: SELECT emp_id, REGEXP_SUBSTR(text, '[0-9]{3}-[0-9]{3}-[0-9]{4}|[0-9]{3}-[0-9]{4}') text FROM employee_comment WHERE REGEXP_LIKE(text,'[0-9]{3}-[0-9]{3}-[0-9]{4}|[0-9]{3}-[0-9]{4}'); Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  32. RegExp matching • Order of ORed together patterns matters: • First matching pattern wins SELECT emp_id, REGEXP_SUBSTR(text, '[0-9]{3}-[0-9]{4}|[0-9]{3}-[0-9]{3}-[0-9]{4}') text FROM employee_comment WHERE REGEXP_LIKE(text,'[0-9]{3}-[0-9]{4}|[0-9]{3}-[0-9]{3}-[0-9]{4}'); Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  33. RegExp matching • There’s a shared structure between the two, tho • Area code is just optional • Can use ? op SELECT emp_id, REGEXP_SUBSTR(text, '([0-9]{3}-)?[0-9]{3}-[0-9]{4}') text FROM employee_comment WHERE REGEXP_LIKE(text,'([0-9]{3}-)?[0-9]{3}-[0-9]{4}'); Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  34. RegExp matching • Also, different kinds of separators: • dash, dot, just blank • Can OR together whole number patterns • Better: Just use set of choices of each sep. SELECT emp_id, REGEXP_SUBSTR(text, '([0-9]{3}[-. ])?[0-9]{3}[-. ][0-9]{4}') text FROM employee_comment WHERE REGEXP_LIKE(text,'([0-9]{3}[-. ])?[0-9]{3}[-. ][0-9]{4}'); Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  35. RegExp matching • One other thing: area codes in parentheses • Of course, area codes are still optional • Parentheses must be escaped - \( \) SELECT emp_id, REGEXP_SUBSTR(text, '([0-9]{3}[-. ]|\([0-9]{3}\) )?[0-9]{3}[-. ][0-9]{4}') text FROM employee_comment WHERE REGEXP_LIKE(text,'([0-9]{3}[-. ]|\([0-9]{3}\) )?[0-9]{3}[-. ][0-9]{4}'); Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  36. And now for something completely different: XML • XML: eXtensible Mark-up Language • Very popular language for semi-structured data • Mark-up language: consists of elements composed of tags, like HTML • Emerging lingua franca of the Internet, Web Services, inter-vender comm Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  37. Unstructured data • At one end of continuum: unstructured data • Text files • Stock market prices • CIA intelligence intercepts • Audio recordings • “Just one damn bit after another” • ~ Henry Ford • No (intentional, formal) patterns to the data • Difficult to manage/make sense of • Why we need data-mining Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  38. Structured data • At the other end: structured data • Tables in RDBMSs • Data organized into semantic chunks • entities • Similar/related entities grouped together • Relationships, classes • Entities in same group have same structure • Same fields/attributes/properties • Easy to make sense of • But sometimes too rigid a req. • Difficult to send—convert to tab-delimited Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  39. Semi-structured data • Not too random • Data organized into entities • Similar/related grouped to form other entities • Not too structured • Some attributes may be missing • Size of attributes may vary • Support of lists/sets • Juuust Right • Data is self-describing Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  40. Semi-structured data • Predominant examples: • HTML: HyperText Mark-up Language • XML: eXtensible Mark-up Language • NB: both mark-up languages (use tags) • Mark-up lends self of semi-structured data • Demarcate boundaries for entities • But freely allow other entities inside Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  41. Data model for semi-structured data • Usually represented as directed graphs • Graph: set of vertices (nodes) and edges • Dots connected by lines; not nec. a tree! • In model, • Nodes ~ entities or fields/attributes • Edges ~ attribute-of/sub-entity-of • Example: publisher publishes >=0 books • Each book has one title, one year, >=1 authors • Draw publishers graph Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  42. XML is a SSD language • Standard published by W3C • Officially announced/recommended in 1998 • XML != HTML • XML != a replacement for HTML • Both are mark-up languages • Big diffs: • XML doesn’t use predefined tags (!) • But it’s extensible: tags can be added • HTML is about presentation: <I>, <B>, <P> • XML is about content: <book>, <author> Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  43. XML syntax • Like HTML in many respects but more strict • All tags must be closed • Can’t have: this is a line<br> • Every start tag has an end tag • Although <br/> style can replace both • IS case-sensitive • IS space-sensitive • XML doc has a unique root element Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  44. XML syntax • Tags must be properly nested • Not allowed <b><i>I’m not kidding</b></i> • Intuition: file folders • Elements may have quoted attributes • <Myelm myatt=“myval”>…</Myelm> • Comments same as in HTML: • <!-- Pay no attention… --> • Draw publishers XML Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  45. Escape chars in XML • Some chars must be escaped • Distinguish content from syntax • Can also declare value to be pure text: <elm>3 &lt; 5</elm> <elm>&quot;Don&apos;t call me &apos;Ishmael&apos;!&quot;</elm> <aRealTag> <![CDATA[<notAtag>jsdljsd<neitherAmI<“'><>>]]></aRealTag> Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  46. XML Namespaces • Different schemas/DTDs may overlap • XHTML and MathML share some tags • Soln: namespaces • as in Java/C++/C# <bookxmlns:isbn="www.isbn-org.org/def"> <title>...</title> <number>15</number> <isbn:number>...</isbn:number> </book> Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  47. <persons> <row><name>Michael</name> <ssn>123</ssn></row> <row><name>Hilary</name> <ssn>456</ssn></row> <row><name>Bill</name> <ssn>789</ssn></row> </persons> From Relational Data to XML Data persons XML: persons row row row name ssn name ssn name ssn “Michael” 123 “Hilary” 456 “Bill” 789 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  48. Semi-structured Data Explained • List-valued attributes • XML is not 1NF! • Impossible in (single, BCNF) tables: <persons> <row><name>Hilary</name> <phone>202-222-2222</phone> <phone>914-222-2222</phone></row> <row><name>Bill</name> <phone>914-222-2222</phone> <phone>212-333-3333</phone></row> </persons>  two phones! ??? Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  49. <movieinfo> <movieid="o111"> <title>Lost in Translation</title> <year>2003</year> <starsidref="o333 o444"/> </movie> <movieid="o222"> <title>Hamlet</title> <year>1999</year> <starsidref="o333"/> </movie> <personid="o456"> <personid="o111"> <name>Bill Murray</name> <moviesidref="o111 o222"/> </person> </movieinfo> Object ids and References • SSD graph might not be trees! • But XML docs must be • Would cause much redundancy • Soln: same concept as pointers in C/C++/J • Object ids and references • Graph example: • Movies: Lost in Translation, Hamlet • Stars: Bill Murray, Scarlet Johansson Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

  50. What do we do with XML? • Things done with XML: • Send to partners • Parse XML received • Convert to RDBMS rows • Query for particular data • Convert to other XML • Convert to formats other than XML • Lots of tools/standards for these… Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

More Related