User Auditing

User Auditing

  -----------------------------------------------

 SYSDBA priviledges

      select * from v$pwfile_users;

      select GRANTEE,GRANTED_ROLE from dba_role_privs where GRANTED_ROLE='DBA';

  Audit

      SHow parameter audit_trail

      Show parameter audit

       

 To check the failed loggin 

      set lines 130

      col OS_USERNAME for a20

      col USERNAME for a20

      col USERHOST for a20

      select OS_USERNAME,USERNAME,ACTION_NAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode

      from dba_audit_trail 

      where returncode > 0

      action code-- logon (if account locked )

      reture code-- Ora error

Value for Action# column of 100 indicates a login record.

          select   ACTION#, userid,userhost,returncode, count(*)  from sys.aud$ 

         where NTIMESTAMP# >= sysdate-15  and returncode <> 0 and Action#=100

         group by  ACTION#, userid,userhost,returncode having count(*) > 100 

         order by ACTION#, userid,userhost