Series: Project Lockdown

Phase 1

Duration: One Day

It's Phase 1 of your security and compliance project. Let's see what you can do within 24 hours to lock-down your infrastructure.

Download: Phase 1 Checklist (PDF)

Covered in this Installment:

• 1.1 Remove Default Passwords
• 1.2 Remove Case-sensitive Passwords
• 1.3 Configure Oracle Binary Permissions
• 1.4 Secure Other Executables
• 1.5 Change DBSNMP Password
• 1.6 Limit SYSDBA Login
• 1.7 Create a Listener Password
• 1.8 Protect the Listener
• 1.9 Trim Sweeping Privileges
• 1.10 Move Audit Trail to a Different Tablespace

1.1 Remove Default Passwords

Background

During Oracle software installation and database creation, it is common for accounts to be created and then forgotten. These accounts, which often carry default passwords (such as “tiger” for SCOTT), are favored entry points for intruders. You would be shocked to hear how many production database installations I have audited that use change_on_install or oracle as the password for SYS. Your first line of action should be to immediately identify and remove these default passwords.

Strategy

In Oracle Database 11g, this activity has become extremely easy, almost to the point of being trivial. The database has a special view, dba_users_with_defpwd, that lists the usernames with the default passwords. Here is an example usage:

SQL> select * from dba_users_with_defpwd
2  /            
   
USERNAME
------------------------------
DIP
MDSYS
XS$NULL
SPATIAL_WFS_ADMIN_USR
CTXSYS
OLAPSYS
OUTLN
OWBSYS
SPATIAL_CSW_ADMIN_USR
EXFSYS
ORACLE_OCM
… output truncated …

The output clearly shows the usernames that have the default password. You can join this view with DBA_USERS to check on the status of the users:

1  select d.username, account_status
2  from dba_users_with_defpwd d, dba_users u
3* where u.username = d.username
SQL> /
       
   
USERNAME            ACCOUNT_STATUS
------------------------------     --------------------------------
PM                  EXPIRED & LOCKED
OLAPSYS             EXPIRED & LOCKED
BI                  EXPIRED & LOCKED
SI_INFORMTN_SCHEMA  EXPIRED & LOCKED
OWBSYS              EXPIRED & LOCKED
XS$NULL             EXPIRED & LOCKED
ORDPLUGINS          EXPIRED & LOCKED
APPQOSSYS           EXPIRED & LOCKED
… output truncated …

In versions prior to Oracle Database 11g, how do you identify the accounts with default passwords? One option is to try to log into the account using the default password—but this is definitely a cumbersome approach, not to mention a time-consuming one.

Fortunately, there is a more elegant option. Take a look at the password column in the view DBA_USERS:

SQL> select username, password
2  from dba_users
3  where username = 'SCOTT';
USERNAME                       PASSWORD
------------------------------ ------------------
SCOTT                          F894844C34402B67

The password is hashed and thus undecipherable, but we know that SCOTT’s password is tiger. Therefore, the hash value for tiger when userid is SCOTT is F894844C34402B67. Now, if SCOTT’s password changes, this hash value also changes. You can then confirm in the view DBA_USERS to see if SCOTT’s password matches this hash value, which will verify the password as tiger. Note, however, that the hash value is not a hash value of the password itself; if another user has the password tiger, that hash value will be different.

SQL> create user scott2 identified by tiger;
       
User created. 
       
SQL> select username, password
 2  from dba_users
 3  where username = 'SCOTT2';
 
       
USERNAME                       PASSWORD
------------------------------ --------------------
SCOTT2                         C44C11D4C34DB67D

Note the different hash value (C44C11D4C34DB67D), even though the password is identical.

So how can you use this information? It’s simple. If you create the default users with default passwords, you will come to know the hash values of those passwords. Then you can build a table of such accounts and the hashed values of the default passwords and compare them against the password hashes stored in the data dictionary.

In January 2006, Oracle made a downloadable utility available for identifying default passwords and their users. This utility, available via a patch 4926128 is available on OracleMetaLink as described in the document ID 361482.1. As of this writing, the utility checks a handful of default accounts in a manner similar to that described above; by the time you read this, however, its functionality may well have expanded.

Furthermore, security expert Pete Finnigan has done an excellent job of collecting all such default accounts created during various Oracle and third-party installations, which he has exposed for public use in his Website, petefinnigan.com. (Standard disclaimer: Oracle does not validate the content of third-party Websites.) Rather than reinvent the wheel, we will use Finnigan’s work and thank him profusely. I have changed his approach a little bit, however.

First, create the table to store the default accounts and default password:

CREATE TABLE osp_accounts
(
 product          VARCHAR2(30),
 security_level   NUMBER(1),
 username         VARCHAR2(30),
 password         VARCHAR2(30),
 hash_value       VARCHAR2(30),
 commentary       VARCHAR2(200)
)

Then you can load the table using data collected by Finnigan. (Download the script at petefinnigan.com/default/osp_accounts_public.zip.)[ After the table is loaded, you are ready to search for default passwords. I use a very simple SQL statement to find out the users:

col password format a20
col account_status format a20
col username format a15
select o.username, o.password, d.account_status
from dba_users d, osp_accounts o
where o.hash_value = d.password
/
           
       
USERNAME        PASSWORD             ACCOUNT_STATUS
--------------- -------------------- --------------------
CTXSYS          CHANGE_ON_INSTALL    OPEN
OLAPSYS         MANAGER              OPEN
DIP             DIP                  EXPIRED & LOCKED
DMSYS           DMSYS                OPEN
EXFSYS          EXFSYS               EXPIRED & LOCKED
SYSTEM          ORACLE               OPEN
WMSYS           WMSYS                EXPIRED & LOCKED
XDB             CHANGE_ON_INSTALL    EXPIRED & LOCKED
OUTLN           OUTLN                OPEN
SCOTT           TIGER                OPEN
SYS             ORACLE               OPEN

Here you can see some of the most vulnerable of situations, especially the last line, where the username is SYS and the password is oracle (as is that of SYSTEM)! It may not be change_on_install, but it’s just as predictable.

The vulnerability varies across versions. In Oracle Database 10g and later, the database installation has a prompt that asks what the password should be, instead of assuming it to be change_on_install or something else. Because the user is forced to make a decision, it is likely that the password will be a nondefault one. However, if the user chooses something as predictable as oracle, then the point is moot. (Perhaps oracle was chosen when the database was being built prior to production as a convenience for the DBAs. After it went to production, the password stuck around.)

In versions prior to Oracle Database 10g, the password is not prompted to be entered, and hence it is likely that the default password—for example, change_on_install for SYS and manager for SYSTEM—is active. This tool will help you identify such cases.

Also note that the userid SCOTT—the demo account for learning SQL techniques—may be fine for a development database but not for a production one. It is a potential back-door entry for intruders, and you should immediately drop it.

Accounts like CTXSYS, DMSYS, and OLAPSYS are required for Oracle tools. The best strategy is to drop these users if you are not using these options. If you are not sure you are using them, or you just want to reserve the opportunity, you can keep these accounts but lock them from connections. To lock an account and expire the password, you would issue this SQL

alter user dmsys account lock expire password;

which will set the account status to EXPIRED & LOCKED. When the user tries to log in, the following error will be raised:

ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.

Change the password for all accounts that you cannot lock. One such account is DBNSMP, but we’ll discuss that later.

Implications

The locking of unused accounts shouldn’t cause any problems.

Action Plan

Identify the unused accounts. Lock them and expire their passwords.

For the used accounts, change the password if the default password is being used.

1.2 Use Case-Sensitive Passwords

Background

Passwords in Oracle Database have historically been case insensitive; in other words, the passwords abc123 and “Abc123 were deemed identical. This led to consternation in some circles. Many regulations and security mandates require that passwords be of mixed case (have at least one uppercase letter).

Since Oracle Database 11g Release 1, case-sensitive passwords are available. So, abc123 and Abc123 are not deemed the same. If the user sets the password as Abc123, then abc123 will not work. Here is a demonstration:

SQL> alter user sh identified by Abc123;
User altered.

Here’s what happens if you try to connect with abc123:

SQL> conn sh/abc123
ERROR:
ORA-01017: invalid username/password; logon denied        
Warning: You are no longer connected to ORACLE.

The password is not accepted. Now let’s try the actual mixed-case password:

SQL> conn sh/Abc123
Connected.

A database parameter sec_case_sensitive_logon controls the behavior. If set to TRUE, the Oracle database differentiates between abc123 and Abc123. Setting it to FALSE ignores the case sensitivity, reverting to the pre–Oracle Database 11g approach. Here is a demonstration of setting it to FALSE:

SQL> alter system set sec_case_sensitive_logon = false;
 
System altered.
 

SQL> conn sh/abc123
Connected.
SQL> conn sh/Abc123
Connected.

Note how the database didn’t differentiate between the passwords.

Implications

Should you set the value to TRUE or FALSE? That’s an important question. In Oracle 11g Release 1, if you used DBCA to create the database, you were asked whether you wanted to keep the Oracle Database 10g–style case-insensitive password or apply the new Oracle Database 11g–style security. If you accepted the default—in other words, Oracle Database 11g–style—the parameter was set to TRUE and the passwords were checked for case. In Oracle 11g Release 2, DBCA doesn’t ask; it sets the values by default.

Suppose the password of a user called ARUP is dream1ng. Most likely, the user enters the password in SQL*Plus, SQL Developer, and so on as dream1ng. However, some tools may convert it to all uppercase making the password DREAM1NG. When this password is passed to the database, authentication will be refused. This will occur right after the database upgrade, so most likely the reason will be attributed to a bug in Oracle Database 11g or something similarly esoteric.

Often users make mistakes too. Because in pre–Oracle Database 11g they were not required to differentiate between case-sensitive passwords, they may continue to enter their passwords in whatever case they feel comfortable with, failing authentication.

Either way, it might not be possible to change the code quickly to pass the password as is or change user behavior. Therefore you should take a phased approach to changing this particular behavior; otherwise, it may cause application outages.

If users connect to an Oracle Database 11g database via db link, they must now use the password in right case otherwise, it will be rejected.

Action Plan

If the database is not subject to a condition that requires case-sensitive passwords, you may not care about it. Still, it is always a good idea to make passwords case sensitive so the database will be compliant with any regulations yet to come. Besides, it makes passwords more secure.

If this is a brand-new database, you should turn on the case-sensitive feature (leave the default). Make sure the users know about this feature so that they will be careful in entering the password in right case.

If you are upgrading the database from Oracle Database 10g, you should be little bit more flexible. Follow the steps shown below:

Make sure the audit_trail initialization parameter is set to db before the database is restarted:

audit_trail = db

This turns on the audit trail. (Oracle Database 11g already has this on by default.)

Make sure you have session auditing turned on. Issue the following command:

SQL> audit session;

It enables auditing by session. (Oracle Database 11g already has this on by default; so if you didn’t disable it explicitly, it’s on.)

Instead of turning the case-sensitive feature on immediately, leave it off by setting the parameter explicitly in the parameter file after you upgrade the database:

sec_case_sensitive_logon = false

This reinstates the Oracle Database 10g behavior; in other words, it lets the database ignore the case of passwords.

Make your users aware of the case-sensitive feature and encourage them to enter passwords in the proper case. Note: Due to the parameter being false, they can still enter passwords in any case and be authenticated.

Make the applications pass passwords in the proper case. The passwords will still be authenticated if they don’t, but at least applications will be ready.

During a change window, issue this statement:

SQL> alter system sec_case_sensitive_logon = true;

This enables case sensitivity in passwords. Immediately afterward, check for invalid login attempts in the audit trail:

select username, userhost, terminal
from dba_audit_trail
where returncode = 1017;

If any user failed authentication, they would have received an error message “ORA-01017: invalid username/password; logon denied” and this fact would have been recorded in the audit trail with the returncode value as 1017. The above query would have shown those invalid login attempts.

If you see any record, from the host and terminal, you can determine the source of these logins, allowing you to take corrective action.

1.3 Configure Oracle Binary Permissions

Background

Oracle Database uses several binary files. The most important is the executable oracle in UNIX and Linux flavors and oracle.exe in Windows.

Note the permission on these files. For instance, in UNIX, you may see something like this.

# cd $ORACLE_HOME/bin
# ls -l oracle
-rwsr-s--x   1 oracle oinstall      69344968 Jun 10 14:05 oracle

The permissions (the same in all relevant Oracle versions) are the default. Let’s see what they mean. (If you are familiar with the UNIX permissions, you can skip this subsection and proceed to the subsection “Two-Task Architecture.”)

The first position indicates the type of the file. In UNIX, everything—regular files, directories, and devices—is considered a file. This is a true file, hence the first position shows “-.” Had it been a directory, this position would have shown “d”; in the case of a character special device, it would have shown “c,” and so on.

The second position onward shows the permissions given on the file. The permissions are shown in blocks of three, indicating the status for the Read, Write, and Execute respectively. The first three positions show the permissions for the owner, the next three show the permissions given to the group the file belongs to, and the last three show the permissions provided to all others.

In each permission set, the permissions are shown as either a value or “-.” If a “-” appears in the place, it indicates that the permission is not granted on that privilege. For instance, in the above case, note the sixth position, indicating that the Write permission for the Group is set to “-,” which indicates that the group “dba” (the group the file belongs to) cannot write to this file. If the permission is granted, then the value is set to the corresponding letter. Again, in the above example, the Read permission for the Group (denoted by the fifth position) shows “r,” indicating that the group “dba” can read this file.

Note the last three positions, which indicate the permissions for the Others (not the owner, oracle, or the users belonging to the group dba). From the permissions, you can see that Others can simply execute this file but not read it or write to it.

This explains “r,” “w,” and “x”—for Read, Write and Execute, respectively—but what about the character “s” in the place where there should have been an “x”? This is an interesting twist to the Execute privileges. The presence of this “s” on the permission above indicates that this program is setuid enabled. When the program runs, regardless of who runs it, it will run as the user who owns it, i.e. oracleThis is one way in which the program can be owned by Oracle software but run by anyone who would connect to it. Thus, the program can operate under the privileges of oracle and not the user who runs it, which makes it possible to open database files and so on.

Two-Task Architecture. Recall how Oracle Database processes operate, by decoupling the user process from the server process. If you don’t remember this completely, I highly recommend rereading the first few chapters of the Oracle Database 10g Concepts Manual. Below is a highly distilled version of the interaction, which merely lays the foundation for understanding the permissions; it’s not a substitute for reviewing the contents of the manual.

When a user connects to an Oracle database—say, with SQL*Plus—Oracle creates a new process to service this user’s program. This new process is called the Oracle server process, which differs from the user’s process (sqlplus, sqlplus.exe, TOAD.exe, or whatever it else it may be). This server process interacts with the memory structures such as the System Global Area (SGA) and reads from the datafiles; if the data is not found in the buffer cache in the SGA, and so on. Under no circumstances is the user’s process (sqlplus) allowed to directly interact with the Oracle database datafiles. Because there are two processes (the user process and the server process) working in tandem to get the work done, this is sometimes known as two-task architecture. If a user process does something potentially disruptive, such as violating the memory management in the host machine, the Oracle database itself is not affected and the damage is limited to the user’s process.

(Note that the above applies to Oracle connections in a dedicated server environment. In a multithreaded server environment, this model is a little different in the sense that a single server process can service more than one user process. It’s still two-task, but instead of a 1:1 relation between the server and user processes, it’s 1:many.)

The server processes are run under the user who owns the Oracle software. Here’s an example. Suppose the user logs into the database using SQL*Plus:

$ sqlplus arup/arup

After this, if you search for this process:

$ ps -aef|grep sqlplus

it shows this:

oracle 6339 6185 0 13:06 pts/0 00:00:00 sqlplus

This, of course, assumes that no other SQL*Plus sessions have been running on the server.

Note the process id (6339). Now if you search that process ID:

$ ps -aef|grep 6339

You will get two processes:

oracle    6339  6185  0 13:06 pts/0    00:00:00 sqlplus
oracle    6340  6339  0 13:06 ?        00:00:00 oracleDBA102 
                                                 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

The first one you’ve seen before (the process for the SQL*Plus session). The second one—process ID 6340—is the server process that Oracle creates for the user. Note the Parent Process ID of the process; it’s 6339, which is the process ID of the SQL*Plus session.

The process name is oracleDBA102 (DESCRIPTION=(LOCAL=YES) (ADDRESS=(PROTOCOL=beq))), which tells you several things. First, the presence of the clause LOCAL=YES indicates that this process started due to another process that is running locally, on the same server as the database itself. It also shows PROTOCOL=beq, which means that the connection was made through a bequeath connection.

You can also find the information about the server process from the dynamic views:

select spid
from v$session s, v$process p
where s.sid = (select sid from v$mystat where rownum <2)
and p.addr = s.paddr;

The value returned by the above query is the process ID of the server process. This is the only way to get the process ID if the client process is on a different server, such as someone running SQL*Plus on a laptop connecting to the database.

Now, assume that the user connects through a slightly modified manner. Instead of connecting directly on the server, she uses the TNS string. Assume that your TNS string looks like this (on the server oradba):

DBA102 =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = oradba)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = DBA102)
 )
 )

