Oracle Database Security Tips

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

Revoke Job-Related Privileges

Enabling Data Dictionary Protection

You can protect the data dictionary by setting the O7_DICTIONARY_ACCESSIBILITY initialization parameter to FALSE.
This parameter prevents users who have the ANY system privilege from using those privileges on the data dictionary,
that is, on objects in the SYS schema.

Oracle Database provides highly granular privileges. One such privilege, commonly referred to as the ANY privilege,
is typically granted to only application owners and individual database administrators. For example, you could grant
the DROP ANY TABLE privilege to an application owner. You can protect the Oracle data dictionary from accidental or
malicious use of the ANY privilege by turning on or off the 07_DICTIONARY_ACCESSIBILITY initialization parameter.

Initialization Parameters Used for Installation and Configuration Security
specifies whether or not the server returns complete database software information to clients.
Controls the display of the product version information, such as the release number, in a client connection.
An intruder could use the database release number to find information about security vulnerabilities that
may be present in the database software. You can enable or disable the detailed product version display by
setting this parameter.

Controls restrictions on SYSTEM privileges. See “Enabling Data Dictionary Protection”
Finding and Changing Default Passwords


Disable Remote Operating System Authentication
Disallow sqlplus / as sysdba



Initialization Parameter

-bash-3.2$ less sqlnet.ora
# Generated by Oracle configuration tools.

Oracle administrators should therefore take immediate action to protect their installations. For those
who don’t use clustering, this will be a relatively easy matter. They can use the

dynamic_registration = off

“dynamic_registration = off” in the in the listener.ora configuration file.

Oracle enables you to restrict database access based on IP address by modifying the SQLNET.ORA file.

The SQLNET.ORA file is Oracle configuration file that typically resides
$ORACLE_HOME/NETWORK/ADMIN directory on UNIX systems and
ORACLE_HOME\network\admin directory on Windows systems.
If SQLNET.ORA file is not found there then you will have to see if you
have a TNS_ADMIN environment variable pointing to a different directory
because SQLNET.ORA file can also be stored in the directory specified by
the TNS_ADMIN environment variable.

Below steps can be used to authorize users from accessing Oracle database based on their IP Address.

1.)Turn On Hostname/IP Checking for Listeners:

Open SQLNET.ORA file in a text editor and add below line

tcp.validnode_checking = yes

2.)Supply lists of nodes to be Allowed/Denied:

Now you will have to use tcp.invited_nodes and tcp.excluded_nodes to supply a list of nodes that you
want to allow or deny for getting access to your database. Make sure that you always enter localhost
as an invited node. Also you must ensure that all node addresses come in one line and no wildcards are
used. Remember the list of included nodes have higher precedence over the list of excluded nodes.

tcp.invited_nodes = (localhost,hostname1,hostname2)
tcp.excluded_nodes = (hostname1,hostname2)

One thing that you should keep in mind is that if you are only using the tcp.invited_nodes then only those
specific nodes will be allowed to access your database and all other IP addresses will be denied from accessing
your database.

Similarly if you are only using tcp.excluded_nodes then only those specific nodes will be denied from getting
access your database and all other IP addresses will be allowed to access your database.

3.)Restart Listeners:
Finally you will need to restart your listeners by running below commands.


SQLNET.ORA for Allowed IP Addresses:

Suppose you want to allow users from IP addresses 70.127.349.101 and 70.127.349.160 only to access your database.
In such scenario your SQLNET.ORA file will look like

tcp.validnode_checking = yes
tcp.invited_nodes = (localhost,70.127.349.101,70.127.349.160)

SQLNET.ORA for Banned IP Addresses:

Suppose you want to ban users from IP addresses 70.127.349.216, 192.176.420.301 and 70.127.349.191 from getting access
to your database. In such scenario your SQLNET.ORA file will look like

tcp.validnode_checking = yes
tcp.excluded_nodes = (70.127.349.216, 192.176.420.301, 70.127.349.191)


Killing Oracle Idle Session!!

Making Idle Session SNIPED:

An idle session can be setup to become sniped after x minutes by setting the initialization parameter
resource_limit = true in the init.ora and idle_time in the user profile.

You can make user session becomes sniped after 8 hours of idle time by running below command:

alter profile DEFAULT set idle_time=480;


Finding the SNIPED Sessions:

Below query can be used to get the SNIPED idle sessions and kill them.

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON, SID, v$session.SERIAL#, v$process.SPID UNIX_PROCESS, v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE, 'alter system kill session ' || '''' || SID || ', ' || v$session.serial# || '''' || ' immediate;' kill_sql FROM v$session, v$process
WHERE ((v$session.paddr = v$process.addr) AND (status = 'SNIPED'))
ORDER BY logon_time ASC;


Killing Oracle Idle Sessions While Shutdown (UNIX – LINUX):

Whenever we shutdown our database with IMMEDIATE then we have to wait till all processes gets terminated.
More the database has open sessions, more the time it will likely take to terminate them.

Below command can be executed in UNIX shell to kill all Oracle sessions where database SID is OTE.
It does not kill the SMON and PMON processes, only the LOCAL=NO.

$ ps -ef|grep 'oracleOTE (LOCAL=NO)'|grep -v grep|awk '{print$2}'|xargs -i kill {}



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.