, * MSSQL support by Charles Hand * * Copyright (C) 2000, 2001 Carnegie Mellon University * Portions Copyright (C) 2001 Iowa National Guard * (see the file 'acid_main.php' for license details) * * Purpose: Create the ACID DB schema * */ ?>
acidDBConnect($db_connect_method, $alert_dbname, $alert_host, $alert_port, $alert_user, $alert_password); $tblAcidAG_present = $db->acidTableExists("acid_ag"); $tblAcidAGAlert_present = $db->acidTableExists("acid_ag_alert"); $tblAcidIPCache_present = $db->acidTableExists("acid_ip_cache"); $tblAcidEvent_present = $db->acidTableExists("acid_event"); $index_event_sig_present = $db->acidIndexExists("event", "signature"); $index_event_time_present = $db->acidIndexExists("event", "timestamp"); if ( $debug_mode > 0 ) { echo "\$submit = $submit
table acid_ag present? $tblAcidAG_present
table acid_ag_alert present? $tblAcidAGAlert_present
table acid_ip_cache present? $tblAcidIPCache_present
table acid_event present? $tblAcidEvent_present
index event.signature present? $index_event_sig_present
index event.time present? $index_event_time_present
"; } if ( $submit == "Create ACID AG" ) { if ( !$tblAcidAG_present ) { if ( ($db->DB_type == "mysql") || ($db->DB_type == "mysqlt") || ($db->DB_type == "maxsql") ) { $sql = ' CREATE TABLE acid_ag ( ag_id INT UNSIGNED NOT NULL AUTO_INCREMENT, ag_name VARCHAR(40), ag_desc TEXT, ag_ctime TIMESTAMP, ag_ltime TIMESTAMP, PRIMARY KEY (ag_id), INDEX (ag_id))'; } else if ($db->DB_type == "postgres") { $sql = ' CREATE TABLE acid_ag ( ag_id SERIAL NOT NULL, ag_name TEXT, ag_desc TEXT, ag_ctime TIMESTAMP, ag_ltime TIMESTAMP, PRIMARY KEY (ag_id))'; } else if ($db->DB_type == "mssql") // Microsoft recommends specifying NULL if NULL is permitted // Otherwise it will unpredictably change the nullability. { $sql = ' CREATE TABLE acid_ag ( ag_id NUMERIC(10,0) IDENTITY(1,1) NOT NULL, ag_name VARCHAR(40) NULL, ag_desc TEXT NULL, ag_ctime TIMESTAMP NULL, ag_ltime TIMESTAMP NULL, PRIMARY KEY (ag_id))'; } $db->acidExecute($sql, -1, -1, false); if ( $db->acidErrorMessage() != "" ) ErrorMessage("Unable to CREATE table 'acid_ag': ".$db->acidErrorMessage()); else ErrorMessage("Successfully created 'acid_ag'"); $tblAcidAG_present = $db->acidTableExists("acid_ag"); } if ( !$tblAcidAGAlert_present ) { if ( ($db->DB_type == "mysql") || ($db->DB_type == "mysqlt") || ($db->DB_type == "maxsql") ) { $sql = ' CREATE TABLE acid_ag_alert( ag_id INT UNSIGNED NOT NULL, ag_sid INT UNSIGNED NOT NULL, ag_cid INT UNSIGNED NOT NULL, PRIMARY KEY (ag_id, ag_sid, ag_cid), INDEX (ag_id), INDEX (ag_sid, ag_cid))'; } else if ($db->DB_type == "postgres") { $sql = ' CREATE TABLE acid_ag_alert( ag_id INT8 NOT NULL, ag_sid INT4 NOT NULL, ag_cid INT8 NOT NULL, PRIMARY KEY (ag_id, ag_sid, ag_cid) ); CREATE INDEX acid_ag_alert_aid_idx ON acid_ag_alert (ag_id); CREATE INDEX acid_ag_alert_id_idx ON acid_ag_alert (ag_sid, ag_cid);'; } else if ($db->DB_type == "mssql") // Microsoft recommends specifying NULL if NULL is permitted // Otherwise it will unpredictably change the nullability. { $sql = ' CREATE TABLE acid_ag_alert ( ag_id NUMERIC(10,0) NOT NULL, ag_sid NUMERIC(10,0) NOT NULL, ag_cid NUMERIC(10,0) NOT NULL, PRIMARY KEY (ag_id, ag_sid, ag_cid) )'; } $db->acidExecute($sql, -1, -1, false); if ( $db->acidErrorMessage() != "" ) ErrorMessage("Unable to CREATE table 'acid_ag_alert': ". $db->acidErrorMessage()); else ErrorMessage("Successfully created 'acid_ag_alert'"); $tblAcidAGAlert_present = $db->acidTableExists("acid_ag_alert"); } if ( !$tblAcidIPCache_present ) { if ( ($db->DB_type == "mysql") || ($db->DB_type == "mysqlt") || ($db->DB_type == "maxsql") ) { $sql = ' CREATE TABLE acid_ip_cache( ipc_ip INT UNSIGNED NOT NULL, ipc_fqdn VARCHAR(50), ipc_dns_timestamp TIMESTAMP, ipc_whois TEXT, ipc_whois_timestamp TIMESTAMP, PRIMARY KEY (ipc_ip), INDEX (ipc_ip)) '; } else if ($db->DB_type == "postgres") { $sql = ' CREATE TABLE acid_ip_cache( ipc_ip INT8 NOT NULL, ipc_fqdn TEXT, ipc_dns_timestamp TIMESTAMP, ipc_whois TEXT, ipc_whois_timestamp TIMESTAMP, PRIMARY KEY (ipc_ip) ); '; } else if ($db->DB_type == "mssql") // Microsoft recommends specifying NULL if NULL is permitted // Otherwise it will unpredictably change the nullability. { $sql = ' CREATE TABLE acid_ip_cache ( ipc_ip NUMERIC(10,0) NOT NULL, ipc_fqdn VARCHAR(50) NULL, ipc_dns_timestamp TIMESTAMP NULL, ipc_whois TEXT NULL, ipc_whois_timestamp TIMESTAMP NULL, PRIMARY KEY (ipc_ip) )'; } $db->acidExecute($sql, -1, -1, false); if ( $db->acidErrorMessage() != "" ) ErrorMessage("Unable to CREATE table 'acid_ip_cache': ". $db->acidErrorMessage()); else ErrorMessage("Successfully created 'acid_ip_cache'"); $tblAcidIPCache_present = $db->acidTableExists("acid_ip_cache"); } if ( !$tblAcidEvent_present ) { if ( ($db->DB_type == "mysql") || ($db->DB_type == "mysqlt") || ($db->DB_type == "maxsql") ) { if ( $db->acidGetDBVersion() < 100 ) $sig_ddl = "signature VARCHAR(255) NOT NULL,"; else $sig_ddl = "signature INT UNSIGNED NOT NULL,"; $sql = ' CREATE TABLE acid_event ( sid INT UNSIGNED NOT NULL, cid INT UNSIGNED NOT NULL,'. $sig_ddl.' sig_name VARCHAR(255), sig_class_id INT UNSIGNED, sig_priority INT UNSIGNED, timestamp TIMESTAMP NOT NULL, ip_src INT UNSIGNED, ip_dst INT UNSIGNED, ip_proto INT, layer4_sport INT UNSIGNED, layer4_dport INT UNSIGNED, PRIMARY KEY (sid,cid), INDEX (signature), INDEX (sig_name), INDEX (sig_class_id), INDEX (sig_priority), INDEX (timestamp), INDEX (ip_src), INDEX (ip_dst), INDEX (ip_proto), INDEX (layer4_sport), INDEX (layer4_dport) ) '; } else if ($db->DB_type == "postgres") { if ( $db->acidGetDBVersion() < 100 ) $sig_ddl = "signature TEXT NOT NULL,"; else $sig_ddl = "signature INT8 NOT NULL,"; $sql = ' CREATE TABLE acid_event ( sid INT8 NOT NULL, cid INT8 NOT NULL,'. $sig_ddl.' sig_name TEXT, sig_class_id INT8, sig_priority INT8, timestamp TIMESTAMP NOT NULL, ip_src INT8, ip_dst INT8, ip_proto INT4, layer4_sport INT4, layer4_dport INT4, PRIMARY KEY (sid,cid) ); CREATE INDEX acid_event_signature ON acid_event (signature); CREATE INDEX acid_event_sig_name ON acid_event (sig_name); CREATE INDEX acid_event_sig_class_id ON acid_event (sig_class_id); CREATE INDEX acid_event_sig_priority ON acid_event (sig_priority); CREATE INDEX acid_event_timestamp ON acid_event (timestamp); CREATE INDEX acid_event_ip_src ON acid_event (ip_src); CREATE INDEX acid_event_ip_dst ON acid_event (ip_dst); CREATE INDEX acid_event_ip_proto ON acid_event (ip_proto); CREATE INDEX acid_event_layer4_sport ON acid_event (layer4_sport); CREATE INDEX acid_event_layer4_dport ON acid_event (layer4_dport); '; } else if ($db->DB_type == "mssql") // Microsoft recommends specifying NULL if NULL is permitted // Otherwise it will unpredictably change the nullability. { if ( $db->acidGetDBVersion() < 100 ) $sig_ddl = "signature TEXT NOT NULL,"; else $sig_ddl = "signature NUMERIC(10,0) NOT NULL,"; $sql = ' CREATE TABLE acid_event ( sid NUMERIC(10,0) NOT NULL, cid NUMERIC(10,0) NOT NULL,'. $sig_ddl.' sig_name TEXT NULL, sig_class_id NUMERIC(10,0) NULL, sig_priority NUMERIC(10,0) NULL, timestamp TIMESTAMP NOT NULL, ip_src NUMERIC(10,0) NULL, ip_dst NUMERIC(10,0) NULL, ip_proto NUMERIC(10,0) NULL, layer4_sport NUMERIC(10,0) NULL, layer4_dport NUMERIC(10,0) NULL, PRIMARY KEY (sid,cid) ) '; } $db->acidExecute($sql, -1, -1, false); if ( $db->acidErrorMessage() != "" ) ErrorMessage("Unable to CREATE table 'acid_event': ". $db->acidErrorMessage()); else ErrorMessage("Successfully created 'acid_event'"); $tblAcidEvent_present = $db->acidTableExists("acid_event"); } if ($db->DB_type == "mssql") // Sheesh! If you create the indexes at the same time you create the // tables, you get Attention and a disconnect (no error message). // If you create the indexes here, it works. Go figure. { $sql = ' CREATE INDEX acid_ag_ag_id_idx ON acid_ag (ag_id) CREATE INDEX acid_ag_alert_aid_idx ON acid_ag_alert (ag_id) CREATE INDEX acid_ag_alert_id_idx ON acid_ag_alert (ag_sid, ag_cid) CREATE INDEX acid_event_sig_class_id ON acid_event (sig_class_id) CREATE INDEX acid_event_sig_priority ON acid_event (sig_priority) CREATE INDEX acid_event_timestamp ON acid_event (timestamp) CREATE INDEX acid_event_ip_src ON acid_event (ip_src) CREATE INDEX acid_event_ip_dst ON acid_event (ip_dst) CREATE INDEX acid_event_ip_proto ON acid_event (ip_proto) CREATE INDEX acid_event_layer4_sport ON acid_event (layer4_sport) CREATE INDEX acid_event_layer4_dport ON acid_event (layer4_dport) '; $db->acidExecute($sql, -1, -1, false); if ($db->acidErrorMessage() != "") ErrorMessage("Unable to CREATE MSSQL Acid table indexes : ".$db->acidErrorMessage()); else ErrorMessage("Successfully created MSSQL Acid table indexes"); } echo '

'; } if ( $submit == "Create Indexes" ) { if ( $index_event_sig_present == 0) createDBIndex($db, "event", "signature", "signature_index"); if ( $index_event_time_present == 0) createDBIndex($db, "event", "timestamp", "timestamp_index"); $index_event_sig_present = $db->acidIndexExists($db, "event", "signature"); $index_event_time_present = $db->acidIndexExists($db, "event", "timestamp"); } echo '
Operation Description Status
ACID tables Adds tables to extend the Snort DB to support the ACID functionality '; if ( $tblAcidAG_present && $tblAcidAGAlert_present && $tblAcidIPCache_present && $tblAcidEvent_present ) ErrorMessage(" DONE "); else echo ''; echo '
Search Indexes (Optional) Adds indexes to the Snort DB to optimize the speed of the queries '; if ( ( $index_event_sig_present == 1 ) && ( $index_event_time_present == 1 ) ) ErrorMessage(" DONE "); else if ( ( $index_event_sig_present == 0 ) || ( $index_event_time_present == 0 ) ) echo ''; else ErrorMessage("Unable to assess the indexes on the underlying database"); echo '
'; if ( ($tblAcidAG_present) && ($tblAcidAGAlert_present) && ($tblAcidIPCache_present) && ($tblAcidEvent_present) ) echo '

The underlying Alert DB is configured for usage with ACID.

Additional DB permissions
In order to support Alert purging (the selective ability to permanently delete alerts from the database) and DNS/whois lookup caching, the DB user "'.$alert_user.'" must have the DELETE and UPDATE privilege on the database "'.$alert_dbname.'@'.$alert_host.'"

Goto the Main page to use the application.'; $et->PrintTiming(); echo "\n

\n"; PrintACIDSubFooter(); ?>