How to prevent human misoperation MySQL database tips _ MySQL

Source: Internet
Author: User
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

 
 
  1. [oldboy_c64 ~]# mysql --help|grep dummy
  2. -U, --i-am-a-dummy Synonym for option --safe-updates, -U.
  3. 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

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

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.