From 0c8d1d765783bbc09cc1ca63ffdd233f0ce31613 Mon Sep 17 00:00:00 2001 From: Rob Austein Date: Tue, 28 Apr 2015 15:29:12 -0400 Subject: First public commit of PKCS #11 implementation. --- schema.sql | 117 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 117 insertions(+) create mode 100644 schema.sql (limited to 'schema.sql') diff --git a/schema.sql b/schema.sql new file mode 100644 index 0000000..82d9482 --- /dev/null +++ b/schema.sql @@ -0,0 +1,117 @@ +-- SQLite3 schema for Cryptech PKCS #11 implementation. +-- +-- Author: Rob Austein +-- Copyright (c) 2015, SUNET +-- +-- Redistribution and use in source and binary forms, with or +-- without modification, are permitted provided that the following +-- conditions are met: +-- +-- 1. Redistributions of source code must retain the above copyright +-- notice, this list of conditions and the following disclaimer. +-- +-- 2. Redistributions in binary form must reproduce the above copyright +-- notice, this list of conditions and the following disclaimer in +-- the documentation and/or other materials provided with the +-- distribution. +-- +-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS +-- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT +-- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS +-- FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE +-- COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, +-- INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, +-- BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; +-- LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER +-- CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, +-- STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) +-- ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF +-- ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + +-- Notes: +-- +-- The CHECK constraints in the attribute tables are checking +-- CKA_TOKEN, to make sure we don't accidently file token objects in +-- the session table or vice versa. +-- +-- temp.object.token_object_id is a foreign-key reference to +-- main.token_object.id, but we can't use a real foreign key reference +-- because they're in different databases. If we're careful about how +-- we do our joins, this is harmless, but may lead to some clutter if +-- a long running session has handles on token objects which some +-- other process deletes from the database. If this happens and we +-- care for some reason, we can clean up such clutter with something +-- like: +-- +-- WITH +-- known AS (SELECT token_object_id FROM token_object) +-- DELETE FROM object +-- WHERE token_object_id IS NOT NULL +-- AND token_object_id NOT IN known; + +PRAGMA foreign_keys = ON; + +CREATE TEMPORARY TABLE IF NOT EXISTS session ( + session_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, + session_handle INTEGER NOT NULL UNIQUE + CHECK (session_handle > 0 AND session_handle <= 0xFFFFFFFF) +); + +CREATE TEMPORARY TABLE IF NOT EXISTS object ( + object_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, + object_handle INTEGER NOT NULL UNIQUE + CHECK (object_handle > 0 AND object_handle <= 0xFFFFFFFF), + session_id INTEGER REFERENCES session + ON DELETE CASCADE ON UPDATE CASCADE + DEFERRABLE INITIALLY DEFERRED, + token_object_id INTEGER, + session_object_id INTEGER REFERENCES session_object + ON DELETE CASCADE ON UPDATE CASCADE + DEFERRABLE INITIALLY DEFERRED, + CHECK (token_object_id IS NULL OR (session_id IS NULL AND session_object_id IS NULL)), + UNIQUE (token_object_id), + UNIQUE (session_id, session_object_id) +); + +CREATE TEMPORARY TABLE IF NOT EXISTS session_object ( + session_object_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, + keyid TEXT UNIQUE, + object_id INTEGER NOT NULL UNIQUE + REFERENCES object + ON DELETE CASCADE ON UPDATE CASCADE +); + +CREATE TEMPORARY TABLE IF NOT EXISTS session_attribute ( + type INTEGER NOT NULL, + session_object_id INTEGER NOT NULL REFERENCES session_object + ON DELETE CASCADE ON UPDATE CASCADE, + value BLOB NOT NULL, + UNIQUE (type, session_object_id), + CHECK (type <> 1 OR value = X'00') +); + +CREATE TABLE IF NOT EXISTS token_object ( + token_object_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, + keyid TEXT UNIQUE +); + +CREATE TABLE IF NOT EXISTS token_attribute ( + type INTEGER NOT NULL, + token_object_id INTEGER NOT NULL REFERENCES token_object + ON DELETE CASCADE ON UPDATE CASCADE, + value BLOB NOT NULL, + UNIQUE (type, token_object_id), + CHECK (type <> 1 OR value <> X'00') +); + +-- http://sqlite.org/foreignkeys.html says we might want these. + +CREATE INDEX IF NOT EXISTS temp.object__session ON object(session_id); +CREATE INDEX IF NOT EXISTS temp.object__session_object ON object(session_object_id); +CREATE INDEX IF NOT EXISTS temp.session_object__object ON session_object(object_id); +CREATE INDEX IF NOT EXISTS temp.session_attribute__session_object ON session_attribute(session_object_id); +CREATE INDEX IF NOT EXISTS token_attribute__token_object ON token_attribute(token_object_id); + +-- Local variables: +-- indent-tabs-mode: nil +-- End: -- cgit v1.2.3