Wednesday 18 July 2012

Tuesday 17 July 2012

Managing MYSQL users

These are a few commands I use in mysql to manage users and grants. I do this infrequently so I put them here to save having to google them when I need them.

Grant a user 'dev' all privileges on a database called "test";

mysql> GRANT ALL PRIVILEGES ON `test`.* TO 'dev'@'localhost' IDENTIFIED BY 'devtest';
Query OK, 0 rows affected (0.02 sec)


See full syntax for GRANT command

To see what privileges a user has been granted;

mysql> SHOW GRANTS FOR 'dev'@'localhost';
+-----------------------------------------------------------------------------------------+
| Grants for dev@localhost                                                                |
+-----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dev'@'localhost' IDENTIFIED BY PASSWORD '*D98YCCE724CCT7BFA48E1' |

| GRANT ALL PRIVILEGES ON `test`.* TO 'dev'@'localhost'                                   |
+-----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


Sometimes I need to list all the users that have had permissions granted to them;

mysql> SELECT CONCAT('SHOW GRANTS FOR \'', user,'\'@\'', host, '\';') AS mygrants FROM mysql.user ORDER BY mygrants;


+-------------------------------------------------+
| mygrants                                        |
+-------------------------------------------------+
| SHOW GRANTS FOR ''@'localhost';                 |
| SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; |
| SHOW GRANTS FOR 'dev'@'192.168.4.2';            |
| SHOW GRANTS FOR 'dev'@'localhost';              |
| SHOW GRANTS FOR 'root'@'127.0.0.1';             |
| SHOW GRANTS FOR 'root'@'::1';                   |
| SHOW GRANTS FOR 'root'@'localhost';             |
+-------------------------------------------------+


From that table you can copy-paste the relevant line to see the grants for a particular user.


Revoke a grant

mysql> REVOKE ALL PRIVILEGES ON `test`.* FROM 'dev'@'localhost';
Query OK, 0 rows affected (0.02 sec)


After revoking a users privileges, you will notice that the user still shows up with USAGE rights. To make a user go away completely you need to "drop" them;

mysql> drop user 'dev'@'localhost';
Query OK, 0 rows affected (0.00 sec)



Thursday 12 July 2012

HOWTO: Squid 3 Transparent Proxy

A lot of the stuff on the Internet describing how to do transparent proxy is outdated and does not work on more recent distro's that sport Squid V3.

This guide is Googles top hit for "squid transparent proxy" but it doesn't work. If you attempt to configure Squid 3 using the "httpd_accel" directives provided in that post squid will simply fail to start.

It seems that the developers of Squid 3 have streamlined the configuration of squids transparent proxy feature down to a single word.

If you find the http_port directive in your squid.conf and add the word "transparent" to the end of it then you have basically configured squid as a transparent proxy.


Find a line like this;


http_port 3128


Add "transparent" to the end so that it looks like this;

http_port 3128 transparent

Restart squid and you are done. All that is required now is to redirect traffic on your firewall to go to the proxy.

You can use your iptables firewall to redirect web traffic (port 80) to your squid proxy with  these commands;

iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 80 -j DNAT --to 10.1.1.1:3128
iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 80 -j REDIRECT --to-port 3128


This assumes that your LAN adaptor (the adapter that your client requests are coming in on) is eth0 and that the IP address of your proxy is 10.1.1.1

You can test that your proxy is working by accessing the Internet from a network client on your LAN and monitoring squids access log file;


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

If you browse to www.tuxnetworks.com while watching the access.log file then you should see something like this;

1342076113.358      1 10.1.1.14 TCP_HIT/200 437 GET http://www.tuxnetworks.com/ - NONE/- text/html

Enjoy! 

Tuesday 10 July 2012

HOWTO: nvidia-173 on Mint 13 (and Ubuntu 12.04 Precice)

I tried to install Mint 13 on an ancient PC with a Geforce 6200 graphics card. 

It didn't work. 

The symptom was that Cinnamon would be missing all panels and the window borders were missing. All that was visible on the desktop was the wallpaper and default icons.

It was possible to right-click the desktop and open a shell.

I then installed Mate desktop which worked, but was horribly slow.

I determined that the problem was with the nvidia-current driver, and that for the older 6200 adapter I needed to use the legacy nvidia 173 driver.

I couldn't install that due to an unresolvable dependency error. AAARGH!

I downloaded the binary from the nvidia website but that refused to build the kernel modules without providing any useful error feedback. AAAARGH again!

Eventually I found some clues on the 'net suggesting downgrading to the version of X from oneiric repository. 

This is how you do that.

 
Add this repository to your sources list file.

deb http://archive.ubuntu.com/ubuntu/ oneiric main

Edit your apt preferences file;

# vi /etc/apt/preferences


Add a section as follows;

Package: xorg xserver-xorg*
Pin: release a=oneiric
Pin-Priority: 1050


This will instruct your package manager to always use the oneiric repository for xorg and xserver* packages


Update your sources and do an upgrade;

apt-get update && apt-get upgrade

Explicitly install the x server packages along with the nvidia-173 legacy package.
 
sudo apt-get install xorg xserver-xorg-input-all xserver-xorg-video-all nvidia-173 nvidia-settings

Update: If you take a look at which driver you using in the "Additional Drivers" utility it may report that "This driver is activated but not currently in use". This is an error in jockey which is not reporting the driver status properly.