5

During in a audit task, I am going to delete few mysql users. Before delete I want to check, when the last time these user access the database server. I want to know if we have this information stored in information_schema or mysql databases. If not how can we achieve this?

Faizan Younus
  • 793
  • 1
  • 8
  • 13
  • Questions asking us to recommend or find a book, tool, software library, tutorial or other off-site resource are off-topic for Stack Overflow as they tend to attract opinionated answers and spam. Instead, describe the problem and what has been done so far to solve it. – Marco Salerno Oct 31 '17 at 11:51
  • 1
    @MarcoSalerno: How does that policy apply here? – David Oct 31 '17 at 11:52
  • He is just asking for a solution, plus he didn't provide his tries, plus the question is unclear and broad – Marco Salerno Oct 31 '17 at 11:53
  • @MarcoSalerno: There are vote-to-close options for "Too Broad" or for questions seeking debugging help without providing code. But those aren't the ones you quoted. How is this question asking for recommendations for off-site resources, as you indicated? The OP is asking for information, but is not asking for recommendations for any particular tool. – David Oct 31 '17 at 11:54
  • He is asking for a solution without providing code as i said, we aren't a tutorial website ^^ – Marco Salerno Oct 31 '17 at 11:55
  • The debugging flag is for other circumstances – Marco Salerno Oct 31 '17 at 11:56
  • @MarcoSalerno I can't provide my tries and code in this case. I tried and search solution for this but when I can't find a solution, I posted a question here.I agree it is not a tutorial website. – Faizan Younus Oct 31 '17 at 12:03
  • 2
    @FaizanYounus: Unless there are records of login events in an audit log somewhere, I'm not seeing any tables which directly store the last login for a database user. – David Oct 31 '17 at 12:07
  • @MarcoSalerno Thank you. – Faizan Younus Oct 31 '17 at 12:13

1 Answers1

5

You can look in the general log for any unsuccessful connection attempts to MySQL however out-of-the-box, MySQL typically does not log successful DB connections. This could become a very, very large log and that activity may quickly overwhelm the I/O subsystem and cause problems for the DB.

You can setup this sort of logging however it will result in quite a LOT of data! have a look at this post over in the DBA Exchange: https://dba.stackexchange.com/questions/668/audit-logins-on-mysql-database

If you are using a DB server that is yours and yours alone, you should be able to do this. If you have a DBaaS type thing, multi-tenant DB systems would die logging each connect. This may not be possible?

You may also find some steps on enabling usertracking in MySQL from the Percona blog: https://www.percona.com/blog/2012/12/28/auditing-login-attempts-in-mysql/

t3ln3t
  • 230
  • 2
  • 6
  • One does not need to log each connection, just the timestamp of the most recent connection. – ColinM Feb 16 '23 at 15:28