September 26, 2017 at 11:39 am
Eric M Russell - Tuesday, September 26, 2017 9:47 AMTDE and encryption of personal identifying columns should be standard database design practice today, and the keys held only by a handful of need to know executives. A sysadmin, even the resident God Of Data, doesn't need to read credit card numbers to perform daily maintenance operations on the Customer database and keep it running.
Likewise, the NSA doesn't need to read our email and eavesdrop on phone conversation without a warrant just to keep us protected from terrorists.
Good example. The encryption keys are often created by and managed by an admin. They shouldn't necessarily be. There are plenty of cases where someone like a CFO could generate one, keep the private key, and send the public one to the DBA for use on the system. Or require two admins to use this.
September 26, 2017 at 12:11 pm
Steve Jones - SSC Editor - Tuesday, September 26, 2017 11:36 AMpatrickmcginnis59 10839 - Tuesday, September 26, 2017 9:28 AMWhy not go ahead and set your system up then lock out the administrator account then? In this case, don't give anyone the complete set of administrative privileges? I'm guessing what you really want is a computer system that guarantees that no adverse actions can ever happen without an entity knowing about it, ie., you want a server running in a company that, for the set of employees that can be trusted, this set is empty.If your company has no trusted employees at all, you should probably not be running servers locally, instead, run them on Azure 🙂
I need some administrator. I also need some separation of duties. It's not a panacea to separate duties, but temptation and circumstances often create issues when there aren't checks and balances. Even if you trust someone today, that doesn't mean they are worthy of total trust.
Be careful of pinging the needle that we trust no one or everyone. Or that we trust them to do nothing or everything. There are a lot of spaces between there.
I don't expect any security system to prevent all issues. I do expect that I have the ability to account for some issues and track back on others. An administrator that owns everything also can erase and prevent us from understanding what happened. The military has to trust people, and they do have issues. They also put in controls that let them prevent future issues by tracking who did something, and controls that try to ensure that in critical situations, it's not easy for one person to go rogue.
If you want protection from an administrator, you could consider sending logs to a remote system that he lacks administrative access to. If the remote system subsequently ceased accepting new log updates, you could then investigate further. But are we so sure that windows lacks facilities to accomplish this? I'm still not convinced that windows cannot provide adequate security, in the base product or with additional tools.
September 26, 2017 at 4:40 pm
patrickmcginnis59 10839 - Tuesday, September 26, 2017 12:11 PMIf you want protection from an administrator, you could consider sending logs to a remote system that he lacks administrative access to. If the remote system subsequently ceased accepting new log updates, you could then investigate further. But are we so sure that windows lacks facilities to accomplish this? I'm still not convinced that windows cannot provide adequate security, in the base product or with additional tools.
There is some of this, but not a real time facility, meaning can I ensure this happens without an admin changing a log before it gets picked up. Timing is everything.
I don't think there are great separation of duties for everything. A domain admin or local admin is still a God in many ways. Perhaps there are ways, but they aren't easy, or convenient, which means they aren't deployed widely, and the OS is failing in its design. I also think Sudo represents a similar issue for Linux systems.
September 27, 2017 at 6:44 am
IT folks aren't gods. Executives with gold parachutes are gods that can escape the consequences of their hubris.
September 27, 2017 at 7:27 am
Where I work, security is fairly high on the list of priorities. With that being said, even here things could be more secure. Sure, I have a separate dedicated "admin" login, but I have both full sysadmin to my SQL Server and full local Admin to the same servers. At one time I seem to recall the security guideline documents we use indicated that if an application (such as SQL or OS) could be configured to require logins from two users before granting admin privileges, that that must be enabled.
Arguably, this is a feature that perhaps MS (and other OS and application vendors, as well as developers of in-house applications) should look at making available "natively." On the OS side it could be either an optional component, or something that is controlled by Group / Local Policy settings (and really only two settings, on or off, and once on, it requires the two admin login to turn it back off.) Implementing something like this in SQL Server might be more of a challenge (but it would go a long ways towards stopping applications that "need" SA privileges,) but I'd bet it could be done.
Sure, it'd be a pain when you need to fix something RIGHT NOW and all the other admin-level users are unavailable, or if it happens after hours, but that would be the price you'd have to pay for that level of security.
Such a system would also go a ways towards at least reducing the possibility of a rogue admin wiping out your systems (especially if your logins are not user / password, but user / smartcard & pin)
September 27, 2017 at 8:56 am
It would be nice to have a sudo like command for each user on Windows, that tracks back to that particular user using extra privileges.
September 27, 2017 at 9:00 am
Steve Jones - SSC Editor - Tuesday, September 26, 2017 4:40 PMpatrickmcginnis59 10839 - Tuesday, September 26, 2017 12:11 PMIf you want protection from an administrator, you could consider sending logs to a remote system that he lacks administrative access to. If the remote system subsequently ceased accepting new log updates, you could then investigate further. But are we so sure that windows lacks facilities to accomplish this? I'm still not convinced that windows cannot provide adequate security, in the base product or with additional tools.There is some of this, but not a real time facility, meaning can I ensure this happens without an admin changing a log before it gets picked up. Timing is everything.
I don't think there are great separation of duties for everything. A domain admin or local admin is still a God in many ways. Perhaps there are ways, but they aren't easy, or convenient, which means they aren't deployed widely, and the OS is failing in its design. I also think Sudo represents a similar issue for Linux systems.
I'm probably just trying too hard, I probably would not be able to produce a solution that would settle questions you have. This page has a pretty good read about it, while it is exchange specific, it does at least attempt to have a similar discussion.
https://blogs.technet.microsoft.com/exchange/2014/09/12/protecting-against-rogue-administrators/
I like the idea of removing local administrative privileges to individual machines from admin types and I bet a security layout that includes this has the potential to mitigate some pathways but none the less, the case remains that someone originally has to have that sort of access to modify the potential rogue admins access, but what about that original someone?
I know you are dead set on blaming the OS design for this, but I myself can't until I understand what the designs lack (and the explanation has to be specific beyond some "x versus y" where x and y are undefined. However I think we can agree to disagree here 🙂
September 27, 2017 at 11:01 am
Depending on the level of automation, there may be no reason for anyone to have sysadmin access on a database server. With tools like TeamCity, Octopus, DbUp, and RedGate, etc., most common day to day tasks can be performed without logging into the server and performing ad-hoc operations.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 27, 2017 at 11:44 am
patrickmcginnis59 10839 - Wednesday, September 27, 2017 9:00 AMI'm probably just trying too hard, I probably would not be able to produce a solution that would settle questions you have. This page has a pretty good read about it, while it is exchange specific, it does at least attempt to have a similar discussion.https://blogs.technet.microsoft.com/exchange/2014/09/12/protecting-against-rogue-administrators/
I like the idea of removing local administrative privileges to individual machines from admin types and I bet a security layout that includes this has the potential to mitigate some pathways but none the less, the case remains that someone originally has to have that sort of access to modify the potential rogue admins access, but what about that original someone?
I know you are dead set on blaming the OS design for this, but I myself can't until I understand what the designs lack (and the explanation has to be specific beyond some "x versus y" where x and y are undefined. However I think we can agree to disagree here 🙂
We do disgree. On the Windows side, a forest admin exists that can do anything. There could be a design decision that allows a forest admin to not be allowed to change anything inside a domain, but can set a user that is a domain admin to handle that task. Certainly this doesn't prevent the forest admin from creating a new account that's a domain admin, but that would be an audited event, which we can do. The same thing in SQL. SA can access any db. I can encrypt and hide some things, but that's a fundamental failing of the design in that encrypting data means that I have huge performance issues. I'd hope that the design would be sa/sysadmin can admin the instance, but not access any database. They aren't owners of dbs, nor can they be mapped to dbo. Instead, they'd need to create a second account at the db level. A pain, perhaps, but a fundamental good design choice for the system. Certainly we could give multiple rights to a person so they can handle all the tasks (instance and db level, or forest and domain), but we might ask that this is a separate account, or make it easy for this to happen.
September 27, 2017 at 11:55 am
Steve Jones - SSC Editor - Wednesday, September 27, 2017 11:44 AMpatrickmcginnis59 10839 - Wednesday, September 27, 2017 9:00 AMI'm probably just trying too hard, I probably would not be able to produce a solution that would settle questions you have. This page has a pretty good read about it, while it is exchange specific, it does at least attempt to have a similar discussion.https://blogs.technet.microsoft.com/exchange/2014/09/12/protecting-against-rogue-administrators/
I like the idea of removing local administrative privileges to individual machines from admin types and I bet a security layout that includes this has the potential to mitigate some pathways but none the less, the case remains that someone originally has to have that sort of access to modify the potential rogue admins access, but what about that original someone?
I know you are dead set on blaming the OS design for this, but I myself can't until I understand what the designs lack (and the explanation has to be specific beyond some "x versus y" where x and y are undefined. However I think we can agree to disagree here 🙂
We do disgree. On the Windows side, a forest admin exists that can do anything. There could be a design decision that allows a forest admin to not be allowed to change anything inside a domain, but can set a user that is a domain admin to handle that task. Certainly this doesn't prevent the forest admin from creating a new account that's a domain admin, but that would be an audited event, which we can do. The same thing in SQL. SA can access any db. I can encrypt and hide some things, but that's a fundamental failing of the design in that encrypting data means that I have huge performance issues. I'd hope that the design would be sa/sysadmin can admin the instance, but not access any database. They aren't owners of dbs, nor can they be mapped to dbo. Instead, they'd need to create a second account at the db level. A pain, perhaps, but a fundamental good design choice for the system. Certainly we could give multiple rights to a person so they can handle all the tasks (instance and db level, or forest and domain), but we might ask that this is a separate account, or make it easy for this to happen.
The reality is the vast majority of databases and servers do not need the same level of security nuclear launch codes are guarded with and in most cases doing so would be counter productive to day to day operations. As much as I support more options for security they should remain that, options.
September 27, 2017 at 12:46 pm
ZZartin - Wednesday, September 27, 2017 11:55 AMThe reality is the vast majority of databases and servers do not need the same level of security nuclear launch codes are guarded with and in most cases doing so would be counter productive to day to day operations. As much as I support more options for security they should remain that, options.
Options are fine, but we don't have option for good separation. And I agree with you. In most cases, this isn't needed.
September 27, 2017 at 3:00 pm
Steve Jones - SSC Editor - Wednesday, September 27, 2017 12:46 PMZZartin - Wednesday, September 27, 2017 11:55 AMThe reality is the vast majority of databases and servers do not need the same level of security nuclear launch codes are guarded with and in most cases doing so would be counter productive to day to day operations. As much as I support more options for security they should remain that, options.Options are fine, but we don't have option for good separation. And I agree with you. In most cases, this isn't needed.
Not to play the cynic, but it's often been the case that it's not needed....until someone makes off with your entire database... or 173 Million user records etc...It's unfortunately rather easy to under-secure something.
As I recall - one of the early complaints about MS' security philosophy was that you have to choose to make something (more) secure. I think in this day and age, we should be flipping that paradigm around: start with everything completely locked down, and choose to make thing LESS secure.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 27, 2017 at 3:31 pm
ZZartin - Wednesday, September 27, 2017 11:55 AMSteve Jones - SSC Editor - Wednesday, September 27, 2017 11:44 AMpatrickmcginnis59 10839 - Wednesday, September 27, 2017 9:00 AMI'm probably just trying too hard, I probably would not be able to produce a solution that would settle questions you have. This page has a pretty good read about it, while it is exchange specific, it does at least attempt to have a similar discussion.https://blogs.technet.microsoft.com/exchange/2014/09/12/protecting-against-rogue-administrators/
I like the idea of removing local administrative privileges to individual machines from admin types and I bet a security layout that includes this has the potential to mitigate some pathways but none the less, the case remains that someone originally has to have that sort of access to modify the potential rogue admins access, but what about that original someone?
I know you are dead set on blaming the OS design for this, but I myself can't until I understand what the designs lack (and the explanation has to be specific beyond some "x versus y" where x and y are undefined. However I think we can agree to disagree here 🙂
We do disgree. On the Windows side, a forest admin exists that can do anything. There could be a design decision that allows a forest admin to not be allowed to change anything inside a domain, but can set a user that is a domain admin to handle that task. Certainly this doesn't prevent the forest admin from creating a new account that's a domain admin, but that would be an audited event, which we can do. The same thing in SQL. SA can access any db. I can encrypt and hide some things, but that's a fundamental failing of the design in that encrypting data means that I have huge performance issues. I'd hope that the design would be sa/sysadmin can admin the instance, but not access any database. They aren't owners of dbs, nor can they be mapped to dbo. Instead, they'd need to create a second account at the db level. A pain, perhaps, but a fundamental good design choice for the system. Certainly we could give multiple rights to a person so they can handle all the tasks (instance and db level, or forest and domain), but we might ask that this is a separate account, or make it easy for this to happen.
The reality is the vast majority of databases and servers do not need the same level of security nuclear launch codes are guarded with and in most cases doing so would be counter productive to day to day operations. As much as I support more options for security they should remain that, options.
Do you mean the launch codes that were a string of all zero's for several decades? :hehe:
September 28, 2017 at 12:15 am
Your system is only as good as the weakest part.
Security can be a blocker if applied blindly. I'm finding that for the same reasons that tech debt is prevalent a security paradigm does not exist that examines what people need to do and implements something appropriate.
I have also found a lack of pragmatism in the security function. So much so that a programme director lost his rag and described the security team as being a bunch of guys who "sit on their arse and say no from 9 to 5".
The security team did not see their role as helping to deliver by defining what the security design should be. They saw their role as critiquing work that had been done.
We had a team of talented people working together to deliver the best solution we could, achieving the impossible for the ungrateful but getting demoralised by security guys turning up at the last minute to urinate on the work of others.
September 28, 2017 at 8:57 am
David.Poole - Thursday, September 28, 2017 12:15 AMYour system is only as good as the weakest part.Security can be a blocker if applied blindly. I'm finding that for the same reasons that tech debt is prevalent a security paradigm does not exist that examines what people need to do and implements something appropriate.
I have also found a lack of pragmatism in the security function. So much so that a programme director lost his rag and described the security team as being a bunch of guys who "sit on their arse and say no from 9 to 5".
The security team did not see their role as helping to deliver by defining what the security design should be. They saw their role as critiquing work that had been done.
We had a team of talented people working together to deliver the best solution we could, achieving the impossible for the ungrateful but getting demoralised by security guys turning up at the last minute to urinate on the work of others.
We had a similar issue to what you describe. For better or for worse - the failing we were able to identify was that security would be late in the game because they were being brought into the conversation at the very last minute. Meaning - They were a line item on the deployment checklist and frankly weren't being consulted ahead of time: The only choices they ended up with were to a. simply accept the design as is (security issues and all), or b. point out the security issues and end up with the reputation as you described.
As of now - they are part of pre-inception and initial design sessions, so that they can steer the design away from known pitfalls BEFORE development starts.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply