# Example OpenRADIUS schema for MySQL
#
# 2003/04/28 - Created, EvB
# 2003/05/02 - Added groupname and comment columns, EvB
# 2004/01/13 - Added acct_delay_time column, EvB

create database openradius;

use openradius;


# Owner of the database

grant all on openradius.* to openradius@localhost identified by 'openradius';


# Data table containing clients, users, groups, realms, hints, huntgroups,
# whatnot; everything that adds attributes, whether for use in subsequent
# queries, for checking or for use in the reply.

create table data (
    id int not null auto_increment,
    space varchar(8) not null,
    name varchar(64) not null,

    attribute varchar(64) not null,
    value varchar(255),

    groupname varchar(64),
    comment varchar(255),

    primary key (id),
    index space_name (space, name)
);


# Logging table

create table log (
    log_id int not null auto_increment,
  
    log_when datetime,
    log_who varchar(64),
    log_what varchar(255),

    primary key (log_id)
);


# Accounting table
#
# 1. To detect duplicates in Stop records (the only ones you need for metered
# billing) while recording them, lock the accounting table and check that the
# query
#
# select acct_id 
#   from accounting 
#  where acct_nas = ? 
#    and acct_status_type = ? 
#    and acct_sessionid = ? 
#    and acct_timestamp + <dupinterval> >= now()
#
# does not return any records before inserting. The theory behind this is that
# although Acct-Session-Id may collide among NASes and a single NAS may reuse
# the same values quickly, a single NAS' values should be unique over the
# period during which duplicates can be expected.
#
# This period is at most max_retrans_count * timeout, which is typically
# below one minute. Any simplistic NAS' values for Acct-Session-Id should be
# unique within such a short timespan. Even a simple counter that's reset at
# reboot will do as session id that way.
#
# So, we can safely conclude a record is a duplicate -- erring on the safe
# side, i.e. in favour of dropping the record -- if the same NAS generated the
# same Acct-Session-Id in the last minute or two. False negatives are only
# possible if a NAS uses different Session-Ids for the same session, which is
# forbidden by RFC2866.
#
# 2. To match Stop records to Start records (not very useful in most cases, as
# only few broken NASes send information in the Start record that is not
# repeated in the Stop record, and for 'sessions in progress' it's probably
# better to use a separate session table; see below), I'd advise to lock the
# table and use the following statement; do the (standard) insert only if the
# update affected no rows.
#
# update accounting 
#    set acct_status_type = 'Stop',
#        acct_session_time = ?,
#        acct_input_octets = ?,
#        acct_output_octets = ?,
#        acct_input_packets = ?,
#        acct_output_packets = ?,
#  where acct_nas = ?
#    and acct_sessionid = ?
#    and acct_timestamp + ? + 600 >= now()  # use acct_session_time as bind var
#    and acct_timestamp + ? <= now() + 600  # dito
#    and acct_status_type = 'Start'
#    and user_name = ?			    # optional
#    and nas_port = ?			    # optional
#
# The idea is to look back in time, 10 minutes on either side around now() -
# session time, for a start record from the same NAS with the same session id.
# A few extra safety checks prevent overeager matching for NASes that are 
# broken enough to reuse the same session id for a start record within 10
# minutes. Be sure to only use information for this that is known in both the
# Start- and Stop records; some NASes may not send eg. Framed-IP-Address in
# start records, so don't use that.

