Database Security: Access Control and SQL Injection

1. Database Security:Access Control and SQL Injection Lars Olson CS 461 Fall 2007

2. Overview • Security challenges for databases • SQL overview • Access Control • Standard SQL access control syntax • Semantics of revocation • Oracle Virtual Private Database policies • SQL Injection • Attack examples • Preventing attacks

3. References (Access Control) • P. P. Griffiths and B. W. Wade: “An Authorization Mechanism for a Relational Database System” • Oracle Corporation: “Oracle Virtual Private Database” (white paper) • R. Agrawal et al.: “Hippocratic Databases” • Any database textbook or reference with SQL • H. Garcia-Molina et al.: “Database Systems: The Complete Book” • R. Ramakrishnan and J. Gehrke: “Database Management Systems

4. References (SQL Injection) • Attack examples • SecuriTeam SQL Injection Walkthrough • More examples and prevention tips (e.g. prepared statements) • S. Friedl: “SQL Injection Attacks by Example” • Wikipedia entry: “SQL Injection” • Second-order SQL injection and syntax analysis • Z. Su and G. Wassermann: “The Essence of Command Injection Attacks in Web Applications” • Other references available through S. Ng: “Advanced Topics on SQL Injection Protection” • S. Bandhakavi et al: “CANDID:  Preventing SQL Injection Attacks Using Dynamic Candidate Evaluations”

5. Access Control for Databases • Challenges: • Multiple operations: select (read), insert/update/delete (write), reference, create trigger, execute stored procedure, create tables, ... • Table-level access control is too coarse-grained, cell-level access control is too tedious (more on that later) • SQL has standardized access control policy definition language • Security model developed by Griffiths and Wade in 1976

6. Application b Access Control Rules Database User a b Targets for Attack • Database applications often need to serve multiple users • Programmers often give their applications elevated privileges

7. Quick SQL Overview (1) • Creating tables: createtable table_name ( column1 type1, column2 type2, ... ); • Deleting tables: droptable table_name;

8. Quick SQL Overview (2) • Types: • int • float • date • char(size) • Always delimited by single quote (apostrophe) • Use two single quotes to represent the apostrophe character • varchar(size) (varchar2 in Oracle) • text (long in Oracle)

9. Quick SQL Overview (3) • Querying tables: select column1, column2 from table_name; or select * from table_name; • Conditions: select columns from table_name where condition;

10. Quick SQL Overview (4) • Inserting new rows: insertinto table_name values (value1, value2); or insertinto table_name set column1=value1, column2=value2, ...; • Updating rows: update table_name set column1=value1 where condition;

11. Quick SQL Overview (5) • Deleting rows: deletefrom table_name where condition; • Set values in conditions: select * from table_name where column in (select_statement); or select * from table_name where column in (value1, value2, ...);

12. Quick SQL Overview (6) • Joining tables: select * from table1, table2 where table1.attribute1 = table2.attribute2; • Built-in Functions selectcount(*) from test;

13. Quick SQL Overview (7) • Pattern Matching select * from test where a like'%c_t%'; • Other Keywords select * from test where a isnull; • Metadata Tables • Highly vendor-specific • Available tables, table structures are usually stored in some reserved table name(s).

14. SQL grant Syntax grant privilege_list on resource to user_list; • Privileges include select, insert, etc. • Resource may be a table, a database, a function, etc. • User list may be individual users, or may be a user group Griffiths Wade 76

15. Example Application • Alice owns a database table of company employees: name varchar(50), ssn int, salary int, email varchar(50) • Some information (ssn, salary) should be confidential, others can be viewed by any employee.

16. Simple Access Control Rules • Suppose Bob needs access to the whole table (but doesn’t need to make changes): grant select on employee to bob; • Suppose Carol is another employee, who should only access public information: grant select(name,email) on employee to carol; • not implemented in PostgreSQL (see next two slides for how to work around this) • not implemented for select in Oracle • implemented in MySQL

17. Creating Views • Careful with definitions! • A subset of the database to which a user has access, or: • A virtual table created as a “shortcut” query of other tables • View syntax: createview view_name as query_definition; • Querying views is nearly identical to querying regular tables

18. View-Based Access Control • Alternative method to grant Carol access to name and email columns: createview employee_public asselect name,email from employee; grantselecton employee_public to carol;

