Securing Oracle databases

Here is a checklist of things to do to secure an Oracle database.

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:\Oracle\Ora8i_816\network\admin\listener.ora
                   Old parameter file - D:\Oracle\Ora8i_816\network\admin\listener.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.

This was first published in April 2003

Dig deeper on Oracle database security

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close