Re: user last activity and log in
Mellow greetings, Enhance your calm. Lets get our facts straight and not go off our rockers. MySQL 5.6 Enterprise edition will be able to do this natively ( https://blogs.oracle.com/MySQL/entry/new_in_mysql_enterprise_edition), but otherwise you cannot do it natively. This does not mean its impossible, though as there's a few easy work arounds. 1) Force all logins to use the PAM or AD authentication plugin -- if the authentication is success then log it in AD or PAM 2) use a init-connect to log logins but that doesn't work for users with super privileges as Keith mentioned below (thanks Keith for actually trying to help!) 3) Write your own plugin using the MySQL Plugin APIs 4) use the McAfee Audit Plugin for MySQL (Free: http://www.devshed.com/c/a/MySQL/McAfee-Releases-Audit-Plugin-for-MySQL-Users-86548/ ) Singer Wang (yes, I just watched Demolition Man) On Thu, Oct 4, 2012 at 11:29 PM, Keith Murphy bmur...@paragon-cs.comwrote: My friend Dave Holoboff wrote this up some time ago: http://mysqlhints.blogspot.com/2011/01/how-to-log-user-connections-in-mysql.html You know you people sound like children. Really unprofessional. Go ahead --- call me names. i left middle school almost 30 years ago. It won't bother me. Can we knock off the name calling and actually offer advice and possible solutions? I thought that was what this list was for. For those of us out in the field doing things ... This might be your ticket. It requires a restart of MySQL (which may or may not be acceptable) bit it's a fairly clean solution. Minimal load, easy to query for your last connection time and how often connections are made by a user. Again, requires a restart to enable (and disable) . Oh, and users with super privileges won't be logged. Thanks, Keith -- Keith Murphy Senior MySQL DBA Principal Trainer Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- * * (c) 850-637-3877
Re: user last activity and log in
My bad actually, the MySQL Enterprise Audit is available in MySQL 5.5 S On Fri, Oct 5, 2012 at 2:14 AM, Singer Wang w...@singerwang.com wrote: Mellow greetings, Enhance your calm. Lets get our facts straight and not go off our rockers. MySQL 5.6 Enterprise edition will be able to do this natively ( https://blogs.oracle.com/MySQL/entry/new_in_mysql_enterprise_edition), but otherwise you cannot do it natively. This does not mean its impossible, though as there's a few easy work arounds. 1) Force all logins to use the PAM or AD authentication plugin -- if the authentication is success then log it in AD or PAM 2) use a init-connect to log logins but that doesn't work for users with super privileges as Keith mentioned below (thanks Keith for actually trying to help!) 3) Write your own plugin using the MySQL Plugin APIs 4) use the McAfee Audit Plugin for MySQL (Free: http://www.devshed.com/c/a/MySQL/McAfee-Releases-Audit-Plugin-for-MySQL-Users-86548/ ) Singer Wang (yes, I just watched Demolition Man) On Thu, Oct 4, 2012 at 11:29 PM, Keith Murphy bmur...@paragon-cs.comwrote: My friend Dave Holoboff wrote this up some time ago: http://mysqlhints.blogspot.com/2011/01/how-to-log-user-connections-in-mysql.html You know you people sound like children. Really unprofessional. Go ahead --- call me names. i left middle school almost 30 years ago. It won't bother me. Can we knock off the name calling and actually offer advice and possible solutions? I thought that was what this list was for. For those of us out in the field doing things ... This might be your ticket. It requires a restart of MySQL (which may or may not be acceptable) bit it's a fairly clean solution. Minimal load, easy to query for your last connection time and how often connections are made by a user. Again, requires a restart to enable (and disable) . Oh, and users with super privileges won't be logged. Thanks, Keith -- Keith Murphy Senior MySQL DBA Principal Trainer Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- * * (c) 850-637-3877
Re: user last activity and log in
- Original Message - From: Singer Wang w...@singerwang.com 2) use a init-connect to log logins but that doesn't work for users with super privileges as Keith mentioned below (thanks Keith for actually trying to help!) That is indeed quite the nifty trick. Thanks, Keith :-) 3) Write your own plugin using the MySQL Plugin APIs 4) use the McAfee Audit Plugin for MySQL (Free: Hmm. Smells of McAfee, but looks interesting. Not sure what to think :-) -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user last activity and log in
There is no such thing. Your application has to deal with such info. LS On Oct 4, 2012, at 11:28 AM, Aastha wrote: Hello, I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data or anyt specific sql Aastha Gupta -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user last activity and log in
Am 04.10.2012 17:28, schrieb Aastha: I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data or any specific sql no - because this would mean a WRITE QUERY in the mysql-database for every connection - having a web-application with hundrets of calls per second would kill the performance this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer signature.asc Description: OpenPGP digital signature
Re: user last activity and log in
It is possible in MySQL 5.6 S On Thu, Oct 4, 2012 at 11:30 AM, List Man list@bluejeantime.com wrote: There is no such thing. Your application has to deal with such info. LS On Oct 4, 2012, at 11:28 AM, Aastha wrote: Hello, I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data or anyt specific sql Aastha Gupta -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user last activity and log in
- Original Message - From: Reindl Harald h.rei...@thelounge.net this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer I notice no specification of what kind of users, so I'm assuming DB users. There *is* such a thing: you can find it in the general query log. Turning that on is a considerable performance overhead, though, and so is firmly discouraged on production systems. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user last activity and log in
Yes, i meant DB users. On Thu, Oct 4, 2012 at 10:57 AM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Reindl Harald h.rei...@thelounge.net this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer I notice no specification of what kind of users, so I'm assuming DB users. There *is* such a thing: you can find it in the general query log. Turning that on is a considerable performance overhead, though, and so is firmly discouraged on production systems. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user last activity and log in
it does not matter what kind of users usually each application has it's own datanase and it's own user, the application makes the connection and can at this point log whatever you want using the general query log can only be a bad joke you will log EVERY query and not only logins again: it is not the job of a RDBMS to waste I/O and performance with such things - the application as example could refresh it only once per user-session the RDBMS would write blindly for each connection Am 04.10.2012 18:18, schrieb Aastha: Yes, i meant DB users. On Thu, Oct 4, 2012 at 10:57 AM, Johan De Meersman vegiv...@tuxera.be mailto:vegiv...@tuxera.be wrote: - Original Message - From: Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer I notice no specification of what kind of users, so I'm assuming DB users. There *is* such a thing: you can find it in the general query log. Turning that on is a considerable performance overhead, though, and so is firmly discouraged on production systems. signature.asc Description: OpenPGP digital signature
Re: user last activity and log in
- Original Message - From: Reindl Harald rei...@thelounge.net it does not matter what kind of users I'm happy for you that you still have all the answers anyone could ever want, Harald. Regardless of having any background knowledge on the circumstance of the question, even. You truly are a gifted individual. using the general query log can only be a bad joke you will log EVERY query and not only log-ins Yes, which is why I specified explicitly that it is very much discouraged for production use. However, it can be useful at times. I recently turned it on to investigate sudden, unpredictable and above all annoyingly brief peaks in the number of connections, and I needed to know what APPLICATION INSTANCE was responsible, not which particular user - as well as have a good view of what the offending sessions did. A tcpdump would have been an option, but given that wireshark still isn't too good at decoding MySQL traffic I still opted for the full query log. There was some more tomfoolery involved, but after almost a week of logging we successfully identified the culprit. Now you may do things differently, and you may also reach a satisfactory solution; but I am absolutely sick and tired of hearing how your way is the only valid way, casually implying that the rest of the world are all bloody idiots that should just shut up and listen while you tell them every ridiculous way in which they are wrong and inferior. PLEASE, for your own sake - not to mention the nerves of the people around you - learn to accept that there are a lot of different ways to do things, and that sometimes people pick their optimal solution on quite different criteria than the ones you use. That does not necessarily make them wrong, merely different from you. As the Perl mantra goes, There's More Than One Way To Do It. /Johan -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user last activity and log in
Am 04.10.2012 23:12, schrieb Johan De Meersman: - Original Message - From: Reindl Harald rei...@thelounge.net it does not matter what kind of users I'm happy for you that you still have all the answers anyone could ever want, Harald. not all but the one to the topic IT IS IMPOSSIBLE MYSQL CAN NOT DO WHAT THE OP WANT Regardless of having any background knowledge on the circumstance of the question, even. mysql can not an dwill not log user-logins You truly are a gifted individual. your opinion, but the answer to the question of the OP is simply NO you can't using the general query log can only be a bad joke you will log EVERY query and not only log-ins Yes, which is why I specified explicitly that it is very much discouraged for production use. it is NOT the answer to the question damned it doe NOT log the last login of a mysql user in a USEABLE form Now you may do things differently, and you may also reach a satisfactory solution; but I am absolutely sick and tired of hearing how your way is the only valid way i don't give a damn about what you are tired of the answer to I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data is SIMPLY NO and not a useless full query log casually implying that the rest of the world are all bloody idiots maybe in this case your conclusion i liked to call you a bloody idiot for bringing full query log as answer comes because you realized how useless the idea is that should just shut up and listen while you tell them every ridiculous way in which they are wrong and inferior maybe you should shut up yourself as long you are hypersensible learn to accept that there are a lot of different ways to do things again: your solution full query log is not one if you can't face the truth this is your problem and that sometimes people pick their optimal solution on quite different criteria than the ones you use. if someone does not like answers he should not ask questions There's More Than One Way To Do It. full query og is none of them if it takes SIX hours for your reply in the way you did here my conclusion is that you recently came home drunken and should go to bed signature.asc Description: OpenPGP digital signature
Re: user last activity and log in
Hi, 2012/10/4 Reindl Harald h.rei...@thelounge.net Am 04.10.2012 17:28, schrieb Aastha: I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data or any specific sql no - because this would mean a WRITE QUERY in the mysql-database for every connection - having a web-application with hundrets of calls per second would kill the performance No because MySQL does not have this facility. (5.6) Saying that a feature is not present because the hypothetical implementation would impact performance doesn't make much sense in my opinion. this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer I can suggest a reading here: http://www.amazon.com/Implementing-Database-Security-Auditing-Examples/dp/183342 Regards -- Claudio
RE: user last activity and log in
In looking at a couple hundred machine, I see that Connections / Uptime has a median of about 0.5 (one connection every 2 seconds) and a max of about 140. 140 writes to some audit table _might_ have a small impact on the system. -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, October 04, 2012 3:51 PM To: Reindl Harald Cc: mysql@lists.mysql.com Subject: Re: user last activity and log in Hi, 2012/10/4 Reindl Harald h.rei...@thelounge.net Am 04.10.2012 17:28, schrieb Aastha: I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data or any specific sql no - because this would mean a WRITE QUERY in the mysql-database for every connection - having a web-application with hundrets of calls per second would kill the performance No because MySQL does not have this facility. (5.6) Saying that a feature is not present because the hypothetical implementation would impact performance doesn't make much sense in my opinion. this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer I can suggest a reading here: http://www.amazon.com/Implementing-Database-Security-Auditing- Examples/dp/183342 Regards -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user last activity and log in
beside the fact that msql CAN NOT do this at all the median is not really releavt in the median you see also night hours with zero load on a typical webserver with load you have much more * a cms system * many page requests per second * no you can not use persistent connections if you have let's say 100 databases and 100 domains with 500 prefork pcroesses because these would mean in the worst case 5 connections * enable query log on machines with some hundret queriers per second would be a self DOS and fill your disks Am 05.10.2012 01:26, schrieb Rick James: In looking at a couple hundred machine, I see that Connections / Uptime has a median of about 0.5 (one connection every 2 seconds) and a max of about 140. 140 writes to some audit table _might_ have a small impact on the system. -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, October 04, 2012 3:51 PM To: Reindl Harald Cc: mysql@lists.mysql.com Subject: Re: user last activity and log in Hi, 2012/10/4 Reindl Harald h.rei...@thelounge.net Am 04.10.2012 17:28, schrieb Aastha: I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data or any specific sql no - because this would mean a WRITE QUERY in the mysql-database for every connection - having a web-application with hundrets of calls per second would kill the performance No because MySQL does not have this facility. (5.6) Saying that a feature is not present because the hypothetical implementation would impact performance doesn't make much sense in my opinion. this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer I can suggest a reading here: http://www.amazon.com/Implementing-Database-Security-Auditing- Examples/dp/183342 signature.asc Description: OpenPGP digital signature
Re: user last activity and log in
My friend Dave Holoboff wrote this up some time ago: http://mysqlhints.blogspot.com/2011/01/how-to-log-user-connections-in-mysql.html You know you people sound like children. Really unprofessional. Go ahead --- call me names. i left middle school almost 30 years ago. It won't bother me. Can we knock off the name calling and actually offer advice and possible solutions? I thought that was what this list was for. For those of us out in the field doing things ... This might be your ticket. It requires a restart of MySQL (which may or may not be acceptable) bit it's a fairly clean solution. Minimal load, easy to query for your last connection time and how often connections are made by a user. Again, requires a restart to enable (and disable) . Oh, and users with super privileges won't be logged. Thanks, Keith -- Keith Murphy Senior MySQL DBA Principal Trainer Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- * * (c) 850-637-3877
Re: user last activity and log in
One small correction. Init-connect doesn't require a restart of MySQL. I was thinking of init-file. So that's even better. On Thursday, October 4, 2012, Keith Murphy wrote: My friend Dave Holoboff wrote this up some time ago: http://mysqlhints.blogspot.com/2011/01/how-to-log-user-connections-in-mysql.html You know you people sound like children. Really unprofessional. Go ahead --- call me names. i left middle school almost 30 years ago. It won't bother me. Can we knock off the name calling and actually offer advice and possible solutions? I thought that was what this list was for. For those of us out in the field doing things ... This might be your ticket. It requires a restart of MySQL (which may or may not be acceptable) bit it's a fairly clean solution. Minimal load, easy to query for your last connection time and how often connections are made by a user. Again, requires a restart to enable (and disable) . Oh, and users with super privileges won't be logged. Thanks, Keith -- Keith Murphy Senior MySQL DBA Principal Trainer Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- * * (c) 850-637-3877 -- Keith Murphy Senior MySQL DBA Principal Trainer Paragon Consulting Services http://www.paragon-cs.com 850-637-3877
