Basic Security Measures for Oracle

This article is based on Oracle Database Security Checklist and MOS Note:340009.1 and presents some basic steps to increase the security of your Oracle database servers.

Default Oracle Passwords
Password Management
Revoke Job-Related Privileges
Revoke Unnecessary Privileges
Securing the Listener
Miscellaneous

Default Oracle Passwords

By default Oracle creates a number of schemas, each with a default password. Although many of these users are locked, it is still good practice to switch to non-default passwords in case the are unlocked by mistake. In addition, regular users often switch their passwords to match their username. Both of these situations represent a security risk.

Pete Finnigan has an Oracle Default Password Auditing Tool that checks for default passwords, and users whose passwords match their usernames. This is pretty handy to pick up any loose ends. To use this tool, download and extract the zip file. Run the osp_install.sql script to install the password checker and the ops_exec.sql file to run it.

Oracle 11g includes a new view called DBA_USERS_WITH_DEFPWD, which displays all users which have a default password set.

SELECT * FROM dba_users_with_defpwd ORDER BY username;

USERNAME
------------------------------
APPQOSSYS
BI
DIP
EXFSYS
IX
MDDATA
MDSYS
OLAPSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
OWBSYS
PM
SCOTT
SI_INFORMTN_SCHEMA
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
WMSYS
XDB
XS$NULL

22 rows selected.

SQL>

Password Management

Lock Unused Accounts

The Oracle database includes a range of functionality to help secure database users. Unused accounts should be locked, while accounts that are used intermittently should be unlocked as needed.

ALTER USER scott ACCOUNT UNLOCK;

-- Use the schema.

ALTER USER scott ACCOUNT LOCK;

Password Aging, Expiration and History

Password aging, expiration and history is managed via profiles, as shown below.

CONN sys/password AS SYSDBA

CREATE PROFILE my_profile LIMIT
  FAILED_LOGIN_ATTEMPTS 3  -- Account locked after 3 failed logins.
  PASSWORD_LOCK_TIME 5     -- Number of days account is locked for. UNLIMITED required explicit unlock by DBA.
  PASSWORD_LIFE_TIME 30    -- Password expires after 90 days.
  PASSWORD_GRACE_TIME 3    -- Grace period for password expiration.
  PASSWORD_REUSE_TIME 120  -- Number of days until a specific password can be reused. UNLIMITED means never.
  PASSWORD_REUSE_MAX 10    -- The number of changes required before a password can be reused. UNLIMITED means never.
/

ALTER USER scott PROFILE my_profile;

The PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX parameters work in conjunction, so if either is set to unlimited password reuse is prevented.

Password Verification : Enforcing Password Complexity

Password complexity is enforced using a verification function. Oracle provide an example password verification function in the "$ORACLE_HOME/rdbms/admin/utlpwdmg.sql" file, but you can code a custom one if you prefer. The function must accept three parameters (username, password and old_password) and return a boolean value, where the value TRUE signifies the password is valid. The simple example below just forces the password to be at least 8 characters long.

CREATE OR REPLACE FUNCTION my_verification_function (
  username      VARCHAR2,
  password      VARCHAR2,
  old_password  VARCHAR2)
  RETURN BOOLEAN AS
BEGIN
  IF LENGTH(password) < 8 THEN
    RETURN FALSE;
  ELSE
    RETURN TRUE;
  END IF;
END my_verification_function;
/

Once the function is compiled under the SYS schema it can be referenced by the PASSWORD_VERIFY_FUNCTION parameter of a profile.

ALTER PROFILE my_profile LIMIT
  PASSWORD_VERIFY_FUNCTION my_verification_function;

The code below assigns the completed profile to a user and tests it.

SQL> ALTER USER scott PROFILE my_profile;

User altered.

SQL> ALTER USER scott IDENTIFIED BY small;
ALTER USER scott IDENTIFIED BY small
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-28003: password verification for the specified password failed


SQL> ALTER USER scott IDENTIFIED BY much_bigger;

User altered.

SQL>

A more complete example of a password verification function is provided by the "$ORACLE_HOME/rdbms/admin/utlpwdmg.sql" script.

If you have trouble thinking of strong passwords, try using a password generator like RandPass.com. It creates random strong passwords with a phonetic sound, making them easier to remember.

In Oracle 11g the "$ORACLE_HOME/rdbms/admin/utlpwdmg.sql" script has been updated to improve the default password verification function. It also applies the function to the DEFAULT profile, enabling it for all users.

Case Sensitive Passwords

From Oracle 11g onward, passwords can be case sensitive. You can read more about how to configure this here.

Revoke Job-Related Privileges

