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 NONESQL> show parameter O7_dictionary_accessibility
NAME TYPE VALUE
———————————— ———– ——–
O7_DICTIONARY_ACCESSIBILITY boolean TRUESQL> show parameter remote_os_roles
NAME TYPE VALUE
———————————— ———– ——–
remote_os_roles boolean TRUESQL> 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 UNLIMITED16 rows selected.
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 DBSQL> show parameter O7_dictionary_accessibility
NAME TYPE VALUE
———————————— ———– ————–
O7_DICTIONARY_ACCESSIBILITY boolean FALSESQL> show parameter remote_os_roles
NAME TYPE VALUE
———————————— ———– ————–
remote_os_roles boolean FALSESQL> 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 716 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.
