The Most Dangerous SQL Statement.

Why is there a need to Know, Learn and fully Understand "The Most Dangerous SQL Statements"? Let us compare these SQL statements with our life decisions we make. For every decision or action we make in life, we know the risk; that is how we take life's decision by weighing advantages over risk. There are SQL commands that are dangerous therefore we need to know the impact and prepare for the risk before running those SQL commands.  Recently, I took a poll of SQL Statements that DBA and Developer think are the most dangerous. The list below is from the poll and it is by no mean in any order. Also, I will explain what each SQL statement does and why the samplers think are dangerous to them.

DROP: This is a powerful SQL statement for any database, not just Oracle. It has the ability to drop a table, database, schema, database objects, table column etc. Once the drop is performed, the objects may be gone forever and may not get it back, therefore, always make sure you do not need them before dropping them. If you are not 100% sure then make a backup copy saved somewhere which it is easily accessible when required. Some database like Oracle may let you recover from recycle bin, therefore, get yourself familiar with recovery tools like recycle bin, flashback etc on the database you work.

TRUNCATE  Deletes all the data from a table without removing the structure of these objects. Truncate generates no undo logs, therefore it is faster than a delete but the transaction cannot be rolled back. Also, TRUNCATE does not invoke delete triggers.  Speed comes with a price, therefore, you need to save a copy somewhere if you need it back.

DELETE: Deletes row from a table and the change should be committed after completion to make the delete permanent. Delete can be rollbacked because it generates undo logs. TRUNCATE deletes all the rows from a table without creating undo files and it cannot be rolled back at least in Oracle database. Truncate is much faster than a delete. The change is permanent for TRUNCATE.  Drop and Truncate are DDL statements and cannot be rolled back. In some database, delete is permanent because it issues auto-commit from the database or from the IDE client. 

COMMIT: This command makes the DML or DDL change permanent. In most databases, you have the option to run COMMIT after DML but in some, you don't get the options. SQL Server is one example that issue auto-commit by default, all your DML change are permanent and you don't have the options to rollback. Use it wise or lost it all.

GRANT ALL: This will let a user or schema owner have all the privileges that exist on a table or view. Some lazy DBA or developer use these but this is not recommended instead give the least privilege to user/schema to do a job.

CREATE OR REPLACE: The CREATE OR REPLACE  replaces the existing database object with a new one. This is different from dropping and recreating objects. This is used to preserve the grants on the object whereas in drop & recreate you will need to put the privileges back in. This is a serious command that can do harm when it wasn't intended to replace the existing objects. Always have a backup DDL or SQL of existing before replacing it. I have seen the issue with this command more in Synonym where developer and dba are creating new synonym using create or replace. If you are creating an object for the first time, avoid using create or replace. 

What do you think is most dangerous SQL command to you? Please comment below. To me it is a SELECT and here is why? All the SQL statements we described above are reversible if you have the good back up and proper disaster recovery in place. Yes, you may have to go through a pain to get it back but what is not reversible is the result of the SELECT statement. 

Hackers look for information from the database. Recently Equifax a credit reporting company was hacked where customers credit card and their personal information were stolen. It happened because of the SELECT statement. This damage that SELECT can make is something that is not repairable. If the select from the table was prevented, the problem could have avoided. The are few things DBA and developer can do to make SELECT restricted only to intended applications or authorized users. Use of proper grant, using stored procedures for reading data back from the table, and designing application and writing code free of SQL injections are some of the examples on how SELECT statement can be avoided by unauthorized users on a database level.

In a nutshell, If you ever come across these SQL commands, know what command does in detail, learn the risk and prepare what you can do to overcome the risk or prevent it. I would start with a rollback query when running these SQL statements. This helps you get the data and object back to their original states when asked or needed. A good developer and DBA starts with rollback plan, writes a rollback code and test it before implementing the actual change request. Always test your rollback plan, don't just plan your rollback in your head or in the paper because these kinds of rollback plans can surprise you and later your boss or HR can surprise you. Test your rollback and don't let anyone surprise you.