1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
--
-- eurephia database schema for SQLite3
--
-- GPLv2 - Copyright 2008
-- David Sommerseth
CREATE TABLE openvpn_certificates (
depth integer NOT NULL,
digest varchar(64) NOT NULL,
common_name varchar(64) NOT NULL,
organisation varchar(64) NOT NULL,
email varchar(256) NOT NULL,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
certid integer PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX opevpn_certificates_digest ON openvpn_certificates(digest);
CREATE TABLE openvpn_users (
username varchar(32) NOT NULL,
password varchar(128) NOT NULL,
activated timestamp ,
deactivated timestamp ,
last_accessed timestamp ,
uid integer PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_users_uname ON openvpn_users(username);
CREATE TABLE openvpn_usercerts (
uid integer NOT NULL,
certid integer NOT NULL,
accessprofile integer ,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
uicid integer PRIMARY KEY AUTOINCREMENT
);
CREATE INDEX openvpn_usercerts_uid ON openvpn_usercerts(uid);
CREATE INDEX openvpn_usercerts_certid ON openvpn_usercerts(certid);
CREATE TABLE openvpn_accesses (
access_descr varchar(128) ,
fw_profile varchar(64) NOT NULL,
accessprofile integer PRIMARY KEY AUTOINCREMENT
);
CREATE TABLE openvpn_lastlog (
uid integer ,
certid integer ,
protocol varchar(4) NOT NULL,
remotehost varchar(128) NOT NULL,
remoteport integer NOT NULL,
macaddr varchar(20) ,
vpnipaddr varchar(32) NOT NULL,
vpnipmask varchar(32) NOT NULL,
sessionstatus integer NOT NULL DEFAULT 0,
sessionkey varchar(128) ,
login timestamp ,
logout timestamp ,
session_deleted timestamp ,
session_duration timestamp,
bytes_sent integer ,
bytes_received integer ,
llid integer PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_lastlog_sessionkey ON openvpn_lastlog(sessionkey);
CREATE TABLE openvpn_macaddr_history (
sessionkey varchar(64) NOT NULL,
macaddr varchar(20) NOT NULL,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
semaid integer PRIMARY KEY AUTOINCREMENT
);
CREATE INDEX openvpn_macaddr_hist_sessionkey ON openvpn_macaddr_history(sessionkey);
CREATE INDEX openvpn_macaddr_hist_macaddr ON openvpn_macaddr_history(macaddr);
CREATE TABLE openvpn_sessions (
sessionkey varchar(128) NOT NULL,
datakey varchar(256) NOT NULL,
dataval text ,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
sessid integer PRIMARY KEY AUTOINCREMENT
);
CREATE INDEX openvpn_sessions_sessionkey ON openvpn_sessions(sessionkey);
CREATE UNIQUE INDEX openvpn_sessions_sess_datakey ON openvpn_sessions(sessionkey, datakey);
CREATE TABLE openvpn_blacklist (
digest varchar(64) ,
username varchar(32) ,
remoteip varchar(32) ,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
last_accessed timestamp ,
blid integer PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_blacklist_digest ON openvpn_blacklist(digest);
CREATE UNIQUE INDEX openvpn_blacklist_username ON openvpn_blacklist(username);
CREATE UNIQUE INDEX openvpn_blacklist_remoteip ON openvpn_blacklist(remoteip);
CREATE TABLE openvpn_attempts (
username varchar(32) ,
digest varchar(64) ,
remoteip varchar(32) ,
attempts integer DEFAULT 0,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
last_attempt timestamp ,
atpid integer PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_attempts_username ON openvpn_attempts(username);
CREATE UNIQUE INDEX openvpn_attempts_digest ON openvpn_attempts(digest);
CREATE UNIQUE INDEX openvpn_attempts_remoteip ON openvpn_attempts(remoteip);
CREATE TABLE openvpn_sessionkeys (
sessionseed varchar(128) NOT NULL,
sessionkey varchar(128) NOT NULL,
PRIMARY KEY(sessionkey)
);
CREATE INDEX opevpn_sessionkeys_seed ON openvpn_sessionkeys(sessionseed);
CREATE TABLE openvpn_config (
datakey varchar(64) NOT NULL,
dataval text ,
cfgid integer PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_config_key ON openvpn_config(datakey);
CREATE TABLE eurephia_adminlog (
uid integer NOT NULL,
interface char NOT NULL, -- C-onsole, W-eb
status integer NOT NULL,
login timestamp NOT NULL,
last_action timestamp NOT NULL,
logout timestamp ,
sessionkey varchar(128) NOT NULL,
ealid integer PRIMARY KEY AUTOINCREMENT
);
CREATE INDEX eurephia_adminlog_uid ON eurephia_adminlog(uid);
CREATE INDEX eurephia_adminlog_sesskey ON eurephia_adminlog(sessionkey);
CREATE TABLE eurephia_adminaccess (
uid integer NOT NULL,
interface char NOT NULL, -- C-onsole, W-eb
access varchar(64) NOT NULL
);
CREATE INDEX eurephia_adminacc_uid ON eurephia_adminaccess (uid);
CREATE INDEX eurephia_adminacc_uid_intf ON eurephia_adminaccess (uid,interface);
|
