Category: UNIX

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

What the heck do I do with a .cpio file?

For some unknown reason, Oracle considers it necessary to distribute their UNIX software in .cpio files. Since this is the only time I ever use cpio, I can never remember the command and I always end up looking it up.

Well, for future reference, here is how you extract a .cpio file to the current directory on most platforms:

cpio -idmv < filename_to_extract.cpio

Some platforms, like AIX, may give errors like this with these options:

cpio: 0511-903 Out of phase!
cpio attempting to continue...

cpio: 0511-904 skipping 732944 bytes to get back in phase!
One or more files lost and the previous file is possibly corrupt!

cpio: 0511-027 The file name length does not match the expected value.

If you run into these you need to add the c option as the headers are stored in ASCII. The command should now look like this:

cpio -idcmv < filename_to_extract.cpio

For more information refer to the man page for cpio, but this is all I ever do with cpio. For a better UNIX archiving utility, consider tar.

UNIX timestamp to Oracle Date Conversion

Earlier today a situation came up where a UNIX timestamp (a count of the number of seconds from January 1, 1970, midnight GMT) needed to be converted into an Oracle DATE format. The Oracle TO_DATE (covered in more detail in my article Oracle, SQL, Dates and Timestamps) does not support this type of conversion.

A Google search confirmed for me that their was no easy way to make the TO_DATE function do this, but I did find this article from the Oracle + PHP Cookbook on Oracle’s site which contained this simple function to convert UNIX timestamps to Oracle dates.

[SQL]CREATE OR REPLACE
FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
/**
* Converts a UNIX timestamp into an Oracle DATE
*/
unix_epoch DATE := TO_DATE(‘19700101000000′,’YYYYMMDDHH24MISS’);
max_ts PLS_INTEGER := 2145916799; — 2938-12-31 23:59:59
min_ts PLS_INTEGER := -2114380800; — 1903-01-01 00:00:00
oracle_date DATE;

BEGIN

IF unixts > max_ts THEN
RAISE_APPLICATION_ERROR(
-20901,
‘UNIX timestamp too large for 32 bit limit’
);
ELSIF unixts < min_ts THEN RAISE_APPLICATION_ERROR( -20901, 'UNIX timestamp too small for 32 bit limit' ); ELSE oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND'); END IF; RETURN (oracle_date); END; / [/SQL] Once compiled, you can use the function to convert numerical UNIX timestamps into Oracle dates in the same manner you would use the TO_DATE function to convert text to dates. Here is an example: SQL> select unixts_to_date(1094165422) from dual;

UNIXTS_TO
---------
02-SEP-04

oracle, sql, dba, database administration, database development, unix, unix timestamp, timestamp

Oracle on Solaris: 32-bit or 64-bit

It is important for optimal performance to make sure you match up your Oracle RDBMS installation with your OS. Running a 32-bit version of Oracle on a 64-bit OS is may not give you peak performance, but also will not be able to address large segments of RAM and large files. So how do you know what your OS supports? How can you tell if that Oracle install from before you started is 64-bit? Here’s how:

Is my Operating System 64-bit?

In Solaris, from the command line (you don’t have to be root in most cases) run this command:

/usr/bin/isainfo -kv

If your OS is 64-bit, you will see output like:

64-bit sparcv9 kernel modules

If your OS is 32-bit, you will get this output:

32-bit sparc kernel modules

For Linux users

If you are running Linux, you can check your distribution with the uname command:

uname -m

The output will read x86_64 for 64-bit and i686 or similar for 32-bit.

How about this Oracle install? Is it 64-bit?

The question here is weather your Oracle binaries are 64-bit. While some of the binaries associated with Oracle may be 32-bit, the important ones will be 64 bit. To check those, follow these steps from the command line:

cd $ORACLE_HOME/bin
file oracl*

This will display the file type of your oracle binaries. If you are running 64-bit binaries, the output should look like this:

oracle: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped
oracleO: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped

If your binaries are 32-bit, the output will look like this:

oracle: ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped

If you find you are running 32-bit and decide to go to 64 be careful. The switch can be a bit tricky. Read the documentation closely and make sure your service contract is payed up!

oracle, dba, database administration, database, solaris, linux, sun, sun microsystems, 32-bit, 64-bit

Search UNIX without the junk

If you have done much UNIX systems administration you have probably seen output like this from the ‘find’ command:

$ find / -name lifeaftercoffee.com
find: /proc/tty/driver: Permission denied
find: /proc/sys/kernel/pax: Permission denied
find: /proc/net: Permission denied
find: /proc/4680/fd: Permission denied
find: /usr/local/dh/apache/logs/basic-argon/fastcgi: Permission denied
find: /usr/local/dh/apache/logs/basic-bongo/fastcgi: Permission denied
find: /usr/local/dh/apache/logs/basic-cabo/fastcgi: Permission denied
find: /usr/local/dh/apache/logs/basic-dap/fastcgi: Permission denied
find: /usr/local/dh/apache/logs/basic-adamant/fastcgi: Permission denied
find: /usr/local/dh/apache/logs/basic-emu/fastcgi: Permission denied
find: /usr/local/dh/apache/logs/basic-fritz/fastcgi: Permission denied
find: /usr/local/dh/apache/logs/basic-grog/fastcgi: Permission denied
…

Annoying, to say the least, that your actual search results may be buried in pages upon pages of this.

Here’s a quick way around this. Redirect the error output to /dev/null (the black hole of data.) It’s as simple as appending ‘2>/dev/null’ to the end of the command.

$ find ./ -name lifeaftercoffee.com 2>/dev/null
/home/jonemmons/logs/lifeaftercoffee.com
/home/jonemmons/lifeaftercoffee.com

Any errors are ignored, which can complicate troubleshooting, but if things aren’t doing what you want them to, just drop the redirect and run the command to see the errors again.

The command may vary depending on your shell and breed of UNIX, but this has always worked for me.

Easy Linux CommandsFor more tips like this check out my book Easy Linux Commands, only $19.95 from Rampant TechPress.

Technorati tags: , , , ,