Archives of the TeradataForum
Message Posted: Mon, 27 Feb 2006 @ 10:55:35 GMT
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,
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|