10 Tips for Oracle Database Security Best Practices

Database Security has always been a concern for a Database Administrator. However, Oracle Database is very much secure, even then there are gaps which has to be filled by DBA to make Oracle Database Secure.

Oracle Database Security involves many aspects like security at OS Level, Network Level, S/W level etc. Here, I am listing 10 best practices for Oracle Database Security.

O/S and S/W Recommendations for database security

1. Keep Software Up to Date:

First and foremost important aspect of Database Security is to keep your OS and Database Software up to date. Oracle used to publish Critical Patch Updates quarterly. Don't miss these update and apply as recommended by Oracle.

Same in case of O/S software, Check for Database Server vendor site and apply security patches recommended by vendor.

2. Restrict Network Access with Firewalls:

Always keep your database servers behind firewalls. With the help of firewall, System admin can make sure access to Database Server is given to known network routes only. Ask you system admin to open only required ports on DB servers like 1521 etc.

3. Follow the Principle of Least Privileges:

Prevention is always better than cure. Instead of give excess privileges to a user and user does  malicious and then correcting it. A user should be given the least amount of privileges to perform their job. User privileges should be reviewed and revised periodically.

TNS Listener Recommendations for Database Security

Oracle Database connection used to happen TNS protocol. When Listener receive a connection request it starts a new database process and make a connection between client and database.

4. ADD IP Restriction and CONNECT_TIMEOUT:

A DBA can restrict access to Database server from specific IP address only and if this happens, Database will be accessed from given IP addresses only. To achieve this DBA needs to do some entries in $TNS_ADMIN/sqlnet.ora file. One prerequisite for this is your IP's should be static. DHCP will not help, since it used to change IP address. Step to add IP Restriction in Database.

CONNECT_TIMEOUT is another help for DBA to make sure Database connection from client to server completes in give seconds. DBA has to add CONNECT_TIMEOUT parameter in to $TNS_ADMIN/listener.ora file to specifies the time, in seconds, for a client to establish an Oracle Net connection to the database instance.

Oracle Database Security

5. Enable Listener Password:

Stopping Listener is the best way to stop new database connections. "lsnrctl stop" is the command which can do wonders for you enemies and can give you sleepless nights. If, database listener is down, new connections to the database will not be possible. So, it's better to secure you listener by providing password. So that, only authorized person with password can perform  Listener operations.

Oracle 10g onward listener uses local OS authentication. As long as one runs LSNRCTL privileged commands (stop, status, etc) as the same user who started the listener, that user will be able to fully administer the running listener without providing a password. If database env. is single instance then perform these operation of listener.or in $ORACLE_HOME/network/admin/listener.ora otherwise in case of RAC env set password for $GRID_HOME/network/admin/listener.ora

In $TNS_ADMIN/listener.ora, set the following parameter:

ADMIN_RESTRICTIONS_{LISTENER_NAME}=ON
 

For example,
 

ADMIN_RESTRICTIONS_LISTENER=ON

