Oracle Databases: Some Simple Security Steps!

Databases are secure enough, or at least they can be made secure by employing encryption and other standard techniques. Every data admin thinks that way! So more often than not, majority of the database administrators will happily install a new Oracle database, port the schemas and data to it and proceed worry-free, unaware of the potential threats and risks involved. Talk about security? Well yes, they do follow the usual routines such as keeping the software updated with the latest update patches, using hard-to-guess passwords and perhaps employ that odd bit of encryption. Apparently, with just a few minor tweaks, database admins can go beyond that and make their data secure.

Oh No! those abundant privileges

Not so long ago, in Oracle 10g, the privilege to read/write across the network without even using the command line was given to every user, by default. For instance, with execute privileges on the UTL_TCP database package, a user could do virtually anything with TCP/IP — alter/modify web pages, download/edit entire website, and of course generate spam — all without the knowledge of the database administrator.

And ever since Oracle 11g-R2, such execute privileges on UTL_TCP, UTL_HTTP and UTL_SMTP have by default been made PUBLIC, that is, accessible to all database users. Therefore, such privileges won't work unless a database administrator himself allows the database to connect and resolve the network access layers (using the DBMS_NETWORK_ACL_ADMIN package). Sounds secure? Not yet. What if a user installs his own Oracle database and becomes the SYS user, simply to spam others? The remedy? Simple! Restrict the PUBLIC access privileges to the minimum.

Where are you, SCOTT!

We all know Oracle has a demo user named SCOTT with the password TIGER. Little do we notice that this demo user has CREATE TABLE, CREAT DIRECTORY privileges and access to the UTL_FILE package. Thus, logically, SCOTT can map a directory to the $ORACLE_HOME/sqlplus/admin directory and replace the glogin.sql script with one that contains malicious code, such as a command like “GRANT DBA TO SCOTT”. In all versions of Oracle, both glogin.sql and login.sql run whenever an admin logs in.

The easiest cure to prevent such disaster is to revoke PUBLIC access from UTL_FILE and DBMS_SQL packages.

Better safe than sorry

There are numerous other preventive measures that can be taken to enhance security. To begin with, you can limit the number of invalid login attempts that are allowed to a user to access a database schema with an incorrect password. To limit the number of such attempts to, let's say, 3, and in case the user utilizes those attempt, lock the login to that account for one hour, create the following profile:

Now, in order to apply the above profile to a particular user named JONNY (and not to all the users), use the following command:

In the above example, JONNY's account will be locked for an hour if there are three consecutive failed login attempts.

Restricting access via IP address

Yes, Oracle allows you to block or grant access based on the user's IP address. In the Oracle Connection Manager (which is a part of Oracle Net Services), an admin can create a proxy server that screens traffic to the public database on the basis of the rules specified by the administrator. As a result, depending on the IP address of the user (and, if specified, the IP of the requested destination as well), the user's request can be denied or granted. Such rules are, generally, specified in the Oracle Connection Manager's RULE_LIST which lies in the $ORACLE_HOME/network/admin/cman.ora file. As a mid or small-level enterprise, denying access requests to particular IP addresses is not the norm as such, but this feature can be employed to grant selective access to users and thereby implement better abstraction in a cheaper manner. Allowing similar selective access by means of other measures would incur more costs.

For example, in the following example, all connection requests through the proxy server arising from IP address 123.4.5.* are accepted, and allowed to proceed to the main_pcq database server to reach the service named pcqst. However, only one user from IP address 123.4.67.110 is allowed to access the service named awesome:

In the above example, MCT is the Maximum Connection Time and MIT is the Maximum Idle Time. At the moment, only TCP traffic can be filtered by means of Oracle Connection Manager. Other protocols, --TCPS (or TCP-S) aren't supported yet.

Restricting access on the basis of time-of-day

Generally, a mid-level enterprise does not operate 24x7. If yours too is the same, you might consider restricting access to your database on the basis of time-of-day, such that no one can access it at night (or vice-versa). To do so, you just need to implement the following logon trigger:

Note: Daylight savings times

If your enterprise does not have users or workers from different time zones, this part is immaterial for you. However, if your users/workers are spread across different countries, you will probably be worried about Daylight Savings Time issues. If you have the latest version of Oracle installed along with JDK or Java 5+, this shouldn't be a problem. But if you are using Oracle 10g, certain components like Oracle Internet Directory, Oracle Internet Management and Oracle Grid Control will not be able to make allowance for Daylight Savings Time. In such case, you can download the requisite patch from the Oracle website:

http://tinyurl.com/3gr6yhc

Some more privacy lessons

If you wish to restrict access by nodes, you can configure the database listener's configuration file located at $ORACLE_HOME/network/admin/sqlnet.ora and its TCP.VALIDNODE_CHECKING parameter. If the parameter is set, then TCP.EXCLUDED_NODES and TCP.INVITED_NODES settings are used to filter out or allow the specified nodes to communicate with the database.

Track them down, via system triggers

You can track who has modified the compiled code via system triggers. After ensuring that the database initialization parameter _system_trig_enabled is set to TRUE, many significant DDL changes can be tracked and prevented. Such changed include unauthorized attempts to grant privileges, drop or create tables, schemas and even alter or recompile a stored code object. If your enterprise hires specialized security staff for such purposes, such triggers can be used for keeping logs of security issues. However, if you do not have separate security staff like most mid-level enterprises, you can employ separate kinds of system triggers to keep an eye on system events yourself.

In general, a trigger permits you to capture the IP address, username, details of SQL, etc about the malicious attacker. With this level of info, you can probably not become the next James Bond for your enterprise, but you can surely block or restrict the user accounts that seem to be compromised, without even touching or modifying your code.