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.
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
Make sure to have a secure
On Unix System, change the
$ORACLE_HOME/bin files’ permissions to 0751 or less if possible.
Make sure to set
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
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.
$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:
(If you can restrict the “ps” command at the
operating system level.)
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
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
egrep –i ‘exp|connect|sqlplus’ $filename >> exp.lis 2>
Don’t use any external
files if you can. Make sure the count is zero. (SELECT count(*) FROM
Be aware of the following
files that contains passwords:
Type of password
Remote login passwords
Intelligent agent password
exported complete dmp
Contains mod_plsql passwords
Weekly encrypted passwords
Listener passwords (encrypted or text)
Database creation scripts
Oracle passwords if not changed.
Alter default profile to
have password management features.
SQL> ALTER PROFILE default
Password aging, expiration and history is managed via profiles, as shown below.
CONN sys/password AS SYSDBA
CREATE PROFILE my_profile LIMIT
FAILED_LOGIN_ATTEMPTS 3 -- Account locked after 3 failed logins.
PASSWORD_LOCK_TIME 5 -- Number of days account is locked for. UNLIMITED required explicit unlock by DBA.
PASSWORD_LIFE_TIME 30 -- Password expires after 90 days.
PASSWORD_GRACE_TIME 3 -- Grace period for password expiration.
PASSWORD_REUSE_TIME 120 -- Number of days until a specific password can be reused. UNLIMITED means never.
PASSWORD_REUSE_MAX 10 -- The number of changes required before a password can be reused. UNLIMITED means never.
ALTER USER scott PROFILE my_profile;
Lock Or Unlock Accounts with:
ALTER USER scott ACCOUNT LOCK;
ALTER USER scott ACCOUNT UNLOCK;
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
CREATE OR REPLACE FUNCTION verify_password (
VARCHAR2) RETURN BOOLEAN IS
LENGTH (v_new_pw) < 8 THEN
RAISE_APPLICATION_ERROR(-20100, ‘Your password is too short.’);
v_new_pw = v_user THEN
RAISE_APPLICATION_ERROR(-20104, ‘New password same as
v_new_pw = v_old_pw THEN
RAISE_APPLICATION_ERROR(-20108, ‘New password same as old.’);
Lock or drop all the
username account that was not used for more certain time for ex: 90
SQL> AUDIT CREATE SESSION WHENEVER
SQL> -- after 90 days, do the following.
SQL> SELECT distinct (u.username) FROM
2 WHERE NOT EXISTS (SELECT ‘T’ FROM dba_audit_trail a
3 WHERE a.username = u.username and a.logoff_time > sysdate –
17- 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;)
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.
Avoid creating account
externally. Check their privileges with no SYSDBA or SYSOPER roles.
Make sure that the
“O7_DICTIONARY_ACCESSIBILITY” parameter is set to “FALSE.”
Make sure that the
“REMOTE_OS_AUTHENT” and “REMOTE_OS_ROLES” are not set to “TRUE.” These
parameters are set to “FALSE” by default.
Don’t grant the “EXEMPT
ACCESS POLICY” system privilege to any users, unless you have to.
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 !=
Make sure that an ordinary
users do not have any system privileges. Also check for the “SELECT ANY
Revoke any PUBLIC
privileges on DICTIONAY objects.
Check on the “RESOURCE”
role. It gives unlimited tablespace on all tablespaces.
Be sure to revoke the key
dangerous privileges from the “RESOURCE” and “CONNECT” roles.
Change at least the
“IDLE_TIME” parameter of the default and users profile.
Prevent any access to
dba_users, sys.link$, sys.user$, and sys.user_history$ tables. These
tables or views contain users’ password.
Make sure to audit the
auditors by “AUDIT ALL ON sys.aud$ BY ACCESS,” if you are auditing.
Regularly check the
following Oracle log files.
listener.log (Connection attempts are logged)
access_log (Every access to Oracle)
error_log (Oracle errors)
sqlnet.log (Connection failuers)
apache.log (access violations)
Make sure that the
“ADMIN_RESTRICTIONS_listernername” parameter is set to “ON” to prevent
the listener from accepting SET commands while is running.
Set reasonable file
permissions on the listener configuration file. ($ chmod 600
Ensure that the listener
password has been set.
$ LSNRCTL> change_password
(Notice automatic start/stop is a big problem.
Disable logging to
listener.log or sqlnet.log if that is possible.
LOGGING_listener = OFF or lsnrctl set
Never have a link to
production database from test or development database. Use exp/imp
utilities to copy the files.
If you have hardcoded
procedures, you should wrap your functions, procedures, and packages
wrap iname=myproc.sql oname=myprocx.sql