Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...


. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |


Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals II


Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 | Lesson 27 | Lesson 28 | Lesson 29 |


Lesson 29

"The world is a dangerous place to live, not because of the people who are evil, but because of the people who don't do anything about it." 

-Albert Einstein



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


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


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


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.


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


            egrep "i "exp|connect|sqlplus" $filename >> exp.lis 2> /dev/null




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


Remote login passwords


Intelligent agent password

exported complete dmp

Oracle Hashkeys


Apache passwords

Contains mod_plsql passwords


Weekly encrypted passwords


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.








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)


 v_user             IN        VARCHAR2,

 v_new_pw      IN        VARCHAR2,

 v_old_pw        IN        VARCHAR2) RETURN BOOLEAN IS


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



   END IF;




20- Lock or drop all the username account that was not used for more certain time for ex: 90 days.


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- Revoke any PUBLIC privileges on DICTIONAY objects.


27- Check on the "RESOURCE" role. It gives unlimited tablespace on all tablespaces.