Radius with MySQL 修改篇

在漫遊環境中，為了能夠確保知道使用者是由那些連線單位漫遊過來的
就必須做一點修改


其中主要會是在 /etc/raddb/sql/mysql/dialup.conf

accounting_update_query_alt = " 
INSERT INTO ${acct_table1} 
(acctsessionid, acctuniqueid, username, 
realm, nasipaddress, nasportid, 
nasporttype, acctstarttime, acctsessiontime, 
acctauthentic, connectinfo_start, acctinputoctets, 
acctoutputoctets, calledstationid, callingstationid, 
servicetype, framedprotocol, framedipaddress, 
acctstartdelay, xascendsessionsvrkey, clientipaddress) 
VALUES 
('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', 
'%{SQL-User-Name}', 
'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', 
'%{NAS-Port-Type}', 
DATE_SUB('%S', 
INTERVAL (%{%{Acct-Session-Time}:-0} + 
%{%{Acct-Delay-Time}:-0}) SECOND), 
'%{Acct-Session-Time}', 
'%{Acct-Authentic}', '', 
'%{%{Acct-Input-Gigawords}:-0}' << 32 | 
'%{%{Acct-Input-Octets}:-0}', 
'%{%{Acct-Output-Gigawords}:-0}' << 32 | 
'%{%{Acct-Output-Octets}:-0}', 
'%{Called-Station-Id}', '%{Calling-Station-Id}', 
'%{Service-Type}', '%{Framed-Protocol}', 
'%{Framed-IP-Address}', 
'0', '%{X-Ascend-Session-Svr-Key}', '%{Client-IP-Address}')"

以及

accounting_start_query = " 
INSERT INTO ${acct_table1} 
(acctsessionid, acctuniqueid, username, 
realm, nasipaddress, nasportid, 
nasporttype, acctstarttime, acctstoptime, 
acctsessiontime, acctauthentic, connectinfo_start, 
connectinfo_stop, acctinputoctets, acctoutputoctets, 
calledstationid, callingstationid, acctterminatecause, 
servicetype, framedprotocol, framedipaddress, 
acctstartdelay, acctstopdelay, xascendsessionsvrkey, clientipaddress) 
VALUES 
('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', 
'%{SQL-User-Name}', 
'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', 
'%{NAS-Port-Type}', '%S', NULL, 
'0', '%{Acct-Authentic}', '%{Connect-Info}', 
'', '0', '0', 
'%{Called-Station-Id}', '%{Calling-Station-Id}', '', 
'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', 
'%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}', '%{Client-IP-Address}')"

以及

accounting_stop_query_alt = " 
INSERT INTO ${acct_table2} 
(acctsessionid, acctuniqueid, username, 
realm, nasipaddress, nasportid, 
nasporttype, acctstarttime, acctstoptime, 
acctsessiontime, acctauthentic, connectinfo_start, 
connectinfo_stop, acctinputoctets, acctoutputoctets, 
calledstationid, callingstationid, acctterminatecause, 
servicetype, framedprotocol, framedipaddress, 
acctstartdelay, acctstopdelay, clientipaddress) 
VALUES 
('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', 
'%{SQL-User-Name}', 
'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', 
'%{NAS-Port-Type}', 
DATE_SUB('%S', 
INTERVAL (%{%{Acct-Session-Time}:-0} + 
%{%{Acct-Delay-Time}:-0}) SECOND), 
'%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', 
'%{Connect-Info}', 
'%{%{Acct-Input-Gigawords}:-0}' << 32 | 
'%{%{Acct-Input-Octets}:-0}', 
'%{%{Acct-Output-Gigawords}:-0}' << 32 | 
'%{%{Acct-Output-Octets}:-0}', 
'%{Called-Station-Id}', '%{Calling-Station-Id}', 
'%{Acct-Terminate-Cause}', 
'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', 
'0', '%{%{Acct-Delay-Time}:-0}', '%{Client-IP-Address}')"

以及最後

postauth_query = "INSERT INTO ${postauth_table} 
(username, pass, reply, authdate, clientipaddress) 
VALUES ( 
'%{User-Name}', 
'%{%{User-Password}:-%{Chap-Password}}', 
'%{reply:Packet-Type}', '%S', '%{Client-IP-Address}')"

都要把clientipaddress資訊加入，這樣之後查詢時才有跡可循
以上是修改SQL資料庫的部分
所以在資料庫中 radpostauth 及 radacct兩個表格均需增加一筆 clientipaddress 的欄位
其設定屬性為 varchar(15) NOT NULL DEFAULT ''

而 sql 的參數是在 /etc/raddb/sql.conf 中設定

最後~最近發現資料庫寫進去了
可是在 /var/log/radius/radacct/sql-relay 中並沒有相關的資訊
這檔案主要是把radius所使用的sql查詢都記錄下來
因此就必須要修改 /etc/raddb/modules/sql_log

        Start = "INSERT INTO ${acct_table} (AcctSessionId, UserName, 
         NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime, 
         AcctSessionTime, AcctTerminateCause, clientipaddress) VALUES                 
         ('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}', 
         '%{Framed-IP-Address}', '%S', '0', '0', '', '%{Client-IP-Address}');"
        Stop = "INSERT INTO ${acct_table} (AcctSessionId, UserName,  
         NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime, 
         AcctSessionTime, AcctTerminateCause, clientipaddress) VALUES                 
         ('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}', 
         '%{Framed-IP-Address}', '0', '%S', '%{Acct-Session-Time}',  
         '%{Acct-Terminate-Cause}', '%{Client-IP-Address}');"
        Alive = "INSERT INTO ${acct_table} (AcctSessionId, UserName, 
         NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime, 
         AcctSessionTime, AcctTerminateCause, clientipaddress) VALUES                 
         ('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}', 
         '%{Framed-IP-Address}', '0', '0', '%{Acct-Session-Time}','','%{Client-IP-Address}');"

        Post-Auth = "INSERT INTO ${postauth_table}                   
         (username, pass, reply, authdate, clientipaddress) VALUES                    
         ('%{User-Name}', '%{User-Password:-Chap-Password}',         
         '%{reply:Packet-Type}', '%S', '%{Client-IP-Address}');"
}