A Case Study of MySQL database techniques to prevent human misoperation

A Case Study of MySQL database techniques to prevent human misoperation

This question is from internal student training by old boys. It is a database security technique)

A few years ago, the old boy personally encountered a "Murder Case". The boss logged on to the database to update a record and forgot to add the where record, so the tragedy occurred, this allows me to be more familiar with MySQL incremental recovery than other knowledge points. At the same time, I am very concerned about internal database security. Every time I talk about this, I will tell the students the story of this leadership.

1. mysql help

 
 

  
  
[oldboy_c64 ~]# mysql --help|grep dummy       
  
  
 -U, --i-am-a-dummy Synonym for option --safe-updates, -U. 
  
  
i-am-a-dummy      FALSE 
 
 

After the option-U is added to the mysql Command, the mysql program will refuse to execute the UPDATE or DELETE statement without the WHERE or LIMIT keyword.

2. Specify-U for Logon Test

 
 

  
  
[oldboy_c64 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -U 
  
  
Welcome to the MySQL monitor. Commands end with ; or \g. 
  
  
Your MySQL connection id is 14 
  
  
Server version: 5.5.32-log MySQL Community Server (GPL) 
  
  
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 
  
  
Oracle is a registered trademark of Oracle Corporation and/or its 
  
  
affiliates. Other names may be trademarks of their respective 
  
  
owners. 
  
  
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
  
  
mysql> delete from oldboy.student; 
  
  
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column 
  
  
mysql> quit 
  
  
Bye 
 
 

Tip: deletion is not allowed without any conditions. The goal is achieved.

3. Create an alias to avoid misoperation of the old and DBA

 
 

  
  
[oldboy_c64 ~]# alias mysql='mysql -U' 
  
  
[oldboy_c64 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock 
  
  
Welcome to the MySQL monitor. Commands end with ; or \g. 
  
  
Your MySQL connection id is 15 
  
  
Server version: 5.5.32-log MySQL Community Server (GPL) 
  
  
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
  
  
mysql> delete from oldboy.student; 
  
  
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column 
  
  
mysql> delete from oldboy.student where Sno=5; 
  
  
Query OK, 1 row affected (0.02 sec) 
  
  
mysql> quit 
  
  
Bye 
  
  
[oldboy_c64 ~]# echo "alias mysql='mysql -U'" >>/etc/profile 
  
  
[oldboy_c64 ~]# . /etc/profile 
  
  
[oldboy_c64 ~]# tail -1 /etc/profile 
  
  
alias mysql='mysql -U' 
 
 

Conclusion:

After the option-U is added to the mysql Command, the mysql program rejects execution when an UPDATE or DELETE statement without the WHERE or LIMIT keyword is sent.