logotype

Create a MySQL database and assign user permissions

Most of the time to keep the integrity of your MySQL server you may not wish you have phpmyadmin running locally or allow remote login to your MySQL server. This is generally the case when you have a MySQL server driving web based tools like forums or a CMS and you are connecting to it via SSH.

There are a lot of nice GUI tools that are available for user and database management when you have remote or local access to it (tools like Sparx Systems Enterprise Architect or Navicat) however some times you need to get down to the command line.

 

In Ubuntu Server access MySQL:

mysql -u root -p

Enter in the password

Remember now that you are in MySQL land so ever command must end with ;

Create the database:

create database smfau;

If successful you will receive messages like: Query OK, 1 row affected (0.00 sec)

Crate a new user for each database as we don't want to give root access to our applications:

create user 'newuser'@'localhost' identified by 'newuserpassword';

Be sure to swap in your own newuser and newuserpassword.


By default the new user as no permissions to do anything, so we want to give it some limited permissions to the new database. We also probably don't want to grant all permissions either as we have security to consider so we can give it a list of available permissions like this:

grant select, insert, update, delete, create, alter, drop, index on smfau.* to 'newuser'@'localhost';

As we are working directly with the database commandline we don't need to "flush permissions;" to make them active. Your user is now ready to go. For each different web application that needs to use a database I would suggest creating a new user. This means that it is less likely any compromised application to be able to affect the next.

Reference Short List of Commonly Used Permissions:

  • ALL PRIVILEGES- this would allow a MySQL user full access to a database
  • CREATE -  create new tables or databases
  • DROP -  them to delete tables or databases
  • DELETE -  delete rows from tables
  • INSERT - insert rows into tables
  • SELECT - use the Select command to read through databases
  • UPDATE - update table rows
  • GRANT OPTION - grant or remove other users' privileges

 

 

If you are looking for a guide to setup MySQL on Ubuntu then you might want to click here

Optimizing Apache2 for low spec servers

Recently my low spec web servers were starting to see more traffic and as such the available memory was running out.

This will be part 1 on how to optimize a low spec web server to be less hungry on the resources.

One of the most hungry processes on your web server by default will be Apache. But there are a few quick wins you can put in place to reduce its resource drain, even with web servers that are running multiple CMS (in my case both drupal and joomla)

Let's jump in and start modifying the apache2.conf:

sudo pico /etc/apache2/apache2.conf

You want to scroll through until you reach the mpm_prefork_module area. The defaults here are chewing through your servers memory - we want to make sure we are not starting too many servers or have too many spare servers remaining. Here are the settings I have used:

<IfModule mpm_prefork_module>
StartServers 1
MinSpareServers 1
MaxSpareServers 3
MaxClients 10
MaxRequestsPerChild 3000
</IfModule>

<IfModule mpm_worker_module>
StartServers 1
MinSpareThreads 5
MaxSpareThreads 15
ThreadLimit 25
ThreadsPerChild 5
MaxClients 25
MaxRequestsPerChild 200
</IfModule>

Also, you have an option to make some adjustments to KeepAliveTimeout. If the server is just going to be used for basic operations like static websites then something like 15 is ok. If you are using the server with a CMS like Joomla or Drupal then I would suggest keeping it to its default of 5 - this is also the case if you are using the mysql server remotely. This setting is the amount of time the server will wait for subsequent requests on a persistent connection. In reality it means that for the first connections it will wait the full time before 'processing' the login or page load.

Now to restart the server and to monitor the changes

service apache2 restart

I recommend checking out HTOP for monitoring the server, its appetite and what specific processes are doing the mastication.

How to setup TeamSpeak3 in Ubuntu

This guide is a combination of two others (linked below) that I used to successfully setup a TeamSpeak3 server running in Ubuntu.

40,000ft Overview:

  • Create a specific TeamSpeak user
  • Download and install the latest TeamSpeak manually
  • Setup the configs
  • Auto-run on start-up

Create the TeamSpeak user:

sudo adduser teamspeak
su teamspeak

To get the latest TeamSpeak Installers browse through the listing here - keeping in mind that you are looking for the 'server' files. At the time of writing the client software 3.0.13.1 but the server is only at 3.0.10.2:

http://teamspeak.gameserver.gamed.de/ts3/

Download the files to your server:

wget http://teamspeak.gameserver.gamed.de/ts3/releases/3.0.10.2/teamspeak3-server_linux-amd64-3.0.10.2.tar.gz

Unzip them:

tar xvf teamspeak3-server_linux-amd64-3.0.10.2.tar.gz

Move the unzipped files into a more human friendly folder:

mv teamspeak3-server_linux-amd64 teamspeak

And set some permissions to allow them to execute:

cd teamspeak
chmod +X ts3server_linux_amd64
chmod +X ts3server_minimal_runscript.sh

Now we will automatically create the ts3server.ini file:

./ts3server_minimal_runscript.sh createinifile=1

You will be presented with the IMPORTANT information re the server token and serveradmin usernames and passwords. Note this down in a safe place for your first login.

To autorun the server:

sudo pico /etc/rc.local

and add before the "exit 0" type:

sleep 2
su teamspeak -c '/home/teamspeak/teamspeak/ts3server_minimal_runscript.sh inifile=ts3server.ini' &

Save and exit - to test try and restart your server:

sudo shutdown -r now

 

 

References:

Guide 1

Guide 2

How to block IP addresses from accessing webpages in Ubuntu

 

I have recently had a scenario when one of my websites was using an older version of a CMS that wasn't as secure as it could have been and as such took down one of my Ubuntu servers by overloading it with Apache and MySQL requests. When I examined the web logs I found that most of the dodgy traffic was coming from a few specific IP addresses. I used UFW (Uncomplicated Firewall - a front end to iptables) to give the server a break.


It is worth noting that the rules are in order with the first matching being the rule that is used. We are going to add all www traffic but block specific IP addresses, so we are going to do the blocking first, then at the very end enable www then enable the firewall.

 

Steps to Reproduce:

Install UFW:

sudo apt-get install ufw

Check the Status:

sudo ufw status numbered

Make sure any changes are for IPv4 and IPV6

sudo vi /etc/default/ufw
IPV6=yes

Now make sure you don't cut yourself off

sudo ufw allow ssh
sudo ufw allow ftp
sudo ufw allow www

If you need to open up ports:

sudo ufw allow 2000:2100/tcp

And now to the business end. Look at your Apache log files to find the offenders:

tail -f /var/log/apache2/access.log

Example output:

130.185.139.213 - - [14/Jul/2014:14:41:28 +0000] "GET / HTTP/1.0" 200 6684 "-" "-"
128.199.159.98 - - [14/Jul/2014:14:41:36 +0000] "GET / HTTP/1.1" 200 4763 "http://www.yourwebsite.com/" "Mozilla/5.0 (iPad; CPU OS 6_1_3 like Mac OS X) AppleWebKit/536.26 (KHTML, like Gecko) Version/6.0 Mobile/10B329 Safari/8536.25"
178.73.202.206 - - [14/Jul/2014:14:41:44 +0000] "GET /?q=content/adam_lanza_look_warrants\xffasperger testing adults\xffexternal nofollow HTTP/1.1" 404 2961 "http://www.yourwebsite.com/" "Mozilla/5.0 (iPad; CPU OS 5_1_1 like Mac OS X) AppleWebKit/534.46 (KHTML, like Gecko) Version/5.1 Mobile/9B206 Safari/7534.48.3"
178.73.202.206 - - [14/Jul/2014:14:41:45 +0000] "GET /?q=node/add HTTP/1.1" 403 1627 "http://www.yourwebsite.com/" "Mozilla/5.0 (iPad; CPU OS 5_1_1 like Mac OS X) AppleWebKit/534.46 (KHTML, like Gecko) Version/5.1 Mobile/9B206 Safari/7534.48.3"
178.73.202.206 - - [14/Jul/2014:14:41:45 +0000] "GET /?q=user/register HTTP/1.1" 403 3177 "http://www.yourwebsite.com/?q=node/add" "Mozilla/5.0 (iPad; CPU OS 5_1_1 like Mac OS X) AppleWebKit/534.46 (KHTML, like Gecko) Version/5.1 Mobile/9B206 Safari/7534.48.3"
162.243.114.187 - - [14/Jul/2014:14:42:01 +0000] "GET / HTTP/1.1" 200 4748 "http://www.yourwebsite.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/28.0.1500.71 Safari/537.36"
87.149.73.204 - - [14/Jul/2014:14:42:25 +0000] "GET /content/pay_attention_family_survival_system_review HTTP/1.1" 404 516 "http://www.yourwebsite.com/" "Mozilla/5.0 (iPad; CPU OS 5_1_1 like Mac OS X) AppleWebKit/534.46 (KHTML, like Gecko) Version/5.1 Mobile/9B206 Safari/7534.48.3"
91.217.10.134 - - [14/Jul/2014:14:42:29 +0000] "GET /content/akasse HTTP/1.1" 404 490 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) )"

