summaryrefslogtreecommitdiffstats
path: root/auth/auth-cass/src/main/cql/init.cql
blob: c06e5ee952aed6b778ad6aa8d02015aa576ae407 (plain)
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
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
// Table Initialization
// First make sure the keyspace exists.

USE authz;

//
// CORE Table function
//

// Namespace - establish hierarchical authority to modify
// Permissions and Roles
// "scope" is flag to determine Policy.  Typical important scope
// is "company" (1)
CREATE TABLE ns (
  name			varchar,
  scope			int,  // deprecated 2.0.11
  description   	varchar,
  parent 		varchar,
  type			int,
  PRIMARY KEY (name)  
);
CREATE INDEX ns_parent on ns(parent);
  
CREATE TABLE ns_attrib (
  ns            varchar,
  key           varchar,
  value         varchar,
  PRIMARY KEY (ns,key)
);
create index ns_attrib_key on ns_attrib(key);

// Will be cached
CREATE TABLE role (
  ns	    varchar,
  name		varchar,
  perms		set<varchar>, // Use "Key" of "name|type|action"
  description varchar,
  PRIMARY KEY (ns,name)
);
CREATE INDEX role_name  ON role(name);
 
// Will be cached
CREATE TABLE perm (
  ns	    varchar,
  type 		varchar,
  instance	varchar,
  action	varchar,
  roles		set<varchar>, // Need to find Roles given Permissions
  description varchar,
  PRIMARY KEY (ns,type,instance,action)
);

// This table is user for Authorization
CREATE TABLE user_role (
    user		varchar,
    role		varchar, // deprecated: change to ns/rname after 2.0.11
    ns			varchar,
    rname		varchar,
    expires		timestamp,
    PRIMARY KEY(user,role)
  );
CREATE INDEX user_role_ns ON user_role(ns);
CREATE INDEX user_role_role ON user_role(role);

// This table is only for the case where return User Credential (MechID) Authentication
CREATE TABLE cred (
    id    varchar,
    type  int,
    expires timestamp,  
    ns    varchar,
    other int,
    notes varchar,
    cred  blob,
    prev  blob,
    PRIMARY KEY (id,type,expires)
  );
CREATE INDEX cred_ns ON cred(ns);

// Certificate Cross Table
//   coordinated with CRED type 2
CREATE TABLE cert (
    fingerprint blob,
    id    	varchar,
    x500	varchar,
    expires 	timestamp,  
    PRIMARY KEY (fingerprint)
  );
CREATE INDEX cert_id ON cert(id);
CREATE INDEX cert_x500 ON cert(x500);

CREATE TABLE notify (
  user 		text,
  type 		int,
  last 		timestamp,
  checksum 	int,
  PRIMARY KEY (user,type)
);

CREATE TABLE x509 (
  ca     text,
  serial blob,
  id     text,
  x500   text,
  x509   text,
  PRIMARY KEY (ca,serial)
);


CREATE INDEX x509_id   ON x509 (id);
CREATE INDEX x509_x500 ON x509 (x500);

// 
// Deployment Artifact (for Certman)
//
CREATE TABLE artifact (
  mechid        text,
  machine       text,
  type          Set<text>,
  sponsor       text,
  ca            text,
  dir           text,
  os_user       text,
  ns	        text,
  notify        text,
  expires	timestamp,
  renewDays     int,
  sans		Set<text>,
  PRIMARY KEY (mechid,machine)
);
CREATE INDEX artifact_machine ON artifact(machine); 
CREATE INDEX artifact_ns ON artifact(ns); 

//
// Non-Critical Table functions
//
// Table Info - for Caching
CREATE TABLE cache (
   name		varchar,
   seg		int, 		// cache Segment
   touched	timestamp,
   PRIMARY KEY(name,seg)
);

CREATE TABLE history (
  id			timeuuid,
  yr_mon		int,
  user			varchar,
  action 		varchar,
  target		varchar,   // user, user_role, 
  subject		varchar,   // field for searching main portion of target key
  memo			varchar,   //description of the action
  reconstruct 	blob,      //serialized form of the target
  // detail 	Map<varchar, varchar>,  // additional information
  PRIMARY KEY (id)
);
CREATE INDEX history_yr_mon ON history(yr_mon);
CREATE INDEX history_user ON history(user); 
CREATE INDEX history_subject ON history(subject); 

