Accounting Packets and Anonymous Identity

[Selahattin Cilek]

As it happens, it *is* possible to keep track of true user identity 
using the MAC address. I understand that FreeRADIUS always uses the true 
identity in the "post-auth" phase. So if we log the post-auth packets, 
we will have some valuable information about the user:
post-auth
{
      sql
}

Calling-Station-Id is the most valuable piece of information we can 
obtain from the post-auth logs. Using a simple MySQL synchronisation 
table and a stored procedure, we can trace users' current MAC addresses:
CREATE TABLE `raddb`.`users_macs` (`id` INT NOT NULL AUTO_INCREMENT,  
`username` VARCHAR(32) NOT NULL,  `mac` CHAR(17) NOT NULL,  PRIMARY KEY 
(`id`));
CREATE DEFINER=`root`@`localhost` PROCEDURE `synchronise_macs`(IN 
in_user_name VARCHAR(32), IN in_calling_station_id CHAR(17))
BEGIN
     SET @normalised_mac = LOWER(REPLACE(in_calling_station_id, '-', ':'));
     SET @user_name_count = (SELECT COUNT(username) FROM radreply WHERE 
username = in_user_name);

     -- Does the user name exist in our database?
     IF
         @user_name_count > 0
         -- Yes, it does.
     THEN
         -- Does the user name exist in the user_macs table?
         SET @user_mac_count = (SELECT COUNT(username) FROM user_macs 
WHERE username = in_user_name);

         IF
             @user_mac_count = 0
         THEN
             -- No, it does not.
             INSERT INTO
                 user_macs
                 (username, mac)
             VALUES
                 (in_user_name, @normalised_mac);
         ELSE
             -- Yes, it does.
             UPDATE
                 user_macs
             SET
                 mac = @normalised_mac
             WHERE
                 username = in_user_name;
         END IF;

     END IF;

END

Then, edit the dialup.conf file in the sql/mysql directory to call the 
stored procedure above:
postauth_query = "CALL 
synchronise_macs('%{User-Name}','%{Calling-Station-Id}')"

Now, we will always have the true identity and the current MAC address 
of the user.

Thank you all.





On 05.02.2017 01:44, Adam Bishop wrote:
> On 4 Feb 2017, at 21:45, Selahattin Cilek <selahattin_cilek at hotmail.com> wrote:
>> Yes, I know. I know I can't prevent them from configuring their own
>> machines as they like. That is not what I am asking.
> I'm not sure what you're asking then. The username is logged as "anonymous" because the user has typed in "anonymous". FreeRADIUS logs what the NAS and the client send.
>
> If you don't accounting packets to contain "anonymous" you can:
>   * reject their authentication.
>   * configure your NAS to send something more meaningful
>
> There's no secret SQL query - if the user sends "anonymous", and your NAS is configured to use that "anonymous" in accounting, then FreeRADIUS will log "anonymous", and any SQL query will return "anonymous".
>
> If explain your problem further (e.g. why is correlating the Calling-Station-ID in accounting logs to the one in your auth log insufficient) people can probably help further - but you've given precious little information.
>
> Regards,
>
> Adam Bishop
>
>    gpg: E75B 1F92 6407 DFDF 9F1C  BF10 C993 2504 6609 D460
>
> jisc.ac.uk
>
> Jisc is a registered charity (number 1149740) and a company limited by guarantee which is registered in England under Company No. 5747339, VAT No. GB 197 0632 86. Jisc’s registered office is: One Castlepark, Tower Hill, Bristol, BS2 0JA. T 0203 697 5800.
>
> Jisc Services Limited is a wholly owned Jisc subsidiary and a company limited by guarantee which is registered in England under company number 2881024, VAT number GB 197 0632 86. The registered office is: One Castle Park, Tower Hill, Bristol BS2 0JA. T 0203 697 5800.
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus