1. Our team is connecting to DB using SQL assistant. We connect to both dev and prod. Recently one of our team member by mistake connected to prod and deleted data thinking that he is doing it in dev. We would like to avoid such human mistakes. Any ideas in this regard are much appreciated.
2 .What we thought is actually whenever we connect to prod, it should display the screen in some color so that we will clearly identify that it is Prod. and Whenever we are running some delete or drop in Prod it should show us some kind of warning message (like in case of windows) before executing. Does this make sense? Please let me know.
Rather than expecting the tool to keep you from "human mistakes", you should probably review and update the permissions granted to users on the production box, i.e. revoke the delete permission.
I agree with VandeBergB. The Teradata Database provides access controls (GRANT / REVOKE) for this purpose. If your team member was not supposed to delete data from the prod system, then he should not have had access rights to do that.
Client tools (such as Teradata Studio, BTEQ, and SQL Assistant) do not provide the kind of functionality that you're hoping for, and are unlikely ever to have that kind of functionality added, because it would be redundant with the correct feature to use for this -- namely access controls.
Bottom line: You need to use the feature already provided by the Teradata Database for exactly this situation.
If you use the .NET data provider with SQL Assistant (as recommended), then you can used the Advanced connection properties to control the color bar at the top of the query window as a visual reminder. I do this all the time.
What I found helpful is, setting default role for my ID to ALL in development environments, but in production I set it to something that is less destructive. It is slightly annoying to have to set your role in production each time you need it, but well worth the price. Of course this assumes you use roles to control your permissions.
As Fred stated above you can set a color bar to be displayed across the top of the Query window. Maybe use Red for production and Green for development.
If you use Teradata.Net to connect then just set the color on the Advanced tab of the connection dialog. If you use ODBC you would need to connect once using Teradata.Net (using the same name as your ODBC DSN) and set the color. Then close the app and reopen it. As long as the ODBC DSN matches the name you used with Teradata.Net it will display the color for your ODBC connection.
If you dont have the .Net Data Provider installed the only way to set the color for ODBC is to edit the DataSources.config file directly. (not recommended)