Adding and removing users from the FreeRadius Database (MySQL)

In this tutorial I will show you how to add and remove users from the radius database.  It’s a pretty easy task and yes I am even including a script like usual to make it easy for those of you who are not ‘lazy’ just time poor 😉

This is a very popular topic based on visitor statistics! If you need more information or wish to improve upon the content let me know in the comments section.

Okay, before we begin we need:

  • A VPS with FreeRadius / MySQL installed
    If you need to set this up still, take a quick look here for how to set one up in a hurry: Install FreeRadius on CentOS 5 or 6 in just 3 commands!
    Or if you want to learn a little while you set one up then take a look at this How To here:  Installing FreeRadius on CentOS 5/6 or Ubuntu 11
  • The MySQL root password
    Or any account that has the appropriate permissions, to keep it simple though during this tutorial we will use root, you can use whatever account you feel will do the trick though if you like.
  • Some user accounts you want to setup in FreeRadius
    Write down a few usernames and passwords to go with to use during this tutorial and have them ready because we will need them soon.
  • An active putty session already logged into your server, ready to go!
    You can use whatever shell utility you like, but throughout this tutorial we will reference ‘putty’ which is available from here if you would like to download it: http://www.putty.org/
  • About 10-15 minutes of free time to do stuff
    Like I said it’s pretty easy however you might need a little background knowledge of MySQL. However if you managed to install FreeRadius previously you will probably be alright! 🙂

A little about FreeRadius’s MySQL Database

When FreeRadius is used in conjunction with MySQL (most common practice I dare say) it will use a database usually called ‘radius’ and within that database there is a database table called ‘radcheck’. This table is the table we need to interact with as it is the one that contains all the user accounts that can be authenticated with FreeRadius.

It’s important to remember that like a lot of things you can choose what usernames something uses, what the database is called for something and you can even choose to use a remote MySQL server! However for this tutorial we will assume that MySQL and FreeRadius exist on the same server, and that the database is called ‘radius’ and the user account we will use with MySQL is root.

Adding a user account to ‘radcheck’

First connect to your database using the below, if your username is not root, then substitute root in the command for your username.

mysql -uroot -p

Then we need to switch to the radius database, so go ahead and enter in the following at the MySQL prompt:

use radius;

Remember to terminate your MySQL queries with a semi colon!

Now we will run our insert command to add a user. For this example the user we will be adding is Beyonce, and she will have the following login details:
Username: beyonce
Password: putaringonit

The insert code is like the following, I have highlighted the username part we need to replace in red and the password part we need to replace in blue.

INSERT INTO radcheck (id, username, attribute, op, value) VALUES (1,’myusername‘,’User-Password’,’:=’,’mypassword‘);

So here is the code we will run add Beyonce to our FreeRadius server:

INSERT INTO radcheck (id, username, attribute, op, value) VALUES (1,'beyonce','User-Password',':=','putaringonit');

When we run that in the MySQL prompt we should get no errors and Beyonce is now able to log on to our FreeRadius server.

Removing a user account from radcheck

Unfortunately Beyonce has decided to leave our FreeRadius server and so we need to delete her account.

There are a few different ways to target her record in radcheck however we’re going to use her username for this example, but you could also use the id column.

Here is the code to delete a record from radcheck, I have again highlighted the username part we need to change in red.

delete from radcheck where username = ‘myusername

Now, for our example of deleting Beyonce’s login from the database we will do the following:

delete from radcheck where username = 'beyonce'

And we should see from the console output that MySQL has removed her record from radcheck, so she will not be able to pass authentication anymore

Deleting – Afterthoughts…

There are probably times when you want to disable or delete (but keep a record of past user accounts), and if so you will need to think of a solution that is best for you. Generally FreeRadius is used as an endpoint for information, normally there is a greater system in play that sits above FreeRadius that manages user accounts such as WHMCS for example.

It’s also important to remember that deleting someone out of radclient doesn’t kick them out of whatever service they are currently logged into at the time! FreeRadius merely says yes or no at the point of authentication, it doesn’t keep tabs of the user’s session and it doesn’t report back to services to say that someone has been disabled or deleted! So you need to consider how you will manage active sessions with your services for users you want to delete or suspend.

Securing your new MySQL installation with /usr/bin/mysql_secure_installation

After you install MySQL you need to secure it. By default MySQL’s account for ‘root’ does not have a password associated for it, and the server also includes a demo database and all the permissions associated with it. If this server is going to be public or used for anything remotely serious we need to beef up security a bit.

And thats where mysql_secure_installation comes in.

Let’s begin!

After MySQL is installed it will prompt you to run the following command, so that is what we are going to do now!

/usr/bin/mysql_secure_installation

Then you should be greeted by a message asking you to enter the current root password, as there is no current root password we can just hit enter!

Next it will ask us if we want to set a password for root, to this we should say yes!

Now it will ask us for a new password, and then ask us to confirm it, go ahead!

Great now you should see the following success messages and then it should ask us if we want to remove anonymous users, yes we do!

Next it will ask us if we want to allow or disallow root to login remotely, ideally you shouldn’t allow this but there are circumstances where you may want to allow it. In this tutorial we’re going to say we don’t want root to be able to login from anywhere other than localhost, but if your preference is otherwise please google up some information about the subject first! 🙂

Next it will ask us if we want to delete the test database and associated permissions – yes we want to do this!

Lastly it will ask if we want to reload the privilage table, which means our settings will come into immediate effect, to this we will again say yes!

And you’re done! You should see the following message:

This means your server is now post install secure, congrats!