| |
| // 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)) |
| ); |