Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Elevation of Privileges


Elevation of Privileges

Author
Message
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: Administrators
Points: 42464 Visits: 18876
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10782 Visits: 6360
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
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3291 Visits: 3805
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1063 Visits: 13108
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-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23654 Visits: 18280
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-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: Administrators
Points: 42464 Visits: 18876
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
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6831 Visits: 10078
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
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: 2764 Visits: 1694
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
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6831 Visits: 10078
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