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.
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.
you should always try to keep up to date with Oracle Security
on Oracles Security Alert page on the Oracle web site.
Security and Oracle’s Customer information Site: Oracle
Metalink to check a security alerts and patches regularly.
check list to protect your database (Minimum compromising)
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
Make sure to change the orcladmin/welcome and sysman/oem_temp
passwords if you use OEM and Oracle9ias.
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.
Use SSH or SUDO to disable remote log-in to the ORACLE account.
On Unix System, change the $ORACLE_HOME/bin files’ permissions
to 0751 or less if possible.
Make sure to set REMOTE_LOGIN_PASSWORD_FILE=NONE.
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
Make sure that datafiles have only read/write accesses. ($cdmod
–R 600 /u02/oradata)
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.
/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.
for exporting do the following:
you can restrict the “ps” command at the operating system
You can use Oracle Advanced Security
encrypt data over networks.
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
Make sure in UNIX add “set noexec_user_stack=1” in the
/etc/system file to make the stack non-executable.
Don’t give the “ALTER SESSION” system privilege to users
that they don’t need it. No way you should give any one the
” system privilege unless there are
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
egrep –i ‘exp|connect|sqlplus’ $filename >>
exp.lis 2> /dev/null
Don’t use any external files if you can. Make sure the count
is zero. (SELECT count(*) FROM dba_external_tables)
Be aware of the following files that contains passwords:
(encrypted or text)
passwords if not changed.
Make sure to delete the “MDSYS or FINANCE” username since
they are granted ALL PRIVILEDGES.
Alter default profile to have password management features.
ALTER PROFILE default
2 LIMIT FAILED_LOGIN_ATTEMPTS
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
OR REPLACE FUNCTION verify_password (
VARCHAR2) RETURN BOOLEAN IS
IF LENGTH (v_new_pw) < 8 THEN
RAISE_APPLICATION_ERROR(-20100, ‘Your password is too
ELSIF v_new_pw = v_user THEN
RAISE_APPLICATION_ERROR(-20104, ‘New password same as
ELSIF v_new_pw = v_old_pw THEN
RAISE_APPLICATION_ERROR(-20108, ‘New password same as
Lock or drop all the username account that was not used for more
certain time for ex: 90 days.
AUDIT CREATE SESSION WHENEVER SUCCESSFUL;
-- after 90 days, do the following.
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)
Don’t hardcode any password in your scribe. If you have to,
make sure immediately when your job was done.
Make sure that an access to the “UTL_FILE
,” “DBMS_JAVA,” “DBMS_RANDOM
“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
Revoke access the “ALL_USERS” table from public. (REVOKE
SELECT ON all_users FROM PUBLIC;)
If you don’t need c library then remove the EXTPROC (c
library) from the listener.ora.
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)
Revoke any PUBLIC privileges on DICTIONAY objects.
Check on the “RESOURCE” role. It gives unlimited tablespace
on all tablespaces.