create table accounting (
    acct_id bigint(22) not null auto_increment,

    acct_nas varchar(32),		    # NAS-IP-Address or NAS-Identifier
    acct_session_id varchar(32),
    acct_timestamp datetime,

    user_name varchar(64),
    nas_ip_address varchar(16),
    nas_port varchar(16),
    service_type varchar(16),
    framed_protocol varchar(16),
    framed_ip_address varchar(16),
    framed_ip_netmask varchar(16),
    login_ip_host varchar(16),
    login_service varchar(16),
    login_tcp_port integer,
    class varchar(16),
    called_station_id varchar(64),
    calling_station_id varchar(64),
    nas_identifier varchar(64),
    nas_port_type varchar(16),
    port_limit integer,

    acct_status_type varchar(16),
    acct_delay_time integer default 0,
    acct_input_octets integer,
    acct_output_octets integer,
    acct_session_time integer,
    acct_input_packets integer,
    acct_output_packets integer,
    acct_terminate_cause varchar(16),
    acct_multi_session_id varchar(64),
    acct_link_count integer,

    primary key (acct_id),
    unique index nassesstime (acct_nas, acct_session_id, acct_timestamp)
);


# Session table
#
# This is useful to track open sessions, for debugging purposes and to limit
# concurrent access for users, calling station, area codes, realms, whatever. 
# I think a separate table is cleaner than overloading the accounting table.
# By keeping session data elsewhere, you can use your accounting table for
# purely billing-related information.
#
# Theory: the session_key is something you choose to identify the session or
# resource for which you want to keep a current counter. It can be a hash of
# nas, user, the full username, just the username suffix, or whatever you need,
# as long as the information is repeated in start, status update and stop
# records.
#
# Each session has a 'check' statement associated with it that may be used to
# verify the actual count using an external module. Most likely, this string
# will have to contain the type of the NAS and the NAS IP address in some way,
# so that your checking module may do the right thing.
#
# Each session can have multiple records with the same session_key; therefore
# you must count the total number of open sessions using sum(opencount). This
# is useful, because this way, each NAS (or even port type!) can have its own
# check statement. Even if you're keeping a large total ports used count across
# a large number of NASes, you can still have a 'checkrad'-like module verify
# it at each individual NAS if it looks like the customer has hit the limits.
#
# When a session is closed, you must decrement the session_count if above 0,
# and you may drop the record if the session_count reaches 0.
#
# Some extra fields are provided for informational purposes only. Of course,
# you can add more if you need them; even Acct-Input-Octets may be useful if
# you have long running sessions and NASes that send status updates.

create table sessions (
    session_id integer not null auto_increment,	    # not related to RADIUS

    session_key varchar(64),
    session_count integer,
    session_check varchar(255),

    user_name varchar(64),
    nas varchar(64),
    nas_port varchar(16),
    nas_port_type varchar(16),
    framed_ip_address varchar(16),
    login_ip_host varchar(16),
    login_tcp_port integer,
    called_station_id varchar(64),
    calling_station_id varchar(64),

    primary key (session_id),
    index (session_key)
);


# Check items table à la FreeRADIUS' radcheck table. Not used by default.
#
#create table radcheck (
#    radcheck_id integer not null auto_increment,
#    space varchar(8) not null,
#    name varchar(64) not null,
#
#    attribute varchar(64) not null,
#    op varchar(2),
#    value varchar(255),
#
#    primary key (radcheck_id),
#    index space_name (space, name)
#);


# Access rights for the server itself

grant usage on openradius.* to radiusd@localhost identified by 'radiusd';

# New for MySQL 4
grant lock tables on openradius.* to radiusd@localhost;

grant select on data to radiusd@localhost;
grant insert on log to radiusd@localhost;
grant select, insert, update on accounting to radiusd@localhost;
grant select, insert, update, delete on sessions to radiusd@localhost;


# Create some test data

insert into data (space, name, attribute, value) 
    values ('clients', '127.0.0.1', 'Secret', 'h1dd3n');

insert into data (space, name, attribute, value)
    values ('users', 'evbergen', 'clear-password', 'welcome1');
insert into data (space, name, attribute, value)
    values ('users', 'evbergen', 'Framed-IP-Address', '172.31.1.1');
insert into data (space, name, attribute, value)
    values ('users', 'evbergen', 'Service-Type', 'Framed');

insert into data (space, name, attribute, value) 
    values ('users', 'yvonne', 'md5-hex-password', 
    	    'dh1L$e1ea87440827ca6e32a74da4cd204eb6');