Database Security_Access Control and SQL Injection.ppt

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

2、,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

3、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” Wikipedi

4、a 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 Injecti

5、on 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 contr

6、ol 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,Targets for Attack,Database applications often need to serve multiple users Programmers often give their applications elevated privileges,7,Q

7、uick SQL Overview (1),Creating tables: create table table_name ( column1 type1, column2 type2, . ); Deleting tables: drop table 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 charac

8、ter 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: insert into table_nam

9、e values (value1, value2); or insert into table_name set column1=value1, column2=value2, .; Updating rows: update table_name set column1=value1 where condition;,11,Quick SQL Overview (5),Deleting rows: delete from table_name where condition; Set values in conditions: select * from table_name where c

10、olumn 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 select count(*) from test;,13,Quick SQL Overview (7),Pattern Matching s

11、elect * from test where a like %c_t%; Other Keywords select * from test where a is null; 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

12、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 (s

13、sn, 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 doesnt need to make changes): grant select on employee to bob; Suppose Carol is another employee, who should only access public information: grant

14、 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 cre

15、ated as a “shortcut” query of other tables View syntax: create view 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: create view employee_public as select name

16、,email from employee; grant select on employee_public to carol;,19,Row-Level Access Control,Suppose we also allow employees to view their own ssn, salary: create view employee_Carol as select * from employee where name=Carol; grant select on employee_Carol to carol; And we allow them to update their

17、 e-mail addresses: grant update(email) on employee_Carol to carol; (Or create yet another new view),20,Delegating Policy Authority,grant privilege_list on resource to user_list with grant option; Allows other users to grant privileges, including “with grant option” privileges Can grant subset privil

18、eges too Alice: grant select on table1 to bob with grant option; Bob: grant select(column1) on table1 to carol with grant option;,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 hap

19、pens 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

20、) grant(Carol,Bob) revoke(Alice,Bob),Alice,Bob,Carol,grant(Alice,Bob) grant(Alice,Carol) grant(Carol,Bob) revoke(Alice,Bob),24,Not as Easy as it Looks!,grant(Alice,Bob) grant(Bob,Carol) grant(Carol,Bob) revoke(Alice,Bob),Alice,Bob,Carol,grant(Alice,Bob) grant(Bob,Carol) grant(Carol,Bob) revoke(Alice

21、,Bob),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,Bob,Carol,David,?,26,Meanwhile, in the Real World.,Account privileges get chang

22、ed all the time We dont 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 wou

23、ld “revoke select on 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 t

24、o 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 t

25、he 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

26、table get special update privileges.,30,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 ea

27、ch 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: create table test (a int primary key, b varchar2(50); inse

28、rt into test values(1, hello); insert into test values(2, world); commit; Alice wants to limit Bobs access to the row where a=1 Three steps: Grant Bob access to the table: grant select on test to bob; Create a policy function Attach the policy function to the table,33,Simple Policy,create or replace

29、 function testFilter (p_schema varchar2, p_obj varchar2) return varchar2 as begin if (SYS_CONTEXT(userenv, SESSION_USER) = BOB) then return a = 1; else return ; end if; end; /,34,Simple Policy,execute dbms_rls.add_policy( object_schema = alice, object_name = test, policy_name = FilterForBob, functio

30、n_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: create table logtable ( w

31、hen date, entry long);,36,Logging Policy,create or replace function testLogging(p_schema varchar2, p_obj varchar2) return varchar2 as begin insert into alice.logtable values( sysdate, SYS_CONTEXT(userenv, SESSION_USER) | , | SYS_CONTEXT(userenv, CURRENT_SQL); commit; return ; end; /,37,Logging Polic

32、y,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: s

33、elect * 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, userp

34、erms. Create the table: create table userperms (username varchar2(50), a int references test); Note the foreign key reference Not required, but it can help optimization,40,Reflective Policy,Populate the table: insert into userperms values(BOB, 1); insert into userperms values(ALICE, 1); insert into

35、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,create or replace function testFilter(p_schema varchar2, p_obj varchar2) return varchar2 as begin return a in (select a from alice.user

36、perms | 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: insert into userperms values(BOB, 2); commit;,43,Ref

37、lective 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 logi

38、c in the function Alice can be exempted after adding the policy: grant exempt access policy to 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, r

39、evoke 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,

40、 but we dont 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 y

41、ou dont, breaking into someone elses 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 Apac

42、he 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: select count(*) from login where username = $username and password = $password; Setting the password to “ or a = a”: select count(*) from log

43、in where username = alice and password = or a = a; In fact, username doesnt even have to match anyone in the database,49,Detecting Vulnerability,Try single apostrophe If quotes arent filtered, this should yield an error message Error message may be useful to attackers May reveal database vendor (imp

44、ortant 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 is null- and email_addr is null- Use error messages (or lack

45、of),51,Inferring Database Layout (2),Guess at table name and users.email_addr is null- and login.email_addr is null- Can be done with an automated dictionary attack Might discover more than one table in the query Guess at other table names and 1=(select count(*) from test)-,52,Discovering Table Data

46、,Depends on query structure, output format May be directed at a particular user or account (e.g. root) or username like %admin%- May include brute-force password attacks,53,Query Stacking (1),Use semicolon as command separator Useful output is limited by application My main example doesnt output any

47、thing from the database. Try the queries on a login page that displays a query result. 1; select * from test- Doesnt display the entire table? Try modifying the query: 1; select b from test- 1; select a from test where a not in (1)-,54,Query Stacking (2),Displaying database structure Highly vendor-s

48、pecific 1; select relname from pg_class- Output displays only one result? Use repeated application 1; select relname from pg_class where relname not in (views)-,55,Query Stacking (3),Displaying database structure (cont) Table structure: vendor-specific, use repeated application if needed 1; select a

49、ttname from pg_class, pg_attribute where pg_class.relname = login and pg_class.oid = pg_attribute.attrelid-,56,Query Stacking (4),Modifying the database ; insert into login values(100, attacker, attackerpw, 2222, )- ; update login set password=newpw where username like %admin%-,57,Second-Order SQL Injection,Inserting text fields that will pass initial validation, but could