Now the user connects (on the same server, oradba) as follows:

sqlplus arup/arup@dba102

Check the process ID from the dynamic views:

SQL> select spid
 2  from v$session s, v$process p
 3  where s.sid = (select sid from v$mystat where rownum <2)
 4  and p.addr = s.paddr
 5  /
 
       
SPID
------------
6428

The process ID is 6428. Search for this on the server:

$ ps -aef|grep sqlplus | grep -v grep
oracle    6426  6185  0 13:20 pts/0    00:00:00 sqlplus

Now when you search for the server process on the database server:

$ ps -aef|grep 6426 | grep -v grep
oracle 6426 6185 0 13:20 pts/0 00:00:00 sqlplus

you don’t see the server process. There is no child process of the user process 6426. But you know from the dynamic performance views that the server process is 6428, so what is the parent process of that?

$ ps -aef|grep 6428 | grep -v grep
oracle    6428     1  0 13:20 ? 00:00:00 oracleDBA102 (LOCAL=NO)

The parent process is 1. But why isn’t it 6426?

To understand the answer, you have to understand how different Oracle server processes are created. In the first case, when the user did not use a TNS connect string, the connection was routed directly to the database without going to the listener first. The database created a user process and then handed the control of the process to the process owner, a process known as bequeathing—hence the term bequeath process, which showed up in the process name.

In the second case, when the user was still on the same server but connected through the listener, the listener created the process for the user—which is known as forking. Similarly, if the user process were running on a different machine (such as a laptop), the connection would have to be made to the listener and the listener would have created the process. The process was created by a remote server, hence the process name contains the clause LOCAL=NO. Even if the SQL*Plus session was running on the same server, the fact that it was a non-bequeath connection made it a non-LOCAL one.

(Note: Depending on the OS, you may not see the parent ID of the server process in the same way that you see the SQL*Plus session in bequeath connections. In some cases, even though the connection is bequeath, the parent ID will show as 1. Therefore, don’t rely on the parent ID to determine what type of server process it is; use the process name instead.)

Now that you understand the two-task model, let’s see if you get the salient point in this discussion. The database creates and runs the server process, not the user who started the client process such as SQL*Plus. The server process uses the executable oracle or oracle.exe, so only the Oracle software owner, named orasoft (named so as to avoid confusion with the term “oracle,” which is the name of the executable), should have privileges to execute them—no one else. So why do you need permissions for the others?

The short answer is, you don’t. You can remove the unnecessary permissions by issuing this command:

$ chmod 4700 $ORACLE_HOME/bin/oracle

After executing the command, the permissions will look like this:

-rws------ 1 orasoft oinstall 248754168 Oct 8 07:11 oracle

Now we can move on to strategy—via the SUID bit. In this case the SUID bit is set to ON (indicated by rws permissions for the owner).

Strategy

Because you don’t need anyone other than the Oracle software owner (orasoft, in this case) to run the Oracle executable, you should remove the SUID bit from the executable and make it accessible by only the owner—no one else:

$ chmod 0700 $ORACLE_HOME/bin/oracle

