Is there any risk in using "SET FOREIGN_KEY_CHECKS = 0"?

Asked

Viewed 3,432 times

5

According to what I read somewhere, the FOREIGN_KEY_CHECKS:

... specifies whether or not to check foreign key restrictions for Innodb tables.

I mean, if the guy wants to disable the foreign key check, he does:

SET FOREIGN_KEY_CHECKS = 0;

I know it’s common to use this command in case of dumps to migrate bank, backups and things like... But I wonder if something that can disable the checking of foreign keys may or may not be harmful to an application.

I have the following questions:

  • In what types of scenarios would be valid or not use FOREIGN_KEY_CHECKS with the value 0?
  • FOREIGN_KEY_CHECKS is something to be considered in restricting in production environment? It is something that can cause some kind of inconsistency in my application/database?
  • If it is possible to disable the change in FOREIGN_KEY_CHECKS, how is the procedure?
mysql sql database security-guard

  • An analogy. The door of your house has a lock , leave the door open on the day of the change behind some risk !? It wouldn’t be safer to lock up and wait for the truck crew to ring the bell every time they bring it !?

  • 1

    Personally, the scenario that I see to use this is that of the server that is not yet in production, that is, a supposed server that will be "the new", technically it is not necessary to use in the source server (as far as I understand), because it is usually possible to do the whole backup doing "reading" (and depending on the method you use for the dump). The "failure" of security, or better integrity failure is only really possible if you expose the new server, but it doesn’t have much sense to leave something that is not yet "ready" active for other people right? :)

1 answer

4

I wouldn’t say it would cause problems for your database, in 100% of cases, making sure that it doesn’t bring harm in the future. I use this functionality only to recover backups, but never during the execution of a query by the Application, because disabling it can facilitate attacks or abnormalities in the bank. But like everything in information, each case is a case, for example:

  • During recovery of very large backups, some servers have limitation in SQL runtime, keep disabled may be faster.

  • In some cases, the database is outdated for you to maintain, with foreign keys between fields that are not used.

  • There are also some other specific cases where tables are populated in a random order, and instead spend time ordering them, some prefer to disable the check.

Browser other questions tagged mysql sql database security-guard

You are not signed in. Login or sign up in order to post.