09- MySQL 5.1; Barnyard

Leave a comment Go to comments

Introduction

There are a number of reasonable databases that can be used in a variety of situations. In these notes we will focus our attention on the use of MySQL, and will illustrate its use by configuring our intrusion detection system to use Barnyard to log its results to a database.

These notes assume that you are familiar with databases and SQL in general, and will focus their attention solely on the administration of a MySQL database, with an obvious focus on security.

Our CentOS 6.2 virtual machine comes complete with MySQL 5.1.52 already installed. This version of MySQL is almost trivially exploitable through CVE 2012-2122. It seems that there is a problem in how the service checks authentication, and that there is a small chance that an incorrectly provided password will be authenticated. Thus, simply re-trying the same password over and over will allow access. This has been incorporated into a Metasploit module.

Updating the system is simple, provided it is connected to the Internet:

[root@comporellon ~]# yum update mysql
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
 * base: dist1.800hosting.com
 * extras: mirror.ubiquityservers.com
 * updates: mirror.us.leaseweb.net
Setting up Update Process
Resolving Dependencies
--> Running transaction check
---> Package mysql.x86_64 0:5.1.52-1.el6_0.1 will be updated
--> Processing Dependency: mysql = 5.1.52-1.el6_0.1 for package: 
mysql-server-5.1.52-1.el6_0.1.x86_64
---> Package mysql.x86_64 0:5.1.67-1.el6_3 will be an update
--> Processing Dependency: mysql-libs = 5.1.67-1.el6_3 for package: 
mysql-5.1.67-1.el6_3.x86_64
--> Running transaction check
---> Package mysql-libs.x86_64 0:5.1.52-1.el6_0.1 will be updated
---> Package mysql-libs.x86_64 0:5.1.67-1.el6_3 will be an update
---> Package mysql-server.x86_64 0:5.1.52-1.el6_0.1 will be updated
---> Package mysql-server.x86_64 0:5.1.67-1.el6_3 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================
 Package             Arch          Version               Repository      Size
==============================================================================
Updating:
 mysql               x86_64        5.1.67-1.el6_3        updates        886 k
Updating for dependencies:
 mysql-libs          x86_64        5.1.67-1.el6_3        updates        1.2 M
 mysql-server        x86_64        5.1.67-1.el6_3        updates        8.6 M

Transaction Summary
==============================================================================
Upgrade       3 Package(s)

Total download size: 11 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): mysql-5.1.67-1.el6_3.x86_64.rpm                   | 886 kB     00:00     
(2/3): mysql-libs-5.1.67-1.el6_3.x86_64.rpm              | 1.2 MB     00:01     
(3/3): mysql-server-5.1.67-1.el6_3.x86_64.rpm            | 8.6 MB     00:05     
--------------------------------------------------------------------------------
Total                                           1.5 MB/s |  11 MB     00:07     
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
  Updating   : mysql-libs-5.1.67-1.el6_3.x86_64                           1/6 
  Updating   : mysql-5.1.67-1.el6_3.x86_64                                2/6 
  Updating   : mysql-server-5.1.67-1.el6_3.x86_64                         3/6 
  Cleanup    : mysql-server-5.1.52-1.el6_0.1.x86_64                       4/6 
  Cleanup    : mysql-5.1.52-1.el6_0.1.x86_64                              5/6 
  Cleanup    : mysql-libs-5.1.52-1.el6_0.1.x86_64                         6/6 

Updated:
  mysql.x86_64 0:5.1.67-1.el6_3                                                 

Dependency Updated:
  mysql-libs.x86_64 0:5.1.67-1.el6_3    mysql-server.x86_64 0:5.1.67-1.el6_3   

Complete!

This will update the system to 5.1.67.

We will also demonstrate MySQL on Windows 2008 R2 using version 5.1.68.

These notes will carefully follow the MySQL Reference manual, available
at http://dev.mysql.com/doc/refman/5.1/en/ (for 5.1).

Installation

CentOS 6.2

A fully functional MySQL database is set up on the CentOS machines provided to the class. However, the service is not set to start by default. Enable it in the usual fashion. This can be done at the command line by

[root@comporellon ~]# service mysqld start
Initializing MySQL database:  Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h comporellon.cosc.tu password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

                                                           [  OK  ]
Starting mysqld:  
                                                           [  OK  ]

These notes are only presented when MySQL is started for the first time; you won’t see these messages again, and boy are they important!

The key thing to note is that the default installation is quite insecure. We could make the required changes manually, but fortunately they have provided the script /usr/bin/mysqld_safe to automate the process somewhat. Run it from the command line:

[root@comporellon ~]# /usr/bin/mysql_secure_installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

At this point, MySQL is started, and in a not unreasonable security state. To ensure MySQL starts on boot, make the required changes either via chkconfig

[root@comporellon ~]# chkconfig --level 35 mysqld on

or by navigating System → Administration → Services.

The firewall is set up to block the MySQL port (3306); this port can be opened in the usual fashion (System -> Security Level and Firewall -> Other Ports -> 3306 (TCP).)

Connect to your local MySQL server by running the command mysql from a command prompt as a root user. From a root command prompt, run

[root@comporellon ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.67 Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

The -p switch tells mysql to prompt for a password.

Windows Server 2008

The windows installer for MySQL 5.1.68 (Win 64) is available on the labshare. It should function on all recent windows operating systems. It is simple to install using the “typical” settings. Once it is installed, MySQL will run the MySQL Server Instance Configuration Wizard. In this example, we select "Detailed Configuration".
ConfigurationWizard
For Server Type, either “Developer Machine” or “Server Machine” are appropriate for our class exercises. In this example, we select “Server Machine”.
Wizard2

MySQL allows for the use of different mechanisms for store the data- InnoDB and MyISAM. In general, InnoDB is preferred, as it has transaction support, while MyISAM does not. MyISAM on the other hand, is somewhat faster. MyISAM is the default for MySQL prior to 5.5, with InnoDB the default for 5.5+. In our example, we will set MySQL to use transactional databases.
DBType

The InnoDB datafiles will be placed in C:\ProgramData\MySQL\MySQL Server 5.1\data. Note that the directory C:\ProgramData is a hidden directory. You can make hidden directories visible in Windows Explorer by selecting the Organize button, then Folder and search options. From the View tab, update the radio button to Show hidden files, folders, and drives.
Hidden

Given our lab environment, it is unlikely that your database will require more than 20 concurrent connections, so we can use Decision Support (DSS)/OLAP.

The default port for MySQL is TCP 3306; you can open the firewall at this point in the installation. You also want to keep the default, "Strict Mode"; this will force failures when queries have data in an improper format.
Mode

Use the standard character set.

Set MySQL to start as a windows service. You almost certainly want to include the MySQL bin directory in the Windows path.
PATH

Set the root password for the database. If you want to be able to administer your MySQL system remotely, then you will need to either enable root access from remote machines (and we know this is bad!), or create non-root users with sufficient privileges. Why would you want to create an anonymous account?

Connect to your local MySQL server by either running Start → Programs → MySQL → MySQL Server 5.5 → MySQL Command Line Client, or from a command prompt, run

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\seldon>mysql -u root -p
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.68-community MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

Connecting to MySQL

The primary multipurpose tool to connect to a database is the mysql client. When run without any options this sets:

  • The hostname to be localhost. Note that if this is a Linux client, this means more to MySQL than just the hostname.
  • The user name is your Linux user name (on Linux) or “ODBC” on Windows
  • No password is sent
  • No default database is selected.

To set the host name of the server to which you want to connect use the -h option. You can specify the host by its DNS name, or by its IP address, or by specifying localhost.

To set the user name, use the -u option

To specify that you want to enter a password, pass the -p option. This will cause the client to prompt you for a password. If you want to include the password in the command (which you DO NOT WANT TO DO) you follow the -p option with the password, without spaces. As an example, the command

$ mysql -u user -ppass

specifies the user “user” and the password “pass”.

To specify the default database, simply specify that last. The command

$ mysql -u user -h db.towson.edu -p class

attempts to connect to the MySQL server with hostname db.towson.edu as the user “user” and load the database “class”; the client will prompt the user for the password.

Connections to MySQL can be made in a number of different ways.

  • Via TCP/IP. This is used for most remote connections, and can be used for local connections.
  • Via Unix sockets. Available only locally for Linux hosts.

    If the host name is specified localhost, then by default the connection is via a Unix socket. If you want to connect to localhost via TCP/IP, specify the hostname as 127.0.0.1.

  • Via a named pipe connection; available on windows only.
  • Via a shared memory connection to a local server; available on windows only.

    • The particular method can be specified on the command line via the --protocol={TCP|SOCKET|PIPE|MEMORY} option.

    To see more details about the connection that you have made to your server, you can use the command

    mysql> status
--------------
mysql  Ver 14.14 Distrib 5.1.68, for Win64 (unknown)

Connection id:          7
Current database:       mysql
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.1.68-community MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 20 min 42 sec

Threads: 1  Questions: 29  Slow queries: 0  Opens: 15  Flush tables: 1  
Open tables: 8  Queries per second avg: 0.23
--------------

    You can also use the abbreviation /s.

    See also the MySQL manual, 4.2.2. Connecting to the MySQL Server for more details.

    Users & Privileges

    MySQL uses accounts to determine who can authenticate to the database. Though these accounts may share the same name(s) as accounts in the operating system (e.g. root) the MySQL accounts are unrelated to the operating system level accounts.

    When authenticating a user, MySQL uses three factors:

    • The user name
    • The password
    • The hostname (that is the source of the connection attempt).

    It is better to think of the pair (user name,hostname) as a single object. It is possible to have two different accounts with the same user name, provided that they have different hostnames.

    To create a user, one approach is to use the CREATE USER command (manual, section 13.7.1.1). The command

    mysql> CREATE USER 'bill'@'localhost' IDENTIFIED BY 'pass';

    creates:

    • user name: bill
    • hostname: localhost
    • password: pass

    The command

    mysql> CREATE USER 'bill'@'localhost';

    creates:

    • user name: bill
    • hostname: localhost

    but leaves the account with a blank password.

    If you want to specify that an account can log in from any host then you can replace the host with the wildcard- ‘%’, so

    mysql> CREATE USER 'ted'@'%' IDENTIFIED BY 'pass';

    creates an account that can log in to the server from any host with the password “pass”.

    The wildcard ‘%’ can be used for portions of a hostname, e.g. ted@’%.towson.edu’.

    The existence of wilcards in hostnames can be a source of significant complication. Create the following three users:

    mysql> CREATE USER frodo@'%' IDENTIFIED BY 'pass1';
mysql> CREATE USER frodo@'%.tu' IDENTIFIED BY 'pass2';
mysql> CREATE USER frodo@host.tu IDENTIFIED BY 'pass3';

    If frodo connects from the machine host.tu, then which password is to be provided? After all, the hostname matches all three choices! The process is that the system will sort the hosts that apply to the user in order from most specific to most general, and will use the first (most-specific host) in that list.

    Note that on Linux systems, MySQL stores a logs of executed command in the file .mysql_history. In particular, it will record the create user statements. Either protect this file appropriately, remove it, or suppress logging; see the manual, section 4.5.1.3 for details.

    You can always see what account was used for authentication, you can run the query

    mysql> SELECT CURRENT_USER();

    to obtain that information. See the manual, section 12.14

    To drop a user, use the DROP USER command. To drop the user bill@localhost that we created, we simply run

    mysql> DROP USER 'bill'@'localhost'

    If the hostname is not specified, the hostname is assumed to be ‘%’.

    A user who is logged in will not have their session disrupted if their account is deleted. Once they leave their open session, they will be unable to subsequently log back in. Only users with the global CREATE USER privilege can drop a user. For further details, see the manual, section 13.7.1.2.

    To rename a user, use the RENAME USER command. Suppose that we want to change our user ted@’%’ we created so that they can only log in from the host db.class.tu; we can use the syntax

    mysql> RENAME USER ted TO ted@'db.class.tu'

    Note that if the host is not specified, then the wildcard ‘%’ is assumed. Only users with the global CREATE USER privilege can drop a user. For details, see the manual, section 13.7.1.4.

    To change the password for a user, use the SET PASSWORD command. Suppose that we want to change the password for the account ted@db.class to something more complex- we can simply run the command

    mysql> SET PASSWORD FOR ted@db.class = password('password1!');

    and now the password has the value “password1!”. See the manual, section 13.7.1.6 for more details.

    MySQL provides a number of different privileges that can be assigned to users. To view the list of all such privileges, run the command

    mysql> SHOW PRIVILEGES;

    The precise list of privileges varies between versions of MySQL. See also 13.7.5.28 for the syntax, and 6.2.1 for the list of privileges.

    Some of the important privileges include:

    • ALL: This does not mean "all", but does mean all privileges other than GRANT OPTIONS
    • ALTER: Lets the user rename or change the structure of tables; it requires the INSERT and CREATE privileges. Note that the ALTER privilege should be carefully controlled, as it allows the user to bypass some privilege requirements if the user
      renames the table appropriately.
    • CREATE: Lets the user create new databases.
    • CREATE USER: Enables CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES
    • DELETE: Allows the user to delete rows from a database.
    • DROP: Allows the user to delete a database, table, or view. Since MySQL stores privilege data in the database mysql, a user with DROP privileges on that database can eliminate all stored privileges.
    • FILE: This allows the user to read and write files on the host system with the privileges of the MySQL server. For example, a user with FILE privileges can read the contents of /etc/passwd on the host. This can be bad if the user is connecting to the database remotely and is not meant to have this level of access.

      How to do this?

      $ mysql> CREATE DATABASE test;
$ mysql> USE test;
$ mysql> CREATE TABLE t1(d text);

      Here we create a temporary table to store the results of the file action; the table has one column (named d) of type text.

      $ mysql> LOAD DATA INFILE '/etc/passwd' INTO TABLE t1;
$ mysql> SELECT * FROM t1;

      This will reproduce the contents of the file /etc/passwd. Try this on a remote server!

    • GRANT OPTION: This allows the user to grant and revoke privileges for other users; its use is limited to privileges possessed by the grantor.
    • INSERT: Allows the creation of additional rows in a database.
    • RELOAD: Allows the use of the FLUSH commands, most notably the FLUSH PRIVILEGES statement.
    • SELECT: Allows the user to retrieve rows from a database. This is slightly different than allowing a user to run a SELECT query, as some SELECT queries do not obtain their data from a table; consider the query 
      mysql> SELECT curdate();

      which returns the current date; this can be run by users without SELECT privileges as no rows are returned from any database.

    • SHOW DATABASES: Allows the user to see all databases on the system. Without this, a user will only see the databases for which they have privlieges.
    • UPDATE: Allows rows in a database to be updated.
    • USAGE: Equivalent to no privileges. If you create a new user the only privilege they initially receive is the USAGE privilege, which does not grant them the ability to do anything.

    To view the set of privileges for an account, you can run the command

    mysql> SHOW GRANTS FOR ted@'%.class';

    The syntax for the SHOW GRANTS statement is documented in 13.7.5.22. There is no simple way to list the grants for all users at once.

    Privileges are assigned to a user via the use of the GRANT command. This is a complex command; read the documentation at 13.7.1.3. Any user can use the GRANT command, provided their account has the GRANT_OPTION set. A user can only grant privileges that they themselves possess.

    To assign global privileges, use * as the wildcard for both the database and the table name:

    mysql> GRANT ALL ON *.* TO ted@'%.towson.edu'

    These privileges are stored in the mysql database, in the table mysql.user. If the user does not exist, the GRANT command will create the user. A password can be specified at the same time the GRANT command is used:

    mysql> GRANT ALL on *.* to frodo@'%' IDENTIFIED BY 'onering';

    If the account frodo@’%’ does not exist, it will be created with the password ‘onering’. If the account does exist, then the password will be changed.

    To assign privileges to just a single database, only use the wildcard for the table name.

    To see this process in action, let us first create a database:

    mysql> CREATE DATABASE sales;

    Let us also add some tables:

    mysql> create table customers(
-> id INT NOT NULL AUTO_INCREMENT,
-> first_name VARCHAR(20),
-> last_name VARCHAR(20),
-> street_address VARCHAR(60),
-> city VARCHAR(20),
-> state_abrv CHAR(2),
-> zip DECIMAL(5) UNSIGNED,
-> PRIMARY KEY(id));
    mysql> create table creditcards(
-> id INT NOT NULL AUTO_INCREMENT,
-> number varchar(16),
-> userid INT,
-> PRIMARY KEY(id),
-> KEY(userid),
-> FOREIGN KEY(userid) REFERENCES customers(id));

    Now we can assign SELECT privileges to ted as follows:

    mysql> GRANT SELECT on sales.* TO ted@'%.towson.edu'

    These privileges are stored in the mysql database, in the table mysql.db

    To assign privileges to just a single table within a database, specify both the database and the table:

    mysql> GRANT INSERT,UPDATE on sales.customers
-> TO ted@'%.towson.edu';

    This data is stored in mysql.tables_priv

    To assign privileges to a column in a database, follow the privilege with a list of columns within parentheses:

    mysql> GRANT SELECT (id,userid) ON sales.creditcards
-> TO bill@'%.class';

    If you want to provide a user with the ability to grant privileges, use WITH GRANT OPTION.

    To remove privileges from a user, use the REVOKE command. This has the same general syntax as the GRANT command; see 13.7.1.5 for details. The revoked privilege must be the same as the granted privilege.

    The mysql database

    The default installation of MySQL keeps information about the users and the system in a database called mysql. To select this database, execute the command

    mysql> USE mysql;

    View the set of tables in this database with the command

    mysql> SHOW TABLES;

    The precise collection of tables varies with the version of MySQL.

    The first table of interest to us is the user table; view its structure my running

    mysql> DESCRIBE user;

    The precise structure of the table varies with the version of MySQL. It contains details about user accounts, global privileges, and other per-user administrative data.

    To see all of the users on your system, run the command

    mysql> SELECT user,host,password FROM user;

    The precise collection of default accounts depends on how MySQL was installed. If you did not run the mysql_secure_installation script on your CentOS system, you will see a number of users here.

    The password data is a hash of the password; different versions of MySQL use different hashing algorithms. See the manual, section 6.1.2.4 for details.

    Would it be helpful to know the password hash of some of the commonly used passwords? Try

    mysql> SELECT PASSWORD('whateveryouwant');

    If your system has users that you feel are unnecessary, then they should be deleted using the DROP USER syntax described above. To drop the anonymous user on localhost, you can use the syntax

    mysql> DROP USER ''@localhost;

    Changes in the privileges of users can be made by directly editing the appropriate table in the mysql database. Changes made in this fashion do not take effect immediately; you need to run the command FLUSH PRIVILEGES for these changes to
    take effect. See also the manual, section 13.7.6.3.

    The table mysql.db contains database level privileges.

    The table mysql.tables_priv contains the per-table and the per-column privilege assignments.

    Using MySQL

    If you want to test out your system and set up a database with some sample data, you can go online and use the service provided by GenerateData.com.

    For test data for a more sophisticated database, you may want to try Databene Benerator. That is a stand alone application licensed under the GPL that can help you build test data for more complex databases, like the one in their tutorial.

    MySQL and Snort

    We can use MySQL as a backend to store the results from our intrusion detection system. To do so, we first need to configure snort to use the database, and for that we will set up a helper application, called Barnyard2. This tool lets snort use a faster output form (unified). Barnyard2 reads this form, and handles the process of sending it to the database, thus allowing snort to focus on capturing and analyzing traffic.

    Installing Barnyard2

    Grab a copy of Barnyard2, either online or from the labshare. Be sure that you don’t accidentally grab the much older Barnyard project from Sourceforge. That project still appears highly Google ranked, but it is not what you want.

    Take the Barnyard2 package to your intrusion detection system. The database that will store the results can run either on the intrusion detection system or on a separate host. In this example, the intrusion detection system is named tazenda.cosc.tu and the database will be on the separate host comporellon.cosc.tu.

    We compile Barnyard2 on the intrusion detection system in the usual fashion. We tell the compiler that we want to use MySQL as a back end database. Because the compiler gets a bit lost, we are careful to tell it where the MySQL libraries ended up being placed.

    [root@tazenda src]#  unzip /usr/local/src/barnyard2-master.zip  
[root@tazenda barnyard2-master]# cd barnyard2-master
[root@tazenda barnyard2-master]# ./autogen.sh 
[root@tazenda barnyard2-master]# ./configure --with-mysql 
--with-mysql-libraries=/usr/lib64/mysql/
[root@tazenda barnyard2-master]# make
[root@tazenda barnyard2-master]# make install
    Setting up the Database

    Leave the intrusion detection system (tazenda) for now, and log in to the database system (comporellon) as a root user:

    [root@comporellon ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.67 Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

    In this example, we are using the CentOS 6.2 x64 system; the default state of the other systems is slightly different.

    Next we create the database that will store the data, and begin to use that database.

    mysql> create database snort;
Query OK, 1 row affected (0.00 sec)

mysql> use snort;
Database changed

    Now we need to set up the tables that will receive the snort data. Suppose that the the Barnyard2 package was extracted to /usr/local/src/barnyard2-master on the intrusion detection system. Then copy the file /usr/local/src/barnyard2-master/schemas/create_mysql to the database system- say as /home/seldon/Desktop/create_mysql. Then we can create the table structure by simply running

    mysql> source /home/seldon/Desktop/create_mysql;

    Note that the database is now populated with an array of tables:

    mysql> show tables;
+------------------+
| Tables_in_snort  |
+------------------+
| data             |
| detail           |
| encoding         |
| event            |
| icmphdr          |
| iphdr            |
| opt              |
| reference        |
| reference_system |
| schema           |
| sensor           |
| sig_class        |
| sig_reference    |
| signature        |
| tcphdr           |
| udphdr           |
+------------------+
16 rows in set (0.00 sec)

    With the table structure created, we now create a user that will be used solely to interact with the snort database.

    mysql> GRANT ALL ON snort.* to snort@tazenda.cosc.tu IDENTIFIED BY 'password1!';
Query OK, 0 rows affected (0.00 sec)

    You should verify that all works as it ought to by going to the intrusion detection system (tazenda.cosc.tu) and connecting to the database (comporellon.cosc.tu) as the snort user: 

    [seldon@tazenda ~]$ mysql -u snort -h comporellon.cosc.tu -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.67 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> use snort;

    Be sure that you have set your firewall permissions correctly!

    Configuring Barnyard2

    Now that Barnyard2 has been installed and a database set up to receive the results, we can configure Barnyard2. Head back to the intrusion detection system (tazenda).

    Start by copying the configuration file barnyard.conf from the source tree to /etc/snort

    [root@tazenda ~]# cp /usr/local/src/barnyard2-master/etc/barnyard2.conf 
/etc/snort/

    We also need to create a logging directory for Barnyard

    [root@tazenda ~]# mkdir /var/log/barnyard2

    For our first test of the system, modify /etc/barnyard2.conf to correctly set the hostname and interface in lines 63-64; be sure to uncomment them!

    config hostname:   tazenda
config interface:  eth0

    For our first test, use the simplest output module the screen. Note that line 220 reads 

    output alert_fast: stdout

    This will do nicely- alerts will be displayed on the screen.

    Modify the file /etc/snort.conf to use the unified2 output; this can be done simply in Step 6 of that file through the directive

    output unified2: filename merged.log, limit 128

    Then stop snort (if it has already been started) and start it again, from the command line 

    [root@tazenda ~]# service snortd status
snort (pid 23843) is running...
[root@tazenda ~]# service snortd stop
Stopping snort:                                            [  OK  ]
[root@tazenda ~]# snort -c /etc/snort/snort.conf

    [At this point, do not use your startup script /etc/init.d/snort]

    From a second command prompt, start Barnyard2

    [root@tazenda ~]# barnyard2 -c /etc/snort/barnyard2.conf -d /var/log/snort 
-f merged.log -w /etc/snort/barnyard2.waldo 
Running in Continuous mode

        --== Initializing Barnyard2 ==--
Initializing Input Plugins!
Initializing Output Plugins!
Parsing config file "/etc/snort/barnyard2.conf"
Barnyard2 spooler: Event cache size set to [2048] 
Log directory = /var/log/barnyard2

        --== Initialization Complete ==--

  ______   -*> Barnyard2 <*-
 / ,,_  \  Version 2.1.12 (Build 321)
 |o"  )~|  By Ian Firns (SecurixLive): http://www.securixlive.com/
 + '''' +  (C) Copyright 2008-2013 Ian Firns 

Using waldo file '/etc/snort/barnyard2.waldo':
    spool directory = /var/log/snort
    spool filebase  = merged.log
    time_stamp      = 1365377367
    record_idx      = 5828
Opened spool file '/var/log/snort/merged.log.1365377692'
Waiting for new data

    Note that the file names "merged.log" in both the snort.conf filename and the -f variable in the Barnyard2 start command must match.

    You don’t need to manually create the waldo file; it will be created automatically if it does not exist.

    If you still have your web testing rule that fires on any traffic to or from port 80, then a visit to a web page like google.com will then result in Barnyard2 displaying any number of alerts in the general form:

    04/07-16:35:11.269480  [**] [1:1000001:0] Snort Alert [1:1000001:0] [**]
[Classification ID: 0] [Priority ID: 0] {TCP} 10.0.2.18:38576 -> 
74.125.226.196:80
04/07-16:35:11.270922  [**] [1:1000001:0] Snort Alert [1:1000001:0] [**] 
[Classification ID: 0] [Priority ID: 0] {TCP} 74.125.226.196:80 -> 
10.0.2.18:38576
04/07-16:35:11.286636  [**] [1:1000001:0] Snort Alert [1:1000001:0] [**] 
[Classification ID: 0] [Priority ID: 0] {TCP} 74.125.226.196:80 -> 
10.0.2.18:38576
04/07-16:35:11.286719  [**] [1:1000001:0] Snort Alert [1:1000001:0] [**] 
[Classification ID: 0] [Priority ID: 0] {TCP} 10.0.2.18:38576 -> 
74.125.226.196:80
    Updating snort.conf

    Now we would like barnyard to work happily with our startup script. You would think that this is straightforward. Perhaps it ought to be, but life is more complex.

    Run the script /etc/init.d/snortd start as we have built it over the past two weeks

    [root@tazenda ~]# service snortd start
Starting snort: Spawning daemon child...
My daemon child 7728 lives...
Daemon parent exiting (0)
                                                           [  OK  ]

    We know that our snort.conf file tells snort to save the output in files whose names start merged.log; we have seen this and double checked it moments ago when Barnyard2 happily read those files.

    But…. (you knew there was going to be a but)

    This no longer appears to be the case. Indeed, taking a look at the directory /var/log/snort we find

    [root@tazenda ~]# ls -l /var/log/snort/
total 0
-rw-r--r--. 1 root  root  0 Apr  7 16:40 alert
-rw-------. 1 snort snort 0 Apr  7 16:40 pscan
-rw-------. 1 snort snort 0 Apr  7 16:40 snort.log.1365378053

    and so the file is now being called snort.log. [I emptied out the directory before restarting snort- that is why there are no old files here, and all of the files are empty.]

    To see what might be the cause, let’s use ps to determine the precise command that the script is running. Doing do, you find

    [root@tazenda snort]# ps aux | grep snort
snort     7774  0.0 21.8 599692 223288 ?    Ssl  16:40   0:00 /usr/sbin/snort 
-A fast -b -d -D -i eth0 -u snort -g snort -c /etc/snort/snort.conf 
-l /var/log/snort
root      7785  0.0  0.0 103300   852 pts/  S+   16:41   0:00 grep snort

    From here you can play all sorts of fun games- like guess which command line switch is the one that causes all of the problems. [Hah! There are two!]

    If you include the switch -A full, snort appears to change the file name it uses for its output. The -A switch determines the alert mode, and can be set to full, fast, or none Interestingly, I found that no matter which of those choices you make, the name of the output file changes to snort.log. We can handle this problem by commenting out line 69 in /etc/sysconfig/snort so that portion of the file reads

    # How should Snort alert? Valid alert modes include fast, full, none, and
# unsock.  Fast writes alerts to the default "alert" file in a single-line,
# syslog style alert message.  Full writes the alert to the "alert" file
# with the full decoded header as well as the alert message.  None tu#rns off
# alerting. Unsock is an experimental mode that sends the alert information
# out over a UNIX socket to another process that attaches to that socket.
# -A {alert-mode}
# output alert_{type}: {options}
#ALERTMODE=full

    This almost solves the problem. We also cannot use the -b switch to specify tcpdump format for the logs. Modify line 81 of /etc/sysconfig/snort so that portion becomes

    # Should Snort keep binary (AKA pcap, AKA tcpdump) logs also? This is
# recommended as it provides very useful information for investigations.
# -b
# output log_tcpdump: {log name}
BINARY_LOG=0

    If you make these changes to /etc/sysconfig/snort then restart it, it will now correctly send its results to the files /var/log/snort/snort.u2

    [root@tazenda snort]# ls -l
total 1544
-rw-r--r--. 1 root  root  125606 Apr  7 16:45 alert
-rw-------. 1 snort snort 730524 Apr  7 16:47 merged.log.1365378447
-rw-------. 1 snort snort    984 Apr  7 16:47 pscan
-rw-------. 1 snort snort 122816 Apr  7 16:44 snort.log.1365378256
-rw-------. 1 root  root  590121 Apr  7 16:45 snort.log.1365378307
    Completing the Barnyard2 Configuration

    Now that our scripted snort is working with a command line Barnyard2 that is dropping data to the screen, let’s up the ante a bit and get Barnyard2 to log to our database.

    Head back to the /etc/snort/barnyard2.conf file, and comment out line 220

    #output alert_fast: stdout

    Instead, let’s add an appropriate database logging line, say at line 347 of the form

    # database: log to a variety of databases
# ---------------------------------------
#
# Purpose: This output module provides logging ability to a variety of databases
# See doc/README.database for additional information.
#
output database: log, mysql, user=snort password=password1! dbname=snort 
host=comporellon.cosc.tu

    Start Barnyard2 once again

    [root@tazenda ~]# barnyard2 -c /etc/snort/barnyard2.conf -d /var/log/snort 
-f merged.log -w /etc/snort/barnyard2.waldo

    and verify that you are now receiving data in your database:

    [root@tazenda snort]# mysql -u snort -h comporellon.cosc.tu -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.67 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> use snort;
Database changed
mysql> select * from event limit 10;
Empty set (0.00 sec)

mysql> select * from event limit 10;
+-----+-----+-----------+---------------------+
| sid | cid | signature | timestamp           |
+-----+-----+-----------+---------------------+
|   1 |   1 |       476 | 2013-04-07 16:57:24 |
|   1 |   2 |       476 | 2013-04-07 16:57:24 |
|   1 |   3 |       476 | 2013-04-07 16:57:24 |
|   1 |   4 |       476 | 2013-04-07 16:57:24 |
|   1 |   5 |       476 | 2013-04-07 16:57:26 |
|   1 |   6 |       476 | 2013-04-07 16:57:26 |
|   1 |   7 |       476 | 2013-04-07 16:57:26 |
|   1 |   8 |       476 | 2013-04-07 16:57:26 |
|   1 |   9 |       476 | 2013-04-07 16:57:26 |
|   1 |  10 |       476 | 2013-04-07 16:57:26 |
+-----+-----+-----------+---------------------+
10 rows in set (0.00 sec)

mysql>

    Success!

    Starting Barnyard Automatically

    To complete the installation, we need Barnyard2 to start automatically. To do so, Barnyard2 should run as a daemon, so uncomment line 78 of the /etc/snort/barnyard2.conf file

    # enable daemon mode
#
config daemon

    Next, update the barnyard2.conf file with the full location of the waldo file; modify line 134 to read

    # define the full waldo filepath.
#
config waldo_file: /etc/snort/barnyard2.waldo

    The waldo file (where is he anyway?) lets Barnyard2 track how far it has progressed through the various output file created by snort. We specified this precise location in the command line we have used in testing.

    We do not want Barnyard2 running as root; instead we tell Barnyard2 to run as the user (and group) snort by modifying lines 81-87.

    # specifiy the group or GID for barnyard2 to run as after initialisation.
#
config set_gid: snort

# specifiy the user or UID for barnyard2 to run as after initialisation.
#
config set_uid: snort

    Since we want Barnyard2 to run as the user snort, we change the permissions on our waldo file:

    [root@tazenda snort]# chown snort:snort /etc/snort/barnyard2.waldo

    Remember- it was automatically created the first time we ran Barnyard. Since we ran it as root that first time, it was created with root permissions, so we would not be able to use it as snort.

    Copy the startup script from the installation directory to /etc/init.d and make it executable

    [root@tazenda ~]# cp /usr/local/src/barnyard2-master/rpm/barnyard2 /etc/init.d/
[root@tazenda ~]# chmod a+x /etc/init.d/barnyard2

    We need to make a few modifications to the file though. We do not need to specify the location of ARCHIVEDIR, so line 37 can be removed.

    The location of the WALDO_FILE in line 38 should be changed. In our setup, files are not indexed by the interface name, so we do not want to include $INT in the path name; we also have stored the waldo file in /etc/snort rather than in $SNORTDIR; thus these lines should become the single line

    WALDO_FILE="/etc/snort/barnyard2.waldo"

    We also need to remove the dependencies on the interface in the BARNYARD_OPTS line; it should become

    BARNYARD_OPTS="-D -c $CONF -d $SNORTDIR -w $WALDO_FILE -f $LOG_FILE 
-X $PIDFILE $EXTRA_ARGS"

    Combining these changes, we end up with a start() routine in the form

    start() {
	echo -n $"Starting $desc ($prog): "
	for INT in $INTERFACES; do
		PIDFILE="/var/lock/subsys/barnyard2-$INT.pid"
		WALDO_FILE="/etc/snort/barnyard2.waldo"
		BARNYARD_OPTS="-D -c $CONF -d $SNORTDIR -w $WALDO_FILE 
                         -f $LOG_FILE -X $PIDFILE $EXTRA_ARGS"
		daemon $prog $BARNYARD_OPTS
	done
	RETVAL=$?
	echo
	[ $RETVAL -eq 0 ] && touch /var/lock/subsys/$prog
	return $RETVAL
}

    We also put a link to the binary in /usr/sbin

    [root@tazenda ~]# ln -s /usr/local/bin/barnyard2 /usr/sbin/barnyard2

    Copy the configuration file from the installation directory to /etc/sysconfig

    [root@tazenda ~]# cp /usr/local/src/barnyard2-master/rpm/barnyard2.config 
/etc/sysconfig/barnyard2

    We need to make a few changes to this file as well; when complete it should look like

    # Config file for /etc/init.d/barnyard2
LOG_FILE="merged.log"

# You probably don't want to change this, but in case you do
SNORTDIR="/var/log/snort"
INTERFACES="eth0"

# Probably not this either
CONF=/etc/snort/barnyard2.conf

EXTRA_ARGS=""

    In case you are wondering what got changed- both the LOG_FILE variable as well as the CONF variables.

    Finally, we set up our start-up and shutdown scripts:

    [root@tazenda ~]# ln -s /etc/init.d/barnyard2 /etc/rc3.d/S99barnyard2d
[root@tazenda ~]# ln -s /etc/init.d/barnyard2 /etc/rc5.d/S99barnyard2d
[root@tazenda ~]# ln -s /etc/init.d/barnyard2 /etc/rc0.d/K99barnyard2d
[root@tazenda ~]# ln -s /etc/init.d/barnyard2 /etc/rc6.d/K99barnyard2d

    This completes the installation. You can verify that it works by simply rebooting the box and checking that both snort and barnyard2 run correctly.

    Now what?

    All of this simply gets our snort data into the database. The trick later, will be to get it out in a useful fashion. For that we will use some web applications- and those we will cover beginning next week.

  1. No comments yet.
  1. No trackbacks yet.

Leave a comment