Calling all Teradata DBAs - Question around drop table grants.
I'm posting this to solicit input from fellow Teradata DBAs concerning when to grant CT/DT privledges to a Teradata user in a produciton database. In my particular situation, I have set up a user called 'Stored_Procs' in my production database. We utilize Informatica as our ETL tool. Informatica has developed a TPT driver that we use to load into our stage databases. Although this driver works ok, it is very limited compared to native TPT functionality. One of those limitations is the inability to specify a 'worktables' database for all TPT error tables. Because of this limitation, and the fact that the error tables are now embedded in my Production (Stage) database, I must grant CT/DT to the 'Informatica' user. Consequently, whenever a TPT Load job fails, the error tables must be dropped in the Stage database before the workflow can be recovered. As it stands now, sysdba is the only user with those permissions.
The ETL developers in our group have developed a stored procedure in STORED_PROCS that accepts databasename and tablename parameters and , when called, will drop the error tables associated with that table in that database. Safeguards have been added to the SP to make sure the database can never be anything other than a Stage database. If I grant 'STORED_PROCS' create table/drop table with grant permissions, then the ETL group can more quickly recover from a failed TPT Load job without waiting on a dba to drop the tables. Although my initial reaction was to not allow any developers DT permissions on any Production database, I can see the value in using 'STORED_PROCS' under these circumstances. I also understand the value of retaining the error tables to aid in debugging specific issues and this has been communicated to the develoment team. They would only be dropping the tables after determining they are no longer needed.
Does granting drop table on a Prod database to STORED_PROCS sound like a reasonable compromise under these circumstances? Would this violate any known SOX audits? Would this violate any internal audits at any of the companies that you work for? Please give me your thoughts and feedback...
Re: Calling all Teradata DBAs - Question around drop table grants.
A couple suggestions to help address the concerns about audit compliance. Your stored procedure can create some form of audit trail in a table showing when it was called, by whom, and what object was affected. Furthermore, if your error tables are consistently named you can make sure that the object(s) the stored procedure is affecting adhere to the naming standards within the staging database before allowing the DROP TABLE statement to be executed.
An alternative solution would be to rename the error tables and append a timestamp to the table name using the stored procedure. However, this would result in a larger volume of space being required for you staging environment so that the tables can be later dropped by a user (DBA) with sufficient privileges within the environment. The drawback is that this places a burden on the DBA's to keep on top of the objects in the staging environment and remembering to cleanup after any batch stream that failed from the night before.