mysql simultaneous login detection
Gabriel J Marais
forums at 64bit.co.za
Wed May 21 23:51:12 CEST 2008
Hi
I have been trying to setup freeradius with mysql to detect and reject
simultaneous logins for the past two days and been reading up, but I can't
get it working at all. Perhaps someone here has some more information on a
working example for me... ?
mysql> select UserName, GroupName from usergroup where UserName =
'testgiga at saudsl.co.za';
+-----------------------+-----------------+
| UserName | GroupName |
+-----------------------+-----------------+
| testgiga at saudsl.co.za | UNSHAPED_NORMAL |
+-----------------------+-----------------+
1 row in set (0.00 sec)
mysql> select * from radgroupcheck where GroupName = 'UNSHAPED_NORMAL';
+----+-----------------+------------------+----+-------+
| id | GroupName | Attribute | op | Value |
+----+-----------------+------------------+----+-------+
| 9 | UNSHAPED_NORMAL | Auth-Type | := | Local |
| 12 | UNSHAPED_NORMAL | Simultaneous-Use | := | 1 |
+----+-----------------+------------------+----+-------+
2 rows in set (0.00 sec)
mysql> select * from radgroupreply where GroupName = 'UNSHAPED_NORMAL';
+----+-----------------+-----------------------+----+-----------------+-----
-+
| id | GroupName | Attribute | op | Value | prio
|
+----+-----------------+-----------------------+----+-----------------+-----
-+
| 42 | UNSHAPED_NORMAL | Framed-Protocol | = | PPP | 0
|
| 41 | UNSHAPED_NORMAL | Acct-Interim-Interval | = | 3600 | 0
|
| 36 | UNSHAPED_NORMAL | Configuration-Token | = | UNSHAPED_NORMAL | 0
|
| 43 | UNSHAPED_NORMAL | Session-Timeout | = | 3600 | 0
|
| 46 | UNSHAPED_NORMAL | Port-Limit | = | 1 | 0
|
+----+-----------------+-----------------------+----+-----------------+-----
-+
5 rows in set (0.00 sec)
My radius.conf file as follows :-
prefix = /usr/local
exec_prefix = ${prefix}
sysconfdir = ${prefix}/etc
localstatedir = /var
sbindir = ${exec_prefix}/sbin
logdir = /var/log
raddbdir = ${sysconfdir}/raddb
radacctdir = ${logdir}/radacct
confdir = ${raddbdir}
run_dir = ${localstatedir}/run/radiusd
log_file = ${logdir}/radius.log
libdir = ${exec_prefix}/lib
pidfile = ${run_dir}/radiusd.pid
#user = nobody
#group = nobody
max_request_time = 30
delete_blocked_requests = no
cleanup_delay = 5
max_requests = 1024
bind_address = 10.0.0.128
port = 1812
hostname_lookups = no
allow_core_dumps = yes
regular_expressions = yes
extended_expressions = yes
log_stripped_names = no
log_auth = yes
log_auth_badpass = no
log_auth_goodpass = no
usercollide = no
lower_user = no
lower_pass = no
nospace_user = no
nospace_pass = no
checkrad = ${sbindir}/checkrad
security {
max_attributes = 200
reject_delay = 1
status_server = no
}
proxy_requests = no
#$INCLUDE ${confdir}/proxy.conf
$INCLUDE ${confdir}/clients.conf
snmp = no
$INCLUDE ${confdir}/snmp.conf
thread pool {
start_servers = 5
max_servers = 32
min_spare_servers = 3
max_spare_servers = 10
max_requests_per_server = 0
}
modules {
pap {
encryption_scheme = crypt
}
chap {
authtype = CHAP
}
pam {
pam_auth = radiusd
}
# Unix /etc/passwd style authentication
#
unix {
cache = no
cache_reload = 600
radwtmp = ${logdir}/radwtmp
}
#$INCLUDE ${confdir}/eap.conf
mschap {
authtype = MS-CHAP
}
realm suffix {
format = suffix
delimiter = "@"
ignore_default = no
ignore_null = no
}
realm realmpercent {
format = suffix
delimiter = "%"
ignore_default = no
ignore_null = no
}
realm ntdomain {
format = prefix
delimiter = "\\"
ignore_default = no
ignore_null = no
}
checkval {
# The attribute to look for in the request
item-name = Calling-Station-Id
# The attribute to look for in check items. Can be multi
valued
check-name = Calling-Station-Id
# The data type. Can be
# string,integer,ipaddr,date,abinary,octets
data-type = string
# If set to yes and we dont find the item-name attribute in
the
# request then we send back a reject
# DEFAULT is no
#notfound-reject = no
}
# Write a detailed log of all accounting records received.
#
detail {
detailfile =
${radacctdir}/%{Client-IP-Address}/detail-%Y%m%d
detailperm = 0600
}
detail auth_log {
detailfile =
${radacctdir}/%{Client-IP-Address}/auth-detail-%Y%m%d
#
# This MUST be 0600, otherwise anyone can read
# the users passwords!
detailperm = 0600
}
# EDIT MY GJM
sql_log {
path = ${radacctdir}/sql-relay
acct_table = "radacct"
postauth_table = "radpostauth"
Start = "INSERT INTO ${acct_table} (AcctSessionId, UserName,
\
NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime,
\
AcctSessionTime, AcctTerminateCause) VALUES
\
('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}',
\
'%{Framed-IP-Address}', '%S', '0', '0', '');"
Stop = "INSERT INTO ${acct_table} (AcctSessionId, UserName,
\
NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime,
\
AcctSessionTime, AcctTerminateCause) VALUES
\
('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}',
\
'%{Framed-IP-Address}', '0', '%S', '%{Acct-Session-Time}',
\
'%{Acct-Terminate-Cause}');"
Alive = "INSERT INTO ${acct_table} (AcctSessionId, UserName,
\
NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime,
\
AcctSessionTime, AcctTerminateCause) VALUES
\
('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}',
\
'%{Framed-IP-Address}', '0', '0',
'%{Acct-Session-Time}','');"
Post-Auth = "INSERT INTO ${postauth_table}
\
(user, pass, reply, date) VALUES
\
('%{User-Name}', '%{User-Password:-Chap-Password}',
\
'%{reply:Packet-Type}', '%S');"
}
acct_unique {
key = "User-Name, Acct-Session-Id, NAS-IP-Address,
Framed-IP-Address, NAS-Port"
}
$INCLUDE ${confdir}/sql.conf
radutmp {
filename = ${logdir}/radutmp
username = %{User-Name}
case_sensitive = yes
check_with_nas = yes
perm = 0600
callerid = "yes"
}
radutmp sradutmp {
filename = ${logdir}/sradutmp
perm = 0644
callerid = "no"
}
attr_filter {
attrsfile = ${confdir}/attrs
}
#
# The "always" module is here for debugging purposes. Each
# instance simply returns the same result, always, without
# doing anything.
always fail {
rcode = fail
}
always reject {
rcode = reject
}
always ok {
rcode = ok
simulcount = 0
mpp = no
}
#
# The 'expression' module currently has no configuration.
#
# This module is useful only for 'xlat'. To use it,
# put 'exec' into the 'instantiate' section. You can then
# do dynamic translation of attributes like:
#
# Attribute-Name = `%{expr:2 + 3 + %{exec: uid -u}}`
#
# The value of the attribute will be replaced with the output
# of the program which is executed. Due to RADIUS protocol
# limitations, any output over 253 bytes will be ignored.
expr {
}
#
# The 'digest' module currently has no configuration.
#
# "Digest" authentication against a Cisco SIP server.
# See 'doc/rfc/draft-sterman-aaa-sip-00.txt' for details
# on performing digest authentication for Cisco SIP servers.
#
digest {
}
#
# Execute external programs
#
# This module is useful only for 'xlat'. To use it,
# put 'exec' into the 'instantiate' section. You can then
# do dynamic translation of attributes like:
#
# Attribute-Name = `%{exec:/path/to/program args}`
#
# The value of the attribute will be replaced with the output
# of the program which is executed. Due to RADIUS protocol
# limitations, any output over 253 bytes will be ignored.
#
# The RADIUS attributes from the user request will be placed
# into environment variables of the executed program, as
# described in 'doc/variables.txt'
#
exec {
wait = yes
input_pairs = request
}
}
# Authorization. First preprocess (hints and huntgroups files),
# then realms, and finally look in the "users" file.
#
# The order of the realm modules will determine the order that
# we try to find a matching realm.
#
# Make *sure* that 'preprocess' comes before any realm if you
# need to setup hints for the remote radius server
authorize {
auth_log
sql
}
authenticate {
#
# PAP authentication, when a back-end database listed
# in the 'authorize' section supplies a password. The
# password can be clear-text, or encrypted.
Auth-Type PAP {
pap
}
#
# Most people want CHAP authentication
# A back-end database listed in the 'authorize' section
# MUST supply a CLEAR TEXT password. Encrypted passwords
# won't work.
Auth-Type CHAP {
chap
}
#
# MSCHAP authentication.
Auth-Type MS-CHAP {
mschap
}
}
#
# Pre-accounting. Decide which accounting type to use.
#
preacct {
acct_unique
suffix
}
#
# Accounting. Log the accounting data.
#
accounting {
detail
sql
radutmp
# sql
# sql_log
}
session { sql
}
post-auth { sql
# sql_log
}
pre-proxy {
}
post-proxy {
}
My extract from the sql.conf file :-
simul_count_query = "SELECT COUNT(*) FROM radacct WHERE
UserName='%{SQL-User-Name}' AND AcctStopTime = 0"
simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName,
NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol
FROM radacct WHERE UserName='%{SQL-User-Name}' AND AcctStopTime = 0"
Any pointers much appreciated !
Regards
Gabriel
More information about the Freeradius-Users
mailing list