334 lines
13 KiB
JavaScript
Executable File
334 lines
13 KiB
JavaScript
Executable File
// Setup Script for CVE Database
|
||
// This creates a fresh database with multi-vendor support built-in
|
||
|
||
const sqlite3 = require('sqlite3').verbose();
|
||
const bcrypt = require('bcryptjs');
|
||
const fs = require('fs');
|
||
const path = require('path');
|
||
|
||
const DB_FILE = './cve_database.db';
|
||
const UPLOADS_DIR = './uploads';
|
||
|
||
// Initialize database with schema
|
||
function initializeDatabase() {
|
||
return new Promise((resolve, reject) => {
|
||
const db = new sqlite3.Database(DB_FILE, (err) => {
|
||
if (err) reject(err);
|
||
});
|
||
|
||
const schema = `
|
||
CREATE TABLE IF NOT EXISTS cves (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
cve_id VARCHAR(20) NOT NULL,
|
||
vendor VARCHAR(100) NOT NULL,
|
||
severity VARCHAR(20) NOT NULL,
|
||
description TEXT,
|
||
published_date DATE,
|
||
status VARCHAR(50) DEFAULT 'Open',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(cve_id, vendor)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS documents (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
cve_id VARCHAR(20) NOT NULL,
|
||
vendor VARCHAR(100) NOT NULL,
|
||
name VARCHAR(255) NOT NULL,
|
||
type VARCHAR(50) NOT NULL,
|
||
file_path VARCHAR(500) NOT NULL,
|
||
file_size VARCHAR(20),
|
||
mime_type VARCHAR(100),
|
||
uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
notes TEXT,
|
||
FOREIGN KEY (cve_id) REFERENCES cves(cve_id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS required_documents (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
vendor VARCHAR(100) NOT NULL,
|
||
document_type VARCHAR(50) NOT NULL,
|
||
is_mandatory BOOLEAN DEFAULT 1,
|
||
description TEXT
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_cve_id ON cves(cve_id);
|
||
CREATE INDEX IF NOT EXISTS idx_vendor ON cves(vendor);
|
||
CREATE INDEX IF NOT EXISTS idx_severity ON cves(severity);
|
||
CREATE INDEX IF NOT EXISTS idx_status ON cves(status);
|
||
CREATE INDEX IF NOT EXISTS idx_doc_cve_id ON documents(cve_id);
|
||
CREATE INDEX IF NOT EXISTS idx_doc_vendor ON documents(vendor);
|
||
CREATE INDEX IF NOT EXISTS idx_doc_type ON documents(type);
|
||
|
||
-- Users table for authentication
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
username VARCHAR(50) UNIQUE NOT NULL,
|
||
email VARCHAR(255) UNIQUE NOT NULL,
|
||
password_hash VARCHAR(255) NOT NULL,
|
||
role VARCHAR(20) NOT NULL DEFAULT 'viewer',
|
||
is_active BOOLEAN DEFAULT 1,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
last_login TIMESTAMP,
|
||
CHECK (role IN ('admin', 'editor', 'viewer'))
|
||
);
|
||
|
||
-- Sessions table for session management
|
||
CREATE TABLE IF NOT EXISTS sessions (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
session_id VARCHAR(255) UNIQUE NOT NULL,
|
||
user_id INTEGER NOT NULL,
|
||
expires_at TIMESTAMP NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_sessions_session_id ON sessions(session_id);
|
||
CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
|
||
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
|
||
|
||
-- Audit log table for tracking user actions
|
||
CREATE TABLE IF NOT EXISTS audit_logs (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
user_id INTEGER,
|
||
username VARCHAR(50) NOT NULL,
|
||
action VARCHAR(50) NOT NULL,
|
||
entity_type VARCHAR(50) NOT NULL,
|
||
entity_id VARCHAR(100),
|
||
details TEXT,
|
||
ip_address VARCHAR(45),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_audit_user_id ON audit_logs(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_audit_action ON audit_logs(action);
|
||
CREATE INDEX IF NOT EXISTS idx_audit_entity_type ON audit_logs(entity_type);
|
||
CREATE INDEX IF NOT EXISTS idx_audit_created_at ON audit_logs(created_at);
|
||
|
||
INSERT OR IGNORE INTO required_documents (vendor, document_type, is_mandatory, description) VALUES
|
||
('Microsoft', 'advisory', 1, 'Official Microsoft Security Advisory'),
|
||
('Microsoft', 'screenshot', 0, 'Proof of patch application'),
|
||
('Cisco', 'advisory', 1, 'Cisco Security Advisory'),
|
||
('Oracle', 'advisory', 1, 'Oracle Security Alert'),
|
||
('VMware', 'advisory', 1, 'VMware Security Advisory'),
|
||
('Adobe', 'advisory', 1, 'Adobe Security Bulletin');
|
||
|
||
CREATE VIEW IF NOT EXISTS cve_document_status AS
|
||
SELECT
|
||
c.id as record_id,
|
||
c.cve_id,
|
||
c.vendor,
|
||
c.severity,
|
||
c.status,
|
||
COUNT(DISTINCT d.id) as total_documents,
|
||
COUNT(DISTINCT CASE WHEN d.type = 'advisory' THEN d.id END) as advisory_count,
|
||
COUNT(DISTINCT CASE WHEN d.type = 'email' THEN d.id END) as email_count,
|
||
COUNT(DISTINCT CASE WHEN d.type = 'screenshot' THEN d.id END) as screenshot_count,
|
||
CASE
|
||
WHEN COUNT(DISTINCT CASE WHEN d.type = 'advisory' THEN d.id END) > 0
|
||
THEN 'Complete'
|
||
ELSE 'Missing Required Docs'
|
||
END as compliance_status
|
||
FROM cves c
|
||
LEFT JOIN documents d ON c.cve_id = d.cve_id AND c.vendor = d.vendor
|
||
GROUP BY c.id, c.cve_id, c.vendor, c.severity, c.status;
|
||
`;
|
||
|
||
db.exec(schema, (err) => {
|
||
if (err) {
|
||
reject(err);
|
||
} else {
|
||
console.log('✓ Database initialized successfully');
|
||
resolve(db);
|
||
}
|
||
});
|
||
});
|
||
}
|
||
|
||
// Create uploads directory structure
|
||
function createUploadsDirectory() {
|
||
if (!fs.existsSync(UPLOADS_DIR)) {
|
||
fs.mkdirSync(UPLOADS_DIR, { recursive: true });
|
||
console.log('✓ Created uploads directory');
|
||
} else {
|
||
console.log('✓ Uploads directory already exists');
|
||
}
|
||
}
|
||
|
||
// Create default admin user
|
||
async function createDefaultAdmin(db) {
|
||
return new Promise((resolve, reject) => {
|
||
// Check if admin already exists
|
||
db.get('SELECT id FROM users WHERE username = ?', ['admin'], async (err, row) => {
|
||
if (err) {
|
||
reject(err);
|
||
return;
|
||
}
|
||
|
||
if (row) {
|
||
console.log('✓ Default admin user already exists');
|
||
resolve();
|
||
return;
|
||
}
|
||
|
||
// Create admin user with password 'admin123'
|
||
const passwordHash = await bcrypt.hash('admin123', 10);
|
||
|
||
db.run(
|
||
`INSERT INTO users (username, email, password_hash, role, is_active)
|
||
VALUES (?, ?, ?, ?, ?)`,
|
||
['admin', 'admin@localhost', passwordHash, 'admin', 1],
|
||
(err) => {
|
||
if (err) {
|
||
reject(err);
|
||
} else {
|
||
console.log('✓ Created default admin user (admin/admin123)');
|
||
resolve();
|
||
}
|
||
}
|
||
);
|
||
});
|
||
});
|
||
}
|
||
|
||
// Add sample CVE data (optional - for testing)
|
||
async function addSampleData(db) {
|
||
console.log('\n📝 Adding sample CVE data for testing...');
|
||
|
||
const sampleCVEs = [
|
||
{
|
||
cve_id: 'CVE-2024-SAMPLE-1',
|
||
vendor: 'Microsoft',
|
||
severity: 'Critical',
|
||
description: 'Sample remote code execution vulnerability',
|
||
published_date: '2024-01-15'
|
||
},
|
||
{
|
||
cve_id: 'CVE-2024-SAMPLE-1',
|
||
vendor: 'Cisco',
|
||
severity: 'High',
|
||
description: 'Sample remote code execution vulnerability',
|
||
published_date: '2024-01-15'
|
||
}
|
||
];
|
||
|
||
for (const cve of sampleCVEs) {
|
||
await new Promise((resolve, reject) => {
|
||
db.run(
|
||
`INSERT OR IGNORE INTO cves (cve_id, vendor, severity, description, published_date)
|
||
VALUES (?, ?, ?, ?, ?)`,
|
||
[cve.cve_id, cve.vendor, cve.severity, cve.description, cve.published_date],
|
||
(err) => {
|
||
if (err) reject(err);
|
||
else {
|
||
console.log(` ✓ Added sample: ${cve.cve_id} / ${cve.vendor}`);
|
||
resolve();
|
||
}
|
||
}
|
||
);
|
||
});
|
||
}
|
||
|
||
console.log('ℹ️ Sample data added - demonstrates multi-vendor support');
|
||
}
|
||
|
||
// Verify database structure
|
||
async function verifySetup(db) {
|
||
return new Promise((resolve) => {
|
||
db.get('SELECT sql FROM sqlite_master WHERE type="table" AND name="cves"', (err, row) => {
|
||
if (err) {
|
||
console.error('Warning: Could not verify setup:', err);
|
||
} else {
|
||
console.log('\n📋 CVEs table structure:');
|
||
console.log(row.sql);
|
||
|
||
// Check if UNIQUE constraint is correct
|
||
if (row.sql.includes('UNIQUE(cve_id, vendor)')) {
|
||
console.log('\n✅ Multi-vendor support: ENABLED');
|
||
} else {
|
||
console.log('\n⚠️ Warning: Multi-vendor constraint may not be set correctly');
|
||
}
|
||
}
|
||
resolve();
|
||
});
|
||
});
|
||
}
|
||
|
||
// Display setup summary
|
||
function displaySummary() {
|
||
console.log('\n╔════════════════════════════════════════════════════════╗');
|
||
console.log('║ CVE DATABASE SETUP COMPLETE! ║');
|
||
console.log('╚════════════════════════════════════════════════════════╝');
|
||
console.log('\n📊 What was created:');
|
||
console.log(' ✓ SQLite database (cve_database.db)');
|
||
console.log(' ✓ Tables: cves, documents, required_documents, users, sessions, audit_logs');
|
||
console.log(' ✓ Multi-vendor support with UNIQUE(cve_id, vendor)');
|
||
console.log(' ✓ Vendor column in documents table');
|
||
console.log(' ✓ User authentication with session-based auth');
|
||
console.log(' ✓ Indexes for fast queries');
|
||
console.log(' ✓ Document compliance view');
|
||
console.log(' ✓ Uploads directory for file storage');
|
||
console.log(' ✓ Default admin user (admin/admin123)');
|
||
console.log('\n📁 File structure will be:');
|
||
console.log(' uploads/');
|
||
console.log(' └── CVE-XXXX-XXXX/');
|
||
console.log(' ├── Vendor1/');
|
||
console.log(' │ ├── advisory.pdf');
|
||
console.log(' │ └── screenshot.png');
|
||
console.log(' └── Vendor2/');
|
||
console.log(' └── advisory.pdf');
|
||
console.log('\n🚀 Next steps:');
|
||
console.log(' 1. Start the backend API:');
|
||
console.log(' → cd backend && node server.js');
|
||
console.log(' 2. Start the frontend:');
|
||
console.log(' → cd frontend && npm start');
|
||
console.log(' 3. Open http://localhost:3000');
|
||
console.log(' 4. Start adding CVEs with multiple vendors!');
|
||
console.log('\n💡 Key Features:');
|
||
console.log(' • Add same CVE-ID with different vendors');
|
||
console.log(' • Each vendor has separate document storage');
|
||
console.log(' • Quick Check shows all vendors for a CVE');
|
||
console.log(' • Document compliance tracking per vendor');
|
||
console.log(' • Required docs: Advisory (mandatory for most vendors)\n');
|
||
}
|
||
|
||
// Main execution
|
||
async function main() {
|
||
console.log('🚀 CVE Database Setup (Multi-Vendor Support)\n');
|
||
console.log('════════════════════════════════════════\n');
|
||
|
||
try {
|
||
// Create uploads directory
|
||
createUploadsDirectory();
|
||
|
||
// Initialize database
|
||
const db = await initializeDatabase();
|
||
|
||
// Create default admin user
|
||
await createDefaultAdmin(db);
|
||
|
||
// Add sample data
|
||
await addSampleData(db);
|
||
|
||
// Verify setup
|
||
await verifySetup(db);
|
||
|
||
// Close database connection
|
||
db.close((err) => {
|
||
if (err) console.error('Error closing database:', err);
|
||
else console.log('\n✓ Database connection closed');
|
||
|
||
// Display summary
|
||
displaySummary();
|
||
});
|
||
|
||
} catch (error) {
|
||
console.error('❌ Setup Error:', error);
|
||
process.exit(1);
|
||
}
|
||
}
|
||
|
||
// Run the setup
|
||
main();
|