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
|
-- N.B some of these tables assume you have a user table with
-- an id column for linking access tokens, etc, to a user
-- and the use of varchar( 255 ) is unlikey to be big enough
-- if you use the jwt_secret option of the plugin to make
-- tokens JWTs (at which point a TEXT field would be required,
-- and then that has an impact on how you defined the indexes
-- and primary keys...)
create table if not exists oauth2_client (
id varchar( 255 ) NOT NULL PRIMARY KEY,
secret varchar( 255 ) NOT NULL,
active boolean NOT NULL DEFAULT true,
last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
);
create table if not exists oauth2_scope (
id bigint NOT NULL PRIMARY KEY,
description varchar( 255 ) NOT NULL,
UNIQUE KEY( description )
);
create table if not exists oauth2_client_scope (
client_id varchar( 255 ) NOT NULL,
scope_id bigint NOT NULL,
allowed boolean NOT NULL DEFAULT false,
CONSTRAINT `oauth2_client_scope__client_id` FOREIGN KEY ( `client_id` )
REFERENCES `oauth2_client` ( `id` )
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `oauth2_client_scope__scope_id` FOREIGN KEY ( `scope_id` )
REFERENCES `oauth2_scope` ( `id` )
ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY( client_id, scope_id )
);
create table if not exists oauth2_auth_code (
auth_code varchar( 255 ) NOT NULL PRIMARY KEY,
client_id varchar( 255 ) NOT NULL,
user_id integer( 20 ) DEFAULT NULL,
expires timestamp NOT NULL,
redirect_uri tinytext NOT NULL,
verified boolean NOT NULL DEFAULT false,
CONSTRAINT `oauth2_auth_code__client_id`
FOREIGN KEY ( `client_id` )
REFERENCES `oauth2_client` ( `id` )
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `oauth2_auth_code__user_id`
FOREIGN KEY ( `user_id` )
REFERENCES `user` ( `id` )
ON UPDATE CASCADE ON DELETE CASCADE
);
create table if not exists oauth2_auth_code_scope (
auth_code varchar( 255 ) NOT NULL,
scope_id bigint NOT NULL,
allowed boolean NOT NULL DEFAULT false,
CONSTRAINT `oauth2_auth_code_scope__auth_code`
FOREIGN KEY ( `auth_code` )
REFERENCES `oauth2_auth_code` ( `auth_code` )
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `oauth2_auth_code_scope__scope_id`
FOREIGN KEY ( `scope_id` )
REFERENCES `oauth2_scope` ( `id` )
ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY( auth_code, scope_id )
);
create table if not exists oauth2_access_token (
access_token varchar( 255 ) NOT NULL PRIMARY KEY,
refresh_token varchar( 255 ) DEFAULT NULL,
client_id varchar( 255 ) NOT NULL,
user_id integer( 20 ) DEFAULT NULL,
expires timestamp NOT NULL,
CONSTRAINT `oauth2_access_token__client_id`
FOREIGN KEY ( `client_id` )
REFERENCES `oauth2_client` ( `id` )
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `oauth2_access_token__user_id`
FOREIGN KEY ( `user_id` )
REFERENCES `user` ( `id` )
ON UPDATE CASCADE ON DELETE CASCADE
);
create table if not exists oauth2_access_token_scope (
access_token varchar( 255 ) NOT NULL,
scope_id bigint NOT NULL,
allowed boolean NOT NULL DEFAULT false,
CONSTRAINT `oauth2_access_token_scope__auth_code`
FOREIGN KEY ( `access_token` )
REFERENCES `oauth2_access_token` ( `access_token` )
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `oauth2_access_token_scope__scope_id`
FOREIGN KEY ( `scope_id` )
REFERENCES `oauth2_scope` ( `id` )
ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY( access_token, scope_id )
);
create table if not exists oauth2_refresh_token (
refresh_token varchar( 255 ) NOT NULL PRIMARY KEY,
access_token varchar( 255 ) NOT NULL,
client_id varchar( 255 ) NOT NULL,
user_id integer( 20 ) DEFAULT NULL,
CONSTRAINT `oauth2_refresh_token__client_id`
FOREIGN KEY ( `client_id` )
REFERENCES `oauth2_client` ( `id` )
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `oauth2_refresh_token__user_id`
FOREIGN KEY ( `user_id` )
REFERENCES `user` ( `id` )
ON UPDATE CASCADE ON DELETE CASCADE
);
create table if not exists oauth2_refresh_token_scope (
refresh_token varchar( 255 ) NOT NULL,
scope_id bigint NOT NULL,
allowed boolean NOT NULL DEFAULT false,
CONSTRAINT `oauth2_refresh_token_scope__auth_code`
FOREIGN KEY ( `refresh_token` )
REFERENCES `oauth2_refresh_token` ( `refresh_token` )
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `oauth2_refresh_token_scope__scope_id`
FOREIGN KEY ( `scope_id` )
REFERENCES `oauth2_scope` ( `id` )
ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY( refresh_token, scope_id )
);
|
