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;)

2-     Make sure to change the orcladmin/welcome and sysman/oem_temp passwords if you use OEM

3-     Make sure to have a secure ORACLE password.  

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

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

#

12- Don’t use any external files if you can. Make sure the count is zero. (SELECT count(*) FROM dba_external_tables)

13- Be aware of the following files that contains passwords:

14- Alter default profile to have password management features.

SQL> ALTER PROFILE default

  LIMIT FAILED_LOGIN_ATTEMPTS 10

  PASSWORD_LIFE_TIME 90

  PASSWORD_REUSE_MAX 5

  PASSWORD_GRACE_TIME 5

/

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;

15- 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;

16- 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)

            /

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;)

18- Revoke access the “ALL_USERS” table from public. (REVOKE SELECT ON all_users FROM PUBLIC;)

19- If you don’t need c library then remove the EXTPROC (c library) from the listener.ora.

20- 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)

21- Avoid creating account externally. Check their privileges with no SYSDBA or SYSOPER roles.

22- Make sure that the “O7_DICTIONARY_ACCESSIBILITY” parameter is set to “FALSE.”

23- Make sure that the “REMOTE_OS_AUTHENT” and “REMOTE_OS_ROLES” are not set to “TRUE.” These parameters are set to “FALSE” by default.

24- Don’t grant the “EXEMPT ACCESS POLICY” system privilege to any users, unless you have to.

25- 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’;

26- Make sure that an ordinary users do not have any system privileges. Also check for the “SELECT ANY TABLE” privilege.

27- Revoke any PUBLIC privileges on DICTIONAY objects.

28- Check on the “RESOURCE” role. It gives unlimited tablespace on all tablespaces.

29- Be sure to revoke the key dangerous privileges from the “RESOURCE” and “CONNECT” roles.

30- Change at least the “IDLE_TIME” parameter of the default and users profile.

31- Prevent any access to dba_users, sys.link$, sys.user$, and sys.user_history$ tables. These tables or views contain users’ password.

32- Make sure to audit the auditors by “AUDIT ALL ON sys.aud$ BY ACCESS,” if you are auditing.

33- Regularly check the following Oracle log files.

34- Make sure that the “ADMIN_RESTRICTIONS_listernername” parameter is set to “ON” to prevent the listener from accepting SET commands while is running.

35- Set reasonable file permissions on the listener configuration file. ($ chmod 600 listerner.ora)

36- Ensure that the listener password has been set.

$ lsnrctl

$ LSNRCTL> change_password  (Notice automatic start/stop is a big problem.

37- Disable logging to listener.log or sqlnet.log if that is possible.

LOGGING_listener = OFF or lsnrctl set log_status off

38- Never have a link to production database from test or development database. Use exp/imp utilities to copy the files.

39- If you have hardcoded procedures, you should wrap your functions, procedures, and packages source programs.
wrap iname=myproc.sql oname=myprocx.sql