Apache Basic Authentication: Difference between revisions

From Chorke Wiki
Jump to navigation Jump to search
No edit summary
 
(30 intermediate revisions by the same user not shown)
Line 9: Line 9:
a2enmod authn_dbd
a2enmod authn_dbd
a2enmod authz_dbd
a2enmod authz_dbd
authn_socache
a2enmod authn_socache
</source>
</source>


Line 19: Line 19:
<source lang="bash">
<source lang="bash">
systemctl restart apache2
systemctl restart apache2
update-rc.d apache2enable
update-rc.d apache2 enable
</source>
</source>


==Config==
==Config==
<source lang="apache">
<source lang="apache">
# mod_dbd configuration
<VirtualHost *:80>
DBDriver mysql
    ServerName pi4.dev.shahed.biz
DBDParams "dbname=apache_auth user=apache pass=password"
    ServerAdmin [email protected]
    DocumentRoot /var/www/html


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


<Directory "/usr/www/dev.chorke.org/soft/">
    Alias /soft "/var/www/soft/"
  AuthType Basic
    <Directory "/var/www/soft">
  AuthName Team
        AuthType Basic
  AuthBasicProvider dbd
        AuthName Academia
        AuthBasicProvider dbd


  # mod_authn_dbd SQL
        Require valid-user
  AuthDBDUserPWQuery \
        Require dbd-group Admin
    "SELECT password FROM authn WHERE user = %s AND login = 'true'"
        Require dbd-group System
        Options Indexes MultiViews FollowSymLinks


  # mod_authz_core configuration
        AuthDBDUserPWQuery \
  Require dbd-group team
            "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()"


  # mod_authz_dbd configuration
#       AuthDBDUserRealmQuery \
  AuthzDBDQuery "SELECT group FROM authz WHERE user = %s"
#          "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()"


  # when a user fails to be authenticated or authorized,
        AuthzDBDQuery \
  # invite them to login; this page should provide a link
            "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()"
  # to /team-private/login.html
  ErrorDocument 401 "/login-info.html"


  <Files "login.html">
    </Directory>
    AuthDBDUserPWQuery "SELECT password FROM authn WHERE user = %s"


     Require dbd-login
     ErrorLog ${APACHE_LOG_DIR}/error.log
     AuthzDBDQuery "UPDATE authn SET login = 'true' WHERE user = %s"
    CustomLog ${APACHE_LOG_DIR}/access.log combined
     AuthzDBDLoginToReferer On
</VirtualHost>
  </Files>
</source>
 
==Query==
===Oracle===
{|
| valign="top" |
<source lang="sql">
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';
</source>
 
| valign="top" |
<source lang="sql">
--
-- 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;
</source>
|}


  <Files "logout.html">
===MySQL===
     Require dbd-logout
{|
     AuthzDBDQuery "UPDATE authn SET login = 'false' WHERE user = %s"
| valign="top" |
  </Files>
<source lang="sql">
</Directory>
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';
</source>
 
| valign="top" |
<source lang="sql">
--
-- 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;
</source>
|}
 
===PgSQL===
{|
| valign="top" |
<source lang="sql">
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';
</source>
 
| valign="top" |
<source lang="sql">
--
-- 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;
</source>
|}
 
==MySQL Apache User==
<source lang="sql">
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'@'%';
</source>
 
==Knowledge==
<source lang="bash">
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
</source>
</source>


Line 71: Line 224:
* [https://httpd.apache.org/docs/2.4/mod/mod_authn_dbd.html User authentication using an SQL database]
* [https://httpd.apache.org/docs/2.4/mod/mod_authn_dbd.html User authentication using an SQL database]
* [https://httpd.apache.org/docs/2.4/mod/mod_authz_dbd.html Group Authorization and Login using SQL]
* [https://httpd.apache.org/docs/2.4/mod/mod_authz_dbd.html Group Authorization and Login using SQL]
* [https://httpd.apache.org/docs/2.4/misc/password_encryptions.html DBD Password Encryptions Format]
* [https://httpd.apache.org/docs/2.4/mod/mod_dbd.html#dbdparams User authentication DBD Params]
* [https://www.howtoforge.com/tutorial/password-protect-directories-with-mod_authn_dbd-mysql-on-apache-debian-jessie Apache 2 Basic Authentication]
* [https://www.howtoforge.com/tutorial/password-protect-directories-with-mod_authn_dbd-mysql-on-apache-debian-jessie Apache 2 Basic Authentication]
* [https://stackoverflow.com/questions/7320979/ MySQL htaccess mod_rewrite]
* [https://github.com/shahedhossain/shahedhossain.github.io/wiki/Apache2-DBD-Cheat-Sheet Apache2 DBD Cheat Sheet]
* [[Apache/Multilang Errordoc]]
* [[Apache/Multilang Errordoc]]
* [[Apache/AutoIndex]]
* [[Apache/AutoIndex]]
* [[Apache/Proxy]]
* [[Apache/Proxy]]

Latest revision as of 05:30, 9 February 2022

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=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;

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

Retrieved from "https://cdn.chorke.org/weke/index.php?title=Apache_Basic_Authentication&oldid=7165"