September 1, 2010 at 8:10 pm
Comments posted to this topic are about the item Restricting SecurityAdmin on SQL Server 2005/2008
K. Brian Kelley
@kbriankelley
September 1, 2010 at 8:47 pm
Thanks for demonstrating this vulnerability.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 2, 2010 at 1:31 am
Excellent article!
Clement
September 2, 2010 at 3:51 am
Excellent, informative and slightly scarey - all at once!
September 2, 2010 at 6:44 am
Thanks for raising the awareness of this behavior!
September 2, 2010 at 10:04 am
Excellent article and I noticed this on your blog recently and was concerned.
It seems like this is a bug since essentially the securityadmin role now has no real meaning. You might as well be a sysadmin or not have this at all.
I would love to see a server level role that allowed someone to add a login, and a user for a specific database (s) only. That's the type of permissions that I often want to hand over to another person.
September 2, 2010 at 10:50 am
Excellent article.
I have one question about the workaround. If a person has SecurityAdmin, could they give themselves permission to alter the LimitSecurityAdmin trigger?
Chris
September 2, 2010 at 11:09 am
croberts 36762 (9/2/2010)
Excellent article.I have one question about the workaround. If a person has SecurityAdmin, could they give themselves permission to alter the LimitSecurityAdmin trigger?
No. As a securityadmin, you cannot assign permissions to your own login. But that makes me think there's another attack vector that I need to test.
Steve, I would agree with you, but Microsoft was adamant this isn't to be considered a bug. And to consider securityadmin = sysadmin. However, I know folks who've converted and have controls in place assuming securityadmin is limited, so they're stuck in the middle. I wish they would consider it a bug, too, because as Chris just brought up, there are surely more attack vectors.
K. Brian Kelley
@kbriankelley
September 2, 2010 at 12:16 pm
It is definitely good to have this pointed out since a lot of people do not realize it, and this was well written and clear.
From my standpoint, it tends to be irrelevant. Even if they cannot take full control of the server someone with even the SQL Server 2000 limited version of SecurityAdmin could cause so much mischief I would never hand it to someone I would not trust with full control server. At that point, I see the value of it in keeping honest people honest. Even if they know how to bypass it easily, they are faced with the fact that they are bypassing it. This reminds them that they are doing something that is properly in someone else's domain. For an trustworthy person, that is enough; for a non-trustworthy person even limited SecurityAdmin is far too much power.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
September 2, 2010 at 12:21 pm
timothyawiseman (9/2/2010)
It is definitely good to have this pointed out since a lot of people do not realize it, and this was well written and clear.From my standpoint, it tends to be irrelevant. Even if they cannot take full control of the server someone with even the SQL Server 2000 limited version of SecurityAdmin could cause so much mischief I would never hand it to someone I would not trust with full control server. At that point, I see the value of it in keeping honest people honest. Even if they know how to bypass it easily, they are faced with the fact that they are bypassing it. This reminds them that they are doing something that is properly in someone else's domain. For an trustworthy person, that is enough; for a non-trustworthy person even limited SecurityAdmin is far too much power.
Agreed, to a point. From a Principle of Least Privilege perspective, even if you trust someone to be a sysadmin, but they only should be doing the work of a securityadmin, you give them securityadmin. Only the permissions to do the job - no more, no less. And that's where this really busts audit controls.
K. Brian Kelley
@kbriankelley
September 5, 2010 at 2:59 am
I am going to lock out my SA per auidit need. Even if i use control server option, i can addd database and remove database, but i am not able to view my SQL server agent jobs.
Is it possible to view my SQL agent jobs for maintenance as a DBA, by using control server option.
Other than impersonating SA , is there any other option
Thanks
Eben
September 22, 2010 at 4:51 am
Hi Brian,
as a follow-up regarding our tweets and to share this information to other users as well (maybe they´re having an idea):
If we have a user who has only the right GRANT ALTER ANY LOGIN, then this user
is able to create a new login but cannot assign this new user to the sysadmin server role.
However, a user with GRANT ALTER ANY LOGIN can drop a user, which is member of the sysadmin server role, although just removing the user from that role doesn´t work.
In my case this is still too much power.
For example: I try to give a user the permission to check if the server-side accounts are properly mapped to database users and in case there´s a missing mapping to a database user, allow him to map the login.
Regards
Dirk
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
September 22, 2010 at 5:00 am
Steve Jones - Editor (9/2/2010)
...I would love to see a server level role that allowed someone to add a login, and a user for a specific database (s) only. That's the type of permissions that I often want to hand over to another person.
I should have read the other posts closer.
What Steve mentions here is exactly what would be on my mark.
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply