1 /* # Copyright © 2018 Amdocs, Bell Canada
3 # Licensed under the Apache License, Version 2.0 (the "License");
4 # you may not use this file except in compliance with the License.
5 # You may obtain a copy of the License at
7 # http://www.apache.org/licenses/LICENSE-2.0
9 # Unless required by applicable law or agreed to in writing, software
10 # distributed under the License is distributed on an "AS IS" BASIS,
11 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12 # See the License for the specific language governing permissions and
13 # limitations under the License. */
15 // Table Initialization
16 // First make sure the keyspace exists.
21 // CORE Table function
24 // Namespace - establish hierarchical authority to modify
25 // Permissions and Roles
26 // "scope" is flag to determine Policy. Typical important scope
30 scope int, // deprecated 2.0.11
36 CREATE INDEX ns_parent on ns(parent);
38 CREATE TABLE ns_attrib (
44 create index ns_attrib_key on ns_attrib(key);
50 perms set<varchar>, // Use "Key" of "name|type|action"
54 CREATE INDEX role_name ON role(name);
62 roles set<varchar>, // Need to find Roles given Permissions
64 PRIMARY KEY (ns,type,instance,action)
67 // This table is user for Authorization
68 CREATE TABLE user_role (
70 role varchar, // deprecated: change to ns/rname after 2.0.11
74 PRIMARY KEY(user,role)
76 CREATE INDEX user_role_ns ON user_role(ns);
77 CREATE INDEX user_role_role ON user_role(role);
79 // This table is only for the case where return User Credential (MechID) Authentication
89 PRIMARY KEY (id,type,expires)
91 CREATE INDEX cred_ns ON cred(ns);
93 // Certificate Cross Table
94 // coordinated with CRED type 2
100 PRIMARY KEY (fingerprint)
102 CREATE INDEX cert_id ON cert(id);
103 CREATE INDEX cert_x500 ON cert(x500);
105 CREATE TABLE notify (
110 PRIMARY KEY (user,type)
119 PRIMARY KEY (ca,serial)
123 CREATE INDEX x509_id ON x509 (id);
124 CREATE INDEX x509_x500 ON x509 (x500);
127 // Deployment Artifact (for Certman)
129 CREATE TABLE artifact (
142 PRIMARY KEY (mechid,machine)
144 CREATE INDEX artifact_machine ON artifact(machine);
145 CREATE INDEX artifact_ns ON artifact(ns);
148 // Non-Critical Table functions
150 // Table Info - for Caching
153 seg int, // cache Segment
155 PRIMARY KEY(name,seg)
158 CREATE TABLE history (
163 target varchar, // user, user_role,
164 subject varchar, // field for searching main portion of target key
165 memo varchar, //description of the action
166 reconstruct blob, //serialized form of the target
167 // detail Map<varchar, varchar>, // additional information
170 CREATE INDEX history_yr_mon ON history(yr_mon);
171 CREATE INDEX history_user ON history(user);
172 CREATE INDEX history_subject ON history(subject);
175 // A place to hold objects to be created at a future time.
177 CREATE TABLE future (
179 target varchar, // Target Table
180 memo varchar, // Description
181 start timestamp, // When it should take effect
182 expires timestamp, // When not longer valid
183 construct blob, // How to construct this object (like History)
186 CREATE INDEX future_idx ON future(target);
187 CREATE INDEX future_start_idx ON future(start);
190 CREATE TABLE approval (
191 id timeuuid, // unique Key
192 ticket uuid, // Link to Future Record
193 user varchar, // the user who needs to be approved
194 approver varchar, // user approving
195 type varchar, // approver types i.e. Supervisor, Owner
196 status varchar, // approval status. pending, approved, denied
197 memo varchar, // Text for Approval to know what's going on
198 operation varchar, // List operation to perform
199 last_notified timestamp, // Timestamp for the last time approver was notified
202 CREATE INDEX appr_approver_idx ON approval(approver);
203 CREATE INDEX appr_user_idx ON approval(user);
204 CREATE INDEX appr_ticket_idx ON approval(ticket);
205 CREATE INDEX appr_status_idx ON approval(status);
207 CREATE TABLE approved (
208 id timeuuid, // unique Key
209 user varchar, // the user who needs to be approved
210 approver varchar, // user approving
211 type varchar, // approver types i.e. Supervisor, Owner
212 status varchar, // approval status. pending, approved, denied
213 memo varchar, // Text for Approval to know what's going on
214 operation varchar, // List operation to perform
217 CREATE INDEX approved_approver_idx ON approved(approver);
218 CREATE INDEX approved_user_idx ON approved(user);
220 CREATE TABLE delegate (
226 CREATE INDEX delg_delg_idx ON delegate(delegate);
229 CREATE TABLE oauth_token (
230 id text, // Reference
231 client_id text, // Creating Client ID
232 user text, // User requesting
233 active boolean, // Active or not
234 type int, // Type of Token
235 refresh text, // Refresh Token
236 expires timestamp, // Expiration time/Date (signed long)
237 exp_sec bigint, // Seconds from Jan 1, 1970
238 content text, // Content of Token
239 scopes Set<text>, // Scopes
240 state text, // Context string (Optional)
241 req_ip text, // Requesting IP (for logging purpose)
243 ) with default_time_to_live = 21600; // 6 hours
244 CREATE INDEX oauth_token_user_idx ON oauth_token(user);
246 CREATE TABLE locate (
247 name text, // Component/Server name
248 hostname text, // FQDN of Service/Component
249 port int, // Port of Service
250 major int, // Version, Major
251 minor int, // Version, Minor
252 patch int, // Version, Patch
253 pkg int, // Version, Package (if available)
254 latitude float, // Latitude
255 longitude float, // Longitude
256 protocol text, // Protocol (i.e. http https)
257 subprotocol set<text>, // Accepted SubProtocols, ie. TLS1.1 for https
258 port_key uuid, // Key into locate_ports
259 PRIMARY KEY(name,hostname,port)
260 ) with default_time_to_live = 1200; // 20 mins
262 CREATE TABLE locate_ports (
263 id uuid, // Id into locate
265 name text, // Name of Other Port
266 protocol text, // Protocol of Other (i.e. JMX, DEBUG)
267 subprotocol set<text>, // Accepted sub protocols or versions
268 PRIMARY KEY(id, port)
269 ) with default_time_to_live = 1200; // 20 mins;
272 // Used by authz-batch processes to ensure only 1 runs at a time
274 CREATE TABLE run_lock (
278 PRIMARY KEY ((class))