A phased approach to securing your database infrastructure
by Arup Nanda
Published May 2006 - see Table of Contents & Security Primer
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?
Covered in This Installment:
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.
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.Implications
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.
2.2 Limit OS Authentication
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:
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
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.
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.Implications
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.
2.3 Disable Remote OS Authentication
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:
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.
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:
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
2.4 Secure SQL*Plus Using Product Profile
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.
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 validateImplications
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.
2.5 Rein In SQL*Plus
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.
$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.Implications
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.
2.6 Wrap Sensitive Code
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.
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.Implications
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.
2.7 Convert Derived Grants to Direct Grants
Step1 Connect A/****** Grant select on tab1 to B;
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.
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:
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.Implications
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
2.8 Limit Tablespace Quotas
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.
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
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
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.Implications
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 change may also invalidate some dependent procedures.
2.9 Monitor Listener Logs for Attempted Break-Ins
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.
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:
0 0 0
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 as '/u01/app/oracle/10.1/db1/network/log' /
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 nobadfile nologfile nodiscardfile fields terminated by "*" lrtrim missing field values are null ( log_date char(30) date_format date mask "DD-MON-YYYY HH24:MI:SS", connect_data, protocol_data, command, service_name, return_code ) ) 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 as l_begin number(3); l_end number(3); l_val varchar2(2000); begin 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; end;
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 ( 'password', 'rawmode', 'displaymode', 'trc_file', 'trc_directory', 'trc_level', 'log_file', 'log_directory', 'log_status', 'current_listener', 'inbound_connect_timeout', 'startup_waittime', 'save_config_on_stop', 'start', 'stop', 'status', 'services', 'version', 'reload', 'save_config', 'trace', 'spawn', 'change_password', 'quit', 'exit' )
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.
select log_date, connect_data command, protocol_data return_code from listener_log where connect_data in ( 'password', 'rawmode', 'displaymode', 'trc_file', 'trc_directory', 'trc_level', 'log_file', 'log_directory', 'log_status', 'current_listener', 'inbound_connect_timeout', 'startup_waittime', 'save_config_on_stop', 'start', 'stop', 'status', 'services', 'version', 'reload', 'save_config', 'trace', 'spawn', 'change_password', 'quit', 'exit' ) /
LOG_DATE COMMAND RETURN_CODE --------- -------------------- --------------- 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 1169Implications
None; this activity is merely diagnostic. Action Plan
2.10 Audit and Analyze User Access
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.
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 / 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
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.Implications
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.
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.