There are many password breaches happening now a days. How can the system administrator detect if the password database on the server is hacked? Obviously, the admin can know once the password database is announced over the internet. But is there a way to know about the incident before the entire world knows about it? Is it possible to know about the hack at about the same moment when the hack is happening? What kind of traces one should look for detecting the possibility of different attacks?
Start collecting data
There is no "was_compromised" database setting that gets changed when a breach happens. You'll have to monitor your evidence now, and look for suspicious activity. You should look into the concepts around Intrusion Detection more broadly, but I'll give some database specific ideas to get you started. Essentially, start logging information to an central system as soon as possible.
You don't just need the data to determine what happened should a breach occur, but you also need the data to establish what is normal so you can detect what is abnormal.
Enable Database Query Logging
In MySQL, enable the General Query log. If your server is breached, you will often see a large increase in query activity.
Intrusion Detection systems can be to watch for canary values.
One technique that has a very small false-negative rate is to insert fake data into the database, and configure your Snort to detect if that data exits the network. This technique can get tricky, because data can be encoded or encrypted, but setting up multiple tap points will help. You would ideally listen to traffic between the webserver and the internet, as well as between the database and the webserver.
Monitor your web logs
If SQL Injection attacks are being used to grab data, the attacks will show up all over your weblogs. You will often see huge spikes in traffic for a small group of IPs. Knowing how much data your website normally transfers a day is very helpful here.
Start full packet captures
This is the final word. If you can record every packet ever sent to and from the database server you will be able to answer the "Did we get breached" question much more effectively. You'll ideally want to capture between all the your servers.