Archives of the TeradataForum

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

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

Hi,

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),
     trim(substr(trim(a.statementtext),0,90)),
     trim(substr(trim(a.statementtext),90,180)),
     trim(substr(trim(a.statementtext),180,270)),
     trim(substr(trim(a.statementtext),270,360)),
     trim(substr(trim(a.statementtext),360,450)),
     date
     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>		<<First	<Prev	Next>	Last>>