Skip to content

CVE-2012-1675 tns poison fix for oracle (rac) >=11.2.0.4

CVE-2012-1675 tns poison fix for oracle (rac) >=11.2.0.4 published on 1 комментарий к записи CVE-2012-1675 tns poison fix for oracle (rac) >=11.2.0.4

for check i use nmap and
Nmap script to test Oracle DB for «TNS poison vulnerability»

oracle-tns-poison.nse

local bin = require "bin"
local io = require "io"
local nmap = require "nmap"
local shortport = require "shortport"
local stdnse = require "stdnse"
local table = require "table"

description = [[
Simple module to test Oracle DB server for TNS Poison vulnerability.
Module sends to server a packet with command to register new TNS Listener and check response
To more details about this bug see http://seclists.org/fulldisclosure/2012/Apr/204
]]

--
-- @usage
-- nmap --script=oracle-tns-poison -p 1521 <host>
-- 
-- @output
-- PORT     STATE SERVICE REASON
-- 1521/tcp open  oracle  syn-ack
-- | oracle-tns-poison: Host is vulnerable!
--
--
-- This module is based on sid-brute script. Thanks to author: Patrik Karlsson.
--

author = "Ivan Chalykin"
license = "Same as Nmap--See http://nmap.org/book/man-legal.html"
categories = {"vuln"}

portrule = shortport.port_or_service(1521, 'oracle-tns')

local tns_type = {CONNECT=1, REFUSE=4, REDIRECT=5, RESEND=11}

local function create_tns_header(packetType, packetLength)

  local request = bin.pack( ">SSCCS",
    packetLength + 34, -- Packet Length
    0, -- Packet Checksum
    tns_type[packetType], -- Packet Type
    0, -- Reserved Byte
    0 -- Header Checksum
    )

  return request

end

local function create_connect_packet()

  local connect_data =  "(CONNECT_DATA=(COMMAND=service_register_NSGR))"

  local data = bin.pack(">SSSSSSSSSSICCA",
    308, -- Version
    300, -- Version (Compatibility)
    0, -- Service Options
    2048, -- Session Data Unit Size
    32767, -- Maximum Transmission Data Unit Size
    20376, -- NT Protocol Characteristics
    0, -- Line Turnaround Value
    1, -- Value of 1 in Hardware
    connect_data:len(), -- Length of connect data
    34, -- Offset to connect data
    0, -- Maximum Receivable Connect Data
    1, -- Connect Flags 0
    1, -- Connect Flags 1
    connect_data
    )


  local header = create_tns_header("CONNECT", connect_data:len() )

  return header .. data

end

action = function(host, port)

  local socket = nmap.new_socket()
  local catch = function() socket:close() end
  local try = nmap.new_try(catch)
  local request, response, tns_packet
  local indicator

  socket:set_timeout(2000)

      try(socket:connect(host, port))
      request = create_connect_packet( host.ip, port.number)
      try(socket:send(request))
      response = try(socket:receive_bytes(1))
 
      if response:match("ERROR_STACK") then 
        indicator="Not Vulnerable"
        else indicator="Host is vulnerable!"
        end
 
      return indicator
end

[свернуть]

check :

nmap --script=oracle-tns-poison.nse -p 1521 oel6-2

Starting Nmap 6.47 ( http://nmap.org ) at 2016-02-29 10:54 MSK
Nmap scan report for oel6-2 (10.0.0.62)
Host is up (0.00018s latency).
rDNS record for 10.0.0.62: oel6-2.djeday.lan
PORT     STATE SERVICE
1521/tcp open  oracle
|_oracle-tns-poison: Host is vulnerable!

Nmap done: 1 IP address (1 host up) scanned in 0.04 seconds

solution is described by MOS: Valid Node Checking For Registration (VNCR) (Doc ID 1600630.1)
How to Enable VNCR on RAC Database to Register only Local Instances (Doc ID 1914282.1)
fixup:
add to grid listener.ora

[grid@oel6-2 admin]$ cat listener.ora
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))		# line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))		# line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))		# line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON		# line added by Agent