Prior to Oracle 10g, every user with access to the DBMS_JOB package had the ability to schedule database jobs. In these versions this does not represent an obvious security risk, but it allows users to schedule untuned and intensive operations that can reduce database performance. For this reason I suggest removing public access to the DBMS_JOB package.

REVOKE EXECUTE ON dbms_job FROM PUBLIC;

Oracle 10g Release 1 (10.1.0) introduced a new scheduler, along with the concept of external jobs. This scheduler is secured with two new privileges (CREATE JOB and CREATE ANY JOB), neither of which are granted by default. At first this seems like an improvement, but the ability to create a job as any user allows the grantee full access to the SYS user and its privileges. I see no reason to ever grant a user the CREATE ANY JOB privilege, and I would avoid granting the CREATE JOB privilege if possible.

External jobs present an equally large threat as they allow access to the full power of the underlying operating system, including OS authentication connections to the database. In Oracle 10g Release 1 there is no distinction between an internal job and an external job as far as privileges are concerned, so even the CREATE JOB privilege represents a massive security breach. In Oracle 10g Release 2 (10.2.0) this situation is improved by the addition of the CREATE EXTERNAL JOB privilege, allowing access to internal and external jobs to be granted separately. Even so, access to the scheduler should be guarded very carefully.

Revoke Unnecessary Privileges

As a rule of thumb, you should grant users the smallest number of privileges necessary to do their job.

MOS Note:340009.1 discusses the Oracle Voyager Worm and suggests that removal of excessive privileges may prevent attacks from happening in the first place, or spreading from a compromised system.

REVOKE CREATE DATABASE LINK FROM connect;
REVOKE EXECUTE ON utl_tcp FROM public;
REVOKE EXECUTE ON utl_smtp FROM public;
REVOKE EXECUTE ON utl_http FROM public;
REVOKE EXECUTE ON utl_mail FROM public;
REVOKE EXECUTE ON utl_inaddr FROM public;
REVOKE EXECUTE ON utl_file FROM public;
REVOKE EXECUTE ON dbms_java FROm public;

In the same way, granting excessive numbers of roles may be dangerous. Instead create you own roles that contain only necessary privileges.

Securing the Listener

In versions prior to 10g Release 1, the TNS listener should be password protected using the lsnrctl utility or the netmgr GUI. When using the lsnrctl utility, the change_password command is used to set the password for the first time, or to change an existing password.

LSNRCTL> change_password
Old password:
New password:
Reenter new password:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.mydomain)(PORT=1521)))
Password changed for LISTENER
The command completed successfully
LSNRCTL>

The "Old password:" value should be left blank if the password is being set for the first time. Once the new password is set, the configuration should be saved using the save_config command.

LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.mydomain)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File   /u01/app/oracle/product/10.1.0/db_1/network/admin/listener.ora
Old Parameter File   /u01/app/oracle/product/10.1.0/db_1/network/admin/listener.bak
The command completed successfully
LSNRCTL>

Once the password is set, subsequent attempts to perform privileged operations such as save_config and stop will fail unless the password is set using the set password command.

LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL>

The image below shows the same operation being performed by the Oracle Net Manager (netmgr) GUI.

Setting the listener password using Net Manager (netmgr).

In addition to password protection, MOS Note:340009.1 suggests changing the TNS listener default port from 1521 to a different port. This will certainly help prevent generic attacks where worms are specifically targeting port 1521, but will only cause a minor delay for a targeted hack where open ports are scanned.

The TNS listener port settings are configured by editing the "$ORACLE_HOME/network/admin/listener.ora" file and restarting, or reloading, the listener. In addition, the LDAP entries or local "$ORACLE_HOME/network/admin/tnsnames.ora" file entries of any clients must be modified to reflect the changes.

Client access to the server can be restricted by adding the following entries to the "$ORACLE_HOME/network/admin/protocol.ora" file.

tcp.validnode_checking = YES
tcp.excluded_nodes = {list of IP addresses}
tcp.invited_nodes = {list of IP addresses}

This may work OK in a 3-tier architecture where only a small number of applicaton servers connect to the database.

Miscellaneous

On installation and database creation, only install the options you are going use. Some options such as the HTTP Server and XML DB are open for attack unless they are properly patched and secured. Many addition features require their own schemas, which must be secured.
Secure the operating system of the database server. There is little point securing your Oracle installation if you have weak operating system security. Things to consider include:
- Make sure all servers are behind a firewall.
- Reduce the number of people with access to the servers.
- Make sure strong passwords are used in all cases.
- Protect sensitive areas of the file system.
Apply security patches as soon as possible.
Consider enabling data dictionary protection by setting the 07_DICTIONARY_ACCESSIBILITY initialization parameter to FALSE. The prevents users with ALL-type system privileges modifying the data dictionary, unless they are a DBA-privileged account.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.