Apache Basic Authentication

Install

apt -y install mariadb-server mariadb-client
apt install libaprutil1-dbd-mysql

a2enmod dbd
a2enmod authn_dbd
a2enmod authz_dbd
a2enmod authn_socache

systemctl restart mysql
update-rc.d mysql enable

systemctl restart apache2
update-rc.d apache2 enable

Config

<VirtualHost *:80>
    ServerName pi4.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 /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 m00te00x00 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()"

#       AuthDBDUserRealmQuery \
#           "SELECT u.password FROM m00te00x00 u LEFT JOIN m00tj01x00 j ON u.id = j.user_id LEFT JOIN m00ts01x00 r ON j.realm_id = r.id WHERE u.username = %s AND r.name = %s 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()"

        AuthzDBDQuery \
            "SELECT g.name FROM m00te00x00 u LEFT JOIN m00tj00x00 j ON u.id = j.user_id LEFT JOIN m00ts00x00 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>

Query

Oracle

SELECT u.user_pass AS "password" FROM m00te00x00 u WHERE u.user_name = '&user_name' AND u.is_signin = 1 AND u.is_active = 1 AND u.is_locked = 0 AND u.user_expired > trunc(sysdate) AND u.pass_expired > trunc(sysdate); -- update sign in UPDATE m00te00x00 SET is_signin = 1 WHERE user_name = '&user_name';

-- -- find groups by user -- SELECT g.group_name AS "group" FROM m00te00x00 u LEFT JOIN m00tj00x00 a ON u.user_code = a.user_code LEFT JOIN m00ts00x00 g ON a.group_code = g.group_code WHERE u.user_name = '&user_name' AND u.is_signin = 1 AND u.is_active = 1 AND u.is_locked = 0 AND u.user_expired > trunc(sysdate) AND u.pass_expired > trunc(sysdate) AND a.is_active = 1 AND g.is_active = 1;

MySQL

SELECT u.user_pass AS "password" FROM m00te00x00 u WHERE u.user_name = 'user_name' AND u.is_signin = 1 AND u.is_active = 1 AND u.is_locked = 0 AND u.user_expired > DATE(SYSDATE()) AND u.pass_expired > DATE(SYSDATE()); -- update sign in UPDATE m00te00x00 SET is_signin = 1 WHERE user_name = 'user_name';

-- -- find groups by user -- SELECT g.group_name AS "group" FROM m00te00x00 u LEFT JOIN m00tj00x00 a ON u.user_code = a.user_code LEFT JOIN m00ts00x00 g ON a.group_code = g.group_code WHERE u.user_name = 'user_name' AND u.is_signin = 1 AND u.is_active = 1 AND u.is_locked = 0 AND u.user_expired > DATE(SYSDATE()) AND u.pass_expired > DATE(SYSDATE()) AND a.is_active = 1 AND g.is_active = 1;

PgSQL

SELECT u.user_pass AS "password" FROM m00te00x00 u WHERE u.user_name = 'user_name' AND u.is_signin = 1 AND u.is_active = 1 AND u.is_locked = 0 AND u.user_expired > DATE(NOW()) AND u.pass_expired > DATE(NOW()); -- update sign in UPDATE m00te00x00 SET is_signin = 1 WHERE user_name = 'user_name';

-- -- find groups by user -- SELECT g.group_name AS "group" FROM m00te00x00 u LEFT JOIN m00tj00x00 a ON u.user_code = a.user_code LEFT JOIN m00ts00x00 g ON a.group_code = g.group_code WHERE u.user_name = 'user_name' AND u.is_signin = 1 AND u.is_active = 1 AND u.is_locked = 0 AND u.user_expired > DATE(NOW()) AND u.pass_expired > DATE(NOW()) AND a.is_active = 1 AND g.is_active = 1;

Schema » MariaDB

echo -n 'Password: ';read -s CHORKE_HTTP_AUTHNZ;export CHORKE_HTTP_AUTHNZ;echo
# Password: sadaqah!

Schema » MariaDB » Check

cat <<'SQL'| mariadb
\! echo " "
SELECT concat(user, '@', host) AS 'user\n+--------------------------+' FROM mysql.user ORDER BY 1 ASC;
\! echo " "
-- SHOW databases;
SELECT schema_name AS 'database\n+--------------------------+' FROM information_schema.schemata ORDER BY 1 ASC;
SQL

