Guideline for hiding EncryptionKey while using DBMS_CRYPTO.

HI

F.Y.I. Only

There are single custom user developed package which will having two customized function like encrypt and decrypt.

Four Simple steps to configure the PLSQL – DBMS_CRYPTO
1. You have to modify the EncryptionKey in this package inside encrypt function.
2. Wrapping the Package code completed and generate the sql file using Wrap Utility, (a standalone programming utility that encrypts PL/SQL source code)

3. Package source code is unreadable to anyone, even the owner of the package like DBA or Developer.
4. This way we can hide the encryption logic completely from every one.

Demo
http://oracleflash.com/41/Encrypt-or-Decrypt-sensitive-data-using-PLSQL—DBMS_CRYPTO.html

Reference
http://www.dba-oracle.com/t_dbms_crypto.htm

How to Protect your Server Against the Shellshock Bash Vulnerability ?

There’s latest security flaw Bash Bug called Shellshock affecting Linux nodes. It’s a major vulnerability related to Bash.

Please check/review if Linux nodes are affected by this security flaw, and prepare plan for patching it.
The Shellshock vulnerability can be exploited on systems that are running Services or applications that allow unauthorized remote users to assign Bash environment variables. Examples of exploitable systems include the following:
 Apache HTTP Servers that use CGI scripts (via mod_cgi and mod_cgid) that are written in Bash or launch to Bash subshells
 Certain DHCP clients
 OpenSSH servers that use the ForceCommand capability
Various network-exposed services that use Bash

For more details, please refer below link –

Resolution

[root@kvmpri01-vm05 ~]# rpm -qa | grep bash
bash-4.1.2-14.el6.x86_64
[root@kvmpri01-vm05 ~]#
[root@kvmpri01-vm05 ~]#
[root@kvmpri01-vm05 ~]# bash --version
GNU bash, version 4.1.2(1)-release (x86_64-redhat-linux-gnu)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>

This is free software; you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
[root@kvmpri01-vm05 ~]#
[root@kvmpri01-vm05 ~]#
[root@kvmpri01-vm05 ~]# env VAR='() { :;}; echo Bash is vulnerable!' bash -c "echo Bash Test"
Bash is vulnerable!
Bash Test

sftp> put bash-4.1.2-15.el6_5.2.x86_64.rpm
Uploading bash-4.1.2-15.el6_5.2.x86_64.rpm to /root/bash-4.1.2-15.el6_5.2.x86_64.rpm
100% 905KB 905KB/s 00:00:00

[root@kvmpri01-vm05 ~]# rpm -Uvh bash-4.1.2-15.el6_5.2.x86_64.rpm
warning: bash-4.1.2-15.el6_5.2.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY
Preparing... ########################################### [100%]
1:bash ########################################### [100%]
[root@kvmpri01-vm05 ~]#
[root@kvmpri01-vm05 ~]# env VAR='() { :;}; echo Bash is vulnerable!' bash -c "echo Bash Test"
Bash Test

[root@kvmpri01-vm05 ~]# which bash
/bin/bash

[root@kvmpri01-vm05 ~]# bash --version
GNU bash, version 4.1.2(1)-release (x86_64-redhat-linux-gnu)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>

This is free software; you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

Hiding the password

Initialization parameter

NAME TYPE VALUE
—————————— ——- ——————–
os_authent_prefix string ops$

in my init.ora. I then:

create user ops$tkyte identified externally;

grant connect to ops$tkyte;

useradd tkyte
usermod -G oinstall,dba tkyte
passwd tkyte

cd /home/oracle
cp .bash_profile /home/tkyte

su – tkyte

bash-3.2$ sqlplus /

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 3 18:48:08 2014

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

ERROR:
ORA-12547: TNS:lost contact

-3.2$ cd $ORACLE_HOME/bom
-bash: cd: /home/oracle/u01/app/oracle/product/11.2.0/db_1/bom: No such file or directory
-bash-3.2$ cd $ORACLE_HOME/bin
-bash-3.2$ ls oracle
oracle
-bash-3.2$ ls -ltr oracle
-rwxr-x–x 1 oracle oinstall 232399473 Aug 9 2013 oracle

-bash-3.2$ sqlplus /

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 3 19:08:31 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is “OPS$TKYTE”
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-bash-3.2$ expdp userid=/ full=y

http://www.dadbm.com/how-to-fix-ora-12547-tns-lost-contact-when-try-to-connect-to-oracle/
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:142212348066

ORA-24247: network access denied by access control list (ACL)

TEST_USER @DB11> SELECT utl_inaddr.get_host_name FROM dual;
SELECT utl_inaddr.get_host_name FROM dual
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at line 1

http://oraexplorer.com/2010/02/oracle-11g-network-access-denied-by-access-control-list-acl-when-using-utl_inaddr/

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

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

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

SELECT * FROM DBA_USERS_WITH_DEFPWD;

======================================================================================================
Disable Remote Operating System Authentication
Disallow sqlplus / as sysdba

sqlnet.ora

#SQLNET.AUTHENTICATION_SERVICES = (NTS)

Initialization Parameter
remote_os_authent=false

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

SQLNET.AUTHENTICATION_SERVICES=(NONE)
========================================================================================================
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)

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

$LSNRCTL STOP
$LSNRCTL START

Example
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 {}

=========================================================================================================

More