mysql simultaneous login detection

21 May 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@saudsl.co.za';
+-----------------------+-----------------+
| UserName              | GroupName       |
+-----------------------+-----------------+
| testgiga@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

mysql simultaneous login detection

Gabriel J Marais