Syed Jahanzaib Personal Blog to Share Knowledge !

January 18, 2018

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

Filed under: freeradius — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 8:48 AM

FreeRadius Core info

restrcited vlan.jpg

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:

p4.jpg

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"  # < < < <  Look at this, this is the VLAN id user is coming from, this will be matched with the vlanid we add in the users table
User-Password = "zaib"
NAS-Identifier = "Win"
NAS-IP-Address = 101.11.11.253
# Executing section authorize from file /etc/freeradius/sites-enabled/default
+group authorize {
++[preprocess] = ok

++? if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}")
sql_xlat
expand: %{User-Name} -> zaib
sql_set_user escaped user --> 'zaib'
expand: select vlanid from users where username = '%{User-Name}' -> 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):)


 

p4-2.jpg

2 Comments »

  1. […] FREERADIUS WITH MIKROTIK – Part #9 – Restrict user auth from allowed VLAN only […]

    Like

    Pingback by Mikrotik with Freeradius/mySQL # Part-1 | Syed Jahanzaib Personal Blog to Share Knowledge ! — January 18, 2018 @ 8:48 AM

  2. thank you MR syed for your great tutorial but I have one question and I wish to have help from you .
    I want to get all web access logs for any user ,I searched alote about this without result .
    can u help me with this point please

    Like

    Comment by starking123engMuhammad — January 22, 2018 @ 6:02 PM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: