And what sort of controls do you need on the transactions?

If it is a heavy duty OLTP application then you better funnel all the inserts and updates through stored procedures or you gonna have locking problems. If it's a data warehouse, most of the data should be locked down anyhow since it's of a historical nature.

About the only exception to this rule is where there's a middle tier betwixt the client and the database. Even in that scenario, allowing the middle tier direct access to the tables basically means that the client is still required to go through the necessary processes.

From the read perspective (SELECT), you could possibly allow a little more freedom. But you're still better off creating filtered views - even if that view is at the current time little more than a straight output of the table. It allows you to clamp down on access if you ever need that facility (i.e. access rights can be enforced down to the row level - not just at the table level). If you want to have updateable views, the INSTEAD OF triggers can provide a facility to treat the update any way you'd like.

And yes, I do think you are being paranoid. But the second you look at things from the perspective of being a DBA, a heavy dose of paranoia is part of the job description.