How To Create a New User for MariaDB

I had to setup a few test databases recently and I had forgotten how to create a new local user. For future reference.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

You can also use the percent sign to allow both local and remote access.

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password'; 

In order to allow permissions to tables you need to grant privileges. This command will grant your user privileges to all databases on the server. Not normally what you would do as it is not very secure but in this case I am just setting up a test on a VM inside the local network.

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

When you are all done you will need to reload the permissions in order for them to take effect.

FLUSH PRIVILEGES;

References: 

http://dev.mysql.com/doc/refman/5.7/en/create-user.html 

https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

What Time is It?

One of my Windows 2008R2 servers recently had a hard time syncing time to the default time.windows.com. I know I shouldn't really use it in the first place but what can I say.

I took the time to learn some more about Windows time sync set it up correctly to sync off of multiple servers. The Windows Time Service is controlled by the w32tm executable. Some of the options may work in a regular prompt but for the good commands you will need to run it in an elevated command prompt.

First lets see what is going one by querying the status.

w32tm /query /status
Leap Indicator: 0(no warning)
Stratum: 2 (secondary reference - syncd by (S)NTP)
Precision: -6 (15.625ms per tick)
Root Delay: 0.0559595s
Root Dispersion: 0.0273364s
ReferenceId: 0x808A8DAC (source IP:  128.138.141.172)
Last Successful Sync Time: 5/19/2015 8:05:01 PM
Source: time.nist.gov,0x1
Poll Interval: 8 (256s)

To determine the current source we can query the source.

w32tm /query /source
time.nist.gov,0x1

To compare the current computer to a time server you can use the stripchart function.

w32tm /stripchart /computer:time.nist.gov
Tracking time.nist.gov [128.138.141.172:123].
The current time is 5/19/2015 8:02:40 PM.
20:02:40 d:+00.0599903s o:-00.0166235s  [                           *                           ]
20:02:42 d:+00.0559723s o:-00.0174569s  [                           *                           ]
20:02:44 d:+00.0569959s o:-00.0179584s  [                           *                           ]
20:02:46 d:+00.0569889s o:-00.0174459s  [                           *                           ]

The command I used for this server.

w32tm /config /manualpeerlist:"pool.ntp.org,0x1 time.nist.gov,0x1" /syncfromflags:manual /reliable:yes /update

One item that I have not fully answered is if I should change the 0x1 after each entry to 0x8. 0x1 acts like it is a peer whereas 0x8 is just a client. I suspect that the time servers just ignore the peer information and treat all of the requests as clients but I have not found a definitive answer yet.


Related:
Windows Server w32tm
Windows Time Service at Ntp.org
More Windows Time Service at Ntp.org
How Windows Time Service Works
Windows Time Service Tools and Settings
Explanation of some of the modes of operation
Older Blog Entry from one of the Windows Time Service Authors

Syslog to MySQL Update

I am in the process of updating some machines around here.
I am installing Mageia 4 Linux on one machine and it will be my syslog repository.

Mageia 4 is running journalctl and does not use syslog natively by default.
I had to install rsyslog, rsyslog-mysql, mariadb, and other required packages.

The first step is to modify the 00_ common.conf. Most of the lines matched what I needed but we needed to enable the UDP port to listen for other devices logging to this one.
 Uncomment the following two lines.

$ModLoad imudp.so
$UDPServerRun 514




Restart rsyslog to use the new settings.

systemctl rsyslog restart

Now run netstat -a and verify the the UDP port *.syslog or UDP port 514 is open.

Force some device that is configured to log to this server to do something to create and entry. I logged into a switch to get it to log. Then check the log file.

tail /var/log/syslog

If you have entries from the correct device you know that remote logging is working. Now to get the database part done.

rsyslog come with a default SQL schema in  /usr/share/doc/rsyslog-mysql/.
The file is called createDB.sql.

To setup the default database I ran:

mysql < createDB.sql

 This creates a database called Syslog with two tables.

Then I created a new user and gave it privileges to the Syslog database:

GRANT ALL PRIVILEGES ON Syslog.* To 'Syslogusername'@'localhost' IDENTIFIED BY 'password';

The rsyslog config file /etc/rsyslog.conf calls config files in two other locations.

One location is /etc/rsyslodg.d/ and the file 01_mysql.conf needs to have two lines:

$ModLoad ommysql.so
 

*.*     :ommysql:127.0.0.1,Syslog,Syslogusername,password

The first line loads the mysql library and the second line has the database name and the user credentials.

Another restart of the rsyslog service and your entries should now start being logged into the database.

SELECT * FROM Syslog.SystemEvents; and check for the entries. 

Reverse Sensing System Troubleshooting

The reverse sensing system on my 2006 F-150 stopped working recently. I had not noticed that the off light was illuminated on the switch and I while I was backing up, it dawned on me that there was no beeping from the reverse sensing system.

The reverse sensing system OFF light being illuminates is one of the key determinations for where to start troubleshooting. If the OFF light is illuminated ALL of the time, even when parked and driving forward then you most likely have a problem with the module/speaker.
If however the OFF light only illuminates when you put the vehicle in reverse then you most likely have a bad sensor or wiring to the sensors. We will tackle the sensor problem first.

To troubleshoot the sensors, park the vehicle in a quiet and safe place. Put a chock BEHIND one of the wheels. Then turn the key to the run position but DO NOT start the vehicle. Then place vehicle in reverse. Walk behind the vehicle and place your ear close to each of the sensors.
You should hear a faint, tic - tic - tic - tic. The sensors are ultrasonic and you should be able to hear each one fire. Listen to each one until you find one that is not making any sound. That is probably the bad one. This assumes that you have not been in an accident or had any damage recently, that should be rather obvious. Also take a look at the sensor wiring connector for corroded or loose connections. If you have to replace a sensor they are about $150 each from the dealer.

