Mikrotik with Freeradius/mySQL – Auto MAC Binding on 1st Login – Part 4

~ Auto Mac Binding via EXEC / PHP in Freeradius 2.x ~
! From the CORE of FREERADIUS !
By
Syed jahanzaib

---

FREERADIUS WITH MIKROTIK – Part #1

 

There are others parts too, look at part-1 for listing, i will update only part-1 listing

---

Personnel Note:

[At end of this guide I used BASH script method. This is efficient and customizable at many extend and can be scheduled to run hourly giving leverage to users. TRIGGER method is also good to auto insert the mac address immediately once new session is made in radacct table of user if there is no mac entry in the radcheck table for his username, in my opinion .Trigger are not recommended if you have thousands of active users connecting .]

This post is just for demonstration purposes. in production environment you should make your own module and add it in proper relevant places. This post contains just minimalist working config to begin with. Make sure to refine it in prd environment.

This is another post about freeradius. My aim is to let people know that creating your own Radius Billing system is not ROCKET SCIENCE. The only thing required is the ultimate passion to achieve the goal & with the proper googling , reading a LOT, understand logic’s, then you can do all on your own. Just wanted to break the image that most of professionals don’t like to share there knowledge. I strongly encourage to read the FR mailing list and Google

---

OP Requirements:

[ Sort of Wired one 😉 ]

We have a working Freeradius installation. All users can login to mikrotik which verify user account authentication requests via this radius. All working fine. Now OP wants to add Auto MAC binding feature so that when user first time login to NAS, his MAC should auto binds with his account, so next time if he tries to login from another workstation, he must get access denied.

---

Components used in this guide:

• Ubuntu 12.4 / x86
• Freeradius 2.1.10 [Default apt-get installation]
• MySQL 5.5.47 [Default apt-get installation]

---

SOLUTION # 1 – [My personnel preferred method]

1# BASH script to add MAC address in radcheck fo FREERADIUS [Zaib Updated this old script at 29-OCT-2019]

I scheduled this bash script to run hourly at some ISP network. It worked flawless and gave good results.

• It will list Online users from radacct table into file
• then it will run loop formula, & see which user dont have entry in RADCHECK table for mac validation
• if not found, then it will add mac entry in RADCHECK (for validation) and in users table as well (for display purposes)

#!/usr/bin/env bash
####!/bin/sh
#set -x
#trap "set +x; set -x" DEBUG
# BASH base script to add MAC for missing users # By Syed Jahanzaib
# Modified on : 25-OCTOBER-2019
# Local Variables
# Mysql credentials
SQLID="root"
SQLPASS="SQLROOTPASS"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
DB="radius"
#Table which contain main users information
TBL="users"
#Rad user group in which we will update user profile like from 1mb to expired or likewise
GROUP="radusergroup"
RADACCT_USR_LIST_TMP="/tmp/radacct_users_list_tmp.txt"
EMPTY_MAC_USR_LIST_TMP="/tmp/empty_mac_users_list_tmp.txt"
EMPTY_MAC_USR_LIST_FINAL="/tmp/empty_mac_users_list.txt_fnial.txt"
SCRIPT_TIME_LOG="/tmp/empty_mac_users_list_time_taken.txt"
> $RADACCT_USR_LIST_TMP
> $EMPTY_MAC_USR_LIST_TMP
> $EMPTY_MAC_USR_LIST_FINAL
> $SCRIPT_TIME_LOG
# Date Time Variables
DATE=$(date +%d-%m-%Y)
DT_HMS=$(date +'%H:%M:%S')
FULL_DATE=`date`
CURR_HOUR=$(date +%H)
TODAY=$(date +"%Y-%m-%d")
WEEK=`date -d "-1000 days" '+%Y-%m-%d'`
BEGIN="1970-01-01"
H=$(date +'%-H')

$CMD "use $DB; select username, callingstationid from radacct WHERE acctstoptime IS NULL;" | sort >> $RADACCT_USR_LIST_TMP
# IF no user found , show error and exit - zaib
CHK=`wc -m $RADACCT_USR_LIST_TMP | awk {'print $1}'`
if [ "$CHK" -eq 0 ]; then
echo "No user found who have empty mac , Exiting ..."
exit 1
fi
TOT=`cat $RADACCT_USR_LIST_TMP | wc -l`

echo "
- Script Start Time : $DATE $DT_HMS
- Total Users Online : $TOT
- Sleeping 5 Seconds for review ..."
sleep 5
echo " - Now checking online users mac entry in RADACCT table ...
"
# Apply Count Loop Formula while deleting first line which have junk text
num=0
cat $RADACCT_USR_LIST_TMP | while read users
do
num=$[$num+1]
USERNAME=`echo $users | awk '{print $1}'`
MAC_IN_RADACCT=`echo $users | awk '{print $2}' | tr -d ' '`
MAC_CHK_IN_RDCHK_TBL=`$CMD "use $DB; select value from radcheck where username = '$USERNAME' and attribute = 'Calling-Station-Id';"`
if [ -z "$MAC_CHK_IN_RDCHK_TBL" ]; then
echo "**INFO: $USERNAME have no MAC binded in RADCHECK, Now adding $MAC_IN_RADACCT from radacct table ******************
"
$CMD "use $DB; insert into radcheck (username, attribute, op, value) VALUES ('$USERNAME', 'Calling-Station-Id', ':=', '$MAC_IN_RADACCT');"
$CMD "use $DB; update $TBL set binded_mac_0 = '$MAC_IN_RADACCT' where username = '$USERNAME';"
else
echo "ALERT: $USERNAME mac already binded."
fi
done
DATE=$(date +%d-%m-%Y)
DT_HMS=$(date +'%H:%M:%S')
echo "- Total Users Scanned : $TOT
- Script Start Time : $DATE $DT_HMS"
echo "Script Finished here ... Powered by Syed.Jahanzaib"

 

---

SOLUTION #2

To fulfill such weird requirements, we have to use external program example PHP program (via exec) which will be executed when user gets connect successfully. It will then look in RADCHECK table for this specific user MAC address value name “Calling-Station-Id”. If it’s unable to find it, then it will add the entry so that next time user will login his MAC will be verified by the CHECKVAL module in freeradius to match the mac address. If there is mac address entry, it will simply ignore and process further , will also print message that “MAC Entry already found – z@iB”

First enable the CHECKVAL module in following file > /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

Search & uncomment the checkval module. Save & Exit.

Now edit EXEC module file by

nano /etc/freeradius/modules/exec

Remove all previous contents (if its lab testing otherwise be careful editing this file) & paste following

exec {
wait = yes
program = "/usr/bin/php /temp/checkmac.php %{User-Name} %{Calling-Station-Id}"
input_pairs = request
}

Save & Exit.

Now create the php program which will be executed by above module.

mkdir /temp
mkdir /temp/checkmac.php
touch /temp/checkmac.php
nano /temp/checkmac.php

and use following to paste make sure to modify relevant details …

>

checkmac.php contents

 0) {
printf ("MAC Entry already found by ZAiBBBBBBBBBBBBBBBB");
}
else {
printf ("Seems to be New User, adding its MAC address in table ...");
mysql_query("INSERT into radcheck (UserName, Attribute, op, Value) values ('$argv[1]', 'Calling-Station-Id', ':=', '$argv[2]')");
}
?>

---

TESTING ….

Start FR in debug mode by freeradius -X and try to login with the test ID from your workstation (or use the radtest or ntradping)

rad_recv: Access-Request packet from host 192.168.0.1 port 42449, id=45, length=188
Service-Type = Framed-User
Framed-Protocol = PPP
NAS-Port = 15728851
NAS-Port-Type = Ethernet
User-Name = "zaib"
Calling-Station-Id = "0C:84:DC:1E:0B:8D"
Called-Station-Id = "service1"
NAS-Port-Id = "ether10"
MS-CHAP-Challenge = 0x49c4549501e07fad5e6dae708bc815ed
MS-CHAP2-Response = 0x0100acaa712e29adad9abb681c5ef666e69300000000000000003cd5a092d7c816de798b7f5d09acba6f04eeed208cd6c19b
NAS-Identifier = "MIKROTIK"
NAS-IP-Address = 192.168.0.1
# Executing section authorize from file /etc/freeradius/sites-enabled/default
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
[mschap] Found MS-CHAP attributes. Setting 'Auth-Type = mschap'
++[mschap] returns ok
++[digest] returns noop
[suffix] No '@' in User-Name = "zaib", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
[exec] expand: %{User-Name} -> zaib
[exec] expand: %{Calling-Station-Id} -> 0C:84:DC:1E:0B:8D
Exec-Program output: Seems to be New User, adding its MAC address in table ...
Exec-Program-Wait: plaintext: Seems to be New User, adding its MAC address in table ...
Exec-Program: returned: 0
++[exec] returns ok

As you CAN SEE

“Exec-Program output: Seems to be New User, adding its MAC address in table …”

Now see the difference …

RADCHECK TABLE, Before Login …

RADCHECK TABLE, After Login …

When user will login again, radcheck table will be searched, if the mac found it will simply skip the add part and print the statement

[exec] expand: %{User-Name} -> zaib
[exec] expand: %{Calling-Station-Id} -> 0C:84:DC:1E:0B:8D
Exec-Program output: MAC Entry already found
Exec-Program-Wait: plaintext: MAC Entry already found
Exec-Program: returned: 0
++[exec] returns ok

& If the user will login from any other mac/workstation, he will be denied access.

---

SOLUTION #3

Trigger approach to add MAC address automatically upon user connection

Use following TRIGGER on radacct table. It will add the MAC address for the user in RADCHECK table after user is successfully connected. (or you can modify it as well)

--
-- Triggers `radacct`
--
DELIMITER $$
CREATE TRIGGER `chk_mac_after_insert` AFTER INSERT ON `radacct` FOR EACH ROW BEGIN
SET @mac = (SELECT count(*) from radcheck where username=New.username and attribute='Calling-Station-ID');
IF (@mac = 0) THEN
INSERT into radcheck (username,attribute,op,value) values (NEW.username,'Calling-Station-ID',':=',NEW.callingstationid);
UPDATE users SET mac = NEW.callingstationid where username = NEW.username;
END IF;
END
$$
DELIMITER ;

---

3# Allow Multiple MAC Login along with CHECKVAL !

We know that CHECKVAL allows only single mac to connect. Recently there was a requirement that a user have 2 systems that he uses in day & night timings. Since he had no wifi router,  therefore he wanted to have 2 MAC allowed. (one Id will connect at one time). So we can Use the += operator instead to allow multiple mac if we are using CHECKVAL operative to limit mac address!

INSERT INTO `radcheck` (`id`, `username`, `attribute`, `op`, `value`) VALUES
(1, 'zaib', 'Cleartext-Password', ':=', 'zaib'),
(11, 'zaib', 'Expiration', ':=', '25 Feb 2019 17:00:00'),
(27, 'zaib', 'Calling-Station-ID', '+=', '24:26:42:D4:BC:43'),
(42, 'zaib', 'Calling-Station-ID', '+=', '00:0C:29:B9:D8:A0');[/soureceode]</pre>

<hr />

&nbsp;

<hr />



<hr />

<h2>Additional Notes for DMASOFTLAB Radius Manager Auto Mac binding on user first login</h2>
<pre>
in DMA radius Manager there is builtin option to auto bind the user mac upon first login. in DMA panel, Goto <em>Home &gt; Settings &gt; Lock first seen mac</em> , although this option is working fine, but still if you need alternate method using mysql trigger, then use this trigger

Login to mysql, use radius db, and issue



-- MySQL dump 10.13 Distrib 5.5.54, for debian-linux-gnu (i686)
--
-- Host: localhost Database: radius
-- ------------------------------------------------------
-- Server version 5.5.54-0ubuntu0.12.04.1
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `chk_mac_after_insert` AFTER INSERT ON `radacct` FOR EACH ROW BEGIN
SET @mac = (SELECT count(*) from rm_users where username=New.username and mac='Calling-Station-ID');
IF (@mac = 0) THEN
UPDATE rm_users SET mac = 'Calling-Station-ID' , usemacauth = '1' where username = 'New.username';
END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;

--
-- Dumping routines for database 'radius'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

---

Regard’s
Syed Jahanzaib ~