Oracle 11g Security – Enabling default security settings

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

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles