Project Lockdown

Background
Oracle users can be authenticated in different ways, most commonly via database authentication. When a user is created as create user ananda identified by abc123, the only way the user can log in to database is by passing its userid and password.

One alternative is operating system authentication, in which the user is created as:

create user ops$ananda identified externally;

If the host operating system has a userid named "ananda", then Oracle does not check its credentials anymore. It simply assumes the host must have done its authentication and lets the user into the database without any further checking.

That's where the problem lies. If the host operating system is strong in authentication, it may be secure; but in some weak OSs, it is possible to login as a user by cracking the password or entering without a password:

sqlplus /

Note the lack of userid and password—the string "/" instructs the database to accept the connection of the userid ananda to the database account ops$ananda.

This type of authentication commonly useful in shell scripts so that you don't have to embed the password in the script, but simply call it as sqlplus /. This is not only convenient but also somewhat secure, since the password is not present. However, consider this scenario: In weak-security OSs, someone can create an account called ananda and then use it to log into account ops$ananda.

Must it be ops$? Not really; you can change it by setting an initialization parameter. In the following example, I have set it to osauthent$.

os_authent_prefix = 'osauthent$'

You can find these users by using the following query:

SQL> select username, password from dba_users
  2  where password = 'EXTERNAL'
  3  /
 
USERNAME                       PASSWORD
------------------------------ ------------------------------
OPS$ANANDA                     EXTERNAL
OPS$ORACLE                     EXTERNAL

When the initialization parameter is set like this, the account ops$ananda will not work; instead, you need to create those accounts (OS-authenticated) as osauthent$ananda. In an interesting twist , you can also set it to "" (null). In that case the OS user ananda will map to Oracle user ananda. You can even set the password for this account:

alter user ops$ananda identified by oracle;

In that case, the user can log into the database in either manner:

sqlplus /
sqlplus ops$ananda/oracle

So, what's wrong with that? Well, consider the situation. Suppose the parameter os_authent_prefix is set to "" (null). In a weak OS, someone can create a user called SYSTEM and login as

sqlplus /

This will log the user as the Oracle user SYSTEM! Once logged in, the user can do anything they want—create users, drop data files, look into sensitive data, and a lot of other things. Suddenly, something that seemed like a convenience is a huge liability.

Strategy
As you can see, the issue arises only in certain combination of occurrences. One of them is the OS_AUTHENT_PREFIX being not null, and the other one is setting the password for OS-authenticated accounts. So the first thing to check is the OS authentication prefix.

SQL> select value    
  2  from v$parameter
  3  where name = 'os_authent_prefix';
 
VALUE
-----------------------
ops$

If the above returns null, then you should make plans to change it. The actual value is not important, but you must include some non-alphanumeric character. That way, the OS-authenticated username will never match an actual user.

Second, you need to make sure the OS-authenticated accounts are authenticated exactly that way—by the OS—and never have a password. For example, if your OS_AUTHENT_PREFIX were set to OPS$, you would use the following query to find out whether or not the password is set:

SQL> select username, password from dba_users
  2  where username like 'OPS$%';

USERNAME                       PASSWORD
------------------------------ ------------------------------
OPS$ORACLE                     17C96FEC14DC431F
OPS$ANANDA                     EXTERNAL

This shows that the user OPS$ORACLE cannot login through the OS authentication route or the password route. This is exactly what you want to avoid; there should be only one way to authenticate. To change the mode of authentication of OPS$ORACLE, you should use:

alter user OPS$ORACLE identified externally;

This changes the PASSWORD column to EXTERNAL.

Action Plan

Background
Remote OS authentication is a similar process. In OS authentication, you can create users who are authenticated on the local operating system. For instance, if you have a user named ananda on the UNIX server and a user named OPS$ANANDA in the database running on the same server, the user ananda can simply issue the following command to log into the database:

sqlplus /

This works for the local users—users on the same server where the database runs. However, what happens when a user of a remote server tries to connect to the database? For instance, suppose you have two servers, node1 and node2, and a database runs on the node1 server. This database has a user OPS$ANANDA identified externally. There is a user ananda on the server node2, not node1. When user ananda on the server node2 tries to connect to the database on node1 by issuing:

sqlplus /@node1

will he be able to connect?

