Биллинговая система Nodeny

ee /usr/local/etc/raddb/sql.conf

authorize_check_query = "call radcheck1('%{SQL-User-Name}')"
authorize_reply_query = "call radreply1('%{SQL-User-Name}')"
accounting_update_query = "call radupdate1('%{SQL-User-Name}')"
accounting_stop_query = "call radstop1('%{SQL-User-Name}')"

use bill;

DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$  
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
  SELECT id,name,'Password' AS Attribute,AES_DECRYPT(passwd,'hardpass3') AS Value,'=='
    FROM users1 WHERE name=login;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$  
CREATE PROCEDURE `radreply` (IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;  
  DECLARE usr_ip VARCHAR(15);

  SELECT id,ip INTO usr_id,usr_ip FROM users1 WHERE name=login;
  INSERT into dblogin (mid,act,time) VALUES (usr_id,47,unix_timestamp());

  SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
  SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
  SELECT NULL,login,'Framed-Protocol','PPP','=';
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radupdate`;
DELIMITER $$  
CREATE PROCEDURE `radupdate` (IN login VARCHAR(64))
BEGIN
  INSERT into dblogin (mid,act,time) VALUES
    ((SELECT id FROM users1 WHERE name=login LIMIT 1),47,unix_timestamp());
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radstop`;
DELIMITER $$  
CREATE PROCEDURE `radstop` (IN login VARCHAR(64))
BEGIN
  INSERT into dblogin (mid,act,time) VALUES
    ((SELECT id FROM users1 WHERE name=login LIMIT 1),46,unix_timestamp());
END$$
DELIMITER ;

use bill;

DROP PROCEDURE IF EXISTS `radcheck1`;
DELIMITER $$  
CREATE PROCEDURE `radcheck1` (IN login VARCHAR(64))
BEGIN
  SELECT id,name,'Password' AS Attribute,AES_DECRYPT(passwd,'hardpass3') AS Value,'=='
    FROM users1 WHERE name=login;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radreply1`;
DELIMITER $$  
CREATE PROCEDURE `radreply1` (IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;  
  DECLARE usr_ip VARCHAR(15);

  SELECT id,ip INTO usr_id,usr_ip FROM users1 WHERE name=login;
  INSERT into dblogin (mid,act,time) VALUES (usr_id,47,unix_timestamp());

  SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
  SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
  SELECT NULL,login,'Framed-Protocol','PPP','=';
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radupdate1`;
DELIMITER $$  
CREATE PROCEDURE `radupdate1` (IN login VARCHAR(64))
BEGIN
  INSERT into dblogin (mid,act,time) VALUES
    ((SELECT id FROM users1 WHERE name=login LIMIT 1),47,unix_timestamp());
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radstop1`;
DELIMITER $$  
CREATE PROCEDURE `radstop1` (IN login VARCHAR(64))
BEGIN
  INSERT into dblogin (mid,act,time) VALUES
    ((SELECT id FROM users1 WHERE name=login LIMIT 1),46,unix_timestamp());
END$$
DELIMITER ;

ee /usr/local/etc/raddb/sql.conf

authorize_check_query = "call radcheck1('%{SQL-User-Name}')"
authorize_reply_query = "call radreply1('%{SQL-User-Name}')"
accounting_update_query = "call radupdate1('%{SQL-User-Name}')"
accounting_stop_query = "call radstop1('%{SQL-User-Name}')"

use bill;

DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$  
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
  SELECT id,name,'Password' AS Attribute,AES_DECRYPT(passwd,'hardpass3') AS Value,'=='
    FROM users1 WHERE name=login;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$  
CREATE PROCEDURE `radreply` (IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;  
  DECLARE usr_ip VARCHAR(15);

  SELECT id,ip INTO usr_id,usr_ip FROM users1 WHERE name=login;
  INSERT into dblogin (mid,act,time) VALUES (usr_id,47,unix_timestamp());

  SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
  SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
  SELECT NULL,login,'Framed-Protocol','PPP','=';
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radupdate`;
DELIMITER $$  
CREATE PROCEDURE `radupdate` (IN login VARCHAR(64))
BEGIN
  INSERT into dblogin (mid,act,time) VALUES
    ((SELECT id FROM users1 WHERE name=login LIMIT 1),47,unix_timestamp());
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radstop`;
DELIMITER $$  
CREATE PROCEDURE `radstop` (IN login VARCHAR(64))
BEGIN
  INSERT into dblogin (mid,act,time) VALUES
    ((SELECT id FROM users1 WHERE name=login LIMIT 1),46,unix_timestamp());
END$$
DELIMITER ;

router ospf
 ospf router-id 1.2.3.4
 redistribute connected
 passive-interface default
 no passive-interface lagg0
 network 1.2.3.0/27 area 0.0.0.0
 area 0.0.0.0 authentication message-digest
 neighbor 1.2.3.1
 neighbor 1.2.3.2
!
line vty
!