Database Reference
In-Depth Information
The aim in making this improvement is that database links represent a form of anonymized access that often
allows connections from low to high-security areas. In some environments such as cash processing, database links
are completely banned. Banning database links is a straightforward policy to state, but unfortunately Oracle does not
have a feature that enables you to enforce that ban. If you have the username and password for a normal database
account you can connect to it from a DB Link originating from another DB under your control.
However, from 11.2.0.3 upwards and including 12c, there is a
DBLINK_INFO
field that can be used to identify a
DB Link session from its audit trail. Of course, the audit trail is a passive record, not an active part of the security
protection process. Unfortunately database triggers are not so useful here, as a trigger cannot kill its own session. So
we need Unix to read the DB audit trail for the
DBLINK_INFO
field and then kill that remote session automatically.
I have coded such a solution using bash and found it to be a reliable database link blocker.
I've written a shell script as a simple PoC example that will take a
tail
of Oracle syslog and grep it for an
incoming DB Link. The key is to use the capital F of
tail -F
, as it will keep going when the inode fills up, which
therefore enables
daemon mode.
When an incoming DB Link is detected, the script piped from
tail
will then lock
the DB Link account and kill the session automatically, and then stay in daemon mode to persist this protection
indefinitely. I have used this script reliably for weeks without interruption.
I'll show the script in a moment. First though, here is a demonstration of the
-F
capability.
$ tail -F /var/log/oracle.log | grep 'DBLINK_INFO'
Sep 4 15:38:13 orlin Oracle Audit[23332]: LENGTH: "458" SESSIONID:[6] "205917" ENTRYID:[2] "28"
STATEMENT:[2] "13" USERID:[14] "DBLINK_ACCOUNT" USERHOST:[13] "SHOPBUILD6621" TERMINAL:[13]
"SHOPBUILD6621" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[9] "ALL_USERS"
COMMENT$TEXT:[159] "DBLINK_INFO: (SOURCE_GLOBAL_NAME=orcl4.enterprise.internal.city.ac.uk, DBLINK_
NAME=TEST_LINK.ENTERPRISE.INTERNAL.CITY.AC.UK, SOURCE_AUDIT_SESSIONID=4294967295)" OS$USERID:[6]
"SYSTEM" DBID:[10] "2267081778"
tail: '/var/log/oracle.log' has become inaccessible: No such file or directory
tail: `/var/log/oracle.log' has appeared; following end of new file
Sep 4 16:18:53 orlin Oracle Audit[23332]: LENGTH: "454" SESSIONID:[6] "205917" ENTRYID:[2] "29"
STATEMENT:[2] "16" USERID:[14] "DBLINK_ACCOUNT" USERHOST:[13] "SHOPBUILD6621" TERMINAL:[13]
"SHOPBUILD6621" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$NAME:[5] "USER$"
COMMENT$TEXT:[159] "DBLINK_INFO: (SOURCE_GLOBAL_NAME=orcl4.enterprise.internal.city.ac.uk, DBLINK_
NAME=TEST_LINK.ENTERPRISE.INTERNAL.CITY.AC.UK, SOURCE_AUDIT_SESSIONID=4294967295)" OS$USERID:[6]
"SYSTEM" DBID:[10] "2267081778"
Following is the working shell script, which receives piped input from the above
tail -F
command.
#!/bin/bash
#input
while read line; do
read inpvar
#print the sessionid and remove double quotes
myvar='echo $inpvar | awk '{print $10}'|sed s/\"//g'
echo $(date) 'Oracle sessionid ' $myvar ' is a dblink which will be locked and session killed! ' >>
dblinkblocker.log
#lock the account before killing it.
out='sqlplus -s "sys/a@192.168.1.3/pdborcl as sysdba" <<EOF
set heading off feedback off verify off
select username from v\\$session where audsid='$myvar';
exit
EOF
'