The permissions now look like this:

-rwx------ 1 orasoft oinstall 248754168 Oct 8 07:11 oracle

Implications

This is a major change, and it’s important that you understand its impact. When a user (not the Oracle software owner) on the server tries a local connection, the executable oracle is run on his behalf as if the user orasoft is running it. Because the server process will open the datafiles (owned by orasoft), either it must run as orasoft or the user must have permissions to open the datafiles.

For example, suppose the UNIX user ananda logs in to the same server the database is on and connects locally:

$ sqlplus arup/arup

The user will immediately get an error:

ERROR:
ORA-12546: TNS:permission denied
       
Enter user-name:

The reason why is very simple: you removed the SUID permission on the file oracle. When the user executes a local connection, he essentially tries to run the executable oracle, but because the SUID is not set, it’s not tried as user orasoft but rather as ananda. As user ananda does not have permission to run this file, it will not be executed—hence the ORA-12546 error.

So, how can ananda connect to the database? There are two options. One is to make all the user processes run on a different server than the database server itself—thus there are no bequeath connections to the database, only non-LOCAL ones. Because the non-LOCAL connections go through the listener process and the listener spawns a server process for them, the server process is owned by orasoft (the Oracle software owner) and not by the user who is running the client process. There is no permission to issue.

Alternatively, if you must run some user processes on the database server itself, you can connect through the listener with

$ sqlplus arup/arup@dba102

which has the same effect as a user connecting from outside the server. Now only the user who owns the Oracle software (in this case, orasoft) can connect to the database through a bequeath connection.

DBAs with individual OS IDs will not be able to shut down or start up the database using the command connect / as sysdba, even if they belong to group dba. They can do so with

$ sqlplus /nolog
SQL> connect sys/Password_of_SYS@dba102 as sysdba

Yes, this approach makes use of the SYS password, but that’s a better practice compared to / as sysdba. A much better practice yet is to create Oracle userids for individual DBAs: 

connect ANANDA/Password_of_ANANDA@dba102 as sysdba

A favorite hacker trick is to get into the server using any account and then try to force into the database. (A typical “loose door” is the user “nobody.”) Even if the hacker does not get into the database, he can create a denial-of-service attack by buffer overflow of the oracle executable. If the ability to execute the file is removed, then the effectiveness of the attack is severely limited. At the same time, as you saw, you have not removed any functionality from legitimate users. Most users connect to the database using the listener anyway, and they will not be affected much.

Action Plan

See if any other user on the system makes a bequeath connection. You can accomplish this by

• Simply asking

• Searching for processes on the server to see if you find something as obvious as SQL*Plus

• Checking the column MACHINE of V$SESSION:

select program
from v$session
where machine = '<machine_name>';

If something comes up, you can identify the exact program running by turning on auditing (which you will learn about in the subsequent phases of this article series)[ and capturing any program coming from the server.

Action

IF no programs connect from the server, THEN

   Change the permissions of the oracle executable
  chmod 0700 $ORACLE_HOME/oracle

ELSIF some program connects from the server

   Change the connection from UserID/Password to 
  UserID/Password@Connect_String

END IF
IF you frequently connect from shell scripts as sqlplus / as sysdba 
THEN

   Change it to use DBAUser/Password@Connect_String

END IF

1.4 Secure Other Executables

Background

Take a look at the other executables in the $ORACLE_HOME/bin directory; some may look familiar, such as sqlplus or lsnrctl (the utility to start the listener); others may not.

Some of these files—such as tnslsnr, the utility that the listener process runs, or dbsnmp, which was used in Oracle Intelligent Agent—are not directly touched by the end user. To properly secure them, you must understand what they do and take appropriate action.

Recall that if the SUID bit is set for a file, then regardless of who runs the file it runs under the privileges of the owner, not the executor. You also learned that setting the SUID can be dangerous and should be discouraged.

There are several other files that have the SUID set to on. Let’s find them.

$ cd $ORACLE_HOME
$ find . -type f \( -perm -2000 -o -perm -4000 \) -exec ls -l {} \;

In Oracle Database 10g Release 1 and later, the above should return only the following executables (Oracle Database 11g should return only a subset of this):

-rwsr-s--x    1 orasoft  dba      93300507 Jul 22 11:20 ./bin/oracleO
-r-sr-s---    1 root     dba             0 Jul  1 23:15 ./bin/oradism
-rwsr-s--x    1 orasoft  dba         94492 Jul 22 11:22 ./bin/emtgtctl2
-rwsr-s---    1 root     dba         18944 Jul 22 11:22 ./bin/nmb
-rwsr-s---    1 root     dba         20110 Jul 22 11:22 ./bin/nmo
-r-sr-sr-x    1 nobody   nobody      58302 Jul 22 11:23 ./bin/extjob

Let’s see what these files are:

In Oracle9i Database Release 2, you will find a different file, ./bin/dbsnmp, which is the Oracle Intelligent Agent executable file. The permissions are set as such:

-rwsr-s--- 1 root dba 2986836 Jan 26 2005 dbsnmp

The problem with this file is that it needs root privileges to work properly, hence the SUID bit must be set to on. However, because this file is owned by root, hackers typically exploit it to gain access as root. The best advice is to eliminate it, or make it owned by the Oracle software owner and set the permissions to 700. You will lose some functionality, but it’s worth it to eliminate the risk.

The other executable to consider is tnslsnr, which is the Oracle Net Listener. There are two executables:

• tnslsnr—the actual listener executable
• lsnrctl—the utility that is used to manage the listener, such as starting, stopping, and so on

If you look at the permissions:

$ ls -l *lsnr*
-rwxr-x--x   1 orasoft    oinstall    214720 Oct 25 01:23 lsnrctl
-rwxr-xr-x   1 orasoft    oinstall    214720 Oct  1 18:50 lsnrctl0
-rwxr-x--x   1 orasoft    oinstall   1118816 Oct 25 01:23 tnslsnr
-rwxr-xr-x   1 orasoft    oinstall   1118816 Oct  1 18:50 tnslsnr0

the files have execute privileges for all. Like the executable oracleO, when a new file tnslsnr is created by relinking the Oracle software, the existing file tnslsnr is renamed to tnslsnr0. This is done because if the process needs to be rolled back, the old executable can be copied over the new one. Because it’s the copy of the old executable, the file tnslsnr0 may contain the same functionality as the original tnslsnr. The same goes for lsnrctl0.

Strategy

Now that you understand the purpose of each executable, let’s see how you can secure your database infrastructure. Most of the strategy has been discussed in the above section on background information. So, in essence, your strategic moves are all these actions

1. Remove all permissions to others from the files that are not needed—for example, lsnrctl0.
2. Restrict permissions for executables to Oracle software only.
3. Remove the SUID bit if the Oracle software owner starts the processes.

So, you want to change the permissions of the listener-related files as follows:

$ chmod 700 lsnrctl tnslsnr lsnrctl0 tnslsnr0
$ chmod 600 lsnrctl0 tnslsnr0

Verify the result:

$ ls -l *lsnr*
-rwx------   1 orasoft    oinstall    214720 Oct 25 01:23 lsnrctl
-rw-------   1 orasoft    oinstall    214720 Oct  1 18:50 lsnrctl0
-rwx------   1 orasoft    oinstall   1118816 Oct 25 01:23 tnslsnr
-rw-------   1 orasoft    oinstall   1118816 Oct  1 18:50 tnslsnr0

Implications

There are a few implications in this case:

• Changing the oracleO executable has no impact on the operation of the database. If you ever face an issue that points to a corrupt oracle executable, your best bet is to rename the oracleO file to “oracle.” If you do so, make sure you reset to permissions to 700. The same goes for lsnrctl0 and tnslsnr0 executables.

• Changing the emtgtctl2 permissions will have no impact if you use the Oracle software owner userid as the Oracle Enterprise Manager OS credentials. If you use a different userid (not orasoft, for example), the SUID must be reset to the old value and the permissions must be set as they were.

• The executable dbnsmp is used by Oracle Enterprise Manager Intelligent Agent, but only up until Oracle9i Database Release 2. Again, if you use the Oracle software owner as the OS credentials, there is no impact from changing the permissions. If you use a different userid, you must reset the permissions to the previous value.

Action Plan

1. Change permissions of oracleO, tnslsnr0, and lsnrctl0 to 0600.
2. Change permissions for tnslsnr and lsnrctl to 0700.
3. Do you use external jobs in Oracle Enterprise Manager?
Copy

Copied to Clipboard

Error: Could not Copy

IF no THEN change the permissions of extjob to 0000
ELSE

   Change the permissions of extjob to 0700 and change the 
    owner and group to orasoft and oinstall (or whatever the user 
    and group of the Oracle software owner are).

END IF

4. Copy

   Copied to Clipboard

   Error: Could not Copy

   IF you are on Oracle9i Database THEN
   
    
     Are you using Oracle Intelligent Agent?
   
   IF no THEN
   
    
     Change ownership of dbsnmp to orasoft
     Change permissions to 0700
   
   ELSE
   
    
     No change needed
   
   END IF

Note: If you apply a patch or upgrade the database, the permissions will be reset, so you need to re-examine them after the upgrade.

1.5 Change DBSNMP Password

Background

As you may know, Oracle Intelligent Agent communicates with Oracle Enterprise Manager to pass on information about components such as the database, the listener, and the server itself. To get data about the database, it needs to connect to the database using some userid. By default, the userid used is DBSNMP.

When the database is created, the password of DBSNMP] is also set to dbsnmp. This user has some powerful privileges, such as UNLIMITED TABLESPACE, SELECT ANY DICTIONARY (which allows the user to select from dynamic performance views and data dictionary views), and ANALYZE ANY DICTIONARY (which allows analyze of the system objects). Many intruders use this userid and password for back-door entry into the database. Needless to say, this is a huge security hole.