// 
// A place to hold objects to be created at a future time.
//
CREATE TABLE future (
  id        uuid,  		// uniquify
  target    varchar,   		// Target Table
  memo	    varchar,    	// Description
  start     timestamp, 		// When it should take effect
  expires   timestamp, 		// When not longer valid
  construct blob, 		// How to construct this object (like History)
  PRIMARY KEY(id)
);
CREATE INDEX future_idx ON future(target);
CREATE INDEX future_start_idx ON future(start);


CREATE TABLE approval (
  id	    timeuuid,	      // unique Key
  ticket    uuid,	      // Link to Future Record
  user 	    varchar,          // the user who needs to be approved
  approver  varchar, 	      // user approving
  type      varchar,          // approver types i.e. Supervisor, Owner
  status    varchar,          // approval status. pending, approved, denied
  memo      varchar,          // Text for Approval to know what's going on
  operation varchar,	      // List operation to perform
  last_notified timestamp,    // Timestamp for the last time approver was notified
  PRIMARY KEY(id)
 );
CREATE INDEX appr_approver_idx ON approval(approver);
CREATE INDEX appr_user_idx ON approval(user);
CREATE INDEX appr_ticket_idx ON approval(ticket);
CREATE INDEX appr_status_idx ON approval(status);

CREATE TABLE approved (
  id        timeuuid,         // unique Key
  user      varchar,          // the user who needs to be approved
  approver  varchar,          // user approving
  type      varchar,          // approver types i.e. Supervisor, Owner
  status    varchar,          // approval status. pending, approved, denied
  memo      varchar,          // Text for Approval to know what's going on
  operation varchar,          // List operation to perform
  PRIMARY KEY(id)
 );
CREATE INDEX approved_approver_idx ON approved(approver);
CREATE INDEX approved_user_idx ON approved(user);

CREATE TABLE delegate (
  user      varchar,
  delegate  varchar,
  expires   timestamp,
  PRIMARY KEY (user)  
);
CREATE INDEX delg_delg_idx ON delegate(delegate);

// OAuth Tokens
CREATE TABLE oauth_token (
  id            text,                   // Reference
  client_id     text,                   // Creating Client ID
  user          text,                   // User requesting
  active	boolean,		// Active or not
  type		int,			// Type of Token
  refresh       text,                   // Refresh Token
  expires       timestamp,              // Expiration time/Date (signed long)
  exp_sec	bigint,			// Seconds from Jan 1, 1970
  content       text,                   // Content of Token
  scopes        Set<text>,	 	// Scopes
  state		text,			// Context string (Optional)
  req_ip	text,			// Requesting IP (for logging purpose)
  PRIMARY KEY(id)
) with default_time_to_live = 21600;    // 6 hours
CREATE INDEX oauth_token_user_idx ON oauth_token(user);

CREATE TABLE locate (
  name		text,			// Component/Server name
  hostname	text,			// FQDN of Service/Component
  port		int,			// Port of Service
  major		int,			// Version, Major
  minor		int,			// Version, Minor
  patch		int,			// Version, Patch
  pkg		int,			// Version, Package (if available)
  latitude	float,			// Latitude
  longitude	float,			// Longitude
  protocol	text,			// Protocol (i.e. http https)
  subprotocol   set<text>,		// Accepted SubProtocols, ie. TLS1.1 for https
  port_key      uuid,			// Key into locate_ports
  PRIMARY KEY(name,hostname,port)
) with default_time_to_live = 1200;	// 20 mins

CREATE TABLE locate_ports (
  id		uuid,			// Id into locate
  port		int,			// SubPort
  name		text,			// Name of Other Port
  protocol	text,			// Protocol of Other (i.e. JMX, DEBUG)
  subprotocol   set<text>,		// Accepted sub protocols or versions
  PRIMARY KEY(id, port)
) with default_time_to_live = 1200;	// 20 mins; 

//
// Used by authz-batch processes to ensure only 1 runs at a time
//
CREATE TABLE run_lock (
  class text,
  host text,
  start timestamp,
  PRIMARY KEY ((class))
);