Tag: Oracle Security

(Re)Securing Oracle Application Server Control

OracleIf you’re running an Oracle Application Server 10g instance you are probably familiar with Oracle Enterprise Manager Application Server Control. If not, go back to the manual. This is not a how-to on setting it up or using it. If you want to know how to secure it and refresh the certificate when it expires, read on.

Application Server Control is installed with Application Server 10g and typically runs on a port like 1810. By default it uses the non-secure http protocol. Since your whole application server is controlled through this interface, you probably want to secure it. The instructions below will generate a self signed certificate and get your Application Server Control up and running with https.

As usual this post is written for Oracle Application Server 10g on UNIX. Always review the documentation for your release before trying any of these steps.

Securing Application Server Control

Oracle has provided a simple way to secure Application Server Control.

Note: If $ORACLE_HOME/bin is not in your path you will need to provide this path to emctl.

1. Connect to the command line on the application server and set all the appropriate environment variables for your application instance.

2. Run the command emctl stop iasconsole to stop Application Server Control.

3. Run the command emctl secure em to secure Application Server control. This will perform a few steps including generating a self-signed secure certificate.

4. Run emctl start iasconsole to start Application Server Control.

If all goes well you will now be able to connect to your Application Server Control instance on the same port as before but now with the https protocol. In most browsers you will need to specify ‘https://’ in the URL.

Depending on your browser settings you may get a warning when accessing the site that the secure certificate was not issued by a trusted company. That is normal with a self-signed certificate. You can either tell your browser to trust the certificate or simply disregard the warning when it appears.

Renewing the Certificate

By default the certificate created in the steps above will only be good for six months. Once the cert goes stale you will probably get a warning that the certificate date is invalid. You may additionally get some java errors like below.

When this happens you can simply re-secure Application Server Control with the same steps above. This will create a new certificate which will be valid for another six months.

Some Potential Problems

If the certificate has expired you will likely get a java error like this:

IOException in sending Request :: javax.net.ssl.SSLException: SSL handshake failed: X509CertExpiredErr

If this happens simply re-secure Application Server Control with the instructions above.

Sometimes Application Server Control will not shut down properly and you may get an error like this:

IOException in sending Request :: javax.net.ssl.SSLException: SSL handshake failed: SSLIOClosedOverrideGoodbyeKiss

If this happens you will probably have to kill the enterprise manager process (look for a process called emagent) and re-secure again.

oracle, oracle application server, oracle security

Oracle Optimal Flexible Architecture Explained

OracleI have posted about the Oracle OFA (Optimal Flexible Architecture) and recent changes to OFA for Oracle Database 10g, but I wanted to go into a more practical application of these rules to act as a quick reference.

This is not intended to be a complete explanation of the OFA standard. For more complete information refer to the OFA whitepaper. The OFA Standard Recommendations below are taken directly from the OFA whitepaper.

OFA recommendations 9-11 pertain to very specific installation types I have chosen to exclude them. The examples below are based on a UNIX environment.

OFA Standard Recommendation 1: Name all mount points that will hold site specific data to match the pattern /pm where p is a string constant chosen not to misrepresent the contents of any mount point, and m is a unique fixed-length key that distinguishes one mount point from another.

Typical Application: Name operating system mount points with the convention /u01, /u02, /u03 etc.

Options: Anything can be used instead of the leading ‘u’, however be careful not to use something which could eventually misrepresent the future contents of the directory.

OFA Standard Recommendation 2:Name home directories matching the pattern /pm/h/u, where pm is a mount point name, h is selected from a small set of standard directory names, and u is the name of the owner of the directory.

Typical Application: The original OFA whitepaper states the oracle user home directory be placed in a directory like /u01/app/oracle.

Options: This rule is typically overlooked. The oracle user is usually given a home directory which matches other UNIX users, such as /export/home/oracle/ or /home/oracle, however the directory /u01/app/oracle (or similar) should be created for software installation (see recommendation 4). The recommended home directory (/u01/app/oracle) is typically referred to as $ORACLE_BASE.

OFA Standard Recommendation 3:Refer to explicit path names only in files designed specifically to store them, such as the UNIX /etc/passwd file and the Oracle oratab file; refer to group memberships only in /etc/group.

Typical Application: Whenever possible, refer to the oracle user’s home directory as ~oracle and use other environment variables such as $ORACLE_HOME and $ORACLE_BASE instead of full paths. As an example, you would typically use $ORACLE_BASE/admin instead of /u01/app/oracle/admin.

Options: ~oracle, $ORACLE_BASE and $ORACLE_HOME are typically used as environment variables. Other variables such as $ORACLE_ADMIN can be set and used in a similar fashion.

