logon – Technology Geek

it is difficult to audit failed sign-on attempts because the user never gets connected to Oracle but you can try this solution below :

Step 1 :
sqlplus / as sysdba
Connected.
SQL> alter system set audit_trail=DB scope=spfile ;
OR

audit_trail=true

Step 2 :

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.

Step 3 :

sqlplus / as sysdba

SQL> audit session whenever not successful ;

Step 4 :

Test auditing if it works

sqlplus scott/osama ;

ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

Step 5:

sqlplus / as sysdba

Use the below Script :

select os_username, username, terminal, to_char(timestamp,’MM-DD-YYYY HH24:MI:SS’) “TIME” from dba_audit_trail;

select os_username,username,userhost,to_char(timestamp,’mm/dd/yyyy hh24:mi:ss’) timestamp,returncode from dba_audit_session where action_name = ‘LOGON’ and returncode > 0
order by timestamp ;

OS_USERNAME USERNAME TERMINAL TIME
————— ————— ————— ——————-
oracle SCOTT pst/1 07-06-201216:21:13

Enjoy

Osama …

When I was Browsing On internet Today i saw Amazing Article Talking about how to prevent Developers From using Toad and other tools on production Database its Small script (After Logon Trigger ) On database level :

    CONNECT / AS SYSDBA;

    CREATE OR REPLACE TRIGGER block_tools_from_prod
      AFTER LOGON ON DATABASE
    DECLARE
      v_prog sys.v_$session.program%TYPE;
    BEGIN
      SELECT program INTO v_prog
        FROM sys.v_$session
      WHERE audsid = USERENV(‘SESSIONID’)
        AND audsid != 0 — Don’t Check SYS Connections
        AND ROWNUM = 1; — Parallel processes will have the same AUDSID’s

      IF UPPER(v_prog) LIKE ‘%TOAD%’ OR UPPER(v_prog) LIKE ‘%T.O.A.D%’ OR — Toad
         UPPER(v_prog) LIKE ‘%SQLNAV%’ OR     — SQL Navigator
         UPPER(v_prog) LIKE ‘%PLSQLDEV%’ OR — PLSQL Developer
         UPPER(v_prog) LIKE ‘%BUSOBJ%’ OR   — Business Objects
         UPPER(v_prog) LIKE ‘%EXCEL%’       — MS-Excel plug-in
      THEN
         RAISE_APPLICATION_ERROR(-20000, ‘Development tools are not allowed here.’);
      END IF;
    END;
    /
    SHOW ERRORS

Where I saw it :

http://www.psoug.org/snippet/Block_TOAD_and_other_tools_516.htm

Tag: logon

Prevent developers from using TOAD,other tools on production databases