How
can I secure my Oracle Database?
Gathered
By:
John Kazerooni
While
today, we take security seriously, unfortunately we don’t take database security as seriously as
other security. The database information is the most valuable asset that an organization owns. The
database security today should be taken more seriously; and it should not be compromised to save
money.
This article only will address the most important security holes that we as a DBA may
encounter in our database setup. This topic is endless and remembers that there is no such a secure
environment. What we will try to do is: to make a safer and securer environment for ourselves.
First,
you should always try to keep up to date with Oracle Security vulnerabilities on Oracles Security
Alert page on the Oracle web site.
Oracle’s
Security Site: Oracle
Security
and Oracle’s
Customer information Site: Oracle Metalink
to check a security alerts and patches regularly.
A
check list to protect your database (Minimum compromising)
The
following items may or maybe applicable to your database environment. It is very important at least
to pay attention to following basic items.
1-
Make sure to change passwords
(not easy to find) of your SYS,
and SYSTEM usernames and lock or drop other usernames if you don’t use them. To lock an account (ALTER
USER username ACCOUNT LOCK; DROP USER username CASCADE;)
1-
Make sure to change the
orcladmin/welcome and sysman/oem_temp passwords if you use OEM and Oracle9ias.
2-
Consider to lock ORACLE
account if needed. Be sure to have supper account to access to ORACLE account and then run the
ORACLE’s profile.
3-
Make sure to have a secure
ORACLE password.
For
Oracle Net Password Protection:
Non-encrypted
Password Protection:
/*
Add PASSWORDS_listener entry to listener.ora. PASSWORDS_listener=(mypassword)
$lsnrctl
lsnrctl>
SET current_listener your_listener_name
lsnrctl>
SET password mypassword
lsnrctl>
STOP or START
Encrypted
Password Protection;
/*
There should not be any PASSWORDS_listener entry */
$lsnrctl
lsnrctl>
SET current_listener your_listener_name
lsnrctl>
SET save_config_on_stop ON
lsnrctl>
CHANGE_PASSWORD
lsnrctl>
SET password
/* since it is encrypted you can not say
“SET password newpassword” */
lsnrctl>
STOP or START
Forgot
your password:
/*
Comment the following lines in the listener.ora file */
PASSWORDS_listener
= 77DE8751BF7645921
SAVE_CONFIG_ON_STOP_LISTERNER=ON
Restrict
operator to SET commands
/*
Add the following line */
ADMIN_RESTRICTIONS_LISTENER=ON |
4-
Use SSH or SUDO to disable
remote log-in to the ORACLE account.
5-
On Unix System, change the
$ORACLE_HOME/bin
files’ permissions to 0751 or less if possible.
6-
Make sure to set
REMOTE_LOGIN_PASSWORD_FILE=NONE.
7-
Make sure that the ORACLE
account is not a member of root (UNIX) and it is only a member of the dba group.
$ grep –i root /etc/group
$ grep –i dba /etc/group
8-
Make sure that datafiles have
only read/write accesses. ($cdmod –R 600 /u02/oradata)
9-
Don’t hard code a user name
and password in your sql scripts. If you have to, make sure to use /nolog to instead of entering the
username and password.
$sqlplus /nolog @mysqlscripts.sql (still this is not good since your
username and password is in sql scripts but it is better than $sqlplus scott/tiger @mysqlscripts
that the whole world can find out.
Or for exporting do the following:
$exp UP=scott/tiger
$exp parfile=yourparm.ctl
(If you can restrict the “ps” command at the operating system level.)
10- You
can use Oracle Advanced Security to encrypt data over networks.
Read This
Link
11- Make
sure to set the following environment variables to TRUE to prevent password to be revealing to
others. (ORA_ENCRYTP_LOGIN in client and server, and DBLINK_ENCRYPT_LOGIN in both servers)
12- Make
sure in UNIX add “set noexec_user_stack=1” in the /etc/system file to make the stack
non-executable.
13- Don’t
give the “ALTER SESSION” system privilege to users that they don’t need it. No way you should
give any one the “ALTER SYSTEM” system privilege unless there are DBAs.
14- Use
the following UNIX script to check to see if there are any “exp, connect or sqlplus” command
with a password in them.
# find /u01 -name “*” –print | while read filename
do
egrep
–i ‘exp|connect|sqlplus’ $filename >> exp.lis 2> /dev/null
done
#
15- Don’t
use any external files if you can. Make sure the count is zero. (SELECT count(*) FROM
dba_external_tables)
16- Be
aware of the following files that contains passwords:
File
name
|
Type
of password
|
orapwd<sid>.ora
|
Remote
login passwords
|
snmp_rw.ora
|
Intelligent
agent password
|
exported
complete dmp
|
Oracle
Hashkeys
|
htaccess
|
Apache
passwords
|
wdbsvr.app
|
Contains
mod_plsql passwords
|
webcache.xml
|
Weekly
encrypted passwords
|
listener.ora
|
Listener
passwords (encrypted or text)
|
Database
creation scripts
|
Oracle
passwords if not changed.
|
17- Make
sure to delete the “MDSYS or FINANCE” username since they are granted ALL
PRIVILEDGES.
18- Alter
default profile to have password management features.
SQL> ALTER PROFILE default
2 LIMIT
FAILED_LOGIN_ATTEMPTS 10
3 PASSWORD_LIFE_TIME 90
4 PASSWORD_REUSE_MAX 5
5 PASSWORD_GRACE_TIME 5
/
19- If
needed write a password in house verification function. The following is a sample of a function
verifies password that checks to ensure old password is not the same of new password and the length
of a new password. You can make this very complex due to your company business rules. Check also the
%ORACLE_HOME%\rdbm\admin\utlpwdmg.sql file. Then alter your profile. (ALTER
PROFILE DEFAULT limit password_verify_function verify_password)
CREATE OR REPLACE FUNCTION verify_password (
v_user
IN VARCHAR2,
v_new_pw
IN VARCHAR2,
v_old_pw
IN VARCHAR2) RETURN
BOOLEAN IS
BEGIN
IF LENGTH (v_new_pw)
< 8 THEN
RAISE_APPLICATION_ERROR(-20100,
‘Your password is too short.’);
ELSIF v_new_pw =
v_user THEN
RAISE_APPLICATION_ERROR(-20104,
‘New password same as username.’);
ELSIF v_new_pw =
v_old_pw THEN
RAISE_APPLICATION_ERROR(-20108,
‘New password same as old.’);
ELSE
RETURN(TRUE);
END IF;
END;
/
20- Lock
or drop all the username account that was not used for more certain time for ex: 90 days.
SQL> AUDIT CREATE SESSION WHENEVER SUCCESSFUL;
SQL> -- after 90 days, do the following.
SQL> SELECT distinct (u.username) FROM dba_users u
2
WHERE NOT EXISTS (SELECT ‘T’ FROM dba_audit_trail a
3
WHERE a.username = u.username and a.logoff_time > sysdate – 90)
/
21- Don’t
hardcode any password in your scribe. If you have to, make sure immediately when your job was done.
22- Make
sure that an access to the “UTL_FILE,” “UTL_TCP,” “UTL_HTTP,”
UTL_SMTP,” “DBMS_JAVA,”
“DBMS_RANDOM,” “DBMS_SQL,” “DBMS_SYS_SQL” and “DBMS_BACKUP_RESTORE” packages
weren’t granted to PUBLIC; revoke them if they are and give access to those only needed. (REVOKE
EXECUTE ON utl_file FROM PUBLIC;)
23- Revoke
access the “ALL_USERS” table from public. (REVOKE SELECT ON all_users
FROM PUBLIC;)
24- If
you don’t need c library then remove the EXTPROC (c library) from the
listener.ora.
25- Make
sure that the SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE AND DELETE_CATALOG_ROLE system privileges or
all DBA_ were granted only to DBAs. (dba_role_privs)
26- Avoid
creating account externally. Check their privileges with no SYSDBA or SYSOPER roles.
27- Make
sure that the “O7_DICTIONARY_ACCESSIBILITY” parameter is set to “FALSE.”
28- Make
sure that the “REMOTE_OS_AUTHENT” and “REMOTE_OS_ROLES” are not set to “TRUE.” These
parameters are set to “FALSE” by default.
29- Don’t
grant the “EXEMPT ACCESS POLICY” system privilege to any users, unless you have to.
30- Make
sure that no other objects except sys’s objects are in the system tablespace. If there are, please
move them to an alternative location.
SELECT owner, segment_name, segment_type FROM dba_segments
WHERE tablespace_name = ‘SYSTEM’ and owner != ‘SYS’;
31- Make
sure that an ordinary users do not have any system privileges. Also check for the “SELECT ANY
TABLE” privilege.
32- Revoke
any PUBLIC privileges on DICTIONAY objects.
33- Check
on the “RESOURCE” role. It gives unlimited tablespace on all tablespaces.
34- Be
sure to revoke the key dangerous privileges from the “RESOURCE” and “CONNECT” roles.
35- Change
at least the “IDLE_TIME” parameter of the default and users profile.
36- Prevent
any access to dba_users, sys.link$, sys.user$, and sys.user_history$ tables. These tables or views
contain users’ password.
37- Make
sure to audit the auditors by “AUDIT ALL ON sys.aud$ BY ACCESS,” if you are auditing.
38- Regularly
check the following Oracle log files.
Type
|
File/System
|
Window
OS
|
Eventviewer
|
UNIX
OS
|
syslog
|
Oracle
|
listener.log
(Connection attempts are logged)
|
Oracle
|
access_log
(Every access to Oracle)
|
Oracle
|
error_log
(Oracle errors)
|
Oracle
|
sqlnet.log
(Connection failuers)
|
Oracle
|
apache.log
(access violations)
|
39- Make
sure that the “ADMIN_RESTRICTIONS_listernername” parameter is set to “ON” to prevent the
listener from accepting SET commands while is running.
40- Set
reasonable file permissions on the listener configuration file. ($ chmod 600 listerner.ora)
|