-- 2. If you change definition of tables
-- update the schema counter at the bottom!!
--- TODO: Use "USING" in joins whenever possible
-
.load librules-db-sql-udf.so
PRAGMA foreign_keys = ON;
PRAGMA auto_vacuum = NONE;
BEGIN EXCLUSIVE TRANSACTION;
--- PRAGMA cache_size = 2000;
+-- Update here on every schema change! Integer value.
+PRAGMA user_version = 3;
CREATE TABLE IF NOT EXISTS app (
app_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
FOREIGN KEY(label_id) REFERENCES label(label_id)
);
-CREATE INDEX IF NOT EXISTS app_index ON app(app_id, label_id);
CREATE TABLE IF NOT EXISTS app_permission (
FOREIGN KEY(permission_id) REFERENCES permission(permission_id)
);
+-- Used by ltl_ view
+CREATE INDEX IF NOT EXISTS app_permission_permission_id_index ON app_permission(permission_id);
+
CREATE TABLE IF NOT EXISTS app_path (
app_id INTEGER NOT NULL,
path TEXT NOT NULL,
label_id INTEGER NOT NULL,
access INTEGER NOT NULL,
+ access_reverse INTEGER NOT NULL,
app_path_type_id INTEGER NOT NULL ,
-- TODO:
FOREIGN KEY(app_path_type_id) REFERENCES app_path_type(app_path_type_id)
);
+-- Used by ltl_ view
+CREATE INDEX IF NOT EXISTS app_path_app_path_type_id_index ON app_path(app_path_type_id);
+
CREATE TABLE IF NOT EXISTS app_path_type (
app_path_type_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL ,
UNIQUE (name)
);
--- CREATE INDEX IF NOT EXISTS app_path_type_index ON app_path_type(app_path_type_id, name);
CREATE TABLE IF NOT EXISTS permission_permission_rule (
FOREIGN KEY(label_id) REFERENCES label(label_id)
);
+-- Used by ltl_ view
+CREATE INDEX IF NOT EXISTS permission_label_rule_label_id_index ON permission_label_rule(label_id);
+
CREATE TABLE IF NOT EXISTS permission_app_path_type_rule (
permission_id INTEGER NOT NULL,
app_path_type_id INTEGER NOT NULL,
FOREIGN KEY(app_path_type_id) REFERENCES app_path_type(app_path_type_id)
);
+-- Used by ltl_ view
+CREATE INDEX IF NOT EXISTS permission_app_path_type_rule_app_path_type_id_index
+ ON permission_app_path_type_rule(app_path_type_id);
+
+CREATE TABLE IF NOT EXISTS label_app_path_type_rule (
+ label_id INTEGER NOT NULL,
+ app_path_type_id INTEGER NOT NULL,
+ access INTEGER NOT NULL DEFAULT 0,
+ is_reverse INTEGER NOT NULL DEFAULT 0,
+
+ PRIMARY KEY (label_id, app_path_type_id, is_reverse),
+
+ FOREIGN KEY(label_id) REFERENCES label(label_id),
+ FOREIGN KEY(app_path_type_id) REFERENCES app_path_type(app_path_type_id)
+);
CREATE TABLE IF NOT EXISTS label (
label_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
FOREIGN KEY(permission_type_id) REFERENCES permission_type(permission_type_id)
);
-
+-- Not aggregated rules
CREATE TABLE IF NOT EXISTS all_smack_binary_rules(
subject TEXT NOT NULL,
object TEXT NOT NULL,
is_volatile INTEGER NOT NULL
);
--- TEMPORARY TABLES ------------------------------------------------------------
--- Definitions are repeated in code.
+-- Index used for grouping and sorting by (subject, object)
+-- and used for filtering by subject
+CREATE INDEX IF NOT EXISTS all_smack_binary_rules_subject_object_index
+ ON all_smack_binary_rules(subject, object);
-CREATE TEMPORARY TABLE history_smack_rule(
- subject TEXT NOT NULL,
- object TEXT NOT NULL,
- access INTEGER NOT NULL
-);
+-- Index used for filtering by object
+CREATE INDEX IF NOT EXISTS all_smack_binary_rules_object_index
+ ON all_smack_binary_rules(object);
+-- TEMPORARY TABLES ------------------------------------------------------------
+-- Definitions are repeated in code.
CREATE TEMPORARY TABLE modified_label(
- name TEXT NOT NULL,
- UNIQUE (name)
+ name TEXT NOT NULL PRIMARY KEY
);
-
+-- Not aggregated subset of modified rules
CREATE TEMPORARY TABLE all_smack_binary_rules_modified(
subject TEXT NOT NULL,
object TEXT NOT NULL,
- access INTEGER NOT NULL
+ access INTEGER NOT NULL,
+ is_volatile INTEGER NOT NULL
);
+-- Aggregated subset of rules after changes
+CREATE TEMPORARY TABLE current_smack_rule_modified(
+ subject TEXT NOT NULL,
+ object TEXT NOT NULL,
+ access INTEGER NOT NULL
+);
+-- Aggregated subset of rules before changes
CREATE TEMPORARY TABLE history_smack_rule_modified(
subject TEXT NOT NULL,
object TEXT NOT NULL,
-- Adding api features adds a label it it's not present.
INSERT OR IGNORE INTO label(name) VALUES (NEW.label_name);
- INSERT INTO permission_label_rule(permission_id, label_id, access, is_reverse)
+ INSERT OR REPLACE INTO permission_label_rule(permission_id,
+ label_id,
+ access,
+ is_reverse)
SELECT NEW.permission_id,
label.label_id,
str_to_access(NEW.access),
CREATE TRIGGER permission_app_path_type_rule_view_insert_trigger
INSTEAD OF INSERT
ON permission_app_path_type_rule_view
+WHEN NEW.permission_id IS NULL
BEGIN
INSERT INTO permission_app_path_type_rule(permission_id,
app_path_type_id,
END;
+CREATE TRIGGER permission_app_path_type_id_rule_view_insert_trigger
+INSTEAD OF INSERT
+ON permission_app_path_type_rule_view
+WHEN NEW.permission_id IS NOT NULL
+BEGIN
+ INSERT OR REPLACE INTO permission_app_path_type_rule(permission_id,
+ app_path_type_id,
+ access,
+ is_reverse)
+ SELECT NEW.permission_id,
+ app_path_type.app_path_type_id,
+ str_to_access(NEW.access),
+ NEW.is_reverse
+ FROM app_path_type
+ WHERE app_path_type.name = NEW.app_path_type_name;
+END;
+
+
+-- LABEL TO APP PATH TYPE RULE VIEW --------------------------------------------
+DROP VIEW IF EXISTS label_app_path_type_rule_view;
+CREATE VIEW label_app_path_type_rule_view AS
+SELECT
+ label_app_path_type_rule.label_id AS label_id,
+ label.name AS label_name,
+ app_path_type.name AS app_path_type_name,
+ label_app_path_type_rule.access AS access,
+ label_app_path_type_rule.is_reverse AS is_reverse
+FROM label_app_path_type_rule
+LEFT JOIN label USING(label_id)
+LEFT JOIN app_path_type USING(app_path_type_id);
+
+
+DROP TRIGGER IF EXISTS label_app_path_type_rule_view_insert_trigger;
+CREATE TRIGGER label_app_path_type_rule_view_insert_trigger
+INSTEAD OF INSERT
+ON label_app_path_type_rule_view
+BEGIN
+ INSERT OR IGNORE INTO label(name) VALUES (NEW.label_name);
+
+ INSERT INTO label_app_path_type_rule(label_id,
+ app_path_type_id,
+ access,
+ is_reverse)
+ SELECT label.label_id,
+ app_path_type.app_path_type_id,
+ str_to_access(NEW.access),
+ NEW.is_reverse
+ FROM label, app_path_type
+ WHERE label.name = NEW.label_name AND
+ app_path_type.name = NEW.app_path_type_name;
+END;
+
+
+DROP TRIGGER IF EXISTS label_app_path_type_rule_view_delete_trigger;
+CREATE TRIGGER label_app_path_type_rule_view_delete_trigger
+INSTEAD OF DELETE
+ON label_app_path_type_rule_view
+BEGIN
+ -- Delete the rules with this label
+ DELETE FROM label_app_path_type_rule
+ WHERE label_app_path_type_rule.label_id
+ IN (SELECT label.label_id
+ FROM label
+ WHERE label.name = OLD.label_name);
+
+ -- Delete the label if it's not referenced
+ DELETE FROM label_view
+ WHERE label_view.name = OLD.label_name;
+END;
+
-- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
DROP VIEW IF EXISTS permission_permission_rule_view;
CREATE VIEW permission_permission_rule_view AS
INSTEAD OF INSERT ON permission_permission_rule_view
BEGIN
- INSERT INTO permission_permission_rule(permission_id,
- target_permission_id,
- access,
- is_reverse)
+ INSERT OR REPLACE INTO permission_permission_rule(permission_id,
+ target_permission_id,
+ access,
+ is_reverse)
SELECT tmp_permission_view.permission_id,
tmp_target_permission_view.permission_id,
str_to_access(NEW.access),
CREATE TRIGGER label_view_delete_trigger
INSTEAD OF DELETE ON label_view
WHEN OLD.label_id NOT IN (SELECT app.label_id
- FROM app) AND
+ FROM app) AND
OLD.label_id NOT IN (SELECT permission_label_rule.label_id
- FROM permission_label_rule) AND
+ FROM permission_label_rule) AND
OLD.label_id NOT IN (SELECT app_path.label_id
- FROM app_path)
+ FROM app_path) AND
+ OLD.label_id NOT IN (SELECT label_app_path_type_rule.label_id
+ FROM label_app_path_type_rule)
BEGIN
DELETE FROM label WHERE label.name = OLD.name;
END;
app_path.path AS path,
label.name AS path_label_name,
app_path.access AS access,
+ app_path.access_reverse AS access_reverse,
app_path_type.name AS path_type_name
FROM app_path
INSERT OR IGNORE INTO label(name) VALUES (NEW.path_label_name);
-- Add the path
- INSERT INTO app_path(app_id, path, label_id, access, app_path_type_id)
+ INSERT OR IGNORE INTO app_path(app_id, path, label_id, access, access_reverse, app_path_type_id)
SELECT application_view.app_id,
NEW.path,
label.label_id,
str_to_access(NEW.access),
+ str_to_access(NEW.access_reverse),
app_path_type.app_path_type_id
FROM application_view, app_path_type, label
WHERE application_view.name = NEW.owner_app_label_name AND
FROM permission_view
WHERE permission_view.name = "ALL_APPS" AND
permission_view.type_name = "ALL_APPS");
+ -- Delete paths
+ DELETE FROM path_view
+ WHERE path_view.owner_app_label_name=OLD.app_name;
+
END;
-- APP PERMISSION VOLATILE VIEW ------------------------------------------------
-- ltl = label to label
DROP VIEW IF EXISTS ltl_permission_permission_rule_view;
CREATE VIEW ltl_permission_permission_rule_view AS
-SELECT (CASE WHEN is_reverse = 0 THEN app1.name ELSE app2.name END) AS subject,
- (CASE WHEN is_reverse = 1 THEN app1.name ELSE app2.name END) AS object,
+SELECT app1.name AS subject,
+ app2.name AS object,
p.access,
app1.is_volatile OR app2.is_volatile AS is_volatile
FROM permission_permission_rule AS p
INNER JOIN app_label_with_permission_view AS app1 USING(permission_id)
INNER JOIN app_label_with_permission_view AS app2
ON app2.permission_id = p.target_permission_id
-WHERE app1.app_id != app2.app_id;
+WHERE is_reverse = 0 AND app1.app_id != app2.app_id
+UNION ALL
+SELECT app2.name AS subject,
+ app1.name AS object,
+ p.access,
+ app1.is_volatile OR app2.is_volatile AS is_volatile
+FROM permission_permission_rule AS p
+INNER JOIN app_label_with_permission_view AS app1 USING(permission_id)
+INNER JOIN app_label_with_permission_view AS app2
+ ON app2.permission_id = p.target_permission_id
+WHERE is_reverse = 1 AND app1.app_id != app2.app_id;
-- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
-- ltl = label to label
DROP VIEW IF EXISTS ltl_permission_label_rule_view;
CREATE VIEW ltl_permission_label_rule_view AS
-SELECT (CASE WHEN is_reverse = 0 THEN app.name ELSE label.name END) AS subject,
- (CASE WHEN is_reverse = 1 THEN app.name ELSE label.name END) AS object,
+SELECT app.name AS subject,
+ label.name AS object,
+ p.access,
+ app.is_volatile
+FROM permission_label_rule AS p
+INNER JOIN app_label_with_permission_view AS app USING(permission_id)
+INNER JOIN label USING(label_id)
+WHERE is_reverse = 0 AND app.name != label.name
+UNION ALL
+SELECT label.name AS subject,
+ app.name AS object,
p.access,
app.is_volatile
FROM permission_label_rule AS p
INNER JOIN app_label_with_permission_view AS app USING(permission_id)
INNER JOIN label USING(label_id)
-WHERE app.name != label.name;
+WHERE is_reverse = 1 AND app.name != label.name;
+
-- ltl = label to label
DROP VIEW IF EXISTS ltl_permission_app_path_type_rule_view;
CREATE VIEW ltl_permission_app_path_type_rule_view AS
-SELECT (CASE WHEN is_reverse = 0 THEN app.name ELSE label.name END) AS subject,
- (CASE WHEN is_reverse = 1 THEN app.name ELSE label.name END) AS object,
+SELECT app.name AS subject,
+ label.name AS object,
p.access,
app.is_volatile
FROM permission_app_path_type_rule AS p
INNER JOIN app_label_with_permission_view AS app USING(permission_id)
INNER JOIN app_path USING(app_path_type_id)
INNER JOIN label USING(label_id)
-WHERE app.name != label.name;
+WHERE is_reverse = 0 AND app.name != label.name
+UNION ALL
+SELECT label.name AS subject,
+ app.name AS object,
+ p.access,
+ app.is_volatile
+FROM permission_app_path_type_rule AS p
+INNER JOIN app_label_with_permission_view AS app USING(permission_id)
+INNER JOIN app_path USING(app_path_type_id)
+INNER JOIN label USING(label_id)
+WHERE is_reverse = 1 AND app.name != label.name;
+
+
+-- LABEL TO PATH TYPE RULE VIEW -------------------------------------------
+-- ltl = label to label
+DROP VIEW IF EXISTS ltl_label_app_path_type_rule_view;
+CREATE VIEW ltl_label_app_path_type_rule_view AS
+SELECT label.name AS subject,
+ path_label.name AS object,
+ l.access AS access,
+ 0 AS is_volatile
+FROM label_app_path_type_rule AS l
+INNER JOIN label USING(label_id)
+INNER JOIN app_path USING(app_path_type_id)
+INNER JOIN label AS path_label ON app_path.label_id = path_label.label_id
+WHERE is_reverse = 0 AND path_label.name != label.name
+UNION ALL
+SELECT path_label.name AS subject,
+ label.name AS object,
+ l.access AS access,
+ 0 AS is_volatile
+FROM label_app_path_type_rule AS l
+INNER JOIN label USING(label_id)
+INNER JOIN app_path USING(app_path_type_id)
+INNER JOIN label AS path_label ON app_path.label_id = path_label.label_id
+WHERE is_reverse = 1 AND path_label.name != label.name;
-- PERMISSION TO APPLICATION'S OWN PATHS ---------------------------------------
INNER JOIN label USING(label_id);
+-- PERMISSION FROM PATHS TO APPLICATIONS ---------------------------------------
+-- ltl = label to label
+DROP VIEW IF EXISTS ltl_app_path_reverse_view;
+CREATE VIEW ltl_app_path_reverse_view AS
+SELECT label.name AS subject,
+ application_view.name AS object,
+ app_path.access_reverse AS access
+FROM app_path
+INNER JOIN application_view USING(app_id)
+INNER JOIN label USING(label_id)
+WHERE app_path.access_reverse != 0 ;
+
+
-- SMACK RULES VIEWS -----------------------------------------------------------
DROP VIEW IF EXISTS all_smack_binary_rules_view;
CREATE VIEW all_smack_binary_rules_view AS
SELECT subject,
object,
- bitwise_or(access) AS access,
- MIN(is_volatile) AS is_volatile
+ access,
+ is_volatile
FROM (SELECT subject, object, access, is_volatile
FROM ltl_permission_permission_rule_view
UNION ALL
SELECT subject, object, access, is_volatile
FROM ltl_permission_app_path_type_rule_view
UNION ALL
+ SELECT subject, object, access, is_volatile
+ FROM ltl_label_app_path_type_rule_view
+ UNION ALL
SELECT subject, object, access, 0
FROM ltl_app_path_view
- )
-GROUP BY subject, object
-ORDER BY subject, object ASC;
+ UNION ALL
+ SELECT subject, object, access, 0
+ FROM ltl_app_path_reverse_view
+ );
-- ALL INSERTED DATA VIEW ------------------------------------------------------
-- This view is used to clear the database from inserted rules.
SELECT subject, object,
s1.access & ~s2.access AS access_add,
s2.access & ~s1.access AS access_del
- FROM all_smack_binary_rules AS s1
- INNER JOIN history_smack_rule AS s2
+ FROM current_smack_rule_modified AS s1
+ INNER JOIN history_smack_rule_modified AS s2
USING (subject, object)
WHERE s1.access != s2.access
SELECT subject, object,
s1.access AS access_add,
0 AS access_del
- FROM all_smack_binary_rules AS s1
- LEFT JOIN history_smack_rule s2
+ FROM current_smack_rule_modified AS s1
+ LEFT JOIN history_smack_rule_modified s2
USING (subject, object)
WHERE s2.subject IS NULL AND
s2.object IS NULL
SELECT subject, object,
0 AS access_add,
s1.access AS access_del
- FROM history_smack_rule s1
- LEFT JOIN all_smack_binary_rules AS s2
+ FROM history_smack_rule_modified s1
+ LEFT JOIN current_smack_rule_modified AS s2
USING (subject, object)
WHERE s2.subject IS NULL AND
s2.object IS NULL
)
ORDER BY subject, object ASC;
-
--- Update here!
-PRAGMA schema_version = 1;
-
-COMMIT TRANSACTION;
\ No newline at end of file
+COMMIT TRANSACTION;
projects / platform / core / security / libprivilege-control.git / blobdiff