My Frequent Command on MySQL 8.0 Administration

List of available users

SELECT User, Host FROM mysql.user;

Show privileges of an account

show grants for exampleuser@localhost;

Contoh:

mysql> show grants for epintaruser@localhost;

+------------------------------------------------------------------+

| Grants for epintaruser@localhost                                 |

+------------------------------------------------------------------+

| GRANT USAGE ON *.* TO `epintaruser`@`localhost`                  |

| GRANT ALL PRIVILEGES ON `epintar`.* TO `epintaruser`@`localhost` |

+------------------------------------------------------------------+

2 rows in set (0.00 sec)

Alter user password

ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';

Contoh:

mysql> alter user 'moodle_user'@'%' identified by 'p4sswordku';

Query OK, 0 rows affected (0.00 sec)



mysql>

Grant privileges of a database to a user

Syntax:

GRANT <privileges> ON <database>.<object> TO '<user>'@'<host>';

Sebelumnya harus sudah ada terlebih dahulu proses pembuatan user dengan host yang sama

Contoh:

mysql> GRANT ALL PRIVILEGES ON digitoskelasku.* TO 'moodle_user'@'%';
Query OK, 0 rows affected (0.00 sec)

Create alternate Super Admin Users

Using root account for daily activities is not recommended, so we need to have other user account that has superadmin privileges

mysql> create user admin@localhost identified by '@dminpassw0rd';
mysql> grant all privileges on *.*  to admin@localhost;