The answer depends on the settings of the database initialization parameter remote_os_authent. If the parameter is set to TRUE, he connection attempt is successful; if FALSE, the OS-authenticated connections from remote servers are not allowed. The default is FALSE.

Why not? For a very good reason: On your database server you may have a great deal of security, so OS-authenticated users may not be an issue. But you cannot say the same thing for client machines. Depending on the OS, an adversary may be able to create an account called ananda on the remote servers, and when it connects to the database server, it can connect to ops$ananda without a password. Because you can't easily control client machines, this functionality puts a big chink into your armor.

Strategy
The task here is quite simple. Check if the parameter is set to TRUE. If true, change it to FALSE and restart the database.

Implications
In many databases, this parameter may already be set to FALSE. However, if it's set to TRUE and some client machines connect as OS-authenticated users, those accounts will not work. You can find them before making this change:

select username, machine, osuser, terminal
from v$session
where username like 'OPS$%'
/

USERNAME      MACHINE              OSUSER       TERMINAL
------------- -------------------- ------------ -----------
OPS$ANANDA    PRONT\ANANDAT42      ananda       ANANDAT42

Here we see that the user ananda has logged as remote OS authenticated from the machine PRONT\ANANDAT42. Note here that ananda is a user in the machine PRONT\ANANDAT42, not the server where the database runs.

Action Plan

1. If some users connect using remote authentication, then remove their no-password functionality.
2. In the initialization parameter file, change remote_os_authent to FALSE and restart the database.

Background
When inside SQL*Plus, you may have noticed that you can issue commands like host to go to the *nix prompt or run to execute some script. One little used but potentially deadly attack originates from this functionality. You may have some SQL*Plus script that is called from the shell similar to the following:

sqlplus someuser/somepass@remotedb @myscript.sql

Here myscript.sql is a SQL*Plus script file that executes and then exits. During execution, the user may press Control-C (or the break key sequence for the specific OS) and interrupt the flow of the program. In this case the user gets the SQL*Plus prompt, connected as someuser. At this stage, the user can do pretty much everything that is possible from the SQL*Plus prompt—and therefore do some damage.

This is a security hole you should patch up immediately.

Strategy
The fix is easy: All you have to do is restrict these commands to a "special place" that SQL*Plus refers to before running commands. This special place is a table named SQLPLUS_PRODUCT_PROFILE in the SYSTEM schema. If the table is not present, you will get a warning similar to "Product User Profile Not Loaded" every time you start SQL*Plus.

SQL> select * from system.SQLPLUS_PRODUCT_PROFILE
  2  /
 
no rows selected
 
SQL> insert into system.SQLPLUS_PRODUCT_PROFILE
  2  values ('SQL*Plus','%','HOST',null,null,'DISABLED',null,null)
  3  /
 
1 row created.
 
SQL> commit;
 
Commit complete.

Now when you start SQL*Plus and enter the host command (or its equivalent "!"), you will get the error:

SQL> host;
SP2-0544: Command "host" disabled in Product User Profile

If you want to disable this for a certain user , simply replace the "%" (for all users) in the above insert command with the username you want to restrict. For instance, if you want this disabled for user SCOTT only, use the following statement:

SQL> insert into system.SQLPLUS_PRODUCT_PROFILE
  2  values ('SQL*Plus','SCOTT','HOST',null,null,'DISABLED',null,null)
  3  /

You can also use wildcards in the username—such as APP% for all users starting with APP (APPUSER1, APPUSER2).

This approach works for other commands (and their abbreviated or shortcut forms) as well:

accept          edit          repheader
append          execute       run
archive log     exit          save 
attribute       quit          set
break           get           show 
btitle          help          shutdown 
change          host          spool
clear           input         start
column          list          startup 
compute         password      store
connect         pause         timing
copy            print         ttitle 
define          prompt        undefine
del             recover       variable
describe        remark        whenever oserror
disconnect      repfooter     whenever sqlerror

This method also applies to SQL commands, not just SQL*Plus. For instance, if you want to disable the lock command, you will need to insert:

insert into system.SQLPLUS_PRODUCT_PROFILE
values ('SQL*Plus','%','LOCK',null,null,'DISABLED',null,null)
/

Note that the SQL command lock is disabled, yet we have used the "SQL*Plus" keyword above.

