Basic Security Measures for Oracle

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.

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

[oracle@MaxwellDBA ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 15 09:55:19 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

sys@cdb$root:orclcdb> show user;
USER is "SYS"
sys@cdb$root:orclcdb> alter session set container=ORCLPDB1;

Session altered.

sys@cdb$root:orclcdb> show con_name;

CON_NAME
------------------------------
ORCLPDB1
sys@cdb$root:orclcdb> set pagesize 200 linesize 200
sys@cdb$root:orclcdb> col username format a20
sys@cdb$root:orclcdb> col product format a20
sys@cdb$root:orclcdb> 
sys@cdb$root:orclcdb> SELECT * FROM dba_users_with_defpwd ORDER BY username;

USERNAME             PRODUCT
-------------------- --------------------
HR
IX
OE
PM
SCOTT
SH

6 rows selected.

sys@cdb$root:orclcdb> 

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.

sys@cdb$root:orclcdb> show user;
USER is "SYS"
sys@cdb$root:orclcdb> ALTER USER SCOTT ACCOUNT LOCK;

User altered.

sys@cdb$root:orclcdb> 
sys@cdb$root:orclcdb> --- Use the schema
sys@cdb$root:orclcdb> ALTER USER SCOTT ACCOUNT UNLOCK;

User altered.

sys@cdb$root:orclcdb> 

Lock SYS and SYSTEM Accounts

The vast majority of the time there is no need to access the SYS and SYSTEM accounts, since you should be using a DBA account to do your day-to-day work. As a result, these should be locked and only unlocked when needed. So for example, I might do the following.

sys@cdb$root:orclcdb> 
sys@cdb$root:orclcdb> 
sys@cdb$root:orclcdb> show user;
USER is "SYS"
sys@cdb$root:orclcdb> CREATE USER maxwellpan IDENTIFIED BY maxwellpan;

User created.

sys@cdb$root:orclcdb> GRANT DBA TO maxwellpan;

Grant succeeded.

sys@cdb$root:orclcdb>

--- 
ALTER USER sys ACCOUNT LOCK;
ALTER USER system ACCOUNT LOCK;

Password Aging, Expiration and History

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

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.
/

[oracle@MaxwellDBA ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 15 11:15:46 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

sys@cdb$root:orclcdb> show user;
USER is "SYS"
sys@cdb$root:orclcdb> set pagesize 200 linesize 200
sys@cdb$root:orclcdb> show con_name;

CON_NAME
------------------------------
CDB$ROOT
sys@cdb$root:orclcdb> ALTER SESSION SET CONTAINER=ORCLPDB1
  2  ;

Session altered.

sys@cdb$root:orclcdb> CREATE PROFILE my_profile LIMIT
  2    FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME 5
  4    PASSWORD_LIFE_TIME 30
  5    PASSWORD_GRACE_TIME 3
  6    PASSWORD_REUSE_TIME 120
  PASSWORD_REUSE_MAX 10
  8  /

Profile created.

sys@cdb$root:orclcdb> ALTER USER maxwellpan PROFILE my_profile;

User altered.

sys@cdb$root:orclcdb> 

The PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX parameters work in conjunction. If either one is set to unlimited and the other set to an integer value, password reuse is prevented.

Notes.

• Password expiration is an outdated concept. It was originally based on the approximate time it takes a computer to crack a strong password.
• Password expiration can lead to less secure practices. Forcing users to change passwords regularly increases the likelihood of them writing down passwords or using predictable patterns.
• Password expiry can be useful if you don't have a proper policy for removing users from the system, but really you should fix the root cause of your problem, which is to have a proper leavers policy.

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.

sys@cdb$root:orclcdb> show user;
USER is "SYS"
sys@cdb$root:orclcdb> show con_name;

CON_NAME
------------------------------
ORCLPDB1
sys@cdb$root:orclcdb> CREATE OR REPLACE FUNCTION my_verification_function (
  2    username      VARCHAR2,
  password      VARCHAR2,
  old_password  VARCHAR2)
  5    RETURN BOOLEAN AS
  6  BEGIN
  IF LENGTH(password) < 8 THEN
  8      RETURN FALSE;
  9    ELSE
 10      RETURN TRUE;
  END IF;
 12  END my_verification_function;
 13  /

Function created.

sys@cdb$root:orclcdb>

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

sys@cdb$root:orclcdb> 
sys@cdb$root:orclcdb> ALTER PROFILE my_profile LIMIT PASSWORD_VERIFY_FUNCTION my_verification_function;

Profile altered.

sys@cdb$root:orclcdb> show user;
USER is "SYS"
sys@cdb$root:orclcdb> show con_name;

CON_NAME
------------------------------
ORCLPDB1
sys@cdb$root:orclcdb>

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

sys@cdb$root:orclcdb> show user;
USER is "SYS"
sys@cdb$root:orclcdb> show con_name;

CON_NAME
------------------------------
ORCLPDB1
sys@cdb$root:orclcdb> ALTER USER maxwellpan PROFILE my_profile;

User altered.

sys@cdb$root:orclcdb> ALTER USER maxwellpan IDENTIFIED BY max123;
ALTER USER maxwellpan IDENTIFIED BY max123
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed


sys@cdb$root:orclcdb> ALTER USER maxwellpan IDENTIFIED BY maxwellpan;

User altered.

sys@cdb$root:orclcdb> 

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

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, and the DBMS_REFRESH package, which can also schedule jobs.

REVOKE EXECUTE ON dbms_job FROM PUBLIC;
REVOKE EXECUTE ON dbms_refresh FROM PUBLIC;

Revoke Unnecessary Privileges

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

 the Oracle Voyager Worm 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

 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>

Restrict Schema Access to Specific IP Address

An AFTER LOGON trigger can be used to lock down access to specific schemas. The trigger below only allows access to USER1 and USER2 when the connection is made from the IP addresses "192.168.0.101" and "192.168.0.102".

CREATE OR REPLACE TRIGGER system.check_ip_addresses 
AFTER LOGON 
ON DATABASE
BEGIN 
  IF USER IN ('USER1', 'USER2') THEN
    IF SYS_CONTEXT('USERENV', 'IP_ADDRESS') NOT IN ('192.168.0.101','192.168.0.102') THEN
      RAISE_APPLICATION_ERROR(-20000, 'Can not log in from this IP address (' || l_ip_address || ')');
    END IF;
  END IF;
END; 
/