Apache Basic Authentication: Difference between revisions
Jump to navigation
Jump to search
(→Query) |
|||
| Line 73: | Line 73: | ||
==Query== | ==Query== | ||
===Oracle=== | |||
{| | {| | ||
| valign="top" | | | valign="top" | | ||
| Line 114: | Line 115: | ||
AND u.user_expired > trunc(sysdate) | AND u.user_expired > trunc(sysdate) | ||
AND u.pass_expired > trunc(sysdate) | AND u.pass_expired > trunc(sysdate) | ||
AND a.is_active = 1 | |||
AND g.is_active = 1; | |||
</source> | |||
|} | |||
===MySQL=== | |||
{| | |||
| 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(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 a.is_active = 1 | ||
AND g.is_active = 1; | AND g.is_active = 1; | ||
Revision as of 19:11, 31 January 2020
Install
apt -y install mariadb-server mariadb-client
apt install libaprutil1-dbd-mysql
a2enmod dbd
a2enmod authn_dbd
a2enmod authz_dbd
authn_socache
systemctl restart mysql
update-rc.d mysql enable
systemctl restart apache2
update-rc.d apache2 enable
Config
# mod_dbd configuration
DBDriver mysql
DBDParams "dbname=apache_auth user=apache pass=password"
DBDMin 4
DBDKeep 8
DBDMax 20
DBDExptime 300
<Directory "/var/chorke/www/dev.chorke.org/soft/">
AuthType Basic
AuthName Team
AuthBasicProvider dbd
# mod_authn_dbd SQL
AuthDBDUserPWQuery \
"SELECT u.user_pass FROM m00te00x00 u WHERE u.user_name = %s 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)"
# mod_authz_core configuration
Require dbd-group team
# mod_authz_dbd configuration
AuthzDBDQuery \
"SELECT g.group_name 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 = %s 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"
# when a user fails to be authenticated or authorized,
# invite them to login; this page should provide a link
# to /team-private/login.html
ErrorDocument 401 "/login-info.html"
<Files "login.html">
AuthDBDUserPWQuery \
"SELECT u.user_pass FROM m00te00x00 u WHERE u.user_name = %s 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)"
Require dbd-login
AuthzDBDQuery \
"UPDATE m00te00x00 SET is_signin = 1 WHERE user_name = %s"
AuthzDBDLoginToReferer On
</Files>
<Files "logout.html">
Require dbd-logout
AuthzDBDQuery \
"UPDATE m00te00x00 SET is_signin = 0 WHERE user_name = %s"
</Files>
</Directory>
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;
|