We help IT Professionals succeed at work.

Scripts / commands to detect failed DB login attempts and querying of unusual large amount of data

241 Views
Last Modified: 2018-10-01
Without DB Activity Monitoring in place, we would like daily job/script
(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
Comment
Watch Question

Author

Commented:
https://www.netwrix.com/how_to_track_down_failed_oracle_logon_attempts.html

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
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Senior Oracle DBA
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Thanks for the script Johnsone;  how do we cut off the data queried on daily basis?
Possible to add a criteria to query for last 24 hrs, thanks
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Thanks;  on sudden large amt of data being queried, how do we detect this?

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?
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
This condition will be very hard to detect: "unusual high amount of data being queried".  Yes, you can set up one or more custom resource profiles (your database will already have at least one default profile) and assign them to the various Oracle users in your database.  But, these do not track the amount of data being sent to a client.  These only track the number of blocks of data that a query reads in the database server, regardless of how many of those rows actually get returned to the client.

Author

Commented:
> set up a resource profile to stop people from running queries that do too many reads
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)?

Author

Commented:
If it doesn't block, perhaps an alert /detection that a query that returns more than 30MB
(or 300,000 rows) have been run (possibly within the last 1 hour as plan to detect this on
hourly basis)
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why 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.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
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