RE: PUP table

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Fri, 13 Aug 2004 14:00:19 +1000
Message-ID: <E10A27083F21674ABFAF6CD3D0BAC2F9016835@calbbsv025.cal.riotinto.org>

You can do with after logon on database trigger eg as per http://www.orafaq.com/scripts/security/notoad.txt

Note - sys can still connect
And, someone can just alter the name of client application to get around = it

SQL> CREATE OR REPLACE TRIGGER block_access   2 AFTER LOGON ON DATABASE
  3 BEGIN
  4 =20
  5 RAISE_APPLICATION_ERROR(-20000, 'No one can connect');   6 end;
  7 /

Trigger created.

SQL> show errors
No errors.
SQL> connect usera_at_db
Enter password:=20
ERROR:

ORA-00604: error occurred at recursive SQL level 1
ORA-20000: No one can connect
ORA-06512: at line 3

Warning: You are no longer connected to ORACLE. SQL> connect sys_at_db
Enter password:=20
Connected.
SQL>=20 SQL> drop trigger userb.block_access;

Trigger dropped.

SQL> connect usera_at_db
Enter password:=20
Connected.
SQL>=20
SQL>=20 HTH,
Bruce Reardon

-----Original Message-----
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Tim Gorman Sent: Friday, 13 August 2004 1:49 PM

Unfortunately (or rather, fortunately), a failure in an AFTER LOGON = trigger
does not do anything to interfere with the session...

   SQL> connect scott/tiger
   Connected.
   SQL> create or replace trigger x

     2          after logon
     3          on schema
     4  begin
     5          raise_application_error(-20000, 'raise error');
     6  end x;
     7  /

Trigger created.

   SQL> connect scott/tiger
   Connected.
   SQL>=20 However, in the "alert.log", it says:

Thu Aug 12 21:43:03 2004
Errors in file /Users/oracle/base/admin/TST1/udump/tst1_ora_8963.trc:

   ORA-00604: error occurred at recursive SQL level 1
   ORA-20000: AFTER LOGIN trigger exception
   ORA-06512: at line 2

And the trace file isn't much more helpful than that...

NOTICE
=20

This e-mail and any attachments are private and confidential and=20 may contain privileged information
=20

If you are not an authorised recipient, the copying or distribution=20 of this e-mail and any attachments is prohibited and you must not=20 read, print or act in reliance on this e-mail or attachments
=20

This notice should not be removed

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Aug 12 2004 - 22:56:09 CDT