database security
 Phase 2 Checklist (PDF)
security, database, 10g, All

Project Lockdown

A phased approach to securing your database infrastructure

by Arup Nanda

Published May 2006 - see Table of Contents & Security Primer

Phase 2

Duration: One Week

It's Phase 2 of your security and compliance project. Let's see what you can do within 5 business days to lock down your infrastructure.

Even if it only lasted 24 hours, you accomplished much in Phase 1 of Project Lockdown. Now it's time for the second phase, which will take about a week. In this phase, you will follow up on work performed in the earlier phase and make new headway as well. As before, you will take some simple yet highly effective actions for securing the database.

Why does this phase last a week, you may ask?

  • Some changes require setting of parameters that lead to recycling of the database, and that can take some planning.
  • Some changes are based on Phase 1 actions, and may require some careful planning.
Let's get started!

Covered in This Installment:
· 2.1 Remove utl_file_dir
· 2.2 Limit OS Authentication
· 2.3 Disable Remote OS Authentication
· 2.4 Secure SQL*Plus Using Product Profile
· 2.5 Rein In SQL*Plus
· 2.6 Wrap Sensitive Code
· 2.7 Convert Derived Grants to Direct Grants
· 2.8 Limit Tablespace Quotas
· 2.9 Monitor Listener Logs for Attempted Break-Ins
· 2.10 Audit and Analyze User Access

2.1 Remove utl_file_dir

Oracle provides a way to manipulate OS files from within the database, via SQL and/or PL/SQL, without accessing the host operating system. This facility is provided by the built-in PL/SQL package utl_file. For instance, here is a snippet of code that opens a file named myfile.txt in the directory /tmp and writes the line "Some Text" to it:

1  declare
2    l_filename  utl_file.file_type;
3  begin
4    l_filename := utl_file.fopen ('/tmp','myfile.txt','W');
5    utl_file.put_line (l_filename, 'Some Text', TRUE);
6    utl_file.fclose(l_filename);
7  end;

If the file myfile.txt is not present, this code segment will create it. However, for it to work, beforehand you must declare /tmp as a directory that can be opened by the utl_file package via the following initialization parameter:

utl_file_dir = '/tmp'

and then restart the database.

The presence of this parameter indicates that the users can create a file in the directory in /tmp, or overwrite any file there, as long as it's owned by the Oracle software owner. So what's wrong with that?

Nothing, except that most systems will not have this parameter set to /tmp. Rather, to allow developers or users to manipulate files without knowing in advance where the files will be created, the setting will most likely be "*", as in the following:

utl_file_dir = '*'

This means the user can open a file wherever the Oracle software owner has permission to read or write files—even the Oracle data files! Theoretically, then, an adversary can write a program that intentionally corrupts a data file. If this file happens to be for the SYSTEM tablespace, the entire database is gone. (Of course, you can restore it from backup, but the damage is already done.) Even worse, the adversary could damage an archived log file, which would prevent recovery beyond that archived log. The adversary could even remove the file, using the utl_file.fremove supplied package.

Therefore, setting utl_file_dir to "*" is simply a bad idea.

In Oracle9 i Database and later, there is no need for utl_file_dir—programs use the directory object to specify locations, not absolute directories. So, in the above example where the application needs to open a file myfile.txt in the /tmp directory, it can do so by:

1  declare
2    l_filename  utl_file.file_type;
3  begin
4    l_filename := utl_file.fopen ('TMP_DIR','myfile.txt','W');
5    utl_file.put_line (l_filename, 'Some Text', TRUE);
6    utl_file.fclose(l_filename);
7  end;

The directory object TMP_DIR must have been created earlier by:

create directory TMP_DIR as '/tmp';

This command, create directory, is given only once. Subsequently, anyone who has access to the directory can create or read files from here.

You could grant this directory object in a fine-grained manner. For instance, suppose you have a directory object on the directory where the alert log is located and you have created an application that reads and manipulates the alert log. You can grant read access to the directory to SCOTT but write access to ananda, who is a DBA.

So, remove the utl_file_dir from the database parameter and replace the code with directory objects.

As a corollary, you should also remove the create directory system privilege from PUBLIC and any other user.

The implication depends on how much the utl_file package is used to manipulate external files. If the package is never used, you can remove it right away. If you are using it, you need a plan first.

Action Plan

  1. Scan the code to see the following string: utl_file.fopen.

  2. Collect all the names in the first parameter. For instance ,when you see utl_file.fopen ('/tmp','myfile.txt','W'), record the value "/tmp". Go through all the files to get all the directory locations. There may be 10 files using "/tmp", 5 using "/ftpdata", and so on.

  3. As user SYS, create a directory object for each of these directories, e.g.

    create directory TMPDIR for '/tmp';
  4. Grant these directories to the users accessing them, e.g.
  5. grant read on directory TMPDIR to SCOTT;
  6. Remove the directory name form the code replacing it with directory object name. For instance, the line:
  7. utl_file.fopen ('/tmp','myfile.txt','W')

    will become

    utl_file.fopen ('TMPDIR','myfile.txt','W')
  8. Recompile the programs.

  9. Remove create any directory system privilege from PUBLIC and all users except DBAs.
    revoke create any directory from public;

