Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Aug 20, 2009

Oracle 11g Security - Enabling default security settings

By James Koopmann

As we all know, Oracle has quite a few 2-Day topical books *2 Day DBA, Performance Tuning Guide, RAC Guide, Data Replication and Integration Guide, and the 2 Day + Security Guide) that step us through the initial concepts of each topic. Well, with security in general being such is a hot topic these days; I was prompted to take a peek at the 2 Day + Security Guide, walk through of each topic and bring you what I found. So, in the next few installments, I’ll be writing about Oracle security. Go take a peek at the 2 Day + Security Guide and follow along with each installment.

As a warning, the 2 Day + Security Guide primarily uses Oracle’s Enterprise Manager to perform administrative tasks and requires the reader to have read the 2 Day + DBA Guide. No worries if you have issues with either of these as I’ll venture off of Enterprise Manger where appropriate and provide the DBA background where necessary. The Guide also recommends that you have some exposure to SQL*Plus to execute various PL/SQL code and know about Database Configuration Assistant (DBCA) and Oracle's Net Manager. If you've created any databases in the past, you probably have enough experience to forge ahead. If not, then I’ll add insight where needed.

Assuming we have all installed the Oracle software and have a running database instance, it is only logical to ask ourselves, before that database is used, if it is secure. Securing the database installation and configuration is the first topic(s) we will tackle here. After all, without a secured installation and configuration most every other task we try in securing our database will be futile. The method used in the 2 Day + Security Guide is to secure an installation and configuration involves locking down or restricting areas of access to specific database files.

To enable default profile security settings using DBCA:

1.  Bring up the Database Configuration Assistant GUI. All that is typically required here is to log into the database system as the Oracle user and execute the dbca command—typically located at $ORACLE_HOME/bin/dbca. Click the Next button to bring up the Operations window.

2.  Select the Configure Database Options option and then click the Next button to bring up the Database window.

3.  Select the Database you’d like to configure and then click the Next button to bring up the Database Content window. A small popup window will appear that retrieves information from the database; no alarm needed.

4.  Click the Next button to bring up the Security Settings window.

5.  Depending on how you’ve created your database initially, the Security Settings screen could take on two different appearances; one screen image if you had configured the database with pre-11g default security settings and a second screen image if you actually have already configured your database to make use of the enhanced 11g default security settings.

  • Pre-11g security settings screen image
  • With pre-11 g security settings, you can basically keep the pre-11g settings or opt to apply the enhanced 11g default security settings.

    So, what exactly are we opting to effect if we enable the enhanced 11g default security settings? The 2 Day guide explicitly points out a few init.ora parameters and some defaults within the password profile. Here is what those fields look like if we use SQL*Plus. Just remember your output may vary depending on if you have explicitly modified any of these.

    SQL> show parameter audit_trail
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- --------
    audit_trail                          string      NONE
    
    SQL> show parameter O7_dictionary_accessibility
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- --------
    O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE
    
    SQL> show parameter remote_os_roles
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- --------
    remote_os_roles                      boolean     TRUE
    
    SQL> SELECT * FROM DBA_PROFILES  
          WHERE profile       = 'DEFAULT'
            AND resource_type = 'PASSWORD';
    PROFILE    RESOURCE_NAME                    RESOURCE LIMIT
    ---------- -------------------------------- -------- -------------
    DEFAULT    FAILED_LOGIN_ATTEMPTS            PASSWORD 10
    DEFAULT    PASSWORD_LIFE_TIME               PASSWORD UNLIMITED
    DEFAULT    PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
    DEFAULT    PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
    DEFAULT    PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
    DEFAULT    PASSWORD_LOCK_TIME               PASSWORD UNLIMITED
    DEFAULT    PASSWORD_GRACE_TIME              PASSWORD UNLIMITED
    
    16 rows selected.
    
  • 11g security settings screen image
  • With the 11g default security settings enabled you can either keep the current settings or opt to revert to pre-11g default security. Interesting here, that is not an option in the pre-11g security screen is, when we select to revert, there are two additional options to revert audit settings and revert password profiles. While I wouldn’t suggest this approach, if you wanted to revert only one of the options this is possible.

6.  Assuming that your database is using pre-11g security settings and you want to use the new enhanced 11g default security, select the option to Apply the enhanced 11g default security settings as recommended by Oracle. Then click the Next button to display the Connection Mode screen.

7.  Click the Finish button to initiate the changing of security settings.

8.  A small pop-up window will ask if you wish to have DBCA restart you database for you. This is an option you will have to make for yourself. I personally like to restart my database manually. Plus I can choose the time and not affect any other users on the system. So click the Yes or No button depending on your preference.

9.  Click the OK button to start the process of setting the security settings.

10.  Watch Oracle work. Don’t know if I would want to click that Stop button. There really isn’t much going on behind the scenes (in the way of setting parameters and profiles) so just let it run. Plus, I noticed that there is no indication within this process of when DBCA will actually be restarting the database. So Stopping or aborting the process could be more problematic than it is worth.

11.  When complete, Oracle will display a box asking if you want to perform any additional configuration changes. Click the No button and DBCA will exit.

So what exactly have we accomplished? In effect, all this procedure has done is set a few init.ora parameters and change a few defaults within the password profile. Here is what they would look like after enabling the new 11g default security settings.

SQL> show parameter audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
audit_trail                          string      DB

SQL> show parameter O7_dictionary_accessibility
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

SQL> show parameter remote_os_roles
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
remote_os_roles                      boolean     FALSE

SQL> SELECT * FROM DBA_PROFILES  
      WHERE profile       = 'DEFAULT'
        AND resource_type = 'PASSWORD';
PROFILE    RESOURCE_NAME                    RESOURCE LIMIT
---------- -------------------------------- -------- ----------
DEFAULT    FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT    PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT    PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT    PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT    PASSWORD_LOCK_TIME               PASSWORD 1
DEFAULT    PASSWORD_GRACE_TIME              PASSWORD 7

16 rows selected.

Some might question having to go through a GUI just to set such a small number of items. After all, we could just have easily issued the following commands followed by a database restart:

SQL> ALTER SYSTEM SET remote_os_roles=FALSE SCOPE=SPFILE;
SQL> ALTER SYSTEM SET remote_os_roles=FALSE SCOPE=SPFILE;
SQL> ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=FALSE SCOPE=SPFILE;
SQL> ALTER SYSTEM SET audit_trail=DB SCOPE=SPFILE;
SQL> ALTER PROFILE default LIMIT
  2        PASSWORD_LIFE_TIME  180
  3        PASSWORD_LOCK_TIME  1
  4        PASSWORD_GRACE_TIME 7;

I think the answer to the question is really two fold. Firstly, you can never really be sure what Oracle might be doing under the covers or what might be done in the future. Secondly, you have to ask yourself if your knowledge of Oracle and database security is toned enough to know that these few parameters should be set. After all, who wants to read the manuals from cover to cover? I think the option to enable and disable options through a GUI is a good idea—ultimately linking a knowledgebase to database operations. Stay tuned, as I’ll be visiting more Oracle security options and features.

» See All Articles by Columnist James Koopmann



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date