#TIL : The safest way to reset root password of MySQL Server

When you get stucked in this error message “Access denied for user ‘[email protected]’ …”, you search the way to reset the root password on the Internet, but life is Hard ! (No answer makes you feel it’s right way, even some do not work)

So to solve this problem, we need to understand MySQL Authentication

Step 1 : Disable MySQL Authentication by skip loading grant-tables on loading MySQL server

Open MySQL server config file, it might be in /etc/mysql/mysql.conf.d/mysqld.cnf. Add this line to section mysql

1
2
[mysqld]
skip-grant-tables

DANGER : BE CAREFULL ! AFTER RESETTING SERVER, YOUR MYSQL SERVER ALLOWS ANY CONNECTION FROM ANY USER FROM ANY HOST BY ANY PASSWORD

So safe way is to make sure that you are the only one connect MySQL, by

  • change to listening port of the server
1
2
3
4
[mysqld]
skip-grant-tables
port=6033
bind-address = 127.0.0.1
  • disable access through MySQL socket
1
$ sudo chmod 400 /var/run/mysqld/mysqld.sock

Step 2 : Restart the MySQL server

1
$ sudo systemctl restart mysql

Step 3 : Connect to mysql server by mysql cli, now you can connect free

1
$ mysql -h 127.0.0.1 -P 6033

Step 4 : Analyze mysql.user table

1
2
3
4
5
6
7
8
9
mysql> use mysql              
Database changed
mysql> select Host, User, plugin, password_expired, account_locked from user where User = 'root';
+-----------+------------------+-----------------------+------------------+----------------+
| Host | User | plugin | password_expired | account_locked |
+-----------+------------------+-----------------------+------------------+----------------+
| % | root | mysql_native_password | N | N |
+-----------+------------------+-----------------------+------------------+----------------+
4 rows in set (0.00 sec)

These fields meaning :

  • Host : allowed client host name or IP address
    • 127.0.0.1 : allow local clients connect via TCP
    • localhost : allow local clients connect via local UNIX socket file /var/run/mysqld/mysqld.sock
    • % : any wildcard, allow from all hosts
  • User : allowed user name
    • root : allow root user
  • plugin :
    • mysql_native_password : use hashing function of MySQL PASSWORD('YOURPASSWORD'), stored in authentication_string field (MySQL 5.7+) or password field (MySQL 5.6 or older)
    • auth_socket : use socket
  • password_expired :
    • Y : password is expired
    • N : password is not expired (still working)
  • account_locked :
    • Y : account is locked
    • N : account is not locked (still working)

Step 5 : Reset your password

Rewrite your sql command by replacing NEWPASSWORD and WHERE statement to match account we analyze in Step 4

MySQL 5.7+

1
mysql> update user set plugin = 'mysql_native_password', authentication_string = PASSWORD('NEWPASSWORD'), password_expired = 'N', account_locked = 'N' where Host = '%' and User = 'root';

MySQL 5.6 or older

1
mysql> update user set plugin = 'mysql_native_password', password = PASSWORD('NEWPASSWORD'), password_expired = 'N', account_locked = 'N' where Host = '%' and User = 'root';

Make sure that we changed 1 row by checking the result log : Query OK, 1 rows affected (0.00 sec)

Step 6 : Flushing privileges

1
2
mysql> flush privileges;
mysql> quit;

Step 7 : Rollback all config changes

Update your mysql server config file, make sure to comment out skip-grant-tables

1
2
3
4
[mysqld]
# skip-grant-tables
port=3306
bind-address = 127.0.0.1
1
$ sudo systemctl restart mysql

Trying to connect to MySQL server with your new password

1
$ mysql -u root -h 127.0.0.1 -p

If anything works perfectly, last step is enabling access to socket file

1
$ sudo chmod 777 /var/run/mysqld/mysqld.sock

HOPE IT HELP ! WE SOLVE PROBLEMS BY UNDERSTANDING IT !

#TIL : Install CA root certificate on iOS device

Disclaimer : ⚠️ You can do it, but it’s at your own risk !

Sometimes you want to accept a SSL firewall proxy or self-MITM proxy, the important step is installing its CA root certificate to your device. Because iOS apps almost use all https connections (that’s new rule).

