MS SQL Server security - Least permissions required question

Author

Least permissions required question

Mike

2006-10-25, 6:01 am

Migrating from classic ASP and Access to ASP.NET and Sql Server Express
Edition... I'm sort of aware of the principal of giving users to a sql
server db the least permissions required for them to do whatever they need.

I have created a database and on the permissions tab, I found 2 types of
account: User and Guest. Neither of them seemed to have any permissions. I
selected Guest and gave it Connect, Select, Delete, Update and Insert
permissions. Everything works as I expected. I guess I've done this right
because the app refused to connect until I applied Connect permissions to
the Guest account.

My question is two-fold: in a web environment, where I will only ever need
one account to allow the app to connect, is what I have done ok from a
security pov? And how should I apply permissions regarding stored
procedures? Should I grant EXECUTE permission to the Guest account
regardless? Or should I grant EXECUTE to the account on a procedure by
procedure basis?

Thanks

Mike

Mike

2006-10-25, 6:01 am

"Mike" <Mike@discussions.microsoft.com> wrote in message
news:e3fkTSf5GHA.3560@TK2MSFTNGP03.phx.gbl...
> Migrating from classic ASP and Access to ASP.NET and Sql Server Express
> Edition... I'm sort of aware of the principal of giving users to a sql
> server db the least permissions required for them to do whatever they
> need.
>
> I have created a database and on the permissions tab, I found 2 types of
> account: User and Guest. Neither of them seemed to have any permissions.
> I selected Guest and gave it Connect, Select, Delete, Update and Insert
> permissions. Everything works as I expected. I guess I've done this
> right because the app refused to connect until I applied Connect
> permissions to the Guest account.
>
> My question is two-fold: in a web environment, where I will only ever need
> one account to allow the app to connect, is what I have done ok from a
> security pov? And how should I apply permissions regarding stored
> procedures? Should I grant EXECUTE permission to the Guest account
> regardless? Or should I grant EXECUTE to the account on a procedure by
> procedure basis?
>
> Thanks
>
> Mike
>

Now I've looked through some of the Q and As here (which I should really
have done earlier), my question becomes a bit more refined:

If I restrict all access to the db through procedures, will granting EXECUTE
on the procedure implicitly allow the user to INSERT/DELETE etc *for that
procedure* or will I still need to grant a blanket INSERT/DELETE permission
to the user? My question about how I've *created* the user still stands, as
does whether I should grant EXEC for everything or on a procedure by
procedure basis.

Mike

Dan Guzman

2006-10-25, 6:01 am

>> I have created a database and on the permissions tab, I found 2 types of

I suggest you avoid the guest user entirely and instead add the user used by
the application to the database. Using the guest user gives all logins
access under that security context and is much more than 'least permissions
required '.
[color=darkred]
> My question about how I've *created* the user still stands, as does
> whether I should grant EXEC for everything or on a procedure by procedure
> basis.

The Best Practice is to grant EXECUTE permissions only on the stored
procedures needed by the application. Permissions on the indirectly
referenced objects (e.g. tables) are not needed as long as the ownership
chain is unbroken. You should grant EXECUTE at a higher level (e.g. schema)
only if the user or role needs to execute all those procs.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Mike" <Mike@discussions.microsoft.com> wrote in message
news:eQtLacf5GHA.4592@TK2MSFTNGP04.phx.gbl...
>
>
>
> "Mike" <Mike@discussions.microsoft.com> wrote in message
> news:e3fkTSf5GHA.3560@TK2MSFTNGP03.phx.gbl...
>
> Now I've looked through some of the Q and As here (which I should really
> have done earlier), my question becomes a bit more refined:
>
> If I restrict all access to the db through procedures, will granting
> EXECUTE on the procedure implicitly allow the user to INSERT/DELETE etc
> *for that procedure* or will I still need to grant a blanket INSERT/DELETE
> permission to the user? My question about how I've *created* the user
> still stands, as does whether I should grant EXEC for everything or on a
> procedure by procedure basis.
>
> Mike
>

Mike

2006-10-25, 6:01 am

Yes, that helps clarify things for me immensely. Thank you Dan.

Mike

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:uux2FMi5GHA.3960@TK2MSFTNGP02.phx.gbl...
>
> I suggest you avoid the guest user entirely and instead add the user used
> by the application to the database. Using the guest user gives all logins
> access under that security context and is much more than 'least
> permissions required '.
>
>
> The Best Practice is to grant EXECUTE permissions only on the stored
> procedures needed by the application. Permissions on the indirectly
> referenced objects (e.g. tables) are not needed as long as the ownership
> chain is unbroken. You should grant EXECUTE at a higher level (e.g.
> schema) only if the user or role needs to execute all those procs.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Mike" <Mike@discussions.microsoft.com> wrote in message
> news:eQtLacf5GHA.4592@TK2MSFTNGP04.phx.gbl...
>
>

Sponsored Links