Are Steam games locked down?

Steam doesn't lock your games down, no. They can be patched, modded, tweaked, and memory-hacked just fine, so long as the original game doesn't have its own built-in DRM scheme that would interfere. And of course, anything that changes the original game files will be undone when Steam pushes down an update, so such tweaks will have to be reapplied, possibly after waiting for the tweak to be updated to be compatible with the new version of the game files.

Because Steam is both an online store and a DRM scheme, the DRM is only of the sort where your game is tied to your login and usually has to be launched through Steam itself in order to play. Locking down the files would be superfluous (though not unthinkable), and thankfully Valve has enough market sense to maintain the Steam platform's DRM at a relatively low level that mostly fades away into the background context of an online store. The only times that people report Steam's DRM being troublesome is when you don't have an always-on network connection.

Usually Steam games just have a modified executable that requires Steam to be running, while all other game files are identical to the original ones. If mods require manipulating game files, Steam may however consider them corrupted and re-download them, or simply overwrite modifications when updating the game. Nonetheless you can set Steam to not update a specific game (and/or set the respective files write-protected), which should keep all modifications permanent (at the expense of not receiving updates for that game).