19. Row-Level Access Control • Suppose we also allow employees to view their own ssn, salary: createview employee_Carol asselect * from employee where name='Carol'; grantselecton employee_Carol to carol; • And we allow them to update their e-mail addresses: grantupdate(email) on employee_Carol to carol; • (Or create yet another new view…)

20. Delegating Policy Authority grant privilege_list on resource to user_list withgrantoption; • Allows other users to grant privileges, including “with grant option” privileges • Can grant subset privileges too • Alice: grantselecton table1 to bob withgrantoption; • Bob: grantselect(column1) on table1 to carol withgrantoption;

21. SQL revoke Syntax revoke privilege_list on resource from user_list; • What happens when a user is granted access from two different sources, and one is revoked? • What happens when a “with grant option” privilege is revoked?

22. Griffiths-Wade Model • Sequences of grant/ revoke operations • When a privilege is revoked, the ACLs should be indistinguishable from a sequence in which the grant never occurred.

23. Grants from Multiple Sources • grant(Alice,Bob) • grant(Alice,Carol) • grant(Carol,Bob) • revoke(Alice,Bob) • grant(Alice,Bob) • grant(Alice,Carol) • grant(Carol,Bob) • revoke(Alice,Bob) Bob Alice Carol

24. Not as Easy as it Looks! • grant(Alice,Bob) • grant(Bob,Carol) • grant(Carol,Bob) • revoke(Alice,Bob) • grant(Alice,Bob) • grant(Bob,Carol) • grant(Carol,Bob) • revoke(Alice,Bob) Bob Alice Carol

25. Cascading Revocations • grant(Alice,Bob) • grant(Alice,Carol) • grant(Carol,David) • grant(Bob,Carol) • revoke(Alice,Carol) • grant(Alice,Bob) • grant(Alice,Carol) • grant(Carol,David) • grant(Bob,Carol) • revoke(Alice,Carol) Alice ? Carol David Bob

26. Meanwhile, in the Real World... • Account privileges get changed all the time • We don’t always want to redo everything • Tedious • Involves other users’ actions • SQL revoke command has two optional arguments: • cascade: undoes all dependent grant commands • restrict: exits with failure if there exist dependent grants Ramakrishnan Gehrke 03

27. Cascading Revocations • How would “revokeselecton table1 from carol cascade” work in the previous example? • Only privileges granted solely through the revoked privileges will also be revoked. • If there exists a path in the graph from the grantor, then do not revoke. • Allows us to specify exceptions preemptively.

28. Disadvantages to SQL Model • Too many views to create • Tedious for many users, each with their own view • View redefinitions that change the view schema require dropping the view, redefining, then reissuing privileges • Fine-grained policies each require their own view— and no obvious way to see that the views come from the same table

29. Disadvantages (cont) • Complicated policy logic can be difficult to express and to update • Update anomalies • Updates need to be made in multiple places • If any steps are forgotten, the database is in an inconsistent state • e.g. Suppose we have an employees table, and all managers in this table get special update privileges.

30. Database Query Table policy function VPD function evaluator User name Other data App-defined context Rewritten query Virtual Private Databases • Security model for Oracle • Policies are user-defined functions that return a condition for an SQL where clause • Applications can also define a “context,” e.g. for role-based access control Oracle 05

31. Features • Functions are executed each time the table is accessed. • Multiple functions can be attached to a table. • Different functions can be defined depending on: • Operation (read vs. write) • Columns being accessed

32. Simple Policy • Two users, Alice and Bob • Alice creates a table: createtable test (a intprimarykey, b varchar2(50)); insertinto test values(1, 'hello'); insertinto test values(2, 'world'); commit; • Alice wants to limit Bob’s access to the row where a=1 • Three steps: • Grant Bob access to the table: grantselecton test to bob; • Create a policy function • Attach the policy function to the table

33. Simple Policy createorreplacefunction testFilter (p_schema varchar2, p_obj varchar2) returnvarchar2as begin if (SYS_CONTEXT('userenv', 'SESSION_USER') = 'BOB') then return'a = 1'; else return''; endif; end; /

34. Simple Policy execute dbms_rls.add_policy( object_schema => 'alice', object_name => 'test', policy_name => 'FilterForBob', function_schema => 'alice', policy_function => 'testFilter', statement_types => 'select, update, insert',update_check => true); • Query the table, once as Alice and again as Bob: select * from alice.test;

