<AuthBy SQL>
authenticates users
from an SQL database, and stores accounting records to an SQL database. It
is implemented in AuthSQL.pm
. <AuthBy
SQL>
is very powerful and configurable, and it has many
parameters in order to customise its behaviour. You need to have some
familiarity with SQL and relational databases in order to configure and
use <AuthBy SQL>
.<AuthBy
SQL>
uses the Perl DBI/DBD interface to connect to your
database. You can therefore use <AuthBy SQL>
with a large number of commercial and free SQL database systems. In order
to use SQL, you will need to install your database software, install the
matching Perl DBD module, and install the Perl DBI module before
<AuthBy SQL>
works. <AuthBy SQL>
receives an Access-Request
message, it tries to find a password and check and reply items for the
user in a database table (you can change this behaviour with the
AuthColumnDef
parameter). Radiator constructs an SQL
select statement from the AuthSelect
parameter. By
changing AuthSelect
, you can control the table it
looks in, and the names of the columns for the password, check and reply
columns. If the user is found, all the check items are compared with the
attributes in the request, including Expiration
and
other special check items. For more information about check and reply
items, see Section 7. Check and reply items.<AuthBy SQL>
replies with an Access-Accept
message containing all the attributes in the reply items. If the user does
not appear in the database, or if any check attribute does not match, an
Access-Reject message is sent to the client.AuthSelect
statement does not generate a simple
password, check items, and reply items as a result, you can configure
Radiator how to interpret the columns in the result with the
AuthColumnDef
parameter. If you do not specify any
AuthColumnDef
parameters, Radiator assumes that
AuthSelect
returns password, check items, and reply
items in that order.<AuthBy SQL>
receives an Accounting-Request message, it can store any number of the
attributes from the request in an SQL table. You can control the table it
stores in, and the names of the columns where the attributes are stored,
and the attribute that is stored there. To enable SQL accounting you must
define AccountingTable and you must define at least one
AcctColumnDef
. If you do not do both of these
<AuthBy SQL>
acknowledges Accounting-Request
message but will not store them anywhere. The example
goodies/sql.cfg
in the Radiator distribution shows a
typical setup that will work with the table schemas in
goodies/*Create.sql
files.goodies/*Create.sql
files in the Radiator
distribution. Regard these as a starting point for constructing large
scalable user and accounting databases.<AuthBy
SQL>
understands also the same parameters as
<AuthBy xxxxxx>
. For more information, see
Section 3.32. <AuthBy xxxxxx>. It supports also
all the common SQL configuration parameters. For more information about
the SQL configuration parameters, see Section 3.8. SQL configuration.# Check user status is current. No reply items in DB # Note: The entire statement must be on one line AuthSelect select PW, CHECK from USERS where NAME=%0 and STATUS = 1
AuthSelect select PASSWORD from SUBSCRIBERS where BINARY USERNAME=%0
AuthColumnDef
parameters, Radiator assumes that
the first column returned is the password; the second is the check items
(if any) and the third is the reply items (if any). If you specify any
AuthColumnDef
parameters, Radiator uses the column
definitions you provide.AuthColumnDef
ignores
the returned columns if their value is one of the following:AuthColumnDef
parameters, one for each interesting field returned by AuthSelect. The
general format is:AuthColumnDef n, attributename, type[, formatted]
n
is the index of the field in the result of
AuthSelect. 0 is the first field.attributename
is the name of the attribute to
be checked or replied. The value of the attribute is in the nth field
of the result. The special attributename
"GENERIC" indicates that it is a list of comma separated
attribute=value
pairs.type
indicates whether it is a
check
or reply
item. A type
of request
sets the named attribute in the
incoming request, from where it can be retrieved later in the
authentication process with special formatting characters.formatted
, if this keyword is present, the
value retrieved from the database is subject to special character
processing before its value is used, and can therefore contain
%{something}
forms which are replaced at
authentication time. Reply items values are always formatted when they
are added to a reply. Therefore there is typically no need to use this
flag when the type is reply
.AuthSelect select PASSWORD from SUBSCRIBERS \ where USERNAME=%0
AuthColumnDef 0, User-Password, check
AuthSelect select PASSWORD, CHECKATTR, REPLYATTR \ from SUBSCRIBERS \ where USERNAME=%0
AuthColumnDef 0, User-Password, check AuthColumnDef 1, GENERIC, check AuthColumnDef 2, GENERIC, reply
AuthColumnDef
, you need to set it like
this:
AuthColumnDef 0, Encrypted-Password, check
AuthSelect select SERVICE, PASSWORD, MAXTIME from SUBSCRIBERS \ where USERNAME=%0
AuthColumnDef 0, Service-Type, check, formatted AuthColumnDef 1, User-Password, check AuthColumnDef 2, Session-Timeout, reply
AuthColumnDef 1, NAS-IP-Address, check AuthColumnDef 2, Framed-IP-Address, reply
# store accounting records in RADUSAGEyyyymm table AccountingTable RADUSAGE%Y%m
# unix Encrypted password are in CRYPTPW AuthSelect select CRYPTPW from USERS where N = %0 EncryptedPassword
Acct-Status-Type
attribute values that will be processed
in Accounting requests. The value is a comma-separated list of valid
Acct-Status-Type
attribute values including,
Start
, Stop
, Alive
,
Modem-Start
, Modem-Stop
,
Cancel
, Accounting-On
and
Accounting-Off
. See your dictionary for a full
list.HandleAcctStatusTypes
is specified and
an Accounting request has an Acct-Status-Type
not
mentioned in HandleAcctStatusTypes
, then the request
will be ACCEPTed but not otherwise processed by the enclosing clause. The
default is to handle all Acct-Status-Type
values.# Only process Start and Stop requests, ACCEPT and acknowledge everything else HandleAcctStatusTypes Start,Stop
# We only want Stops AccountingStopsOnly
%b %e,
%Y %H:%M
which formats to, for example, Sep 3, 1995
13:37
.# Include seconds in dates in a way that MS-SQL likes DateFormat %b %e, %Y %H:%M:%S
AcctColumnDef Column,Attribute[,Type][,Format]
Column
is the name of the SQL column where the data is inserted.
Attribute
is the name of the RADIUS attribute to store
there. Type
is an optional data type specifier, which
specifies the data type of the SQL column. Format
is an
optional format string that can be used to format the value. Columns and
their values are included in accounting SQL statements in alphabetical
order by column name.Type
names are
recognised:integer
integer-date
Format
field is used as the date format (if it is
present), otherwise the standard DateFormat
parameter for this AuthBy SQL is used (which defaults to the format
'Sep 3, 1995 13:37'). This is useful for inserting the Timestamp
attribute as an SQL datetime type. The default is compatible with at
least Microsoft SQL and Sybase datetime columns. If it is not suitable
for your database, consider defining your own
DateFormat
parameter for this AuthBy SQL. The
resulting value is quoted after conversion.formatted-date
integer-date
instead. It has a much wider range of
formatting and date options.TimeDate
module Date::Format
according to the format string. TimeDate
is
available from CPAN. For more information, see Section 2.1.2. CPAN. It is most useful
for SQL databases with unusual date formats, like Oracle.
formatted-date
is now only provided for historical
reasons, and new installations should probably use
integer-date
in conjunction with
DateFormat
instead. The resulting value is not
quoted after conversion.formatted
literal
below to generate unquoted data.literal
inet_aton
formatted-date
to create date
formats to suit your SQL database. This example inserts the Timestamp into
an Oracle DATE or TIMESTAMP type column called TIME_STAMP:AcctColumnDef TIME_STAMP,Timestamp,formatted-date,\ to_date('%e %m %Y %H:%M:%S', 'DD MM YYYY HH24:MI:SS')
insert into ACCOUNTING (TIME_STAMP, ......) values (to_date('16 02 1999 16:40:02', 'DD MM YYYY HH24:MI:SS'), ....)
formatted-date
and
integer-date
, the format
field can be
used to build custom values in your insert statement. This can be very
useful to call SQL conversion functions on your data. If you specify a
format, it is used as a sprintf-style format, where %s is replaced by your
value.AcctColumnDef
lines, one for each attribute you want
to store in the accounting table. If there are no
AcctColumnDef
lines, then the accounting table is not
updated.Acct-Delay-Time
attribute (if present), as an integer
number of seconds since midnight Jan 1, 1970 UTC. The Timestamp attribute
is added by Radiator to all received Accounting requests, and is set to
the current time according to the host on which the Radiator is
running.AcctColumnDef USERNAME,User-Name AcctColumnDef TIME_STAMP,Timestamp,integer AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type AcctColumnDef ACCTDELAYTIME,Acct-Delay-Time,integer AcctColumnDef ACCTINPUTOCT,Acct-Input-Octets,integer AcctColumnDef ACCTOUTPUTOCT,Acct-Output-Octets,integer AcctColumnDef ACCTSESSIONID,Acct-Session-Id AcctColumnDef ACCTSESSTIME,Acct-Session-Time,integer AcctColumnDef ACCTTERMINATECAUSE,Acct_Terminate-Cause AcctColumnDef NASIDENTIFIER,NAS-Identifier AcctColumnDef NASPORT,NAS-Port,integer # Insert date-time without and with seconds DateFormat %Y-%m-%d %H:%M AcctColumnDef DATE_TIME,Timestamp, integer-date AcctColumnDef DATE_TIME_SEC,Timestamp, integer-date, %Y-%m-%d %H:%M:%S
AcctColumnDef
lines are correct.AcctInsertQuery
.formatted
type is useful for inserting values set up in
GlobalVars, or to get values from the current reply (possibly put there by
a preceding AuthBy).
AcctColumnDef ACCOUNTTYPE,%{Reply:accounttype},formatted AcctColumnDef SERVERNAME,%{GlobalVar:servername},formatted
AcctColumnDef START_TIME,%b-0%{Acct-Session-Time},literal
AcctSQLStatement delete from ONLINE where SessionID='%{Quote:%{Acct-Session-Id}}'
# maybe update if this is a duplicate AcctInsertQuery update or insert into %0 (%1) values (%2)
# Log all accounting to a single log file in LogDir AcctFailedLogFileName %L/misseddetails
AcctLogFileFormat %{Timestamp} %{Acct-Session-Id}\ %{User-Name}
# Change the flag in the first field to 'Accept' or 'Reject' PostAuthSelectHook sub{my($self,$name,$p,$user,$row)=@_;\ my $flag = $row->[0];\ $row->[0] = $flag ? 'Reject’:Accept';\ }
SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName=%0
NullPasswordMatchesAny
does not have
any effect if one or more AuthColumnDef
s are
defined.