![database workbench show output database workbench show output](https://cdn-images-1.medium.com/max/2400/1*H1tGbkyp7KjBZnHW1v8y-w.png)
If you want to see the list of all the users that are currently logged into the database, you can query the processlist table of the information_schema database. User() function retrieves the database address along with the host and user name. To retrieve the current user, there are two functions provided by MySQL namely user() and current_user() that nearly function in the same way retrieving the user name and the host. We can retrieve the user information by querying it. The information related to the user such as password-related fields, hosts, and other privileges assigned to the user is all stored in the table named user located in MySQL database. Step 2: Now, If you want to retrieve the list of the users that are currently logged in to the system, we can write the query using the DISTINCT keyword in the following manner:Įxplanation: The above output shows that there is only one user that is logged into my system that is an AcmeInfi user. Step 1: We will first retrieve the whole process list that is currently being executed by MySQL.This can be done in the following manner:Įxplanation: This table contains process-related information such as process id, user, host, DB, Command, Time, State, and INFO. The processlist schema stores the list of all the users that are logged in and data related to each of the operations being performed by users on that database at that particular instant when the query was fired. In that case, if we want to fetch the list of the users that are currently logged in to your MySQL server can be done by querying on the processlist table of information_schema. There might be the case that there are multiple users of MySQL database server and many users can access the system at the same time. Checking all the currently logged in users Both the functions USER() and CURRENT_USER() can be used to retrieve and fetch the current user of the database. This can be used in the following manner:Įxplanation: The above-returned result of the CURRENT_USER() function is in the format of the name of the user and then symbol that is further followed by the host in our case it is (%). Step 2: There is also one more method available to retrieve the current user that is current_user() function. Step 1: The following query can be used for that purposeĮxplanation: The above function returns the name of the current user then the host that in our case is % and then the database server’s address. MySQL provides us with the function user() that can be used to get the current user of the database. Most of the columns of the user table that store the values like boolean i.e either true or false have the datatype of an enum having values ‘Y’ that stands for true and ‘N’ that specifies false i.e enum(‘N’,’Y’) datatype that informs whether the certain privilege is assigned to the user or not. Then, in that case, you can choose privilege related columns as done in the following SELECT query:Įxplanation: Similarly, other columns can also be retrieved whichever you want using the select query. Step 4: Suppose, you want to retrieve assigned privilege-related information of the users.
![database workbench show output database workbench show output](https://i.stack.imgur.com/fquZN.jpg)
To retrieve more information about users, we can add the columns we want to retrieve in the select query as follows: Step 3: To retrieve the data from the user table of the MySQL database, you first need to login to MySQL using MySQL -u root pg command if you are using MySQL from command-line through terminal or by entering the credentials in case if you are using any IDE or editor such as sqlyog of workbench.Īn alternative to using the above select query, you can firstly fire the command to select the MySQL database and then simply fire the following select query that will produce the same output as above: Step 2: We will first retrieve the names of all the users from the user table using the following SELECT query:Įxplanation: We can see that four users are present for my database server. If your current chosen database being used is MySQL or you can use: Step 1: To view the complete list of the columns of the user table you can describe the user table using the following query: Some of them are user, host, ssl_type, ssl_cipher, max_questions, max_connections, authentication_string, password_expired, account_locked, password_lifetime, password_last_changed, and other columns related to privileges assigned to the user for different database operations.
![database workbench show output database workbench show output](https://docs.oracle.com/cd/E19078-01/mysql/mysql-workbench/images/wb-reverse-engineer-database-wizard-show-filter.png)
This table contains a total of 45 columns to store user information. User-related information in the user tableĪs discussed previously, there is a database named MySQL that stores the user-related information in the table named user. Hadoop, Data Science, Statistics & others 1.