The following SQL commands can be disabled:

alter            drop          revoke
analyze          explain       rollback
associate        flashback     savepoint
audit            grant         select
call             insert        set constraints
comment          lock          set role
commit           merge         set transaction
create           noaudit       truncate
delete           purge         update 
disassociate     rename        validate

This action requires some careful planning. If your shell scripts depend on the host command from SQL*Plus, disabling them would obviously stop these jobs. One very good example is the old host backup command. A typical SQL script file may look like:

alter tablespace xyz begin backup;
host cp /fs1/xyz.dbf /backup/xyz.dbf
alter tablespace xyz end backup;

...and so on for all tablespaces. This script will of course fail—unless it's run from the SYS user, in which case the command disabling won't not work.

Action Plan
Disable all such commands from SQL*Plus when they are least likely to be used.

Background
Depending on your use of SQL*Plus scripts, you may or may not be exposed to the previously described threats. In either case it's beneficial to reduce the threat level by taking preventive measures. One option is to disable the commands in the product profile as described, but an easier one, available since Oracle9 i Database, is to use SQL*Plus restrictions. In this option, you have to call SQL*Plus with a new parameter, restrict. There are different levels of restriction. Level 1 disables the host and edit commands only.

sqlplus –restrict 1 scott/tiger

Once inside the shell, if the user calls host command, he gets:

SQL> host
SP2-0738: Restricted command "host" not available

Note the error message is different from the product user profile message.

Using -restrict 2 disables save, store, and spool commands, in addition to host and edit. Using -restrict 3 adds get and start to the existing list.

The most important difference is that all users, even SYS, will be subject to this limitation. The product profile restriction does not apply to SYS.

Strategy
This may be a better option than using the product user profile. In this case, create a shell script named "sqlplus" in $ORACLE_HOME/bin and rename the sqlplus executable to something like "sqlplus_orig". Place the following line in the script "sqlplus".

$ORACLE_HOME/bin/sqlplus_orig –restrict 1

This will have the same effect as the command line parameter. Of course, you can add any parameter to restrict you want.

Action Plan

Background
You may have placed sensitive information such as password or encryption keys inside stored procedures and functions. When you do so, an adversary can easily select it from within the code. Here is an example form a site I recently audited:

l_password := 'GobbleDGook';

An adversary can select the source code as:

select text
from dba_source
where name = 'MYFUNC';

The best way to eliminate this risk is to use the wrap utility. Once you create the script file to create the procedure or function, wrap it by:

wrap iname=myfunc.sql oname=myfunc.plb

This will create a file myfunc.plb that has the source code stored is an undecipherable manner, protecting your code.

In Oracle Database 10g Release 2, you can create the wrapped code directly without a script by calling the supplied procedure:

begin
   dbms_ddl.create_wrapped 
     ('create or replace function myfunc ...')
end;

You can also use this to write your own wrap utility:

1  declare
2     l_input_code    dbms_sql.varchar2s;
3  begin
4     l_input_code (1) := 'Array to hold the MYP';
5     l_input_code (2) := 'create or replace procedure myproc as ';
6     l_input_code (3) := '  l_key VARCHAR2(200);';
7     l_input_code (4) := 'begin ';
8     l_input_code (5) := '  ...;';
9     l_input_code (6) := 'end;';
10    l_input_code (7) := 'the end';
11    sys.dbms_ddl.create_wrapped (
12             ddl     => l_input_code,
13             lb      => 2,
14             ub      => 6
15     );
16* end;

and then place all your code in the input varray for wrapping.

One other thing to be aware of: In Oracle9 i , the wrap utility does not wrap variable values. Thus, secret words like password and keys are clearly shown inside the wrapped code.

Let's see an example. Here are the contents of the file a.sql:

create or replace procedure myproc
as
    l_v varchar2(200);
begin
    l_v := 'SecretKey';
end;

I have used the value of the secret words as SecretKey. Let's wrap the source:

$ wrap iname=a.sql oname=a.plb

Now if you check inside of the wrapped code, you will still see the value in cleartext:

$ grep SecretKey a.plb
1SecretKey:

Again, this issue is not present in Oracle Database 10g Release 1 and later.

To protect your variable values, you should make the cleartext value inside the code less readable. For instance, your code could read:

