The One ‘Security’ Feature in Oracle You Probably Shouldn’t Use

Security is on everyone’s mind, especially database administrators, and some DBMS vendors supply tools and/or settings, which allow the database to monitor and report possibly suspicious activity. Oracle is no different, yet one setting can be particularly distressing by creating Denial of Service (DoS) attacks from within. Let’s look at all of the supplied security settings and see why that parameter can disrupt business if it’s set ‘incorrectly’.

Oracle provides several security parameters that can be set in versions from 11.1 onward; those settings are:


sec_case_sensitive_logon
sec_max_failed_login_attempts
sec_protocol_error_further_action
sec_protocol_error_trace_action
sec_return_server_release_banner

The first in the list, sec_case_sensitive_login, is deprecated in 12c, as case-sensitive logons are enabled by default. The second is an additional attempt to limit failed login attempts, adding to the profile limit FAILED_LOGIN_ATTEMPTS (which may or may not be set to a value other than the default for some profiles). The init parameter takes precedence over the profile setting so if sec_max_failed_login_attempts is set to 5, for example, every user account will be limited to 5 failed login attempts before it is locked, regardless of the FAILED_LOGIN_ATTEMPTS setting in the granted profile.

The next two are intended to thwart DoS attacks by specifying how to proceed after receiving bad packets and what tracing action to take, usually (but not always) from malicious clients. The settings for sec_protocol_error_further_action are:

CONTINUE (the default):   this continues the server process execution by accepting the packets sent from the (possibly) 
                          suspect client.

(DELAY, <integer>): this implements a delay of <integer> seconds before the next packet from the same client
                          connection is accepted.  For malicious connections this limits the consumption of resources, yet
                          for valid, trusted connections it causes a delay in processing which degrades performance.

(DROP, <integer>):  causing the server to forcibly terminate the client connection after <integer> bad packets have
                          been received.  When network issues interfere with valid clients this can cause loss of transactions. 
                          The client can reconnect and try the transactions again.

Allowing server processes to continue does provide for better performance at the expense of allowing malicious clients to continue sending suspect/bad packets. For those with a database server on a network accessible to ‘outsiders’ this can be a security risk that isn’t worth taking. Fortunately I have not personally seen any database server that isn’t behind a firewall (or two or three) and not on a private network inaccessible to the public directly.

We come to the setting that can create problems when none actually exist; sec_protocol_error_trace_action. There are four possible settings which can be used:

NONE:	Bad packets are ignored and no trace or log files are generated.

TRACE:	A detailed trace file is generated when suspect (bad) packets are received.  Although such trace files can
	be used to debug problems in client/server communication and can detail where bad packets originate it's
	also possible that left unchecked this setting can consume disk space at an alarming rate, stopping database
	processing due to 'No space left on device' errors.

LOG:	Minimal log messages are printed in the alert log and the server trace files, considerably reducing the disk
	space consumed.

ALERT:	Alert messages are written to the alert log.

When network problems, which normally don’t interfere with transaction processing present themselves, a large number of trace files can be generated when this parameter is set to the default value of TRACE. I have seen up to 28 trace files generated in a five-minute span, which would result in 336 trace files per hour. Many times these trace files are small; over time if this trace file generation is left unchecked/unmonitored millions of these trace files can result, filling a file system to capacity and possibly halting database processing and killing processes and OEM agents. Setting this parameter to LOG can greatly reduce the disk space consumed. Using ALERT also reduces the disk space usage but requires an ‘alert log scraper’ to find the messages and notify the DBAs. Using NONE is not recommended.

It would be a good idea to regularly check various file systems (notably the one where the alert log resides) for available space; network problems can occur without warning and databases that once were not generating trace data may now be filling directories with essentially useless files. The trace files I’ve read that are generated in this manner don’t provide really useful information:


2016-02-11 11:55:12.751634 : nagbltrm:entry
2016-02-11 11:55:12.833433 : nau_gtm:entry
2016-02-11 11:55:12.833462 : nau_gtm:exit
2016-02-11 11:55:12.833469 : nagbltrm:exit
2016-02-11 11:55:12.833482 : nnfgdei:entry

The first line in such trace files provides the trace file name, including the full path. The next lines, shown above, report the date/time and the module being called and whether it’s an entry or exit call. No IP address, no information that could be used to actually trace such connections. No entries exist in the alert log when TRACE is the setting for this parameter, making it difficult to know where the suspect packets originate or why they appear. One piece of information Oracle does provide is the O/S process id, the first number in the trace file name but once the trace file is generated I’ve found that the O/S process has ‘disappeared’, most likely disconnected due to errors. No trace files are generated in $ORACLE_BASE/diag/rdbms/<database name>/<sid>/trace, making investigation even more difficult. Since this setting is causing trace files to be generated when suspect network packets arrive it’s only logical that the Network team be involved in troubleshooting the issue; a ‘sniffer’ can be invaluable in locating and identifying bad packets. Once the source of the bad packets is found it should be a simple matter to correct the issue (if it is an internal network issue) or block the offending (possibly malicious) client and stop the trace files from being generated.

Security can’t be an afterthought, and any tools or settings a DBMS vendor can provide to aid in stopping malicious traffic are welcome additions to the security arsenal. When those tools or settings generate false positives because the algorithm is, well, ‘too simple’, then using such settings can be harmful. Care must be taken to monitor how these settings behave in your database, with your network configuration so database performance and availability aren’t compromised.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles