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 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.

Examples:

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.

Examples:

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

User altered.

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

User altered.

SQL> connect jemmons/abc!123;
Connected.

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;
Connected.

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

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

Useful Oracle Security Views

One of the cool things about oracle is that if you have the right privileges you can learn all it’s secrets from the so-called “data dictionary.”

If you’re interested in this then you probably already know what that’s about, so here are what I consider the important data dictionary views for Oracle security:

DBA_SYS_PRIVS – Probably most important, this show system privileges granted to users and roles

DBA_TAB_PRIVS – While the name implies table privileges this actually incorporates view privileges, procedures, packages and functions

DBA_COL_PRIVS – Not always used, but this view tracks grants on columns

DBA_ROLE_PRIVS – This shows who (or what roles) a role has been granted to

These views don’t seem to have changed much from 9i to 10g (or in 8i at that matter) but it is always best to check documentation for your version of Oracle.

Stay tuned for some handy queries to run on these to get quick reports on who can see what.

Technorati tags: , , , , , ,