Database Security: Keamanan Database 🔒

-- PostgreSQL
CREATE USER app_user WITH 
    PASSWORD 'secure_password123'
    VALID UNTIL '2025-12-31';

-- MySQL
CREATE USER 'app_user'@'localhost' 
IDENTIFIED BY 'secure_password123'
PASSWORD EXPIRE INTERVAL 90 DAY;

-- SQL Server
CREATE LOGIN app_user 
WITH PASSWORD = 'secure_password123',
CHECK_EXPIRATION = ON,
CHECK_POLICY = ON;

CREATE USER app_user FOR LOGIN app_user;

-- PostgreSQL
ALTER USER app_user WITH 
    PASSWORD 'new_password123'
    CONNECTION LIMIT 5;

-- MySQL
ALTER USER 'app_user'@'localhost'
IDENTIFIED BY 'new_password123'
WITH MAX_CONNECTIONS_PER_HOUR 100;

-- SQL Server
ALTER LOGIN app_user 
WITH PASSWORD = 'new_password123',
DEFAULT_DATABASE = sales_db;

-- PostgreSQL
DROP USER IF EXISTS app_user;

-- MySQL
DROP USER IF EXISTS 'app_user'@'localhost';

-- SQL Server
DROP LOGIN app_user;
DROP USER app_user;

-- PostgreSQL
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin;

-- MySQL
CREATE ROLE 'readonly', 'readwrite', 'admin';

-- SQL Server
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin;

-- PostgreSQL
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;

-- MySQL
GRANT SELECT ON sales_db.* TO 'readonly';
GRANT SELECT, INSERT, UPDATE ON sales_db.* TO 'readwrite';
GRANT ALL PRIVILEGES ON sales_db.* TO 'admin';

-- SQL Server
GRANT SELECT ON SCHEMA::dbo TO readonly;
GRANT SELECT, INSERT, UPDATE ON SCHEMA::dbo TO readwrite;
GRANT CONTROL ON SCHEMA::dbo TO admin;

-- PostgreSQL
GRANT readonly TO analyst_user;
GRANT readwrite TO app_user;
GRANT admin TO dba_user;

-- MySQL
GRANT 'readonly' TO 'analyst_user'@'localhost';
GRANT 'readwrite' TO 'app_user'@'localhost';
GRANT 'admin' TO 'dba_user'@'localhost';

-- SQL Server
ALTER ROLE readonly ADD MEMBER analyst_user;
ALTER ROLE readwrite ADD MEMBER app_user;
ALTER ROLE admin ADD MEMBER dba_user;

-- PostgreSQL
GRANT SELECT ON orders TO app_user;
GRANT INSERT, UPDATE ON products TO app_user;

-- MySQL
GRANT SELECT ON sales_db.orders TO 'app_user'@'localhost';
GRANT INSERT, UPDATE ON sales_db.products TO 'app_user'@'localhost';

-- SQL Server
GRANT SELECT ON orders TO app_user;
GRANT INSERT, UPDATE ON products TO app_user;

-- PostgreSQL
REVOKE ALL ON orders FROM public;
REVOKE INSERT ON products FROM app_user;

-- MySQL
REVOKE ALL PRIVILEGES ON sales_db.orders FROM 'app_user'@'localhost';
REVOKE INSERT ON sales_db.products FROM 'app_user'@'localhost';

-- SQL Server
REVOKE ALL ON orders FROM public;
REVOKE INSERT ON products FROM app_user;

-- PostgreSQL
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY order_access_policy ON orders
    FOR ALL
    TO public
    USING (user_id = CURRENT_USER::integer);

-- MySQL (using views as alternative)
CREATE VIEW user_orders AS
SELECT * FROM orders
WHERE user_id = @current_user_id;

-- SQL Server
CREATE SECURITY POLICY OrderFilter
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(user_id) 
ON dbo.orders;

-- PostgreSQL
CREATE POLICY dept_access_policy ON employees
    FOR SELECT
    USING (department = (
        SELECT department 
        FROM user_departments 
        WHERE username = CURRENT_USER
    ));

-- MySQL (using views)
CREATE VIEW dept_employees AS
SELECT e.* 
FROM employees e
JOIN user_departments ud 
    ON e.department = ud.department
WHERE ud.username = USER();

-- SQL Server
CREATE SECURITY POLICY DeptFilter
ADD FILTER PREDICATE dbo.fn_DeptPredicate(department) 
ON dbo.employees;

-- PostgreSQL
GRANT SELECT (id, name, price) ON products TO app_user;
REVOKE SELECT (cost_price) ON products FROM app_user;

-- MySQL
GRANT SELECT (id, name, price) ON sales_db.products TO 'app_user'@'localhost';
REVOKE SELECT (cost_price) ON sales_db.products FROM 'app_user'@'localhost';

-- SQL Server
GRANT SELECT ON products(id, name, price) TO app_user;
DENY SELECT ON products(cost_price) TO app_user;

-- PostgreSQL
CREATE VIEW public_products AS
SELECT id, name, price
FROM products;

GRANT SELECT ON public_products TO app_user;

-- MySQL
CREATE VIEW public_products AS
SELECT id, name, price
FROM products;

GRANT SELECT ON sales_db.public_products TO 'app_user'@'localhost';

-- SQL Server
CREATE VIEW public_products AS
SELECT id, name, price
FROM products;

GRANT SELECT ON public_products TO app_user;

-- PostgreSQL (using pgcrypto)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

UPDATE users 
SET credit_card = pgp_sym_encrypt(
    credit_card, 
    'encryption_key'
);

-- MySQL
UPDATE users 
SET credit_card = AES_ENCRYPT(
    credit_card, 
    'encryption_key'
);

-- SQL Server
UPDATE users 
SET credit_card = EncryptByKey(
    Key_GUID('CreditCardKey'),
    credit_card
);

-- PostgreSQL (Enterprise only)
-- Not available in community edition

-- MySQL Enterprise
ALTER INSTANCE ROTATE INNODB MASTER KEY;

-- SQL Server
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE SSLCert;

ALTER DATABASE sales_db
SET ENCRYPTION ON;

-- PostgreSQL
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name TEXT,
    action TEXT,
    old_data JSONB,
    new_data JSONB,
    user_name TEXT DEFAULT CURRENT_USER,
    action_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- MySQL
CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100),
    action VARCHAR(10),
    old_data JSON,
    new_data JSON,
    user_name VARCHAR(100) DEFAULT USER(),
    action_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- SQL Server
CREATE TABLE audit_log (
    id INT IDENTITY(1,1) PRIMARY KEY,
    table_name NVARCHAR(100),
    action NVARCHAR(10),
    old_data NVARCHAR(MAX),
    new_data NVARCHAR(MAX),
    user_name NVARCHAR(100) DEFAULT SYSTEM_USER,
    action_timestamp DATETIME2 DEFAULT SYSDATETIME()
);

-- PostgreSQL
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, action, new_data)
        VALUES (TG_TABLE_NAME, 'INSERT', row_to_json(NEW));
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, action, old_data, new_data)
        VALUES (TG_TABLE_NAME, 'UPDATE', row_to_json(OLD), row_to_json(NEW));
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, action, old_data)
        VALUES (TG_TABLE_NAME, 'DELETE', row_to_json(OLD));
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER products_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION audit_trigger();

-- MySQL
DELIMITER //
CREATE TRIGGER products_audit
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, action, new_data)
    VALUES ('products', 'INSERT', JSON_OBJECT(
        'id', NEW.id,
        'name', NEW.name,
        'price', NEW.price
    ));
END //
DELIMITER ;

-- SQL Server
CREATE TRIGGER products_audit
ON products
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @action NVARCHAR(10)
    
    IF EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED)
        SET @action = 'UPDATE'
    ELSE IF EXISTS (SELECT * FROM INSERTED)
        SET @action = 'INSERT'
    ELSE
        SET @action = 'DELETE'
        
    INSERT INTO audit_log (table_name, action, old_data, new_data)
    SELECT 
        'products',
        @action,
        (SELECT * FROM DELETED FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        (SELECT * FROM INSERTED FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
END;