.::: Create user 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 

MariaDB [(none)]> select @@hostname, user, host, password, authentication_string from mysql.user;
+------------+-------------+-------------+-------------------------------------------+-----------------------+
| @@hostname | user        | host        | password                                  | authentication_string |
+------------+-------------+-------------+-------------------------------------------+-----------------------+
| teguhth    | root        | localhost   | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |                       |
| teguhth    | root        | nmslinux    | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |                       |
| teguhth    | root        | 127.0.0.1   | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |                       |
| teguhth    | root        | ::1         | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |                       |
| teguhth    | teguh       | %           | 2c5b8df849650f89                          |                       |
| teguhth    | root        | %           | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |                       |
| teguhth    | tk          | localhost   | *65DDB9EA87AFAB9C3E63D516D355C665B7A58CF4 |                       |
| teguhth    | teguh       | 10.10.10.10 | *EB39BF97E50561CC0C8AA79DC4F49D572679286F |                       |
| teguhth    | teguhread   | %           | *FED6D8B57ED48A6DE7F749DFBE0F4C29BBB610E1 |                       |
| teguhth    | teguhread01 | %           | *FED6D8B57ED48A6DE7F749DFBE0F4C29BBB610E1 |                       |
+------------+-------------+-------------+-------------------------------------------+-----------------------+
10 rows in set (0.00 sec)

MariaDB [(none)]> 

 
7. Show privilege user

MariaDB [(none)]> select @@hostname, user, host, password, authentication_string from mysql.user;
+------------+-------------+-------------+-------------------------------------------+-----------------------+
| @@hostname | user        | host        | password                                  | authentication_string |
+------------+-------------+-------------+-------------------------------------------+-----------------------+
| MCS-Dev    | root        | localhost   | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |                       |
| MCS-Dev    | root        | nmslinux    | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |                       |
| MCS-Dev    | root        | 127.0.0.1   | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |                       |
| MCS-Dev    | root        | ::1         | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |                       |
| MCS-Dev    | abc         | %           | *0D3CED9BEC10A777AEC23CCC353A8C08A633045E |                       |
| MCS-Dev    | root        | %           | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |                       |
| MCS-Dev    | tk          | localhost   | *65DDB9EA87AFAB9C3E63D516D355C665B7A58CF4 |                       |
| MCS-Dev    | teguh       | 10.10.10.10 | *EB39BF97E50561CC0C8AA79DC4F49D572679286F |                       |
| MCS-Dev    | teguhread   | %           | *FED6D8B57ED48A6DE7F749DFBE0F4C29BBB610E1 |                       |
| MCS-Dev    | teguhread01 | %           | *FED6D8B57ED48A6DE7F749DFBE0F4C29BBB610E1 |                       |
| MCS-Dev    | mariatest   | %           | *EB39BF97E50561CC0C8AA79DC4F49D572679286F |                       |
| MCS-Dev    | cba         | %           | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |                       |
+------------+-------------+-------------+-------------------------------------------+-----------------------+
12 rows in set (0.00 sec)

MariaDB [(none)]> show grants for mariatest;
+-------------------------------------------------------------------------------------------------------------------------------------+
| Grants for mariatest@%                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'mariatest'@'%' IDENTIFIED BY PASSWORD '*EB39BF97E50561CC0C8AA79DC4F49D572679286F' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> show grants for 'teguh'@'10.10.10.10';
+-------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for teguh@10.10.10.10                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'teguh'@'10.10.10.10' IDENTIFIED BY PASSWORD '*EB39BF97E50561CC0C8AA79DC4F49D572679286F' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>