Designing for Security

-- Create application user with limited permissions
-- Reference: BookVault security roles
CREATE USER 'bookvault_app'@'%' IDENTIFIED BY 'super_secure_password_2024!';

-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE ON bookvault.customers TO 'bookvault_app'@'%';
GRANT SELECT, INSERT, UPDATE ON bookvault.orders TO 'bookvault_app'@'%';
GRANT SELECT, INSERT, UPDATE ON bookvault.order_items TO 'bookvault_app'@'%';
GRANT SELECT ON bookvault.books TO 'bookvault_app'@'%';
GRANT SELECT ON bookvault.authors TO 'bookvault_app'@'%';
GRANT SELECT ON bookvault.categories TO 'bookvault_app'@'%';

-- Create roles for different access levels
-- Reference: BookVault role hierarchy
CREATE ROLE bookvault_readonly;
CREATE ROLE bookvault_customer_service;
CREATE ROLE bookvault_inventory_manager;

-- Define role permissions
GRANT SELECT ON bookvault.* TO bookvault_readonly;

GRANT SELECT, UPDATE ON bookvault.customers TO bookvault_customer_service;
GRANT SELECT ON bookvault.orders TO bookvault_customer_service;
GRANT SELECT ON bookvault.order_items TO bookvault_customer_service;

GRANT SELECT, INSERT, UPDATE ON bookvault.books TO bookvault_inventory_manager;
GRANT SELECT, INSERT, UPDATE ON bookvault.categories TO bookvault_inventory_manager;

-- Add encrypted email storage to customers table
-- Reference: BookVault customer data protection
ALTER TABLE customers 
ADD COLUMN email_encrypted VARBINARY(255),
ADD COLUMN encryption_key_id INT;

-- Example: Store encrypted email (application handles encryption)
INSERT INTO customers (
    first_name, 
    last_name, 
    email_encrypted, 
    encryption_key_id,
    city, 
    created_at
) VALUES (
    'Sarah', 
    'Johnson', 
    AES_ENCRYPT('sarah.johnson@email.com', 'encryption_key_here'),
    1,
    'Seattle', 
    NOW()
);

-- Create secure user authentication table
-- Reference: BookVault admin user security
CREATE TABLE admin_users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,  -- bcrypt hash
    salt VARCHAR(32) NOT NULL,            -- unique per user
    failed_attempts INT DEFAULT 0,
    locked_until TIMESTAMP NULL,
    last_login TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- VULNERABLE: Direct string concatenation (NEVER DO THIS)
-- Reference: BookVault search vulnerability example
-- SELECT * FROM books WHERE title LIKE '%" + userInput + "%'
-- User enters: %'; DELETE FROM books; --
-- Result: SELECT * FROM books WHERE title LIKE '%'; DELETE FROM books; --%'

-- SECURE: Prepared statement approach
-- Reference: BookVault secure book search
PREPARE search_books FROM 
'SELECT book_id, title, author_id, price 
 FROM books 
 WHERE title LIKE ? 
 AND price BETWEEN ? AND ?
 ORDER BY title';

-- Parameters are treated as data only, never as SQL code
SET @search_term = '%python%';
SET @min_price = 10.00;
SET @max_price = 50.00;

EXECUTE search_books USING @search_term, @min_price, @max_price;

-- Create audit trail table for sensitive operations
-- Reference: BookVault security audit log
CREATE TABLE audit_log (
    audit_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    table_name VARCHAR(50) NOT NULL,
    operation VARCHAR(10) NOT NULL,  -- INSERT, UPDATE, DELETE
    user_id INT,
    username VARCHAR(50),
    old_values JSON,                 -- Before change
    new_values JSON,                 -- After change
    ip_address VARCHAR(45),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_table_time (table_name, timestamp),
    INDEX idx_audit_user_time (username, timestamp)
);

-- Create audit trigger for customer table changes
-- Reference: BookVault customer data audit trail
DELIMITER //
CREATE TRIGGER customers_audit_update
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (
        table_name, 
        operation, 
        user_id,
        old_values,
        new_values,
        username
    ) VALUES (
        'customers',
        'UPDATE',
        NEW.customer_id,
        JSON_OBJECT('email', OLD.email, 'city', OLD.city),
        JSON_OBJECT('email', NEW.email, 'city', NEW.city),
        USER()
    );
END//
DELIMITER ;

-- Configure connection security settings
-- Reference: BookVault database server hardening
SET GLOBAL max_connections = 100;
SET GLOBAL connect_timeout = 10;
SET GLOBAL interactive_timeout = 300;
SET GLOBAL wait_timeout = 300;

-- Require SSL connections for application users
ALTER USER 'bookvault_app'@'%' REQUIRE SSL;

-- Show current connection security status
SHOW VARIABLES LIKE 'have_ssl';
SHOW STATUS LIKE 'Ssl_cipher';