| Author |
Topic |
|
homebrew
Posting Yak Master
114 Posts |
 Posted - 2006-03-28 : 14:00:47
|
| We're thinking of using application roles, but I don't quite get how to give it permissions. We currently have a sql login that's being used & abused. It has dbo access, and I want to use an app role that also has dbo access so that it can read, write and create work tables and run Stored Procedures via an application . I can see how to grant access to specific tables and SPs, but I don't want to have to manually manage specific objects. Is there a way to give the application role permission to the database so that it has rights to any tables & SPs in the future ?What am I missing ? |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-03-29 : 08:27:22
|
what's the sql login for?can you provide them with individual accounts or use nt accounts instead and say they're audited and be held responsible if things go wrong?accountability, based on my experience, is a deterrent to permission abuse  --------------------keeping it simple... |
 |
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2006-03-29 : 11:02:39
|
| The sql login is used by some applications that cannot use a windows login. But the sql login & password are commonly known, so we're looking for a solution that keeps the applications working, but does away with the sql login, and application roles seemed like a possible solution.To give the app role permissions to all the object for reading & writing, it seems that I can add the App Role to the database role db_owner. It works for the first connection, but the developer testing it got someking of "transport" error when he tried to re-connect a second time. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-03-30 : 03:22:48
|
| if you still provide dbo wouldn't it be the same scenario? did you know they can connect via query analyzer using the approle?if it's read and write, db_datareader and db_datawriter are your roles, but this will not provide them exec permissions on sprocs and functions--------------------keeping it simple... |
|
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2006-03-30 : 11:08:36
|
| How could you log into Query Analyzer with an approle ? It fails if I try it. I'm setting up different App Roles with different permissions depending on the applications. Crystal reports only need read, while some others will need to create temp work tables. I'm open to better suggestions.Thanks |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-03-30 : 19:43:58
|
| provide each of your developers with dbo rights if they need it but apply it on their nt account, this will give them a sense of accountabilityfor your applications, approles are a way to go, but only apply the minimum permissionsmy understanding of your problem is the abuse of the dbo permission right?Unless the application you're referring to will serve as the focus point for connection to the database by the developers, then you can even audit who did what... is that what you were planning to do?--------------------keeping it simple... |
|
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2006-03-31 : 15:07:55
|
| I'm trying to figure out the best way to fix a bad situation. Currently there's a widely known sql login that has db_owner permissions. I want to get rid of it without breaking the current production applications that use it. I was thinking I could phase it out by using the App Roles in the various applications. I've been doing a little home-made auditing of the current login, but don't have anything in place for the future.I spoke with someone else on this, and you both gave me pretty much the same advice ... Thanks for your help. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-31 : 18:13:34
|
quote:
Originally posted by homebrew
We're thinking of using application roles, but I don't quite get how to give it permissions. We currently have a sql login that's being used & abused. It has dbo access, and I want to use an app role that also has dbo access so that it can read, write and create work tables and run Stored Procedures via an application . I can see how to grant access to specific tables and SPs, but I don't want to have to manually manage specific objects. Is there a way to give the application role permission to the database so that it has rights to any tables & SPs in the future ?What am I missing ?
If you want security you have to actually set it up. There is no magic bullet.The first thing you should do is create roles in the database that represent what different type of database users need to do.Examples:DataEntryReporterAnalystThen grant only the necessary object permission to each role that it needs. You only have to specify the permissions on an object one time, so it is not that hard to manage.Then setup users. If at all possible, setup NT Domain groups that control access to the roles, and then add NT logins to those groups. Add the NT groups as logins and users in your database, and add the NT groups to the needed database roles. Do not put any users in the DBO role, or any of the other fixed database roles.If you really need to have a SQL Server login or application role, make the password impossible to remember to cut down on the abuse. Something like this:exec master.dbo.sp_password @old = NULL , @new = 'Dwz4u46ZgU5YcE7ZV>w5862uUR5K63#qnuZpjF8wY', @loginame = 'mylogin' Then set back, and wait for the complaints. If you get a lot, you know you're on the right track. The people that complain the loudest will be the ones that caused the most trouble.CODO ERGO SUM |
|
|
|
|
Application Role Permissions