Posts Tagged ‘created’

How to Set MySQL MariaDB server root user to be able to connect from any host on the Internet / Solution to ‘ ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES) ‘

Tuesday, September 3rd, 2019

How-to-set-up-MariaDB-server-root-admin-user-to-be-able-to-connect-from-any-host-anywhere-mariadb-seal-logo-picture

In this small article, I'll shortly explain on how I setup a Standard default package MariaDB Database server on Debian 10 Buster Linux and how I configured it to be accessible from any hostname on the Internet in order to make connection from remote Developer PC with MySQL GUI SQL administration tools such as MySQL WorkBench / HeidiSQL / Navicat / dbForge   as well as the few set-backs experienced in the process (e.g. what was the reason for ' ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) '  error and its solution.

Setting new or changing old MariaDB (MySQL) root server password

 

I've setup a brand new MariaDB database (The new free OpenSource software fork of MySQL) mariadb-server-10.3 on a Debian 10, right after the OS was installed with the usual apt command:

# apt install mariadb-server

Next tep was to change the root access password which was set to empty pass by default, e.g. connected with mysql CLI locally while logged via SSH on server and run:

MariaDB [(none)]> mysql -u root -p

use mysql;
update user set authentication_string=PASSWORD("MyChosenNewPassword") where User='root';

There was requirement by the customer, that MySQL server is not only accessed locally but be accessed from any IP address from anywhere on the Internet, so next step was to do so.

Allowing access to MySQL server from Anywhere

Allowing access from any host to MariaDB SQL server  is a bad security practice but as the customer is the King I've fulfilled this weird wish too, by changing the listener for MariaDB (MySQL) on Debian 10 codenamed Buster
 
changing the default listener
to be not the default 127.0.0.1 (localhost) but any listener is done by modifying the bind-address directive in conf /etc/mysql/mariadb.conf.d/50-server.cnf:

root@linux:~# vim /etc/mysql/mariadb.conf.d/50-server.cnf

Then comment out

bind-address  = 127.0.0.1

and  add instead 0.0.0.0 (any listener)

 

bind-address  = 0.0.0.0
root@linux:/etc/mysql/mariadb.conf.d# grep -i bind-address 50-server.cnf
##bind-address            = 127.0.0.1
bind-address    = 0.0.0.0


