Apache2 DBD Cheat Sheet

MySQL or MariaDB

-- Apache User
CREATE TABLE apache_user(
    created_at DATETIME,
    created_by VARCHAR(4),
    updated_at DATETIME,
    updated_by VARCHAR(4),
    deleted_at DATETIME,
    deleted_by VARCHAR(4),
    version_no INT NOT NULL,
    id VARCHAR(4) NOT NULL,
    username VARCHAR(32) NOT NULL,
    password VARCHAR(64) NOT NULL,
    is_unlocked INT(1) NOT NULL,
    is_signed_in INT(1),
    is_activated INT(1) NOT NULL,
    user_expired_at DATETIME,
    pass_expired_at DATETIME
) ENGINE = InnoDB COMMENT = 'Apache User';

-- Apache User Group
CREATE TABLE apache_group(
    created_at DATETIME,
    created_by VARCHAR(4),
    updated_at DATETIME,
    updated_by VARCHAR(4),
    deleted_at DATETIME,
    deleted_by VARCHAR(4),
    version_no INT NOT NULL,
    id VARCHAR(4) NOT NULL,
    name VARCHAR(64) NOT NULL,
    is_activated INT(1) NOT NULL,
    expired_at DATETIME
) ENGINE = InnoDB COMMENT = 'Apache Group';

-- Apache User Group Relation
CREATE TABLE apache_user_group(
    created_at DATETIME,
    created_by VARCHAR(4),
    updated_at DATETIME,
    updated_by VARCHAR(4),
    deleted_at DATETIME,
    deleted_by VARCHAR(4),
    version_no INT NOT NULL,
    id VARCHAR(6) NOT NULL,
    user_id VARCHAR(4) NOT NULL,
    group_id VARCHAR(4) NOT NULL
) ENGINE = InnoDB COMMENT = 'Apache User Group';

-- Apache User Realm
CREATE TABLE apache_realm(
    created_at DATETIME,
    created_by VARCHAR(4),
    updated_at DATETIME,
    updated_by VARCHAR(4),
    deleted_at DATETIME,
    deleted_by VARCHAR(4),
    version_no INT NOT NULL,
    id VARCHAR(4) NOT NULL,
    name VARCHAR(64) NOT NULL,
    is_activated INT(1) NOT NULL,
    expired_at DATETIME
) ENGINE = InnoDB COMMENT = 'Apache Realm';

-- Apache User Realm Relation
CREATE TABLE apache_user_realm(
    created_at DATETIME,
    created_by VARCHAR(4),
    updated_at DATETIME,
    updated_by VARCHAR(4),
    deleted_at DATETIME,
    deleted_by VARCHAR(4),
    version_no INT NOT NULL,
    id VARCHAR(6) NOT NULL,
    user_id VARCHAR(4) NOT NULL,
    realm_id VARCHAR(4) NOT NULL
) ENGINE = InnoDB COMMENT = 'Apache User Realm';

INSERT INTO apache_user_realm (created_at, created_by, updated_at, updated_by, deleted_at, deleted_by, version_no, id, user_id, realm_id) VALUES 
('2021-01-01 00:00:00', '1000', NULL, NULL, NULL, NULL, '0', '100000', '1000', '1000'),
('2021-01-01 00:00:00', '1000', NULL, NULL, NULL, NULL, '0', '100001', '1001', '1000'),
('2021-01-01 00:00:00', '1000', NULL, NULL, NULL, NULL, '0', '100002', '1000', '1001'),
('2021-01-01 00:00:00', '1000', NULL, NULL, NULL, NULL, '0', '100003', '1001', '1001');

-- apache user password by username
SELECT
    u.password
FROM
    apache_user u
WHERE
    u.username = 'system' AND 
    u.deleted_at IS NULL  AND
    u.deleted_by IS NULL  AND
    IFNULL(u.is_signed_in, 0) = 0 AND
    IFNULL(u.is_activated, 0) = 1 AND
    IFNULL(u.is_unlocked , 0) = 1 AND
    IFNULL(u.user_expired_at, SYSDATE() + INTERVAL 1 DAY) > SYSDATE() AND
    IFNULL(u.pass_expired_at, SYSDATE() + INTERVAL 1 DAY) > SYSDATE()

-- apache user role by username
SELECT
    g.name
FROM
    apache_user u
LEFT JOIN apache_user_group j ON u.id       = j.user_id
LEFT JOIN apache_group      g ON j.group_id = g.id
WHERE
    u.username = 'system' AND
    j.deleted_at IS NULL  AND
    j.deleted_by IS NULL  AND
    g.deleted_at IS NULL  AND
    g.deleted_by IS NULL  AND
    IFNULL(g.is_activated, 0) = 1 AND 
    IFNULL( g.expired_at, SYSDATE() + INTERVAL 1 DAY) > SYSDATE()

-- apache user password by username and realm name
SELECT
    u.password
FROM
    apache_user u
LEFT JOIN apache_user_realm j ON u.id       = j.user_id
LEFT JOIN apache_realm r      ON j.realm_id = r.id
WHERE
    u.username = 'system'        AND
    r.name     = 'Academia Soft' AND
    u.deleted_at IS NULL  AND
    u.deleted_by IS NULL  AND 
    j.deleted_at IS NULL  AND
    j.deleted_by IS NULL  AND 
    r.deleted_at IS NULL  AND
    r.deleted_by IS NULL  AND
    IFNULL(u.is_signed_in, 0) = 0 AND
    IFNULL(u.is_activated, 0) = 1 AND
    IFNULL(u.is_unlocked , 0) = 1 AND
    IFNULL(r.is_activated, 0) = 1 AND
    IFNULL(u.user_expired_at, SYSDATE() + INTERVAL 1 DAY) > SYSDATE() AND
    IFNULL(u.pass_expired_at, SYSDATE() + INTERVAL 1 DAY) > SYSDATE() AND
    IFNULL(r.expired_at, SYSDATE() + INTERVAL 1 DAY) > SYSDATE()

<VirtualHost *:80>
    ServerName doc.dev.shahed.biz
    ServerAdmin admin@dev.shahed.biz
    DocumentRoot /var/www/html

    DBDMin  4
    DBDKeep 8
    DBDMax  20
    DBDExptime 300
    DBDriver mysql
    DBDParams "host=127.0.0.1,port=3306,user=apache,pass=password,dbname=apache"

    Alias /docs "/var/www/docs/"
    <Directory "/var/www/docs">
        Options Indexes MultiViews FollowSymLinks
        AllowOverride All
        Require all granted
        Order Allow,Deny
        Allow from all
    </Directory>

    Alias /soft "/var/www/soft/"
    <Directory "/var/www/soft">
        AuthType Basic
        AuthName Academia
        AuthBasicProvider dbd

        Require valid-user
        Require dbd-group Admin
        Require dbd-group System
        Options Indexes MultiViews FollowSymLinks

        AuthDBDUserPWQuery \
            "SELECT u.password FROM apache_user u WHERE u.username = %s AND u.deleted_at IS NULL and u.deleted_by IS NULL AND IFNULL(u.is_signed_in, 0) = 0 AND IFNULL(u.is_activated, 0) = 1 AND IFNULL(u.is_unlocked, 0) = 1 AND IFNULL(u.user_expired_at, SYSDATE() + INTERVAL 1 DAY) > SYSDATE() AND IFNULL(u.pass_expired_at, SYSDATE() + INTERVAL 1 DAY) > SYSDATE()"

        AuthzDBDQuery \
            "SELECT g.name FROM apache_user u LEFT JOIN apache_user_group j ON u.id = j.user_id LEFT JOIN apache_group g ON j.group_id = g.id WHERE u.username = %s AND j.deleted_at IS NULL and j.deleted_by IS NULL AND g.deleted_at IS NULL and g.deleted_by IS NULL AND IFNULL(g.is_activated, 0) = 1 AND IFNULL(g.expired_at, SYSDATE() + INTERVAL 1 DAY) > SYSDATE()"

    </Directory>

    ErrorLog ${APACHE_LOG_DIR}/error.log
    CustomLog ${APACHE_LOG_DIR}/access.log combined
</VirtualHost>