MariaDB/MySQL Database Hardening
1) Start the server using –skip-show-database option.
2) All LOAD DATA LOCAL statements can be disabled from the server side by starting mysqld with the –local-infile=0 option.
3) Disable command history by setting MYSQL_HISTFILE to /dev/null or linking mysql_history to /dev/null
4) Rename user ‘root’. Furthermore, please make sure this account meets functional account requirements.
5) To remove the anonymous user, run the following commands:
shell> mysql -u root [password]
mysql> DELETE FROM mysql.user WHERE user = ”;
To enable ssl-cipher log :
========================
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ] grep ‘ssl-cipher’ my.cnf
ssl-cipher=AES128+EECDH:AES128+EDH
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ]
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ] service rh-mariadb101-mariadb restart
Stopping rh-mariadb101-mariadb: [ OK ]
Starting rh-mariadb101-mariadb: [ OK ]
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ]
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ] mysql -u root -p’xxxx’ -h localhost –ssl
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> \s
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 3
Current database: *** NONE ***
————–
mysql Ver 15.1 Distrib 10.1.13-MariaDB, for Linux (x86_64) using EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server: MariaDB
Server version: 10.1.13-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 8 sec
Threads: 1 Questions: 4 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 11 Queries per second avg: 0.500
————–
MariaDB [(none)]>
To mandate SSL for every user :
========================
[ root @ dbversity.com : ~ ] mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 52
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> select Host,User,Password,Select_priv as sel,Insert_priv as ins,Update_priv as upd,Delete_priv as del,Create_priv as crt,Drop_priv,Grant_priv,Super_priv,ssl_type,ssl_cipher,x509_issuer,x509_subject,authentication_string as auth,is_role,default_role as dr from mysql.user;
+————–+————-+——————————————-+—–+—–+—–+—–+—–+———–+————+————+———-+————+————-+————–+——+———+—-+
| Host | User | Password | sel | ins | upd | del | crt | Drop_priv | Grant_priv | Super_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | auth | is_role | dr |
+————–+————-+——————————————-+—–+—–+—–+—–+—–+———–+————+————+———-+————+————-+————–+——+———+—-+
| localhost | root | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | Y | Y | | | | | | N | |
| dbversity.com | root | | Y | Y | Y | Y | Y | Y | Y | Y | | | | | | N | |
| 127.0.0.1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | | | | | | N | |
| ::1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | | | | | | N | |
| localhost | | | N | N | N | N | N | N | N | N | | | | | | N | |
| dbversity.com | | | N | N | N | N | N | N | N | N | | | | | | N | |
| % | civausr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY | | | | | N | |
| localhost | civausr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | N | N | N | N | N | N | N | N | | | | | | N | |
| % | testusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | | | | | | N | |
| localhost | testusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | | | | | | N | |
| % | ssluser | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY | | | | | N | |
| % | newusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | | | | | | N | |
| % | newusr_ssl | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY | | | | | N | |
| % | aduser | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | N | N | N | N | N | N | N | N | | | | | | N | |
| % | sa_readonly | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | N | N | N | N | N | Y | N | | | | | | N | |
+————–+————-+——————————————-+—–+—–+—–+—–+—–+———–+————+————+———-+————+————-+————–+——+———+—-+
15 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show grants for ‘root’@’localhost’;
+—————————————————————————————————————————————-+
| Grants for root@localhost |
+—————————————————————————————————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ WITH GRANT OPTION |
| GRANT PROXY ON ”@’%’ TO ‘root’@’localhost’ WITH GRANT OPTION |
+—————————————————————————————————————————————-+
2 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show grants for ‘ssluser’@’%’;
+—————————————————————————————————————————–+
| Grants for ssluser@% |
+—————————————————————————————————————————–+
| GRANT ALL PRIVILEGES ON *.* TO ‘ssluser’@’%’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ REQUIRE SSL |
+—————————————————————————————————————————–+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show grants for ‘newusr’@’%’;
+—————————————————————————————————————-+
| Grants for newusr@% |
+—————————————————————————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO ‘newusr’@’%’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ |
+—————————————————————————————————————-+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO ‘newusr’@’%’ REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for ‘newusr’@’%’;
+—————————————————————————————————————————-+
| Grants for newusr@% |
+—————————————————————————————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO ‘newusr’@’%’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ REQUIRE SSL |
+—————————————————————————————————————————-+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show grants for ‘civausr’@’localhost’;
+—————————————————————————————————————-+
| Grants for civausr@localhost |
+—————————————————————————————————————-+
| GRANT USAGE ON *.* TO ‘civausr’@’localhost’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ |
+—————————————————————————————————————-+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> GRANT USAGE ON *.* TO ‘civausr’@’localhost’ REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for ‘civausr’@’localhost’;
+—————————————————————————————————————————-+
| Grants for civausr@localhost |
+—————————————————————————————————————————-+
| GRANT USAGE ON *.* TO ‘civausr’@’localhost’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ REQUIRE SSL |
+—————————————————————————————————————————-+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> show grants for ‘testusr’@’%’;
+—————————————————————————————————————–+
| Grants for testusr@% |
+—————————————————————————————————————–+
| GRANT ALL PRIVILEGES ON *.* TO ‘testusr’@’%’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ |
+—————————————————————————————————————–+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO ‘testusr’@’%’ REQUIRE SSL;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show grants for ‘testusr’@’%’;
+—————————————————————————————————————————–+
| Grants for testusr@% |
+—————————————————————————————————————————–+
| GRANT ALL PRIVILEGES ON *.* TO ‘testusr’@’%’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ REQUIRE SSL |
+—————————————————————————————————————————–+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.25 sec)
MariaDB [(none)]> select Host,User,Password,Select_priv as sel,Insert_priv as ins,Update_priv as upd,Delete_priv as del,Create_priv as crt,Drop_priv,Grant_priv,Super_priv,ssl_type,ssl_cipher,x509_issuer,x509_subject,authentication_string as auth,is_role,default_role as dr from mysql.user where ssl_type!=’ANY’;
+————–+————-+——————————————-+—–+—–+—–+—–+—–+———–+————+————+———-+————+————-+————–+——+———+—-+
| Host | User | Password | sel | ins | upd | del | crt | Drop_priv | Grant_priv | Super_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | auth | is_role | dr |
+————–+————-+——————————————-+—–+—–+—–+—–+—–+———–+————+————+———-+————+————-+————–+——+———+—-+
| localhost | root | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | Y | Y | | | | | | N | |
| dbversity.com | root | | Y | Y | Y | Y | Y | Y | Y | Y | | | | | | N | |
| 127.0.0.1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | | | | | | N | |
| ::1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | | | | | | N | |
| localhost | | | N | N | N | N | N | N | N | N | | | | | | N | |
| dbversity.com | | | N | N | N | N | N | N | N | N | | | | | | N | |
| localhost | testusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | | | | | | N | |
| % | aduser | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | N | N | N | N | N | N | N | N | | | | | | N | |
| % | sa_readonly | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | N | N | N | N | N | Y | N | | | | | | N | |
+————–+————-+——————————————-+—–+—–+—–+—–+—–+———–+————+————+———-+————+————-+————–+——+———+—-+
9 rows in set (0.06 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show grants for ‘testusr’@’%’;
+—————————————————————————————————————————–+
| Grants for testusr@% |
+—————————————————————————————————————————–+
| GRANT ALL PRIVILEGES ON *.* TO ‘testusr’@’%’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ REQUIRE SSL |
+—————————————————————————————————————————–+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO ‘testusr’@’%’ REQUIRE SSL; FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> show grants for ‘aduser’@’%’;
+—————————————————————————————————————-+
| Grants for aduser@% |
+—————————————————————————————————————-+
| GRANT SHOW DATABASES ON *.* TO ‘aduser’@’%’ IDENTIFIED BY PASSWORD ‘*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C’ |
| GRANT SELECT ON `mysql`.`tables_priv` TO ‘aduser’@’%’ |
| GRANT SELECT ON `mysql`.`proxies_priv` TO ‘aduser’@’%’ |
| GRANT SELECT ON `mysql`.`db` TO ‘aduser’@’%’ |
| GRANT SELECT ON `mysql`.`user` TO ‘aduser’@’%’ |
| GRANT SELECT ON `mysql`.`columns_priv` TO ‘aduser’@’%’ |
| GRANT SELECT ON `mysql`.`procs_priv` TO ‘aduser’@’%’ |
+—————————————————————————————————————-+
7 rows in set (0.00 sec)
MariaDB [(none)]> GRANT SHOW DATABASES ON *.* TO ‘aduser’@’%’ IDENTIFIED BY PASSWORD ‘*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C’ REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT SELECT ON `mysql`.`tables_priv` TO ‘aduser’@’%’ REQUIRE SSL;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show grants for ‘aduser’@’%’;
+—————————————————————————————————————————-+
| Grants for aduser@% |
+—————————————————————————————————————————-+
| GRANT SHOW DATABASES ON *.* TO ‘aduser’@’%’ IDENTIFIED BY PASSWORD ‘*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C’ REQUIRE SSL |
| GRANT SELECT ON `mysql`.`tables_priv` TO ‘aduser’@’%’ |
| GRANT SELECT ON `mysql`.`proxies_priv` TO ‘aduser’@’%’ |
| GRANT SELECT ON `mysql`.`db` TO ‘aduser’@’%’ |
| GRANT SELECT ON `mysql`.`user` TO ‘aduser’@’%’ |
| GRANT SELECT ON `mysql`.`columns_priv` TO ‘aduser’@’%’ |
| GRANT SELECT ON `mysql`.`procs_priv` TO ‘aduser’@’%’ |
+—————————————————————————————————————————-+
7 rows in set (0.00 sec)
MariaDB [(none)]> GRANT SELECT ON `mysql`.`procs_priv` TO ‘aduser’@’%’ require ssl;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> show grants for ‘testusr’@’localhost’;
+————————————————————————————————————————-+
| Grants for testusr@localhost |
+————————————————————————————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO ‘testusr’@’localhost’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ |
+————————————————————————————————————————-+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO ‘testusr’@’localhost’ require ssl;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for ‘testusr’@’localhost’;
+————————————————————————————————————————————-+
| Grants for testusr@localhost |
+————————————————————————————————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO ‘testusr’@’localhost’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ REQUIRE SSL |
+————————————————————————————————————————————-+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show grants for ‘sa_readonly’@’%’;
+——————————————————————————————————————————————–+
| Grants for sa_readonly@% |
+——————————————————————————————————————————————–+
| GRANT SELECT, CREATE USER ON *.* TO ‘sa_readonly’@’%’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ WITH GRANT OPTION |
+——————————————————————————————————————————————–+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> GRANT SELECT, CREATE USER ON *.* TO ‘sa_readonly’@’%’ REQUIRE SSL WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for ‘sa_readonly’@’%’;
+——————————————————————————————————————————————————–+
| Grants for sa_readonly@% |
+——————————————————————————————————————————————————–+
| GRANT SELECT, CREATE USER ON *.* TO ‘sa_readonly’@’%’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ REQUIRE SSL WITH GRANT OPTION |
+——————————————————————————————————————————————————–+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]>
MariaDB [(none)]> show grants for ‘root’@’::1′;
+—————————————————————+
| Grants for root@::1 |
+—————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’::1′ WITH GRANT OPTION |
+—————————————————————+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’::1′ REQUIRE SSL WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for ‘root’@’127.0.0.1’;
+———————————————————————+
| Grants for root@127.0.0.1 |
+———————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’127.0.0.1’ WITH GRANT OPTION |
+———————————————————————+
1 row in set (0.00 sec)
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’127.0.0.1’ REQUIRE SSL WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for ‘root’@’dbversity.com’;
+————————————————————————+
| Grants for root@dbversity.com |
+————————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’dbversity.com’ WITH GRANT OPTION |
| GRANT PROXY ON ”@’%’ TO ‘root’@’dbversity.com’ WITH GRANT OPTION |
+————————————————————————+
2 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’dbversity.com’ REQUIRE SSL WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select Host,User,Password,Select_priv as sel,Insert_priv as ins,Update_priv as upd,Delete_priv as del,Create_priv as crt,Drop_priv,Grant_priv,Super_priv,ssl_type,ssl_cipher,x509_issuer,x509_subject,authentication_string as auth,is_role,default_role as dr from mysql.user where ssl_type!=’ANY’;
+————–+——+——————————————-+—–+—–+—–+—–+—–+———–+————+————+———-+————+————-+————–+——+———+—-+
| Host | User | Password | sel | ins | upd | del | crt | Drop_priv | Grant_priv | Super_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | auth | is_role | dr |
+————–+——+——————————————-+—–+—–+—–+—–+—–+———–+————+————+———-+————+————-+————–+——+———+—-+
| localhost | root | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | Y | Y | | | | | | N | |
| localhost | | | N | N | N | N | N | N | N | N | | | | | | N | |
| dbversity.com | | | N | N | N | N | N | N | N | N | | | | | | N | |
+————–+——+——————————————-+—–+—–+—–+—–+—–+———–+————+————+———-+————+————-+————–+——+———+—-+
3 rows in set (0.00 sec)
MariaDB [(none)]> show grants for ‘root’@’localhost’;
+—————————————————————————————————————————————-+
| Grants for root@localhost |
+—————————————————————————————————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ WITH GRANT OPTION |
| GRANT PROXY ON ”@’%’ TO ‘root’@’localhost’ WITH GRANT OPTION |
+—————————————————————————————————————————————-+
2 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ REQUIRE SSL WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for ‘root’@’localhost’;
+—————————————————————————————————————————————————-+
| Grants for root@localhost |
+—————————————————————————————————————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ REQUIRE SSL WITH GRANT OPTION |
| GRANT PROXY ON ”@’%’ TO ‘root’@’localhost’ WITH GRANT OPTION |
+—————————————————————————————————————————————————-+
2 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> select Host,User,Password,Select_priv as sel,Insert_priv as ins,Update_priv as upd,Delete_priv as del
,Create_priv as crt,Drop_priv,Grant_priv,Super_priv,ssl_type as dr from mysql.user where ssl_type=’ANY’;
+————–+————-+——————————————-+—–+—–+—–+—–+—–+———–+————+————+—–+
| Host | User | Password | sel | ins | upd | del | crt | Drop_priv | Grant_priv | Super_priv | dr |
+————–+————-+——————————————-+—–+—–+—–+—–+—–+———–+————+————+—–+
| localhost | root | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| dbversity.com | root | | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| 127.0.0.1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| ::1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| % | civausr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| localhost | civausr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | N | N | N | N | N | N | N | N | ANY |
| % | testusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| localhost | testusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | ssluser | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | newusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | newusr_ssl | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | aduser | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | N | N | N | N | N | N | N | N | ANY |
| % | sa_readonly | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | N | N | N | N | N | Y | N | ANY |
+————–+————-+——————————————-+—–+—–+—–+—–+—–+———–+————+————+—–+
13 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> select Host,User,Password,Select_priv as sel,Insert_priv as ins,Update_priv as upd,Delete_priv as del ,Create_priv as crt,Drop_priv as drp,Grant_priv as grnt,Super_priv as super,ssl_type as dr from mysql.user where ssl_type=’ANY’;
+————–+————-+——————————————-+—–+—–+—–+—–+—–+—–+——+——-+—–+
| Host | User | Password | sel | ins | upd | del | crt | drp | grnt | super | dr |
+————–+————-+——————————————-+—–+—–+—–+—–+—–+—–+——+——-+—–+
| localhost | root | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| dbversity.com | root | | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| 127.0.0.1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| ::1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| % | civausr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| localhost | civausr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | N | N | N | N | N | N | N | N | ANY |
| % | testusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| localhost | testusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | ssluser | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | newusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | newusr_ssl | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | aduser | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | N | N | N | N | N | N | N | N | ANY |
| % | sa_readonly | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | N | N | N | N | N | Y | N | ANY |
+————–+————-+——————————————-+—–+—–+—–+—–+—–+—–+——+——-+—–+
13 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> select Host,User,Password,Select_priv as sel,Insert_priv as ins,Update_priv as upd,Delete_priv as del ,Create_priv as crt,Drop_priv as drp,Grant_priv as grnt,Super_priv as supr,ssl_type as dr from mysql.user where ssl_type=’ANY’;
+————–+————-+——————————————-+—–+—–+—–+—–+—–+—–+——+——+—–+
| Host | User | Password | sel | ins | upd | del | crt | drp | grnt | supr | dr |
+————–+————-+——————————————-+—–+—–+—–+—–+—–+—–+——+——+—–+
| localhost | root | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| dbversity.com | root | | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| 127.0.0.1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| ::1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| % | civausr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| localhost | civausr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | N | N | N | N | N | N | N | N | ANY |
| % | testusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| localhost | testusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | ssluser | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | newusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | newusr_ssl | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | aduser | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | N | N | N | N | N | N | N | N | ANY |
| % | sa_readonly | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | N | N | N | N | N | Y | N | ANY |
+————–+————-+——————————————-+—–+—–+—–+—–+—–+—–+——+——+—–+
13 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> select Host,User,Password,Select_priv as sel,Insert_priv as ins,Update_priv as upd,Delete_priv as del ,Create_priv as crt,Drop_priv as drp,Grant_priv as grnt,Super_priv as supr,ssl_type from mysql.user where ssl_type=’ANY’;
+————–+————-+——————————————-+—–+—–+—–+—–+—–+—–+——+——+———-+
| Host | User | Password | sel | ins | upd | del | crt | drp | grnt | supr | ssl_type |
+————–+————-+——————————————-+—–+—–+—–+—–+—–+—–+——+——+———-+
| localhost | root | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| dbversity.com | root | | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| 127.0.0.1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| ::1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | ANY |
| % | civausr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| localhost | civausr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | N | N | N | N | N | N | N | N | ANY |
| % | testusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| localhost | testusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | ssluser | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | newusr | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | newusr_ssl | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | Y | Y | Y | Y | Y | N | Y | ANY |
| % | aduser | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | N | N | N | N | N | N | N | N | ANY |
| % | sa_readonly | *9F1E8C39D634182D145B3D26DE12D09F7C66D903 | Y | N | N | N | N | N | Y | N | ANY |
+————–+————-+——————————————-+—–+—–+—–+—–+—–+—–+——+——+———-+
13 rows in set (0.00 sec)
To enable skip-show-database :
========================
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ] sed “s/#skip-show-database/skip-show-database/g” my.cnf | grep skip
skip-show-database
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ]
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ] sed -i “s/#skip-show-database/skip-show-database/g” my.cnf
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ]
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ] grep ‘skip’ my.cnf
skip-show-database
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ]
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ] service rh-mariadb101-mariadb restart
Stopping rh-mariadb101-mariadb: [ OK ]
Starting rh-mariadb101-mariadb: [ OK ]
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ]
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ]
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ]
[ root @ dbversity.com : ~ ] mysql -u root -p -h localhost –ssl
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> show global variables like “%skip_show%”;
+——————–+——-+
| Variable_name | Value |
+——————–+——-+
| skip_show_database | ON |
+——————–+——-+
1 row in set (0.00 sec)
MariaDB [(none)]>
To disable local-infile :
=================
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ] grep ‘local-infile’ my.cnf
local-infile=0
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ]
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ] mysql -u root -p’mypassword’ -h localhost –ssl
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> show global variables like “%local%”;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| local_infile | OFF |
+—————+——-+
1 row in set (0.00 sec)
MariaDB [(none)]>
To disable mysql history :
===================
MariaDB/MySQL stores the commands typed in the mysql> prompt in the ~/.mysql_history file.
[ root @ dbversity.com : ~ ] ll -lhtr .mysql_history
-rw——- 1 root root 19K Jun 15 06:12 .mysql_history
[ root @ dbversity.com : ~ ]
Connect to mysql from the llinux command line and execute few sql commands as shown below.
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ] mysql -u root -p’mypassword’ -h localhost –ssl
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> show global variables like “%local%”;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| local_infile | OFF |
+—————+——-+
1 row in set (0.00 sec)
Now, if you press up arrow, you can see all the previous commands you’ve typed from the mysql prompt.
Exit from the mysql command prompt and view the ~/.mysql_history file that will contain all the sql commands you executed from the mysql command prompt.
[ root @ dbversity.com : ~ ] head .mysql_history
_HiStOrY_V2_
show\040databases;
show\040dbs
;
show\040databases;
use\040mysql
show\040tables;
select\040*\040from\040user;
[ root @ dbversity.com : ~ ]
[ root @ dbversity.com : ~ ]
Disable mysql history by pointing .mysql_history to /dev/null
===============================================
First, remove the ~/.mysql_history file
[ root @ dbversity.com : ~ ] rm -rf .mysql_history
[ root @ dbversity.com : ~ ]
Next, create a symbolic link of ~/.mysql_history pointing to /dev/null as shown below.
[ root @ dbversity.com : ~ ] ln -s /dev/null ~/.mysql_history
[ root @ dbversity.com : ~ ] ls -l .mysql_history
lrwxrwxrwx 1 root root 9 Jun 15 07:26 .mysql_history -> /dev/null
[ root @ dbversity.com : ~ ]
Now, login to the mysql and execute few sql commands. You’ll notice that ~/.mysql_history file is empty and does not store any previously typed commands.
[ root @ dbversity.com : ~ ] mysql -u root -p -h localhost –ssl
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+——————–+
5 rows in set (0.00 sec)
MariaDB [(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [test]>
MariaDB [test]>
MariaDB [test]> create table tbl (id int, name varchar(10));
Query OK, 0 rows affected (0.09 sec)
MariaDB [test]>
MariaDB [test]> ^DBye
[ root @ dbversity.com : ~ ]
[ root @ dbversity.com : ~ ] cat .mysql_history
[ root @ dbversity.com : ~ ]
Disable mysql history using MYSQL_HISTFILE environment variable
===================================================
First, remove the ~/.mysql_history file
[ root @ dbversity.com : ~ ] rm ~/.mysql_history
rm: remove regular file `/root/.mysql_history’? y
[ root @ dbversity.com : ~ ]
[ root @ dbversity.com : ~ ]
Next, set the MYSQL_HISTFILE env variable to /dev/null
[ root @ dbversity.com : ~ ] export MYSQL_HISTFILE=/dev/null
[ root @ dbversity.com : ~ ] set | grep MYSQL
MYSQL_HISTFILE=/dev/null
_=MYSQL_HISTFILE
[ root @ dbversity.com : ~ ]
[ root @ dbversity.com : ~ ] env | grep MYSQL
MYSQL_HISTFILE=/dev/null
[ root @ dbversity.com : ~ ]
Now, login to the mysql and execute few sql commands. You’ll notice that ~/.mysql_history file is not getting created anymore.
[ root @ dbversity.com : ~ ] mysql -u root -p -h localhost –ssl
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+——————–+
5 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [test]> exit
Bye
[ root @ dbversity.com : ~ ]
[ root @ dbversity.com : ~ ] cat ~/.mysql_history
cat: /root/.mysql_history: No such file or directory
[ root @ dbversity.com : ~ ]
Now, login to the mysql and execute few sql commands. You’ll notice that ~/.mysql_history file is not getting created anymore.
To disable/remove anonymous users :
=============================
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ] mysql -u root -p’mypassword’ -h localhost –ssl
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> select Host,User,Password FROM mysql.user WHERE user = ”;
+————–+——+———-+
| Host | User | Password |
+————–+——+———-+
| localhost | | |
| dbversity.com | | |
+————–+——+———-+
2 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> DELETE FROM mysql.user WHERE user = ”;
Query OK, 2 rows affected (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> select Host,User,Password FROM mysql.user WHERE user = ”;
Empty set (0.00 sec)
MariaDB [(none)]>
To enable General log :
=================
[ root @ dbversity.com : /var/opt/rh/rh-mariadb101/lib/mysql ] grep “general” /etc/opt/rh/rh-mariadb101/my.cnf
general-log
general-log-file=/var/opt/rh/rh-mariadb101/lib/mysql/general_query.log
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ] service rh-mariadb101-mariadb restart
Stopping rh-mariadb101-mariadb: [ OK ]
Starting rh-mariadb101-mariadb: [ OK ]
[ root @ dbversity.com : /etc/opt/rh/rh-mariadb101 ]
[ root @ dbversity.com : /var/opt/rh/rh-mariadb101/lib/mysql ] mysql -u root -p -h localhost –ssl
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> show global variables like ‘%general%’;
+——————+——————————————————-+
| Variable_name | Value |
+——————+——————————————————-+
| general_log | ON |
| general_log_file | /var/opt/rh/rh-mariadb101/lib/mysql/general_query.log |
+——————+——————————————————-+
2 rows in set (0.00 sec)
MariaDB [(none)]> ^DBye
[ root @ dbversity.com : /var/opt/rh/rh-mariadb101/lib/mysql ] head general_query.log
/opt/rh/rh-mariadb101/root/usr/libexec/mysqld, Version: 10.1.13-MariaDB (MariaDB Server). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
160615 4:09:00 3 Connect root@localhost as anonymous on information_schema
3 Query show databases
3 Query show tables
3 Field List ALL_PLUGINS
3 Field List APPLICABLE_ROLES
[ root @ dbversity.com : /var/opt/rh/rh-mariadb101/lib/mysql ]
select Host,User,Password,Select_priv as sel,Insert_priv as ins,Update_priv as upd,Delete_priv as del,Create_priv as crt,Drop_priv as drp,Grant_priv as grnt,Super_priv as super,ssl_type,ssl_cipher,x509_issuer,x509_subject,authentication_string as auth,is_role,default_role as dr from mysql.user;