Rev 3202 | Blame | Compare with Previous | Last modification | View Log
-----------------------------------------
--
-- Database radius schema for MariaDB
--
-- Adapted by Rexy for ALCASAR NAC
--
-----------------------------------------
--
-- Table structure for table `radacct`
--
DROP TABLE IF EXISTS radacct;
CREATE TABLE radacct (
radacctid bigint(21) NOT NULL AUTO_INCREMENT,
acctsessionid varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
acctuniqueid varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
username varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
realm varchar(64) COLLATE utf8_bin DEFAULT '',
nasipaddress varchar(15) COLLATE utf8_bin NOT NULL DEFAULT '',
nasportid varchar(32) COLLATE utf8_bin DEFAULT NULL,
nasporttype varchar(32) COLLATE utf8_bin DEFAULT NULL,
acctstarttime datetime DEFAULT NULL,
acctupdatetime datetime DEFAULT NULL,
acctstoptime datetime DEFAULT NULL,
acctinterval int(12) DEFAULT NULL,
acctsessiontime int(12) DEFAULT NULL,
acctauthentic varchar(32) COLLATE utf8_bin DEFAULT NULL,
connectinfo_start varchar(128) COLLATE utf8_bin DEFAULT NULL,
connectinfo_stop varchar(128) COLLATE utf8_bin DEFAULT NULL,
acctinputoctets bigint(20) DEFAULT NULL,
acctoutputoctets bigint(20) DEFAULT NULL,
calledstationid varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '',
callingstationid varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '',
acctterminatecause varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
servicetype varchar(32) COLLATE utf8_bin DEFAULT NULL,
framedprotocol varchar(32) COLLATE utf8_bin DEFAULT NULL,
framedipaddress varchar(15) COLLATE utf8_bin NOT NULL DEFAULT '',
framedipv6address varchar(45) COLLATE utf8_bin NOT NULL DEFAULT '',
framedipv6prefix varchar(45) COLLATE utf8_bin NOT NULL DEFAULT '',
framedinterfaceid varchar(45) COLLATE utf8_bin NOT NULL DEFAULT '',
delegatedipv6prefix varchar(45) COLLATE utf8_bin NOT NULL DEFAULT '',
class varchar(64) DEFAULT NULL,
PRIMARY KEY (radacctid),
UNIQUE KEY acctuniqueid (acctuniqueid),
KEY username (username),
KEY framedipaddress (framedipaddress),
KEY framedipv6address (framedipv6address),
KEY framedipv6prefix (framedipv6prefix),
KEY framedinterfaceid (framedinterfaceid),
KEY delegatedipv6prefix (delegatedipv6prefix),
KEY acctsessionid (acctsessionid),
KEY acctsessiontime (acctsessiontime),
KEY acctstarttime (acctstarttime),
KEY acctinterval (acctinterval),
KEY acctstoptime (acctstoptime),
KEY nasipaddress (nasipaddress),
KEY class (class)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--
-- Table structure for table `radcheck`
--
DROP TABLE IF EXISTS radcheck;
CREATE TABLE radcheck (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
username varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
attribute varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
op char(2) COLLATE utf8_bin NOT NULL DEFAULT '==',
value varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--
-- Table structure for table `radgroupcheck`
--
DROP TABLE IF EXISTS radgroupcheck;
CREATE TABLE radgroupcheck (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
groupname varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
attribute varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
op char(2) COLLATE utf8_bin NOT NULL DEFAULT '==',
value varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY groupname (groupname(32))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--
-- Table structure for table `radgroupreply`
--
DROP TABLE IF EXISTS radgroupreply;
CREATE TABLE radgroupreply (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
groupname varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
attribute varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
op char(2) COLLATE utf8_bin NOT NULL DEFAULT '=',
value varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY groupname (groupname(32))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--
-- Table structure for table `radreply`
--
DROP TABLE IF EXISTS radreply;
CREATE TABLE radreply (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
username varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
attribute varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
op char(2) COLLATE utf8_bin NOT NULL DEFAULT '=',
value varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY username (username(32))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--
-- Table structure for table `radusergroup`
--
DROP TABLE IF EXISTS radusergroup;
CREATE TABLE radusergroup (
username varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
groupname varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
priority int(11) NOT NULL DEFAULT '1',
KEY username (username(32))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--
-- Table structure for table `radpostauth`
--
DROP TABLE IF EXISTS radpostauth;
CREATE TABLE radpostauth (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
pass varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
reply varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
authdate timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
class varchar(64) DEFAULT NULL,
PRIMARY KEY (id),
KEY username (username),
KEY class (class)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--
-- Table structure for table `nas`
--
DROP TABLE IF EXISTS nas;
CREATE TABLE nas (
id int(10) NOT NULL AUTO_INCREMENT,
nasname varchar(128) NOT NULL,
shortname varchar(32),
type varchar(30) DEFAULT 'other',
ports int(5),
secret varchar(60) DEFAULT 'secret' NOT NULL,
server varchar(64),
community varchar(50),
description varchar(200) DEFAULT 'RADIUS Client',
PRIMARY KEY (id),
KEY nasname (nasname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--
-- Table structure for table `mtotacct`
--
DROP TABLE IF EXISTS mtotacct;
CREATE TABLE mtotacct (
mtotacctid bigint(21) NOT NULL AUTO_INCREMENT,
username varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
acctdate date NOT NULL DEFAULT '0000-00-00',
connnum bigint(12) DEFAULT NULL,
conntotduration bigint(12) DEFAULT NULL,
connmaxduration bigint(12) DEFAULT NULL,
connminduration bigint(12) DEFAULT NULL,
inputoctets bigint(12) DEFAULT NULL,
outputoctets bigint(12) DEFAULT NULL,
nasipaddress varchar(15) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (mtotacctid),
KEY username (username),
KEY acctdate (acctdate),
KEY UserOnDate (username,acctdate),
KEY nasipaddress (nasipaddress)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--
-- Table structure for table `totacct`
--
DROP TABLE IF EXISTS totacct;
CREATE TABLE totacct (
totacctid bigint(21) NOT NULL AUTO_INCREMENT,
username varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
acctdate date NOT NULL DEFAULT '0000-00-00',
connnum bigint(12) DEFAULT NULL,
conntotduration bigint(12) DEFAULT NULL,
connmaxduration bigint(12) DEFAULT NULL,
connminduration bigint(12) DEFAULT NULL,
inputoctets bigint(12) DEFAULT NULL,
outputoctets bigint(12) DEFAULT NULL,
nasipaddress varchar(15) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (totacctid),
KEY username (username),
KEY acctdate (acctdate),
KEY UserOnDate (username,acctdate),
KEY nasipaddress (nasipaddress),
KEY nasipaddressOnDate (acctdate,nasipaddress)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--
-- Table structure for table `userinfo`
--
DROP TABLE IF EXISTS userinfo;
CREATE TABLE userinfo (
id int(10) NOT NULL AUTO_INCREMENT,
username varchar(64) COLLATE utf8_bin DEFAULT NULL,
name varchar(200) COLLATE utf8_bin DEFAULT NULL,
mail varchar(200) COLLATE utf8_bin DEFAULT NULL,
department varchar(200) COLLATE utf8_bin DEFAULT NULL,
workphone varchar(200) COLLATE utf8_bin DEFAULT NULL,
homephone varchar(200) COLLATE utf8_bin DEFAULT NULL,
mobile varchar(200) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (id),
KEY username (username),
KEY department (department)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;