3.37. <AuthBy SQL> Previous topic Parent topic Child topic Next topic

<AuthBy SQL> authenticates users from an SQL database, and stores accounting records to an SQL database. It is implemented in AuthSQL.pm. <AuthBy SQL> is very powerful and configurable, and it has many parameters in order to customise its behaviour. You need to have some familiarity with SQL and relational databases in order to configure and use <AuthBy SQL>.
<AuthBy SQL> uses the Perl DBI/DBD interface to connect to your database. You can therefore use <AuthBy SQL> with a large number of commercial and free SQL database systems. In order to use SQL, you will need to install your database software, install the matching Perl DBD module, and install the Perl DBI module before <AuthBy SQL> works.
When <AuthBy SQL> receives an Access-Request message, it tries to find a password and check and reply items for the user in a database table (you can change this behaviour with the AuthColumnDef parameter). Radiator constructs an SQL select statement from the AuthSelect parameter. By changing AuthSelect, you can control the table it looks in, and the names of the columns for the password, check and reply columns. If the user is found, all the check items are compared with the attributes in the request, including Expiration and other special check items. For more information about check and reply items, see Section 7. Check and reply items.
If all the check items are satisfied by the attributes in the request, <AuthBy SQL> replies with an Access-Accept message containing all the attributes in the reply items. If the user does not appear in the database, or if any check attribute does not match, an Access-Reject message is sent to the client.
If your AuthSelect statement does not generate a simple password, check items, and reply items as a result, you can configure Radiator how to interpret the columns in the result with the AuthColumnDef parameter. If you do not specify any AuthColumnDef parameters, Radiator assumes that AuthSelect returns password, check items, and reply items in that order.
When <AuthBy SQL> receives an Accounting-Request message, it can store any number of the attributes from the request in an SQL table. You can control the table it stores in, and the names of the columns where the attributes are stored, and the attribute that is stored there. To enable SQL accounting you must define AccountingTable and you must define at least one AcctColumnDef. If you do not do both of these <AuthBy SQL> acknowledges Accounting-Request message but will not store them anywhere. The example goodies/sql.cfg in the Radiator distribution shows a typical setup that will work with the table schemas in goodies/*Create.sql files.
Some example scripts for constructing database tables for various RDBMSs can be found in the goodies/*Create.sql files in the Radiator distribution. Regard these as a starting point for constructing large scalable user and accounting databases.
<AuthBy SQL> understands also the same parameters as <AuthBy xxxxxx>. For more information, see Section 3.28. <AuthBy xxxxxx>. It supports also all the common SQL configuration parameters. For more information about the SQL configuration parameters, see Section 3.8. SQL configuration.