CS674 Database Security – Spring 2, 2011
MET Boston University

Enhanced Database Security
(Research Paper)

Submitted by Shahid Sami
April 24, 2011

Table of Contents

PAPER OVERVIEW 3
DETAILED DESCRIPTION 3
IMPLEMENTATION 3 1. Removing Default Passwords 3 2. Configuring Oracle Binary Permissions 6 3. Use of UMASK 7 4. Limiting SYSDBA login 9 5. Protecting the Listner 10 6. Limiting the privileges 12
PITFALLS AND RECOMMENDATIONS 13
RESOURCES 14

PAPER OVERVIEW

I will be researching on the following topics.

• Removing Default Passwords • Configuring Oracle Binary Permissions • Use of UMASK • Limiting SYSDBA login • Protecting the Listener • Limiting the privileges

DETAILED DESCRIPTION

Based on the Oracle 11g database, I will research on the above topics in detail. I will look into the shortcomings of the earlier versions of Oracle, the risks involved in those. I will also look into different types of authentications. How the binaries of the database can be protected. How to protect and secure the listener?

IMPLEMENTATION

1. Removing Default Passwords

When Oracle software is installed and a new database is created, the database create some common users. These users will have default passwords which are well know to many oracle users and hackers may try using them. So as a first step, one should identify these passwords and change them appropriately.

Security expert Pete Finnigan has written some scripts to collect these default passwords in a table and compare the passwords with existing passwords to find out if any user has the default password. The below steps how to use his scripts to find those users and try to lock and/or change the passwords.

1. Download from http://www.petefinnigan.com/default/osp_accounts_public.zip and copy the directory “osp_account_directory” on local machine or server where we need to address the databases.

[pic]

2. Connect to the SQL as sys and run the script (osp_install.sql) from above directory.

Connect sys as sysdba @/home/oracle/Desktop/samiShahid/osp_install.sql

[pic]

[pic]

3. Login as oraprobe user and execute the following query to get the status of users who are vulnerable in the database. This query shows that the users who are using default passwords and are not locked and expired.

col password format a20 col account_status format a20 col username format a15 select o.username, u.password, d.account_status from dba_users d, osp_accounts o , sys.user$ u where d.user_id = u.user# and o.hash_value = u.password and d.account_status not like 'EXPIRED%LOCKED' ;
[pic]
4. Identify un-used users, lock and expire those accounts as follows.

From the above query results, determine which accounts you want to lock and/or change the default passwords. The syntax for locking and expiring is:

ALTER USER ACCOUNT LOCK PASSWORD EXPIRE

The syntax for changing the password is:

ALTER USER IDENTIFIED BY

Note: It is always a very good practice to always lock the user SCOTT in a production environment as many people are familiar with the password tiger.

There is also a dictionary view dba_users_withdefpwd which gives all the user who are using default passwords.

2. Configuring Oracle Binary Permissions

In oracle software one of the most important binary file among many is the file with name oracle.exe in windows environment and oracle and in unix/linux environment. When we look at the permissions on this file in a unix environment as shown below this executable uses as special permission ‘s’ instead of ‘x’.

$ cd $ORACLE_HOME/bin
$ ls -l oracle
-rwsr-s--x 1 oracle dba 69936576 Oct 26 13:59 oracle

Instead of using the normal executable permissions ‘x’, the ‘s’ indicates that the this file has the setuid enable. It means that though the owner of this file is oracle, anybody who connects can run the program. This may expose the possibility of opening the important database files.

When a user connects to the oracle two processes will be create. One is the user process which use tools like sqlplus, toad etc to connect and the other is the server process which run the above oracle file. What this means is that there is no need for the oracle binary file to have permissions to other user at all. Therefore the unnecessary permissions can be removed using the following command.

$ chmod 4700 $ORACLE_HOME/bin/oracle

After the above command, the file permissions for the oracle file will look like this.

-rws------ 1 oracle dba 69375856 Mar 9 2011 oracle

Now we can disable the setuid and just give the executable permissions so that no othe user can run the binary file oracle. This can be done by issuing the following command.

$ chmod 0700 $ORACLE_HOME/bin/oracle

After the above command, the file permissions for the oracle file will look like this.

-rwx------ 1 oracle dba 69375856 Mar 9 2011 oracle

This has a significant impact. If user tries to connect as a local user from the server the permission is denied as he the user does not have the permission to execute the oracle binary file. This is illustrated below.

