Here is my requirement, though I'm aware what could possibly be done to fulfill it but still looking out for the best solution to work out with.
Few days ago someone(some hacker or some previous developer) had truncated my important table causing inconvenience to my customers. I need to change my database password, server's password and other application's password in order to avoid such obnoxious actions and prevent my application.
Additionally first I need to know if this kind of activity is being performed with my data before my customers so that I can find out any work around for them. This is what I thought till now-
- I should create a window service which will run in every 1 hour and check in all tables or most importantly in main tables that # of records are as expected or not. If not then an alert will be sent to me, so that I can quickly fix that.
- I should create an SQL job which will perform same steps that I mentioned in first point.
These workarounds seems fine and workable. but I'm really curious to know what else can I do to prevent my data or alert me in case of data lost. I'm sure there are other efficient and more feasible way to achieve what I want. It would be great if anyone can help me around.
This is a web application and only authenticated users can login. My SQL Server edition is Enterprise.
Best Answer
First and foremost this is an issue of access control. Eliminate all redundant logins, and reduce permissions on the required logins to the strictly necessary minimum. Do not use 'well known' logins shared by every person with DB access, make sure every user uses its own login (eg. use Integrated Auth).
It is not clear from your post whether this server is accessed directly from the internet or not. Ideally you should set up a VPN between your app(s) and the server and avoid exposing the server to the internet. If not possible, there are some defenses you need to set up. The default SQL Server ports are subject to constant scans and brute force attacks. You should use a non-default port to, at least, avoid the overhead of rejecting these constant brute force scan attacks. Next, you should restrict the internet client addresses that can connect. This can be done, ideally, at IP level using Firewall rules. SQL Server also can enforce this, using logon triggers, as Alex suggested. If possible, deploy both firewall rules and a logon trigger.
Enable SQL Server logon audit. Advertise it that is enabled, so that employees are deterred from trying to hack into the system.
Establish backup policies, with frequent log backups, and keep backups off-site. Have restore plans, and do restore drills to validate the backups and the restore procedures. If someone penetrates your defenses and manages to erase data, you should be able to quickly restore it.
And lets not forget the app itself. You may well be barking up the wrong tree and the erase may had been done via the app itself, via the usual SQL injection exploits. You must audit the app and ensure this is not the case, fix any issue you find.
Right now, you are in an impossible situation. You have lost data, and don't even know how. Contact professional help to help you find how the data was erased. If you don't know where the ship is leaking, is very hard to patch the hole...
My 2c on the idea of 'monitor the database see when someone deleted my data' is ... a waste of time right now. W/o understanding how the data was erased, it will just turn into an amusing game for somebody that will periodically erase your data, just to see how fast you are reacting today... Really, focus on mitigation and deterrence. Detection is nice to have, but it cannot be your first line of defense.
For structural database changes (eg. modify a table, alter a procedure etc) my company DBHistory.com offers a service that audits all server changes and keeps an off-site record. Unfortunately, at this moment I cannot offer data changes audit.