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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
|
# Datamodel for OAuthStoreMySQL
#
# You need to add the foreign key constraints for the user ids your are using.
# I have commented the constraints out, just look for 'usa_id_ref' to enable them.
#
# The --SPLIT-- markers are used by the install.php script
#
# @version $Id: mysql.sql 51 2008-10-15 15:15:47Z marcw@pobox.com $
# @author Marc Worrell
#
# Changes:
#
# 2008-10-15 (on r48) Added ttl to consumer and server tokens, added named server tokens
#
# ALTER TABLE oauth_server_token
# ADD ost_token_ttl datetime not null default '9999-12-31',
# ADD KEY (ost_token_ttl);
#
# ALTER TABLE oauth_consumer_token
# ADD oct_name varchar(64) binary not null default '',
# ADD oct_token_ttl datetime not null default '9999-12-31',
# DROP KEY oct_usa_id_ref,
# ADD UNIQUE KEY (oct_usa_id_ref, oct_ocr_id_ref, oct_token_type, oct_name),
# ADD KEY (oct_token_ttl);
#
# 2008-09-09 (on r5) Added referrer host to server access token
#
# ALTER TABLE oauth_server_token ADD ost_referrer_host VARCHAR(128) NOT NULL;
#
#
# Log table to hold all OAuth request when you enabled logging
#
CREATE TABLE IF NOT EXISTS oauth_log (
olg_id int(11) not null auto_increment,
olg_osr_consumer_key varchar(64) binary,
olg_ost_token varchar(64) binary,
olg_ocr_consumer_key varchar(64) binary,
olg_oct_token varchar(64) binary,
olg_usa_id_ref int(11),
olg_received text not null,
olg_sent text not null,
olg_base_string text not null,
olg_notes text not null,
olg_timestamp timestamp not null default current_timestamp,
olg_remote_ip bigint not null,
primary key (olg_id),
key (olg_osr_consumer_key, olg_id),
key (olg_ost_token, olg_id),
key (olg_ocr_consumer_key, olg_id),
key (olg_oct_token, olg_id),
key (olg_usa_id_ref, olg_id)
# , foreign key (olg_usa_id_ref) references any_user_auth (usa_id_ref)
# on update cascade
# on delete cascade
) engine=InnoDB default charset=utf8;
#--SPLIT--
#
# /////////////////// CONSUMER SIDE ///////////////////
#
# This is a registry of all consumer codes we got from other servers
# The consumer_key/secret is obtained from the server
# We also register the server uri, so that we can find the consumer key and secret
# for a certain server. From that server we can check if we have a token for a
# particular user.
CREATE TABLE IF NOT EXISTS oauth_consumer_registry (
ocr_id int(11) not null auto_increment,
ocr_usa_id_ref int(11),
ocr_consumer_key varchar(64) binary not null,
ocr_consumer_secret varchar(64) binary not null,
ocr_signature_methods varchar(255) not null default 'HMAC-SHA1,PLAINTEXT',
ocr_server_uri varchar(255) not null,
ocr_server_uri_host varchar(128) not null,
ocr_server_uri_path varchar(128) binary not null,
ocr_request_token_uri varchar(255) not null,
ocr_authorize_uri varchar(255) not null,
ocr_access_token_uri varchar(255) not null,
ocr_timestamp timestamp not null default current_timestamp,
primary key (ocr_id),
unique key (ocr_consumer_key, ocr_usa_id_ref),
key (ocr_server_uri),
key (ocr_server_uri_host, ocr_server_uri_path),
key (ocr_usa_id_ref)
# , foreign key (ocr_usa_id_ref) references any_user_auth(usa_id_ref)
# on update cascade
# on delete set null
) engine=InnoDB default charset=utf8;
#--SPLIT--
# Table used to sign requests for sending to a server by the consumer
# The key is defined for a particular user. Only one single named
# key is allowed per user/server combination
CREATE TABLE IF NOT EXISTS oauth_consumer_token (
oct_id int(11) not null auto_increment,
oct_ocr_id_ref int(11) not null,
oct_usa_id_ref int(11) not null,
oct_name varchar(64) binary not null default '',
oct_token varchar(64) binary not null,
oct_token_secret varchar(64) binary not null,
oct_token_type enum('request','authorized','access'),
oct_token_ttl datetime not null default '9999-12-31',
oct_timestamp timestamp not null default current_timestamp,
primary key (oct_id),
unique key (oct_ocr_id_ref, oct_token),
unique key (oct_usa_id_ref, oct_ocr_id_ref, oct_token_type, oct_name),
key (oct_token_ttl),
foreign key (oct_ocr_id_ref) references oauth_consumer_registry (ocr_id)
on update cascade
on delete cascade
# , foreign key (oct_usa_id_ref) references any_user_auth (usa_id_ref)
# on update cascade
# on delete cascade
) engine=InnoDB default charset=utf8;
#--SPLIT--
#
# ////////////////// SERVER SIDE /////////////////
#
# Table holding consumer key/secret combos an user issued to consumers.
# Used for verification of incoming requests.
CREATE TABLE IF NOT EXISTS oauth_server_registry (
osr_id int(11) not null auto_increment,
osr_usa_id_ref int(11),
osr_consumer_key varchar(64) binary not null,
osr_consumer_secret varchar(64) binary not null,
osr_enabled tinyint(1) not null default '1',
osr_status varchar(16) not null,
osr_requester_name varchar(64) not null,
osr_requester_email varchar(64) not null,
osr_callback_uri varchar(255) not null,
osr_application_uri varchar(255) not null,
osr_application_title varchar(80) not null,
osr_application_descr text not null,
osr_application_notes text not null,
osr_application_type varchar(20) not null,
osr_application_commercial tinyint(1) not null default '0',
osr_issue_date datetime not null,
osr_timestamp timestamp not null default current_timestamp,
primary key (osr_id),
unique key (osr_consumer_key),
key (osr_usa_id_ref)
# , foreign key (osr_usa_id_ref) references any_user_auth(usa_id_ref)
# on update cascade
# on delete set null
) engine=InnoDB default charset=utf8;
#--SPLIT--
# Nonce used by a certain consumer, every used nonce should be unique, this prevents
# replaying attacks. We need to store all timestamp/nonce combinations for the
# maximum timestamp received.
CREATE TABLE IF NOT EXISTS oauth_server_nonce (
osn_id int(11) not null auto_increment,
osn_consumer_key varchar(64) binary not null,
osn_token varchar(64) binary not null,
osn_timestamp bigint not null,
osn_nonce varchar(80) binary not null,
primary key (osn_id),
unique key (osn_consumer_key, osn_token, osn_timestamp, osn_nonce)
) engine=InnoDB default charset=utf8;
#--SPLIT--
# Table used to verify signed requests sent to a server by the consumer
# When the verification is succesful then the associated user id is returned.
CREATE TABLE IF NOT EXISTS oauth_server_token (
ost_id int(11) not null auto_increment,
ost_osr_id_ref int(11) not null,
ost_usa_id_ref int(11) not null,
ost_token varchar(64) binary not null,
ost_token_secret varchar(64) binary not null,
ost_token_type enum('request','access'),
ost_authorized tinyint(1) not null default '0',
ost_referrer_host varchar(128) not null,
ost_token_ttl datetime not null default '9999-12-31',
ost_timestamp timestamp not null default current_timestamp,
primary key (ost_id),
unique key (ost_token),
key (ost_osr_id_ref),
key (ost_token_ttl),
foreign key (ost_osr_id_ref) references oauth_server_registry (osr_id)
on update cascade
on delete cascade
# , foreign key (ost_usa_id_ref) references any_user_auth (usa_id_ref)
# on update cascade
# on delete cascade
) engine=InnoDB default charset=utf8;
|
