Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Welcome, Guest. Please Login.
Nov 20th, 2017, 1:37am
News: Welcome to Pete Finnigan's Oracle security forum
Home | Help | Search | Members | Login
   Pete Finnigan's Oracle Security Forum
   Oracle Security
   Oracle Security
(Moderator: Pete Finnigan)
   ORA-942 as addition to auditing
« Previous topic | Next topic »
Pages: 1  Reply | Notify of replies | Send Topic | Print
   Author  Topic: ORA-942 as addition to auditing  (Read 14091 times)
Marcel-Jan
PeteFinnigan.com Junior Member
**






   
View Profile | WWW |

Gender: male
Posts: 83
ORA-942 as addition to auditing
« on: Sep 4th, 2006, 9:49am »
Quote | Modify

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.
IP Logged
gamyers
PeteFinnigan.com Junior Member
**



I love YaBB 1G - SP1!

   
View Profile |

Posts: 80
Re: ORA-942 as addition to auditing
« Reply #1 on: Sep 5th, 2006, 3:08am »
Quote | Modify

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;
IP Logged
Marcel-Jan
PeteFinnigan.com Junior Member
**






   
View Profile | WWW |

Gender: male
Posts: 83
Re: ORA-942 as addition to auditing
« Reply #2 on: Sep 6th, 2006, 12:51pm »
Quote | Modify

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.
IP Logged
gamyers
PeteFinnigan.com Junior Member
**



I love YaBB 1G - SP1!

   
View Profile |

Posts: 80
Re: ORA-942 as addition to auditing
« Reply #3 on: Sep 8th, 2006, 2:10am »
Quote | Modify

"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).
IP Logged
Marcel-Jan
PeteFinnigan.com Junior Member
**






   
View Profile | WWW |

Gender: male
Posts: 83
Re: ORA-942 as addition to auditing
« Reply #4 on: Sep 8th, 2006, 12:39pm »
Quote | Modify

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 )
IP Logged
Pages: 1  Reply | Notify of replies | Send Topic | Print

« Previous topic | Next topic »

Powered by YaBB 1 Gold - SP 1.4!
Forum software copyright 2000-2004 Yet another Bulletin Board