Some Musings on the Security Implications of Oracle Database Initialization Parameters
Still following the steps of a database installation, this article will muse about some Initialization Parameters with security relevance.
In order to make a Standard Edition database as secure as possible we could start by looking what is the same in SE and EE, which are in their basic security functions more or less equal (security targets of 11g for EE and SE. And after having installed and secured the software (in Part 1 of this series ) we are now ready to create our first database instance. One of the first steps in this process is – and I assume you don’t use clicka-di-click-DBCA blindly – creating/adapting the initial init.ora–file.
Of the hundreds of initialization parameters in 11g, quite a handful also influence the behavior in such a way that it counts as security relevant. These parameters are often barely noticed or rarely changed from their defaults.
Take for example the parameters OPEN_LINKS and OPEN_LINKS_PER_INSTANCE. When asking colleagues around me most of them never-ever change(d) these defaults (both: 4) and when asked whether the database instance actually uses database links or other remote, distributed connections (XA connections) I harvested looks which can only be interpreted as “ehm… why do you ask, should I have bothered?” Maybe … we should at least look what these parameters are intended to do.
OPEN_LINKS determines the maximum number of concurrently open database links and/or connections of external procedure calls of a single session and OPEN_LINKS_PER_INSTANCE does almost the same but for the whole instance and it includes migratable XA connections as well. First of all, it makes no sense to set OPEN_LINKS larger than OPEN_LINKS_PER_INSTANCE, which is pretty obvious. But why do they matter to security?
Especially the OPEN_LINKS_PER_INSTANCE can consist of connections which are relatively easy to highjack. So, if a hacker got access to a database server with open connections (s)he can get access to one of the connections and access the (target) database without the need to authenticate, because the open connection is already authenticated when the connection was established. And each currently unused, but open connection is a “hole” in the security shell of the targeted database (for example of a pending distributed transaction). So, allowing more connections than you will ever use is like pricking holes in the defenses of the targeted databases. If you know your instance will never use database links or allow XA connections, set these parameters to 0 and close the holes before someone else pokes them open. On the other hand application developers should take care not to leave database links open unnecessarily.
(BTW: securing database links might be another blog in the future …)
Another often overlooked parameter is SQL92_SECURITY which is default (in 11.2) set to FALSE, but should be TRUE. The effect of TRUE is that a user must also have SELECT privileges on a table/view used in a WHERE-clause of an INSERT or UPDATE-statement in order to be able to execute updates or inserts. This tightens the restrictions a little more to prevent unauthorized data changes.
Ever heard of the “SEC_”-parameters like SEC_PROTOCOL_ERROR_FURTHER_ACTION and SEC_PROTOCOL_ERROR_TRACE_ACTION? Both reign over the TTC protocol and its possible errors. The first one governs what should be done if such an error occurs or what has to happen when too many errors have occurred, the second sets the tracing options of these errors. TTC is the Oracle wire protocol used by OCI in the JDBC Thin drivers that allow direct connections to the database on top of Java sockets. Again if something goes wrong with a connection it would be nice to know why. And if someone is trying to break in via a JDBC connection the admin/DBA can directly be warned if the trace action is set to ALERT.
The default trace action is set to TRACE which is okay but it should never be changed to NONE because you could easily miss the many undetected bad packets which can indicate a Denial of Service (DoS) attack on your database clients.
SEC_PROTOCOL_ERROR_FURTHER_ACTION can be set to the values of CONTINUE (the default), DELAY or DROP. The actual actions taken are A) DEFAULT: do nothing and go on, normal operations just continue (except maybe logging it when SEC_PROTOCOL_ERROR_TRACE_ACTION is set to TRACE or LOG), B) DELAY the bad packets of a session and therefore all communication sessions to this instance are slowed down (which is to say until it gets unattractive for the attacker and/or normal user) or C) DROP the offending session after x bad attempts. Setting the last two is a bit tricky because they also must contain a value to indicate what the delay should be or after how many bad packets Oracle server should start dropping sessions.
When setting these option, don’t forget the brackets as indicated in the documentation! The value must be written like below in order to be effectively changed:
SQL > ALTER SYSTEM SET SEC_PROTOCOL_ERROR_FURTHER_ACTION = “(DROP, 20)” SCOPE=BOTH;
In this example the database server would drop offending sessions after 20 bad TTC-packets and the client would show the error ORA-03134.
CONTINUE does not impact the good sessions as does the DELAY, which impacts other sessions by delaying the bad session as wel the waiting good session. This is an indication that something is going on. So, I tend to choose DROP in conjunction with SEC_PROTOCOL_ERROR_TRACE_ACTION=TRACE or even ALERT. LOG only registers a short notice in the alert log which often is not enough to debug what precisely happened.
Aprospos DoS attacks… setting SEC_MAX_FAILED_LOGIN_ATTEMPTS (default: 10) to a value equal or just a tiny bit higher than the highest value used in all of the profiles (where it is called FAILED_LOGIN_ATTEMPTS (default: 10)) is the overall emergency break for failed login’s into the instance and can help to prevent or stop brute force attacks or at least break them when someone is just trying to guess the password of a specific account. This parameter caps higher values of the profiles! Personally, I find 10 consecutive failed login attempts quite high. Batches and other automated processes logging in “know” their correct passwords and users who manually log in and miss it more than 5 times (counted since: a) the last password reset, b) the last succesfull login of c) the unlock command of a dba) are simply clumsy and should be reminded to take more care typing their passwords.
The next SEC_-parameter is SEC_CASE_SENSITIVE_LOGIN. Luckily it defaults to TRUE in 11g and so activates the case_sensitivity of passwords. When migrating from 10g to 11g the former un-sensitive passwords of 10g are kept until the first password change in 11g. It should stay TRUE and case-sensitive password should always be used if possible.
In 12c this parameter will be deprecated and there are other ways to force a case-insensitive login. Have good look into Database Upgrade Guide 12c and follow the link therein to the Database Security Guide.
The last of the SEC_-parameters is a static parameter SEC_RETURN_SERVER_RELEASE_BANNER. This parameter works a little bit like the “ServerTokens” directive of an APACHE Webserver but is only effective for unauthenticated clients which makes it very difficult to test.
In FULL mode (here: TRUE) APACHE might result in invitations to hackers with answers like:
Server: Apache/2.0.41 (Unix) PHP/4.2.2 MyMod/1.2
In Production mode (here: FALSE) an Apache server just answers with:
An Oracle instance answers, when set to FALSE, instead of the correct version number of 22.214.171.124 the server, only with the main RDBMS version of 126.96.36.199 which could be a fully patched or a just Out-of-the-box install with all its bugs.
In order to change the value the database has to be restarted! So leave this one on FALSE.
Below is a list with other parameters which are (partly) relevant to security:
- AUDIT_FILE_DEST: sets the path to the audit-files when AUDIT_TRAIL is set to “OS” or “OS, extended”. This path should be secured and monitored to prevent or at least be able to “see” tinkering with the audit-logs.
- AUDIT_SYS_OPERATIONS: should be set to TRUE, always. It is not as comprehensive as the Fine Grained Auditing some Auditors might expect, but nevertheless “it might guard the Guards” a little bit.
- AUDIT_TRAIL: choose at least “DB, extended”, but on systems where the dba’s are not system administrators maybe someone else should check the audit logs on the file system?
- DIAGNOSTIC_DEST: don’t let it block your Oracle Home and again, don’t let it be tampered with, it contains valuable (forensic?) information about the going-on’s in/of your database
- DISPATCHERS: here goes the same as for the OPEN_LINKS, if you don’t use it don’t set or set it to 0.
- GLOBAL_NAMES: If set to TRUE, db_links have to use the service name resp. the global_name, which could form an extra hurdle for some hackers
- LOG_ARCHIVE_%: protect this directory carefully, because firstly you might need it to restore your database and secondly remember: It contains your data (be it in a form your are not used to access in this form) which you are trying to protect!
- MAX_ENABLED_ROLES: This is a deprecated parameter which is default set to 30 in 11R1 and from 11R2 onward it is ignored, so there is no way to prevent users to gather all roles they can get…and in 12c it will be deprecated.
- O7_DICTIONARY_ACCESSIBILITY: since 11g the default is FALSE, keep it that way otherwise you allow access to data dictionary objects when an ANY privilege is granted.
- OS_AUTHENT_PREFIX: don’t use ‘OPS$’ or ” which everybody would try first…
- OS_ROLES: TRUE would leave it to the OS to manage roles, and the OS is easier to reach than the database…
- REMOTE_LOGIN_PASSWORDFILE: do yourself a favor and never set it to NONE
- REMOTE_OS_AUTHENT: will get deprecated in 12c
- REMOTE_OS_ROLES: keep the default to FALSE and let the database manage the roles of remote users
- RESOURCE_LIMIT: in an EE it would fully activate the Resource Manager when set to TRUE and therefore enforce the resource parameters of the profiles; in SE it only seems to activate the resource limits of the profiles. So, set it to TRUE anyway
- SMTP_OUT_SERVER: if you don’t use it, don’t set it!
- SPFILE: it specifies the path to the binary spfile and that is part of your configuration, which should be extra protected
- UTL_FILE_DIR: just don’t use it anymore, use DIRECTORY objects instead. All OS-paths entered here are available for all authenticated users for read AND write access via PL\SQL!
This list does not pretend to be complete. It only should fire up your imagination to study the init-parameters more. It really is quite interesting!
… and I think, in the next blog I might dive into the Possibilities and Limitations of Profiles and Roles…