This will make a change in "lsnrctl status" output from "Security ON: Local OS Authentication"  (default) to "Security  OFF"
[oracle@database admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-MAY-2013 09:59:59
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                06-MAY-2013 09:52:38
Uptime                    0 days 0 hr. 7 min. 20 sec
Trace Level               off
Security                  OFF
SNMP                      OFF

Listener Parameter File   /etc/oracle/grid/network/admin/listener.ora
Listener Log File         /etc/oracle/diag/tnslsnr/database/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database.example.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "orcl.example.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.example.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Now, We will add password to the listener.
[oracle@database admin]$ lsnrctl
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-MAY-2013 10:01:15
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener LISTENER 
Current Listener is LISTENER

LSNRCTL> change_password
Old password: 
New password: 
Reenter new password: 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Password changed for LISTENER
The command completed successfully
 
Now, DBA will see "Security ON: Password" in "lsnrctl status" , but now you need a password to check listener status. Here password is 123.
LSNRCTL> SET PASSWORD 123
The command completed successfully 

LSNRCTL> STATUS
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                06-MAY-2013 09:52:38
Uptime                    0 days 0 hr. 16 min. 28 sec
Trace Level               off
Security                  ON: Password
SNMP                      OFF

Listener Parameter File   /etc/oracle/grid/network/admin/listener.ora
Listener Log File         /etc/oracle/diag/tnslsnr/database/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database.example.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "orcl.example.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.example.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...

The command completed successfully
Now, your listener is secure. To learn more listener basic operations.

Database Level Recommendations for Security

 6. Disable XDB Protocol:

To support XDB, the TNS Listener process listens on two additional TCP ports: 2100 for ftp access and 8080 for http access. Normally, DBA need not this XDB database.

To disable XDB, remove or comment out the line in init.ora/spfile.ora that reads

*.dispatchers='(PROTOCOL=TCP) (SERVICE=sidXDB)'

7. Implement Password Manage Policy:

Oracle 11g introduced very good password management policy. I would strongly recommend either use default profile for database user or make new one with this. Oracle 11g provide a inbuilt  password verification function which helps to provide strong database user password. Below are steps to enable password verification function.
SQL> SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME LIKE 'PASSWORD%' AND PROFILE LIKE 'DEFAULT';

PROFILE              RESOURCE_NAME             RESOURCE LIMIT
-------------------- ------------------------- -------- ---------------
DEFAULT              PASSWORD_LIFE_TIME        PASSWORD 180
DEFAULT              PASSWORD_REUSE_TIME       PASSWORD UNLIMITED
DEFAULT              PASSWORD_REUSE_MAX        PASSWORD UNLIMITED
DEFAULT              PASSWORD_VERIFY_FUNCTION  PASSWORD NULL
DEFAULT              PASSWORD_LOCK_TIME        PASSWORD 1
DEFAULT              PASSWORD_GRACE_TIME       PASSWORD 7

6 rows selected.

[oracle@database admin]$ sqlplus sys as sysdba 
 
SQL> @ $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
Function created.
Profile altered.
Function created.

SQL> SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME LIKE 'PASSWORD_VERIFY_FUNCTION' AND PROFILE LIKE 'DEFAULT';

PROFILE    RESOURCE_NAME             RESOURCE LIMIT
---------- ------------------------- -------- --------------------
DEFAULT    PASSWORD_VERIFY_FUNCTION  PASSWORD VERIFY_FUNCTION_11G
Now, Every new password provided using default profile will follow "VERIFY_FUNCTION_11G" rules.

8. Change Default Installation Passwords:

After an installation, the application database instance contains default, open schema with default passwords. These accounts and corresponding passwords are well-known to every one and must be changed in production env. If, DBA will not change any one can login into database using these user name and can harm the database. Command to change user password.

sql> alter user {user_name} identified by {New_password};

9. Revoke Default Installation Unnecessary grants:

At time of Database creation by DBA, few grants are given by default to public. DBA has to review and revoke these grants accordingly. Steps of resolve default installation grants.


10. Enable Database Auditing:

Once you have done all suggested things, Now DBA has to enable auditing for database to verify if database is really Secure. To achieve this DBA needs to check audit logs regularly. 

In init.ora, set AUDIT_TRAIL to DB, OS or TRUE. 

AUDIT_TRAIL = OS
 

Set parameter AUDIT_FILE_DEST to the directory where the audit records should be stored.When not set,AUDIT_FILE_DEST defaults to $ORACLE_HOME/rdbms/audit. In this example, the database places audit records in directory /u01/app/oracle/admin/audit.
 
AUDIT_FILE_DEST = /u01/app/oracle/admin/audit
 

Restart the database for these parameters to take effect.Note, the database generates some audit records by default, whether or not AUDIT_TRAIL is enabled. For example, Oracle automatically creates an operating system file as an audit record when a user logs in as SYSDBA or as INTERNAL. 

There could be some more tips for making Database Secure. Please suggest if you know few more.


No comments:

Post a Comment