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

[root@router /var/log]# radtest router2 1 127.0.0.1 0 hardpass5 
Sending Access-Request of id 13 to 127.0.0.1 port 1812
	User-Name = "router2"
	User-Password = "1"
	NAS-IP-Address = 255.255.255.255
	NAS-Port = 0
rad_recv: Access-Reject packet from host 127.0.0.1:1812, id=13, length=20

Ready to process requests.
rad_recv: Access-Request packet from host 127.0.0.1:63422, id=173, length=59
	User-Name = "router2"
	User-Password = "1"
	NAS-IP-Address = 255.255.255.255
	NAS-Port = 0
  Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 0
  modcall[authorize]: module "preprocess" returns ok for request 0
  modcall[authorize]: module "chap" returns noop for request 0
  modcall[authorize]: module "mschap" returns noop for request 0
    rlm_realm: No '@' in User-Name = "router2", looking up realm NULL
    rlm_realm: No such realm "NULL"
  modcall[authorize]: module "suffix" returns noop for request 0
  rlm_eap: No EAP-Message, not doing EAP
  modcall[authorize]: module "eap" returns noop for request 0
  modcall[authorize]: module "files" returns notfound for request 0
radius_xlat:  'router2'
rlm_sql (sql): sql_set_user escaped user --> 'router2'
radius_xlat:  'call radcheck('router2')'
rlm_sql (sql): Reserving sql socket id: 4
rlm_sql_mysql: MYSQL check_error: 1305 received
rlm_sql_getvpdata: database query error
rlm_sql (sql): SQL query error; rejecting user
rlm_sql (sql): Released sql socket id: 4
  modcall[authorize]: module "sql" returns fail for request 0
modcall: leaving group authorize (returns fail) for request 0
There was no response configured: rejecting request 0
Server rejecting request 0.
Finished request 0
Going to the next request
--- Walking the entire request list ---
Waking up in 1 seconds...
--- Walking the entire request list ---
Waking up in 1 seconds...
--- Walking the entire request list ---
Sending Access-Reject of id 173 to 127.0.0.1 port 63422
Waking up in 4 seconds...
--- Walking the entire request list ---
Cleaning up request 0 ID 173 with timestamp 4a6844dc
Nothing to do.  Sleeping until we see a request.

[root@router /home/admin]# radtest router2 1 127.0.0.1 0 hardpass5 
Sending Access-Request of id 199 to 127.0.0.1 port 1812
	User-Name = "router2"
	User-Password = "1"
	NAS-IP-Address = 255.255.255.255
	NAS-Port = 0
rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=199, length=20

versus@versus-laptop:~$ mysql -u root -p bill
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 318
Server version: 5.0.75-0ubuntu10.2-log (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SHOW CREATE PROCEDURE radcheck;
ERROR 1305 (42000): PROCEDURE radcheck does not exist
mysql> DROP PROCEDURE IF EXISTS `radcheck`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER $$  
mysql> CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
    -> BEGIN
    ->   SELECT id,name,'Password' AS Attribute,AES_DECRYPT(passwd,'hardpass3') AS Value,'=='
    ->     FROM users WHERE name=login;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SHOW CREATE PROCEDURE radcheck \G
*************************** 1. row ***************************
       Procedure: radcheck
        sql_mode: 
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `radcheck`(IN login VARCHAR(64))
BEGIN
  SELECT id,name,'Password' AS Attribute,AES_DECRYPT(passwd,'hardpass3') AS Value,'=='
    FROM users WHERE name=login;
END
1 row in set (0.00 sec)

mysql> call radcheck('router2');
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (cp1251_general_ci,IMPLICIT) for operation '='

mysql> SHOW CREATE DATABASE nodeny;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| nodeny   | CREATE DATABASE `nodeny` /*!40100 DEFAULT CHARACTER SET cp1251 */ | 
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> call radcheck('router2');
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (cp1251_general_ci,IMPLICIT) for operation '='

mysql> DROP PROCEDURE IF EXISTS `radcheck`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER $$  
mysql> CREATE PROCEDURE `radcheck` (IN login VARCHAR(64)) 
    -> BEGIN
    ->   SET NAMES 'latin1';
    ->   SELECT id,name,'Password' AS Attribute,AES_DECRYPT(passwd,'hardpass3') AS Value,'=='
    ->     FROM users WHERE name='router2' COLLATE 'latin1_swedish_ci';
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call radcheck('router2'); 
+-----+---------+-----------+-------+----+
| id  | name    | Attribute | Value | == |
+-----+---------+-----------+-------+----+
| 660 | router2 | Password  | 1     | == | 
+-----+---------+-----------+-------+----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

[root@router /home/admin]# radtest router2 1 127.0.0.1 0 hardpass5 
Sending Access-Request of id 124 to 127.0.0.1 port 1812
        User-Name = "router2"
        User-Password = "1"
        NAS-IP-Address = 255.255.255.255
        NAS-Port = 0
rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=124, length=38
        Framed-IP-Address = 172.16.23.8
        Framed-IP-Netmask = 255.255.255.255
        Framed-Protocol = PPP