Schema » MariaDB » Create

cat << DDL | mariadb
CREATE DATABASE IF NOT EXISTS chorke_http_authnz;
CREATE USER chorke_http_authnz@'%' IDENTIFIED BY '${CHORKE_HTTP_AUTHNZ}';
GRANT ALL PRIVILEGES ON chorke_http_authnz.* TO chorke_http_authnz@'%';
FLUSH PRIVILEGES;
DDL

Schema » MariaDB » Verify

echo -n 'Password: ';read -s MYSQL_PWD;export MYSQL_PWD;echo
# Password: sadaqah!

mariadb -P 3306 -u chorke_http_authnz -D chorke_http_authnz
mariadb -P 3306 -u chorke_http_authnz -D chorke_http_authnz -h 10.20.0.1
mariadb -P 3306 -u chorke_http_authnz -D chorke_http_authnz -h 10.19.83.10

Schema » MariaDB » Delete

cat <<'DDL'| mariadb
\! echo " "
REVOKE ALL PRIVILEGES ON chorke_http_authnz.* FROM chorke_http_authnz@'%';
DROP DATABASE IF EXISTS  chorke_http_authnz;
DROP USER     IF EXISTS  chorke_http_authnz@'%';
FLUSH PRIVILEGES;
DDL

Schema » PostgreSQL

echo -n 'Password: ';read -s CHORKE_HTTP_AUTHNZ;export CHORKE_HTTP_AUTHNZ;echo
# Password: sadaqah!

Schema » PostgreSQL » Check

cat <<'SQL'| psql
-- \du+
SELECT usename AS "user" FROM pg_catalog.pg_user ORDER BY 1 ASC;
-- \l+
SELECT datname as "database" FROM pg_database ORDER BY 1 ASC;
SQL

Schema » PostgreSQL » Create

cat << DDL | psql
SELECT 'CREATE DATABASE chorke_http_authnz' 
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'chorke_http_authnz')\gexec

CREATE USER chorke_http_authnz WITH ENCRYPTED PASSWORD '${CHORKE_HTTP_AUTHNZ}';
GRANT ALL PRIVILEGES ON DATABASE chorke_http_authnz TO chorke_http_authnz;
ALTER DATABASE chorke_http_authnz OWNER TO chorke_http_authnz;
DDL

Schema » PostgreSQL » Verify

echo -n 'Password: ';read -s PGPASSWORD; export PGPASSWORD; echo
# Password: sadaqah!

psql -p 5432 -U chorke_http_authnz -d chorke_http_authnz -h 10.20.0.1
psql -p 5432 -U chorke_http_authnz -d chorke_http_authnz -h 10.19.83.10

Schema » PostgreSQL » Delete

cat <<'DDL'| psql
\! printf '\n'
REVOKE ALL PRIVILEGES ON DATABASE chorke_http_authnz FROM chorke_http_authnz;
DROP OWNED    BY        chorke_http_authnz;
DROP DATABASE IF EXISTS chorke_http_authnz;
DROP USER     IF EXISTS chorke_http_authnz;
DDL

MySQL Apache User

CREATE USER 'apache'@'%' IDENTIFIED VIA mysql_native_password USING 'p@$$w0rd';
GRANT USAGE ON *.* TO 'apache'@'%' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
CREATE DATABASE IF NOT EXISTS `apache`;
GRANT ALL PRIVILEGES ON `apache`.* TO 'apache'@'%';

Knowledge

ls -lah /usr/lib/apache2/modules/mod_authn_socache.so
htpasswd -nbs system p@$$w0rd
htpasswd -nbs admin p@$$w0rd
htpasswd -nb user p@$$w0rd

References

User authentication using an SQL database Group Authorization and Login using SQL DBD Password Encryptions Format User authentication DBD Params Apache 2 Basic Authentication MySQL htaccess mod_rewrite Apache2 DBD Cheat Sheet Apache/Multilang Errordoc Apache/AutoIndex Apache/Proxy	Security » Certificate Security » Password Security » ZA Proxy Helm » PostgreSQL Security » Spring Helm » MariaDB Security » HTTP Security » Java PostgreSQL MariaDB	Security » Certificate » TLS » Configuration Generator Security » Certificate » TLS