Note: This post spoils a puzzle solution for Might & Magic 2, as well as one for Wizardry Gaiden 3.
I can think of a couple cases where you are required to do something that feels like an exploit in order to proceed.
In Might & Magic 2, there's one place where you can get a nice diamond ring, one that provides a nice AC bonus. However, you don't get to keep it, because it's in an anti-magic area (so you can't teleport out), and when you leave, you are immediately sent to jail and your ring confiscated. However, you can keep the ring by giving it to a hireling, dismissing them, and then re-recruiting them. Sounds like an exploit, right?
Thing is, there's another item you have to use a similar method to get, the Element Orb. It's in a dungeon where teleportation is blocked, and if you try to leave with the Orb in your inventory, you get the message "The Orb denies you exit!", and you're not allowed to leave. In order to keep the Orb, you, again, have to give it to a hireling and dismiss t hem. Thing is, unlike that diamond ring, there's a whole quest that revolves around getting the Orb, and the Orb is needed to trigger an event elsewhere in the game. (It's not *technically* required, as all it gives you is information, but on a first playthrough without guides this is basically required, and the plot expects you to do this.)
Wizardry Gaiden 3 has a situation that feels similar. There's an Orb in an anti-magic area that you need, but once you get it, you can't leave, as the door will not open. Turns out that there's a box earlier in the game where items you drop go, and you need to take advantage of this box's existence to smuggle the orb out of the dungeon it's found in.
So, these two games require doing things that feel like exploits in order to get plot-critical items.
BlueMooner: Mods of course can significantly alter a game, but I don't consider them cheats. I suppose cheats to me are not just altering a game, but doing so to give powerups or advantages one isn't "supposed" to have. Mods that add new dungeons or NPCs or storylines or tactical options just make a game richer... add more stuff to do... increase the replay value. Again, I guess my position centers around what is "acceptable" in a game without being OP. I don't generally add content that gives more power, but I will do it if I think it's how something "should" be.
Thing is, you might have a mod that adds an interesting dungeon or side-quest, but the reward from it ends up breaking the game.
Incidentally, I like cheats that give me things I'm not supposed to have, so that I can play with things that aren't meant to be available to the player, like the abilities of the final boss, or of monsters in games where you don't normally get them.
I remember using a glitch in Fire Emblem: The Sacred Stones (FE8) that allows you to control the enemies, which then allows you to get many monster-only items. (It also allows you to see the status screens of gorgon eggs, which allows you to read the class description, a line of text that you can't see otherwise.)
Or there's hacking Wizardry 4 to raise your level so that Haman and Mahaman become usable, and it turns out that they're actually programmed to work in a sensible manner (so you can revive monsters that died this turn, or wipe out an enemy party of do-gooders, at the cost of a level). (In case you're wondering, DI/KADORTO, if you cast it, displays a "Not Dead" message, while Loktofeit silently does nothing.)