An example of the MySQL database technique to prevent human misoperation (this is a case where the old boy trained internal students and belongs to database security skills) a few years ago, the old boy personally encountered a murder case and the boss logged on to the database to update a record, as a result, I forgot to add where, so the tragedy happened,
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.