Do I have to use Stored Procedures to get query level security or can I still do this with Dynamic Sql?

Question

I'm developing an application where I'm concerned about locking down access to the database. I know I can develop stored procedures (and with proper parameter checking) limit a database user to an exact set of queries to execute. It's imperative that no other queries other then the ones I created in the stored procedures be allowed to execute under that user.

Ideally even if a hacker gained access to the database connection (which only accepts connections from certain computers) they would only be able to execute the predefined stored procedures.

Must I choose stored procedures for this or can I use Dynamic Sql with these fine grain permissions?

Answer 1

To only allow access to those exact queries, you have to use stored procedures.

What you could also do is create views and only access the data through those views. That would limit the data a potential attacker could access as well.

Answer 2

If you use Stored Procedures (and possibly views, too), you can remove any direct permissions on the underlying tables from your users - thus providing an extra "layer" of security. You only grant execute permissions on the stored procedures - that's all a regular user will ever see.

When you do dynamic / ad-hoc SQL, then you have to grant those users at least SELECT permissions on the base tables directly. This is definitely less of a deterrent - it will show a trained eye your database structure and thus reveal a lot more about your database than just a set of stored procedures.