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 app_path_type_id INTEGER NOT NULL ,
49 -- Desired behavior should be:
50 -- allow one app to register a path only once (already implemented by the primary key)
51 -- prohibit two apps registering the same path with different labels (probably cannot be done by SQL constraints)
52 -- allow two apps to register the same path if label is also same
54 PRIMARY KEY (app_id, path),
56 FOREIGN KEY(app_id) REFERENCES app(app_id),
57 FOREIGN KEY(label_id) REFERENCES label(label_id),
58 FOREIGN KEY(app_path_type_id) REFERENCES app_path_type(app_path_type_id)
61 CREATE TABLE IF NOT EXISTS app_path_type (
62 app_path_type_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
67 -- CREATE INDEX IF NOT EXISTS app_path_type_index ON app_path_type(app_path_type_id, name);
70 CREATE TABLE IF NOT EXISTS permission_permission_rule (
71 permission_id INTEGER NOT NULL,
72 target_permission_id INTEGER NOT NULL,
73 access INTEGER NOT NULL DEFAULT 0,
74 is_reverse INTEGER NOT NULL DEFAULT 0,
76 PRIMARY KEY (permission_id, target_permission_id, is_reverse),
78 FOREIGN KEY(permission_id) REFERENCES permission(permission_id),
79 FOREIGN KEY(target_permission_id) REFERENCES permission(permission_id)
82 CREATE TABLE IF NOT EXISTS permission_label_rule (
83 permission_id INTEGER NOT NULL,
84 label_id INTEGER NOT NULL,
85 access INTEGER NOT NULL DEFAULT 0,
86 is_reverse INTEGER NOT NULL DEFAULT 0,
88 PRIMARY KEY (permission_id,label_id, is_reverse),
90 FOREIGN KEY(permission_id) REFERENCES permission(permission_id),
91 FOREIGN KEY(label_id) REFERENCES label(label_id)
94 CREATE TABLE IF NOT EXISTS permission_app_path_type_rule (
95 permission_id INTEGER NOT NULL,
96 app_path_type_id INTEGER NOT NULL,
97 access INTEGER NOT NULL DEFAULT 0,
98 is_reverse INTEGER NOT NULL DEFAULT 0,
100 PRIMARY KEY (permission_id, app_path_type_id, is_reverse),
102 FOREIGN KEY(permission_id) REFERENCES permission(permission_id),
103 FOREIGN KEY(app_path_type_id) REFERENCES app_path_type(app_path_type_id)
106 CREATE TABLE IF NOT EXISTS label_app_path_type_rule (
107 label_id INTEGER NOT NULL,
108 app_path_type_id INTEGER NOT NULL,
109 access INTEGER NOT NULL DEFAULT 0,
110 is_reverse INTEGER NOT NULL DEFAULT 0,
112 PRIMARY KEY (label_id, app_path_type_id, is_reverse),
114 FOREIGN KEY(label_id) REFERENCES label(label_id),
115 FOREIGN KEY(app_path_type_id) REFERENCES app_path_type(app_path_type_id)
118 CREATE TABLE IF NOT EXISTS label (
119 label_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
125 CREATE TABLE IF NOT EXISTS permission_type (
126 permission_type_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
127 type_name TEXT NOT NULL,
132 CREATE TABLE IF NOT EXISTS permission (
133 permission_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
134 permission_type_id INTEGER NOT NULL,
137 UNIQUE (name, permission_type_id),
139 FOREIGN KEY(permission_type_id) REFERENCES permission_type(permission_type_id)
143 CREATE TABLE IF NOT EXISTS all_smack_binary_rules(
144 subject TEXT NOT NULL,
145 object TEXT NOT NULL,
146 access INTEGER NOT NULL,
147 is_volatile INTEGER NOT NULL
150 -- TEMPORARY TABLES ------------------------------------------------------------
151 -- Definitions are repeated in code.
153 CREATE TEMPORARY TABLE history_smack_rule(
154 subject TEXT NOT NULL,
155 object TEXT NOT NULL,
156 access INTEGER NOT NULL
160 CREATE TEMPORARY TABLE modified_label(
166 CREATE TEMPORARY TABLE all_smack_binary_rules_modified(
167 subject TEXT NOT NULL,
168 object TEXT NOT NULL,
169 access INTEGER NOT NULL
173 CREATE TEMPORARY TABLE history_smack_rule_modified(
174 subject TEXT NOT NULL,
175 object TEXT NOT NULL,
176 access INTEGER NOT NULL
180 -- PERMISSION VIEW -------------------------------------------------------------
181 DROP VIEW IF EXISTS permission_view;
182 CREATE VIEW permission_view AS
183 SELECT permission.permission_id, permission.name, permission_type.type_name
185 INNER JOIN permission_type USING(permission_type_id);
187 DROP TRIGGER IF EXISTS permission_view_insert_trigger;
188 CREATE TRIGGER permission_view_insert_trigger
189 INSTEAD OF INSERT ON permission_view
191 -- Add the permission
192 INSERT OR IGNORE INTO permission(name,permission_type_id)
193 SELECT NEW.name, permission_type.permission_type_id
195 WHERE permission_type.type_name = NEW.type_name;
198 -- Delete the previous definition of the permission
199 DELETE FROM permission_label_rule_view
200 WHERE permission_label_rule_view.permission_name = NEW.name AND
201 permission_label_rule_view.permission_type_name = NEW.type_name;
204 -- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
205 DROP VIEW IF EXISTS permission_label_rule_view;
206 CREATE VIEW permission_label_rule_view AS
208 permission_view.permission_id AS permission_id,
209 permission_view.name AS permission_name,
210 permission_view.type_name AS permission_type_name,
211 label.name AS label_name,
212 permission_label_rule.access AS access,
213 permission_label_rule.is_reverse AS is_reverse
214 FROM permission_label_rule
215 LEFT JOIN permission_view USING(permission_id)
216 LEFT JOIN label USING(label_id);
219 -- Preferred way of adding permission rules would be to use these ONE, multi-row
220 -- insert statement, with one check of a condition
221 -- that there is such permission id. It's impossible to make those inserts in C,
222 -- so the programmer has to secure, that there is a permission with a given id.
223 -- (Check it and insert in the same transaction)
224 -- In runtime we accept ONLY inserts with label.
225 -- All other kinds of permissions are filled during the database creation.
226 DROP TRIGGER IF EXISTS permission_label_rule_view_insert_trigger;
227 CREATE TRIGGER permission_label_rule_view_insert_trigger
228 INSTEAD OF INSERT ON permission_label_rule_view
230 -- Adding api features adds a label it it's not present.
231 INSERT OR IGNORE INTO label(name) VALUES (NEW.label_name);
233 INSERT OR REPLACE INTO permission_label_rule(permission_id,
237 SELECT NEW.permission_id,
239 str_to_access(NEW.access),
242 WHERE label.name = NEW.label_name;
246 -- TODO: Potential problem - undeleted labels.
247 DROP TRIGGER IF EXISTS permission_label_rule_view_delete_trigger;
248 CREATE TRIGGER permission_label_rule_view_delete_trigger
249 INSTEAD OF DELETE ON permission_label_rule_view
251 DELETE FROM permission_label_rule
252 WHERE permission_label_rule.permission_id
253 IN (SELECT permission_view.permission_id
255 WHERE permission_view.name = OLD.permission_name AND
256 permission_view.type_name = OLD.permission_type_name);
260 -- PERMISSION TO APP PATH TYPE RULE VIEW ---------------------------------------
261 DROP VIEW IF EXISTS permission_app_path_type_rule_view;
262 CREATE VIEW permission_app_path_type_rule_view AS
264 permission_view.permission_id AS permission_id,
265 permission_view.name AS permission_name,
266 permission_view.type_name AS permission_type_name,
267 app_path_type.name AS app_path_type_name,
268 permission_app_path_type_rule.access AS access,
269 permission_app_path_type_rule.is_reverse AS is_reverse
270 FROM permission_app_path_type_rule
271 LEFT JOIN permission_view USING(permission_id)
272 LEFT JOIN app_path_type USING(app_path_type_id);
275 DROP TRIGGER IF EXISTS permission_app_path_type_rule_view_insert_trigger;
276 CREATE TRIGGER permission_app_path_type_rule_view_insert_trigger
278 ON permission_app_path_type_rule_view
279 WHEN NEW.permission_id IS NULL
281 INSERT INTO permission_app_path_type_rule(permission_id,
285 SELECT permission_view.permission_id,
286 app_path_type.app_path_type_id,
287 str_to_access(NEW.access),
289 FROM permission_view, app_path_type
290 WHERE permission_view.name = NEW.permission_name AND
291 permission_view.type_name = NEW.permission_type_name AND
292 app_path_type.name = NEW.app_path_type_name;
295 DROP TRIGGER IF EXISTS permission_app_path_type_rule_view_delete_trigger;
296 CREATE TRIGGER permission_app_path_type_rule_view_delete_trigger
298 ON permission_app_path_type_rule_view
301 DELETE FROM permission_app_path_type_rule
302 WHERE permission_app_path_type_rule.permission_id
303 IN (SELECT permission_view.permission_id
305 WHERE permission_view.name = OLD.permission_name AND
306 permission_view.type_name = OLD.permission_type_name);
310 CREATE TRIGGER permission_app_path_type_id_rule_view_insert_trigger
312 ON permission_app_path_type_rule_view
313 WHEN NEW.permission_id IS NOT NULL
315 INSERT OR REPLACE INTO permission_app_path_type_rule(permission_id,
319 SELECT NEW.permission_id,
320 app_path_type.app_path_type_id,
321 str_to_access(NEW.access),
324 WHERE app_path_type.name = NEW.app_path_type_name;
328 -- LABEL TO APP PATH TYPE RULE VIEW --------------------------------------------
329 DROP VIEW IF EXISTS label_app_path_type_rule_view;
330 CREATE VIEW label_app_path_type_rule_view AS
332 label_app_path_type_rule.label_id AS label_id,
333 label.name AS label_name,
334 app_path_type.name AS app_path_type_name,
335 label_app_path_type_rule.access AS access,
336 label_app_path_type_rule.is_reverse AS is_reverse
337 FROM label_app_path_type_rule
338 LEFT JOIN label USING(label_id)
339 LEFT JOIN app_path_type USING(app_path_type_id);
342 DROP TRIGGER IF EXISTS label_app_path_type_rule_view_insert_trigger;
343 CREATE TRIGGER label_app_path_type_rule_view_insert_trigger
345 ON label_app_path_type_rule_view
347 INSERT OR IGNORE INTO label(name) VALUES (NEW.label_name);
349 INSERT INTO label_app_path_type_rule(label_id,
353 SELECT label.label_id,
354 app_path_type.app_path_type_id,
355 str_to_access(NEW.access),
357 FROM label, app_path_type
358 WHERE label.name = NEW.label_name AND
359 app_path_type.name = NEW.app_path_type_name;
363 DROP TRIGGER IF EXISTS label_app_path_type_rule_view_delete_trigger;
364 CREATE TRIGGER label_app_path_type_rule_view_delete_trigger
366 ON label_app_path_type_rule_view
368 -- Delete the rules with this label
369 DELETE FROM label_app_path_type_rule
370 WHERE label_app_path_type_rule.label_id
371 IN (SELECT label.label_id
373 WHERE label.name = OLD.label_name);
375 -- Delete the label if it's not referenced
376 DELETE FROM label_view
377 WHERE label_view.name = OLD.label_name;
380 -- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
381 DROP VIEW IF EXISTS permission_permission_rule_view;
382 CREATE VIEW permission_permission_rule_view AS
384 tmp_permission_view.permission_id AS permission_id,
385 tmp_permission_view.name AS permission_name,
386 tmp_permission_view.type_name AS permission_type_name,
387 tmp_target_permission_view.name AS target_permission_name,
388 tmp_target_permission_view.type_name AS target_permission_type_name,
389 permission_permission_rule.access AS access,
390 permission_permission_rule.is_reverse AS is_reverse
391 FROM permission_permission_rule
392 LEFT JOIN permission_view AS tmp_permission_view USING(permission_id)
393 LEFT JOIN permission_view AS tmp_target_permission_view
394 ON permission_permission_rule.target_permission_id = tmp_target_permission_view.permission_id;
397 -- Trigger for manual addition of rules.
398 DROP TRIGGER IF EXISTS permission_permission_rule_view_insert_trigger;
399 CREATE TRIGGER permission_permission_rule_view_insert_trigger
400 INSTEAD OF INSERT ON permission_permission_rule_view
403 INSERT OR REPLACE INTO permission_permission_rule(permission_id,
404 target_permission_id,
407 SELECT tmp_permission_view.permission_id,
408 tmp_target_permission_view.permission_id,
409 str_to_access(NEW.access),
411 FROM permission_view AS tmp_permission_view,
412 permission_view AS tmp_target_permission_view
413 WHERE tmp_permission_view.name = NEW.permission_name AND
414 tmp_permission_view.type_name = NEW.permission_type_name AND
415 tmp_target_permission_view.name = NEW.target_permission_name AND
416 tmp_target_permission_view.type_name = NEW.target_permission_type_name;
420 DROP TRIGGER IF EXISTS permission_permission_rule_view_delete_trigger;
421 CREATE TRIGGER permission_permission_rule_view_delete_trigger
422 INSTEAD OF DELETE ON permission_permission_rule_view
425 DELETE FROM permission_permission_rule_view
426 WHERE permission_permission_rule_view.permission_id
427 IN (SELECT permission_view.permission_id
429 WHERE permission_view.name = OLD.permission_name AND
430 permission_view.type_name = OLD.permission_type_name);
435 -- LABEL VIEW ------------------------------------------------------------------
436 -- There are no INSTEAD OF triggers on regular tables.
437 -- We use a view to delete unreferenced labels:
438 DROP VIEW IF EXISTS label_view;
439 CREATE VIEW label_view AS SELECT * FROM label;
441 DROP TRIGGER IF EXISTS label_view_delete_trigger;
442 CREATE TRIGGER label_view_delete_trigger
443 INSTEAD OF DELETE ON label_view
444 WHEN OLD.label_id NOT IN (SELECT app.label_id
446 OLD.label_id NOT IN (SELECT permission_label_rule.label_id
447 FROM permission_label_rule) AND
448 OLD.label_id NOT IN (SELECT app_path.label_id
450 OLD.label_id NOT IN (SELECT label_app_path_type_rule.label_id
451 FROM label_app_path_type_rule)
453 DELETE FROM label WHERE label.name = OLD.name;
457 -- APPLICATION VIEW ------------------------------------------------------------
458 DROP VIEW IF EXISTS application_view;
459 CREATE VIEW application_view AS
460 SELECT app.app_id, label.name
462 INNER JOIN app USING(label_id);
464 DROP TRIGGER IF EXISTS application_view_insert_trigger;
465 CREATE TRIGGER application_view_insert_trigger
466 INSTEAD OF INSERT ON application_view
468 -- The app's label could have been added by the permission.
469 INSERT OR IGNORE INTO label(name) VALUES (NEW.name);
472 INSERT INTO app(label_id)
475 WHERE label.name = NEW.name;
477 -- Add the permission granted to all applications
478 INSERT INTO app_permission_view(app_id, name, type_name, is_volatile, is_enabled)
479 VALUES (last_insert_rowid(), "ALL_APPS", "ALL_APPS", 0, 1);
484 DROP TRIGGER IF EXISTS application_view_delete_trigger;
485 CREATE TRIGGER application_view_delete_trigger
486 INSTEAD OF DELETE ON application_view
488 -- Delete rules that correspond to app's paths:
489 DELETE FROM permission_label_rule
490 WHERE permission_label_rule.label_id IN
491 (SELECT app_path.label_id
493 INNER JOIN application_view USING(app_id)
494 WHERE application_view.name = OLD.name);
497 DELETE FROM path_view
498 WHERE path_view.owner_app_label_name=OLD.name;
500 -- Delete apps permissions:
501 DELETE FROM app_permission
502 WHERE app_permission.app_id
503 IN (SELECT application_view.app_id
504 FROM application_view
505 WHERE application_view.name = OLD.name
508 -- Delete application
510 WHERE app.app_id IN (SELECT application_view.app_id
511 FROM application_view
512 WHERE application_view.name = OLD.name
516 DELETE FROM label_view
517 WHERE label_view.name = OLD.name;
521 -- PATH VIEW -------------------------------------------------------------------
522 DROP VIEW IF EXISTS path_view;
523 CREATE VIEW path_view AS
524 SELECT application_view.name AS owner_app_label_name,
525 app_path.path AS path,
526 label.name AS path_label_name,
527 app_path.access AS access,
528 app_path_type.name AS path_type_name
531 LEFT JOIN app_path_type USING (app_path_type_id)
532 LEFT JOIN application_view USING (app_id)
533 LEFT JOIN label USING (label_id);
536 -- For an existing application we add a path.
537 DROP TRIGGER IF EXISTS path_view_insert_trigger;
538 CREATE TRIGGER path_view_insert_trigger
539 INSTEAD OF INSERT ON path_view
540 WHEN NEW.owner_app_label_name IN (SELECT application_view.name
541 FROM application_view)
543 -- The path's label could have been added by the permission.
544 INSERT OR IGNORE INTO label(name) VALUES (NEW.path_label_name);
547 INSERT INTO app_path(app_id, path, label_id, access, app_path_type_id)
548 SELECT application_view.app_id,
551 str_to_access(NEW.access),
552 app_path_type.app_path_type_id
553 FROM application_view, app_path_type, label
554 WHERE application_view.name = NEW.owner_app_label_name AND
555 app_path_type.name = NEW.path_type_name AND
556 label.name = NEW.path_label_name;
559 DROP TRIGGER IF EXISTS path_view_delete_trigger;
560 CREATE TRIGGER path_view_delete_trigger
561 INSTEAD OF DELETE ON path_view
565 WHERE app_path.app_id IN (SELECT app.app_id
567 WHERE label.name = OLD.owner_app_label_name AND
568 app.label_id = label.label_id);
570 -- Delete the path's label if it's not used any more
571 DELETE FROM label_view WHERE label_view.name = OLD.path_label_name;
574 -- APP PERMISSION LIST VIEW ----------------------------------------------------
575 -- Used in check_app_permission_internal to check if permissions are present
576 -- TODO: Check if SQLite optimizer doesn't change app_permission_view to the same code.
577 DROP VIEW IF EXISTS app_permission_list_view;
578 CREATE VIEW app_permission_list_view AS
579 SELECT app_permission.app_id AS app_id,
580 app_permission.permission_id AS permission_id,
581 permission_view.name AS permission_name,
582 permission_view.type_name AS permission_type_name,
583 app_permission.is_volatile AS is_volatile,
584 app_permission.is_enabled AS is_enabled
586 INNER JOIN permission_view USING(permission_id);
591 -- APP PERMISSION VIEW ---------------------------------------------------------
592 DROP VIEW IF EXISTS app_permission_view;
593 CREATE VIEW app_permission_view AS
594 SELECT application_view.app_id,
595 application_view.name AS app_name,
596 permission_view.permission_id,
597 permission_view.name,
598 permission_view.type_name,
599 app_permission.is_volatile,
600 app_permission.is_enabled
602 INNER JOIN application_view USING(app_id)
603 INNER JOIN permission_view USING(permission_id);
606 DROP TRIGGER IF EXISTS app_permission_view_insert_trigger;
607 CREATE TRIGGER app_permission_view_insert_trigger
608 INSTEAD OF INSERT ON app_permission_view
610 INSERT INTO app_permission(app_id, permission_id, is_volatile, is_enabled)
612 permission_view.permission_id,
616 WHERE permission_view.name = NEW.name AND
617 permission_view.type_name = NEW.type_name;
623 -- It's forbidden do change permission from not volatile to volatile.
624 -- We have to check it before inserting anything.
625 -- Used in updating permissions
626 DROP TRIGGER IF EXISTS app_permission_view_update_trigger;
627 CREATE TRIGGER app_permission_view_update_trigger
628 INSTEAD OF UPDATE ON app_permission_view
630 UPDATE OR IGNORE app_permission
631 SET is_enabled = NEW.is_enabled
632 WHERE app_permission.app_id = OLD.app_id AND
633 app_permission.permission_id
634 IN (SELECT permission_view.permission_id
636 WHERE permission_view.name = OLD.name AND
637 permission_view.type_name = OLD.type_name
642 DROP TRIGGER IF EXISTS app_permission_view_delete_trigger;
643 CREATE TRIGGER app_permission_view_delete_trigger
644 INSTEAD OF DELETE ON app_permission_view
646 DELETE FROM app_permission
647 WHERE app_permission.app_id
648 IN (SELECT application_view.app_id
649 FROM application_view
650 WHERE application_view.name = OLD.app_name
653 app_permission.permission_id NOT IN (SELECT permission_view.permission_id
655 WHERE permission_view.name = "ALL_APPS" AND
656 permission_view.type_name = "ALL_APPS");
658 DELETE FROM path_view
659 WHERE path_view.owner_app_label_name=OLD.app_name;
663 -- APP PERMISSION VOLATILE VIEW ------------------------------------------------
664 DROP VIEW IF EXISTS app_permission_volatile_view;
665 CREATE VIEW app_permission_volatile_view AS
667 FROM app_permission_view
668 WHERE app_permission_view.is_volatile = 1;
671 DROP TRIGGER IF EXISTS app_permission_volatile_view_delete_trigger;
672 CREATE TRIGGER app_permission_volatile_view_delete_trigger
673 INSTEAD OF DELETE ON app_permission_volatile_view
675 DELETE FROM app_permission
676 WHERE app_permission.is_volatile = 1 AND
677 app_permission.app_id
678 IN (SELECT application_view.app_id
679 FROM application_view
680 WHERE application_view.name = OLD.app_name
685 -- APPLICATIONS PERMISSIONS ID -------------------------------------------------
686 -- All applications and their permissions
687 DROP VIEW IF EXISTS app_label_with_permission_view;
688 CREATE VIEW app_label_with_permission_view AS
689 SELECT app_permission.permission_id,
690 application_view.name,
691 application_view.app_id,
692 app_permission.is_volatile
694 INNER JOIN application_view USING(app_id)
695 WHERE app_permission.is_enabled = 1;
699 -- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
700 -- ltl = label to label
701 DROP VIEW IF EXISTS ltl_permission_permission_rule_view;
702 CREATE VIEW ltl_permission_permission_rule_view AS
703 SELECT (CASE WHEN is_reverse = 0 THEN app1.name ELSE app2.name END) AS subject,
704 (CASE WHEN is_reverse = 1 THEN app1.name ELSE app2.name END) AS object,
706 app1.is_volatile OR app2.is_volatile AS is_volatile
707 FROM permission_permission_rule AS p
708 INNER JOIN app_label_with_permission_view AS app1 USING(permission_id)
709 INNER JOIN app_label_with_permission_view AS app2
710 ON app2.permission_id = p.target_permission_id
711 WHERE app1.app_id != app2.app_id;
713 -- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
714 -- ltl = label to label
715 DROP VIEW IF EXISTS ltl_permission_label_rule_view;
716 CREATE VIEW ltl_permission_label_rule_view AS
717 SELECT (CASE WHEN is_reverse = 0 THEN app.name ELSE label.name END) AS subject,
718 (CASE WHEN is_reverse = 1 THEN app.name ELSE label.name END) AS object,
721 FROM permission_label_rule AS p
722 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
723 INNER JOIN label USING(label_id)
724 WHERE app.name != label.name;
728 -- PERMISSION TO PATH TYPE RULE VIEW -------------------------------------------
729 -- ltl = label to label
730 DROP VIEW IF EXISTS ltl_permission_app_path_type_rule_view;
731 CREATE VIEW ltl_permission_app_path_type_rule_view AS
732 SELECT (CASE WHEN is_reverse = 0 THEN app.name ELSE label.name END) AS subject,
733 (CASE WHEN is_reverse = 1 THEN app.name ELSE label.name END) AS object,
736 FROM permission_app_path_type_rule AS p
737 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
738 INNER JOIN app_path USING(app_path_type_id)
739 INNER JOIN label USING(label_id)
740 WHERE app.name != label.name;
743 -- LABEL TO PATH TYPE RULE VIEW -------------------------------------------
744 -- ltl = label to label
745 DROP VIEW IF EXISTS ltl_label_app_path_type_rule_view;
746 CREATE VIEW ltl_label_app_path_type_rule_view AS
747 SELECT (CASE WHEN is_reverse = 0 THEN label.name ELSE path_label.name END) AS subject,
748 (CASE WHEN is_reverse = 1 THEN label.name ELSE path_label.name END) AS object,
751 FROM label_app_path_type_rule AS l
752 INNER JOIN label USING(label_id)
753 INNER JOIN app_path USING(app_path_type_id)
754 INNER JOIN label AS path_label ON app_path.label_id = path_label.label_id
755 WHERE path_label.name != label.name;
758 -- PERMISSION TO APPLICATION'S OWN PATHS ---------------------------------------
759 -- ltl = label to label
760 DROP VIEW IF EXISTS ltl_app_path_view;
761 CREATE VIEW ltl_app_path_view AS
762 SELECT application_view.name AS subject,
763 label.name AS object,
764 app_path.access AS access
766 INNER JOIN application_view USING(app_id)
767 INNER JOIN label USING(label_id);
770 -- SMACK RULES VIEWS -----------------------------------------------------------
771 DROP VIEW IF EXISTS all_smack_binary_rules_view;
772 CREATE VIEW all_smack_binary_rules_view AS
775 bitwise_or(access) AS access,
776 MIN(is_volatile) AS is_volatile
777 FROM (SELECT subject, object, access, is_volatile
778 FROM ltl_permission_permission_rule_view
780 SELECT subject, object, access, is_volatile
781 FROM ltl_permission_label_rule_view
783 SELECT subject, object, access, is_volatile
784 FROM ltl_permission_app_path_type_rule_view
786 SELECT subject, object, access, is_volatile
787 FROM ltl_label_app_path_type_rule_view
789 SELECT subject, object, access, 0
790 FROM ltl_app_path_view
792 GROUP BY subject, object
793 ORDER BY subject, object ASC;
795 -- ALL INSERTED DATA VIEW ------------------------------------------------------
796 -- This view is used to clear the database from inserted rules.
797 -- We loose all information about installed applications
799 DROP VIEW IF EXISTS all_inserted_data;
800 CREATE VIEW all_inserted_data AS
804 DROP TRIGGER IF EXISTS all_inserted_data_delete_trigger;
805 CREATE TRIGGER all_inserted_data_delete_trigger INSTEAD OF
806 DELETE ON all_inserted_data
808 DELETE FROM permission_label_rule;
809 DELETE FROM permission_permission_rule;
810 DELETE FROM permission_app_path_type_rule;
812 DELETE FROM app_permission;
814 DELETE FROM permission;
815 DELETE FROM permission_type;
817 DELETE FROM app_path;
818 DELETE FROM app_path_type;
826 -- SMACK RULES MODIFICATIONS VIEW ----------------------------------------------
827 -- This definition is repeated during opening a connection with the database.
828 -- Used to get all smack rules, even volatile.
829 -- Ensure it's the same!
830 CREATE TEMPORARY VIEW modified_smack_rules AS
831 SELECT subject, object,
832 access_to_str(access_add) AS access_add,
833 access_to_str(access_del) AS access_del
835 SELECT subject, object,
836 s1.access & ~s2.access AS access_add,
837 s2.access & ~s1.access AS access_del
838 FROM all_smack_binary_rules AS s1
839 INNER JOIN history_smack_rule AS s2
840 USING (subject, object)
841 WHERE s1.access != s2.access
845 SELECT subject, object,
846 s1.access AS access_add,
848 FROM all_smack_binary_rules AS s1
849 LEFT JOIN history_smack_rule s2
850 USING (subject, object)
851 WHERE s2.subject IS NULL AND
856 SELECT subject, object,
858 s1.access AS access_del
859 FROM history_smack_rule s1
860 LEFT JOIN all_smack_binary_rules AS s2
861 USING (subject, object)
862 WHERE s2.subject IS NULL AND
865 ORDER BY subject, object ASC;
869 PRAGMA schema_version = 1;