Hoe to check which user had deleted data

UDA
N/A

Hoe to check which user had deleted data

data in Teredata table has been deleted manually (Not through Informatica Workflows) is there any way to find when and who did the last delete action on that particular table from DBC database in teradata.Any Information on this willl be really helpfull
3 REPLIES
Enthusiast

Re: Hoe to check which user had deleted data

Hi,

You can find out by using the system tables which maintain the History. USe three tables

dbc.Dbase
dbc.DBQLOGTBL
and dbc.DBQLSQLTBL

(JUst use the joins AS )
INNER JOIN
DBC.DBQLSqlTbl
ON DBQLogTbl.queryid = DBQLSqlTbl.queryid
and DBQLSqlTbl.ProcID=DBQLogTbl.ProcID
INNER JOIN
DBC.Dbase
ON DBQLogTbl.UserID = Dbase.DatabaseId

and put the attributes in the sel statment for what ever you want.

HOPE THIS WILL HELP YOU:-)

Regards
Rajeev T
Senior Apprentice

Re: Hoe to check which user had deleted data

Besides the QueryLog your site might have Access Logging enabled (view dbc.AccessLog).

For security related stuff like that Access Logging is the preferred solution, so your DBAs should think about switchin it on for important tables.

Dieter
Enthusiast

Re: Hoe to check which user had deleted data

Hi Rajeev,

Can you please provide the Script for this.

Thanks