Hi All,

Have any of you used Applications Roles for sql server?

I'm thinking about using them to lockdown security by application.

Any drawbacks?

Thanks,

Fishy

Fishy wrote:

Hi All,

Have any of you used Applications Roles for sql server?

I'm thinking about using them to lockdown security by application.

Any drawbacks?

None, application roles rock Take these steps: - create your tables/views/procs - create the roles in your database/catalog - apply security settings for each role to each table/view/proc

at that point, your database is portable. This means that you can backup the database to a device file and restore it in another sqlserver instance, and by simply adding users to the roles, the database is instantly usable! With user-based security you can't do that, as users are not transportable accross sqlserver instances.

Otis wrote:

This means that you can backup the database to a device file and restore it in another sqlserver instance, and by simply adding users to the roles, the database is instantly usable! With user-based security you can't do that, as users are not transportable accross sqlserver instances.

Just want to clarify because I didn't think you associated users with Application Roles. I thought the idea was to create the Application Role with a password. Give permissions to the role to allow for whatever security your application needs. Then , within the app, call sp_setapprole passing the Application Role and the password. This would then override the permissions that the user would normally have.

So, am I off here?

Is it something you would still recommend?

Thanks,

Fishy

Fishy, your description of Application Roles is correct.

However, I would implement it like Frans has described, i.e. create your own DB Specific Roles, and then add users to it.

Thanks for responding DevilDog.

How would I prevent a user from using Query Analyser or Enterprise Manager from running sps or messing with my tables?

Thanks,

Fishy

oh! stupid me.

Indeed, it's something completely different. Sorry about that.

I have used the application role once, I now remember, but it wasn't very succesful (in a webapp that is), as the connection apparently was kept open or something, while I needed a connection per call. I never saw a system with it in production nor in documentation.

Otis wrote:

oh! stupid me.

Indeed, it's something completely different. Sorry about that.

Oh, no problem... It's good to know that even you can misunderstand once and a while And, even when you are wrong your still right (or at least you give a better reason to do something a different way).

Keep up the Great work! Your awsome!

Bring on Eddy!!!

Fishy wrote:

Bring on Eddy!!!

WOrking on it! DB2 driver is beta now, so more time for eddy I hope to have something usable next week

Fishy wrote:

How would I prevent a user from using Query Analyser or Enterprise Manager from running sps or messing with my tables?

Aside from not installing SQL QA or SQL EM on the users machine, you cant really. But, IMO, application roles as defined by SQL Server is a PITA.

I think you need to ask yourself, "How would someone ever learn the credentials to access the system?" So, you might store the connection string in a config file. If this is what you do, then I would encrypt it, so that users could not see it. If your app is a web app, then the only people with access to the web.config file, should, hopefully, be people who have administrative rights to that machine. If you sitll dont want administrators to have access, then encrypt the data in the web.config file.

The microsoft configuration management application block makes encrypting config files fairly simple.

Lastly, before you ship your code, you can write a simple TSQL Script, to Modify all tables, views, store procs, triggers, and user defined functions to use the "With Encryption" keyword to keep people from using EM or QA to get at that stuff.