$ sqlplus admdba/admdba

ERROR:
ORA-12546: TNS:permission denied
Enter user-name:

However the user will be able to connect if connect string is used as shown below.

$ sqlplus admdba/admdba@orcl

Connected.

This is same as user connecting from a remote server using the tnsnames file.

3. Use of UMASK

As you know, you can change permissions in unix using the chmod command. However, as chmod works on existing files only, how can you make sure that files created later have the same permissions?

The exact permissions set on a newly created file are dictated by a special parameter called umask. The umask is a set of values that is subtracted from the all permissions to arrive at the permission value of the new file. To calculate permissions which will result from specific UMASK values, subtract the UMASK from 666 for files and from 777 for directories.
For example, if you have umask set to 777, it's subtracted from the overall permission value 777, resulting in 000—no permissions on the new file. The umask is a powerful and effective way to set permissions for the different files Oracle will create.
The overall umask of the Oracle software owner should be 022, which results in the files as Read+Write by owner and Read by all others. You can place this in the login profile file of the user so that it takes effect at all times.

There are many different types of files used by Oracle—data files, redo log files, trace files, and so on. Datafiles may be known beforehand and you can easily change their permissions, but tracefiles are generated at runtime. Thus, you should use umask to ensure the files are not exposed to any external users —trace files contain a variety of confidential information that can be exploited by hackers. For instance, someone could theoretically steal data files by copying them, mount them on a separate server, and bring the database up to rifle through its contents.

Set the umask for the directories as shown below:

|Directory |Description |Umask |
|Directory specified by the nitialization parameter |Some trace files are generated here as well as the database |0177 |
|background_dump_dest |alert log. Permissions should be rw------- (Read+Write by | |
| |Oracle software owner only). | |
|Directory specified by the initialization parameter |Trace files are generated here. Permissions should be the same|0177 |
|user_dump_dest |as above. | |
|$ORACLE_HOME/rdbms/log |Some database log files are generated here. |0177 |
| |Permissions should be the same as above. | |
|$ORACLE_HOME/rdbms/audit |Audit trails of database audit are stored here by default, |0177 |
| |unless you have set the audit_file_dest initialization | |
| |parameter. Permissions should be the same as above. Even if | |
| |you have DB audit trail, some common events such as SYSDBA | |
| |connections and database startup/shutdown are always audited | |
| |and placed here. | |
|Directory specified by the initialization parameter |Audit trails of database audit are stored here by default, |0177 |
|audit_file_dest |unless you have set the audit_file_dest initialization | |
| |parameter. Permissions should be the same as above. | |

4. Limiting SYSDBA login

Any unix user who belongs to group ‘dba’ can login to the database as as SYSDBA.

sqlplus / as sysdba

[pic]

Even though this is a convenience so that the database administrator does not need to remember the password to login as SYS account, it is very vulnerable. Even a strong SYS password is of use in this situation.

The auto login as sysdba can be disabled by setting the value of a parameter SQLNET.AUTHENTICATION_SERVICES in SQLNET.ora file to NONE. This file is available in $ORACLE_HOME/network/admin. (If the file is not available you can create one). Once this is set the root user cannot login as sysdba.

[pic]

5. Protecting the Listner

By default Oracle creates EXTPROC entry in the listner.ora configuration file for connecting to external procedural calls. This may give access to a hacker access to database and operating system.

[pic]

It is always a good practice to delete this entry if there no need for external procedural calls.

If hackers have access to run the listener utility and have access to the listener.ora, they can change the listener.ora file and set commands like SET DISPLAYMODE VERBOSE which sends lot of information to trace file. They can then run the listener utility to set the trace level to support and direct the trace file to any other folder and get lot of information in the trace file.

[pic]

To restrict the above, add the following line to the listener.ora file and restart the server.

ADMIN_RESTRICTIONS_LISTENER = ON

Now if the hacker runs the command, an error will be generated.

LSNRCTL> set trc_directory /hacker_dir
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=PROPRD1))
TNS-12508: TNS:listener could not resolve the COMMAND given

6. Limiting the privileges

A typical user needs privileges that are important to perform his or her job—nothing neither more nor less. As this policy may prove unrealistic, however, you may need to adopt a middle-of-the-road approach: removing the most powerful privileges the users do not need.