OFA Standard Recommendation 4: Store each version of Oracle Server distribution software in a directory matching the pattern h/product/v, where h is the login home directory of the Oracle software owner, and v represents the version of the software.

Typical Application: Oracle server software should be stored in a directory below the home ($ORACLE_BASE) in the format $ORACLE_BASE/product/9.2. 9.2 should be replaced with the version of Oracle software installed.

Options: This could be on any of the /u01 sequence of partitions.

NOTE: For 10g Oracle has added another level to allow multiple installs of the same version of software. The new recommendation is to install software in a directory in the format of $ORACLE_BASE/product/10.1/db_1.

OFA Standard Recommendation 5:For each database with db_name=d, store database administration files in the following
subdirectories of h/admin/d:
• adhoc — ad hoc SQL scripts for a given database
• adump — audit trail trace files
• arch — archived redo log files
• bdump — background process trace files
• cdump — core dump files
• create — programs used to create the database
• exp – database export files
• logbook — files recording the status and history of the database
• pfile — instance parameter files
• udump — user SQL trace files
where h is the Oracle software owner’s login home directory.

Typical Application: Again the $ORACLE_BASE directory should be used for the home directory, resulting in a path of $ORACLE_BASE/admin/adump

Options: Not all these directories are always used; however, if there are multiple DBAs I highly recommend the adhoc and logbook directories.

OFA Standard Recommendation 6:Name Oracle database files using the following patterns:
• /pm/q/d/control.ctl — control files
• /pm/q/d/redon.log — redo log files
• /pm/q/d/tn.dbf — data files
where pm is a mount point name, q is a string denoting the separation of Oracle data from all other files, d is the db_name OFA Standard • 21 Oracle System Performance Group Technical Paper, September 24, 1995 of the database, n is a distinguishing key that is fixed-length for a given file type, and t is an Oracle tablespace name. Never store any file other than a control, redo log, or data file associated with database d in /pm/q/d.

Typical Application: Control, redo, and data files are typically stored in one or more of the /u01 series of partitions in a subdirectory called oradata then in a folder matching the database name. The resulting path should resemble /u01/oradata/orcl.

Only control, redo, and data files should reside in this path and they should have the appropriate extensions (.ctl, .log or .dbf respectively).

Control and redo files should have a fixed length number to identify them, such as control01.ctl or redo04.log.

Data files should contain the tablespace name and a fixed length number resulting in the format system01.dbf.

Options: Many place the database name in these files. This is redundant since the database name is already in the path to these files and should be avoided as it complicates changing the database name.

OFA Standard Recommendation 7: Separate groups of segments with different lifespans, I/O request demands, and backup frequencies among different tablespaces. For each Oracle database, create the following special tablespaces in addition to those needed for applications segments:
• SYSTEM — data dictionary segments only
• TEMP — temporary segments only
• RBS — rollback segments only
• TOOLS — general-purpose tools only
• USERS — miscellaneous user segments

Typical Application: Here a segment is a piece of data in the database associated with a table, index, or other database object. Basically you should represent the tablespaces listed above and any needed for the appropriate application. Do not put application data in any of these tablespaces, but instead create one or more tablespaces for each application.

Options: TOOLS and USERS tablespaces may be omitted, however it is likely you will want them in the long run.

NOTE: For database 10g Oracle has added a new tablespace called SYSAUX which contains all non-essential system components.

OFA Standard Recommendation 8: Name tablespaces connotatively with eight or fewer characters.

Typical Application: The eight character limit is not necessary; however, tablespace names should be kept fairly short. More importantly tablespace names should be indicative of their contents. WEBCT_DATA or BANNER_INDEX_SMALL are acceptable tablespace names, but DATA or INDEX would not be.

Options: There is a lot of flexibility here. Using a consistent convention at your site is key.

oracle, sql, dba, database administration, database development, database security, database, oracle security

The Center for Internet Security – Oracle Security Benchmark

Wondering if you’re doing everything possible to secure your Oracle database? You probably aren’t, but the Center for Internet Security has compiled a practical checklist which will get you damned close!

The Center for Internet Security (CIS) is a non-profit organization which is committed to providing information on best-practices for security. The CIS does not report every vulnerability in a piece of software, but rather provides a set of best-practices for setup and configuration of systems and applications to minimize security risks.

The CIS Oracle Benchmarks provide a wealth of information on installing and configuring Oracle. Far from a step-by-step on how to install Oracle, it does fill in many of the gaps that are easily overlooked.

The CIS approach is very practical. As an example, item 2.01 from the Oracle benchmark reads as follows:

2.01
Installation
Try to ensure that no other users are connected while installing Oracle 10g

The Oracle 10g installer application could potentially
create files in a temporary directory with public
privileges. It would be possible for any local user to
delete, overwrite or corrupt these files during the
installation process. Try to ensure that no other users
are connected while installing Oracle 10g. Also set the
$TMP and $TMPDIR environment variables to a
protected directory with access given only to the Oracle
software owner and the ORA_INSTALL group.

In this compressed format the Oracle 10g Benchmarks still span 55 pages; however, these 55 pages represent the equivalent of several years of experience.

While the complete list of benchmarks offered by the CIS is relatively small it hits the high points on enterprise level software. I expect in the long run the list will grow to include more of the open-source solutions we are now finding commonplace.

So grab your favorite sys-admin and a big cup of coffee and run through this checklist. You’ll be surprised what you find. If you happen to be the DBA _and_ the sys-admin you’d better make it an extra large coffee.

oracle, oracle security, oracle 10g, oracle 9i, database administration, dba, database, database security

Changes to OFA for Oracle 10g

With Oracle 10g there has been a small but useful change made to Oracle’s Optimal Flexible Architecture.

The Optimal Flexible Architecture standards are a guideline for setting up Oracle databases to minimize downtime and maximize scalability. For more information, check out my article on OFA.

In 10g, Oracle has added one level to the ORACLE_HOME path. An Oracle home directory which was formerly /u01/app/oracle/product/9.2.0 is now one more level deeper at /u01/app/oracle/product/10g/db_1.

By adding install type and an install number as the final level of the Oracle home variable it is now possible to have two or more installs of the same version of Oracle in the same Oracle directory.

Oracle offers up the idea of using db_1, db_2 and so-on for full database releases and client_1, client_2 and so-on for client installs I can also see using this method to install Oracle applications servers in the same directory as well.

To read more about OFA, check out my earlier article on the topic and Oracle’s explanation of OFA.

oracle, sql, dba, database administration, database development, database security, database, oracle security

Tracking Oracle Logons

There are a couple ways of tracking logon and logoff information within an Oracle database, however few offer the simplicity and flexibility of the use of an logon and logoff trigger. Here is how I built a simple set of triggers and accompanying table to track usernames, logon time, logoff time, machine connected from and program used.

I performed these steps as system. If you want to install these as another user you will need to assign the appropriate permissions first.

The first step is to create a table to store the information. This table may grow quickly if this is a very active system so we want to put it somewhere other than the system tablespace. For reference, mine currently has around 3000 records in it and is around 200k in size. Of course mileage may vary.

CREATE TABLE session_audit
(
user_id VARCHAR2(30),
session_id NUMBER,
host VARCHAR2(30),
program VARCHAR2(60),
logon_time DATE,
logoff_time DATE
)
TABLESPACE users;

This will be the table you query to determine login information. Of course you will probably want to purge data from here occasionally to keep it from getting too big.

Next we create the logon trigger which will populate all but the logoff_time of the session_audit table. This uses the sys_context function to lookup the host and session id (different from the SID) of the session. The program is retrieved by a subquery on the V$SESSION table.

CREATE OR REPLACE TRIGGER tr_session_audit_logon
AFTER LOGON ON DATABASE
DECLARE
session_id number;
BEGIN
select sys_context('USERENV','SESSIONID') into session_id from dual;
IF session_id != 0 --ignore internal connections
THEN
BEGIN
INSERT INTO session_audit (
user_id,
session_id,
host,
program,
logon_time,
logoff_time
)
VALUES(
user,
session_id,
sys_context('USERENV','HOST'),
(SELECT program FROM v$session
WHERE sys_context('USERENV','SESSIONID') = AUDSID),
sysdate,
NULL
);
END;
END IF;
END;

UPDATE: I have found that the old version of this resulted in an ORA-1427 error when someone made an internal connection. The problem also came up when dbms_jobs were run. The code now ignores internal connections (where the session id is 0).

Finally we create the logoff trigger to fill in the logoff_time.

CREATE OR REPLACE TRIGGER tr_session_audit_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
UPDATE session_audit
SET logoff_time = sysdate
WHERE sys_context('USERENV','SESSIONID') = session_id;
END;

That’s it. You can now search the session_audit table for logins durring a time window or all logins for a specific user. You could even check who is using a certain application.

I mentioned one possible issue, that you don’t want the session_audit table to fill up your system tablespace, however it is also important to mention that if your logon trigger fails for some reason people will not be able to log in, so watch the space, no matter where you put it.

Donald Burleson (who desperately needs a new portrait for his website) of Burleson Consulting offers these instructions for a similar trigger, however he is grabbing more information and fills most of it in at the end.

oracle, sql, dba, database administration, database development, database security, database, oracle security