You will quickly see which IP addresses are hitting your servers the hardest, and which ones are not valid traffic.

(Remember not to block googlebot unless it is someone pretending to be googlebot)

To quickly add the IP addresses into UFW use the following command, replacing the IP addresses you wish to block:

ufw deny from 128.199.159.98 ; ufw deny from 178.73.202.206 ; ufw deny from 87.149.73.204  ; ufw deny from 94.23.59.173 ; ufw deny from 66.35.75.11

Once you are happy then allow all others to access www traffic

ufw allow www

If in future you want to remove a specific rule (like www when you are adding more IP addresses:

ufw status numbered
ufw delete 10 //or which ever number the 80/tcp rule is


If you are unsure if the IP address is a googlebot or not - or you don't know if it is legit you can do a forward and reverse look-up on its IP address. Unfortunately Google doesn't release a list of their bots IP addresses as they are constantly changing. So for example you might see the following line in your Apache access.log:

66.249.79.69 - - [14/Jul/2014:13:58:23 +0000] "GET /?q=shop&page=58 HTTP/1.1" 200 5654 "-" "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"

Run the following:

host 66.249.79.69
69.79.249.66.in-addr.arpa domain name pointer crawl-66-249-79-69.googlebot.com.

then a reverse to confirm:

host crawl-66-249-79-69.googlebot.com
crawl-66-249-79-69.googlebot.com has address 66.249.79.69

If the IP address doesn't match both ways - block it

 Now to enable UFW

sudo ufw enable

 

How to make an external HDD mount on boot of Ubuntu

To finish off my Network Attached Storage project I needed the external HDDs to mount when Ubuntu boots.

To do this we add the entries to fstab.

Incase we make any mistakes, lets create a backup of the current fstab file:

sudo cp /etc/fstab /etc/fstab.bak

Now we need to get the UUIDs of each of the drives we want to mount:

sudo blkid

Edit the fstab file

sudo pico /etc/fstab

Now add the next line to the file filling in all the relevant info for yourself for me it was

UUID=98C4CB77C4CB55E2 /media/wd1 ntfs uid=1000,gid=1000,umask=0022,sync,auto,rw 0 0

And the break down of what the info is.

UUID=<uuid> <pathtomount> <file system> uid=<userid>,gid=<groupid>,umask=0022,sync,auto,rw 0 0

Be sure to check the uid and guid of your system as they wont always be 1000:

id

Result:

uid=1000(oit) gid=1000(oit) groups=1000(oit),4(adm),24(cdrom),27(sudo),30(dip),46(plugdev),109(lpadmin),124(sambashare)

To unmount the drive use the following command, this is useful to safely remove a usb drive:

umount /media/wd1

 

There is a nice description on fstab over at linuxstall.com if you want more info

Copyright 2017 OReillyIT. All rights reserved.