This is the way to install and enable custom CA Root cert :

  • Step 1 : encode your certificate to binary-PEM (only need when you try cat [ca-cert] and see ASCII base64 characters)
1
openssl x509 -outform der -in [ca-cert] -out [new-ca-cert].crt
  • Step 2 : Transfer the root certificate to your device (can use 1 of 2 methods : uploading cert to public webserver and open link in Safari app; or share certificate file through AirDrop - between 2 Apple devices).

Tips : use ngrok as a simple tunnel webserver if you don’t have AirDrop supported PC.

  • Step 3 : Click Install on install profile screen

  • Step 4 : Enable installed certificate, go to Settings > General > About > Certificate Trust Settings, then switch On your certificate item. (You could disable it when you don’t need it)

;) Check the web connection !

#TIL : Prevent source hacking from .git directory exposing

Many web project use Git as source version control tools. So in production
server, we could expose the hidden .git directory - which contains all most
infomation about project source code.

To “rip” a source code from a vulnerable website, we can use this tool : https://github.com/kost/dvcs-ripper#git

So to prevent this happens, try to deny all http access to hidden files and
directories (usually starts by . character)

Example of Nginx config

1
2
3
location ~ /\. {
deny all;
}

#TIL : Set up simple rate limiting on specified port using UFW

Allow unmetrered connections on networking is so risky. Attacker can use the brute-force attacks to comprosise your service (or simple DOS).

Linux has a cool firewall to hanlde this, via ip-tables. But it’s so complicated to remember all the rule and syntax. That’s why UFW was born to save us. :D

You can use simple command to manage your firewall

1
2
3
4
5
6
7
8
9
10
$ ufw default deny incoming # deny any incoming port, should be run before allow any port
$ ufw default allow outgoing # allow any outgoing port
$ ufw allow 80 # allow port 80
$ ufw deny 53/udp # allow udp protocol to port 53
$ ufw disable # disable firewall
$ ufw enable # enable firewall
$ ufw status # check all the rules
$ ufw delete [num] # delete the rule by its order in status result
$ ufw reload # reload all rule
$ ufw limit ssh/tcp # finnaly, limit ssh (port 22 tcp), deny connections if an IP address has attempted to initiate 6 or more connections in the last 30 seconds

#TIL : HSTS rule in browser

HTTP Strict Transport Security (HSTS) is a web security policy mechanism which helps to protect websites against protocol downgrade attacks and cookie hijacking.

Enabling HSTS on your web will make your browser validate every SSL issues more strictly :

  • User can not visit http version on browser
  • User can not add SSL exception for the domain to ignore the warning. (when SSL cert expire or invalid common name)

Note : You can manually remove a domain from HSTS in Chrome by accessing this page URL chrome://net-internals/#hsts

So remember to add HSTS to your website !

#TIL : Create SSH tunnel manually

SSH Tunnel is a fast way to transfer traffic through unsafe internet today. It would be used in MySQL connect, FTP connect or HTTP connect, …

Syntax :

1
$ ssh -L [local_port]:[remote_endpoint]:[remote_port] [ssh_user]:[ssh_ip]

Example :

Lets say you have a EC2 instance (123.45.67.89) and remote DB instance (98.76.54.32) listening port 3306

1
$ ssh -L 3307:98.76.54.32:3306 [email protected]

Testing ssh tunnel

1
2
3
$ telnet 127.0.0.1 3307
$ # or
$ mysql -h 127.0.0.1 -P 3307 -u root -p

#TIL : Runing old java applets on brower

Mostly morden browser has stop support Java plugins, so you can’t run Java applet on browser.

Temporary way :

  • run in IE or Safari
  • run in an old Firefox (version 23)

And what if old java applet can’t be runned on Java 8 because of weak signature algorithm. Try this

  • Open java.security file :
    • In MacOS, located in /Library/Java/JavaVirtualMachines/jdk[jdk-version].jdk/Contents/Home/jre/lib/security
    • In Windows, located in C:\Program File x86\Java\jre\lib\security
  • Comment this line, jdk.certpath.disabledAlgorithms=MD2, MD5, RSA keySize < 1024
  • Rerun applet