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 [email protected]
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=apache,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()"
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;
|
MySQL Apache User
CREATE USER 'apache'@'%' IDENTIFIED VIA mysql_native_password USING '***';
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