Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Elevation of Privileges Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2014 8:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:36 AM
Points: 31,362, Visits: 15,823
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
Post #1597551
Posted Wednesday, July 30, 2014 3:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:45 AM
Points: 5,813, Visits: 3,734
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!!!
Post #1597620
Posted Wednesday, July 30, 2014 6:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:04 AM
Points: 19, Visits: 95
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
Post #1597673
Posted Wednesday, July 30, 2014 8:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:53 PM
Points: 2,431, Visits: 2,865
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


-------------------
"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
Post #1597757
Posted Wednesday, July 30, 2014 8:44 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:22 AM
Points: 502, Visits: 11,146
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.

Post #1597777
Posted Wednesday, July 30, 2014 8:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:00 PM
Points: 18,060, Visits: 16,093
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
Post #1597787
Posted Wednesday, July 30, 2014 8:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:36 AM
Points: 31,362, Visits: 15,823
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
Post #1597790
Posted Wednesday, July 30, 2014 9:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:23 PM
Points: 1,792, Visits: 5,041
Security, things like accounts and their functional role, should be part of the system design documentation and also part of the QA test plan.
Post #1597794
Posted Wednesday, July 30, 2014 9:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:43 PM
Points: 2,494, Visits: 1,581
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!
Post #1597829
Posted Wednesday, July 30, 2014 10:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:23 PM
Points: 1,792, Visits: 5,041
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/
Post #1597844
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse