09- Databases

Databases- MySQL

• CentOS 6.2 x64 / MySQL 5.1.52
• Windows Server 2008 R2 x64/ MySQL 5.5.22 (x64)
• OpenSuSE 11.3 / MySQL 5.1.46

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.

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.

These notes will carefully follow the MySQL Reference manual, available
at http://dev.mysql.com/doc/refman/5.5/en/ (for 5.5). The manuals for other versions are also available there. References to the manual will be to the manual for version 5.5; these sections may be in slightly different locations for other versions of MySQL.

Installation

CentOS 5.5

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@biers ~]# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]

It can enabled permanently either through the use of chkconfig

[root@biers ~]# 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.

Windows Server 2008

The windows installer for MySQL 5.5.22 (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. Select “Detailed Configuration”. For Server Type, either “Developer Machine” or “Server Machine” are appropriate for our class exercises. In this example, we select “Server Machine”.

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 om 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 multifunctional databases. The InnoDB datafiles will be placed in C:\MySQL Datafiles\.

Given our lab environment, it is unlikely that your database will require more than 20 concurrent connections. The default port for MySQL is TCP 3306; you can open the
firewall at this point in the installation. 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.

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, 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

C:\Users\Sam Vimes>mysql -u root -p

OpenSuSE 11.3

The MySQL community server is already installed but not running on the default OpenSuSE machine from the labshare. To start it, run YaST → System → System Services (Runlevel) and start the service.

The firewall blocks access to the default port (3306); it can be opened in the usual fashion (YasT -> Firewall -> Allowed Services -> MySQL Server -> Add). Connect to your local MySQL server by running mysql as root from a command prompt.

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

or the abbreviation

mysql> \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; we can use the syntax

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

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.27 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> USE DATABASE 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.
• PROXY: Available only in MySQL >= 5.5.7. Allows one user to impersonate another. It is described in the manual, section 6.3.7.
• 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 UNSIGNED DECIMAL(5),
-> 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 admiistrative data for SSL.

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. Notice that the OpenSuSE and the CentOS versions contain a number of accounts. Accounts without a user name allow any user to use that account. Accounts without passwords allow access without passwords. The password data is a hash of the password; different versions of MySQL use different hasing algorithms. See section 5.3.2.3.

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 unecessary, 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 12.4.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 isleofgods.cosc.tu and the database will be on the separate host biers.cosc.tu.

We compile Barnyard2 on the intrusion detection system in the usual fashion. We tell the compiler that we may want to use either MySQL or PostgreSQL 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@isleofgods src]# tar -xzvf ./barnyard2-1.9.tar.gz 
[root@isleofgods src]# cd barnyard2-1.9
[root@isleofgods barnyard2-1.9]# ./configure --with-postgresql --with-mysql 
--with-mysql-libraries=/usr/lib64/mysql/
[root@isleofgods barnyard-0.2.0]# make
[root@isleofgods barnyard-0.2.0]# make install

Setting up the Database

First, log in to the database system as a root user:

[root@biers ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.52 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>

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

Since this is the first time we have used the database, we check to see what users already exist on the system.

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

Database changed
mysql> select user,host, password from user;
+------+---------------+----------+
| user | host          | password |
+------+---------------+----------+
| root | localhost     |          |
| root | biers.cosc.tu |          |
| root | 127.0.0.1     |          |
|      | localhost     |          |
|      | biers.cosc.tu |          |
+------+---------------+----------+
5 rows in set (0.00 sec)

From this, we see that there are five users- three root users and two guest users; moreover not one has a password set. Let’s fix this!

mysql> set password for root@localhost=password('password1!');
Query OK, 0 rows affected (0.00 sec)

mysql> set password for root@biers.cosc.tu=password('password1!');
Query OK, 0 rows affected (0.00 sec)

mysql> set password for root@127.0.0.1=password('password1!');
Query OK, 0 rows affected (0.00 sec)

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

mysql> drop user ''@biers.cosc.tu;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host, password from user;
+------+---------------+-------------------------------------------+
| user | host          | password                                  |
+------+---------------+-------------------------------------------+
| root | localhost     | *0262F498E91CA294A8BA96084EEEDB5F635B23A3 |
| root | biers.cosc.tu | *0262F498E91CA294A8BA96084EEEDB5F635B23A3 |
| root | 127.0.0.1     | *0262F498E91CA294A8BA96084EEEDB5F635B23A3 |
+------+---------------+-------------------------------------------+
3 rows in set (0.00 sec)

Now we have a reasonable default starting point.

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.01 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/src/barnyard2-1.9 on the intrusion detection system. Then copy the file /usr/src/barnyard2-1.9/schemas/create_mysql to the database system- say as /home/vimes/Desktop/create_mysql. Then we can create the table structure by simply running

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

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@isleofgods.cosc.tu identified by 
'password1!';

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

[vimes@isleofgods ~]$ mysql -u snort -h biers.cosc.tu -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.52 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;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
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)

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.

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

[root@isleofgods ~]# cp /usr/src/barnyard2-1.9/etc/barnyard2.conf /etc/snort/

We also need to create a logging directory for Barnyard

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

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

config hostname:	isleofgods
config interface:	eth0

For our first test, use the simplest output module the screen. Note that line 215 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 snort.u2, limit 128

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

[root@isleofgods ~]# 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@isleofgods ~]# barnyard2 -c /etc/snort/barnyard2.conf -d /var/log/snort/ 
-f snort.u2 -w /etc/snort/barnyard2.waldo

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

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:

Waiting for new data
04/08-13:30:41.864234  [**] [1:1000001:0] Snort Alert [1:1000001:0] [**] 
[Classification ID: (null)] [Priority ID: 0] {TCP} 173.194.73.105:80 -> 
192.168.1.70:50623
04/08-13:30:41.864274  [**] [1:1000001:0] Snort Alert [1:1000001:0] [**]
[Classification ID: (null)] [Priority ID: 0] {TCP} 192.168.1.70:50623 -> 
173.194.73.105: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@isleofgods ~]# /etc/init.d/snortd start
Starting snort: Spawning daemon child...
My daemon child 12564 lives...
Daemon parent exiting (0)                          

We know that our snort.conf file tells snort to save the output in files whose names start snort.u2; 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@isleofgods ~]# ls -l /var/log/snort/
total 1496
-rw-------. 1 snort snort 1480173 Apr  8 15:03 alert
-rw-------. 1 snort snort   24172 Apr  8 15:03 pscan
-rw-------. 1 snort snort   14657 Apr  8 15:03 snort.log.1333922191

and so the file is now being called snort.log.

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@isleofgods ~]# ps aux | grep snort
snort    12564  0.0 23.3 609260 238212 ?       Ssl  14:56   0:00 /usr/sbin/snort 
-A full -b -d -D -i eth0 -u snort -g snort -c /etc/snort/snort.conf 
-l /var/log/snort
root     12574  0.0  0.0 103300   852 pts/0    S+   14:57   0:00 grep snort

From here you can play all sorts of fun games- like 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 turns 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@isleofgods ~]# ls -l /var/log/snort/
total 1572
-rw-------. 1 snort snort 1502854 Apr  8 15:12 alert
-rw-------. 1 snort snort   24669 Apr  8 15:11 pscan
-rw-------. 1 snort snort   24998 Apr  8 15:12 snort.log.1333922191
-rw-------. 1 snort snort   37761 Apr  8 15:13 snort.u2.1333923198

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.

Comment out line 215

#output alert_fast: stdout

and add an appropriate database logging line, say at line 311 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=biers.cosc.tu

Start Barnyard2 once again

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

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

[root@isleofgods ~]# mysql -u snort -h biers.cosc.tu -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.52 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;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from event limit 10;
+-----+-----+-----------+---------------------+
| sid | cid | signature | timestamp           |
+-----+-----+-----------+---------------------+
|   1 |   1 |         1 | 2012-04-08 15:31:50 |
|   1 |   2 |         2 | 2012-04-08 15:32:37 |
|   1 |   3 |         2 | 2012-04-08 15:32:37 |
|   1 |   4 |         2 | 2012-04-08 15:32:37 |
|   1 |   5 |         2 | 2012-04-08 15:32:37 |
|   1 |   6 |         2 | 2012-04-08 15:32:37 |
|   1 |   7 |         2 | 2012-04-08 15:32:37 |
|   1 |   8 |         2 | 2012-04-08 15:32:37 |
|   1 |   9 |         2 | 2012-04-08 15:32:37 |
|   1 |  10 |         2 | 2012-04-08 15:32:37 |
+-----+-----+-----------+---------------------+
10 rows in set (0.00 sec)

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 75 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

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

This file 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@isleofgods ~]# chown snort:snort /etc/snort/barnyard2.waldo 

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

[root@isleofgods ~]# cp /usr/src/barnyard2-1.9/rpm/barnyard2 /etc/init.d/
[root@isleofgods ~]# 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@isleofgods ~]# ln -s /usr/local/bin/barnyard2 /usr/sbin/barnyard2

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

[root@isleofgods ~]# cp /usr/src/barnyard2-1.9/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="snort.u2"

# 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=""

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

[root@isleofgods ~]# ln -s /etc/init.d/barnyard2 /etc/rc3.d/S99barnyard2d
[root@isleofgods ~]# ln -s /etc/init.d/barnyard2 /etc/rc5.d/S99barnyard2d
[root@isleofgods ~]# ln -s /etc/init.d/barnyard2 /etc/rc0.d/K99barnyard2d
[root@isleofgods ~]# 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.