Permissions for tables and Sprocs

Hi everyone,
We are creating a new application in .net and i would like to deny
access of the tables to users, but allow them to perform routines by
stored procedures. I am using Sql2005, and was wondering if anyone had
some knowlegde about this and point me in the right direction? Has
anyone set this up before?
ThanksThis is standard practice. As long as you don't use dynamic SQL in the proce
dures and the procedure
and table has the same owner, then the user only need permissions to the pro
cedures. See
http://www.sommarskog.se/grantperm.html for some elaboration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1166175982.773661.294100@.73g2000cwn.googlegroups.com...
> Hi everyone,
> We are creating a new application in .net and i would like to deny
> access of the tables to users, but allow them to perform routines by
> stored procedures. I am using Sql2005, and was wondering if anyone had
> some knowlegde about this and point me in the right direction? Has
> anyone set this up before?
> Thanks
>|||Tibor Karaszi wrote:
> This is standard practice. As long as you don't use dynamic SQL in the pro
cedures and the procedure
> and table has the same owner, then the user only need permissions to the p
rocedures. See
> http://www.sommarskog.se/grantperm.html for some elaboration.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Tibor,
Thanks for the quick reply, and i will look at the link you sent. One
problem though, we do use dynamic Sql within some of the sprocs, using
the execute_sql sproc?
Can you explain why this makes a difference?|||> Can you explain why this makes a difference?
that is just the way it is. SQL Server doesn't know what string you are abou
t to execute, so it
cannot allow the user of the proc to "execute just anything" without permiss
ion checking. I.e., a
conscious design decision. See the article, Erland mentions tow ways in 2005
to handle this
(signature and certificate or EXECUTE AS).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1166180348.976164.146510@.16g2000cwy.googlegroups.com...
> Tibor Karaszi wrote:
> Tibor,
> Thanks for the quick reply, and i will look at the link you sent. One
> problem though, we do use dynamic Sql within some of the sprocs, using
> the execute_sql sproc?
> Can you explain why this makes a difference?
>|||Tibor,
I have read a couple articles now and feel that Execute As seems to be
the easiest option for the moment, and talks about setting up another
user to execute the code as. With this in mind, some articles say
about assigning this new user to a Role in the database, and th Role is
actually what is assigned the permissions. Do you agree with this?
Also, when they say role, do they mean a database role, or application
role? I am not sure i am understanding the differences between the
two, or why one should be used rather then the other.
Thanks again for all your help so far|||Database roles are containers for database users and other roles. Roles are
commonly used for security because you can grant permissions to the role
once and then control security for individual users using role membership.
Application roles are different beasts. An app role is a database security
context that can be activated in application code using sp_setapprole. Once
activated, it remains in effect until the connection is closed or
sp_unsetapprole is activated. App roles provide a means to elevate
permissions while a user is using an application and prevent direct ad-hoc
database access.
Regarding procedure permissions, like Tibor suggested, normal ownership
chaining works well except when you execute SQL dynamically or need to run
statements that require more permissions. In those cases, you can specify
an alternate security context using certificates or EXECUTE AS. You can
peruse Erland's article for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1166184171.866934.154250@.80g2000cwy.googlegroups.com...
> Tibor,
> I have read a couple articles now and feel that Execute As seems to be
> the easiest option for the moment, and talks about setting up another
> user to execute the code as. With this in mind, some articles say
> about assigning this new user to a Role in the database, and th Role is
> actually what is assigned the permissions. Do you agree with this?
> Also, when they say role, do they mean a database role, or application
> role? I am not sure i am understanding the differences between the
> two, or why one should be used rather then the other.
> Thanks again for all your help so far
>