Now if the OFF light is illuminated all of the time then we probably have a speaker or module issue. The speakers appear to be a common problem as there was a tech bulletin issued and their is a Parking Distance Control Kit  P/N 4L1Z-15A866-AA that is specifically addressed for the Tech Bulletin. In my 2006 F150 Crew Cab, the module was located on the drivers side up under the dash, up above the drivers left foot. Lay on the floor where your left foot would go and look up. You should see a black box with a speaker on it.

I used a multimeter to take a resistance check of the speaker and received a reading of infinity. The speaker appeared to be open, a common failure mode for speakers. The resistance should be close to the impedance of the speaker, about 4 ohms for a speaker with 4 ohms of impedance.

I picked up a new speaker from the dealership for $34 even though I found it online for much cheaper. I felt ripped off but I wanted to get it fixed and not have to wait for shipping since when I put things off I almost never get back to them again soon.

I replaced the speaker and my system started working again just fine.

Now if you want, you can take the vehicle to the dealership and they can hook up the computer and they can tell you which sensor or part to replace but I do not like paying over $100 and waiting half of the day for that. The reverse sensor engine codes are not readable by most of the scanners. I have a decent scanner that can read extended codes on most makes but nothing on the reverse sensing system.

Trust Relationship Problems

One of the workstations in a domain was displaying "Trust Relationship Between Workstation and Domain has Failed" or something similar whenever a user tried to logon. I was able to logon via the local admin and access the network. This problem is caused by the shared secret or password used by Kerberos that had gotten out of sync with the domain controller. This particular machine has had some problem with hibernating in the past and is not used on a daily basis so that kind of makes sense. Googling the problem results in many hits that advise you to remove the machine from the domain and then rejoin it. This sounds a little heavy handed and at least one person agrees with me. I attempted the solution using netdom.exe and ran into a few issues and then making a command decision I figured it would take less time to leave and rejoin the domain then to mess with anything else. I can learn something new another day. After the reboots, the problem was solved and users could login normally. I should look at that hibernate problem while I am here....

Disable SSTP Certificate Revocation Check

Windows 7 supports a number of different types of VPN protocols. PPTP and IKE can be problematic when attempting to connect through a router that is conducting NAT. SSTP gets around that problem by tunneling over an SSL session. In order to enable SSTP, the server must have a certificate with a hostname that matches the certificate and the CRL URL must be reachable by the client. These are not always easy to accomplish when using a self signed certificate. In my case I had everything working except for the CRL URL. The client was unable to check the CRL so it rejected the connection. An interesting form of Denial of Service could be implemented by denying access to CRLs, but that is a bit of a digression. On the client computer, create a DWORD value NoCertRevocationCheck under HKLM\System\CurrentControlSet\Services\Sstpsvc\Parameters Set the value = 1. This will disable the certificate revocation check and enable the connection to proceed.

Smoothwall Enhanced Firewall Log Module

I really love my Smoothwall firewall. It offers good security using an older computer and it is infinitely customizable. One of the big strengths of Smoothwall is that with a little bit of Perl you can make it do almost whatever you want. There is a large ecosystem of third party modules that allow you to customize the functionality of your Smoothwall box.

One of the modules that I use is the enhanced firewall log. It is currently at version 1.4.3 and it enhances the normal log viewer and allows you to sort on different values and adds color coding to the entries.

This module has worked fine for me up until today. I was having some issues reaching some sites on the Internet and it appeared the firewall was the issue. I logged into the Smoothwall and it was slow to respond. The box had an uptime of a little over 9 months so I decided to reboot it. After the reboot was complete the firewall log was giving me an HTTP 500 error. Every other page was fine.

The error log showed:

Premature end of script headers: firewalllog.dat,
 at /httpd/cgi-bin/logs.cgi/firewalllog.dat line 409

After some Internet searching I found the following link in the Smoothwall communities.

Copy and paste of relevant information that fixed my problem.

he enhanced firewall logs mod with the CIDR capabilities assumes that all of the data the first field of the ipblock file will be numeric but it doesn't test it... my fix adds two lines and gets us around this problem... at least at the stage of reading the data from the ipblock file

 in /httpd/cgi-bin/logs.cgi/firewalllog.dat, near line 57, you should find

# Added by fwlogmod

use Socket;

use Net::CIDR;

# END added by fwlogmod

right after that, add

use Scalar::Util qw(looks_like_number);

so the block looks like this (until the maintainer possibly adds this to their released code)

# Added by fwlogmod

use Socket;

use Net::CIDR;

# END added by fwlogmod

use Scalar::Util qw(looks_like_number);

then down near line 217 you should find

open (ACTIVEBLOCKFILE, "/var/smoothwall/ipblock/config");

@ll=;
close(ACTIVEBLOCKFILE);
foreach $lll (@ll) {
            chomp($lll);
            @ittt=split(/,/,$lll);
            $cidrstr= $ittt[0];

between those last two lines, add

next if !looks_like_number($ittt[0]);  ## make sure it is a number!

so the whole block now looks like this

open (ACTIVEBLOCKFILE, "/var/smoothwall/ipblock/config");

@ll=;
close(ACTIVEBLOCKFILE);
foreach $lll (@ll) {
            chomp($lll);
            @ittt=split(/,/,$lll);
            next if !looks_like_number($ittt[0]);  ## make sure it is a number!
            $cidrstr= $ittt[0];

save and done... now if the first field of the ipblock file is not numeric, your firewall log viewer won't blow up on you.