Strategy

You have to change the password of this user to something other than dbsnmp. However, you can’t just change the password at the database level, because that password is also stored in the agent configuration files. You need to update the configuration files to use the new password as well. Here’s the procedure for Oracle Database 10g:

1. First change the password of the user DBSNMP to something else—for example, TopSecret:

SQL> alter user dbsnmp identified by topsecret;

2. Go to the directory where the Oracle Agent Home is installed (not ORACLE_HOME)—for example, /u01/app/oracle/10.1/gridc.
3. Go to directory <hostname>/sysman/emd , where <hostname> is the name of the host or server. For instance, if the name of the server is prolin1, then the directory should be prolin1/sysman/emd.
4. Here you will find a file named targets.xml. Copy it under a new name (for example, targets.xml.old).
5. Open the file targets.xml and search for the word “dbsnmp”; the contents should be similar to:
Copy

Copied to Clipboard

Error: Could not Copy

<Target TYPE="oracle_database" NAME="PROPRD1_prolin1">
<Property NAME="MachineName" VALUE="192.168.101"/>
<Property NAME="OracleHome" VALUE="/u01/app/oracle/10.1/db1"/>
<Property NAME="Port" VALUE="1521"/>
<Property NAME="Role" VALUE="NORMAL"/>
<Property NAME="SID" VALUE="PROPRD1"/>
<Property NAME="ServiceName" VALUE="PROPRD"/>
<Property NAME="UserName" VALUE="dbsnmp"/>
<strong><Property NAME="password" VALUE="3797cf30e7c4a9c6" ENCRYPTED="TRUE"/></strong>
<CompositeMembership>
<MemberOf TYPE="rac_database" NAME="PROPRD" ASSOCIATION="cluster_member"/>
</CompositeMembership>
</Target>

6. Note this line (in bold type, above):

<Property NAME="password" VALUE="3797cf30e7c4a9c6" ENCRYPTED="TRUE"/>

This is where you will set the value of the password. Replace the above with

<Property NAME="password" VALUE="topsecret" ENCRYPTED="FALSE"/>

Note that you changed the value of ENCRYPTED to FALSE.

7. If this is a RAC database, this line will occur twice in the file. Make sure you change both occurrences. Search the file for the word “password” to locate these two instances
8. Now stop the agent by issuing this command

/u01/app/oracle/10.1/gridc/bin/emctl stop agent

Restart the agent:

/u01/app/oracle/10.1/gridc/bin/emctl stop agent

9. When you restart the agent, the cleartext password in the configuration file is encrypted. If you check the above line in the targets.xml file again, you will see something similar to the following:

<Property NAME="password" VALUE="3797cf30e7c4a9c6" ENCRYPTED="TRUE"/>

Note how the cleartext value has been converted to an encrypted value.

10. Now the agent is configured with the new password.
11. If you use the standalone Database Console instead of Oracle 10g Grid Control, then the procedure is similar—except that in Step 2, you would go to ORACLE_HOME, not where the Agent Home is located.

Implications

There are no user implications here.

Action Plan

1. Change the password of the user DBSNMP.

2. Update the agent files to reflect the new password.

1.6 Limit SYSDBA Login

Background

You might have noticed that any *nix user who is the member of the group dba can log in as the SYSDBA user by issuing this command:

sqlplus / as sysdba

This is usually taken as a matter of convenience, because you don’t need to remember or enter the password of the user SYS. However, this also creates a vulnerability: Any user who can log in as a dba group member can log into the database as SYS. The fortified password of SYS is not of much use then. If you have a strong SYS account, you should perhaps protect that as well as the dba group users so that the password of SYS is necessary to log in as SYS. This approach does not eliminate the risk of infiltration but does reduce it considerably.

Strategy

