Make mods for Skyrim

I'm sure they're vulnerable in some way, as hardly any software is free of potential exploits, however, I havn't heard of any active exploits in Gamebryo games. As far as i know, mods have no way of interacting with anything outside of the game. Some tools, such as SKSE and ScriptDragon, make it possible for them to access configuration files in their directories, but that shouldn't be a big concern. Also, remember that the CK is not the only way to make .ESP files. Many programs have been vulnerable to maliciously-modified files. I would say the risk is fairly low, but it is not non-existent.

If you deeply care about security, I would advise against immediately downloading new mods, and letting the other people test them first. The added benefit of that approach is that mature mods are less likely to break your save or have dirty edits and such.

To answer the new question, mods can modify quite a bit, including most visuals and many game mechanics. There are some things that mods cannot do, such as add skill trees (Though they may emulate them in a way). I failed to locate any details on exactly what files the Steam Workshop will accept (A detail one would image should be on their wiki), but at the very least they will be .esp files and .bsa archives, textures, models, sounds, Papyrus scripts and text files.

Mod Organizer and Wrye Bash will both display missing and present Masters for mods.

If you don't want to make the switch to MO (though I'd recommend it) and you don't need a bashed patch, it would still be worthwhile to download Wrye to just run every now and then to check dependencies.