Quick synopsis:
The guys on my team have been working on a production database (sql server 2005). We've added various things such as constraints, added triggers, etc.
我团队中的人员一直在研究生产数据库(sql server 2005)。我们添加了各种内容,例如约束,添加的触发器等。
Now we've found that someone or something has been rolling back our changes at various times. Problem is we all share a common admin login. (dumb, yeah I know, we're fixing this). Its causing tons of frustration, at this point we just want to find out whodunnit.
现在我们发现有人或某些东西在不同时间回滚了我们的变化。问题是我们都共享一个共同的管理员登录。 (愚蠢,是的,我知道,我们正在解决这个问题)。它造成了大量的挫败感,此时我们只是想找出whodunnit。
How would you go about tracking down the guilty party?
你会如何追查有罪的党?
NOTE: I'm NOT looking for a way to fix this, that's already being done. I'm looking for a way to track down the culprit.
注意:我不是在寻找解决这个问题的方法,这已经完成了。我正在寻找一种方法来追查罪魁祸首。
11 个解决方案
#1
Stay away from production databases. Create your scripts and email them to the DBA in charge (if you don't have one, get one). Developers with access to production database is a recipe for disaster - I don't have it and don't want to have it.
远离生产数据库。创建您的脚本并通过电子邮件发送给负责的DBA(如果您没有,请获取一个)。能够访问生产数据库的开发人员是灾难的一种方法 - 我没有它,也不想拥有它。
#2
Tracking down your problem is obviously a symptom and not the cause: since it's a SQL Server 2005 database, there should be a 'Default' trace that runs out of the box. It's very lightweight, but does include some object creation and deletion. You can view it from the sys.traces view using the following query:
追踪您的问题显然是一个症状,而不是原因:因为它是一个SQL Server 2005数据库,应该有一个开箱即用的“默认”跟踪。它非常轻量级,但确实包含一些对象创建和删除。您可以使用以下查询从sys.traces视图中查看它:
SELECT *
FROM sys.traces
WHERE id = 1
It rolls over after only a few MB so it's usefulness will depend on how much activity there is on the server.
它仅在几MB之后翻转,因此它的有用性将取决于服务器上的活动量。
Presumably, the real cause is not having your changes scripted and in version control.
据推测,真正的原因是没有脚本化和版本控制。
Agree with the other posters who mentioned that all changes to a Production Database should be done only by an Admin, and not individual developers.
同意其他提及生产数据库的所有更改只能由管理员而非个别开发人员完成的海报。
#3
I'll assume that you have a audit log with change data capture-esque features. This will be keeping track of the who, what, and when of each change.
我假设你有一个带有变更数据捕获特征的审计日志。这将跟踪每次更改的人员,内容和时间。
Are the rollbacks intermittent or consistent? Any chance you have auto commit turned off and forget to commit your changes?
回滚是间歇性的还是一致的?您是否有机会关闭自动提交并忘记提交更改?
There can't be that many people that have sufficient permissions to do such a thing. Find out who can do it and ask. Better than any technology you can put in place.
没有那么多人有足够的权限去做这样的事情。找出谁可以做到并问。比任何技术都要好。
Hacker? It should be somebody on the inside. If someone outside your firewall has access to that database you need to talk to your network people.
黑客?它应该是内部的人。如果防火墙外的人可以访问该数据库,则需要与网络人员交谈。
Try adding a monitor to that URL and port to see what requests come through.
尝试将监视器添加到该URL和端口以查看请求的来源。
#4
The thing you are going to have to watch out for is that if someone is maliciously altering the database, and they have admin access, you have to assume they are smart enough to cover their tracks. At this point, you can stop further damage, but if the attacker is any good at all, you'll either blame the wrong person as the log files will be altered, or all the evidence point to the right person will be gone.
你需要注意的是,如果有人恶意改变数据库,并且他们拥有管理员访问权限,你必须假设他们足够智能以覆盖他们的轨道。在这一点上,你可以阻止进一步的伤害,但如果攻击者有任何好处,你要么责怪错误的人,因为日志文件将被改变,或者所有证据指向合适的人都将消失。
The best way to do is it to have it so that no one has direct admin access to the production database. We have a system set up so that no account has administrative access by default, and everyone has their own accounts. No one gets to use the SA account.
最好的办法是拥有它,以便没有人可以直接管理员访问生产数据库。我们设置了一个系统,以便默认情况下没有帐户具有管理访问权限,并且每个人都有自己的帐户。没有人可以使用SA帐户。
Someone has to grant the account access and it is automatically removed 24 hours after being granted. Ideally, the same person to grant access shouldn't be the one that gets administrative access to the database. That way two people always have to be involved to make changes to the system.
有人必须授予帐户访问权限,并在授予后24小时自动删除。理想情况下,授予访问权限的同一人不应该是获得对数据库的管理访问权限的人。这样,两个人总是需要参与对系统进行更改。
Ideally, two people should always be involved in making changes. That way the second person can verify what the first does. (It's easy to make mistakes at 10 at night after working several hours).
理想情况下,两个人应该总是参与进行更改。这样,第二个人可以验证第一个人做了什么。 (工作几个小时后,晚上10点很容易出错)。
People will counter this by saying that sometimes they "need" to be able to make quick changes. In most places this is not the case. It may take an extra 10 minutes to get a second person involved, and explain the situation. It will take years to clean up a reputation about someone stealing/altering corporate data.
人们会反驳说,有时他们“需要”能够快速做出改变。在大多数地方,情况并非如此。让第二个人参与可能需要额外的10分钟,并解释情况。清理有关窃取/更改公司数据的人的声誉需要数年时间。
#5
By adding user-level security like you should have.
通过添加您应具备的用户级安全性。
#6
Can you cross-reference roll-back times with the whereabouts of people on the team?
您能否与团队中人员的下落交叉参考回滚时间?
Or alternatively - just ask everyone?
或者 - 只是问每个人?
#7
SQL Server 2005 added DDL and DML triggers so you can track who's modifying data as well as the data structure.
SQL Server 2005添加了DDL和DML触发器,因此您可以跟踪谁在修改数据以及数据结构。
#8
If you're fixing it -- and by "fixing it" I mean locking down the production database and following some of the other practices mentioned here -- then don't worry about finding the culprit. It was probably accidental anyway and when you lock it down someone will start wondering why something doesn't work.
如果你正在修理它 - 并且通过“修复它”我的意思是锁定生产数据库并遵循这里提到的一些其他做法 - 那么不要担心找到罪魁祸首。无论如何它可能是偶然的,当你把它锁定时,有人会开始想知道为什么有些东西不起作用。
Tracking down the user who did won't solve anything. If it was malicious, they'll lie and say it was accidental.
追踪那些做过的用户将无法解决任何问题。如果它是恶意的,他们会撒谎并说这是偶然的。
The root cause is the security on the database so the group at fault is the one that allowed the database to be so susceptible.
根本原因是数据库的安全性,因此故障组是允许数据库易受影响的组。
#9
Asking everyone isn't useful, people lie and/or don't know they are screwing this up. We assume it's malicious but hope it's not.
问每个人都没用,人们撒谎和/或不知道他们搞砸了。我们认为这是恶意的,但希望不是。
#10
Wow, you've got a real problem then. If you can't trust your own people...
哇,你有一个真正的问题。如果你不能相信自己的人......
Time to turn off all the IDs except one. Make sure that person knows what they're doing and doesn't lie.
是时候关掉除了一个以外的所有ID。确保那个人知道他们在做什么,不会说谎。
#11
In addition to what you've already received in responses, my vote is that it's nobody; you're simply mistaken about how you're using the system.
除了你在回复中已经收到的内容之外,我的投票是没有人;你只是错误地认为你是如何使用这个系统的。
Now, don't get me wrong, I'm not talking about incompetence here. What I do mean, though, is that there may well be scripts that are running periodically, and someone rightly mentioned that sometimes auto-commit may be on versus off and someone's getting fooled.
现在,不要误会我的意思,我不是在谈论无能。但我的意思是,可能会有定期运行的脚本,并且有人正确地提到,有时自动提交可能会开启而非关闭,而有人会被愚弄。
I also believe you are asking for trouble by mixing ANY development work in the production environment. Disk space is CHEAP - a terabyte is less than $300 these days! You don't need whiz-bang performance for development work in most circumstances...
我也相信你在生产环境中混合任何开发工作都会遇到麻烦。磁盘空间很便宜 - 这几天TB的价格不到300美元!在大多数情况下,您不需要开发工作的高性能......
#1
Stay away from production databases. Create your scripts and email them to the DBA in charge (if you don't have one, get one). Developers with access to production database is a recipe for disaster - I don't have it and don't want to have it.
远离生产数据库。创建您的脚本并通过电子邮件发送给负责的DBA(如果您没有,请获取一个)。能够访问生产数据库的开发人员是灾难的一种方法 - 我没有它,也不想拥有它。
#2
Tracking down your problem is obviously a symptom and not the cause: since it's a SQL Server 2005 database, there should be a 'Default' trace that runs out of the box. It's very lightweight, but does include some object creation and deletion. You can view it from the sys.traces view using the following query:
追踪您的问题显然是一个症状,而不是原因:因为它是一个SQL Server 2005数据库,应该有一个开箱即用的“默认”跟踪。它非常轻量级,但确实包含一些对象创建和删除。您可以使用以下查询从sys.traces视图中查看它:
SELECT *
FROM sys.traces
WHERE id = 1
It rolls over after only a few MB so it's usefulness will depend on how much activity there is on the server.
它仅在几MB之后翻转,因此它的有用性将取决于服务器上的活动量。
Presumably, the real cause is not having your changes scripted and in version control.
据推测,真正的原因是没有脚本化和版本控制。
Agree with the other posters who mentioned that all changes to a Production Database should be done only by an Admin, and not individual developers.
同意其他提及生产数据库的所有更改只能由管理员而非个别开发人员完成的海报。
#3
I'll assume that you have a audit log with change data capture-esque features. This will be keeping track of the who, what, and when of each change.
我假设你有一个带有变更数据捕获特征的审计日志。这将跟踪每次更改的人员,内容和时间。
Are the rollbacks intermittent or consistent? Any chance you have auto commit turned off and forget to commit your changes?
回滚是间歇性的还是一致的?您是否有机会关闭自动提交并忘记提交更改?
There can't be that many people that have sufficient permissions to do such a thing. Find out who can do it and ask. Better than any technology you can put in place.
没有那么多人有足够的权限去做这样的事情。找出谁可以做到并问。比任何技术都要好。
Hacker? It should be somebody on the inside. If someone outside your firewall has access to that database you need to talk to your network people.
黑客?它应该是内部的人。如果防火墙外的人可以访问该数据库,则需要与网络人员交谈。
Try adding a monitor to that URL and port to see what requests come through.
尝试将监视器添加到该URL和端口以查看请求的来源。
#4
The thing you are going to have to watch out for is that if someone is maliciously altering the database, and they have admin access, you have to assume they are smart enough to cover their tracks. At this point, you can stop further damage, but if the attacker is any good at all, you'll either blame the wrong person as the log files will be altered, or all the evidence point to the right person will be gone.
你需要注意的是,如果有人恶意改变数据库,并且他们拥有管理员访问权限,你必须假设他们足够智能以覆盖他们的轨道。在这一点上,你可以阻止进一步的伤害,但如果攻击者有任何好处,你要么责怪错误的人,因为日志文件将被改变,或者所有证据指向合适的人都将消失。
The best way to do is it to have it so that no one has direct admin access to the production database. We have a system set up so that no account has administrative access by default, and everyone has their own accounts. No one gets to use the SA account.
最好的办法是拥有它,以便没有人可以直接管理员访问生产数据库。我们设置了一个系统,以便默认情况下没有帐户具有管理访问权限,并且每个人都有自己的帐户。没有人可以使用SA帐户。
Someone has to grant the account access and it is automatically removed 24 hours after being granted. Ideally, the same person to grant access shouldn't be the one that gets administrative access to the database. That way two people always have to be involved to make changes to the system.
有人必须授予帐户访问权限,并在授予后24小时自动删除。理想情况下,授予访问权限的同一人不应该是获得对数据库的管理访问权限的人。这样,两个人总是需要参与对系统进行更改。
Ideally, two people should always be involved in making changes. That way the second person can verify what the first does. (It's easy to make mistakes at 10 at night after working several hours).
理想情况下,两个人应该总是参与进行更改。这样,第二个人可以验证第一个人做了什么。 (工作几个小时后,晚上10点很容易出错)。
People will counter this by saying that sometimes they "need" to be able to make quick changes. In most places this is not the case. It may take an extra 10 minutes to get a second person involved, and explain the situation. It will take years to clean up a reputation about someone stealing/altering corporate data.
人们会反驳说,有时他们“需要”能够快速做出改变。在大多数地方,情况并非如此。让第二个人参与可能需要额外的10分钟,并解释情况。清理有关窃取/更改公司数据的人的声誉需要数年时间。
#5
By adding user-level security like you should have.
通过添加您应具备的用户级安全性。
#6
Can you cross-reference roll-back times with the whereabouts of people on the team?
您能否与团队中人员的下落交叉参考回滚时间?
Or alternatively - just ask everyone?
或者 - 只是问每个人?
#7
SQL Server 2005 added DDL and DML triggers so you can track who's modifying data as well as the data structure.
SQL Server 2005添加了DDL和DML触发器,因此您可以跟踪谁在修改数据以及数据结构。
#8
If you're fixing it -- and by "fixing it" I mean locking down the production database and following some of the other practices mentioned here -- then don't worry about finding the culprit. It was probably accidental anyway and when you lock it down someone will start wondering why something doesn't work.
如果你正在修理它 - 并且通过“修复它”我的意思是锁定生产数据库并遵循这里提到的一些其他做法 - 那么不要担心找到罪魁祸首。无论如何它可能是偶然的,当你把它锁定时,有人会开始想知道为什么有些东西不起作用。
Tracking down the user who did won't solve anything. If it was malicious, they'll lie and say it was accidental.
追踪那些做过的用户将无法解决任何问题。如果它是恶意的,他们会撒谎并说这是偶然的。
The root cause is the security on the database so the group at fault is the one that allowed the database to be so susceptible.
根本原因是数据库的安全性,因此故障组是允许数据库易受影响的组。
#9
Asking everyone isn't useful, people lie and/or don't know they are screwing this up. We assume it's malicious but hope it's not.
问每个人都没用,人们撒谎和/或不知道他们搞砸了。我们认为这是恶意的,但希望不是。
#10
Wow, you've got a real problem then. If you can't trust your own people...
哇,你有一个真正的问题。如果你不能相信自己的人......
Time to turn off all the IDs except one. Make sure that person knows what they're doing and doesn't lie.
是时候关掉除了一个以外的所有ID。确保那个人知道他们在做什么,不会说谎。
#11
In addition to what you've already received in responses, my vote is that it's nobody; you're simply mistaken about how you're using the system.
除了你在回复中已经收到的内容之外,我的投票是没有人;你只是错误地认为你是如何使用这个系统的。
Now, don't get me wrong, I'm not talking about incompetence here. What I do mean, though, is that there may well be scripts that are running periodically, and someone rightly mentioned that sometimes auto-commit may be on versus off and someone's getting fooled.
现在,不要误会我的意思,我不是在谈论无能。但我的意思是,可能会有定期运行的脚本,并且有人正确地提到,有时自动提交可能会开启而非关闭,而有人会被愚弄。
I also believe you are asking for trouble by mixing ANY development work in the production environment. Disk space is CHEAP - a terabyte is less than $300 these days! You don't need whiz-bang performance for development work in most circumstances...
我也相信你在生产环境中混合任何开发工作都会遇到麻烦。磁盘空间很便宜 - 这几天TB的价格不到300美元!在大多数情况下,您不需要开发工作的高性能......