FREERADIUS WITH MIKROTIK – Part #9 – Restrict user auth from allowed VLAN/Service Type only

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s>

Disclaimer:
This solution was designed for some specific situation described below. Surely there might be better ways, but sometimes dueto my laziness & dumbness, I select whichever gives required results. You may fine tune it, or modify it as required. I always recommends to read the FR mailing list for any query.

It’s a weird world we live in.
EVERY HUMAN BEING IS BORN FREE BUT YET EVERYWHERE HE IS IN CHAINS … ~

Syed Jahanzaib ~

---

Scenario:

OP have single Mikrotik CCR as NAS (pppoe server) and Freeradius as billing system. VLAN(s) are configured on Cisco switch for each reseller/dealer area with proper Trunking configured from SW to CCR. OP have few reseller/franchise managers, like Dealer-A, Dealer-B. They can create there own users in freeradius using customized fronted designed in php. All dealers can view/add/edit there own users only.

Sometimes it happens that Dealer-A creates User-ID and provide it to a user/friend who is sitting in Dealer-B network, therefore from Billing perspective its a Loss for Dealer-B.

---

Requirement:

To prevent users roaming. Every user must be allowed to connect from there respective dealer network Only. [That is ‘allowed-vlan]. User’s MAC address validation is also in place but we know that mac can be cloned easily.

---

Solution:

We have a user table which contains a column vlanid. We will add a SQL IF statement which will check the the connecting user NAS-Port-Id & match it with users allowed vlanid in the user’s table.

Users Table Example:

mysql> describe users;
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(128) | NO | UNI | NULL | |
| password | varchar(32) | NO | | NULL | |
| firstname | text | NO | | NULL | |
| lastname | text | NO | | NULL | |
| email | text | NO | | NULL | |
| mobile | text | NO | | NULL | |
| cnic | text | NO | | NULL | |
| srvname | text | NO | | NULL | |
| srvid | int(3) | NO | | NULL | |
| expiration | date | YES | | NULL | |
| mac | varchar(30) | NO | | NULL | |
| bwpkg | varchar(256) | NO | | NULL | |
| pool | varchar(128) | YES | | other | |
| is_enabled | int(1) | NO | | NULL | |
| is_days_expired | int(1) | NO | | NULL | |
| is_qt_expired | int(1) | NO | | NULL | |
| is_uptime_expired | int(1) | NO | | NULL | |
| qt_total | varchar(32) | NO | | NULL | |
| qt_used | varchar(20) | NO | | NULL | |
| uptime_limit | varchar(20) | NO | | NULL | |
| uptime_used | varchar(32) | NO | | NULL | |
| owner | text | NO | | NULL | |
| vlanid | varchar(32) | NO | | NULL | |
| createdon | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------------+--------------+------+-----+-------------------+-----------------------------+

mysql> select * from users;
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+------------+-------------------+-------------+----------------------+------------+-----------------+---------------+-------------------+----------+---------+--------------+-------------+--------+-------------+---------------------+
| id | username | password | firstname | lastname | email | mobile | cnic | srvname | srvid | expiration | mac | bwpkg | pool | is_enabled | is_days_expired | is_qt_expired | is_uptime_expired | qt_total | qt_used | uptime_limit | uptime_used | owner | vlanid | createdon |
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+------------+-------------------+-------------+----------------------+------------+-----------------+---------------+-------------------+----------+---------+--------------+-------------+--------+-------------+---------------------+
| 1 | zaib | zaib | OK | jahanzaib | aacableAThotmail.com | 03333021909 | 1234567890-1-1 | | 0 | 2018-01-04 | 00:0C:29:B9:D8:A0 | 1024k/1024k | public-pool | 1 | 0 | 0 | 0 | 300 | 38980 | | | manager1 | ether1-LAN | 2018-01-17 16:45:05 |

Now we will add the SQL IF statement that will actually check every incoming Authentication request for matching vlan with users table.

Edit Default Sites-Enabled file,

nano /etc/freeradius/sites-enabled/default

& paste following in `Authorize` Section

if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") {
update reply {
Reply-Message = 'Error: You are not allowed to connect form this VLAN !'
}
update control {
Auth-Type := "Reject"
}
}

Save & Exit.

Now reload Freeradius in Debug Mode (by freeradius -X) & monitor the debugging.

If user will connect from another vlan which does not matches the vlanid in user’s table he will get denied with the message.

Note: For testing purposes . I have added dummy entry in user’s vlanid.

rad_recv: Access-Request packet from host 101.11.11.253 port 34175, id=11, length=119
Service-Type = Framed-User
Framed-Protocol = PPP
NAS-Port = 14
NAS-Port-Type = Ethernet
User-Name = "zaib"
Calling-Station-Id = "00:0C:29:B9:D8:A0"
Called-Station-Id = "testppp"
NAS-Port-Id = "ether1-LAN"  # <  select vlanid from users where username = 'zaib'
rlm_sql (sql): Reserving sql socket id: 29
sql_xlat finished
rlm_sql (sql): Released sql socket id: 29
expand: %{sql: select vlanid from users where username = '%{User-Name}'} -> ether1-LAN-DUMMY
expand: %{NAS-Port-Id} -> ether1-LAN
? Evaluating ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") -> TRUE
++? if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") -> TRUE
++if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") {
+++update reply {
+++} # update reply = noop
+++update control {
+++} # update control = noop
++} # if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") = noop

Sending delayed reject for request 0
Sending Access-Reject of id 11 to 101.11.11.253 port 34175

# Meaningful Reply Message by zaib
Reply-Message = "Error: You are not allowed to connect form this VLAN !"

Waking up in 4.9 seconds.
Cleaning up request 0 ID 11 with timestamp +31
Ready to process requests.

& if the users request matches , he will be granted access (off course after all other checks):)

---

2# Limit login by Service Type like PPTP only or PPPoE only

Just to share logic, may be incorrect but In Mikrotik I see following

• PPPoE > NAS-Port-Type = Ethernet
• PPTP  > NAS-Port-Type = Virtual

Now using users table where information is stored, you can then create query to match if user is using the allowed service type.

Users table example:

mysql> describe users;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(10)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(64) | NO   |     | NULL    |                |
| srvtype  | varchar(16) | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

</div>
<div>

mysql> select * from users;
# this type will allow pppoe only
+----+----------+----------+
| id | username | srvtype  |
+----+----------+----------+
|  1 | zaib     | Ethernet |
+----+----------+----------+
1 row in set (0.00 sec)

</div>
<div>

Now edit /etc/freeradius/sites-enabled/default & paste following in `Authorize` Section [look for correct syntax & braces if required]

if ("%{sql: select from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") {
update reply {
Reply-Message = 'Error: You are not allowed to connect form this VLAN !'
}
update control {
Auth-Type := "Reject"
}
}

Now try to connect to users from different type service

(9)   } # if ("%{sql: select srvtype from users where username = '%{User-Name}'}" != "%{NAS-Port-Type}")  = noop
(9)   Reply-Message = "Error: You are not allowed to connect using this service type !"
(9) Found Auth-Type = Reject
(9) Auth-Type = Reject, rejecting user
(9) Failed to authenticate the user

 

---

---