469,090 Members | 1,092 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

Securing local MSDE applications

Hi,

I am writing an application that uses MSDE to store data.
Both application & MSDE run on the same computer.
I want to regulate the operations done on the DB by the user. For
example, I don't want to allow "standard" users to delete records,
update certain fields, etc...
I can regulate these rules within my program, but what if the user runs
MSDE query for example on the DB and fetches the list of users &
passwords from the DB ?
In other words, I need to make sure only the application has access to
the DB. This seems like a common type of problem but I haven't been
able to find any solutions.
Any suggestions would be greatly appreciated.

Danny

Jul 23 '05 #1
1 1276
> In other words, I need to make sure only the application has access to
the DB. This seems like a common type of problem but I haven't been
able to find any solutions.
One approach is to employ application roles. This allows users to connect
using their regular login but with limited permissions until the application
role is activated.

You can create an application role using sp_addapprole and grant the needed
permissions:

USE MyDatabase
EXEC sp_addapprole 'MyAppRole', 'MyAppRolePassword'
GRANT ALL ON MyTable TO MyAppRole

You can then enable an app role from within your application.

EXEC sp_setapprole 'MyAppRole', 'MyAppRolePassword'

With this approach, users are limited to their regular SQL Server
permissions outside your application. Another method is to create and use a
standard SQL login for database connectivity within your app that is unknown
to your users.

See the Books Online for more information.
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Danny Liberty" <dl******@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com... Hi,

I am writing an application that uses MSDE to store data.
Both application & MSDE run on the same computer.
I want to regulate the operations done on the DB by the user. For
example, I don't want to allow "standard" users to delete records,
update certain fields, etc...
I can regulate these rules within my program, but what if the user runs
MSDE query for example on the DB and fetches the list of users &
passwords from the DB ?
In other words, I need to make sure only the application has access to
the DB. This seems like a common type of problem but I haven't been
able to find any solutions.
Any suggestions would be greatly appreciated.

Danny

Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by izzy | last post: by
2 posts views Thread by Rosy Moss | last post: by
7 posts views Thread by DKode | last post: by
11 posts views Thread by Wm. Scott Miller | last post: by
10 posts views Thread by Jim Devenish | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.