Securing Oracle databases

In order to properly secure your Oracle databases, the following steps are necessary. I tested these on Oracle 8.1.6 and 8.1.7.

1. Check profiles
   
   
   • Check the DB user

           select * from all_users;
     

   • Check the system privilege for PUBLIC grants

           select * from dba_sys_privs
           where GRANTEE = 'PUBLIC'
           or privilege like '%ANY%';
     

   • Check Profiles available on the server

           select * from dba_profiles;
     

   • Decide your profile method and create new profile if necesary

              CREATE PROFILE "<PROFILE_NAME>" LIMIT
                 CPU_PER_SESSION           DEFAULT
                 CPU_PER_CALL              DEFAULT
                 CONNECT_TIME              DEFAULT
                 IDLE_TIME                 DEFAULT
                 SESSIONS_PER_USER         DEFAULT
                 LOGICAL_READS_PER_SESSION DEFAULT
                 LOGICAL_READS_PER_CALL    DEFAULT
                 PRIVATE_SGA               DEFAULT
                 COMPOSITE_LIMIT           DEFAULT
                 FAILED_LOGIN_ATTEMPTS     DEFAULT
                 PASSWORD_LOCK_TIME        DEFAULT
                 PASSWORD_GRACE_TIME       DEFAULT
                 PASSWORD_LIFE_TIME        DEFAULT
                 PASSWORD_REUSE_MAX        DEFAULT
                 PASSWORD_REUSE_TIME       DEFAULT
                 PASSWORD_VERIFY_FUNCTION  DEFAULT;
     

   LIMIT Explain Table
   ===================
   
   CPU_PER_SESSION           Total amount of CPU time allowed in a
                             session. The limit is expressed in seconds.
   
   CPU_PER_CALL              Total amount of CPU time allowed in a
                             call (a parse, execute, or fetch).
                             The limit is expressed in seconds.
   
   CONNECT_TIME              Maximum elapsed time allowed for a
                             session. The limit is expressed in minutes.
   
   IDLE_TIME                 Maximum idle time allowed in a session.
                             Idle time is a continuous period of inactive
                             time during a session. Long-running queries and
                             other operations are not subject to this limit. The
                             limit is expressed in minutes.
   
   SESSIONS_PER_USER         Maximum number of concurrent
                             sessions allowed for a user.
   
   LOGICAL_READS_PER_SESSION Total number of data block reads
                             allowed in a session. The limit includes 
                             blocks read from memory and disk.
   
   LOGICAL_READS_PER_CALL    Maximum number of data block reads
                             allowed for a call (a parse, execute,
                             or fetch) to process a SQL statement.
   
   PRIVATE_SGA               Maximum amount of private space a
                             session can allocate in the shared
                             pool of the System Global Area (SGA). The 
                             Private SGA limit applies only if
                             you are using the multi-threaded
                             server architecture. The limit is expressed in  
                             kilobytes (Kbytes).
   
   COMPOSITE_LIMIT           Total resource cost for a session.
                             The resource cost for a session is the
                             weighted sum of the CPU time used in the session, 
                             the connect time, the number of reads made in the
                             session, and the amount of private SGA space 
                             allocated.
   
   FAILED_LOGIN_ATTEMPTS     Limits the number of failed logon attempts
                             allowed before a user is locked out
                             from the account.
   
   PASSWORD_LOCK_TIME        Specifies the number of days the account is
                             locked after failing the specified
                             number of logon attempts. If UNLIMITED is specified,
                             only the database administrator can unlock the 
                             account.
   
   PASSWORD_GRACE_TIME       Limits the number of days during which a
                             password can be changed following the first
                             successful login after password expiration.
   
   PASSWORD_LIFE_TIME        Limits the number of days after which a
                             password expires.
   
   PASSWORD_REUSE_MAX        Specifies the number of times a password must
                             be changed before it can be reused.
   
   PASSWORD_REUSE_TIME       Limits the number of days before a password
                             can be reused after it expires.
   
   PASSWORD_VERIFY_FUNCTION  Allows a PL/SQL routine to be used for password
                             verification when users who are assigned this profile
                             log into a database. This PL/SQL routine must be
                             locally available for execution on the database to  
                             which this profile applies. Oracle provides a default
                             script (utlpwdmg.sql), however, you can also create 
                             your own routine, or use third-party software as an
                             alternative. The password verification routine must 
                             be owned by SYS.
   
   NULL                      No password verification is set by default.
   

2. Change DEFAULT users' passwords
   
   
   • Change DEFAULT passwords. Login as sys user

     alter user <USER_NAME> identified by <PASSWORD>;
     

3. Set/Change LISTENER password
   
   
   • Run LSNRCTL utility from command line

     lsnrctl

     Result:

     LSNRCTL for 32-bit Windows: Version 8.1.6.0.0 - Production on 06-JAN-2002 19:54:18
     (c) Copyright 1998, 1999, Oracle Corporation.  All rights reserved.
     Welcome to LSNRCTL, type "help" for information.
     LSNRCTL>
     

   • Enter change_password - Result:

                  Old password: (Enter the old password and press ENTER. If there isn't one, press ENTER)
                  New password: (Enter a new password)
                  Reenter new password: (Reenter the password)
     
                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<SID>)(PORT=1521)))
                  Password changed for LISTENER
                  The command completed successfully
                  LSNRCTL>
     

   • Enter: set password - To activate the password. Result:

                  Password: (Enter the password and press ENTER)
                  The command completed successfully
                  LSNRCTL>
     

   • Enter: save_config - To save the LISTENER configuration. Result:

                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<SID>)(PORT=1521)))
                  Saved LISTENER configuration parameters.
                  Listener parameter file - D:OracleOra8i_816
     etworkdminlistener.ora
                  Old parameter file - D:OracleOra8i_816
     etworkdminlistener.bak
                  The command completed successfully
                  LSNRCTL>
     

   • To START/STOP the LISTENER:

           -- lsnrctl
           -- set password
           -- Enter the password
           -- start/stop
     

Reader Feedback

Dave K. writes: This tip contains incomplete, useless, and erroneous information. While the goal (improving security) is a noble one, this tip needs work.

For example, under #1 (Check Profiles): What is the first bullet supposed to mean? What is to be checked? Second bullet: OK, but the author says nothing about what to do with the result of this query. Third bullet: So what? Now you have a list of what you should already know. Fourth bullet: Shows how to create a completely useless profile, which actually already exists as the "DEFAULT" profile anyway. The list that follows is helpful, but is mis-labeled ("LIMIT Explain Table" - this has NOTHING to do with the Explain Table!), and is just a set of definitions out of the documentation.

Under number 2, it is of little use to say to change the DEFAULT users' passwords (BTW, there is no such thing, but he means the users pre-defined by Oracle), but he should tell the reader what they are.

Number 3 is very poorly explained.

I'll stop there. In my opinion, an experienced DBA would be able to figure all of this out, but will already know all of this anyway. The inexperienced DBA will be completely confused and frustrated by this tip.

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.