Call: +44 (0)7759 277220 Call
Forum

Welcome, Guest. Please Login.
Dec 10th, 2024, 5:02am
News: If you would like to register contact the forum admin
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 18145 times)
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
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

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
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

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
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

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
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

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
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

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
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
  • PFCLScan PFCLScan

    Simply connect PFCLScan to your Oracle database and it will automatically discover the security issues that could make your Oracle database vulnerable to attack and to the potential loss of your data.

  • PFCL Obfuscate PFCLObfuscate

    PFCLObfuscate is the only tool available that can automatically add license controls to your PL/SQL code. PFCLObfuscate protects your Intellectual Property invested in your PL/SQL database code.

  • PFCLCode PFCLCode

    PFCLCode is a tool to allow you to analyse your PL/SQL code for many different types of security issues. PFCLCode gives you a detailed review and reports and includes a powerful colour syntax highlighting code editor

  • PFCLForensics PFCLForensics

    PFCLForensics is the only tool available to allow you to do a detailed live response of a breached Oracle database and to then go on and do a detailed forensic analysis of the data gathered.

  • Products We resell PFCLReselling

    PeteFinnigan.com Limited has partnered with a small number of relevant companies to resell their products where they enhance or compliment what we do

  • PFCLATK PFCLATK

    PFCLATK is a toolkit that allows detailed pre-defined policy driven audit trails for your Oracle database. The toolkit also provides for a centralised audit trail and centralised activity reporting

  • PFCLCookie PFCLCookie

    PFCLCookie is a useful tool to use to audit your websites for tracking cookies. Scan websites in a natural way using powerful browser driven scanner

  • PFCL Training PFCLTraining

    PFCLTraining is a set of expert training classes for you, aimed at teaching how to audit your own Oracle database, design audit trails, secure code in PL/SQL and secure and lock down your Oracle database.

  • PFCL Services PFCLServices

    Choose PFCLServices to add PeteFinnigan.com Ltd to your team for your Oracle Security needs. We are experts in performing detailed security audits, data security design work and policy creation

  • PFCLConsulting PFCLConsulting

    Choose PFCLConsulting to ask PeteFinnigan.com Limited to set up and use our products on your behalf

  • PFCLCustom PFCLCustom

    All of our software products can be customised at a number of levels. Choose this to see how our products can be part of your products and services

  • PFCLCloud PFCLCloud

    Private cloud, public cloud, hybrid cloud or no cloud. Learn how all of our services, trainings and products will work in the cloud

  • PFCLUserRights PFCLUserRights

    PFCLUserRights allows you to create a very detailed view of database users rights. The focus of the reports is to allow you to decide what privileges and accounts to keep and which to remove.

  • PFCLSTK PFCLSTK

    PFCLSTK is a toolkit application that allows you to provide database security easily to an existing database. PFCLSTK is a policy driven toolkit of PL/SQL that creates your security

  • PFCLSFTK PFCLSFTK

    PFCLSFTK is a toolkit that solves the problem of securing third party applications written in PL/SQL. It does this by creating a thin layer between the application and database and this traps SQL Injection attempts. This is a static firewall.

  • PFCLSEO PFCLSEO

    PFCLSEO is a web scanner based on the PFCLScan technology so that a user can easily scan a website for technical SEO issues