2.2 Limit OS Authentication

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.

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';

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.

The implications of these changes may be extensive depending on the usage of these accounts. If you have any of these types of accounts, scan the programs to find out how easily they can be changed.

Action Plan

Find out which programs are using the OPS$ accounts.
If none then
  Check initialization parameter os_authent_prefix
If it's null then
    Change it to OPS$ (database restart required)
  Check password of OPS$ accounts
If not EXTERNAL then
    Change them to EXTERNAL
If some then
  Check if they are using it as a password as well (e.g. OPS$ORACLE/mypass).
If a password is used, remove it—e.g. the line sqlplus OPS$ORACLE/mypass should become sqlplus /.

2.3 Disable Remote OS Authentication

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.

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

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$%'
The output might show something like this:
------------- -------------------- ------------ -----------
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.

2.4 Secure SQL*Plus Using Product Profile

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.

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

  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
Before starting, note an important point: This approach applies to the SQL*Plus running on the server itself. It does not affect the SQL*Plus running elsewhere, such as at the client.

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.

2.5 Rein In SQL*Plus

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.

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.

The implications are similar to those of the previous task, only a little more extensive since they apply to SYS as well. If your scripts call the restricted commands, you may need to modify them or use a non-restricted version.

Action Plan

Identify the SQL scripts that call the restricted commands.
If some are found, then
  Modify them OR
Use an unrestricted version
  Rename sqlplus to sqlplus_orig
Create the file sqlplus with the restriction on

2.6 Wrap Sensitive Code

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:

     ('create or replace function myfunc ...')

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
    l_v varchar2(200);
    l_v := 'SecretKey';

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

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
    l_v1 varchar2(200);
    l_v2 varchar2(200);
    l_v3 varchar2(200);
    l_lv varchar2(200);
    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);

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.

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.

There are no implications. However, you should be aware of a very serious issue: Wrapping is a one-way street; you can wrap cleartext code, but not create cleartext from the wrapped code. So, you should preserve the cleartext code in some safe place for further editing. If you lose it, you lose the ability to change the code forever.

Action Plan

Identify the code containing the sensitive data.
IF in Oracle9i, THEN
  Break the values into multiple parts and embed each one inside a phrase.
Create a variable to extract the parts from the phrases.
Reconstruct the value inside the code.
  Do nothing.
Create script files from the clear text.
Wrap the script.
Run the wrapped script.

2.7 Convert Derived Grants to Direct Grants

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.

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.

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

There are two potential implications. First, since you are revoking privileges and re-granting them, you may introduce errors by failing to re-grant the privileges. So, it's important to get a snapshot of the privileges before and after this change to confirm success. Use this script to find out the privileges:
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.

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

2.8 Limit Tablespace Quotas

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:

   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.

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');
The output is
--------------- --------------- --------------
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.


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

There are no implications for removing these privileges and reducing the quota to 0 on the SYSTEM tablespace. However, if you have segments in the SYSTEM tablespace and you move them to a different tablespace, there will be two consequences:
  • 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

Find out the default tablespace of users other than SYS, SYSTEM, and OUTLN.
If it's SYSTEM, change it to a non-SYSTEM tablespace.
Find out the segments in the SYSTEM tablespace belonging to users other than SYS, SYSTEM, and OUTLN.
IF found, THEN


Move them out to their tablespaces
Rebuild indexes, materialized view, etc.

Find out the users with UNLIMITED TABLESPACE system privilege
IF found, THEN
  Grant them unlimited quota on all tablespaces except SYSTEM and SYSAUX
Revoke the system privilege UNLIMITED TABLESPACE

2.9 Monitor Listener Logs for Attempted Break-Ins

Phase 1.7, you learned how to secure the Oracle Listener by restricting the ability to change parameters online. That's fine and good, but how will you know if and when anyone tries to break in? Prevention is just part of the story; tracking the effectiveness of your defenses is just as important.

Well, you can explore attempted unsuccessful logins from the listener log file. When a user supplies the wrong password and attempts to modify the listener, the following message writes to the listener log:

12-NOV-2005 23:23:12 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=prolin01)(USER=ananda
))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER_PROLIN01)(VERSION=168821760)) * stop * 1190
TNS-01190: The user is not authorized to execute the requested listener command

This shows that on Nov 12 at 11:23 p.m. the user "ananda" was trying to stop the listener (note the presence of (COMMAND=stop) ) but supplied a wrong password. Could that indicate an attempted break-in? Possibly. Or perhaps ananda is a legitimate user but made a typo while entering the password, generating this error. But if you see this error many times, it may indeed signify an attempted break-in. You should visit user ananda and verify that he indeed was trying to stop the listener.

Similarly, when the admin options are restricted in the listener, your user can't just set the parameters from the command line. If the user tries a command like:

$ lsnrctl
LSNRCTL> set trc_level support

He will immediately get an error.

TNS-12508: TNS:listener could not resolve the COMMAND given

And the following entry will appear in the listener log file.

12-NOV-2005 23:26:34 * trc_level * 12508
TNS-12508: TNS:listener could not resolve the COMMAND given

