Database System Security

Database SystemSecurityPaul J. WagnerUMSSIA 2008

Need for Database SystemSecurity Education“The value is in the data ” – 3M PosterAttacks have changed from glory-seekingto attempted financial gainSecurity curriculum is relatively light indatabase systems area!Focus currently on protecting informationthrough network configuration, systemsadministration, application security

GoalsUnderstand security issues in a generaldatabase system environment, withexamples from specific databasemanagement systems (DBMSs)Consider database security issues incontext of general security principles andideasExamine issues relating to both databasestorage and database systemcommunication with other applications

Main MessageDatabase system security is more thansecuring the database; to achieve asecure database system, we need a:!!!!!!Secure databaseSecure DBMSSecure applications / application developmentSecure operating system in relation todatabase systemSecure web server in relation to databasesystemSecure network environment in relation todatabase system

diagram from .NET Framework Security article, Meier et. al.

Secure DatabasesDatabase – a domain-specific collectionof data; e.g. UWEC student databaseHistorical database security framework!!Separate set of usernames, passwordsDatabase administrator assigns privilegesfor each user

Secure Databases (2)Security issues!Default users/passwords, especially if havehigher-level privilegesOracle: sys, system accounts – privileged(Oracle 8i and prior - with default passwords)Oracle: scott account – well-known account andpassword, part of public group!!e.g. public can access all users tableGeneral password policies (length, domain,changing, protection)

Secure Databases (3)Managing Privileges!Privilege typesSystem - actionsObject – data!!Problem: M (users) x N (privileges) possiblecombinationsSolution: Roles (a security pattern)Collections of (usually system) privileges!Manage with Grant / Revoke, higher level toolsGiving (or removing ) privileges or roles to (from)users

Secure Databases (4)Encryption of Data!!!Sensitive data should be encrypted withindatabaseMost DBMSs now have utilities for thisExamine cost of this processMay slow access time, use resourcesMay interfere with system administration!Don’t forget about data once out of the databaseNeed to decrypt for usersEnsure data is still protected when DB sends it to aclient for usage, or when client sends to DB

Secure Databases (5)Other Database Issues – Inference Attack!Statistical Database – database used forstatistical purposes, usually Online AnalyticalProcessing (OLAP) as in data warehousesitituationsGenerally allows only aggregate function queries(may be limited to SUM, COUNT, AVERAGE) on agroup of rowsMay not allow queries on individual rows!Problem: it may still be possible to inferindividual data values from such a database

Secure Databases (6)!Inference Attack Example:Base query: find count of all UWEC studentsExample SQL:!SELECT Count(*) FROM StudentDB;returns single number, 10,000doesn’t expose any individual information, but wecan do more

Secure Databases (7)!Inference Attack Example (2):Find count of UWEC students that:!!!Have a local address in Eau ClaireHave a GPA of between 3.1 and 3.2Are majoring in computer scienceSELECT Count(*) FROM StudentDB WHEREcond1 AND cond2 AND cond3 AND Getting count down to fewer and fewer studentsas we add filter conditionsIf count gets down to 1, you know an individualexists with those characteristics

Secure Databases (8)!May even be able to gain private informationFind average hourly pay rate for UWEC studentsthat have the above characteristics!Inference Attack Prevention Techniques:Return an approximate number of rows (e.g.between A and B)Limit the number of filtering conditions on a queryReturn ranges for values, especially for sensitivefields (e.g. salary falls between X and Y)!Difficult to balance functionality and securitywith such data

Secure Databases (9)Other Database Issues - Correlation Attack!!Data in database may be anonymousHowever, it may be able to be correlated withanother set of data which is not anonymousAnonymity can thus be removed!Example: Statistical analysis of public butanonymized Netflix data set using InternetMovie Database (imdb.com) dataBy comparing rankings and timestamps from Netflixdata, authors identified a subset of individualsthrough correlated data from IMDB

Secure Databases (10)More Information on this 2/anonymity and t 2.html - Discussion of problem ingeneral, this analysis, other similar effortshttp://www.cs.utexas.edu/ shmat/shmat netflixprelim.pdf - Paper on the original analysis

Secure DBMSDatabase Management System (DBMS) – the domainindependent set of software used to manage and accessyour database(s)Many Possible Holes in DBMS ty/alerts.htm (50 listed)Majority of problems - buffer overflow problems in (legacy) DBMScodeMiscellaneous attacks (Denial of Service, source code disclosureof stored procedures and JSPs, others)DBMS is not an island!Oracle example - UTL FILE package in PL/SQL (Oracle’sprocedural language on top of SQL)allows read/write access to files in directory/ies specified inutl file dir parameter in init.orapossible access through symbolic links

Secure DBMS (2)Need for continual patching of DBMS!!Encourage awareness of issues, continuousvigilance as a database administratorCost of not patchingSQL Slammer Worm - 100,000 systems affected!Even though patch had been available for 6 months

Secure DBMS (3)US-CERTadvisories!List of e

Secure ApplicationDevelopmentAccess to Oracle Database orEnvironment Through ApplicationsNeed to consider security of applicationsusing database as well as security of datain database itselfExample: SQL Injection Attack

SQL InjectionSQL Injection!Definition – inserting malicious SQL code through anapplication interfaceOften through web application, but possible with anyinterface!Typical scenarioThree-tier application (web interface, application, database)Overall application tracks own usernames and passwords indatabase (advantage: can manage users in real time)Web interface accepts username and password, passesthese to application layer as parameters

SQL Injection (2)!Example: Application Java code (receivingusername and password from client) containsSQL statement:String query "SELECT * FROM users table " " WHERE username " " ‘ " username " ‘ " " AND password " " ‘ " password " ‘ "Expected Result: "SELECT * FROM users tableWHERE username ‘wagnerpj‘AND password ‘paulpass‘;Note: String values must be single quoted in SQL, soapplication provides these before and after eachpassed string parameter

SQL Injection (3)!!Application is expecting one username/passwordpair row to be returned if success, no rows iffailureCommon variant to simplify the above check:SELECT COUNT(*) FROM users tableWHERE username ‘someuser‘AND password ‘somepass‘;

SQL Injection (4)!Attacker enters:any username (valid or invalid)password of: Aa‘ OR ‘ ‘ ‘!!Query becomes: SELECT * FROM users tableWHERE username ‘anyname‘ AND password ‘Aa‘ OR ‘ ‘ ‘ ‘;Note: WHERE clause ? and F or T F or T TAND has higher precedence than OR!!All user/pass rows returned to applicationIf application checking for 0 vs. more than 0rows, attacker is in

SQL Injection - PreventionWhat’s the problem here?!Not checking and controlling input properlySpecifically, not controlling string input!Note: there are a variety of ways SQLinjection can happenRegular inclusion of SQL metacharacters through!!!!Variable interpolationString concatenation with variables and/or constantsString format functions like sprintf()String templating with variable replacementHex or Unicode encoded metacharacters

SQL Injection Prevention (2)How to resolve this?!First Possible Solution: Check ContentClient code checks to ensure certain content rulesare metServer code checks content as well (why?)Specifically – don’t allow apostrophes to be passedProblem: there are other characters that can causeproblems; e.g.!!!-;%// SQL comment character// SQL command separator// SQL LIKE subclause wildcard characterWhich characters do you filter (blacklist) / keep(whitelist)?!Question: is it better to blacklist or whitelist?

SQL Injection – Variant 1Any username, password: ‘ or 1 1-!Note: -- comments out rest of line, includingterminating single quote in applicationQuery becomes: SELECT * FROMusers table WHERE username ‘anyname‘AND password ‘ ‘ OR 1 1--‘;Note: “OR 1 1” can be appended to anynumeric input and force evaluation to true!Numeric SQL injection vs. String SQL injection

SQL Injection – Variant 2Any username, password: foo’;DELETE FROMusers table WHERE username LIKE ‘%Query becomes: SELECT * FROM users tableWHERE username ‘anyname‘ AND password ‘foo‘; DELETE FROM users table WHEREusername LIKE ‘%’Note: system executes two statements!!SELECT * FROM users table WHERE username ‘anyname’ AND password ‘foo’; // returns nothingDELETE FROM users table WHERE username LIKE ‘%’Depending on level of privilege for executing user, we may havetrouble here

SQL Injection – Variant 3ODBC (open database connectivity, betweenany language and any DBMS) allowed shellinjection using ‘ ’ character!‘ shell(“cmd /c echo “ & char(124) & “format c:”) ’Similar issue has existed with MS SQL ServerExtended Stored Procedures

SQL Injection – Variant 4Second-Order SQL Injection!User creates account with user root’-Application escapes and inserts as root’’--!User resets password using script/applicationQuery fetches username from database to verifyaccount exists with correct old password; thenexecutes:UPDATE users table SET PASSWORD ‘pass’WHERE username ‘root’--’!NOTE: above scenario allows user to reset thepassword on the real root account

SQL Injection – Variant 5PL/SQL Cursor InjectionStructure!!!PL/SQL is procedural language in Oracle built ontop of SQLFunctions and procedures can be defined whichset up cursors that allow execution of dynamicallydefined SQL statementsInjection techniques can be used with thesestructures as njection.pdf

SQL Injection – Variant 6Assume web page that gets press releases from db!http://www.company.com/pressRelease.jsp?id 5Generates:SELECT title, description, releaseDate, bodyFROM pressReleases WHERE id 5What if send:!!!http://www.company.com/pressRelease.jsp?id 5 AND 1 1If get press release 5 back, there is a vulnerabilityNow can send:!!http://www.company.com/pressRelease.jsp?id 5 ANDuser name() ‘dbo’If get p.r. 5, know that you’re running as user dbofrom HP white paper on Blind SQLInjection

SQL Injection – InterludeMany possible variations of SQL InjectionMany potential metacharacters to filter!Dependent on DBMS varietyMaybe filtering input by blacklistingmetacharacters isn’t the best approach What is the core problem here?!How is SQL injection similar to a bufferoverflow?

SQL Injection – Prevention (3)Review!Regular StatementsSQL query (a string) is generated entirely at run-timeCustom procedure and data are compiled and runCompilation allows combination of procedure anddata, allowing problems with SQL metacharactersString sqlQuery null;Statement stmt null;sqlQuery "select * from users where " "username " "'" fe.getUsername() "'" " and " "upassword " "'" fe.getPassword() "'";stmt conn.createStatement();rset stmt.executeQuery(sqlQuery);

SQL Injection – Prevention(4)Better Solution!Prepared Statements (Parameterized Queries)SQL query is precompiled with placeholdersData is added in at run-time, converted to correct type for thegiven fieldsString sqlQuery null;PreparedStatement pStmt null;sqlQuery "select * from users where username ? andupassword ?";pStmt conn.prepareStatement(sqlQuery);pStmt.setString(1, fe.getUsername());pStmt.setString(2, fe.getPassword());rset pStmt.executeQuery();