This process is controlled by the parameter SQLNET.AUTHENTICATION_SERVICES in the file SQLNET.ORA. If this parameter is set to NONE, then the auto login of the SYSDBA role is disabled. To disable it, place the following line in the SQLNET.ORA file located in the $ORACLE_HOME/network/admin directory.

SQLNET.AUTHENTICATION_SERVICES=(NONE)

From that point on, if a *nix user belonging to the group dba wants to connect using this familiar login:

$ sqlplus / as sysdba

they will get this:

ERROR:
ORA-01031: insufficient privileges

To connect, you must provide the SYS password:

$ sqlplus /nolog
SQL> connect sys/oracle as sysdba

This protects against someone who still does not know the SYS password from gaining access to the dba accounts.

Implications

As shown above, the biggest implication is the use of SYS passwords. You may need to make some changes to the scripts that connect to SYS.

If you ever lose the SYS password, don’t worry. You can comment the line in the file SQLNET.ORA and then connect the old-fashioned way: / as sysdba.

Action Plan

IF you use SYS connections in scripts THEN

 
  Change / as sysdba to sys/<SysPassword> as sysdba
  Place SQLNET.AUTHENTICATION_SERVICES=(NONE) in the 
  file SQLNET.ORA

ELSE

 
  No change needed

END IF

1.7 Create a Listener Password

Background

One of the most popular hacker tricks is to inject a large amount of text into the listener, thereby causing it to abort. The database could still be up, but since the listener is down, no new connections can be established—which in effect is a denial-of-service attack.

To do that, the hacker might attempt to change the attributes of the listener. A popular tactic here is to list the various services handled by the listener via the services command. Note how much information is displayed—possibly enough for the hacker to gain illegitimate access:

LSNRCTL> set displaymode verbose
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=prolin1.proligence.com)(PORT=1521)(IP=FIRST)))
Services Summary...
Service "PROPRD" has 1 instance(s).
 Instance "PROPRD1", status READY, has 1 handler(s) for this
service...
 Handler(s):
 "DEDICATED" established:0 refused:0 state:ready
 LOCAL SERVER
(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=/u01/oracle/products/10.1/db1/bin/ora
cle)(ARGV0=oraclePROPRD11)(ARGS='(LOCAL=NO)')(ENVS='_=/u01/oracle/pro
ducts/10.1/db1/bin/racgmain,_USR_ORA_CONNECT_STR=/ as
sysdba,_CAA_CHECK_INTERVAL=600,SHLIB_PATH=/u01/oracle/products/10.1/d
b1/lib32:/u01/oracrs/10gr1crs/lib32:/opt/nmapi/nmapi2/lib/hpux32:,_CA
A_ACTIVE_PLACEMENT=0,PATH=,_USR_ORA_ALERT_NAME=,_USR_ORA_IF=,_CAA_OPT
IONAL_RESOURCES=,_USR_ORA_START_TIMEOUT=0,ORACLE_BASE=/u01/oracle/pro
ducts/10.1/db2,_USR_ORA_DISCONNECT=false,_CAA_SCRIPT_TIMEOUT=600,_CAA
_UPTIME_THRESHOLD=7d,_USR_ORA_STOP_TIMEOUT=0,_CAA_FAILOVER_DELAY=0,_U
SR_ORA_PRECONNECT=none,_USR_ORA_FLAGS=,_CAA_TYPE=application,_USR_ORA
_INST_NOT_SHUTDOWN=,_CAA_REASON=boot,INIT_STATE=3,_USR_ORA_OPEN_MODE=
,_CAA_STATE=:OFFLINE,,_CAA_RESTART_ATTEMPTS=5,_CAA_ACTION_SCRIPT=/u01
/oracle/products/10.1/db1/bin/racgwrap,_CAA_DESCRIPTION=CRS
application for
Instance,_CAA_HOSTING_MEMBERS=prolin1,ORA_RACG_EXEC_ENV=LD_LIBRARY_PA
TH=/u01/oracle/products/10.1/db1/lib:/u01/oracrs/10gr1crs/lib:/opt/nm
api/nmapi2/lib/hpux64:/usr/lib:,_CAA_CLIENT_LOCALE=,_CAA_NAME=ora.PRO
PRD1.PROPRD11.inst,ORA_CRS_HOME=/u01/oracrs/10gr1crs,_CAA_AUTO_START=
1,_CAA_TARGET=:ONLINE,,_USR_ORA_PFILE=,_USR_ORA_OPI=false,_USR_ORA_CH
ECK_TIMEOUT=0,_CAA_PLACEMENT=restricted,_USR_ORA_LANG=,LD_LIBRARY_PAT
H=/u01/oracle/products/10.1/db1/lib:/u01/oracrs/10gr1crs/lib:/opt/nma
pi/nmapi2/lib/hpux64:/usr/lib:,_CAA_REQUIRED_RESOURCES=ora.prolin1.vi
p,_CAA_FAILURE_THRESHOLD=0,ORACLE_HOME=/u01/oracle/products/10.1/db1,
_USR_ORA_SRV=,PWD=/u01/oracrs/10gr1crs/bin,_USR_ORA_VIP=,_USR_ORA_STO
P_MODE=immediate,_CAA_FAILURE_INTERVAL=0,_USR_ORA_NETMASK=,_USR_ORA_D
EBUG=0,ORACLE_SID=PROPRD1,ORA_NET2_DESC=9,12,ORACLE_SPAWNED_PROCESS=1
')(ENV_POLICY=NONE))

Another hacker trick is to shut down the listener. New connections will be refused, again effectively creating a denial-of-service attack.

In addition, the listener can be remotely administered. Using this technique, the hacker can remotely stop a listener by attacking another vulnerable machine.

How do you protect yourself from these threats?

Strategy

The best option is to remove all permissions from the executable files tnslsnr and lsnrctl except those for the owner. (This method has been described in the previous section.) This way no one except the Oracle software owner can start or stop the listener. The executables look like this:

-rwx------   1 orasoft    oinstall    214720 Oct 25 01:23 lsnrctl
-rwx------   1 orasoft    oinstall   1118816 Oct 25 01:23 tnslsnr

In some cases, you may want to grant the authority to start and stop the listener. In such a case, you have to turn on the permissions:

$ chmod 0711 lsnrctl

However, in such a case, you should prevent unauthorized use by enforcing a password. When you set a password, all commands—except some benign ones such as HELP—are disabled.

Setting the password works the same way in all versions of Oracle Database, but the enforcement mechanism varies:

• In Oracle9i Database Release 2 and earlier, all users need a password.

• In Oracle Database 10g Release 1 and later, the OS user who owns the database software does not need a password. All others need a password.

Here’s how to set the password:

$ lsnrctl
       
LSNRCTL> change_password
Old password: <OldPassword> Not displayed
New password: <NewPassword> Not displayed
Reenter new password: <NewPassword> Not displayed
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prolin1)(PORT=1521)(IP=FIRST)))
Password changed for LISTENER
The command completed successfully

