Database Reference
In-Depth Information
outp='sqlplus -s "sys/a@192.168.1.3/pdborcl as sysdba" <<EOF
set heading off feedback off verify off
alter user $out account lock;
exit
EOF
'
#select out the sid and serial for that sessionid to enable a kill
output='sqlplus -s "sys/a@192.168.1.3/pdborcl as sysdba" <<EOF
set heading off feedback off verify off
select sid, serial# from v\\$session where audsid=$myvar;
exit
EOF
'
echo 'rawid'$output >> dblinkblocker.log
#replace space with comma
nvar='echo $output | sed s/\,//g'
echo 'without commas'$nvar >> dblinkblocker.log
#remove leading space
#pvar='echo $nvar | sed -r 's/^.{1}//''
echo 'processed id' $nvar >> dblinkblocker.log
#replace space in the middle with a comma
ovar='echo $nvar | sed -e 's/ /\,/g''
echo 'moreprocesed id' $ovar >> dblinkblocker.log
#kill the session from inside the db.
outputb='sqlplus -s "sys/a@192.168.1.3/pdborcl as sysdba" <<EOF
set heading off feedback off verify off
alter system kill session '$ovar' immediate;
exit
EOF
'
echo '$outputb' >> dblinkblocker.log
echo 'Oracle sessionid ' $myvar ' is an incoming dblink and has been locked and session killed! '
echo '' >> dblinkblocker.log
done
Putting it all together, we are tailing local Oracle syslog from the local OS of the DB Server. For example:
[oracle@orlin dblinkblocker]$ tail -F /var/log/oracle.log | grep 'DBLINK_INFO' | /home/oracle/
shell/dblinkblocker/j.sh
Oracle sessionid 208769 is an incoming DBlink and has been locked and session killed!
And the following is what we see from the client DB that is initiating the database link. The first SQL statement
causes the DB Link entry in the audit trail, and then the session is killed, thus disabling any further statements.
select username from all_users@TEST_LINK
