Subversion Repositories ALCASAR

Rev

Rev 3202 | Details | Compare with Previous | Last modification | View Log

Rev Author Line No. Line
3278 rexy 1 
-----------------------------------------
1800 richard 2 
--
3278 rexy 3 
-- Database radius schema for MariaDB
4 
--
5 
--  Adapted by Rexy for ALCASAR NAC
6 
--
7 
-----------------------------------------
8 
 
9 
--
1800 richard 10 
-- Table structure for table `radacct`
11 
--
12 
 
3278 rexy 13 
DROP TABLE IF EXISTS radacct;
14 
CREATE TABLE radacct (
15 
  radacctid            bigint(21)   NOT NULL AUTO_INCREMENT,
16 
  acctsessionid        varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
17 
  acctuniqueid         varchar(32)  COLLATE utf8_bin NOT NULL DEFAULT '',
18 
  username             varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
19 
  realm                varchar(64)  COLLATE utf8_bin DEFAULT '',
20 
  nasipaddress         varchar(15)  COLLATE utf8_bin NOT NULL DEFAULT '',
21 
  nasportid            varchar(32)  COLLATE utf8_bin DEFAULT NULL,
22 
  nasporttype          varchar(32)  COLLATE utf8_bin DEFAULT NULL,
23 
  acctstarttime        datetime     DEFAULT NULL,
24 
  acctupdatetime       datetime     DEFAULT NULL,
25 
  acctstoptime         datetime     DEFAULT NULL,
26 
  acctinterval         int(12)      DEFAULT NULL,
27 
  acctsessiontime      int(12)      DEFAULT NULL,
28 
  acctauthentic        varchar(32)  COLLATE utf8_bin DEFAULT NULL,
29 
  connectinfo_start    varchar(128) COLLATE utf8_bin DEFAULT NULL,
30 
  connectinfo_stop     varchar(128) COLLATE utf8_bin DEFAULT NULL,
31 
  acctinputoctets      bigint(20)   DEFAULT NULL,
32 
  acctoutputoctets     bigint(20)   DEFAULT NULL,
33 
  calledstationid      varchar(50)  COLLATE utf8_bin NOT NULL DEFAULT '',
34 
  callingstationid     varchar(50)  COLLATE utf8_bin NOT NULL DEFAULT '',
35 
  acctterminatecause   varchar(32)  COLLATE utf8_bin NOT NULL DEFAULT '',
36 
  servicetype          varchar(32)  COLLATE utf8_bin DEFAULT NULL,
37 
  framedprotocol       varchar(32)  COLLATE utf8_bin DEFAULT NULL,
38 
  framedipaddress      varchar(15)  COLLATE utf8_bin NOT NULL DEFAULT '',
39 
  framedipv6address    varchar(45)  COLLATE utf8_bin NOT NULL DEFAULT '',
40 
  framedipv6prefix     varchar(45)  COLLATE utf8_bin NOT NULL DEFAULT '',
41 
  framedinterfaceid    varchar(45)  COLLATE utf8_bin NOT NULL DEFAULT '',
42 
  delegatedipv6prefix  varchar(45)  COLLATE utf8_bin NOT NULL DEFAULT '',
43 
  class                varchar(64)  DEFAULT NULL,
44 
  PRIMARY KEY (radacctid),
45 
  UNIQUE KEY acctuniqueid (acctuniqueid),
46 
  KEY username (username),
47 
  KEY framedipaddress (framedipaddress),
48 
  KEY framedipv6address (framedipv6address),
49 
  KEY framedipv6prefix (framedipv6prefix),
50 
  KEY framedinterfaceid (framedinterfaceid),
51 
  KEY delegatedipv6prefix (delegatedipv6prefix),
52 
  KEY acctsessionid (acctsessionid),
53 
  KEY acctsessiontime (acctsessiontime),
54 
  KEY acctstarttime (acctstarttime),
55 
  KEY acctinterval (acctinterval),
56 
  KEY acctstoptime (acctstoptime),
57 
  KEY nasipaddress (nasipaddress),
58 
  KEY class (class)
2371 tom.houday 59 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
1800 richard 60 
 
61 
--
62 
-- Table structure for table `radcheck`
63 
--
64 
 
3278 rexy 65 
DROP TABLE IF EXISTS radcheck;
66 
CREATE TABLE radcheck (
67 
  id        int(11)      unsigned NOT NULL AUTO_INCREMENT,
68 
  username  varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
69 
  attribute varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
70 
  op        char(2)      COLLATE utf8_bin NOT NULL DEFAULT '==',
71 
  value     varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
72 
  PRIMARY KEY (id),
73 
  KEY username (username)
2371 tom.houday 74 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
1800 richard 75 
 
76 
--
77 
-- Table structure for table `radgroupcheck`
78 
--
79 
 
3278 rexy 80 
DROP TABLE IF EXISTS radgroupcheck;
81 
CREATE TABLE radgroupcheck (
82 
  id        int(11)      unsigned NOT NULL AUTO_INCREMENT,
83 
  groupname varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
84 
  attribute varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
85 
  op        char(2)      COLLATE utf8_bin NOT NULL DEFAULT '==',
86 
  value     varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
87 
  PRIMARY KEY (id),
88 
  KEY groupname (groupname(32))
2372 tom.houday 89 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
1800 richard 90 
 
91 
--
92 
-- Table structure for table `radgroupreply`
93 
--
94 
 
3278 rexy 95 
DROP TABLE IF EXISTS radgroupreply;
96 
CREATE TABLE radgroupreply (
97 
  id        int(11)      unsigned NOT NULL AUTO_INCREMENT,
98 
  groupname varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
99 
  attribute varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
100 
  op        char(2)      COLLATE utf8_bin NOT NULL DEFAULT '=',
101 
  value     varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
102 
  PRIMARY KEY (id),
103 
  KEY groupname (groupname(32))
2371 tom.houday 104 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
1800 richard 105 
 
106 
--
107 
-- Table structure for table `radreply`
108 
--
109 
 
3278 rexy 110 
DROP TABLE IF EXISTS radreply;
111 
CREATE TABLE radreply (
112 
  id        int(11)      unsigned NOT NULL AUTO_INCREMENT,
113 
  username  varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
114 
  attribute varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
115 
  op        char(2)      COLLATE utf8_bin NOT NULL DEFAULT '=',
116 
  value     varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
117 
  PRIMARY KEY (id),
118 
  KEY username (username(32))
2371 tom.houday 119 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
1800 richard 120 
 
121 
--
122 
-- Table structure for table `radusergroup`
123 
--
124 
 
3278 rexy 125 
DROP TABLE IF EXISTS radusergroup;
126 
CREATE TABLE radusergroup (
127 
  username  varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
128 
  groupname varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
129 
  priority  int(11)     NOT NULL DEFAULT '1',
130 
  KEY username (username(32))
2371 tom.houday 131 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
1800 richard 132 
 
133 
--
2372 tom.houday 134 
-- Table structure for table `radpostauth`
135 
--
136 
 
3278 rexy 137 
DROP TABLE IF EXISTS radpostauth;
138 
CREATE TABLE radpostauth (
139 
  id       int(11)      NOT NULL AUTO_INCREMENT,
140 
  username varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
141 
  pass     varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
142 
  reply    varchar(32)  COLLATE utf8_bin NOT NULL DEFAULT '',
143 
  authdate timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
144 
  class    varchar(64)  DEFAULT NULL,
145 
  PRIMARY KEY (id),
146 
  KEY username (username),
147 
  KEY class (class)
2372 tom.houday 148 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
149 
 
150 
--
3202 rexy 151 
-- Table structure for table `nas`
152 
--
153 
 
3278 rexy 154 
DROP TABLE IF EXISTS nas;
155 
CREATE TABLE nas (
156 
  id          int(10)      NOT NULL AUTO_INCREMENT,
157 
  nasname     varchar(128) NOT NULL,
158 
  shortname   varchar(32),
159 
  type        varchar(30)  DEFAULT 'other',
160 
  ports       int(5),
161 
  secret      varchar(60)  DEFAULT 'secret' NOT NULL,
162 
  server      varchar(64),
163 
  community   varchar(50),
164 
  description varchar(200) DEFAULT 'RADIUS Client',
3202 rexy 165 
  PRIMARY KEY (id),
3278 rexy 166 
  KEY nasname (nasname)
167 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
3202 rexy 168 
 
169 
--
2372 tom.houday 170 
-- Table structure for table `mtotacct`
171 
--
172 
 
3278 rexy 173 
DROP TABLE IF EXISTS mtotacct;
174 
CREATE TABLE mtotacct (
175 
  mtotacctid      bigint(21)  NOT NULL AUTO_INCREMENT,
176 
  username        varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
177 
  acctdate        date        NOT NULL DEFAULT '0000-00-00',
178 
  connnum         bigint(12)  DEFAULT NULL,
179 
  conntotduration bigint(12)  DEFAULT NULL,
180 
  connmaxduration bigint(12)  DEFAULT NULL,
181 
  connminduration bigint(12)  DEFAULT NULL,
182 
  inputoctets     bigint(12)  DEFAULT NULL,
183 
  outputoctets    bigint(12)  DEFAULT NULL,
184 
  nasipaddress    varchar(15) COLLATE utf8_bin DEFAULT NULL,
185 
  PRIMARY KEY (mtotacctid),
186 
  KEY username (username),
187 
  KEY acctdate (acctdate),
188 
  KEY UserOnDate (username,acctdate),
189 
  KEY nasipaddress (nasipaddress)
2372 tom.houday 190 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
191 
 
192 
--
1800 richard 193 
-- Table structure for table `totacct`
194 
--
195 
 
3278 rexy 196 
DROP TABLE IF EXISTS totacct;
197 
CREATE TABLE totacct (
198 
  totacctid       bigint(21)  NOT NULL AUTO_INCREMENT,
199 
  username        varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
200 
  acctdate        date        NOT NULL DEFAULT '0000-00-00',
201 
  connnum         bigint(12)  DEFAULT NULL,
202 
  conntotduration bigint(12)  DEFAULT NULL,
203 
  connmaxduration bigint(12)  DEFAULT NULL,
204 
  connminduration bigint(12)  DEFAULT NULL,
205 
  inputoctets     bigint(12)  DEFAULT NULL,
206 
  outputoctets    bigint(12)  DEFAULT NULL,
207 
  nasipaddress    varchar(15) COLLATE utf8_bin DEFAULT NULL,
208 
  PRIMARY KEY (totacctid),
209 
  KEY username (username),
210 
  KEY acctdate (acctdate),
211 
  KEY UserOnDate (username,acctdate),
212 
  KEY nasipaddress (nasipaddress),
213 
  KEY nasipaddressOnDate (acctdate,nasipaddress)
2371 tom.houday 214 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
1800 richard 215 
 
216 
--
217 
-- Table structure for table `userinfo`
218 
--
219 
 
3278 rexy 220 
DROP TABLE IF EXISTS userinfo;
221 
CREATE TABLE userinfo (
222 
  id         int(10)      NOT NULL AUTO_INCREMENT,
223 
  username   varchar(64)  COLLATE utf8_bin DEFAULT NULL,
224 
  name       varchar(200) COLLATE utf8_bin DEFAULT NULL,
225 
  mail       varchar(200) COLLATE utf8_bin DEFAULT NULL,
226 
  department varchar(200) COLLATE utf8_bin DEFAULT NULL,
227 
  workphone  varchar(200) COLLATE utf8_bin DEFAULT NULL,
228 
  homephone  varchar(200) COLLATE utf8_bin DEFAULT NULL,
229 
  mobile     varchar(200) COLLATE utf8_bin DEFAULT NULL,
230 
  PRIMARY KEY (id),
231 
  KEY username (username),
232 
  KEY department (department)
2371 tom.houday 233 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;