If you are setting the password for the first time, you can press ENTER when asked for the “Old Password.” After making the change, save it in the parameter file:

LSNRCTL> save_config

This command encrypts the password and places it in the listener parameter file. You can examine it later:

#----ADDED BY TNSLSNR 24-OCT-2005 17:02:28---
PASSWORDS_LISTENER_ODSSDB01 = 75CD180DE6C75466
#--------------------------------------------

When you decide to use a command, you have to supply the right password (in Oracle Database 10g and later, the OS user who owns the software does not need a password):

LSNRCTL> services
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-01169: The listener has not recognized the password

To pass the correct password, issue these commands:,

LSNRCTL> set password mypassword
The command completed successfully
LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
...

If you pass the wrong password, you will receive this error:

TNS-01169: The listener has not recognized the password.

If you do not pass the password and try executing a powerful command, you will receive this error:

TNS-01190: The user is not authorized to execute the requested listener command

To confirm that the password is in effect, view the setting in the STATUS display of the listener. To do so, issue this command:

$ lsnrctl status

The output varies between versions. For Oracle9i Database, here is a partial output: 

STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 9.2.0.6.0 - Production
Start Date                25-OCT-2005 10:26:47
Uptime                    0 days 13 hr. 8 min. 36 sec
Trace Level               off
Security                  ON

Note the last line (Security ON), which indicates that the password is set.

In Oracle Database 10g, this process is a little different. In that release, the listener is set to be executable by the Oracle software owner only without a password. If you have set a password, then other users can execute it after supplying the password. Here is the status display:

STATUS of the LISTENER
------------------------
Alias                     LISTENER_ODSPDB02
Version                   TNSLSNR for HPUX: Version 10.1.0.4.0 - Production
Start Date                16-OCT-2005 05:58:35
Uptime                    9 days 17 hr. 44 min. 41 sec
Trace Level               off
Security                  ON: Local OS Authentication

Note the last parameter, ON: Local OS Authentication which indicates that the password has not been set. When the password is set, the display will show that line as

Security ON: Password or Local OS Authentication

Note the additional clause above—Password— which indicates that a password has been set. In Oracle Database 11g Release 2, the password in listener.ora has been deprecated, so you shouldn’t set a password at all. In other words, you should not issue any listener commands using any userid other than the Oracle Database software (or the Oracle Grid Infrastructure software) owner.

Implications

In Oracle Database 10g and later, there are no implications. OS authentication is used to authenticate the user, and there is no need to provide a password to start or stop the listener. In Oracle9i Database and earlier, you have to provide a password, and hence the script requires a password.

Action Plan

IF Oracle Database 10g or later THEN

 
  Remove the permissions from all except the owner

ELSE

 
  Remove the permissions from all except the owner
  Set a password for the listener operations

END IF

1.8 Protect the Listener

Background

Creating a buffer overflow—crashing the listener by sending a large string to be executed—is a common intruder tactic. Another popular one is to see the various components using SET DISPLAYMODE VERBOSE from the lsnrctl utility. In this case, the hacker may manipulate the settings by running lsnrctl on a vulnerable machine to administer the listener on the target server. Here’s an example:

LSNRCTL> set trc_level support
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prolin1)(PORT=1521)))
LISTENER parameter "trc_level" set to support
The command completed successfully
LSNRCTL> set trc_directory /tmp
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prolin1)(PORT=1521)))
LISTENER parameter "trc_directory" set to /tmp
The command completed successfully

Because the trace level is SUPPORT, the listener generates a lot of information that you may not want the hacker to know. Also, because the trace files are written to the directory /tmp, he can easily see them as well. All this can be learned without even being on the server.

Strategy

To protect the listener, the best option is to set a password. In addition, you can employ another approach: restricting the ability to set various parameters of the listener using the lsnrctl utility. In that case, the only way to change parameters is to set them in the listener configuration file and then reload it. To set these restrictions, you can place the following line in the listener.ora file:

ADMIN_RESTRICTIONS_LISTENER = ON

and then restart the listener. Now, you can no longer use the SET command in the lsnrctl prompt to change a value. For example:

LSNRCTL> set trc_directory /hacker_dir
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=PROPRD1))
TNS-12508: TNS:listener could not resolve the COMMAND given

Note the TNS-12508 error. From now on, to change a value of a parameter you must do so in listener.ora and then use the reload command:

LSNRCTL> reload

This is true for all Oracle versions discussed in this series.

Even if you use a password to protect the listener, you should still use this technique to further restrict the hacker’s ability to administer the listener. This is particularly true in Oracle Database 10g, where a listener password is not required for the Oracle software owner.

Implications

The implications are negligible. Few users edit parameters online anyway; rather, they edit listener.ora and then reload the listener. Thus, this change will not affect them at all.

Note, however, that it will no longer be possible to use remote listener control to administer a listener on a different server. Instead, you have to log onto the server to make the change in listener.ora and then reload the listener, which is best practice anyway.

Action Plan

Place the parameter ADMIN_RESTRICTIONS_LISTENER = ON in the file listener.ora.

1. Reload the listener by issuing lsnrctl reload.

1.9 Trim Sweeping Privileges

Background

A typical user needs privileges that are important to perform his or her job—nothing more nor less. As this policy may prove unrealistic, however, you may need to adopt a middle-of-the-road approach: removing the most-powerful privileges the users do not need.

One example of a powerful privilege is CREATE ANY TABLE, which lets the user create a table in any schema, not just its own. Rarely do users need this privilege; you can safely revoke it. On the other hand, a privilege like QUERY REWRITE, which allows the users sessions to rewrite a query to take advantage of a function-based index or materialized view, is relatively innocuous.

Strategy

First, identify all the privileges you consider innocuous (CREATE TYPE, CREATE SESSION, and so on). Here I have included UNLIMITED TABLESPACE as a nonsweeping privilege, but you may disagree:

Version:1.0 StartHTML:0000000167 EndHTML:0000012073 StartFragment:0000000461 EndFragment:0000012057

set pages 50000
break on privilege skip 1

select privilege, grantee, admin_option
from dba_sys_privs
where privilege not in
(
/* list any other privilege here you don't find "sweeping"
*/
'ALTER SESSION',
'QUERY REWRITE',
'CREATE DIMENSION',
'CREATE INDEXTYPE',
'CREATE LIBRARY',
'CREATE OPERATOR',
'CREATE PROCEDURE',
'CREATE SEQUENCE',
'CREATE SESSION',
'CREATE SNAPSHOT',
'CREATE SYNONYM',
'CREATE TABLE',
'CREATE TRIGGER',
'CREATE TYPE',
'CREATE USER',
'CREATE VIEW',
'UNLIMITED TABLESPACE'
)
and grantee not in
('SYS','SYSTEM','WKSYS','XDB',
'MDSYS','ORDPLUGINS','ODM','DBA')
/* Place all the user names you want to exclude */
order by privilege, grantee
/

