FreeRADIUS User Group Management with MySQL
FreeRADIUS is a high performance RADIUS Server. A number of network devices can be used with freeRADIUS Server as a RADIUS Client including MikroTik Router. As a modular RADIUS suite, freeRADIUS accepts MySQL module to query user authentication and authorization and to store accounting data. In my previous article, I discussed how to install and configure FreeRADIUS MySQL module. I also discussed how to authenticate MikroTik Login with freeRADIUS MySQL user. In this article, we will talk about how to configure user group in MySQL module to categorize freeRADIUS user’s reply attributes.
FreeRADIUS User Group
Group is used to categorize some attributes those will be applied on some users. In freeRADIUS, these attribute are referred to as AVP. So, before going to configure user group, we should have clear concept on freeRADIUS AVP.
Attribute Value Pair (AVP)
AVP is the workhorse of the RADIUS Protocol. Communication between a RADIUS Server and RADIUS Client is done based on AVPs. AVP can be categorized either check or reply attributes. Check attributes are sent from the client to the server. Reply attributes are sent from the server to the client.
Attributes serve as carriers of information between the RADIUS Client and Server. The RADIUS Client supplies information about itself as well as the user connecting through it using AVP. The RADIUS Server also uses AVP to respond to the client. The Client can then use this response to control the user’s connection based on the AVPs received in the server’s response.
Each RADIUS client device (NAS) such as MikroTik Router has its own attributes known as Vendor-Specific Attributes (VSAs) that are used in Access-Request and Access-Accept packets. For example, when a MikroTik Router wants to authenticate a user from freeRADIUS Server, it sends Access-Request packets with User-Name and User-Password AVPs. If freeRADIUS is able to authenticate this user, it replies with defined reply attributes (such as MikroTik-Group) for this user. The mapping between Server and Client attributes communication is done by Dictionary. The Dictionary for the different vendors is loaded from /usr/share/freeradius directory when freeRADIUS starts.
The freeRADIUS Server also has its own attributes which are known as freeRADIUS internal Dictionary. These attributes are used to perform internal process of freeRADIUS Server and cannot be used by the RADIUS client. For example, Cleartext-Password AVP is used to assign user password and User-Profile is used to create user profile in freeRADIUS and these both attributes are freeRADIUS internal attribute.
FreeRADIUS Group Management with MySQL Database
The SQL database allows defining check and reply attributes for groups. The check attributes are stored in radgroupcheck table and the reply attributes are stored in radgroupreply table.
A Radius user can be assigned to zero or more groups. Groups are assigned through radusergroup table. An entry into this table specifies a priority of a certain group to a user. This allows certain item values in groups with higher priorities (smaller values) to override item values in groups with lower priorities (larger values).
With this in mind, we will do a simple practical example for MikroTik Router. We will create groups for MikroTik Router’s login users. We know that there are there permission levels (full, write and read) for MikroTik login user. So, we will create there groups (fullaccess, writeaccess and readaccess) in our SQL database and assign users to these groups. When a user will be authenticated to login MikroTik Router, he/she will get his permission from radgroupreply table.
How to configure MySQL (MariaDB) database with freeRADIUS was discussed in previous article. If you are new in freeRADIUS with MySQL database, spend time to configure freeRADIUS with MySQL and then follow the below section to configure freeRADIUS user group.
Insert Group Reply AVP in radgroupreply Table
Group reply AVP is stored in radgroupreply table. We will insert MikroTik-Group AVP that controls MikroTik Login user group in this table. We know that MikroTik Router has three permission levels for login user. So, we will create three groups in radgroupreply table and assign group permission with MikroTik-Group AVP. The following steps will show how to insert group reply AVP in radgroupreply table.
- Login to your freeRADIUS server with root user.
- Now login to your MariaDB server and select radius database.
[root@freeradius ~]# mysql -uroot -pPasskey85 radius
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 24
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [radius]>
- To show available columns in radgroupreply table, issue the following command.
MariaDB [radius]> show columns from radgroupreply;
+———–+——————+——+—–+———+—————-+| Field | Type | Null | Key | Default | Extra |
+———–+——————+——+—–+———+—————-+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| groupname | varchar(64) | NO | MUL | | |
| attribute | varchar(64) | NO | | | |
| op | char(2) | NO | | = | |
| value | varchar(253) | NO | | | |
+———–+——————+——+—–+———+—————-+
- To create full permission group, issue the below command.MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“fullaccess”,”Mikrotik-Group”,”:=”,”full”);
- To create write permission group, issue the following command.MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“writeaccess”,”Mikrotik-Group”,”:=”,”write”);
- To create read permission group, issue the following command.MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“readaccess”,”Mikrotik-Group”,”:=”,”read”);
- To show all entries in radgroupreply table, issue the follow command.
MariaDB [radius]> select * from radgroupreply;
+—-+————-+—————-+—-+——-+| id | groupname | attribute | op | value |
+—-+————-+—————-+—-+——-+
| 2 | fullaccess | Mikrotik-Group | := | full |
| 3 | writeaccess | Mikrotik-Group | := | write |
| 4 | readaccess | Mikrotik-Group | := | read |
+—-+————-+—————-+—-+——-+
Three groups with reply attribute have been created successfully. Now we will create three users in freeRADIUS MySQL database.
Insert freeRADIUS User in radcheck Table
FreeRADIUS user login information is stored in radcheck table. So, we will now insert three user entries (bob, alice and tom) who will login to MikroTik Router in this table. The following steps will show how to insert user information in radcheck table.
- To show available columns in radcheck table, issue the following command.
MariaDB [radius]> show columns from radcheck;
+———–+——————+——+—–+———+—————-+| Field | Type | Null | Key | Default | Extra |
+———–+——————+——+—–+———+—————-+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(64) | NO | MUL | | |
| attribute | varchar(64) | NO | | | |
| op | char(2) | NO | | == | |
| value | varchar(253) | NO | | | |
+———–+——————+——+—–+———+—————-+
- To insert bob user, issue the following command.MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“bob”,”Cleartext-Password”,”:=”,”passme”);
- To insert alice user, issue the following command.
MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“alice”,”Cleartext-Password”,”:=”,”passme”);
- To insert tom user, issue the following command.MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“tom”,”Cleartext-Password”,”:=”,”passme”);
- To show all entries in radcheck table, issue the following command.
MariaDB [radius]> select * from radcheck;
+—-+———-+——————–+—-+——–+| id | username | attribute | op | value |
+—-+———-+——————–+—-+——–+
| 2 | bob | Cleartext-Password | := | passme |
| 3 | alice | Cleartext-Password | := | passme |
| 4 | tom | Cleartext-Password | := | passme |
+—-+———-+——————–+—-+——–+
FreeRADIUS user creation in database table has been completed. Now we will assign these users to group.
Assigning User to Group
User and group mapping is stored in radusergroup table. So, to assign user to a group, we have to put entry in this table. The following steps will show how to map user to group with radusergroup table.
- To show available columns in radusergroup table, issue the following command.
MariaDB [radius]> show columns from radusergroup;
+———–+————-+——+—–+———+——-+| Field | Type | Null | Key | Default | Extra |
+———–+————-+——+—–+———+——-+
| username | varchar(64) | NO | MUL | | |
| groupname | varchar(64) | NO | | | |
| priority | int(11) | NO | | 1 | |
+———–+————-+——+—–+———+——-+
- To map bob user to fullaccess group, issue the following command.MariaDB [radius]> insert into radusergroup (username,groupname,priority) values (“bob”,”fullaccess”,10);
- To map alice user to writeaccess group, issue the following group.MariaDB [radius]> insert into radusergroup (username,groupname,priority) values (“alice”,”writeaccess”,10);
- To map tom user to read group, issue the following command.MariaDB [radius]> insert into radusergroup (username,groupname,priority) values (“tom”,”readaccess”,10);
- To view all entries, issue the following command.
MariaDB [radius]> select * from radusergroup;
+———-+————-+———-+| username | groupname | priority |
+———-+————-+———-+
| bob | fullaccess | 10 |
| alice | writeaccess | 10 |
| tom | readaccess | 10 |
+———-+————-+———-+
Assigning user to group has been completed. Now we will first test user group reply with radtest program and then MikroTik login user with Winbox.
Testing User Group with radtest Program
After user group configuration in freeRADIUS MySQL database, we will now test user group reply with radtest program. So, issue the following command to check bob user login with reply AVP.
[root@freeradius ~]# radtest bob passme 127.0.0.1 100 testing123
Sent Access-Request Id 74 from 0.0.0.0:47585 to 127.0.0.1:1812 length 73
User-Name = “bob”
User-Password = “passme”
NAS-IP-Address = 192.168.40.10
NAS-Port = 100
Message-Authenticator = 0x00
Cleartext-Password = “passme”
Received Access-Accept Id 74 from 127.0.0.1:1812 to 0.0.0.0:0 length 32
Mikrotik-Group = “full”
If everything is OK, the radtest program will show the above output. That means, bob user is able to login with username and password and after successful login he will get full permission in MikroTik Router.
Testing User Group with MikroTik Router
Now we will test freeRADIUS user group with our desired MikroTik Router. How to configure MikroTik Radius to connect with freeRADIUS was discussed in my previous article. If you are new in MikroTik Radius with freeRADIUS server, follow that article to configure your MikroTik Router with freeRADIUS server and then issue the following steps to test freeRADIUS user group with MikroTik Router.
- Open Winbox software and put your MikroTik Router IP in Connect To input field and put bob username in Login input filed and then put bob password (passme) in Password input field. Now click on Connect button and if everything is OK, you will be able to login to MikroTik Router.
Similarly you can login with alice and tom and find their permission level provided in freeRADIUS group configuration.
If you face any confusion to follow above steps properly, watch the below video about freeRADIUS user group management. I hope it will reduce your any confusion.
How to configure freeRADIUS user group has been discussed in this article. I hope you will now be able to configure your freeRADIUS group according to your desire requirement. However, if you face any confusion, feel free to discuss in comment or contact with me from Contact page. I will try my best to stay with you. In the next article, I will discuss how to configure freeRADIUS user profile to manage MikroTik PPPoE user login and their bandwidth. So, keep tuned with System Zone.
Why not a Cup of COFFEE if the solution?