This entry should tell you that someone was trying to set the trc_level directly on the LSNRCTL prompt. Again, this could be an honest mistake, but repeated attempts may indicate an attack.

The best approach is to check the listener log files periodically for these patterns. You can do that in several ways.

First, you could write a shell script using the following code:

$ grep "(COMMAND=stop)" listener.log | cut -f4 -d"*"

The following output would indicate that the listener command STOP was issued three times:


You can also make this script much more informative using sophisticated tools such as awk or scripting languages like PERL. However, if SQL is most familiar to you (which is likely), using SQL to extract information from this log file will be much more attractive.

The trick here is to use the listener log as an external table. First, create a directory object on the directory where the listener log is located.

create directory listener_log_dir

Next, create the external table on the log file. Note the contents of the log file carefully; it generally contains six pieces of information separated by the "*" character. These pieces will become columns of the external table.

create table listener_log
   log_date      date,
   connect_data  varchar2(300), 
   protocol_data varchar2(300),
   command       varchar2(15),
   service_name  varchar2(15),
   return_code   number(10)
organization external (
   type oracle_loader
   default directory LISTENER_LOG_DIR
   access parameters
      records delimited by newline
      fields terminated by "*" lrtrim
      missing field values are null
          log_date char(30) date_format 
          date mask "DD-MON-YYYY HH24:MI:SS",
   location ('listener_prolin01.log')
reject limit unlimited

After the table is created, you can select from it to confirm the definition is correct.

The lines are fairly descriptive but embedded commands such as (COMMAND=stop) can make it difficult to decipher. In that case, write another function to extract the values from the strings:

create or replace function extract_value
    p_in varchar2,
    p_param in varchar2
return varchar2
    l_begin     number(3);
    l_end       number(3);
    l_val       varchar2(2000);
    l_begin := instr (upper(p_in), '('||p_param||'=');
    l_begin := instr (upper(p_in), '=', l_begin);
    l_end := instr (upper(p_in), ')', l_begin);
    l_val := substr (p_in, l_begin+1, l_end - l_begin - 1);
    return l_val;

The monitoring becomes extremely easy then. All you have to do to uncover failed login attempts is issue

col l_user format a10with the embedde
col service format a20
col logdate format a20
col host format a10
col RC format a5
select to_char(log_date,'mm/dd/yy hh24:mi:ss') logdate,
       extract_value (connect_data,'HOST')    host,
       extract_value (connect_data,'USER')    l_user,
       extract_value (connect_data,'SERVICE') service,
       action                                 RC
from listener_log
where extract_value (connect_data, 'COMMAND') in

This returns output similar to

LOGDATE              COMMAND         HOST       L_USER     SERVICE              RC
-------------------- --------------- ---------- ---------- -------------------- -----
10/02/05 02:57:36    stop            prlddb01   oraprld    LISTENER_PRLDDB01    0
10/02/05 04:47:03    stop            prlddb01   oraprld    listener_prlddb01    0
10/03/05 15:14:53    stop            prlddb01   oraprld    LISTENER_PRLDDB01    0
11/18/05 23:48:26    reload          prlddb01   oraprld    LISTENER_PRLDDB01    0

As you can see, the output shows the date and time of the command along with the return codes. You can also modify the query to show only those values where the return code is not 0. You can also add a predicate to show records after a certain date only, so that the attempts for only today are shown. If you run this script every day, you can see the attempts of that day only.

The above shows data for invalid passwords only. For the admin-restricted listeners, the error string shows only three fields and thus the columns of the table LISTENER_LOG have different meanings: The second column shows the command issued by the user and the third column shows the return code.

        connect_data           command,
        protocol_data          return_code
from listener_log
where connect_data in

This returns:

--------- -------------------- ---------------
06-NOV-05 change_password      0
06-NOV-05 save_config          0
06-NOV-05 log_file             0
06-NOV-05 trc_level            12508
06-NOV-05 save_config_on_stop  12508
06-NOV-05 log_directory        12508
06-NOV-05 log_directory        12508
06-NOV-05 stop                 1169
06-NOV-05 stop                 1169
06-NOV-05 services             1169
06-NOV-05 status               1169
06-NOV-05 reload               1169
06-NOV-05 status               1169
06-NOV-05 stop                 1169
06-NOV-05 status               1169
06-NOV-05 stop                 1169
None; this activity is merely diagnostic.

Action Plan

  1. Create the Listener Log external table.
  2. Select records where administrative commands have been issued without a password, identifiable by a non-zero return code.
  3. Select records where commands were issued on the listener control prompt.
  4. If records are found that can't be explained by activities by any of the DBAs, you may have identified foul play.

2.10 Audit and Analyze User Access

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.

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.


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  /

---------- ----------
         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;
The output shows something similar to the following:
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;
the output is:
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.

Auditing may have minimal impact on performance; but it still has some impact. Furthermore, remember that the audit trails are written into the tablespace SYSTEM, which may get filled up. So you have to be vigilant for free space inside SYSTEM tablespace.

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.

Go on to Phase 3

 Read the Project Lockdown TOC and Security Primer