I tried to search to see if this was brought up before, so forgive me if it was and point me to the thread...

I admit the DB side is my worst technology skill...but I've always been taught to use stored procs and the benefits for doing so, primarily: 1) execution plan caching 2) security

i understand now that using sp_executesql with dynamic sql accomplishes #1, but what about the security standpoint?

the benefit of using stored procs is i can assign specific execute rights only on my db procs to my application user(s) -- with llblgen i have to assign select/insert/update/delete rights on all tables, opening up some major questions about that side of things.

can someone help me understand if i am correct...or if not, is there something that llblgen can do to alleviate this?

using this tool in a project is great, but within an organization that has strict security standards, this is being raised as an issue...

Ah, my favorite topic, procs vs dyn. sql!

heuertk wrote:

I tried to search to see if this was brought up before, so forgive me if it was and point me to the thread...

I admit the DB side is my worst technology skill...but I've always been taught to use stored procs and the benefits for doing so, primarily: 1) execution plan caching 2) security

i understand now that using sp_executesql with dynamic sql accomplishes #1, but what about the security standpoint?

The security standpoint is overrated for procs. I'll try to explain. The main point pro-procs people try to make is that with an API of stored procs, the user can only execute the procs, not a random delete query. So if a user gets access to the db and use a query tool, he can only execute the procs available.

This is true, but I haven't seen any proc API without procs which delete stuff. This means that using the provided api, the user can do whatever he wants, as the API provides the logic for it, just in another form.

Dyn. sql uses a different security model: you use roles, and place security restrictions on tables/views for these roles. You place users in these roles. You let applications login using different users, not the user.

the benefit of using stored procs is i can assign specific execute rights only on my db procs to my application user(s) -- with llblgen i have to assign select/insert/update/delete rights on all tables, opening up some major questions about that side of things.

You can use roles to do that, which means you have to assign the rights once. Roles are a good practise for security, also for procs. And even though you assign rights to your procs, a user with an account can do a lot of damage with the procs provided. I.o.w.: they don't help you a lot. Furthermore, you can't 'deny' access to tables for a user if he has execute rights on a proc which accesses tables.

can someone help me understand if i am correct...or if not, is there something that llblgen can do to alleviate this?

using this tool in a project is great, but within an organization that has strict security standards, this is being raised as an issue...

Security standards are great, however in the area of databases, they're often based on what someone needs to keep his/her job.