Here is part of a sample output:

set pages 50000
break on privilege skip 1

select privilege, grantee, admin_option
from dba_sys_privs
where privilege not in
(
   /* list any other privilege here you don't find "sweeping"
   */
   'ALTER SESSION',
   'QUERY REWRITE',
   'CREATE DIMENSION',
   'CREATE INDEXTYPE',
   'CREATE LIBRARY',
   'CREATE OPERATOR',
   'CREATE PROCEDURE',
   'CREATE SEQUENCE',
   'CREATE SESSION',
   'CREATE SNAPSHOT',
   'CREATE SYNONYM',
   'CREATE TABLE',
   'CREATE TRIGGER',
   'CREATE TYPE',
   'CREATE USER',
   'CREATE VIEW',
   'UNLIMITED TABLESPACE'
)
and grantee not in
 ('SYS','SYSTEM','WKSYS','XDB',
  'MDSYS','ORDPLUGINS','ODM','DBA')
    /* Place all the user names you want to exclude */
order by privilege, grantee
/

Here is part of a sample output:

PRIVILEGE                   GRANTEE                        ADM
--------------------------- ------------------------------ ---
ADMINISTER DATABASE TRIGGER EXFSYS                         NO
                            IMP_FULL_DATABASE              NO

ADMINISTER RESOURCE MANAGER EXP_FULL_DATABASE              NO
                            IMP_FULL_DATABASE              NO

ALTER ANY MATERIALIZED VIEW DWETL                          NO
                            REPORTMAN                      NO

ALTER ANY OUTLINE           REPORTMAN                      NO

ALTER ANY PROCEDURE         IMP_FULL_DATABASE              NO
                            QCO                            NO

ALTER ANY RULE              CDC_PUB                        YES

ALTER ANY RULE SET          CDC_PUB                        YES

ALTER ANY TABLE             IMP_FULL_DATABASE              NO
                            CNSMP                          NO
                            QCO                            NO

ALTER ANY TRIGGER           IMP_FULL_DATABASE              NO
                            QCO                            NO
                            VCHANG                         NO

ALTER ANY TYPE              IMP_FULL_DATABASE              NO

ALTER SYSTEM                ORADBA                         NO
                            QCO                            NO

ALTER TABLESPACE            QCO                            NO

ALTER USER                  QCO                            NO
                            SYSMAN                         NO

ANALYZE ANY                 AFFMAN                         NO
                            ARAO                           NO
                            CONCASTER                      NO
CREATE ANY SYNONYM          ATHOTANG                       YES
                            ARUP                           YES
                            IMP_FULL_DATABASE              NO
                            DB_MONITOR                     YES
                            QCO                            YES
                            RCHUNG                         YES
                            SPOT                           YES

CREATE ANY TABLE            IMP_FULL_DATABASE              NO
                            CNSMP                          NO
                            QCO                            NO
                            SYSMAN                         NO

DROP ANY TABLE              ATHOTANG                       YES
                            IMP_FULL_DATABASE              NO
                            CNSMP                          NO
                            QCO                            YES
_ and so on _

Note some of the key lines of the output. Some sweeping privileges—DROP ANY TABLE, for instance—probably shouldn’t have been granted to anyone. In this case, let’s see which users have this privilege.

• IMP_FULL_DATABASE is a role used to do a full database import, usually granted to the DBA or to any other user needed in the import. The privilege is probably necessary.

• QCO refers to Quest Central for Oracle, a popular tool for managing databases. The privilege is probably necessary; but you have to be careful to make sure no one except the DBAs have the access to the QCO userid..

• The users CNSMP and ATHOTANG, unless they are DBAs, do not appear to need any sweeping privileges. The privilege should be revoked.

Implications

This is one action you can’t execute immediately. Before revoking any privilege from any user, you have to carefully analyze its impact.

If you have doubts, the best course of action is to interview the people behind the userids. For example, perhaps ATHOTANG does not really need to drop tables but was assumed to do so. (Don’t be surprised—such assumptions are common.)

Action Plan

This task requires some planning, so reserve any action for the next phase. Until then, collect the required information.

1.10 Move Audit Trails to a Different Tablespace

Background

When the audit trail is set to database and for fine-grained auditing trails, the trail records are stored in regular database tables—AUD$ and FGA_LOG$ under the SYS schema. They are located in the SYSTEM and SYSAUX tablespaces, respectively. Because records in these tables simply keep growing as activity increases, the space consumed by these special tablespaces also increases, which may not be desirable. If the system tablespace runs out of space, the database will abort.

Therefore, you should move these tables into a special tablespace—say, AUDIT_TS—as shown below:

create tablespace aud_ts
datafile '+DATA'
size 10M
segment space management auto
/

In Oracle Database 11g Release 2, moving these tables to a different tablespace is supported. Prior to this release, the move is officially not supported. This section pertains to Oracle Database 11g Release 2 only.

Move the AUD$ table to that tablespace:
begin
 dbms_audit_mgmt.set_audit_trail_location(
 audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std,
 audit_trail_location_value  => 'AUD_TS');
end;
 /

If you want to move the FGA_LOG$ table as well, you can do so by executing the above PL/SQL block but replacing the parameter as shown below:

audit_trail_type            => dbms_audit_mgmt.audit_trail_fga_std

Once executed, the tables will be moved to the AUD_TS tablespace.

Implications

The advantages are obvious—the audit trails are the only items that keep growing in the database based on usage, and hence moving it out of the SYSTEM and SYSAUX tablespaces is a desirable move.

Important: If the tablespace is offline, you will get an error for auditable actions. Here is an example where a user is trying to connect (an event that is audited) but fails because the audit table is not available:

SQL> conn arup/arup
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '+DATA/d112d1/datafile/aud_ts.293.698028255'
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '+DATA/d112d1/datafile/aud_ts.293.698028255'    
   
Warning: You are no longer connected to ORACLE.

To resolve it, you should bring the tablespace online (after fixing the underlying issue, of course). But what if the underlying issue is not resolvable immediately? Then you have no choice but to remove the audit settings. For instance, you can turn off auditing on session

SQL> noaudit session;
Noaudit succeeded.

Now if you try connecting, you will be successful:

SQL> conn arup/arup
Connected.

Action

IF Oracle Database is earlier than 11g Release 2 THEN

 
  Don’t do anything

ELSE

 
  Create a new tablespace
  Move the audit trail(s) to the new tablespace
  Make sure that tablespace is online.

END IF

The content provided here is for instructional purposes only and is not validated by Oracle; use it at your own risk! Under no circumstances should you consider it to be part of a consulting or services offering.

Go on to Phase 2 | Read the Project Lockdown TOC and Security Primer