Menu
I have a MySQL database accessed by a group of my teammates. Is there any command to get the log information of the users who are currently accessing or who have already accessed and logged out?
708
We can use the following query to see the list of all user in the database server: mysql> Select user from mysql.
Answer: In SQL Server, there is a catalog view (ie: system view) called sys. sql_logins. You can run a query against this system view that returns all of the Logins that have been created in SQL Server as well as information about these Logins.
HAS_DBACCESS returns 1 if the user has access to the database, 0 if the user has no access to the database, and NULL if the database name is not valid. HAS_DBACCESS returns 0 if the database is offline or suspect. HAS_DBACCESS returns 0 if the database is in single-user mode and the database is in use by another user.
To determine which users have direct grant access to a table we'll use the DBA_TAB_PRIVS view: SELECT * FROM DBA_TAB_PRIVS; You can check the official documentation for more information about the columns returned from this query, but the critical columns are: GRANTEE is the name of the user with granted access.
Run the following from a mysql tool to view all currently running processes (including sleeping connections):
SHOW PROCESSLIST
Or, you can query the information_schema table to get the same:
select * from information_schema.processlist
To see a history of whom all has logged in, you could configure the general query log to go to a table, by adding the following startup parameter to your mysqld startup "--log-output=TABLE --general-log", then you can query this information out of the general_log table in the mysql schema. Following is the query you could use:
select * from mysql.general_log where command_type = 'Connect';
A word of warning though, this table could get huge. You'll want to periodically clean it out.
148
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With