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:

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

Performing Math on Oracle Dates

Here are some quick and dirty examples for adding and subtracting days and months and finding the difference between dates in Oracle.

These examples select the result from the ‘dual’ table. Dual is a dummy table that exists in all Oracle databases. I am also using sysdate heavily. Sysdate simply returns the current date and time.

Adjusting Days, Weeks, Hours and Minutes

To add and subtract days from a date we simply use + or -. Here are some examples:

SQL> SELECT sysdate + 7 FROM dual;


SQL> SELECT sysdate - 30 FROM dual;


SQL> SELECT to_char(sysdate - 14, 'MM/DD/YYYY HH:MI AM') FROM dual;

11/15/2005 09:41 AM

In the first example we see that the query returns 7 days from today. The second one shows 30 days before today. In the third query I have added the to_char function so we see hour and minute. This shows us that while the date has changed, the time has not.

The first and third examples above also show how adding days would be used to add weeks. If you wanted to simplify this you could add the logic into the query like this:

SQL> SELECT sysdate + (7 * 2) FROM dual;


Likewise to work with hours you would use fractional days. The simplest way to show this is by dividing the hours by 24 right in the query. The parentheses may not be necessary, but I feel they make the query a little clearer.

Here I show the current time, then the current time plus two hours:

SQL> SELECT to_char(sysdate, 'HH:MI AM') FROM dual;

09:48 AM

SQL> SELECT to_char(sysdate + (2/24), 'HH:MI AM') FROM dual;

11:48 AM

An alternative to this method is to use the numtodsinterval function. The example above can instead be written like this:

to_char(sysdate + numtodsinterval(2, 'HOUR'), 'HH:MI AM')
FROM dual;

11:57 AM

Here the numtodsinterval function is doing the work of dividing 2/24 for hours. Valid options for the numtodsinterval are ‘DAY’, ‘HOUR’, ‘MINUTE’, or ‘SECOND’. Here is an example using ‘MINUTE’. When working with minutes the numtodsinterval function is much more readable.

to_char(sysdate + numtodsinterval(45, 'MINUTE'), 'HH:MI AM')
FROM dual;

10:47 AM

Adjusting Months and Years

To work with months and years (either of which may have a varying number of days) Oracle has provided the function numtoyminterval. This works much like the numtodsinterval function mentioned above by taking a number and a string. Valid options for the string are ‘YEAR’ or ‘MONTH’.

to_char(sysdate + numtoyminterval(5, 'MONTH'), 'MM/DD/YYYY')
FROM dual;


to_char(sysdate + numtoyminterval(2, 'YEAR'), 'MM/DD/YYYY')
FROM dual;


Comparing dates

Let’s say we want to compare some dates and find out how many days or weeks between them. To simply see the result in days we can use the minus operator like this:

TO_DATE('12/25/2005', 'MM/DD/YYYY') - sysdate
FROM dual;


The result is expressed in days including fractional hours. Of course if we wanted weeks we could just divide by 7. Similarly if we’re looking for hours we could multiply by 24, but if we want months we need to use the months_between function.

months_between(sysdate, to_date('01/01/2006', 'MM/DD/YYYY'))
FROM dual;


Finding the Latest or Earliest Date in a Set

The Oracle functions greatest and least can be used on dates to return the latest or earliest date.

greatest(sysdate, to_date('09/11/2005','MM/DD/YYYY'),
FROM dual;


least(sysdate, to_date('09/11/2005','MM/DD/YYYY'),
FROM dual;


What’s the Last Day in a Given Month

The last_day function can be used to return the last day in a given month.

SQL> select last_day(sysdate) from dual;


Next Day of the Week After a Date

The next_day function gives the date of the next occurrence of a day of the week (‘Monday’, ‘Tuesday’, etc.) after a given date. Here we see the date of the next Sunday after today:

SQL> select next_day(sysdate,'Sunday') from dual;


Still haven’t found what you’re looking for?

You may also want to look at these other articles:

Converting Time Zones in Oracle
Oracle, SQL, Dates and Timestamps
UNIX timestamp to Oracle Date Conversion

oracle, sql, dba, database administration, database development

Temp Space Usage in Oracle

Don’t just grow your TEMP tablespace, examine it!

Recently I was facing a problem not unusual in high-throughput Oracle databases… a full TEMP tablespace. I’ve run into this before and it can be frustrating trying to determine where all the space went. In this case we are using a 6 gig temporary tablespace and for some reason it was full.

Well, all the gorey details aside, one thing that helped me determine what was going on was this tip from Oracle which contains code to examine who is using your temporary tablespace up.

By running the following command (reprinted here for easy reference and because there are nasty line breaks on Oracle’s site) you get a fairly good idea of the usage of temporary space by tablespace, username and amount used.

set pagesize 10000
set linesize 133
column tablespace format a15 heading 'Tablespace Name'
column segfile# format 9,999 heading 'File|ID'
column spid format 9,999 heading 'Unix|ID'
column segblk# format 999,999,999 heading 'Block|ID'
column size_mb format 999,999,990.00 heading "Mbytes|Used"
column username format a15
column program format a15

select b.tablespace, b.segfile#, b.segblk#,
round(((b.blocks*p.value)/1024/1024),2) size_mb,
a.sid, a.serial#, a.username, a.osuser, a.program, a.status
from v$session a, v$sort_usage b, v$process c, v$parameter p
where'db_block_size' and a.saddr = b.session_addr
and a.paddr=c.addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;

While I consider rewriting this into a somewhat more palatable form, on the occasion that I need this I don’t typically care about nice formatting… I just want the numbers in a hurry.

To clear up unused TEMP space Oracle also recommends running this as sysdba:

alter tablespace temp default storage(pctincrease 0);

oracle, dba, database, database administration, database administrator, database tuning

What Are the Default Restrictions on Oracle Passwords?

What are the valid characters for Oracle passwords? This is a more complicated question than you would think. Here are the basic restrictions on Oracle passwords. I believe these apply to all (8i or later) Oracle database versions; however I did these examples in a 10gR2 instance.

First the rules:

Passwords can be from 1 to 30 characters.

The first character in an Oracle password must be a letter.

Only letters, numbers, and the symbols “#”, “_” and “$” are acceptable in a password.


SQL> alter user jemmons identified by abc123;

User altered.

SQL> alter user jemmons identified by 123abc;
alter user jemmons identified by 123abc
ERROR at line 1:
ORA-00988: missing or invalid password(s)

SQL> alter user jemmons identified by abc!123;
alter user jemmons identified by abc!123
ERROR at line 1:
ORA-00922: missing or invalid option

In the first example we see a password that meets all the rules. The second password starts with a number and therefore fails. The third example contains the special character “!” and fails.

Now the exception:

By placing double quotes around a password you can use most standard ASCII characters in a password.


SQL> alter user jemmons identified by "123abc";

User altered.

SQL> connect jemmons/123abc;
SQL> alter user jemmons identified by "abc!123";

User altered.

SQL> connect jemmons/abc!123;

We see that the quotes let us work around some of these restrictions. This is useful if you are working in a fairly simple environment, however if it is possible that a user’s password could be changed by means which you do not control it is likely doing this would cause more confusion than it would solve.

It is also worth note that capitalization is not considered when storing Oracle passwords. Here’s an example:

SQL> alter user jemmons identified by ABC123;

User altered.

SQL> connect jemmons/abc123;

Before the password is encrypted into the database it is put in all caps. When you enter a password for authentication it is capitalized so it can be compared against the stored encrypted password.

oracle, dba, database, database administration, database security, database administrator

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