Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 27 Feb 2006 @ 10:55:35 GMT

  <Prev Next>  

Subj:   Access log Entries problem.
From:   Srikanth Kappagantula


Currently I am working on reports which generates audit for every Create User request. To Implement that I have used begin logging for a particular login.

I could manage to get the requesttext for all successful user creations. But the list also contains the entries from dbc.accesslog where user creation is failing because of some or the other reason. ( perm space is given when no perm space should be given at user level).

How can I filter all the user records where user creation is failing?

Pls. help its urgent.

I also enclose the query for ur reference.

     sel distinct trim(a.username) "Administrator",a.logdate,trim(a.logtime),
     case when trim(a.statementtype) like 'Create User' then 'User created
     under database' when trim(a.statementtype) like 'Comment Set' then 'User
     contact information modified' when trim(a.statementtype) like 'Modify
     Database' then 'User database information modified' when
     trim(a.statementtype) like 'Drop Database' then 'User dropped' else
     trim(a.statementtype) end "Action Performed", trim(a.databasename),
     from dbc.accesslog a,dbc.logonoff l where a.logdate=date and a.username
     in (sel distinct username from dbc.acclogrules where AcrCreateUser='E +'
     or AcrCreateDatabase='E +' or AcrDropDatabase='E +' or AcrDropUser='E
     +') and databasename not in ('PUBLIC') and statementtext is not null and
     a.result='G' and a.accesstype in ('CD','CU','DD','DR','DU') and
     a.statementtype not in ('Give')
     and databasename not in ('TEMP_DDI_TEMP') and statementtext not like
     ('%TEMP_DDI_TEMP%') and l.sessionno=a.sessionno

Thanks and regards,

Srikanth K.

  <Prev Next>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020