SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Elevation of Privileges


Elevation of Privileges

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: Administrators
Points: 223942 Visits: 19633
Comments posted to this topic are about the item Elevation of Privileges

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Gary Varga
Gary Varga
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35400 Visits: 6562
There simply isn't enough education on security.

I have two decades of commercial experience programming. I have read books, watched online presentations, read whitepapers, read technical articles and gone on training courses. All this on top of a computing MSc done after 6 years in education solely on computing. (This is to highlight how bad the situation is, not to brag :-)). And yet I do not know enough about security.

Security must become a first term (semester) subject at each level of education. For each company, it must be a requisite for each new IT employee to have done this in education or have to complete a course. It must be mandatory1 for IT staff remain up to date somehow.

1I am not stipulating how it is mandatory. This could be achieved by government regulation, accreditation (e.g. ISO), company policy or individually (e.g. IEEE or BCS membership etc).

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Conor.Lillis
Conor.Lillis
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 115
I remember trying to capture this using DDL triggers but never found a way of tracking role changes, yes you can catch new login/users but roles seemed more problematic
webrunner
webrunner
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11797 Visits: 4052
Great points.

I realize this is no substitute for wisdom and experience, but I wonder if at least there could be a workflow to adding logins to the sysadmin role. In other words, a two-factor request would need to be fulfilled - (1) email or form to approve the request and (2) text message to a phone to cross-validate.

I know this could probably be defeated but until it was, it would put potential escalations in front of the authorized person before becoming active.

Thanks,
webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
tripleAxe
tripleAxe
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2792 Visits: 13670
I've seen a lot of places use Active Directory Groups to control access to SQL Server, with a group for the DBA team which has been granted sys admin.

One thing to watch out for here is who can control membership of the group. I've seen non-DBAs "temporarily" added for "testing" purposes.

If you are worried about this it is worth using xp_logininfo from time-to-time to monitor who is in your DBA AD group. I once knew a suspicious DBA who automated a process to run this every few minutes and email an alert to him if group membership changed.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100088 Visits: 18616
For those that are curious, here is a blog rundown of that attack vector by Andreas.
http://www.insidesql.org/blogs/andreaswolter/



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Steve Jones
Steve Jones
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: Administrators
Points: 223942 Visits: 19633
Gary Varga (7/30/2014)
There simply isn't enough education on security.

I have two decades of commercial experience programming. I have read books, watched online presentations, read whitepapers, read technical articles and gone on training courses. All this on top of a computing MSc done after 6 years in education solely on computing. (This is to highlight how bad the situation is, not to brag :-)). And yet I do not know enough about security.

Security must become a first term (semester) subject at each level of education. For each company, it must be a requisite for each new IT employee to have done this in education or have to complete a course. It must be mandatory1 for IT staff remain up to date somehow.

1I am not stipulating how it is mandatory. This could be achieved by government regulation, accreditation (e.g. ISO), company policy or individually (e.g. IEEE or BCS membership etc).


Great points.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Eric M Russell
Eric M Russell
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43297 Visits: 12033
Security, things like accounts and their functional role, should be part of the system design documentation and also part of the QA test plan.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Miles Neale
Miles Neale
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5252 Visits: 1695
tripleAxe (7/30/2014)
I once knew a suspicious DBA who automated a process to run this every few minutes and email an alert to him if group membership changed.


Thanks for the idea tripleAxe. I am not paranoid but careful, and as a result I forwarded the link to the editorial and your comment to our Senior DBA to see if we are doing that as well. It is a good idea.

Not all gray hairs are Dinosaurs!
Eric M Russell
Eric M Russell
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43297 Visits: 12033
SQL Server Audit has an 'Addlogin' event and a 'Add Login to Server Role' event. For example, when a login is created or granted membership in SYSADMIN group.
http://msdn.microsoft.com/en-us/library/ms188646.aspx

But this doesn't handle the scenario where a domain account (ex: mycorp\johnsmith) becomes a member of a domain or local admin group (ex: mycorp\ProductionDBA or Builtin\Administrators) that has SYSADMIN membership. That's not a SQL Server meta-data change, but rather a change in Active Directory.

Using the following technique, you can leverage xp_logininfo to report on what accounts have SYSADMIN membership, either explicitly or via a domain group.
http://www.sqlservercentral.com/articles/Security/76919/


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search