VALID_NODE_CHECKING_REGISTRATION_LISTENER=1

VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=1
REGISTRATION_INVITED_NODES_LISTENER_SCAN1=(oel6-1.djeday.lan,oel6-2.djeday.lan,oel6-3.djeday.lan)
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=1
REGISTRATION_INVITED_NODES_LISTENER_SCAN1=(oel6-1.djeday.lan,oel6-2.djeday.lan,oel6-3.djeday.lan)
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=1
REGISTRATION_INVITED_NODES_LISTENER_SCAN1=(oel6-1.djeday.lan,oel6-2.djeday.lan,oel6-3.djeday.lan)
  • REGISTRATION_INVITED_NODES_LISTENER_SCAN*=( list of public ip’s of all nodes)
  • reload listener conf

    [grid@oel6-2 admin]$ lsnrctl reload
    

    check one more time:

     nmap --script=oracle-tns-poison.nse -p 1521 oel6-2
    
    Starting Nmap 6.47 ( http://nmap.org ) at 2016-02-29 10:55 MSK
    Nmap scan report for oel6-2 (10.0.0.62)
    Host is up (0.00021s latency).
    rDNS record for 10.0.0.62: oel6-2.djeday.lan
    PORT     STATE SERVICE
    1521/tcp open  oracle
    |_oracle-tns-poison: Not Vulnerable
    
    Nmap done: 1 IP address (1 host up) scanned in 0.04 seconds
    

    Job done =)

    quick way to update column online with no locks

    quick way to update column online with no locks published on Комментариев к записи quick way to update column online with no locks нет

    we can update whole column of big table with no downtime with dbms_redefinition and col_mapping
    there is an example:
    find table with lots of rows for test and make a copy:

    create table my_table nologging as select * from big_table;

    create interim table for redefinition and add column to remap data need to be updated, also set default value for column we need to update:

    create table my_table_redef as select * from my_table where 1=2;
    
    alter table my_table_redef add ( CUSTOMDESCRIPTION_TMP varchar2(255));
    alter table my_table_redef MODIFY (CUSTOMDESCRIPTION default 'A');
    
    select count (*) from my_table; 40947444
    

    do redefinition with remap of CUSTOMDESCRIPTION to CUSTOMDESCRIPTION_TMP:

    EXEC DBMS_REDEFINITION.can_redef_table(USER, 'MY_TABLE');
    
    
    begin
    DBMS_REDEFINITION.start_redef_table(
         uname        => USER, 
         orig_table   => 'MY_TABLE',
         int_table    => 'MY_TABLE_REDEF',
         col_mapping =>'ID ID,FIELDNAME FIELDNAME,NEWVALUE NEWVALUE,OLDVALUE OLDVALUE,HISTORY_ID HISTORY_ID,CUSTOMDESCRIPTION CUSTOMDESCRIPTION_TMP, INTERNALVALUE INTERNALVALUE',
         options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
    end;   
    
    EXEC DBMS_REDEFINITION.sync_interim_table(USER, 'MY_TABLE', 'MY_TABLE_REDEF'); 
    
    EXEC DBMS_REDEFINITION.finish_redef_table(USER, 'MY_TABLE', 'MY_TABLE_REDEF');  
    

    drop old column:

    alter table my_table_redef drop column CUSTOMDESCRIPTION_TMP;

    check:

    select count (*),CUSTOMDESCRIPTION from MY_TABLE group by CUSTOMDESCRIPTION;
    40947444	A
    

    and dont forget to reset default value

    alter table MY_TABLE modify CUSTOMDESCRIPTION default null;
    

    segment grown trend

    segment grown trend published on Комментариев к записи segment grown trend нет
    column owner format a10
    column object_name format a30
    column "tablespace name" format a15
    select s.s_id ,s.e_id,o.owner , o.object_name , o.subobject_name , o.object_type , 
    t.name "tablespace name", s.growth/(1024*1024) "growth in mb", 
    (select sum(bytes)/(1024*1024) 
       from dba_segments 
       where segment_name=o.object_name
    ) "total size(mb)" 
    from dba_objects o 
        join 
        (select min(ss.begin_interval_time) as s_id,max(ss.begin_interval_time) as e_id,ts#,obj#, sum(space_used_delta) growth 
          from dba_hist_seg_stat st 
            join dba_hist_snapshot ss on st.snap_id=ss.snap_id
          group by ts#,obj# 
          having sum(space_used_delta) > 0 
          order by 2 desc
        ) s on s.obj#=o.object_id 
        join v$tablespace t on s.ts#=t.ts# 
    where rownum < 11 
    order by 6 desc;
    

    found at community.oracle.com

    Calculating TPS from ASH via SQL_EXEC_ID or from DBA_HIST_SQLSTAT

    Calculating TPS from ASH via SQL_EXEC_ID or from DBA_HIST_SQLSTAT published on 1 комментарий к записи Calculating TPS from ASH via SQL_EXEC_ID or from DBA_HIST_SQLSTAT

    save to my blog ( original )

    to calculate tps by sql_id we can use sql_exec_id , ash and exel

    SELECT TO_CHAR (sample_time, 'HH24:MI'),inst_id,
           MAX (sql_exec_id) - MIN (sql_exec_id) EXECUTIONS_PER_MINUTE
      FROM gv$active_Session_history
     WHERE    sql_id = 'b6asbgkghps5h' and sample_time > sysdate-5/24
    group by TO_CHAR (sample_time, 'HH24:MI'),inst_id
    order by 1 asc;
    

    Screen Shot 2016-02-08 at 15.50.27

    or to split results it frame by 10 minutes:

    select date#,"'1'" as first_node,"'2'" as second_node from (
    SELECT TRUNC(sample_time, 'MI') - MOD(TO_CHAR(sample_time, 'MI'), 10) / (24 * 60) as date#,instance_number,
           MAX (sql_exec_id) - MIN (sql_exec_id) EXECUTIONS_PER_10_MINUTE
      FROM gv$active_Session_history
     WHERE    sql_id = '77qx41mkwcm92' 
    group by TRUNC(sample_time, 'MI') - MOD(TO_CHAR(sample_time, 'MI'), 10) / (24 * 60),instance_number
    order by 1 asc )
    pivot 
    (
       sum(EXECUTIONS_PER_10_MINUTE)
       for instance_number in ('1'  ,'2' )
    ) order by date# asc;
    

    one more type of view

    select * from (
    select s.INSTANCE_NUMBER,s.sql_id,
    sum( nvl(s.executions_delta,0)) execs,TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24') date#
    -- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
    from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS, dba_hist_sqltext st
    where ss.snap_id = S.snap_id
    and ss.instance_number = S.instance_number
    and executions_delta > 0
    and elapsed_time_delta > 0
    and st.sql_id=s.sql_id
    and s.sql_id='8xjwqbfwwppuf'
    -- and st.sql_text not like '/* SQL Analyze%'
    --and s.sql_id in ( select p.sql_id from dba_hist_sql_plan p where p.object_name=’OPN_HIS’)
    and ss.begin_interval_time > sysdate-14
    group by TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24'),s.sql_id,s.INSTANCE_NUMBER )
    pivot ( sum(execs) for instance_number in (1,2 )
    ) order by 1;
    
    

    and result

    or even that way

    select * from (
    select s.sql_id,
    sum( nvl(s.executions_delta,0)) execs,TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24') date#
    -- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
    from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS, dba_hist_sqltext st
    where ss.snap_id = S.snap_id
    and ss.instance_number = S.instance_number
    and executions_delta > 0
    and elapsed_time_delta > 0
    and st.sql_id=s.sql_id
    -- and st.sql_text not like '/* SQL Analyze%'-- and s.sql_id in ( select p.sql_id from dba_hist_sql_plan p where p.object_name='OPN_HIS')
    and ss.begin_interval_time > sysdate-7
    group by TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24'),s.sql_id )
    pivot ( sum(execs) for sql_id in (
    '8xjwqbfwwppuf' ,'14crnjtpxh9aa')
    ) order by 1;
    
    

    Primary Sidebar

    Яндекс.Метрика