March 31, 2009 at 7:26 am
We have a Server that has gotten out of hand with logins and rights.
We are trying to fix that but I'm trying to determine the best way to do that.
All applications run under a SQL Login user. This user generally runs only SP's but there may be some legacy items that use ad hoc.
All developers connect under domain\group for basic rights and then domain\user for very specific cases.
The idea is that this is a production server and only a couple of people should touch it. We want to make sure that it is more secure and the data is good.
Initially, I thought I could give the application SQL Login user datareader and execute on all stored procs that it needed in each database. (if that stored proc has a drop, create, etc does it then need datawriter?)
The developers would then get datareader and execute on all user sp's.
The problem is that the users could then just login with the application SQL Login and edit the data that way if it had datawriter. (I guess you could do a deny for that group).
Does anyone have any recommendations? Taking back permissions from developers is never a good thing for the developer (I've been there myself).
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
March 31, 2009 at 8:37 am
Jason Crider (3/31/2009)
All applications run under a SQL Login user. This user generally runs only SP's but there may be some legacy items that use ad hoc.
I'd recommend running a server-side trace either for each user or for each database to determine which logins need ad hoc access and then just grant them the rights absolutely necessary. You would have to be generous as the trace may not catch everything the application/login touches.
All developers connect under domain\group for basic rights and then domain\user for very specific cases.
If the developers are connecting using the same domain\user then they are getting the same rights every time. So if SSC\Jack is in the SSC\SQLDev group and I connect to the SQL Server I get whatever rights you've granted SSC\Jack and SSC\SQLDev every time. If I'm wrong here someone please correct me.
The idea is that this is a production server and only a couple of people should touch it. We want to make sure that it is more secure and the data is good.
Good idea, but unfortunately for you, this is going to cause some conflict.
Initially, I thought I could give the application SQL Login user datareader and execute on all stored procs that it needed in each database. (if that stored proc has a drop, create, etc does it then need datawriter?)
I tend to not use the datereader and datawriter because they are too broad. Create your own roles that do what needs to be done and only that. If the stored procedure and table(s) have the same owner then no additional rights other then EXEC of the stored procedure are needed. Unless you are creating or dropping permanent tables, then you would need to grant CREATE/DROP rights, but I wouldn't recommend creating or dropping permanent tables with the user stored procedures for this reason.
The developers would then get datareader and execute on all user sp's.
I would hope that you would have a development area for the developers where they can have elevated permissions, then they would only need standard permissions in the production database.
The problem is that the users could then just login with the application SQL Login and edit the data that way if it had datawriter. (I guess you could do a deny for that group).
Why do the users know the SQL Login and Password? Do they each have their own? If you are using a single Login then I would not give that to the users. I'd even consider having a different password between dev and prod so the developers don't know it.
Does anyone have any recommendations? Taking back permissions from developers is never a good thing for the developer (I've been there myself).
I wouldn't say it isn't a good thing, I'd say that it is a difficult thing. Every environment is different, but ideally, developers would have standard rights to production and elevated rights in the development area.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 31, 2009 at 9:36 am
Jack Corbett (3/31/2009)
Jason Crider (3/31/2009)
All applications run under a SQL Login user. This user generally runs only SP's but there may be some legacy items that use ad hoc.
I'd recommend running a server-side trace either for each user or for each database to determine which logins need ad hoc access and then just grant them the rights absolutely necessary. You would have to be generous as the trace may not catch everything the application/login touches.
Good idea. I've considered doing this. I know that I can enable auditing of logins through SQL but successful logins could be information overload and won't give me too much info. Also, all of our internal applications use the same SQL Login so it's not which logins, but what that login touches.
All developers connect under domain\group for basic rights and then domain\user for very specific cases.
If the developers are connecting using the same domain\user then they are getting the same rights every time. So if SSC\Jack is in the SSC\SQLDev group and I connect to the SQL Server I get whatever rights you've granted SSC\Jack and SSC\SQLDev every time. If I'm wrong here someone please correct me.
Currently, there's a domain\group for the developers that can handle generic access. When necessary, a domain\developer login can be created with different rights. My understanding is that domain\developer would overide domain\group but I could be wrong as well.
The idea is that this is a production server and only a couple of people should touch it. We want to make sure that it is more secure and the data is good.
Good idea, but unfortunately for you, this is going to cause some conflict.
Yea, I'm not that popular right now.
Initially, I thought I could give the application SQL Login user datareader and execute on all stored procs that it needed in each database. (if that stored proc has a drop, create, etc does it then need datawriter?)
I tend to not use the datereader and datawriter because they are too broad. Create your own roles that do what needs to be done and only that. If the stored procedure and table(s) have the same owner then no additional rights other then EXEC of the stored procedure are needed. Unless you are creating or dropping permanent tables, then you would need to grant CREATE/DROP rights, but I wouldn't recommend creating or dropping permanent tables with the user stored procedures for this reason.
Some of the DTS jobs will create and drop permanent tables, but we are mainly dealing with creating, dropping and editing temp tables. That's the general rule.
The developers would then get datareader and execute on all user sp's.
I would hope that you would have a development area for the developers where they can have elevated permissions, then they would only need standard permissions in the production database.
That's what we are trying to fix. Up until now anyone could do anything. I inherited this so I'm trying to fix it. Even the SQL Login for the apps is db_owner and that's very bad.
The problem is that the users could then just login with the application SQL Login and edit the data that way if it had datawriter. (I guess you could do a deny for that group).
Why do the users know the SQL Login and Password? Do they each have their own? If you are using a single Login then I would not give that to the users. I'd even consider having a different password between dev and prod so the developers don't know it.
Again, not my idea. That's how it was. They put it in their web.config. I've talked with the lead and we are looking at using machine.config so that it will bring it to a central location and we can change this password from time to time.
Does anyone have any recommendations? Taking back permissions from developers is never a good thing for the developer (I've been there myself).
I wouldn't say it isn't a good thing, I'd say that it is a difficult thing. Every environment is different, but ideally, developers would have standard rights to production and elevated rights in the development area.
Agreed. Determining what those standard rights are is the difficult thing right now. And making it continue to work while making these changes is proving difficult.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
March 31, 2009 at 9:55 am
I'm really surprised that there hasn't been more action on this thread as everyone has an opinion on this stuff.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 31, 2009 at 10:07 am
Jack Corbett (3/31/2009)
I'm really surprised that there hasn't been more action on this thread as everyone has an opinion on this stuff.
Really. I think it's because it's in the SQL Server7,2000 section and that's buried beneath the 2005 section where everyone probably puts there questions.
And thanks for your input. I really appreciate it.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply