July 29, 2014 at 8:04 pm
Comments posted to this topic are about the item Elevation of Privileges
July 30, 2014 at 3:00 am
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!!!
July 30, 2014 at 6:11 am
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
July 30, 2014 at 8:26 am
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 30, 2014 at 8:44 am
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.
July 30, 2014 at 8:53 am
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[/url]
Learn Extended Events
July 30, 2014 at 8:56 am
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.
July 30, 2014 at 9:00 am
Security, things like accounts and their functional role, should be part of the system design documentation and also part of the QA test plan.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 30, 2014 at 9:50 am
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!
July 30, 2014 at 10:18 am
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/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 30, 2014 at 2:02 pm
Eric M Russell (7/30/2014)
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.
Lots of ways to do this, but you also need to check it, or have a process that notifies you of events. It's also a balance as too many notifications can cause you to start ignoring many of them.
July 30, 2014 at 2:21 pm
Steve Jones - SSC Editor (7/30/2014)
Eric M Russell (7/30/2014)
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.
Lots of ways to do this, but you also need to check it, or have a process that notifies you of events. It's also a balance as too many notifications can cause you to start ignoring many of them.
I would expect adding sysadmin logins or granting sysadmin permissions to be a rare event.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 30, 2014 at 4:11 pm
FYI, here's a post someone sent me: http://blogs.xtivia.com/home/-/blogs/alerting-on-elevation-of-privileges-in-sql-server?_33_redirect=http%3A%2F%2Fblogs.xtivia.com%2Fhome%3Fp_p_id%3D33%26p_p_lifecycle%3D0%26p_p_state%3Dnormal%26p_p_mode%3Dview%26p_p_col_id%3Dcolumn-2%26p_p_col_count%3D2%26p_r_p_564233524_categoryId%3D12100%26p_r_p_564233524_resetCur%3Dtrue
July 31, 2014 at 7:50 am
If the windows group like 'MyCorp\ProductionDBA' or 'Builtin\Administrators' are members of SYSADMIN, and user account 'MyCorp\JohnSmith' is added to one of these groups, then he has sysadmin privillage. There is no 'CREATE LOGIN..' or 'GRANT..' operation, and as far as I know, there is no profiler event, extended events, trigger, or meta-data change within SQL Server that could be leveraged to alert this at the time the domain group membership is added.
However, one thing that could be done is to create a LOGIN trigger that checks the sysadmin privillage of an account at time of login and then compares user's account name to a table containing list of known admins.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 31, 2014 at 10:47 am
Eric M Russell (7/31/2014)
If the windows group like 'MyCorp\ProductionDBA' or 'Builtin\Administrators' are members of SYSADMIN, and user account 'MyCorp\JohnSmith' is added to one of these groups, then he has sysadmin privillage. There is no 'CREATE LOGIN..' or 'GRANT..' operation, and as far as I know, there is no profiler event, extended events, trigger, or meta-data change within SQL Server that could be leveraged to alert this at the time the domain group membership is added.However, one thing that could be done is to create a LOGIN trigger that checks the sysadmin privillage of an account at time of login and then compares user's account name to a table containing list of known admins.
No WMI-type event? Something like this?
http://msdn.microsoft.com/en-us/library/aa772153(v=vs.85).aspx
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply