| Author |
Topic |
|
ruan4u
Posting Yak Master
132 Posts |
 Posted - 2004-08-09 : 11:35:17
|
| Currently my app just supports Windows Authentication(WA).In that we have done a wrong thing, but ahving no roles and assiging all users with db_owner.In short no object level security.So what is the best way to accomplish this.Create a role and have rights defined to that Role.this way anyuser who gets access to the role will inherit the same rights.Basically, ours is not a complex app.All we have to ensure when we install at our clients is its not dbo.Will this help?Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-09 : 13:24:45
|
| Yes you would add a Windows group to the SQL Server. The Windows group would contain the list of users that get access to your app. Then you would create a role in the database and add the Windows group to the role. Then you would grant EXEC on the stored procedures to the role if you went down that route (which btw, I hope you did as that's the best approach), otherwise you would grant table level access to the role.Tara |
 |
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-08-09 : 15:44:56
|
| great,thanks tara and iam on the same page and thats what i have done.here is the small call that i need to make and i dont know if i should keep it isolcated.Our app generates Userid(operators) who would be accessing the App.This will typically also be a new windows account.Should also create this account from the app,or have a script ready for the client dbas(some client dont have dba's) to just execute. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-09 : 16:09:40
|
| The application should handle adding users.Tara |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-08-11 : 13:22:38
|
| Not convinced.could u pls elaboratethanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-11 : 14:15:13
|
| Why not? Why wouldn't your application handle the security? DBAs handle application level security. The application should handle the user level security.Tara |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-08-11 : 18:38:29
|
| our app has an operator table.An supervisor level operator will enter the list of operators.the operator id can be say ruan.But my windows login is domian\tduggan.My app will add ruan to the opertaor table.Should it also call a system SP to add domain\tduggan also to database user and assign a role to give thim permission.my question was if this can be done by DBA as a script.thanks tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-11 : 18:51:11
|
| Sure it can be done by a DBA with a script. But IMO, the DBA should not be responsible for this. The best approach is to have the domain accounts added to a group in the Active Directory. This group would be added to SQL Server and granted the necessary permissions. Then all that needs to happen is when a new user is created that it be added to the Windows group at the Active Directory. If that is not the approach that you are taking, then the application should add the Windows account to SQL Server then add the account to a role which already has permissions. You would use sp_grantlogin to add the account, sp_grantdbaccess to add the account to the database, and sp_addrolemember to add it to a role. GRANT is used for permissions but that should already be assigned when the database is installed. A supervisor level user would be the person administering this stuff through the application.Tara |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-08-12 : 08:13:02
|
| Great.We are taking Path I that you explained.except that we assign roles to that windows group.adding the account is now done manually done by our clients dba to the group.i dont think the burden is on the s/w vendor to provide the script too, if we deliver the Specs. |
|
|
|
|
Win Authen contd...