35. Logging Policy • Alice wants to log all accesses to her test table • Create a log table: createtable logtable ( when date, entry long);

36. Logging Policy createorreplacefunction testLogging(p_schema varchar2, p_obj varchar2) returnvarchar2as begin insertinto alice.logtable values( sysdate, SYS_CONTEXT('userenv', 'SESSION_USER') || ',' || SYS_CONTEXT('userenv', 'CURRENT_SQL')); commit; return''; end; /

37. Logging Policy execute dbms_rls.add_policy( object_schema => 'alice', object_name => 'test', policy_name => 'LogAccesses', function_schema => 'alice', policy_function => 'testLogging', statement_types => 'select, update, insert',update_check => true);

38. Logging Policy • Query the test table again, once as Alice and again as Bob: select * from alice.test; • Query the logtable table: select * from logtable; • Note that the original query is recorded, not the rewritten query. • Note also that Bob cannot query logtable.

39. Reflective Policy • Alice wants the permissions on each row in her test table to be defined in another table, userperms. • Create the table: createtable userperms (username varchar2(50), a intreferences test); • Note the foreign key reference • Not required, but it can help optimization

40. Reflective Policy • Populate the table: insertinto userperms values('BOB', 1); insertinto userperms values('ALICE', 1); insertinto userperms values('ALICE', 2); commit; • Note the capital letters in BOB and ALICE • SQL commands are case-insensitive • Table values are case-sensitive

41. Reflective Policy createorreplacefunction testFilter(p_schema varchar2, p_obj varchar2) returnvarchar2as begin return'a in (select a from alice.userperms ' || 'where username = ''' || SYS_CONTEXT('userenv', 'SESSION_USER') || ''')'; end; /

42. Reflective Policy • Query the test table, both as Alice and as Bob: select * from alice.test; • Have Alice allow additional access to Bob, and then have Bob query test again: insertinto userperms values('BOB', 2); commit;

43. Reflective Policy • Notes: • Currently, Alice cannot insert new rows into test (since userperms only gives Alice access to a=1 or a=2) • Alice cannot insert new rows into userperms (since the foreign key constraint requires the a value to exist in test) • Solutions: • Alice can exempt herself by writing the logic in the function • Alice can be exempted after adding the policy: grantexemptaccesspolicyto alice; • Must be granted from system administrator • Exempts from all access policies, cannot specify table-by-table basis

44. Key Points • Access control for databases requires scalability • SQL standard: • grant, revoke • with grant option • view-based access control • Oracle VPD policy functions

45. Software Versions • PostgreSQL • testbed version 8.2 • MySQL • testbed version 5.0 • Oracle • testbed version 10.2 • Only Enterprise Edition has VPD • Other standard SQL databases • Microsoft SQL Server, IBM DB2, Sybase, … • should all work, but we don’t have testbeds

46. Disclaimer!! • Do not use your powers for evil. • The purpose of showing these attacks is to teach you how to prevent them. • Established e-commerce sites are already hardened to this type of attack. • You might cause irreparable harm to a small “mom-and-pop” business. • Even if you don’t, breaking into someone else’s database is illegal and unethical.

47. Characterization of Attack • Not a weakness of SQL • ...at least in general • SQL Server may run with administrator privileges, and has commands for invoking shell commands • Not a weakness of database, PHP/scripting languages, or Apache • Building executable code using data from an untrusted user • Perl taint mode was created to solve a similar problem

48. Simple Attack Example • Logging in with: selectcount(*) from login where username = '$username' and password = '$password'; • Setting the password to “' or 'a' = 'a”: selectcount(*) from login where username = 'alice'and password = ''or'a' = 'a'; • In fact, username doesn’t even have to match anyone in the database

49. Detecting Vulnerability • Try single apostrophe • If quotes aren’t filtered, this should yield an error message • Error message may be useful to attackers • May reveal database vendor (important later on) • Try a comment character (double-hyphen in some databases, # symbol in others) • Only works for numeric fields, if quotes are filtered • Not as commonly filtered

50. Inferring Database Layout (1) • Guess at column names ' and email isnull-- ' and email_addr isnull-- • Use error messages (or lack of)