Then to make the new change effective restart MariaDB (luckily still using the old systemV init script even though systemd is working.
 

root@linux:~# /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.


To make sure it is properly listening on MySQL defaults TCP port 3306, then as usual used netcat.

root@pritchi:~# netstat -etna |grep -i 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      109        1479917  

 

By the way the exact mariadb.cnf used on this middle-sized front-backend server is here – the serveris planned to be a Apache Web server + Database host with MySQL DB of a middle range to be able to serve few thousand of simultaneous unique customers.

To make sure no firewall is preventing MariaDB to be accessed, I've checked for any reject rules iptables and ipset definitions, e.g.:
 

root@linux:~# iptables -L |gre -i rej

root@linux:~# ipset list

 

Then to double make sure the MySQL is allowed to access from anywhere, used simple telnet from my Desktop Laptop PC (that also runs Debian Linux) towards the server .

hipo@jeremiah:~$ telnet 52.88.235.45 3306
Trying 52.88.235.45…
Connected to 52.88.235.45.
Escape character is '^]'.
[
5.5.5-10.3.15-MariaDB-1
                       rQ}Cs>v\��-��G1[W%O>+Y^OQmysql_native_password
Connection closed by foreign host.

 

As telnet is not supporting the data encryption after TCP proto connect, in a few seconds time, remote server connection is terminated.

 

Setting MySQL user to be able to connect to local server MySQL from any remote hostname


I've connected locally to MariaDB server with mysql -u root -p and issued following set of SQL commands to make MySQL root user be able to connect from anywhere:

 

CREATE USER 'root'@'%' IDENTIFIED BY 'my-secret-pass';
GRANT ALL ON *.* TO 'root'@'localhost';
GRANT ALL ON *.* TO 'root'@'%';

 

Next step, I've took was to try logging in with root (admin) MariaDB superuser from MySQL CLI (Command Line Interface) on my desktop just to find out, I'm facing a nasty error.
 

hipo@jeremiah:~$ mysql -u root -H remote-server-hostname.com -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)


My first guess was something is wrong with my root user created in MySQL's mysql.user table (In MySQL this is the privileges table that stores, how MySQL user credentials are handled by mysqld local OS running process.

 

Changing the MySQL root (admin) password no longer possible on Debian 10 Buster?

 

The standard way ot change the MySQL root password well known via a simple dpkg-reconfigure (provided by Debian's debconf is no longer working so below command produces empty output instead of triggering the good old Ncurses text based interface well-known over the years …

 

root@linux:~# /usr/sbin/dpkg-reconfigure mariadb-server-10.3

 

 

Viewing MariaDB (MySQL) username / password set-up from the CLI

 

To list how this set-privileges looked like I've used following command:

 

MariaDB [mysql]> select * from mysql.user where User = 'root';
+———–+——+——————————————-+————-+————-+————-+————-+————-+———–+————-+—————+————–+———–+————+—————–+————+————+————–+————+———————–+——————+————–+—————–+——————+——————+—————-+———————+——————–+——————+————+————–+————————+———————+———-+————+————-+————–+—————+————-+—————–+———————-+———————–+———————–+——————+———+————–+——————–+
| Host      | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | Delete_history_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string | password_expired | is_role | default_role | max_statement_time |
+———–+——+——————————————-+————-+————-+————-+————-+————-+———–+————-+—————+————–+———–+————+—————–+————+————+————–+————+———————–+——————+————–+—————–+——————+——————+—————-+———————+——————–+——————+————+————–+————————+———————+———-+————+————-+————–+—————+————-+—————–+———————-+———————–+———————–+——————+———+————–+——————–+
| localhost | root | *E6D338325F50177F2F6A15EDZE932D68C88B8C4F | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                       | N                | N       |              |           0.000000 |
| %         | root | *E6D338325F50177F2F6A15EDZE932D68C88B8C4F | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 |                       |                       | N                | N       |              |           0.000000 |
+———–+——+——————————————-+————-+————-+————-+————-+————-+———–+————-+—————+————–+———–+————+—————–+————+————+————–+————+———————–+——————+————–+—————–+——————+——————+—————-+———————+——————–+——————+————+————–+————————+———————+———-+————+————-+————–+—————+————-+—————–+———————-+———————–+———————–+——————+———+————–+——————–+

 

The hashed (encrypted) password string is being changed from the one on the server, so please don't try to hack me (decrypt it) 🙂
As it is visible from below output the Host field for root has the '%' string which means, any hostname is authorized to be able to connect and login to the MySQL server, so this was not the problem.

After quite some time on reading on what causes
' ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
',
I've spend some time reading various forum discussions online on the err such as the one on StackOverflow here SuperUser.com's  how to fix access denied for user 'root'@'localhost' and one on askubuntu.com's – ERROR 1045(28000) : Access denied for user 'root@localhost' (using password: no ) and after a while finally got it, thanks to a cool IRC.FREENODE.NET guy nicknamed, hedenface who pointed me I'm that, I'm trying to use the -H flag (Prodice HTML) instead of -h (host_name), it seems somehow I ended up with the wrong memory that the -H stands for hostname, by simply using -h I could again login Hooray!!!

 

root@linux:~$ mysql -u root -h remote-server-host.com -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.3.15-MariaDB-1 Debian 10

 

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


I've further asked the customer to confirm, he can connect also from his Microsoft Windows 10 PC situated on a different LAN network and got his confirmation. Few notes to make here is I've also installed phpmyadmin on the server using phpmyadmin php source code latest version, as in Debian 10 it seems the good old PHP is no longer available (as this crazy developers again made a mess and there is no phpmyadmin .deb package in Debian Buster – but that's a different story I'll perhaps try to document in some small article in future.

How to use find command to find files created on a specific date , Find files with specific size on GNU / Linux

Monday, October 16th, 2017

How to use find command to find files created on a specific date on GNU / Linux?

 

The easiest and most readable way but not most efficient ) especially for big hard disks with a lot of files not the best way) to do it is via:

 

find ./ -type f -ls |grep '12 Oct'

 


Example: To find all files modified on the 12th of October, 2017:

find . -type f -newermt 2017-10-12 ! -newermt 2017-10-13

To find all files accessed on the 29th of september, 2008:

$ find . -type f -newerat 2015-09-29 ! -newerat 2015-09-30

Or, files which had their permission changed on the same day:

$ find . -type f -newerct 2015-09-29 ! -newerct 2015-09-30

If you don't change permissions on the file, 'c' would normally correspond to the creation date, though.

 

Another more cryptic way but perhaps more efficient  to find any file modified on October 12th,2017,  would be with below command:

 

find . -type f -mtime $(( ( $(date +%s) – $(date -d '2017-10-12' +%s) ) / 60 / 60 / 24 – 1 ))

 

 

 

You could also look at files between certain dates by creating two files with touch

touch -t 0810010000 /tmp/f-example1
touch -t 0810011000 /tmp/f-example2

This will find all files between the two dates & times of the 2 files /tmp


 

find / -newer /tmp/f-example1 -and -not -newer /tmp/f-exampl2

 


How to Find Files with a certain size on GNU / Linux?

 

Lets say you got cracked and someone uploaded a shell php file of 50296 bytes a , that's a real scenario that just happened to me:

root@pcfreak:/var/www/blog/wp-admin/js# ls -b green.php 
green.php
root@pcfreak:/var/www/blog/wp-admin/js# ls -al green.php 
-rw-r–r– 1 www-data www-data 50296 окт 12 02:27 green.php

root@pcfreak:/home/hipo# find /var/www/ -type f -size 50296c -exec ls {} \;
/var/www/blog/wp-content/themes/default/green.php
/var/www/blog/wp-content/w3tc/pgcache/blog/tag/endless-loop/_index.html
/var/www/blog/wp-content/w3tc/pgcache/blog/tag/common/_index.html
/var/www/blog/wp-content/w3tc/pgcache/blog/tag/apacheroot/_index.html
/var/www/blog/wp-content/w3tc-bak/pgcache/blog/tag/endless-loop/_index.html
/var/www/blog/wp-content/w3tc-bak/pgcache/blog/tag/common/_index.html
/var/www/blog/wp-content/w3tc-bak/pgcache/blog/tag/apacheroot/_index.html
/var/www/pcfreakbiz/wp-includes/css/media-views.css
 

 

Change default new created Debian users insecure (all users read) permissons to secure

Tuesday, September 11th, 2012

In older versions of Debian, the directory permnissions of /home/ directory used to be secure. As of time of writing this post in Debian GNU / Linux Squeeze (6.0.5), default permissions are set to such, that all permissions are set readable for all users. e.g.:

debian:~$ ls -al /home/test
drwxr-xr-x 3 test test 4096 Jul 24 10:51 test

This kind of permissions are probably set like this because of Apache configuration to display every user custom file content in web via /home/[users]/public_html through http://website-url.com/~test … (whatever) ~username.

This is great for machines which are supposed to be used for hosting, but for mail server or SQL Database host such permissions of new created users is quite insecure practice. Also such a /home directory settings are not good even for Webserver hosting servers, which did not display any web content via /home/ users directories.

Therefore on each new configured Debian server it is a wonderful practice to change default new created user to only readable, writable and executable by the user itself ….

To do so;

1. Edit /etc/adduser.conf

Change the variable:

DIR_MODE=755

to

DIR_MODE=700

This will not affect the permissions of current existing directories in /home/, i.e. /home/user1 , /home/user2 etc. but any new created one with adduser or useradd cmd will get permissions of 700

Here are permissions of recent created user syrma:

debian:~# useradd syrma
debian:~# ls -ld /home/syrma
drwxr-xr-x 98 syrma syrma 12288 7 ceâ—ˆ 13,56 hipo/

In /etc/adduserconf, there are plenty of other useful options, you might want to tune (depending on the type of new configured server). Few worthy to change sometimes are:

FIRST_UID=1000, LAST_UID=29999
FIRST_GID=1000, LAST_GID=29999

This two controls how the USER IDSs (UID) and GROUP IDs (GID)s will get assigned. In some cases it might be useful to assign new users from 1000 onwards.

Another helpful variable to tune for creating new users from there is DSHELL, on Linux defaulting to DSHELL=bash shell.

If there is a necessity to set a GLOBAL new created user quota, this is easily done also via adduser.conf. The file allows you to disable certain strings in username from present in new user logins, configure if new users will have their separate directory or belong to a system wide (user group – like in Slackware) and few others. I suggest anyone new to Linux who hears it for a first time check it.

It is maybe worthy to mention in present moment on Ubuntu Linux, by default /etc/adduser.conf, DIR_HOME=700 (is restrictive).
For some new users, who try to configure Apache read /home/[user_dir]/public_html. This could lead to unexplainable errors like:

[Tue Sep 11 10:20:17 2012] [crit] [client 127.0.0.1] (13) Permission denied:/home/syrma/.htaccess pcfg_openfile: unable to check htaccess file, ensure it is readable

This error is due to /home/syrma, improper permissions (Apache) server user (www-data) fails to read /home/syrma directory content and the Apache forked child exits with the respective error.log critical err.

If you get some errors like this, the fix is to just make the directory with readable and executable flag for all users:

# chmod -R 0755 /home/syrma