One example of a powerful privilege is CREATE ANY TABLE, which lets the user create a table in any schema, not just its own. Rarely do users need this privilege; you can safely revoke it. On the other hand, a privilege like QUERY REWRITE, which allows the users' sessions to rewrite a query to take advantage of a function-based index or materialized view, is relatively innocuous.

First, identify all the privileges you consider innocuous (CREATE TYPE, CREATE SESSION, and so on) as follows:

select privilege, grantee, admin_option from dba_sys_privs where privilege not in
( /* list any other privilege here you don't find "sweeping" */
'ALTER SESSION',
'QUERY REWRITE',
'CREATE DIMENSION',
'CREATE INDEXTYPE',
'CREATE LIBRARY',
'CREATE OPERATOR',
'CREATE PROCEDURE',
'CREATE SEQUENCE',
'CREATE SESSION',
'CREATE SNAPSHOT',
'CREATE SYNONYM',
'CREATE TABLE',
'CREATE TRIGGER',
'CREATE TYPE',
'CREATE USER',
'CREATE VIEW',
'UNLIMITED TABLESPACE'
)
and grantee not in
('SYS','SYSTEM','WKSYS','XDB', 'MDSYS','ORDPLUGINS','ODM','DBA', 'CTXSYS',
' EXP_FULL_DATABASE', 'IMP_FULL_DATABASE','OLAP_DBA', 'OLAP_SYS', 'ORDSYS')
/* Place all the user names you want to exclude */ order by grantee, privilege;

Sample output

PRIVILEGE GRANTEE ADM
---------------------------------------- ------------------------------ ---
SELECT ANY DICTIONARY ADMDBA NO
SELECT ANY SEQUENCE ADMDBA NO
SELECT ANY TABLE ADMDBA NO
CREATE EVALUATION CONTEXT AQ_ADMINISTRATOR_ROLE YES
CREATE RULE AQ_ADMINISTRATOR_ROLE YES
CREATE RULE SET AQ_ADMINISTRATOR_ROLE YES
DEQUEUE ANY QUEUE AQ_ADMINISTRATOR_ROLE YES
ENQUEUE ANY QUEUE AQ_ADMINISTRATOR_ROLE YES
MANAGE ANY QUEUE AQ_ADMINISTRATOR_ROLE YES
CREATE ANY DIRECTORY CARS NO
CREATE ANY SEQUENCE CARS NO
CREATE ANY TABLE CARS NO
CREATE PUBLIC SYNONYM CARS NO
DROP ANY SEQUENCE CARS NO
CREATE CLUSTER CONNECT NO
CREATE DATABASE LINK CONNECT NO
SELECT ANY DICTIONARY DBSNMP NO
DEBUG ANY PROCEDURE JAVADEBUGPRIV NO
DEBUG CONNECT SESSION JAVADEBUGPRIV NO
ANALYZE ANY OEM_MONITOR NO
SELECT ANY DICTIONARY OEM_MONITOR NO
CREATE PUBLIC SYNONYM OLAP_USER NO

Be wary of using the ADMIN OPTION while granting privileges as it can cause a proliferation of privileges.

Dictionary objects also have to be protected from general users. So it is a good practice to set the o7_DICTIONARY_ACCESSIBILITY initialization parameter to FASLE.

alter system set O7_DICTIONARY_ACCESSIBILITY=FALSE scope=spfile ;

One good thing is that the SELECT ANY DICTIONARY system privileges is not included in the GRANT ALL PRIVILEGES.

PITFALLS AND RECOMMENDATIONS

Common pitfalls are not changing the default passwords, not managing the privileges properly, unknown data, not encrypting the sensitive data, lagging behind in software patches, not securing the backups and network security.

The investment in security is lower than the cost involved in data beaches and noncompliance. Auditing is not an overhead. Use a good password policy and lock the account after 3 failed attempts. Remember the CIA triangle

RESOURCES

Oracle 11g DBA handbook, Oracle Security books, interview with my DBA and some online sites.

http://www.petefinnigan.com/default/default_password_list.htm http://www.securedba.com/securedba/2007/12/hardening-the-o.html http://www.ordba.net/Articles/HardeningOracleDB.htm
HOWTO secure and Audit Oracle 10g and 11g by Ron Natan
Database Security and Auditing by Hassan A Afyouni
Oracle Database 11g DBA Handbook
Oracle Database 11g The Complete Reference
Interview with my Oracle DBA.