Search This Blog

Sunday, January 4, 2015

mysql cheat sheet

How to grant access any user from one network only to mysql

 
$ mysql -p
(root@localhost@localhost) 03:49:13 [mysql]> grant all on *.* to ''@'192.168.10.%';
$service mysql reload  


Please note the empty user name in the syntax above.
For this changes to be effective I needed to reload the mysql.


Surprisingly these 2 commands don't work at all as much as you would believe. The '*' or the '%' for the user column have no wildcard meaning.
 
(root@localhost@localhost) 03:49:13 [mysql]> grant all on *.* to '%'@'192.168.10.%';
(root@localhost@localhost) 03:49:13 [mysql]> grant all on *.* to '*'@'192.168.10.%';

This is how you can check the current grants (you need to flush the privileges or reload the mysql config as changes issues with 'grant' don't take actions immediately).
 
(root@localhost@localhost) 03:51:30 [mysql]> select * from user;
+---------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+----------
| 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 | Reference
+---------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+----------
| localhost           | root | *2447D497B9A6A15F2776055CB2D1E9F86758182F | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y
| OL63x64.example.com | root | *DCAB8B850144B862687D44957E317DE424E12923 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y
| 127.0.0.1           | root | *DCAB8B850144B862687D44957E317DE424E12923 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y
| ::1                 | root | *DCAB8B850144B862687D44957E317DE424E12923 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y
| 192.168.10.%        |      | *2447D497B9A6A15F2776055CB2D1E9F86758182F | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y
+---------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+----------
5 rows in set (0.00 sec)

or

[root@OL63x64 log]# pt-show-grants --password=rrr
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.6.12-enterprise-commercial-advanced at 2015-01-04 15:55:27
-- Grants for ''@'192.168.10.%'
GRANT ALL PRIVILEGES ON *.* TO ''@'192.168.10.%' IDENTIFIED BY PASSWORD '*2447D497B9A6A15F2776055CB2D1E9F86758182F';
-- Grants for 'root'@'127.0.0.1'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*DCAB8B850144B862687D44957E317DE424E12923' WITH GRANT OPTION;
-- Grants for 'root'@'::1'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' IDENTIFIED BY PASSWORD '*DCAB8B850144B862687D44957E317DE424E12923' WITH GRANT OPTION;
-- Grants for 'root'@'OL63x64.example.com'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'ol63x64.example.com' IDENTIFIED BY PASSWORD '*DCAB8B850144B862687D44957E317DE424E12923' WITH GRANT OPTION;
-- Grants for 'root'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*2447D497B9A6A15F2776055CB2D1E9F86758182F' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

References
http://dev.mysql.com/doc/refman/5.6/en/default-privileges.html

Show mysqld server and mysql client options

server options compiled-in defaults and any option files that it reads:
mysqld --verbose --help

server options (ignoring the settings in any option files):
mysqld --no-defaults --verbose --help

runtime/running server options     : mysqladmin variables

parsed options from config file(s): mysqld --print-defaults
 mysqld would have been started with the following arguments:
--datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --user=mysql --symbolic-links=0

Monday, September 1, 2014

How to automatically rotate the root password on cloud server

Description

I need to create a public cloud server and use it as a bastion in a secure way.
I hate the java/javascript console that you have to use when something doesn't work with your cloud.
I want to keep the root user enabled. As leaving the password authentication for root is a security risk we need to mitigate this.
By default the default loging method is going to be RSA public key.

The reason I want to keep the root user enabled is that you can easely reset its passwors using the https://mycloud.rackspace.com/ portal. Otherwise the root user should be practically not available.

We could leave it enabled but there is always a risk that somebody with enough time may want to try to hack us.

Problem

How to set up a root password rotation using Cron in Linux,

Solution
 
# crontab -l
# for debugging
# */10 *  *  *    *     echo root:$(/usr/bin/makepasswd --chars 15) | /usr/bin/tee /tmp/test.txt | /usr/sbin/chpasswd
  */10 *  *  *    *     echo root:$(/usr/bin/makepasswd --chars 15) | /usr/sbin/chpasswd

This mitigates the root password attacks and still gives us a possibility to reset the root password over the portal and login over a regular ssh session.

We don't care what the new root password is, if I need it I'll reset it on the myrack portal.

Tuesday, May 27, 2014

How to use F5 Wireshark Plugin for LTM troubleshooting

In this post we are going to look how to use F5 Wireshark Plugin to troubleshoot networking issues on BigIP LTM.
  • Download the and install the plugin in your Wireshark
The full instruction are here F5 Wireshark Plugin. In essence you needed to copy the f5ethtrailer.dll file into C:\Program Files (x86)\wireshark\wireshark16\WiresharkPortable\ and restart my Wireshark.

Once you restart wireshark go to menu Help - About Wireshark, Plugins tab. You should be able to see the plugin listed there if properly installed.

  • The plugin is useful only if you take a capture on LTM with 'noise' information.
The noise is an internal information that TMM is attaching and managing for every packet when is being processed. To have a capture with noise these are the minimal options you need to specify:

tcpdump -w /var/tmp/capture.pcap -s0 -i _interface_:nnn

where the _interface_ can be:
    •  1.1 - example of an physical interface
    • dmz_vlan - a name you gave to your vlan when created
    • 0.0 - is the equivalent of 'any' interface what means capture on all interfaces and all vlans
My favourite syntax is usually something like this:

tcpdump -s0 -nn -w /var/tmp/test1-$(date +%s).pcap -i 0.0:nnn '(host _ip_ and port _port_ ) or arp or not ip' 
  • Open the capture in wireshark as normal
Once you open you will noticed that there is additional section in the packet details.

  • The most useful part of using this plugin is that you can quickly and easily find the client and server site traffic in the capture (It can be a challenging when you have multiple tcp streams and OneConnect profile):
    • Find a single packet of the flow you are interested in (search for VIP or client ip for example).
    • Find the "Flow ID" from the F5 Ethernet trailer (see the picture above for example).
    • Click with right mouse taste on the Flow ID field and select "Prepare as Filter".
    • In the Filter box (on top ) it will pre-populate the syntax for you.
    • Copy the hex value and delete the '.flowid == hex' part and start typing '.'  (dot).
    • It will mediately give you a list of possible options, select anyflowid and copy the hex back as it was originally. Example:
The original filter         : f5ethtrailer.flowid == 0x0d2e6dc0
Filter after modifications  : f5ethtrailer.anyflowid == 0x0d2e6dc0
    • Press Apply button
This filter is gong to find the client and server site flows for you. You can then analyse them packet by packet to find out and understand how and why LTM load balance it to one or another pool member.

References

https://devcentral.f5.com/wiki/advdesignconfig.F5WiresharkPlugin.ashx
https://devcentral.f5.com/questions/tcpdump-with-multiple-pool-members
SOL13637: Capturing internal TMM information with tcpdump

Wednesday, May 21, 2014

Simple MySQL and SQL exercises

How to create a sample MySQL data base and user

You can download an example data base sql file from here: http://www.mysqltutorial.org/mysql-sample-database.aspx. After unziping you should find following file:
 
rado2@ubuntu12-04:~$ ls -la mysqlsampledatabase.sql
-rw-rw-r-- 1 rado2 rado2 190711 May 23  2013 mysqlsampledatabase.sql
 
rado2@ubuntu12-04:~$ more mysqlsampledatabase.sql
/*
http://www.mysqltutorial.org
*/

CREATE DATABASE /*!32312 IF NOT EXISTS*/`classicmodels` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `classicmodels`;

/*Table structure for table `customers` */

DROP TABLE IF EXISTS `customers`;

CREATE TABLE `customers` (
  `customerNumber` int(11) NOT NULL,
  `customerName` varchar(50) NOT NULL,
  `contactLastName` varchar(50) NOT NULL,
  `contactFirstName` varchar(50) NOT NULL,
....

We don't want to use a root user to manipulate our data base records. To create a separate user you can run these commands:
 
$ mysql -u root -p
mysql> use information_schema;
mysql> CREATE USER 'rado2'@'localhost';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'rado2'@'localhost';
mysql> select * from USER_PRIVILEGES ;

To import and inspect the database we can use this commands:
 
$ mysql -u rado2 < mysqlsampledatabase.sql

$ mysql -u rado2
mysql> show databases;
mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
+-------------------------+
8 rows in set (0.00 sec)

mysql> select * from employees  LIMIT 5;
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+
| employeeNumber | lastName  | firstName | extension | email                           | officeCode | reportsTo | jobTitle             |
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+
|           1002 | Murphy    | Diane     | x5800     | dmurphy@classicmodelcars.com    | 1          |      NULL | President            |
|           1056 | Patterson | Mary      | x4611     | mpatterso@classicmodelcars.com  | 1          |      1002 | VP Sales             |
|           1076 | Firrelli  | Jeff      | x9273     | jfirrelli@classicmodelcars.com  | 1          |      1002 | VP Marketing         |
|           1088 | Patterson | William   | x4871     | wpatterson@classicmodelcars.com | 6          |      1056 | Sales Manager (APAC) |
|           1102 | Bondur    | Gerard    | x5408     | gbondur@classicmodelcars.com    | 4          |      1056 | Sale Manager (EMEA)  |
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+

mysql> select * from offices  LIMIT 5;
+------------+---------------+-----------------+--------------------------+--------------+------------+---------+------------+-----------+
| officeCode | city          | phone           | addressLine1             | addressLine2 | state      | country | postalCode | territory |
+------------+---------------+-----------------+--------------------------+--------------+------------+---------+------------+-----------+
| 1          | San Francisco | +1 650 219 4782 | 100 Market Street        | Suite 300    | CA         | USA     | 94080      | NA        |
| 2          | Boston        | +1 215 837 0825 | 1550 Court Place         | Suite 102    | MA         | USA     | 02107      | NA        |
| 3          | NYC           | +1 212 555 3000 | 523 East 53rd Street     | apt. 5A      | NY         | USA     | 10022      | NA        |
| 4          | Paris         | +33 14 723 4404 | 43 Rue Jouffroy D'abbans | NULL         | NULL       | France  | 75017      | EMEA      |
| 5          | Tokyo         | +81 33 224 5000 | 4-1 Kioicho              | NULL         | Chiyoda-Ku | Japan   | 102-8578   | Japan     |
+------------+---------------+-----------------+--------------------------+--------------+------------+---------+------------+-----------+

mysql> show COLUMNS FROM employees
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11)      | NO   | PRI | NULL    |       |
| lastName       | varchar(50)  | NO   |     | NULL    |       |
| firstName      | varchar(50)  | NO   |     | NULL    |       |
| extension      | varchar(10)  | NO   |     | NULL    |       |
| email          | varchar(100) | NO   |     | NULL    |       |
| officeCode     | varchar(10)  | NO   | MUL | NULL    |       |
| reportsTo      | int(11)      | YES  | MUL | NULL    |       |
| jobTitle       | varchar(50)  | NO   |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

mysql> show COLUMNS FROM offices ;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| officeCode   | varchar(10) | NO   | PRI | NULL    |       |
| city         | varchar(50) | NO   |     | NULL    |       |
| phone        | varchar(50) | NO   |     | NULL    |       |
| addressLine1 | varchar(50) | NO   |     | NULL    |       |
| addressLine2 | varchar(50) | YES  |     | NULL    |       |
| state        | varchar(50) | YES  |     | NULL    |       |
| country      | varchar(50) | NO   |     | NULL    |       |
| postalCode   | varchar(15) | NO   |     | NULL    |       |
| territory    | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

Exercise 1: select all employees from offices in USA only
 
mysql> SELECT * FROM employees as e, offices as o  where e.officeCode = o.officeCode and o.country='USA';
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+--------------------+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+
| employeeNumber | lastName  | firstName | extension | email                           | officeCode | reportsTo | jobTitle           | officeCode | city          | phone           | addressLine1         | addressLine2 | state | country | postalCode | territory |
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+--------------------+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+
|           1002 | Murphy    | Diane     | x5800     | dmurphy@classicmodelcars.com    | 1          |      NULL | President          | 1          | San Francisco | +1 650 219 4782 | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
|           1056 | Patterson | Mary      | x4611     | mpatterso@classicmodelcars.com  | 1          |      1002 | VP Sales           | 1          | San Francisco | +1 650 219 4782 | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
|           1076 | Firrelli  | Jeff      | x9273     | jfirrelli@classicmodelcars.com  | 1          |      1002 | VP Marketing       | 1          | San Francisco | +1 650 219 4782 | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
|           1143 | Bow       | Anthony   | x5428     | abow@classicmodelcars.com       | 1          |      1056 | Sales Manager (NA) | 1          | San Francisco | +1 650 219 4782 | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
|           1165 | Jennings  | Leslie    | x3291     | ljennings@classicmodelcars.com  | 1          |      1143 | Sales Rep          | 1          | San Francisco | +1 650 219 4782 | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
|           1166 | Thompson  | Leslie    | x4065     | lthompson@classicmodelcars.com  | 1          |      1143 | Sales Rep          | 1          | San Francisco | +1 650 219 4782 | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
|           1188 | Firrelli  | Julie     | x2173     | jfirrelli@classicmodelcars.com  | 2          |      1143 | Sales Rep          | 2          | Boston        | +1 215 837 0825 | 1550 Court Place     | Suite 102    | MA    | USA     | 02107      | NA        |
|           1216 | Patterson | Steve     | x4334     | spatterson@classicmodelcars.com | 2          |      1143 | Sales Rep          | 2          | Boston        | +1 215 837 0825 | 1550 Court Place     | Suite 102    | MA    | USA     | 02107      | NA        |
|           1286 | Tseng     | Foon Yue  | x2248     | ftseng@classicmodelcars.com     | 3          |      1143 | Sales Rep          | 3          | NYC           | +1 212 555 3000 | 523 East 53rd Street | apt. 5A      | NY    | USA     | 10022      | NA        |
|           1323 | Vanauf    | George    | x4102     | gvanauf@classicmodelcars.com    | 3          |      1143 | Sales Rep          | 3          | NYC           | +1 212 555 3000 | 523 East 53rd Street | apt. 5A      | NY    | USA     | 10022      | NA        |
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+--------------------+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+
10 rows in set (0.00 sec)

References

http://www.mysqltutorial.org/mysql-sample-database.aspx
http://en.wikipedia.org/wiki/Join_%28SQL%29
https://answers.yahoo.com/question/index?qid=20080520200936AAmD1Mt
http://www.cyberciti.biz/tips/mysql-auto-completion-for-database-table-names.html

Tuesday, May 20, 2014

What is the difference between XenServer vs Xen vx XCP vs XAPI

It wasn't clear to me at fist what the differences are between XenServer, Xen and XCP. To make it even more confusing the documentation in many place were referring to XAPI and its importance in managing the hypervisors.

To understand what the XAPI is and how it can be used please take a look at this demo I wrote: How to install ipython on XenServer and test XAPI. As we can see the XAPI is an elegant way on top of the hypervisor itself that exposes some more advance API operation to help to control and managed the VM and hypervisor live cycle.

In a very simplistic way you can think of Xen as a 'hypervisor kernel'. The kernel itself may be difficult to use so we need some management software bundled with it.

It is similar comparing Linux kernel and a distribution together. It is hard to use the kernel on its own, we need a more user friendly tools to do this and this is the place where GNU toolchain is coming into play.

Once we understand this it is now easy to understand this FAQ: What's the difference between Xen hypervisor (from xen.org) and Citrix XenServer or XCP?

If you understood what the last link is about please take a look at these for more advance comparisons:
Here is an example showing the differences between the XenServer and Xen management cli:

How to install ipython on XenServer and test XAPI

We've been using the more user friendly shell to interact with python before: ipython. The example below are showing first how to install and enable EPEL repository to be able to install ipython. Next we are going to write a simple XAPI demo program.

Install ipython
  • Find the distro your XenServer is based on
cat /etc/issue.net
CentOS release 5.7 (Final)
Kernel \r on an \m
  • Check enabled repository 
yum repolist
  • From the EPEL install the relevant rpm packets that will add new repository to your yum
# http://fedoraproject.org/wiki/EPEL
# http://www.mirrorservice.org/sites/dl.fedoraproject.org/pub/epel/5/i386/repoview/epel-release.html

rpm --force -i http://www.mirrorservice.org/sites/dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm
  • Update the repo info
yum list 
yum list | grep ipython
  • Install ipython
yum install ipython.noarch
  • Start and verify that ipython is working fine
ipython

In [3]: import sys
In [4]: sys.version
Out[4]: '2.4.3 (#1, Sep 21 2011, 20:06:00) \n[GCC 4.1.2 20080704 (Red Hat 4.1.2-51)]'

Xapi example using ipython

References

XAPI:
http://blogs.citrix.com/2011/05/18/so-what-is-xenserver-xapi/
http://docs.vmd.citrix.com/XenServer/6.2.0/1.0/en_gb/sdk.html#language_bindings-python

Packages:
http://xmodulo.com/2012/05/how-to-install-additional-packages-in.html
http://thomas-cokelaer.info/blog/2012/01/installing-repositories-under-centos-6-2-to-get-ipython-r-and-other-packages/
http://fedoraproject.org/wiki/EPEL#How_can_I_use_these_extra_packages.3F


Monday, May 19, 2014

Create a VM on an isolated network

For experimenting and testing we want to have a VM that is attached to an isolated network.
In the script below in the part 1) :
  • Take a clone of an existing VM
  • Create a new private network
  • Create and attached new interface to our VM
 Next in part 2) we configure statically an IP of 192.168.32.1 on this new interface.

References

http://blogs.citrix.com/2013/03/18/virtual-hypervisor/
https://wiki.debian.org/NetworkConfiguration
http://docs.vmd.citrix.com/XenServer/4.0.1/reference/ch03s02.html