Introduction Show
The primary function of a database is to store information in a structured way to allow easy access to that information. Having many users with permission to access and work on your MySQL server can become a security concern. A short set of queries in the MySQL client can provide you with valuable information to help prevent potential issues. Use the commands outlined in this tutorial to learn how to list all user accounts on a MySQL server. Prerequisites
How to Access MySQL as RootAccess the MySQL server as root user by entering the following command in your terminal:
or:
The The output above shows that we gained access to the MySQL client as the root user. Now, you can run a MySQL query and retrieve information from the mysql.user database. The following command lists usernames that have access to the server:
This command instructs MySQL to create a table. The system retrieves the information from the Usercolumn in the mysql.user database. The output in this example shows a table with four rows: The list we received has limited value. To retrieve a more detailed overview of the MySQL users and their permissions, we can expand the search parameters and add additional columns. How to List mysql.user Database FieldsBefore expanding the query, let’s list the fields available in the mysql.user database. The following command lists all the available columns:
The options in the Field column represent the information we can request from the mysql.user database. It is possible to combine several options in a single command to get detailed user information. How to Show MySQL User InformationThe following query provides a table with the User, Host, and authentication_string columns:
The query adds two more columns to the User column and provides valuable information such as the user’s password and hostname. Note: The Password field in the mysql.user table has been renamed in MySQL 5.7. The field that lists user passwords is now named authentication_string. How to List Only Unique MySQL UsersAnother useful option is to remove MySQL usernames that appear in multiple rows. To display only unique MySQL usernames, enter the following command:
The output removes duplicate MySQL rows and shows specific usernames only once. Show Current MySQL UserUse the
Or:
In both cases, the output shows that the current MySQL user is [email protected]. Show Logged In MySQL UsersAt any moment, you can check the users who are currently logged in to the MySQL database server. This function provides an invaluable insight when monitoring your MySQL server for unauthorized usage. Enter this query to show the list of currently logged in MySQL users:
The output lists the users who are logged in, the database, and the command being run. Conclusion By combining the commands presented in this article, you are now able to structure user information from the mysql.user database with ease. Instead of just listing users, you can modify search results and find user-specific information. A detailed and customizable overview enables you to make an informed decision regarding the security of your MySQL server. Bagaimana cara membuat hak akses user di MySQL?Untuk memberikan hak akses kepada sebuah user, MySQL menyediakan query GRANT. Berikut format dasar query GRANT: GRANT hak_akses ON nama_database. nama_tabel TO 'nama_user' @ 'lokasi_user' ; hak_akses adalah privileges yang akan berikan kepada user tersebut.
Langkah langkah membuat database MySQL yang benar?MySQL Membuat Database dan Table. Buka command prompt dengan cara tekan ctrl + R keudian ketik cmd lalu enter.. Buka MySQL dengan cara mengetikan cd AppServ\MySQL\bin\MySQL.. Bila meminta password, masukkan password yang kalian buat (tapi biasanya password defaultnya “root”). Bagaimana perintah SQL untuk membuat user baru?Untuk membuat user baru, MySQL menyediakan query CREATE USER, berikut format dasar perintah: CREATE USER 'nama_user'; nama_user adalah nama dari user yang akan dibuat, maksimal 16 karakter.
Apa itu flush privileges MySQL?Flush Privileges adalah sinonim untuk reload. Perintah reload menyuruh server untuk membaca ulang tabel hak akses.
|