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 .load librules-db-sql-udf.so
10 PRAGMA foreign_keys = ON;
11 PRAGMA auto_vacuum = NONE;
13 BEGIN EXCLUSIVE TRANSACTION;
15 -- Update here on every schema change! Integer value.
16 PRAGMA user_version = 3;
18 CREATE TABLE IF NOT EXISTS app (
19 app_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
20 label_id INTEGER NOT NULL,
23 FOREIGN KEY(label_id) REFERENCES label(label_id)
27 CREATE TABLE IF NOT EXISTS app_permission (
28 app_id INTEGER NOT NULL,
29 permission_id INTEGER NOT NULL,
30 is_volatile INTEGER NOT NULL DEFAULT 0,
31 is_enabled INTEGER NOT NULL DEFAULT 1,
33 PRIMARY KEY(app_id, permission_id),
35 FOREIGN KEY(app_id) REFERENCES app(app_id),
36 FOREIGN KEY(permission_id) REFERENCES permission(permission_id)
40 CREATE INDEX IF NOT EXISTS app_permission_permission_id_index ON app_permission(permission_id);
42 CREATE TABLE IF NOT EXISTS app_path (
43 app_id INTEGER NOT NULL,
45 label_id INTEGER NOT NULL,
46 access INTEGER NOT NULL,
47 access_reverse INTEGER NOT NULL,
48 app_path_type_id INTEGER NOT NULL ,
51 -- Desired behavior should be:
52 -- allow one app to register a path only once (already implemented by the primary key)
53 -- prohibit two apps registering the same path with different labels (probably cannot be done by SQL constraints)
54 -- allow two apps to register the same path if label is also same
56 PRIMARY KEY (app_id, path),
58 FOREIGN KEY(app_id) REFERENCES app(app_id),
59 FOREIGN KEY(label_id) REFERENCES label(label_id),
60 FOREIGN KEY(app_path_type_id) REFERENCES app_path_type(app_path_type_id)
64 CREATE INDEX IF NOT EXISTS app_path_app_path_type_id_index ON app_path(app_path_type_id);
66 CREATE TABLE IF NOT EXISTS app_path_type (
67 app_path_type_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
74 CREATE TABLE IF NOT EXISTS permission_permission_rule (
75 permission_id INTEGER NOT NULL,
76 target_permission_id INTEGER NOT NULL,
77 access INTEGER NOT NULL DEFAULT 0,
78 is_reverse INTEGER NOT NULL DEFAULT 0,
80 PRIMARY KEY (permission_id, target_permission_id, is_reverse),
82 FOREIGN KEY(permission_id) REFERENCES permission(permission_id),
83 FOREIGN KEY(target_permission_id) REFERENCES permission(permission_id)
86 CREATE TABLE IF NOT EXISTS permission_label_rule (
87 permission_id INTEGER NOT NULL,
88 label_id INTEGER NOT NULL,
89 access INTEGER NOT NULL DEFAULT 0,
90 is_reverse INTEGER NOT NULL DEFAULT 0,
92 PRIMARY KEY (permission_id,label_id, is_reverse),
94 FOREIGN KEY(permission_id) REFERENCES permission(permission_id),
95 FOREIGN KEY(label_id) REFERENCES label(label_id)
99 CREATE INDEX IF NOT EXISTS permission_label_rule_label_id_index ON permission_label_rule(label_id);
101 CREATE TABLE IF NOT EXISTS permission_app_path_type_rule (
102 permission_id INTEGER NOT NULL,
103 app_path_type_id INTEGER NOT NULL,
104 access INTEGER NOT NULL DEFAULT 0,
105 is_reverse INTEGER NOT NULL DEFAULT 0,
107 PRIMARY KEY (permission_id, app_path_type_id, is_reverse),
109 FOREIGN KEY(permission_id) REFERENCES permission(permission_id),
110 FOREIGN KEY(app_path_type_id) REFERENCES app_path_type(app_path_type_id)
114 CREATE INDEX IF NOT EXISTS permission_app_path_type_rule_app_path_type_id_index
115 ON permission_app_path_type_rule(app_path_type_id);
117 CREATE TABLE IF NOT EXISTS label_app_path_type_rule (
118 label_id INTEGER NOT NULL,
119 app_path_type_id INTEGER NOT NULL,
120 access INTEGER NOT NULL DEFAULT 0,
121 is_reverse INTEGER NOT NULL DEFAULT 0,
123 PRIMARY KEY (label_id, app_path_type_id, is_reverse),
125 FOREIGN KEY(label_id) REFERENCES label(label_id),
126 FOREIGN KEY(app_path_type_id) REFERENCES app_path_type(app_path_type_id)
129 CREATE TABLE IF NOT EXISTS label (
130 label_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
136 CREATE TABLE IF NOT EXISTS permission_type (
137 permission_type_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
138 type_name TEXT NOT NULL,
143 CREATE TABLE IF NOT EXISTS permission (
144 permission_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
145 permission_type_id INTEGER NOT NULL,
148 UNIQUE (name, permission_type_id),
150 FOREIGN KEY(permission_type_id) REFERENCES permission_type(permission_type_id)
153 -- Not aggregated rules
154 CREATE TABLE IF NOT EXISTS all_smack_binary_rules(
155 subject TEXT NOT NULL,
156 object TEXT NOT NULL,
157 access INTEGER NOT NULL,
158 is_volatile INTEGER NOT NULL
161 -- Index used for grouping and sorting by (subject, object)
162 -- and used for filtering by subject
163 CREATE INDEX IF NOT EXISTS all_smack_binary_rules_subject_object_index
164 ON all_smack_binary_rules(subject, object);
166 -- Index used for filtering by object
167 CREATE INDEX IF NOT EXISTS all_smack_binary_rules_object_index
168 ON all_smack_binary_rules(object);
170 -- TEMPORARY TABLES ------------------------------------------------------------
171 -- Definitions are repeated in code.
173 CREATE TEMPORARY TABLE modified_label(
174 name TEXT NOT NULL PRIMARY KEY
177 -- Not aggregated subset of modified rules
178 CREATE TEMPORARY TABLE all_smack_binary_rules_modified(
179 subject TEXT NOT NULL,
180 object TEXT NOT NULL,
181 access INTEGER NOT NULL,
182 is_volatile INTEGER NOT NULL
185 -- Aggregated subset of rules after changes
186 CREATE TEMPORARY TABLE current_smack_rule_modified(
187 subject TEXT NOT NULL,
188 object TEXT NOT NULL,
189 access INTEGER NOT NULL
192 -- Aggregated subset of rules before changes
193 CREATE TEMPORARY TABLE history_smack_rule_modified(
194 subject TEXT NOT NULL,
195 object TEXT NOT NULL,
196 access INTEGER NOT NULL
200 -- PERMISSION VIEW -------------------------------------------------------------
201 DROP VIEW IF EXISTS permission_view;
202 CREATE VIEW permission_view AS
203 SELECT permission.permission_id, permission.name, permission_type.type_name
205 INNER JOIN permission_type USING(permission_type_id);
207 DROP TRIGGER IF EXISTS permission_view_insert_trigger;
208 CREATE TRIGGER permission_view_insert_trigger
209 INSTEAD OF INSERT ON permission_view
211 -- Add the permission
212 INSERT OR IGNORE INTO permission(name,permission_type_id)
213 SELECT NEW.name, permission_type.permission_type_id
215 WHERE permission_type.type_name = NEW.type_name;
218 -- Delete the previous definition of the permission
219 DELETE FROM permission_label_rule_view
220 WHERE permission_label_rule_view.permission_name = NEW.name AND
221 permission_label_rule_view.permission_type_name = NEW.type_name;
224 -- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
225 DROP VIEW IF EXISTS permission_label_rule_view;
226 CREATE VIEW permission_label_rule_view AS
228 permission_view.permission_id AS permission_id,
229 permission_view.name AS permission_name,
230 permission_view.type_name AS permission_type_name,
231 label.name AS label_name,
232 permission_label_rule.access AS access,
233 permission_label_rule.is_reverse AS is_reverse
234 FROM permission_label_rule
235 LEFT JOIN permission_view USING(permission_id)
236 LEFT JOIN label USING(label_id);
239 -- Preferred way of adding permission rules would be to use these ONE, multi-row
240 -- insert statement, with one check of a condition
241 -- that there is such permission id. It's impossible to make those inserts in C,
242 -- so the programmer has to secure, that there is a permission with a given id.
243 -- (Check it and insert in the same transaction)
244 -- In runtime we accept ONLY inserts with label.
245 -- All other kinds of permissions are filled during the database creation.
246 DROP TRIGGER IF EXISTS permission_label_rule_view_insert_trigger;
247 CREATE TRIGGER permission_label_rule_view_insert_trigger
248 INSTEAD OF INSERT ON permission_label_rule_view
250 -- Adding api features adds a label it it's not present.
251 INSERT OR IGNORE INTO label(name) VALUES (NEW.label_name);
253 INSERT OR REPLACE INTO permission_label_rule(permission_id,
257 SELECT NEW.permission_id,
259 str_to_access(NEW.access),
262 WHERE label.name = NEW.label_name;
266 -- TODO: Potential problem - undeleted labels.
267 DROP TRIGGER IF EXISTS permission_label_rule_view_delete_trigger;
268 CREATE TRIGGER permission_label_rule_view_delete_trigger
269 INSTEAD OF DELETE ON permission_label_rule_view
271 DELETE FROM permission_label_rule
272 WHERE permission_label_rule.permission_id
273 IN (SELECT permission_view.permission_id
275 WHERE permission_view.name = OLD.permission_name AND
276 permission_view.type_name = OLD.permission_type_name);
280 -- PERMISSION TO APP PATH TYPE RULE VIEW ---------------------------------------
281 DROP VIEW IF EXISTS permission_app_path_type_rule_view;
282 CREATE VIEW permission_app_path_type_rule_view AS
284 permission_view.permission_id AS permission_id,
285 permission_view.name AS permission_name,
286 permission_view.type_name AS permission_type_name,
287 app_path_type.name AS app_path_type_name,
288 permission_app_path_type_rule.access AS access,
289 permission_app_path_type_rule.is_reverse AS is_reverse
290 FROM permission_app_path_type_rule
291 LEFT JOIN permission_view USING(permission_id)
292 LEFT JOIN app_path_type USING(app_path_type_id);
295 DROP TRIGGER IF EXISTS permission_app_path_type_rule_view_insert_trigger;
296 CREATE TRIGGER permission_app_path_type_rule_view_insert_trigger
298 ON permission_app_path_type_rule_view
299 WHEN NEW.permission_id IS NULL
301 INSERT INTO permission_app_path_type_rule(permission_id,
305 SELECT permission_view.permission_id,
306 app_path_type.app_path_type_id,
307 str_to_access(NEW.access),
309 FROM permission_view, app_path_type
310 WHERE permission_view.name = NEW.permission_name AND
311 permission_view.type_name = NEW.permission_type_name AND
312 app_path_type.name = NEW.app_path_type_name;
315 DROP TRIGGER IF EXISTS permission_app_path_type_rule_view_delete_trigger;
316 CREATE TRIGGER permission_app_path_type_rule_view_delete_trigger
318 ON permission_app_path_type_rule_view
321 DELETE FROM permission_app_path_type_rule
322 WHERE permission_app_path_type_rule.permission_id
323 IN (SELECT permission_view.permission_id
325 WHERE permission_view.name = OLD.permission_name AND
326 permission_view.type_name = OLD.permission_type_name);
330 CREATE TRIGGER permission_app_path_type_id_rule_view_insert_trigger
332 ON permission_app_path_type_rule_view
333 WHEN NEW.permission_id IS NOT NULL
335 INSERT OR REPLACE INTO permission_app_path_type_rule(permission_id,
339 SELECT NEW.permission_id,
340 app_path_type.app_path_type_id,
341 str_to_access(NEW.access),
344 WHERE app_path_type.name = NEW.app_path_type_name;
348 -- LABEL TO APP PATH TYPE RULE VIEW --------------------------------------------
349 DROP VIEW IF EXISTS label_app_path_type_rule_view;
350 CREATE VIEW label_app_path_type_rule_view AS
352 label_app_path_type_rule.label_id AS label_id,
353 label.name AS label_name,
354 app_path_type.name AS app_path_type_name,
355 label_app_path_type_rule.access AS access,
356 label_app_path_type_rule.is_reverse AS is_reverse
357 FROM label_app_path_type_rule
358 LEFT JOIN label USING(label_id)
359 LEFT JOIN app_path_type USING(app_path_type_id);
362 DROP TRIGGER IF EXISTS label_app_path_type_rule_view_insert_trigger;
363 CREATE TRIGGER label_app_path_type_rule_view_insert_trigger
365 ON label_app_path_type_rule_view
367 INSERT OR IGNORE INTO label(name) VALUES (NEW.label_name);
369 INSERT INTO label_app_path_type_rule(label_id,
373 SELECT label.label_id,
374 app_path_type.app_path_type_id,
375 str_to_access(NEW.access),
377 FROM label, app_path_type
378 WHERE label.name = NEW.label_name AND
379 app_path_type.name = NEW.app_path_type_name;
383 DROP TRIGGER IF EXISTS label_app_path_type_rule_view_delete_trigger;
384 CREATE TRIGGER label_app_path_type_rule_view_delete_trigger
386 ON label_app_path_type_rule_view
388 -- Delete the rules with this label
389 DELETE FROM label_app_path_type_rule
390 WHERE label_app_path_type_rule.label_id
391 IN (SELECT label.label_id
393 WHERE label.name = OLD.label_name);
395 -- Delete the label if it's not referenced
396 DELETE FROM label_view
397 WHERE label_view.name = OLD.label_name;
400 -- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
401 DROP VIEW IF EXISTS permission_permission_rule_view;
402 CREATE VIEW permission_permission_rule_view AS
404 tmp_permission_view.permission_id AS permission_id,
405 tmp_permission_view.name AS permission_name,
406 tmp_permission_view.type_name AS permission_type_name,
407 tmp_target_permission_view.name AS target_permission_name,
408 tmp_target_permission_view.type_name AS target_permission_type_name,
409 permission_permission_rule.access AS access,
410 permission_permission_rule.is_reverse AS is_reverse
411 FROM permission_permission_rule
412 LEFT JOIN permission_view AS tmp_permission_view USING(permission_id)
413 LEFT JOIN permission_view AS tmp_target_permission_view
414 ON permission_permission_rule.target_permission_id = tmp_target_permission_view.permission_id;
417 -- Trigger for manual addition of rules.
418 DROP TRIGGER IF EXISTS permission_permission_rule_view_insert_trigger;
419 CREATE TRIGGER permission_permission_rule_view_insert_trigger
420 INSTEAD OF INSERT ON permission_permission_rule_view
423 INSERT OR REPLACE INTO permission_permission_rule(permission_id,
424 target_permission_id,
427 SELECT tmp_permission_view.permission_id,
428 tmp_target_permission_view.permission_id,
429 str_to_access(NEW.access),
431 FROM permission_view AS tmp_permission_view,
432 permission_view AS tmp_target_permission_view
433 WHERE tmp_permission_view.name = NEW.permission_name AND
434 tmp_permission_view.type_name = NEW.permission_type_name AND
435 tmp_target_permission_view.name = NEW.target_permission_name AND
436 tmp_target_permission_view.type_name = NEW.target_permission_type_name;
440 DROP TRIGGER IF EXISTS permission_permission_rule_view_delete_trigger;
441 CREATE TRIGGER permission_permission_rule_view_delete_trigger
442 INSTEAD OF DELETE ON permission_permission_rule_view
445 DELETE FROM permission_permission_rule_view
446 WHERE permission_permission_rule_view.permission_id
447 IN (SELECT permission_view.permission_id
449 WHERE permission_view.name = OLD.permission_name AND
450 permission_view.type_name = OLD.permission_type_name);
455 -- LABEL VIEW ------------------------------------------------------------------
456 -- There are no INSTEAD OF triggers on regular tables.
457 -- We use a view to delete unreferenced labels:
458 DROP VIEW IF EXISTS label_view;
459 CREATE VIEW label_view AS SELECT * FROM label;
461 DROP TRIGGER IF EXISTS label_view_delete_trigger;
462 CREATE TRIGGER label_view_delete_trigger
463 INSTEAD OF DELETE ON label_view
464 WHEN OLD.label_id NOT IN (SELECT app.label_id
466 OLD.label_id NOT IN (SELECT permission_label_rule.label_id
467 FROM permission_label_rule) AND
468 OLD.label_id NOT IN (SELECT app_path.label_id
470 OLD.label_id NOT IN (SELECT label_app_path_type_rule.label_id
471 FROM label_app_path_type_rule)
473 DELETE FROM label WHERE label.name = OLD.name;
477 -- APPLICATION VIEW ------------------------------------------------------------
478 DROP VIEW IF EXISTS application_view;
479 CREATE VIEW application_view AS
480 SELECT app.app_id, label.name
482 INNER JOIN app USING(label_id);
484 DROP TRIGGER IF EXISTS application_view_insert_trigger;
485 CREATE TRIGGER application_view_insert_trigger
486 INSTEAD OF INSERT ON application_view
488 -- The app's label could have been added by the permission.
489 INSERT OR IGNORE INTO label(name) VALUES (NEW.name);
492 INSERT INTO app(label_id)
495 WHERE label.name = NEW.name;
497 -- Add the permission granted to all applications
498 INSERT INTO app_permission_view(app_id, name, type_name, is_volatile, is_enabled)
499 VALUES (last_insert_rowid(), "ALL_APPS", "ALL_APPS", 0, 1);
504 DROP TRIGGER IF EXISTS application_view_delete_trigger;
505 CREATE TRIGGER application_view_delete_trigger
506 INSTEAD OF DELETE ON application_view
508 -- Delete rules that correspond to app's paths:
509 DELETE FROM permission_label_rule
510 WHERE permission_label_rule.label_id IN
511 (SELECT app_path.label_id
513 INNER JOIN application_view USING(app_id)
514 WHERE application_view.name = OLD.name);
517 DELETE FROM path_view
518 WHERE path_view.owner_app_label_name=OLD.name;
520 -- Delete apps permissions:
521 DELETE FROM app_permission
522 WHERE app_permission.app_id
523 IN (SELECT application_view.app_id
524 FROM application_view
525 WHERE application_view.name = OLD.name
528 -- Delete application
530 WHERE app.app_id IN (SELECT application_view.app_id
531 FROM application_view
532 WHERE application_view.name = OLD.name
536 DELETE FROM label_view
537 WHERE label_view.name = OLD.name;
541 -- PATH VIEW -------------------------------------------------------------------
542 DROP VIEW IF EXISTS path_view;
543 CREATE VIEW path_view AS
544 SELECT application_view.name AS owner_app_label_name,
545 app_path.path AS path,
546 label.name AS path_label_name,
547 app_path.access AS access,
548 app_path.access_reverse AS access_reverse,
549 app_path_type.name AS path_type_name
552 LEFT JOIN app_path_type USING (app_path_type_id)
553 LEFT JOIN application_view USING (app_id)
554 LEFT JOIN label USING (label_id);
557 -- For an existing application we add a path.
558 DROP TRIGGER IF EXISTS path_view_insert_trigger;
559 CREATE TRIGGER path_view_insert_trigger
560 INSTEAD OF INSERT ON path_view
561 WHEN NEW.owner_app_label_name IN (SELECT application_view.name
562 FROM application_view)
564 -- The path's label could have been added by the permission.
565 INSERT OR IGNORE INTO label(name) VALUES (NEW.path_label_name);
568 INSERT OR IGNORE INTO app_path(app_id, path, label_id, access, access_reverse, app_path_type_id)
569 SELECT application_view.app_id,
572 str_to_access(NEW.access),
573 str_to_access(NEW.access_reverse),
574 app_path_type.app_path_type_id
575 FROM application_view, app_path_type, label
576 WHERE application_view.name = NEW.owner_app_label_name AND
577 app_path_type.name = NEW.path_type_name AND
578 label.name = NEW.path_label_name;
581 DROP TRIGGER IF EXISTS path_view_delete_trigger;
582 CREATE TRIGGER path_view_delete_trigger
583 INSTEAD OF DELETE ON path_view
587 WHERE app_path.app_id IN (SELECT app.app_id
589 WHERE label.name = OLD.owner_app_label_name AND
590 app.label_id = label.label_id);
592 -- Delete the path's label if it's not used any more
593 DELETE FROM label_view WHERE label_view.name = OLD.path_label_name;
596 -- APP PERMISSION LIST VIEW ----------------------------------------------------
597 -- Used in check_app_permission_internal to check if permissions are present
598 -- TODO: Check if SQLite optimizer doesn't change app_permission_view to the same code.
599 DROP VIEW IF EXISTS app_permission_list_view;
600 CREATE VIEW app_permission_list_view AS
601 SELECT app_permission.app_id AS app_id,
602 app_permission.permission_id AS permission_id,
603 permission_view.name AS permission_name,
604 permission_view.type_name AS permission_type_name,
605 app_permission.is_volatile AS is_volatile,
606 app_permission.is_enabled AS is_enabled
608 INNER JOIN permission_view USING(permission_id);
613 -- APP PERMISSION VIEW ---------------------------------------------------------
614 DROP VIEW IF EXISTS app_permission_view;
615 CREATE VIEW app_permission_view AS
616 SELECT application_view.app_id,
617 application_view.name AS app_name,
618 permission_view.permission_id,
619 permission_view.name,
620 permission_view.type_name,
621 app_permission.is_volatile,
622 app_permission.is_enabled
624 INNER JOIN application_view USING(app_id)
625 INNER JOIN permission_view USING(permission_id);
628 DROP TRIGGER IF EXISTS app_permission_view_insert_trigger;
629 CREATE TRIGGER app_permission_view_insert_trigger
630 INSTEAD OF INSERT ON app_permission_view
632 INSERT INTO app_permission(app_id, permission_id, is_volatile, is_enabled)
634 permission_view.permission_id,
638 WHERE permission_view.name = NEW.name AND
639 permission_view.type_name = NEW.type_name;
645 -- It's forbidden do change permission from not volatile to volatile.
646 -- We have to check it before inserting anything.
647 -- Used in updating permissions
648 DROP TRIGGER IF EXISTS app_permission_view_update_trigger;
649 CREATE TRIGGER app_permission_view_update_trigger
650 INSTEAD OF UPDATE ON app_permission_view
652 UPDATE OR IGNORE app_permission
653 SET is_enabled = NEW.is_enabled
654 WHERE app_permission.app_id = OLD.app_id AND
655 app_permission.permission_id
656 IN (SELECT permission_view.permission_id
658 WHERE permission_view.name = OLD.name AND
659 permission_view.type_name = OLD.type_name
664 DROP TRIGGER IF EXISTS app_permission_view_delete_trigger;
665 CREATE TRIGGER app_permission_view_delete_trigger
666 INSTEAD OF DELETE ON app_permission_view
668 DELETE FROM app_permission
669 WHERE app_permission.app_id
670 IN (SELECT application_view.app_id
671 FROM application_view
672 WHERE application_view.name = OLD.app_name
675 app_permission.permission_id NOT IN (SELECT permission_view.permission_id
677 WHERE permission_view.name = "ALL_APPS" AND
678 permission_view.type_name = "ALL_APPS");
680 DELETE FROM path_view
681 WHERE path_view.owner_app_label_name=OLD.app_name;
685 -- APP PERMISSION VOLATILE VIEW ------------------------------------------------
686 DROP VIEW IF EXISTS app_permission_volatile_view;
687 CREATE VIEW app_permission_volatile_view AS
689 FROM app_permission_view
690 WHERE app_permission_view.is_volatile = 1;
693 DROP TRIGGER IF EXISTS app_permission_volatile_view_delete_trigger;
694 CREATE TRIGGER app_permission_volatile_view_delete_trigger
695 INSTEAD OF DELETE ON app_permission_volatile_view
697 DELETE FROM app_permission
698 WHERE app_permission.is_volatile = 1 AND
699 app_permission.app_id
700 IN (SELECT application_view.app_id
701 FROM application_view
702 WHERE application_view.name = OLD.app_name
707 -- APPLICATIONS PERMISSIONS ID -------------------------------------------------
708 -- All applications and their permissions
709 DROP VIEW IF EXISTS app_label_with_permission_view;
710 CREATE VIEW app_label_with_permission_view AS
711 SELECT app_permission.permission_id,
712 application_view.name,
713 application_view.app_id,
714 app_permission.is_volatile
716 INNER JOIN application_view USING(app_id)
717 WHERE app_permission.is_enabled = 1;
721 -- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
722 -- ltl = label to label
723 DROP VIEW IF EXISTS ltl_permission_permission_rule_view;
724 CREATE VIEW ltl_permission_permission_rule_view AS
725 SELECT app1.name AS subject,
728 app1.is_volatile OR app2.is_volatile AS is_volatile
729 FROM permission_permission_rule AS p
730 INNER JOIN app_label_with_permission_view AS app1 USING(permission_id)
731 INNER JOIN app_label_with_permission_view AS app2
732 ON app2.permission_id = p.target_permission_id
733 WHERE is_reverse = 0 AND app1.app_id != app2.app_id
735 SELECT app2.name AS subject,
738 app1.is_volatile OR app2.is_volatile AS is_volatile
739 FROM permission_permission_rule AS p
740 INNER JOIN app_label_with_permission_view AS app1 USING(permission_id)
741 INNER JOIN app_label_with_permission_view AS app2
742 ON app2.permission_id = p.target_permission_id
743 WHERE is_reverse = 1 AND app1.app_id != app2.app_id;
745 -- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
746 -- ltl = label to label
747 DROP VIEW IF EXISTS ltl_permission_label_rule_view;
748 CREATE VIEW ltl_permission_label_rule_view AS
749 SELECT app.name AS subject,
750 label.name AS object,
753 FROM permission_label_rule AS p
754 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
755 INNER JOIN label USING(label_id)
756 WHERE is_reverse = 0 AND app.name != label.name
758 SELECT label.name AS subject,
762 FROM permission_label_rule AS p
763 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
764 INNER JOIN label USING(label_id)
765 WHERE is_reverse = 1 AND app.name != label.name;
770 -- PERMISSION TO PATH TYPE RULE VIEW -------------------------------------------
771 -- ltl = label to label
772 DROP VIEW IF EXISTS ltl_permission_app_path_type_rule_view;
773 CREATE VIEW ltl_permission_app_path_type_rule_view AS
774 SELECT app.name AS subject,
775 label.name AS object,
778 FROM permission_app_path_type_rule AS p
779 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
780 INNER JOIN app_path USING(app_path_type_id)
781 INNER JOIN label USING(label_id)
782 WHERE is_reverse = 0 AND app.name != label.name
784 SELECT label.name AS subject,
788 FROM permission_app_path_type_rule AS p
789 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
790 INNER JOIN app_path USING(app_path_type_id)
791 INNER JOIN label USING(label_id)
792 WHERE is_reverse = 1 AND app.name != label.name;
795 -- LABEL TO PATH TYPE RULE VIEW -------------------------------------------
796 -- ltl = label to label
797 DROP VIEW IF EXISTS ltl_label_app_path_type_rule_view;
798 CREATE VIEW ltl_label_app_path_type_rule_view AS
799 SELECT label.name AS subject,
800 path_label.name AS object,
803 FROM label_app_path_type_rule AS l
804 INNER JOIN label USING(label_id)
805 INNER JOIN app_path USING(app_path_type_id)
806 INNER JOIN label AS path_label ON app_path.label_id = path_label.label_id
807 WHERE is_reverse = 0 AND path_label.name != label.name
809 SELECT path_label.name AS subject,
810 label.name AS object,
813 FROM label_app_path_type_rule AS l
814 INNER JOIN label USING(label_id)
815 INNER JOIN app_path USING(app_path_type_id)
816 INNER JOIN label AS path_label ON app_path.label_id = path_label.label_id
817 WHERE is_reverse = 1 AND path_label.name != label.name;
820 -- PERMISSION TO APPLICATION'S OWN PATHS ---------------------------------------
821 -- ltl = label to label
822 DROP VIEW IF EXISTS ltl_app_path_view;
823 CREATE VIEW ltl_app_path_view AS
824 SELECT application_view.name AS subject,
825 label.name AS object,
826 app_path.access AS access
828 INNER JOIN application_view USING(app_id)
829 INNER JOIN label USING(label_id);
832 -- PERMISSION FROM PATHS TO APPLICATIONS ---------------------------------------
833 -- ltl = label to label
834 DROP VIEW IF EXISTS ltl_app_path_reverse_view;
835 CREATE VIEW ltl_app_path_reverse_view AS
836 SELECT label.name AS subject,
837 application_view.name AS object,
838 app_path.access_reverse AS access
840 INNER JOIN application_view USING(app_id)
841 INNER JOIN label USING(label_id)
842 WHERE app_path.access_reverse != 0 ;
845 -- SMACK RULES VIEWS -----------------------------------------------------------
846 DROP VIEW IF EXISTS all_smack_binary_rules_view;
847 CREATE VIEW all_smack_binary_rules_view AS
852 FROM (SELECT subject, object, access, is_volatile
853 FROM ltl_permission_permission_rule_view
855 SELECT subject, object, access, is_volatile
856 FROM ltl_permission_label_rule_view
858 SELECT subject, object, access, is_volatile
859 FROM ltl_permission_app_path_type_rule_view
861 SELECT subject, object, access, is_volatile
862 FROM ltl_label_app_path_type_rule_view
864 SELECT subject, object, access, 0
865 FROM ltl_app_path_view
867 SELECT subject, object, access, 0
868 FROM ltl_app_path_reverse_view
871 -- ALL INSERTED DATA VIEW ------------------------------------------------------
872 -- This view is used to clear the database from inserted rules.
873 -- We loose all information about installed applications
875 DROP VIEW IF EXISTS all_inserted_data;
876 CREATE VIEW all_inserted_data AS
880 DROP TRIGGER IF EXISTS all_inserted_data_delete_trigger;
881 CREATE TRIGGER all_inserted_data_delete_trigger INSTEAD OF
882 DELETE ON all_inserted_data
884 DELETE FROM permission_label_rule;
885 DELETE FROM permission_permission_rule;
886 DELETE FROM permission_app_path_type_rule;
888 DELETE FROM app_permission;
890 DELETE FROM permission;
891 DELETE FROM permission_type;
893 DELETE FROM app_path;
894 DELETE FROM app_path_type;
902 -- SMACK RULES MODIFICATIONS VIEW ----------------------------------------------
903 -- This definition is repeated during opening a connection with the database.
904 -- Used to get all smack rules, even volatile.
905 -- Ensure it's the same!
906 CREATE TEMPORARY VIEW modified_smack_rules AS
907 SELECT subject, object,
908 access_to_str(access_add) AS access_add,
909 access_to_str(access_del) AS access_del
911 SELECT subject, object,
912 s1.access & ~s2.access AS access_add,
913 s2.access & ~s1.access AS access_del
914 FROM current_smack_rule_modified AS s1
915 INNER JOIN history_smack_rule_modified AS s2
916 USING (subject, object)
917 WHERE s1.access != s2.access
921 SELECT subject, object,
922 s1.access AS access_add,
924 FROM current_smack_rule_modified AS s1
925 LEFT JOIN history_smack_rule_modified s2
926 USING (subject, object)
927 WHERE s2.subject IS NULL AND
932 SELECT subject, object,
934 s1.access AS access_del
935 FROM history_smack_rule_modified s1
936 LEFT JOIN current_smack_rule_modified AS s2
937 USING (subject, object)
938 WHERE s2.subject IS NULL AND
941 ORDER BY subject, object ASC;