Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Oracle Security >> Oracle Security >> ORA-942 as addition to auditing
        
(Message started by: Pete Finnigan on Sep 4^th, 2006, 9:49am)

Title: ORA-942 as addition to auditing
Post by Pete Finnigan on Sep 4^th, 2006, 9:49am

From time to time I encounter developers or application admninistrators who tell me that they got an "ORA-942 table or view does not exist" message in a development or test environment and they don't know what table or view it is referring to or what SQL did that. The ORA-942 message does not give you that information.

So, to get more information on that, I set an event on ORA-942 in the database. Everytime ORA-942 occurs, a trace is written. Also a line is written in the alertlog that a ORA-942 message occurred.:

Mon Sep 4 08:27:38 2006
Errors in file /oracle/admin/DBNAME/udump/dbname_ora_3225.trc:
ORA-00942: table or view does not exist

In the trace file you can find what session did get the error and on what statement.

/oracle/admin/DBNAME/udump/dbname_ora_4321.trc
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
ORACLE_HOME = /oracle/product/9.2.0
System name: Linux
Node name: host.somedomain.nl
Release: ********
Version: **********
Machine: i686
Instance name: DBNAME
Redo thread mounted by this instance: 1
Oracle process number: 10
Unix process pid: 4321, image: oracle@host.somedomain.nl (TNS V1-V3)

*** SESSION ID:(14.4532) 2006-09-04 08:27:20.177
*** 2006-09-04 08:27:20.177
ksedmp: internal or fatal error
ORA-00942: table or view does not exist
Current SQL statement for this session:
select username from dba_users

----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+274 call ksedst() 0 ? 0 ? 0 ? 0 ? ED ?
BFFF7920 ?
ksddoa()+318 call 00000000 4 ? 3AE ? C53996C ?
[..]

Now consider a production database which is accessed only by (web-)applications. In most applications with fixed SQL a ORA-942 message should never occur. Setting an event on ORA-942 therefore might help us detecting whether someone is looking for tables or views by means of SQL injection or other methods, if the person actidentally fails to find them.

You can set events on sessions or the whole database. Setting an event on the database requires a restart.

In the spfile you can set the ORA-942 event with:
ALTER SYSTEM SET EVENT='942 trace name errorstack forever, level 4' SCOPE=spfile;

If you have set multiple events, your statement should look like this:
ALTER SYSTEM SET EVENT='942 trace name errorstack forever, level 4'
, '1000 trace name context forever, level 1' SCOPE=spfile;

You can reset events lateron with:
alter system reset event scope=SPFILE sid='*';

You might use this event in combination with the max_dump_file_size parameter to avoid filling up your file system. Also you might run a job to remove older trace files from time to time.

In conclusion, you might consider using the event on ORA-942 as addition to regular auditing, so you can now learn quickly when someone is looking for tables or views on your database where none should.

Title: Re: ORA-942 as addition to auditing
Post by Pete Finnigan on Sep 5^th, 2006, 3:08am

It would be interesting to benchmark that against a SERVERERROR trigger (which you'd be able to set on either a specific schema or the entire database).
(My 'track' routine logs the error in a table using an autonomous transaction, but it could also use UTL_FILE, email, Java Stored Procedure....if you want to make it harder for someone to cover their tracks.)

Code:

create or replace TRIGGER log_err after servererror on schema
DECLARE
v_temp VARCHAR2(2000) := substr(dbms_utility.format_error_stack,1,2000);
v_num NUMBER;
v_sql_text ora_name_list_t;
begin
v_temp := translate(v_temp,'''','"');
track(v_temp);
v_num := ora_sql_txt(v_sql_text);
v_temp := null;
BEGIN
FOR i IN 1..v_num LOOP
v_temp := v_temp || v_sql_text(i);
END LOOP;
EXCEPTION
WHEN VALUE_ERROR THEN NULL;
END;
v_temp := translate(v_temp,''''||chr(0)||chr(10),'"');
track(v_temp);
exception
when others then null;
end;

Title: Re: ORA-942 as addition to auditing
Post by Pete Finnigan on Sep 6^th, 2006, 12:51pm

Compared to the SERVERERROR trigger setting an event on ORA-942 is of course rather crude. And if your (3th party) application happens to yield ORA-942s, things would get rather annoying.

On the other hand events can only be altered after a restart of the database, which would be a lot harder to do (unnoticed) by a hacker. (But some DBAs schedule restarts of the database to avoid, for example, that the maximum open cursors is reached.

A risk is when the event is misused in a denial of service attack by trying to fill up the file system with traces. Limiting the max_dump_file_size parameter can make this a bit more work.

I've been thinking of other possible candidates to set an event on:
ORA-00990 missing or invalid privilege
ORA-01031 insufficient privileges
ORA-01039 insufficient privileges on underlying objects of the view
ORA-01749 you may not GRANT/REVOKE privileges to/from yourself
ORA-01996 GRANT failed: password file 'string' is full

ORA-28000: the account is locked (Trying for default accounts, are we?)

The possibilities are endless, not sure the amount of events you can set is too.

By the way, I just read in the Oracle9i Reference that you should not alter the event parameter, "except under the supervision of Oracle Support Services staff". I think you should at least test events on test-databases well, before trying them on a production system.

Title: Re: ORA-942 as addition to auditing
Post by Pete Finnigan on Sep 8^th, 2006, 2:10am

"ORA-28000: the account is locked"
I like that one. Probably one of early things a cracker would try would be the default accounts. A lot of the others could be bypassed by someone careful enough to check the standard privileges views.
Another set of possibilities are exceptions that might be raised by attempts to use SQL Injection on the standard Oracle packages. As I recall, some of those known and fixed exploits would return an error after having executed the malicious code.
If those exceptions could be audited, it may indicate someone is trying to see whether you've been patched (and it may be a good interim measure to audit them if there's no known patch or you can't yet apply the patch).

Title: Re: ORA-942 as addition to auditing
Post by Pete Finnigan on Sep 8^th, 2006, 12:39pm

For attemts to use (standard) packages and procedures by someone you might set an event on the "ORA-06550: line x, column y" message. You get this message for example when you try to execute a package or procedure that is not available. It often goes accompanied by more descriptive PLS messages, but you can't set an event on them. In a production environment the ORA-6550 message should be very uncommon, to say the least.

I still have to test this and other events to see if they really work properly though. I've only used the event on ORA-942 for a long time.

The only strange ORA-942's I've detected so far, by the way, are those that originate from the Intelligent Agent. The statement is always this one:

select sid from V$SESSION where audsid = ( select USERENV('SESSIONID') from dual )