Archives of the TeradataForum

Message Posted: Wed, 26 Jul 2006 @ 21:53:56 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Wednesday, July 26, 2006 16:52 -->

I haven't found any built in ones, or ones that are well published, but here are some quick ones that I came up with a little while ago. They're by no means a 'final version', but should be a good starting pt to hunt down offenders.

< summary of 'denied' queries via access logging >

     select  cast(Logdate || ' ' || Logtime as timestamp(0)) as "timestamp"
            ,sessionno
            ,username
            ,"database Name"
            ,"Object Name"
            ,"SQL"
     from    (select logdate (format 'YYYY-MM-DD')
                    ,logtime
                    ,sessionno
                    ,username
                    ,databasename as "database Name"
                    ,tvmname as "Object Name"
                    ,trim(StatementText) as "SQL"
             from    dbc.accesslog
             where   result    = 'd'
               and   logdate   > date-8
             ) dt
     order by 1,2;

< summary of 'denied' queries via dbql >

     select  a.StartTime (format 'YYYY-MM-DDBHH:MI:SS') as "timestamp"
            ,a.SessionID (format 'ZZZZZZZZZ9') as "session"
            ,trim(b.databasename) as "Username"
            ,c.objectdatabasename
            ,c.objecttablename
            ,a.errorcode as "error"
            ,a.errortext as "error Text"
     from    dbc.dbqlogtbl a
             inner join
             dbc.dbase b
        on   a.userid = b.databaseid
             left join
             dbc.dbqlobjtbl c
        on  (a.procid  = c.procid
       and   a.queryid = c.queryid)
     where   errorcode in ('3524','3523')
       and   cast(starttime as date) > date - 8
     order by LogonDateTime
     ;

< summary of bad logins via eventlog >

     select  username
            ,event
            ,logdate
            ,count(*)
     from    dbc.logonoff
     where   event in ('Bad Account','Bad Password','Bad User')
       and   logdate > date-8
     group by 1,2,3
     order by 3,1,2

	<Prev	Next>		<<First	<Prev	Next>	Last>>