Tip

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
      

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.