create or replace procedure myproc
as
    l_v1 varchar2(200);
    l_v2 varchar2(200);
    l_v3 varchar2(200);
    l_lv varchar2(200);
begin
    l_v1 := 'LotsOfJunktoMakeSureSecurityIsWaterTight';
    l_v2 := 'AdditionalValueToInterpretTheSecretWord';
    l_v3 := 'WowWeDontStopAtTheKeyDoWe';
    l_lv := substr(l_v1,21,3);
    l_lv := l_lv||substr(l_v2,24,3);
    l_lv := l_lv||substr(l_v3,19,3);
end;
/

Note how we have embedded the parts of the secret word inside the strings and used the positions inside the code. You can't see the first part of the code inside the first string.

Strategy
There is no clear way to identify the source code referencing this sensitive data. Your best bet is to ask around the development group to learn who may be using the sensitive words. You can also do a preliminary check to find out if codes contain words like PASSWORD, SECRET, ENC (for ENCrypted, ENCryption, ENCiphered, ENCoded), DEC, and so on.

select name, text
from dba_source
where upper(text) like '%PASSWORD%'
or upper(text) like '%ENC%'
or upper(text) like '%DEC%'
or upper(text) like '%SECRET%'
or upper(text) like '%PASS%'
/

This may offer some clue that will help you identify the possible code segments to examine. Once you identify all such code, you should wrap it using the wrap utility and run the wrapped code.

Action Plan

Background
When you grant a privilege, you can optionally use the with grant option clause so that the grantee can grant it further. Here is an example on grants on a table TAB1 owned by user A.

Step1
Connect A/******
Grant select on tab1 to B;
                              


Step 2
Connect B/******
Grant select on a.tab1 to C;
                            

The user receives the error

ORA-01031: insufficient privileges

due to the fact that user B does not have the privileges to grant the privilege it itself received from someone. However, in step 1, if the statement were

Grant select on tab1 to b with grant option;

then user B would have had the privileges to grant it further, and step 2 would have been successful.

Similarly, B can also grant it C with grant option, who in turn can grant it to D and so on.

On the surface it sounds like a good plan. The original owner A does not need to worry about to whom to grant or revoke privileges; the process is self-managed on an as-needed basis. So what's the problem?

Well, consider this scenario:

Connect A/*****
Revoke select on tab1 from B.

Remember, C got its privileges on TAB1 from B, not directly from A; so what happens to its privileges now that B lost them? C loses its privilege as well, since it was a derived one.

Furthermore, suppose A has granted select on TAB1 to C directly. Now C has two grants on TAB1—one from B and one from A. When you revoke a privilege, the other one is still in effect, making you falsely believe that the privilege is not present.

Despite its ostensible elegance, this process actually creates confusion and security holes and introduces difficult-to-track bugs. It makes much more sense to grant the privileges directly without an intermediary.

Strategy
Your goal is to identify which privileges were granted via another user and then do direct grants instead. These are clearly visible from the view DBA_TAB_PRIVS, where the column grantor shows the user that granted the privilege.

SQL> col grantee format a15
SQL> col privilege format a15
SQL> col owner format a20
SQL> col table_name format a20
SQL> select grantee, privilege, owner, table_name
  2  from dba_tab_privs
  3* where grantor != owner
  4 /

A sample output is shown below.

PUBLIC          EXECUTE         XDB.DBMS_XMLSCHEMA             SYS
PUBLIC          EXECUTE         XDB.XDB_PRIVILEGES             SYS
PUBLIC          EXECUTE         XDB.DBMS_XMLSCHEMA_INT         SYS
APP1            SELECT          ANANDA.MP                      RUSER

The first three lines can be ignored, where the grant is made to the role PUBLIC by the user SYS. The privilege is on the package DBMS_XMLSCHEMA owned by the schema XDB. Being a special supplied package by Oracle, this may be allowed; but the fourth line clearly needs attention. The table MP, owned by ANANDA, has been granted by RUSER and should be corrected. The fix is really simple: Grant select on the object to APP1 directly, even if RUSER has a with grant option privilege.

There are two ways to do that:

1. Owner of the object grants it directly
2. A superuser such as SYS grants it

The second option is easier to implement. The SYS user does not really inherit the grants; it grants the privilege by using the system privilege grant any object privilege. When SYS grants the privilege as:

grant select on a.tab1 to c;

The GRANTOR column shows A, not SYS; which is exactly what you want.

set lines 300
set pages 0
spool grant_direct.sql
select 'grant '||privilege||' on '||owner||
   '.'||table_name||' to '||grantee||';'
from dba_tab_privs
where grantor != owner
/
spool off

Now run the file grant_direct.sql to grant the privileges directly.

After this is successful, you have to revoke the privileges you have granted indirectly. This is not possible in a single statement since you must be connected as the grantor as well.

break on conn skip 2
select 'connect '||grantor conn,
   'revoke '||privilege||' on '||owner||
   '.'||table_name||' from '||grantee||';' line
from dba_tab_privs
where GRANTOR != 'SYS'
and grantor != owner
order by 1,2
/

Spool this script to a file, edit it to supply the password for each user, and execute it to revoke the grants.

SQL> select grantee, privilege, owner, table_name
  2  from dba_tab_privs
  3* where grantor != owner
  4 /

Run this before and after the change, save the outputs, and compare them to make sure the privileges are intact.

The second implication is more pronounced. The grant-and-revoke cycle will make the cursors on these objects in the library cache invalid and will force the cursors to be reparsed, which will momentarily degrade performance.

In addition, some dependent objects will be invalidated. Since the privileges are re-granted, the objects will compile fine when they are referenced; but you may want to take some proactive action and recompile them beforehand.

1. Find out the privileges granted by others, using the grantable option.
2. Revoke the privileges.
3. Re-grant without the grant option.
4. Check for invalid objects and recompile them.

Background
How much space inside a tablespace can a user use, and how many tablespaces can he write to? The answer depends on the quota available to be user on the tablespace. You can specify the quota like this:

alter user ananda quota 12M on users;

This limits the user ananda to create stored objects such as tables, indexes, and materialized views whose total size doesn't exceed 12MB. To confirm or find out how much the user has used, issue the query

SQL> col used format 999,999.999 head "Used (MB)"
SQL> col quota format 999,999.999 head "Quota (MB)"
SQL> col tablespace_name format a15
SQL> select username, tablespace_name,
  2  bytes/1024/1024 used,
  3  max_bytes/1024/1024 quota
  4  from dba_ts_quotas
  5  order by username
  6  /

Sample output is shown below.

USERNAME            TABLESPACE_NAME    Used (MB)   Quota (MB)
------------------- --------------- ------------ ------------
USER1               USERS                   .000      100.000
USER1               APP1_INDEX           504.875        -.000
USER2               USERS                   .125        5.000

This needs some explanation. The output shows that the user USER1 has a quota of 100MB on the tablespace USERS (shown under the column Quota). Of this, the user has used nothing (as shown under the column Used). The second line is interesting—see how the Quota columns shows "-0". It indicates that the user has unlimited tablespace privileges on that tablespace—APP1_INDEX. The user USER2 has a quota of 5MB on tablespace USERS, of which only 0.125 MB has been used.

It's the unlimited tablespace you should watch out for. A user can be given the unlimited quota by:

alter user ananda quota unlimited on users;

However, this act may have a security implication; if a regular user has an unlimited tablespace quota on a business-critical tablespace, the user could potentially fill the tablespace up completely—which is akin to a denial of service attack.

A more serious risk is the system privilege UNLIMITED TABLESPACE, which allows the user to have unlimited quota on all tablespaces without a specific quota grant on them. Let me repeat: The user has unlimited quota on all tablespaces, including SYSTEM—so the user can create objects there. Not good.

First, check for any explicit tablespace quota on SYSTEM:

select
   username,
   bytes/1024/1024 used,
   max_bytes/1024/1024 quota
from dba_ts_quotas
where tablespace_name = 'SYSTEM'
order by username;

If this query brings up something, you should evaluate it and if necessary revoke the quota.

The next step is to identify the users with unlimited tablespace system privileges.

select grantee
from dba_sys_privs
where privilege = 'UNLIMITED TABLESPACE';

This list should be carefully evaluated as this system privilege also includes that for SYSTEM tablespace.

Strategy
Now that you have identified all users and their quotas on tablespaces, your next task is to mitigate their risk. There are two tasks here, one more disruptive than the other.

First, try to remove the unlimited quota from the SYSTEM tablespace. This can be done without much disruption to applications. But before that, you need to make sure the SYSTEM tablespace does not contain objects from outside the SYS schema. The following query accomplishes that.

select owner, segment_type, segment_name
from dba_segments
where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM');

OWNER           SEGMENT_TYPE    SEGMENT_NAME
--------------- --------------- --------------
OUTLN           INDEX           OL$HNT_NUM
OUTLN           INDEX           OL$SIGNATURE
OUTLN           INDEX           OL$NAME
OUTLN           TABLE           OL$NODES
OUTLN           TABLE           OL$HINTS
OUTLN           TABLE           OL$

In this case, only the OUTLN objects are in SYSTEM tablespace, which is acceptable. If you see any other object, then you should move them.

The root cause of the problem could be the

select username
from dba_users
where default_tablespace = 'SYSTEM';

It should return only the following.

USERNAME
----------
SYSTEM
SYS
OUTLN

If it shows other usernames, alter the user to a different tablespace. For instance, to change the default tablespace of user SCOTT to USER_DATA, issue

alter user scott default tablespace user_data;

Then move all the objects out of the system tablespace.

alter table scott.tab1 move tablespace user_data;

Now, your next task is to ensure the quota is 0 for all users in the SYSTEM tablespace. There are two underlying reasons for the quota to be unlimited, one of which is the direct grant of unlimited tablespace. The other one is the grant of the role RESOURCE, which has UNLIMITED TABLESPACE as a system privilege in Oracle9 i Database and earlier. In contrast, Oracle Database 10g does not have the system privilege granted to the RESOURCE role.

For Oracle9 i Database

Confirm that UNLIMITED TABLESPACE is indeed granted to the RESOURCE role.

SQL> select *
  2  from dba_sys_privs
  3  where grantee = 'RESOURCE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE TABLE                             NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       UNLIMITED TABLESPACE                     NO

If UNLIMITED TABLESPACE is not listed, you don't need to do anything at this stage. Jump ahead to "Common Tasks."

For Oracle Database 10g

Confirm that UNLIMITED TABLESPACE is not granted to the RESOURCE role.

SQL> select *
  2  from dba_sys_privs
  3  where grantee = 'RESOURCE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE TABLE                             NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE PROCEDURE                         NO

Common Tasks

Identify the users with the UNLIMITED TABLESPACE privilege and alter their quota to unlimited on all tablespaces.

set lines 300
set pages 0
spool quota.sql
select 'alter user '||grantee||' quota unlimited on '||
tablespace_name||';'
from dba_sys_privs p, dba_tablespaces t
where p.grantee in (
select username
from dba_users
)
and p.privilege = 'UNLIMITED TABLESPACE'
and t.tablespace_name not in ('SYSTEM','SYSAUX')
order by grantee, tablespace_name
/
spool off

This creates a file with contents similar to

alter user ORAAGENT quota unlimited on INDEX01;
alter user ORAAGENT quota unlimited on INDEX02;
alter user ORADBA quota unlimited on INDEX02;

Next, you can execute this script file to have the unlimited quotas of these users. Finally, remove UNLIMITED TABLESPACE.

set lines 300
set pages 0
spool revoke_ut.sql
select 'revoke unlimited tablespace from '||grantee||';'
from dba_sys_privs
where privilege = 'UNLIMITED TABLESPACE'
/
spool off

Then execute this script file to revoke the privilege.

• The rowid will change because of the physical movement. If you have a rowid-based application, be aware of it.
• The indexes on the tables will become unusable—you have to rebuild them.

The change may also invalidate some dependent procedures.

Action Plan

Background
How well do you know your users? Do you know which machines they connect from, what they do when they connect, and so on? Probably not. But keep in mind that a successful security plan involves understanding these details, or at least the important ones. This is where the auditing facility in Oracle Database becomes very useful.

The auditing functionality in Oracle Database is quite comprehensive. Here you need to enable just a portion of that functionality to create a "profile" of the database. All you will attempt to do is see the users connecting, the userid they use to connect, and the type of authentication used. You will also uncover invalid login attempts—for instance, when the userid/password combination was wrong. As discussed previously, finding patterns to these unusual events may provide clues to uncovering a potential attack.

Strategy
To enable auditing, set the following parameter in the database initialization parameter file.

audit_trail = db

This is a static parameter; you must recycle the database for it to take effect. After this is done, issue the following for the auditing to kick in.

AUDIT SESSION;

This command will create a record whenever a user logs in or logs out. An audit trail will be created even if the login attempt was unsuccessful. After the database runs for a while, you can look for patterns in the audit trail. The column RETURNCODE records the Oracle Error Code the user received while making the operation.

SQL> select returncode, count(1)
  2  from dba_audit_trail
  3  group by returncode
  4  /

RETURNCODE   COUNT(1)
---------- ----------
         0    1710880
       604          3
       955         17
       987          2
      1013          2
      1017       1428
      1045          1
      1555          4
      1652          4
      1950          1
      2002          1
      2004          4
     28000          4
     28009          3

This clearly shows a pattern of errors; most of the operations were successful (where the return code is 0). For the rest of the codes, you can get descriptions by issuing

oerr ora <errorno>

from the *nix prompt. For instance, to find out what error code 1017 means, issue

oerr ora 1017

this returns

01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:

This, the most common error, is the target for your analysis as it will reveal attack patterns most effectively. A high incidence of invalid/password combinations may indicate an attempted break-in.

Now you should see where these sessions were coming from. Invalid passwords on a particular userid might indicate an attack on that userid. You can see the userids by:

select username, count(1)
from dba_audit_trail
where returncode = 1017
group by username
order by 2 desc;

USERNAME                         COUNT(1)
------------------------------ ----------
ARAO                                  569
DBSNMP                                381
DW_DQS                                181

Here we see that a user ARAO, apparently a human one, has attempted to use an invalid password 569 times. The next userid, DBSNMP (381 invalid password attempts), is not a human user; it's the userid of Enterprise Manager. This should immediately raise alarm signals—DBSNMP is a favorite hacker target.

To examine it more closely, let's see where these attacks are coming from:

select userhost, terminal, os_username, count(1)
from dba_audit_trail
where returncode = 1017
and username = 'DBSNMP'
group by userhost, terminal, os_username;

USERHOST                  TERMINAL        OS_USERNAM   COUNT(1)
------------------------- --------------- ---------- ----------
prlpdb01                                  oraprlp           199
prlpdb01                  pts/2           oraprlp             4
prlpdb01                  pts/7           oraprlp             9
prlpdb02                                  oraprlp           130
PRONT\PRANANDAT42         PRANANDAT42     ananda              3
progcpdb                  unknown         oracle             34

Notice that the server where this database is running is prlpdb01. Since this is a RAC database, the second node is also present, and the server name is prlpdb02. Most of the bad connection attempts have come from these servers, and using the OS user (oraprlp), which is the Oracle software owner. If this was indeed an attack, the user has access to the Oracle software owner and could have logged in as SYSDBA. There was no need to login as DBSNMP, and clearly the password was wrong. So, it does not look like an attack.

You can also see that the invalid logins are coming from two other machines: PRONT\PRANANDAT42 and progcpdb. They may seem suspicious, we can confirm the identities of these machines—the first one belongs to a DBA named "ananda" and the other one is the Grid Control server, which is expected to connect using this userid.

Next, analyze the pattern of these failures. If they are clustered around a specific time, it could indicate an attack.

SQL> select to_char (timestamp,'mm/dd/yy') ts, count(1)
  2  from dba_audit_trail
  3  where returncode = 1017
  4  and username = 'DBSNMP'
  5  group by to_char (timestamp,'mm/dd/yy')
  6  /

TS                     COUNT(1)
-------------------- ----------
10/14/05                      9
10/16/05                    222
10/27/05                     15
10/28/05                    125
11/09/05                      4
11/11/05                      2
11/12/05                      2
11/14/05                      2

As you can see, there are two distinct clusters: on 10/16 and on 10/28. You should mount a full investigation.

Action Plan

1. Turn on auditing by placing AUDIT_TRAIL initialization parameter.
2. Enable auditing for sessions.
3. Look for invalid or unsuccessful attempts to log in.
4. Examine the unsuccessful attempts for patterns (clusters of dates).

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.