Teguh Triharto Learning Center: .::: Create user MariaDB MySQL admin, readonly, show priviledge database MySQL MariaDB & change password with all hostname & specific hostname :::.

1. Create user & database;

-- for access local
CREATE USER 'root'@'localhost' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- for access remote
CREATE USER 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

ALTER USER 'root'@'%' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;

-- for access local
CREATE USER 'teguh'@'%' IDENTIFIED BY 'teguhteguh';
GRANT ALL PRIVILEGES ON *.* TO 'teguh'@'%' WITH GRANT OPTION;

ALTER USER 'teguh'@'%' IDENTIFIED BY 'teguhteguh';
FLUSH PRIVILEGES;

-- for access remote
CREATE USER 'teguh'@'localhost' IDENTIFIED BY 'teguhteguh';
GRANT ALL PRIVILEGES ON *.* TO 'teguh'@'localhost' WITH GRANT OPTION;

ALTER USER 'teguh'@'localhost' IDENTIFIED BY 'teguhteguh';
FLUSH PRIVILEGES;

create database tgh;
create user 'teguh' identified by 'teguhteguh';
grant all privileges on *.* to 'teguh'@'%' identified by 'teguhteguh' with grant option;
FLUSH PRIVILEGES;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

ALTER USER 'root'@'%' IDENTIFIED BY 'root';

grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;

UPDATE mysql.user SET host='%' WHERE user='root';

FLUSH PRIVILEGES;

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
ALTER USER 'admin'@'localhost' IDENTIFIED BY 'admin';
FLUSH PRIVILEGES;

CREATE USER 'admin'@'%' IDENTIFIED BY 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
ALTER USER 'admin'@'%' IDENTIFIED BY 'admin';
FLUSH PRIVILEGES;

sample log
MariaDB [(none)]> create database tgh;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create user 'teguh' identified by 'teguhteguh';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all privileges on *.* to 'teguh'@'%' identified by 'teguhteguh' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>

2. allow specific hostname
grant all privileges on *.* to 'teguh'@'10.10.10.10' identified by 'teguhteguh' with grant option;
FLUSH PRIVILEGES;

sample log

MariaDB [(none)]> grant all privileges on *.* to 'teguh'@'10.10.10.10' identified by 'teguhteguh' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>

3. update password
UPDATE mysql.user SET Password = OLD_PASSWORD('teguhteguh') WHERE Host = '%' AND User = 'teguh';
FLUSH PRIVILEGES;

sample log

MariaDB [(none)]> UPDATE mysql.user SET Password = OLD_PASSWORD('teguhteguh') WHERE Host = '%' AND User = 'teguh';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>

4. create user readonly all database
create user 'teguhread' identified by 'passwordpassword';
GRANT SELECT, SHOW VIEW ON *.* TO 'teguhread'@'%' identified by 'passwordpassword' with grant option;
FLUSH PRIVILEGES;

sample log

MariaDB [(none)]> create user 'teguhread' identified by 'passwordpassword';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT SELECT, SHOW VIEW ON *.* TO 'teguhread'@'%' identified by 'passwordpassword' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>

5. create user readonly specific database (teguhth)
create user 'teguhread01' identified by 'passwordpassword';
GRANT SELECT, SHOW VIEW ON teguhth.* TO 'teguhread01'@'%' identified by 'passwordpassword' with grant option;
FLUSH PRIVILEGES;

sample log

MariaDB [(none)]> create user 'teguhread01' identified by 'passwordpassword';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT SELECT, SHOW VIEW ON teguhth.* TO 'teguhread01'@'%' identified by 'passwordpassword' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>

6. check mysql mariadb user

select * from mysql.user ;
select @@hostname, user, host, password, authentication_string from mysql.user;

sample log