2 -- 1. Beware of updating schema!
3 -- We can drop views and triggers,
4 -- but we should copy data from tables
5 -- according to the schema version!
6 -- 2. If you change definition of tables
7 -- update the schema counter at the bottom!!
9 -- TODO: Use "USING" in joins whenever possible
11 .load librules-db-sql-udf.so
12 PRAGMA foreign_keys = ON;
13 PRAGMA auto_vacuum = NONE;
15 BEGIN EXCLUSIVE TRANSACTION;
17 -- PRAGMA cache_size = 2000;
19 CREATE TABLE IF NOT EXISTS app (
20 app_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
21 label_id INTEGER NOT NULL,
24 FOREIGN KEY(label_id) REFERENCES label(label_id)
26 CREATE INDEX IF NOT EXISTS app_index ON app(app_id, label_id);
29 CREATE TABLE IF NOT EXISTS app_permission (
30 app_id INTEGER NOT NULL,
31 permission_id INTEGER NOT NULL,
32 is_volatile INTEGER NOT NULL DEFAULT 0,
33 is_enabled INTEGER NOT NULL DEFAULT 1,
35 PRIMARY KEY(app_id, permission_id),
37 FOREIGN KEY(app_id) REFERENCES app(app_id),
38 FOREIGN KEY(permission_id) REFERENCES permission(permission_id)
41 CREATE TABLE IF NOT EXISTS app_path (
42 app_id INTEGER NOT NULL,
44 label_id INTEGER NOT NULL,
45 access INTEGER NOT NULL,
46 access_reverse INTEGER NOT NULL,
47 app_path_type_id INTEGER NOT NULL ,
50 -- Desired behavior should be:
51 -- allow one app to register a path only once (already implemented by the primary key)
52 -- prohibit two apps registering the same path with different labels (probably cannot be done by SQL constraints)
53 -- allow two apps to register the same path if label is also same
55 PRIMARY KEY (app_id, path),
57 FOREIGN KEY(app_id) REFERENCES app(app_id),
58 FOREIGN KEY(label_id) REFERENCES label(label_id),
59 FOREIGN KEY(app_path_type_id) REFERENCES app_path_type(app_path_type_id)
62 CREATE TABLE IF NOT EXISTS app_path_type (
63 app_path_type_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
68 -- CREATE INDEX IF NOT EXISTS app_path_type_index ON app_path_type(app_path_type_id, name);
71 CREATE TABLE IF NOT EXISTS permission_permission_rule (
72 permission_id INTEGER NOT NULL,
73 target_permission_id INTEGER NOT NULL,
74 access INTEGER NOT NULL DEFAULT 0,
75 is_reverse INTEGER NOT NULL DEFAULT 0,
77 PRIMARY KEY (permission_id, target_permission_id, is_reverse),
79 FOREIGN KEY(permission_id) REFERENCES permission(permission_id),
80 FOREIGN KEY(target_permission_id) REFERENCES permission(permission_id)
83 CREATE TABLE IF NOT EXISTS permission_label_rule (
84 permission_id INTEGER NOT NULL,
85 label_id INTEGER NOT NULL,
86 access INTEGER NOT NULL DEFAULT 0,
87 is_reverse INTEGER NOT NULL DEFAULT 0,
89 PRIMARY KEY (permission_id,label_id, is_reverse),
91 FOREIGN KEY(permission_id) REFERENCES permission(permission_id),
92 FOREIGN KEY(label_id) REFERENCES label(label_id)
95 CREATE TABLE IF NOT EXISTS permission_app_path_type_rule (
96 permission_id INTEGER NOT NULL,
97 app_path_type_id INTEGER NOT NULL,
98 access INTEGER NOT NULL DEFAULT 0,
99 is_reverse INTEGER NOT NULL DEFAULT 0,
101 PRIMARY KEY (permission_id, app_path_type_id, is_reverse),
103 FOREIGN KEY(permission_id) REFERENCES permission(permission_id),
104 FOREIGN KEY(app_path_type_id) REFERENCES app_path_type(app_path_type_id)
107 CREATE TABLE IF NOT EXISTS label_app_path_type_rule (
108 label_id INTEGER NOT NULL,
109 app_path_type_id INTEGER NOT NULL,
110 access INTEGER NOT NULL DEFAULT 0,
111 is_reverse INTEGER NOT NULL DEFAULT 0,
113 PRIMARY KEY (label_id, app_path_type_id, is_reverse),
115 FOREIGN KEY(label_id) REFERENCES label(label_id),
116 FOREIGN KEY(app_path_type_id) REFERENCES app_path_type(app_path_type_id)
119 CREATE TABLE IF NOT EXISTS label (
120 label_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
126 CREATE TABLE IF NOT EXISTS permission_type (
127 permission_type_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
128 type_name TEXT NOT NULL,
133 CREATE TABLE IF NOT EXISTS permission (
134 permission_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
135 permission_type_id INTEGER NOT NULL,
138 UNIQUE (name, permission_type_id),
140 FOREIGN KEY(permission_type_id) REFERENCES permission_type(permission_type_id)
144 CREATE TABLE IF NOT EXISTS all_smack_binary_rules(
145 subject TEXT NOT NULL,
146 object TEXT NOT NULL,
147 access INTEGER NOT NULL,
148 is_volatile INTEGER NOT NULL
151 -- TEMPORARY TABLES ------------------------------------------------------------
152 -- Definitions are repeated in code.
154 CREATE TEMPORARY TABLE history_smack_rule(
155 subject TEXT NOT NULL,
156 object TEXT NOT NULL,
157 access INTEGER NOT NULL
161 CREATE TEMPORARY TABLE modified_label(
167 CREATE TEMPORARY TABLE all_smack_binary_rules_modified(
168 subject TEXT NOT NULL,
169 object TEXT NOT NULL,
170 access INTEGER NOT NULL
174 CREATE TEMPORARY TABLE history_smack_rule_modified(
175 subject TEXT NOT NULL,
176 object TEXT NOT NULL,
177 access INTEGER NOT NULL
181 -- PERMISSION VIEW -------------------------------------------------------------
182 DROP VIEW IF EXISTS permission_view;
183 CREATE VIEW permission_view AS
184 SELECT permission.permission_id, permission.name, permission_type.type_name
186 INNER JOIN permission_type USING(permission_type_id);
188 DROP TRIGGER IF EXISTS permission_view_insert_trigger;
189 CREATE TRIGGER permission_view_insert_trigger
190 INSTEAD OF INSERT ON permission_view
192 -- Add the permission
193 INSERT OR IGNORE INTO permission(name,permission_type_id)
194 SELECT NEW.name, permission_type.permission_type_id
196 WHERE permission_type.type_name = NEW.type_name;
199 -- Delete the previous definition of the permission
200 DELETE FROM permission_label_rule_view
201 WHERE permission_label_rule_view.permission_name = NEW.name AND
202 permission_label_rule_view.permission_type_name = NEW.type_name;
205 -- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
206 DROP VIEW IF EXISTS permission_label_rule_view;
207 CREATE VIEW permission_label_rule_view AS
209 permission_view.permission_id AS permission_id,
210 permission_view.name AS permission_name,
211 permission_view.type_name AS permission_type_name,
212 label.name AS label_name,
213 permission_label_rule.access AS access,
214 permission_label_rule.is_reverse AS is_reverse
215 FROM permission_label_rule
216 LEFT JOIN permission_view USING(permission_id)
217 LEFT JOIN label USING(label_id);
220 -- Preferred way of adding permission rules would be to use these ONE, multi-row
221 -- insert statement, with one check of a condition
222 -- that there is such permission id. It's impossible to make those inserts in C,
223 -- so the programmer has to secure, that there is a permission with a given id.
224 -- (Check it and insert in the same transaction)
225 -- In runtime we accept ONLY inserts with label.
226 -- All other kinds of permissions are filled during the database creation.
227 DROP TRIGGER IF EXISTS permission_label_rule_view_insert_trigger;
228 CREATE TRIGGER permission_label_rule_view_insert_trigger
229 INSTEAD OF INSERT ON permission_label_rule_view
231 -- Adding api features adds a label it it's not present.
232 INSERT OR IGNORE INTO label(name) VALUES (NEW.label_name);
234 INSERT OR REPLACE INTO permission_label_rule(permission_id,
238 SELECT NEW.permission_id,
240 str_to_access(NEW.access),
243 WHERE label.name = NEW.label_name;
247 -- TODO: Potential problem - undeleted labels.
248 DROP TRIGGER IF EXISTS permission_label_rule_view_delete_trigger;
249 CREATE TRIGGER permission_label_rule_view_delete_trigger
250 INSTEAD OF DELETE ON permission_label_rule_view
252 DELETE FROM permission_label_rule
253 WHERE permission_label_rule.permission_id
254 IN (SELECT permission_view.permission_id
256 WHERE permission_view.name = OLD.permission_name AND
257 permission_view.type_name = OLD.permission_type_name);
261 -- PERMISSION TO APP PATH TYPE RULE VIEW ---------------------------------------
262 DROP VIEW IF EXISTS permission_app_path_type_rule_view;
263 CREATE VIEW permission_app_path_type_rule_view AS
265 permission_view.permission_id AS permission_id,
266 permission_view.name AS permission_name,
267 permission_view.type_name AS permission_type_name,
268 app_path_type.name AS app_path_type_name,
269 permission_app_path_type_rule.access AS access,
270 permission_app_path_type_rule.is_reverse AS is_reverse
271 FROM permission_app_path_type_rule
272 LEFT JOIN permission_view USING(permission_id)
273 LEFT JOIN app_path_type USING(app_path_type_id);
276 DROP TRIGGER IF EXISTS permission_app_path_type_rule_view_insert_trigger;
277 CREATE TRIGGER permission_app_path_type_rule_view_insert_trigger
279 ON permission_app_path_type_rule_view
280 WHEN NEW.permission_id IS NULL
282 INSERT INTO permission_app_path_type_rule(permission_id,
286 SELECT permission_view.permission_id,
287 app_path_type.app_path_type_id,
288 str_to_access(NEW.access),
290 FROM permission_view, app_path_type
291 WHERE permission_view.name = NEW.permission_name AND
292 permission_view.type_name = NEW.permission_type_name AND
293 app_path_type.name = NEW.app_path_type_name;
296 DROP TRIGGER IF EXISTS permission_app_path_type_rule_view_delete_trigger;
297 CREATE TRIGGER permission_app_path_type_rule_view_delete_trigger
299 ON permission_app_path_type_rule_view
302 DELETE FROM permission_app_path_type_rule
303 WHERE permission_app_path_type_rule.permission_id
304 IN (SELECT permission_view.permission_id
306 WHERE permission_view.name = OLD.permission_name AND
307 permission_view.type_name = OLD.permission_type_name);
311 CREATE TRIGGER permission_app_path_type_id_rule_view_insert_trigger
313 ON permission_app_path_type_rule_view
314 WHEN NEW.permission_id IS NOT NULL
316 INSERT OR REPLACE INTO permission_app_path_type_rule(permission_id,
320 SELECT NEW.permission_id,
321 app_path_type.app_path_type_id,
322 str_to_access(NEW.access),
325 WHERE app_path_type.name = NEW.app_path_type_name;
329 -- LABEL TO APP PATH TYPE RULE VIEW --------------------------------------------
330 DROP VIEW IF EXISTS label_app_path_type_rule_view;
331 CREATE VIEW label_app_path_type_rule_view AS
333 label_app_path_type_rule.label_id AS label_id,
334 label.name AS label_name,
335 app_path_type.name AS app_path_type_name,
336 label_app_path_type_rule.access AS access,
337 label_app_path_type_rule.is_reverse AS is_reverse
338 FROM label_app_path_type_rule
339 LEFT JOIN label USING(label_id)
340 LEFT JOIN app_path_type USING(app_path_type_id);
343 DROP TRIGGER IF EXISTS label_app_path_type_rule_view_insert_trigger;
344 CREATE TRIGGER label_app_path_type_rule_view_insert_trigger
346 ON label_app_path_type_rule_view
348 INSERT OR IGNORE INTO label(name) VALUES (NEW.label_name);
350 INSERT INTO label_app_path_type_rule(label_id,
354 SELECT label.label_id,
355 app_path_type.app_path_type_id,
356 str_to_access(NEW.access),
358 FROM label, app_path_type
359 WHERE label.name = NEW.label_name AND
360 app_path_type.name = NEW.app_path_type_name;
364 DROP TRIGGER IF EXISTS label_app_path_type_rule_view_delete_trigger;
365 CREATE TRIGGER label_app_path_type_rule_view_delete_trigger
367 ON label_app_path_type_rule_view
369 -- Delete the rules with this label
370 DELETE FROM label_app_path_type_rule
371 WHERE label_app_path_type_rule.label_id
372 IN (SELECT label.label_id
374 WHERE label.name = OLD.label_name);
376 -- Delete the label if it's not referenced
377 DELETE FROM label_view
378 WHERE label_view.name = OLD.label_name;
381 -- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
382 DROP VIEW IF EXISTS permission_permission_rule_view;
383 CREATE VIEW permission_permission_rule_view AS
385 tmp_permission_view.permission_id AS permission_id,
386 tmp_permission_view.name AS permission_name,
387 tmp_permission_view.type_name AS permission_type_name,
388 tmp_target_permission_view.name AS target_permission_name,
389 tmp_target_permission_view.type_name AS target_permission_type_name,
390 permission_permission_rule.access AS access,
391 permission_permission_rule.is_reverse AS is_reverse
392 FROM permission_permission_rule
393 LEFT JOIN permission_view AS tmp_permission_view USING(permission_id)
394 LEFT JOIN permission_view AS tmp_target_permission_view
395 ON permission_permission_rule.target_permission_id = tmp_target_permission_view.permission_id;
398 -- Trigger for manual addition of rules.
399 DROP TRIGGER IF EXISTS permission_permission_rule_view_insert_trigger;
400 CREATE TRIGGER permission_permission_rule_view_insert_trigger
401 INSTEAD OF INSERT ON permission_permission_rule_view
404 INSERT OR REPLACE INTO permission_permission_rule(permission_id,
405 target_permission_id,
408 SELECT tmp_permission_view.permission_id,
409 tmp_target_permission_view.permission_id,
410 str_to_access(NEW.access),
412 FROM permission_view AS tmp_permission_view,
413 permission_view AS tmp_target_permission_view
414 WHERE tmp_permission_view.name = NEW.permission_name AND
415 tmp_permission_view.type_name = NEW.permission_type_name AND
416 tmp_target_permission_view.name = NEW.target_permission_name AND
417 tmp_target_permission_view.type_name = NEW.target_permission_type_name;
421 DROP TRIGGER IF EXISTS permission_permission_rule_view_delete_trigger;
422 CREATE TRIGGER permission_permission_rule_view_delete_trigger
423 INSTEAD OF DELETE ON permission_permission_rule_view
426 DELETE FROM permission_permission_rule_view
427 WHERE permission_permission_rule_view.permission_id
428 IN (SELECT permission_view.permission_id
430 WHERE permission_view.name = OLD.permission_name AND
431 permission_view.type_name = OLD.permission_type_name);
436 -- LABEL VIEW ------------------------------------------------------------------
437 -- There are no INSTEAD OF triggers on regular tables.
438 -- We use a view to delete unreferenced labels:
439 DROP VIEW IF EXISTS label_view;
440 CREATE VIEW label_view AS SELECT * FROM label;
442 DROP TRIGGER IF EXISTS label_view_delete_trigger;
443 CREATE TRIGGER label_view_delete_trigger
444 INSTEAD OF DELETE ON label_view
445 WHEN OLD.label_id NOT IN (SELECT app.label_id
447 OLD.label_id NOT IN (SELECT permission_label_rule.label_id
448 FROM permission_label_rule) AND
449 OLD.label_id NOT IN (SELECT app_path.label_id
451 OLD.label_id NOT IN (SELECT label_app_path_type_rule.label_id
452 FROM label_app_path_type_rule)
454 DELETE FROM label WHERE label.name = OLD.name;
458 -- APPLICATION VIEW ------------------------------------------------------------
459 DROP VIEW IF EXISTS application_view;
460 CREATE VIEW application_view AS
461 SELECT app.app_id, label.name
463 INNER JOIN app USING(label_id);
465 DROP TRIGGER IF EXISTS application_view_insert_trigger;
466 CREATE TRIGGER application_view_insert_trigger
467 INSTEAD OF INSERT ON application_view
469 -- The app's label could have been added by the permission.
470 INSERT OR IGNORE INTO label(name) VALUES (NEW.name);
473 INSERT INTO app(label_id)
476 WHERE label.name = NEW.name;
478 -- Add the permission granted to all applications
479 INSERT INTO app_permission_view(app_id, name, type_name, is_volatile, is_enabled)
480 VALUES (last_insert_rowid(), "ALL_APPS", "ALL_APPS", 0, 1);
485 DROP TRIGGER IF EXISTS application_view_delete_trigger;
486 CREATE TRIGGER application_view_delete_trigger
487 INSTEAD OF DELETE ON application_view
489 -- Delete rules that correspond to app's paths:
490 DELETE FROM permission_label_rule
491 WHERE permission_label_rule.label_id IN
492 (SELECT app_path.label_id
494 INNER JOIN application_view USING(app_id)
495 WHERE application_view.name = OLD.name);
498 DELETE FROM path_view
499 WHERE path_view.owner_app_label_name=OLD.name;
501 -- Delete apps permissions:
502 DELETE FROM app_permission
503 WHERE app_permission.app_id
504 IN (SELECT application_view.app_id
505 FROM application_view
506 WHERE application_view.name = OLD.name
509 -- Delete application
511 WHERE app.app_id IN (SELECT application_view.app_id
512 FROM application_view
513 WHERE application_view.name = OLD.name
517 DELETE FROM label_view
518 WHERE label_view.name = OLD.name;
522 -- PATH VIEW -------------------------------------------------------------------
523 DROP VIEW IF EXISTS path_view;
524 CREATE VIEW path_view AS
525 SELECT application_view.name AS owner_app_label_name,
526 app_path.path AS path,
527 label.name AS path_label_name,
528 app_path.access AS access,
529 app_path.access_reverse AS access_reverse,
530 app_path_type.name AS path_type_name
533 LEFT JOIN app_path_type USING (app_path_type_id)
534 LEFT JOIN application_view USING (app_id)
535 LEFT JOIN label USING (label_id);
538 -- For an existing application we add a path.
539 DROP TRIGGER IF EXISTS path_view_insert_trigger;
540 CREATE TRIGGER path_view_insert_trigger
541 INSTEAD OF INSERT ON path_view
542 WHEN NEW.owner_app_label_name IN (SELECT application_view.name
543 FROM application_view)
545 -- The path's label could have been added by the permission.
546 INSERT OR IGNORE INTO label(name) VALUES (NEW.path_label_name);
549 INSERT INTO app_path(app_id, path, label_id, access, access_reverse, app_path_type_id)
550 SELECT application_view.app_id,
553 str_to_access(NEW.access),
554 str_to_access(NEW.access_reverse),
555 app_path_type.app_path_type_id
556 FROM application_view, app_path_type, label
557 WHERE application_view.name = NEW.owner_app_label_name AND
558 app_path_type.name = NEW.path_type_name AND
559 label.name = NEW.path_label_name;
562 DROP TRIGGER IF EXISTS path_view_delete_trigger;
563 CREATE TRIGGER path_view_delete_trigger
564 INSTEAD OF DELETE ON path_view
568 WHERE app_path.app_id IN (SELECT app.app_id
570 WHERE label.name = OLD.owner_app_label_name AND
571 app.label_id = label.label_id);
573 -- Delete the path's label if it's not used any more
574 DELETE FROM label_view WHERE label_view.name = OLD.path_label_name;
577 -- APP PERMISSION LIST VIEW ----------------------------------------------------
578 -- Used in check_app_permission_internal to check if permissions are present
579 -- TODO: Check if SQLite optimizer doesn't change app_permission_view to the same code.
580 DROP VIEW IF EXISTS app_permission_list_view;
581 CREATE VIEW app_permission_list_view AS
582 SELECT app_permission.app_id AS app_id,
583 app_permission.permission_id AS permission_id,
584 permission_view.name AS permission_name,
585 permission_view.type_name AS permission_type_name,
586 app_permission.is_volatile AS is_volatile,
587 app_permission.is_enabled AS is_enabled
589 INNER JOIN permission_view USING(permission_id);
594 -- APP PERMISSION VIEW ---------------------------------------------------------
595 DROP VIEW IF EXISTS app_permission_view;
596 CREATE VIEW app_permission_view AS
597 SELECT application_view.app_id,
598 application_view.name AS app_name,
599 permission_view.permission_id,
600 permission_view.name,
601 permission_view.type_name,
602 app_permission.is_volatile,
603 app_permission.is_enabled
605 INNER JOIN application_view USING(app_id)
606 INNER JOIN permission_view USING(permission_id);
609 DROP TRIGGER IF EXISTS app_permission_view_insert_trigger;
610 CREATE TRIGGER app_permission_view_insert_trigger
611 INSTEAD OF INSERT ON app_permission_view
613 INSERT INTO app_permission(app_id, permission_id, is_volatile, is_enabled)
615 permission_view.permission_id,
619 WHERE permission_view.name = NEW.name AND
620 permission_view.type_name = NEW.type_name;
626 -- It's forbidden do change permission from not volatile to volatile.
627 -- We have to check it before inserting anything.
628 -- Used in updating permissions
629 DROP TRIGGER IF EXISTS app_permission_view_update_trigger;
630 CREATE TRIGGER app_permission_view_update_trigger
631 INSTEAD OF UPDATE ON app_permission_view
633 UPDATE OR IGNORE app_permission
634 SET is_enabled = NEW.is_enabled
635 WHERE app_permission.app_id = OLD.app_id AND
636 app_permission.permission_id
637 IN (SELECT permission_view.permission_id
639 WHERE permission_view.name = OLD.name AND
640 permission_view.type_name = OLD.type_name
645 DROP TRIGGER IF EXISTS app_permission_view_delete_trigger;
646 CREATE TRIGGER app_permission_view_delete_trigger
647 INSTEAD OF DELETE ON app_permission_view
649 DELETE FROM app_permission
650 WHERE app_permission.app_id
651 IN (SELECT application_view.app_id
652 FROM application_view
653 WHERE application_view.name = OLD.app_name
656 app_permission.permission_id NOT IN (SELECT permission_view.permission_id
658 WHERE permission_view.name = "ALL_APPS" AND
659 permission_view.type_name = "ALL_APPS");
661 DELETE FROM path_view
662 WHERE path_view.owner_app_label_name=OLD.app_name;
666 -- APP PERMISSION VOLATILE VIEW ------------------------------------------------
667 DROP VIEW IF EXISTS app_permission_volatile_view;
668 CREATE VIEW app_permission_volatile_view AS
670 FROM app_permission_view
671 WHERE app_permission_view.is_volatile = 1;
674 DROP TRIGGER IF EXISTS app_permission_volatile_view_delete_trigger;
675 CREATE TRIGGER app_permission_volatile_view_delete_trigger
676 INSTEAD OF DELETE ON app_permission_volatile_view
678 DELETE FROM app_permission
679 WHERE app_permission.is_volatile = 1 AND
680 app_permission.app_id
681 IN (SELECT application_view.app_id
682 FROM application_view
683 WHERE application_view.name = OLD.app_name
688 -- APPLICATIONS PERMISSIONS ID -------------------------------------------------
689 -- All applications and their permissions
690 DROP VIEW IF EXISTS app_label_with_permission_view;
691 CREATE VIEW app_label_with_permission_view AS
692 SELECT app_permission.permission_id,
693 application_view.name,
694 application_view.app_id,
695 app_permission.is_volatile
697 INNER JOIN application_view USING(app_id)
698 WHERE app_permission.is_enabled = 1;
702 -- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
703 -- ltl = label to label
704 DROP VIEW IF EXISTS ltl_permission_permission_rule_view;
705 CREATE VIEW ltl_permission_permission_rule_view AS
706 SELECT (CASE WHEN is_reverse = 0 THEN app1.name ELSE app2.name END) AS subject,
707 (CASE WHEN is_reverse = 1 THEN app1.name ELSE app2.name END) AS object,
709 app1.is_volatile OR app2.is_volatile AS is_volatile
710 FROM permission_permission_rule AS p
711 INNER JOIN app_label_with_permission_view AS app1 USING(permission_id)
712 INNER JOIN app_label_with_permission_view AS app2
713 ON app2.permission_id = p.target_permission_id
714 WHERE app1.app_id != app2.app_id;
716 -- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
717 -- ltl = label to label
718 DROP VIEW IF EXISTS ltl_permission_label_rule_view;
719 CREATE VIEW ltl_permission_label_rule_view AS
720 SELECT (CASE WHEN is_reverse = 0 THEN app.name ELSE label.name END) AS subject,
721 (CASE WHEN is_reverse = 1 THEN app.name ELSE label.name END) AS object,
724 FROM permission_label_rule AS p
725 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
726 INNER JOIN label USING(label_id)
727 WHERE app.name != label.name;
731 -- PERMISSION TO PATH TYPE RULE VIEW -------------------------------------------
732 -- ltl = label to label
733 DROP VIEW IF EXISTS ltl_permission_app_path_type_rule_view;
734 CREATE VIEW ltl_permission_app_path_type_rule_view AS
735 SELECT (CASE WHEN is_reverse = 0 THEN app.name ELSE label.name END) AS subject,
736 (CASE WHEN is_reverse = 1 THEN app.name ELSE label.name END) AS object,
739 FROM permission_app_path_type_rule AS p
740 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
741 INNER JOIN app_path USING(app_path_type_id)
742 INNER JOIN label USING(label_id)
743 WHERE app.name != label.name;
746 -- LABEL TO PATH TYPE RULE VIEW -------------------------------------------
747 -- ltl = label to label
748 DROP VIEW IF EXISTS ltl_label_app_path_type_rule_view;
749 CREATE VIEW ltl_label_app_path_type_rule_view AS
750 SELECT (CASE WHEN is_reverse = 0 THEN label.name ELSE path_label.name END) AS subject,
751 (CASE WHEN is_reverse = 1 THEN label.name ELSE path_label.name END) AS object,
754 FROM label_app_path_type_rule AS l
755 INNER JOIN label USING(label_id)
756 INNER JOIN app_path USING(app_path_type_id)
757 INNER JOIN label AS path_label ON app_path.label_id = path_label.label_id
758 WHERE path_label.name != label.name;
761 -- PERMISSION TO APPLICATION'S OWN PATHS ---------------------------------------
762 -- ltl = label to label
763 DROP VIEW IF EXISTS ltl_app_path_view;
764 CREATE VIEW ltl_app_path_view AS
765 SELECT application_view.name AS subject,
766 label.name AS object,
767 app_path.access AS access
769 INNER JOIN application_view USING(app_id)
770 INNER JOIN label USING(label_id);
773 -- PERMISSION FROM PATHS TO APPLICATIONS ---------------------------------------
774 -- ltl = label to label
775 DROP VIEW IF EXISTS ltl_app_path_reverse_view;
776 CREATE VIEW ltl_app_path_reverse_view AS
777 SELECT label.name AS subject,
778 application_view.name AS object,
779 app_path.access_reverse AS access
781 INNER JOIN application_view USING(app_id)
782 INNER JOIN label USING(label_id)
783 WHERE app_path.access_reverse != 0 ;
786 -- SMACK RULES VIEWS -----------------------------------------------------------
787 DROP VIEW IF EXISTS all_smack_binary_rules_view;
788 CREATE VIEW all_smack_binary_rules_view AS
791 bitwise_or(access) AS access,
792 MIN(is_volatile) AS is_volatile
793 FROM (SELECT subject, object, access, is_volatile
794 FROM ltl_permission_permission_rule_view
796 SELECT subject, object, access, is_volatile
797 FROM ltl_permission_label_rule_view
799 SELECT subject, object, access, is_volatile
800 FROM ltl_permission_app_path_type_rule_view
802 SELECT subject, object, access, is_volatile
803 FROM ltl_label_app_path_type_rule_view
805 SELECT subject, object, access, 0
806 FROM ltl_app_path_view
808 SELECT subject, object, access, 0
809 FROM ltl_app_path_reverse_view
811 GROUP BY subject, object
812 ORDER BY subject, object ASC;
814 -- ALL INSERTED DATA VIEW ------------------------------------------------------
815 -- This view is used to clear the database from inserted rules.
816 -- We loose all information about installed applications
818 DROP VIEW IF EXISTS all_inserted_data;
819 CREATE VIEW all_inserted_data AS
823 DROP TRIGGER IF EXISTS all_inserted_data_delete_trigger;
824 CREATE TRIGGER all_inserted_data_delete_trigger INSTEAD OF
825 DELETE ON all_inserted_data
827 DELETE FROM permission_label_rule;
828 DELETE FROM permission_permission_rule;
829 DELETE FROM permission_app_path_type_rule;
831 DELETE FROM app_permission;
833 DELETE FROM permission;
834 DELETE FROM permission_type;
836 DELETE FROM app_path;
837 DELETE FROM app_path_type;
845 -- SMACK RULES MODIFICATIONS VIEW ----------------------------------------------
846 -- This definition is repeated during opening a connection with the database.
847 -- Used to get all smack rules, even volatile.
848 -- Ensure it's the same!
849 CREATE TEMPORARY VIEW modified_smack_rules AS
850 SELECT subject, object,
851 access_to_str(access_add) AS access_add,
852 access_to_str(access_del) AS access_del
854 SELECT subject, object,
855 s1.access & ~s2.access AS access_add,
856 s2.access & ~s1.access AS access_del
857 FROM all_smack_binary_rules AS s1
858 INNER JOIN history_smack_rule AS s2
859 USING (subject, object)
860 WHERE s1.access != s2.access
864 SELECT subject, object,
865 s1.access AS access_add,
867 FROM all_smack_binary_rules AS s1
868 LEFT JOIN history_smack_rule s2
869 USING (subject, object)
870 WHERE s2.subject IS NULL AND
875 SELECT subject, object,
877 s1.access AS access_del
878 FROM history_smack_rule s1
879 LEFT JOIN all_smack_binary_rules AS s2
880 USING (subject, object)
881 WHERE s2.subject IS NULL AND
884 ORDER BY subject, object ASC;
888 PRAGMA schema_version = 1.1;