The key is really in the point that Sebastian made before.
As the site owner it is your responsibility to test the modules to ensure that they meet your needs, and that they don't have an impact on any other systems. With the nature of DNN it would be VERY hard for the DNN core to "restrict" access to the database tables and other items. Are there items that might require a bit more consideration, yes, but overall if you take just an ounce of prevention you will be just fine.
As for the ability for a developer to cause issues with a simple module, yes, this is fully possible, but this again is a key reason why you want to go with some form of "trusted" module developer. Now, the key question really becomes who do you consider trusted. I would assume that many people find commercial products trustable, and I hope that people find modules by myself and other active participants in the community as trustable, but that is something that as a module developer you have to take into consideration. You have to set some standard on how you do your business.
One thing I look at is support options. All modules that are available on my site are free modules, yet I provide free support for ANY issues that come up. I have forums for each individual product and I activly respond to any and ALL questions. I would be leery of installing a module from a developer that has NO support method, even more so if they don't have a contact e-mail address or soemthing.