Scripts / commands to detect failed DB login attempts and querying of unusual large amount of data
(Windows Scheduled Task for MS SQL and Unix cron for Oracle) to
detect if the following took place :
a) failed login attempts to a DB (even a single failed attempt)
b) unusual high amount of data being queried (say 10 times more
than last 30 days' average; if this is not possible, then 15MB of
data)
Q1:
Can provide the specific commands (SQL or OS) for both DB types
above for Windows & UNIX (Solaris & RHEL)?
Q2:
For item a above, which file in MS SQL & Oracle DB contain that info?
As the scripts run once/day, Ideally the script covers the last 24hrs
events/logs so that in the next run
Q3:
Referring to above URL, what's the equivalent Oracle command to set
' audit_trail="true" ' and is there any way to do it without restarting the
Oracle instance & is it necessary to enable Audit Trail to get this info of
failed login attempts?
Q4:
Our DBA said turning on the Audit Trail can have major performance impact:
what can we do about this? How do we estimate the disk space the Audit
Trail will take?
Q5:
What's the command to roll over (ie once/day) a new Audit Trail file once the
sql query to get the failed login attempts is done
Possible to add a criteria to query for last 24 hrs, thanks
Ok, just as what our DBA replied:
"we will not only monitor 1017 error code. Better to capture all login error scenarios
as regulator wanted it.
Btw, turning on audit for table level will eat a lot of space and resource" <==to detect large amt of data queried?
Database Administrator
Can elaborate a example profile that stops queries of more than 30MB data if it's not
possible to restrict by the number of rows (which I was looking at 300,000 rows)?
(or 300,000 rows) have been run (possibly within the last 1 hour as plan to detect this on
hourly basis)
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions