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_name = NEW.name AND
221 permission_type_name = NEW.type_name;
223 DELETE FROM permission_permission_rule_view
224 WHERE permission_name = NEW.name AND
225 permission_type_name = NEW.type_name;
227 DELETE FROM permission_app_path_type_rule_view
228 WHERE permission_name = NEW.name AND
229 permission_type_name = NEW.type_name;
233 -- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
234 DROP VIEW IF EXISTS permission_label_rule_view;
235 CREATE VIEW permission_label_rule_view AS
237 permission_view.permission_id AS permission_id,
238 permission_view.name AS permission_name,
239 permission_view.type_name AS permission_type_name,
240 label.name AS label_name,
241 permission_label_rule.access AS access,
242 permission_label_rule.is_reverse AS is_reverse
243 FROM permission_label_rule
244 LEFT JOIN permission_view USING(permission_id)
245 LEFT JOIN label USING(label_id);
248 -- Preferred way of adding permission rules would be to use these ONE, multi-row
249 -- insert statement, with one check of a condition
250 -- that there is such permission id. It's impossible to make those inserts in C,
251 -- so the programmer has to secure, that there is a permission with a given id.
252 -- (Check it and insert in the same transaction)
253 -- In runtime we accept ONLY inserts with label.
254 -- All other kinds of permissions are filled during the database creation.
255 DROP TRIGGER IF EXISTS permission_label_rule_view_insert_trigger;
256 CREATE TRIGGER permission_label_rule_view_insert_trigger
257 INSTEAD OF INSERT ON permission_label_rule_view
259 -- Adding api features adds a label it it's not present.
260 INSERT OR IGNORE INTO label(name) VALUES (NEW.label_name);
262 INSERT OR REPLACE INTO permission_label_rule(permission_id,
266 SELECT NEW.permission_id,
268 str_to_access(NEW.access),
271 WHERE label.name = NEW.label_name;
275 -- TODO: Potential problem - undeleted labels.
276 DROP TRIGGER IF EXISTS permission_label_rule_view_delete_trigger;
277 CREATE TRIGGER permission_label_rule_view_delete_trigger
278 INSTEAD OF DELETE ON permission_label_rule_view
280 DELETE FROM permission_label_rule
281 WHERE permission_label_rule.permission_id
282 IN (SELECT permission_view.permission_id
284 WHERE permission_view.name = OLD.permission_name AND
285 permission_view.type_name = OLD.permission_type_name);
289 -- PERMISSION TO APP PATH TYPE RULE VIEW ---------------------------------------
290 DROP VIEW IF EXISTS permission_app_path_type_rule_view;
291 CREATE VIEW permission_app_path_type_rule_view AS
293 permission_view.permission_id AS permission_id,
294 permission_view.name AS permission_name,
295 permission_view.type_name AS permission_type_name,
296 app_path_type.name AS app_path_type_name,
297 permission_app_path_type_rule.access AS access,
298 permission_app_path_type_rule.is_reverse AS is_reverse
299 FROM permission_app_path_type_rule
300 LEFT JOIN permission_view USING(permission_id)
301 LEFT JOIN app_path_type USING(app_path_type_id);
304 DROP TRIGGER IF EXISTS permission_app_path_type_rule_view_insert_trigger;
305 CREATE TRIGGER permission_app_path_type_rule_view_insert_trigger
307 ON permission_app_path_type_rule_view
308 WHEN NEW.permission_id IS NULL
310 INSERT INTO permission_app_path_type_rule(permission_id,
314 SELECT permission_view.permission_id,
315 app_path_type.app_path_type_id,
316 str_to_access(NEW.access),
318 FROM permission_view, app_path_type
319 WHERE permission_view.name = NEW.permission_name AND
320 permission_view.type_name = NEW.permission_type_name AND
321 app_path_type.name = NEW.app_path_type_name;
324 DROP TRIGGER IF EXISTS permission_app_path_type_rule_view_delete_trigger;
325 CREATE TRIGGER permission_app_path_type_rule_view_delete_trigger
327 ON permission_app_path_type_rule_view
330 DELETE FROM permission_app_path_type_rule
331 WHERE permission_app_path_type_rule.permission_id
332 IN (SELECT permission_view.permission_id
334 WHERE permission_view.name = OLD.permission_name AND
335 permission_view.type_name = OLD.permission_type_name);
339 CREATE TRIGGER permission_app_path_type_id_rule_view_insert_trigger
341 ON permission_app_path_type_rule_view
342 WHEN NEW.permission_id IS NOT NULL
344 INSERT OR REPLACE INTO permission_app_path_type_rule(permission_id,
348 SELECT NEW.permission_id,
349 app_path_type.app_path_type_id,
350 str_to_access(NEW.access),
353 WHERE app_path_type.name = NEW.app_path_type_name;
357 -- LABEL TO APP PATH TYPE RULE VIEW --------------------------------------------
358 DROP VIEW IF EXISTS label_app_path_type_rule_view;
359 CREATE VIEW label_app_path_type_rule_view AS
361 label_app_path_type_rule.label_id AS label_id,
362 label.name AS label_name,
363 app_path_type.name AS app_path_type_name,
364 label_app_path_type_rule.access AS access,
365 label_app_path_type_rule.is_reverse AS is_reverse
366 FROM label_app_path_type_rule
367 LEFT JOIN label USING(label_id)
368 LEFT JOIN app_path_type USING(app_path_type_id);
371 DROP TRIGGER IF EXISTS label_app_path_type_rule_view_insert_trigger;
372 CREATE TRIGGER label_app_path_type_rule_view_insert_trigger
374 ON label_app_path_type_rule_view
376 INSERT OR IGNORE INTO label(name) VALUES (NEW.label_name);
378 INSERT INTO label_app_path_type_rule(label_id,
382 SELECT label.label_id,
383 app_path_type.app_path_type_id,
384 str_to_access(NEW.access),
386 FROM label, app_path_type
387 WHERE label.name = NEW.label_name AND
388 app_path_type.name = NEW.app_path_type_name;
392 DROP TRIGGER IF EXISTS label_app_path_type_rule_view_delete_trigger;
393 CREATE TRIGGER label_app_path_type_rule_view_delete_trigger
395 ON label_app_path_type_rule_view
397 -- Delete the rules with this label
398 DELETE FROM label_app_path_type_rule
399 WHERE label_app_path_type_rule.label_id
400 IN (SELECT label.label_id
402 WHERE label.name = OLD.label_name);
404 -- Delete the label if it's not referenced
405 DELETE FROM label_view
406 WHERE label_view.name = OLD.label_name;
409 -- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
410 DROP VIEW IF EXISTS permission_permission_rule_view;
411 CREATE VIEW permission_permission_rule_view AS
413 tmp_permission_view.permission_id AS permission_id,
414 tmp_permission_view.name AS permission_name,
415 tmp_permission_view.type_name AS permission_type_name,
416 tmp_target_permission_view.name AS target_permission_name,
417 tmp_target_permission_view.type_name AS target_permission_type_name,
418 permission_permission_rule.access AS access,
419 permission_permission_rule.is_reverse AS is_reverse
420 FROM permission_permission_rule
421 LEFT JOIN permission_view AS tmp_permission_view USING(permission_id)
422 LEFT JOIN permission_view AS tmp_target_permission_view
423 ON permission_permission_rule.target_permission_id = tmp_target_permission_view.permission_id;
426 -- Trigger for manual addition of rules.
427 DROP TRIGGER IF EXISTS permission_permission_rule_view_insert_trigger;
428 CREATE TRIGGER permission_permission_rule_view_insert_trigger
429 INSTEAD OF INSERT ON permission_permission_rule_view
432 INSERT OR REPLACE INTO permission_permission_rule(permission_id,
433 target_permission_id,
436 SELECT tmp_permission_view.permission_id,
437 tmp_target_permission_view.permission_id,
438 str_to_access(NEW.access),
440 FROM permission_view AS tmp_permission_view,
441 permission_view AS tmp_target_permission_view
442 WHERE tmp_permission_view.name = NEW.permission_name AND
443 tmp_permission_view.type_name = NEW.permission_type_name AND
444 tmp_target_permission_view.name = NEW.target_permission_name AND
445 tmp_target_permission_view.type_name = NEW.target_permission_type_name;
449 DROP TRIGGER IF EXISTS permission_permission_rule_view_delete_trigger;
450 CREATE TRIGGER permission_permission_rule_view_delete_trigger
451 INSTEAD OF DELETE ON permission_permission_rule_view
454 DELETE FROM permission_permission_rule
455 WHERE permission_permission_rule.permission_id
456 IN (SELECT permission_view.permission_id
458 WHERE permission_view.name = OLD.permission_name AND
459 permission_view.type_name = OLD.permission_type_name);
464 -- LABEL VIEW ------------------------------------------------------------------
465 -- There are no INSTEAD OF triggers on regular tables.
466 -- We use a view to delete unreferenced labels:
467 DROP VIEW IF EXISTS label_view;
468 CREATE VIEW label_view AS SELECT * FROM label;
470 DROP TRIGGER IF EXISTS label_view_delete_trigger;
471 CREATE TRIGGER label_view_delete_trigger
472 INSTEAD OF DELETE ON label_view
473 WHEN OLD.label_id NOT IN (SELECT app.label_id
475 OLD.label_id NOT IN (SELECT permission_label_rule.label_id
476 FROM permission_label_rule) AND
477 OLD.label_id NOT IN (SELECT app_path.label_id
479 OLD.label_id NOT IN (SELECT label_app_path_type_rule.label_id
480 FROM label_app_path_type_rule)
482 DELETE FROM label WHERE label.name = OLD.name;
486 -- APPLICATION VIEW ------------------------------------------------------------
487 DROP VIEW IF EXISTS application_view;
488 CREATE VIEW application_view AS
489 SELECT app.app_id, label.name
491 INNER JOIN app USING(label_id);
493 DROP TRIGGER IF EXISTS application_view_insert_trigger;
494 CREATE TRIGGER application_view_insert_trigger
495 INSTEAD OF INSERT ON application_view
497 -- The app's label could have been added by the permission.
498 INSERT OR IGNORE INTO label(name) VALUES (NEW.name);
501 INSERT INTO app(label_id)
504 WHERE label.name = NEW.name;
506 -- Add the permission granted to all applications
507 INSERT INTO app_permission_view(app_id, name, type_name, is_volatile, is_enabled)
508 VALUES (last_insert_rowid(), "ALL_APPS", "ALL_APPS", 0, 1);
513 DROP TRIGGER IF EXISTS application_view_delete_trigger;
514 CREATE TRIGGER application_view_delete_trigger
515 INSTEAD OF DELETE ON application_view
517 -- Delete rules that correspond to app's paths:
518 DELETE FROM permission_label_rule
519 WHERE permission_label_rule.label_id IN
520 (SELECT app_path.label_id
522 INNER JOIN application_view USING(app_id)
523 WHERE application_view.name = OLD.name);
526 DELETE FROM path_view
527 WHERE path_view.owner_app_label_name=OLD.name;
529 -- Delete apps permissions:
530 DELETE FROM app_permission
531 WHERE app_permission.app_id
532 IN (SELECT application_view.app_id
533 FROM application_view
534 WHERE application_view.name = OLD.name
537 -- Delete application
539 WHERE app.app_id IN (SELECT application_view.app_id
540 FROM application_view
541 WHERE application_view.name = OLD.name
545 DELETE FROM label_view
546 WHERE label_view.name = OLD.name;
550 -- PATH VIEW -------------------------------------------------------------------
551 DROP VIEW IF EXISTS path_view;
552 CREATE VIEW path_view AS
553 SELECT application_view.name AS owner_app_label_name,
554 app_path.path AS path,
555 label.name AS path_label_name,
556 app_path.access AS access,
557 app_path.access_reverse AS access_reverse,
558 app_path_type.name AS path_type_name
561 LEFT JOIN app_path_type USING (app_path_type_id)
562 LEFT JOIN application_view USING (app_id)
563 LEFT JOIN label USING (label_id);
566 -- For an existing application we add a path.
567 DROP TRIGGER IF EXISTS path_view_insert_trigger;
568 CREATE TRIGGER path_view_insert_trigger
569 INSTEAD OF INSERT ON path_view
570 WHEN NEW.owner_app_label_name IN (SELECT application_view.name
571 FROM application_view)
573 -- The path's label could have been added by the permission.
574 INSERT OR IGNORE INTO label(name) VALUES (NEW.path_label_name);
577 INSERT OR IGNORE INTO app_path(app_id, path, label_id, access, access_reverse, app_path_type_id)
578 SELECT application_view.app_id,
581 str_to_access(NEW.access),
582 str_to_access(NEW.access_reverse),
583 app_path_type.app_path_type_id
584 FROM application_view, app_path_type, label
585 WHERE application_view.name = NEW.owner_app_label_name AND
586 app_path_type.name = NEW.path_type_name AND
587 label.name = NEW.path_label_name;
590 DROP TRIGGER IF EXISTS path_view_delete_trigger;
591 CREATE TRIGGER path_view_delete_trigger
592 INSTEAD OF DELETE ON path_view
596 WHERE app_path.app_id IN (SELECT app.app_id
598 WHERE label.name = OLD.owner_app_label_name AND
599 app.label_id = label.label_id);
601 -- Delete the path's label if it's not used any more
602 DELETE FROM label_view WHERE label_view.name = OLD.path_label_name;
605 -- APP PERMISSION LIST VIEW ----------------------------------------------------
606 -- Used in check_app_permission_internal to check if permissions are present
607 -- TODO: Check if SQLite optimizer doesn't change app_permission_view to the same code.
608 DROP VIEW IF EXISTS app_permission_list_view;
609 CREATE VIEW app_permission_list_view AS
610 SELECT app_permission.app_id AS app_id,
611 app_permission.permission_id AS permission_id,
612 permission_view.name AS permission_name,
613 permission_view.type_name AS permission_type_name,
614 app_permission.is_volatile AS is_volatile,
615 app_permission.is_enabled AS is_enabled
617 INNER JOIN permission_view USING(permission_id);
622 -- APP PERMISSION VIEW ---------------------------------------------------------
623 DROP VIEW IF EXISTS app_permission_view;
624 CREATE VIEW app_permission_view AS
625 SELECT application_view.app_id,
626 application_view.name AS app_name,
627 permission_view.permission_id,
628 permission_view.name,
629 permission_view.type_name,
630 app_permission.is_volatile,
631 app_permission.is_enabled
633 INNER JOIN application_view USING(app_id)
634 INNER JOIN permission_view USING(permission_id);
637 DROP TRIGGER IF EXISTS app_permission_view_insert_trigger;
638 CREATE TRIGGER app_permission_view_insert_trigger
639 INSTEAD OF INSERT ON app_permission_view
641 INSERT OR IGNORE INTO app_permission(app_id, permission_id, is_volatile, is_enabled)
643 permission_view.permission_id,
647 WHERE permission_view.name = NEW.name AND
648 permission_view.type_name = NEW.type_name;
654 -- It's forbidden do change permission from not volatile to volatile.
655 -- We have to check it before inserting anything.
656 -- Used in updating permissions
657 DROP TRIGGER IF EXISTS app_permission_view_update_trigger;
658 CREATE TRIGGER app_permission_view_update_trigger
659 INSTEAD OF UPDATE ON app_permission_view
661 UPDATE OR IGNORE app_permission
662 SET is_enabled = NEW.is_enabled
663 WHERE app_permission.app_id = OLD.app_id AND
664 app_permission.permission_id
665 IN (SELECT permission_view.permission_id
667 WHERE permission_view.name = OLD.name AND
668 permission_view.type_name = OLD.type_name
673 DROP TRIGGER IF EXISTS app_permission_view_delete_trigger;
674 CREATE TRIGGER app_permission_view_delete_trigger
675 INSTEAD OF DELETE ON app_permission_view
677 DELETE FROM app_permission
678 WHERE app_permission.app_id
679 IN (SELECT application_view.app_id
680 FROM application_view
681 WHERE application_view.name = OLD.app_name
684 app_permission.permission_id NOT IN (SELECT permission_view.permission_id
686 WHERE permission_view.name = "ALL_APPS" AND
687 permission_view.type_name = "ALL_APPS");
689 DELETE FROM path_view
690 WHERE path_view.owner_app_label_name=OLD.app_name;
694 -- APP PERMISSION VOLATILE VIEW ------------------------------------------------
695 DROP VIEW IF EXISTS app_permission_volatile_view;
696 CREATE VIEW app_permission_volatile_view AS
698 FROM app_permission_view
699 WHERE app_permission_view.is_volatile = 1;
702 DROP TRIGGER IF EXISTS app_permission_volatile_view_delete_trigger;
703 CREATE TRIGGER app_permission_volatile_view_delete_trigger
704 INSTEAD OF DELETE ON app_permission_volatile_view
706 DELETE FROM app_permission
707 WHERE app_permission.is_volatile = 1 AND
708 app_permission.app_id
709 IN (SELECT application_view.app_id
710 FROM application_view
711 WHERE application_view.name = OLD.app_name
716 -- APPLICATIONS PERMISSIONS ID -------------------------------------------------
717 -- All applications and their permissions
718 DROP VIEW IF EXISTS app_label_with_permission_view;
719 CREATE VIEW app_label_with_permission_view AS
720 SELECT app_permission.permission_id,
721 application_view.name,
722 application_view.app_id,
723 app_permission.is_volatile
725 INNER JOIN application_view USING(app_id)
726 WHERE app_permission.is_enabled = 1;
730 -- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
731 -- ltl = label to label
732 DROP VIEW IF EXISTS ltl_permission_permission_rule_view;
733 CREATE VIEW ltl_permission_permission_rule_view AS
734 SELECT app1.name AS subject,
737 app1.is_volatile OR app2.is_volatile AS is_volatile
738 FROM permission_permission_rule AS p
739 INNER JOIN app_label_with_permission_view AS app1 USING(permission_id)
740 INNER JOIN app_label_with_permission_view AS app2
741 ON app2.permission_id = p.target_permission_id
742 WHERE is_reverse = 0 AND app1.app_id != app2.app_id
744 SELECT app2.name AS subject,
747 app1.is_volatile OR app2.is_volatile AS is_volatile
748 FROM permission_permission_rule AS p
749 INNER JOIN app_label_with_permission_view AS app1 USING(permission_id)
750 INNER JOIN app_label_with_permission_view AS app2
751 ON app2.permission_id = p.target_permission_id
752 WHERE is_reverse = 1 AND app1.app_id != app2.app_id;
754 -- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
755 -- ltl = label to label
756 DROP VIEW IF EXISTS ltl_permission_label_rule_view;
757 CREATE VIEW ltl_permission_label_rule_view AS
758 SELECT app.name AS subject,
759 label.name AS object,
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 = 0 AND app.name != label.name
767 SELECT label.name AS subject,
771 FROM permission_label_rule AS p
772 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
773 INNER JOIN label USING(label_id)
774 WHERE is_reverse = 1 AND app.name != label.name;
779 -- PERMISSION TO PATH TYPE RULE VIEW -------------------------------------------
780 -- ltl = label to label
781 DROP VIEW IF EXISTS ltl_permission_app_path_type_rule_view;
782 CREATE VIEW ltl_permission_app_path_type_rule_view AS
783 SELECT app.name AS subject,
784 label.name AS object,
787 FROM permission_app_path_type_rule AS p
788 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
789 INNER JOIN app_path USING(app_path_type_id)
790 INNER JOIN label USING(label_id)
791 WHERE is_reverse = 0 AND app.name != label.name
793 SELECT label.name AS subject,
797 FROM permission_app_path_type_rule AS p
798 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
799 INNER JOIN app_path USING(app_path_type_id)
800 INNER JOIN label USING(label_id)
801 WHERE is_reverse = 1 AND app.name != label.name;
804 -- LABEL TO PATH TYPE RULE VIEW -------------------------------------------
805 -- ltl = label to label
806 DROP VIEW IF EXISTS ltl_label_app_path_type_rule_view;
807 CREATE VIEW ltl_label_app_path_type_rule_view AS
808 SELECT label.name AS subject,
809 path_label.name AS object,
812 FROM label_app_path_type_rule AS l
813 INNER JOIN label USING(label_id)
814 INNER JOIN app_path USING(app_path_type_id)
815 INNER JOIN label AS path_label ON app_path.label_id = path_label.label_id
816 WHERE is_reverse = 0 AND path_label.name != label.name
818 SELECT path_label.name AS subject,
819 label.name AS object,
822 FROM label_app_path_type_rule AS l
823 INNER JOIN label USING(label_id)
824 INNER JOIN app_path USING(app_path_type_id)
825 INNER JOIN label AS path_label ON app_path.label_id = path_label.label_id
826 WHERE is_reverse = 1 AND path_label.name != label.name;
829 -- PERMISSION TO APPLICATION'S OWN PATHS ---------------------------------------
830 -- ltl = label to label
831 DROP VIEW IF EXISTS ltl_app_path_view;
832 CREATE VIEW ltl_app_path_view AS
833 SELECT application_view.name AS subject,
834 label.name AS object,
835 app_path.access AS access
837 INNER JOIN application_view USING(app_id)
838 INNER JOIN label USING(label_id);
841 -- PERMISSION FROM PATHS TO APPLICATIONS ---------------------------------------
842 -- ltl = label to label
843 DROP VIEW IF EXISTS ltl_app_path_reverse_view;
844 CREATE VIEW ltl_app_path_reverse_view AS
845 SELECT label.name AS subject,
846 application_view.name AS object,
847 app_path.access_reverse AS access
849 INNER JOIN application_view USING(app_id)
850 INNER JOIN label USING(label_id)
851 WHERE app_path.access_reverse != 0 ;
854 -- SMACK RULES VIEWS -----------------------------------------------------------
855 DROP VIEW IF EXISTS all_smack_binary_rules_view;
856 CREATE VIEW all_smack_binary_rules_view AS
861 FROM (SELECT subject, object, access, is_volatile
862 FROM ltl_permission_permission_rule_view
864 SELECT subject, object, access, is_volatile
865 FROM ltl_permission_label_rule_view
867 SELECT subject, object, access, is_volatile
868 FROM ltl_permission_app_path_type_rule_view
870 SELECT subject, object, access, is_volatile
871 FROM ltl_label_app_path_type_rule_view
873 SELECT subject, object, access, 0
874 FROM ltl_app_path_view
876 SELECT subject, object, access, 0
877 FROM ltl_app_path_reverse_view
880 -- ALL INSERTED DATA VIEW ------------------------------------------------------
881 -- This view is used to clear the database from inserted rules.
882 -- We loose all information about installed applications
884 DROP VIEW IF EXISTS all_inserted_data;
885 CREATE VIEW all_inserted_data AS
889 DROP TRIGGER IF EXISTS all_inserted_data_delete_trigger;
890 CREATE TRIGGER all_inserted_data_delete_trigger INSTEAD OF
891 DELETE ON all_inserted_data
893 DELETE FROM permission_label_rule;
894 DELETE FROM permission_permission_rule;
895 DELETE FROM permission_app_path_type_rule;
897 DELETE FROM app_permission;
899 DELETE FROM permission;
900 DELETE FROM permission_type;
902 DELETE FROM app_path;
903 DELETE FROM app_path_type;
911 -- SMACK RULES MODIFICATIONS VIEW ----------------------------------------------
912 -- This definition is repeated during opening a connection with the database.
913 -- Used to get all smack rules, even volatile.
914 -- Ensure it's the same!
915 CREATE TEMPORARY VIEW modified_smack_rules AS
916 SELECT subject, object,
917 access_to_str(access_add) AS access_add,
918 access_to_str(access_del) AS access_del
920 SELECT subject, object,
921 s1.access & ~s2.access AS access_add,
922 s2.access & ~s1.access AS access_del
923 FROM current_smack_rule_modified AS s1
924 INNER JOIN history_smack_rule_modified AS s2
925 USING (subject, object)
926 WHERE s1.access != s2.access
930 SELECT subject, object,
931 s1.access AS access_add,
933 FROM current_smack_rule_modified AS s1
934 LEFT JOIN history_smack_rule_modified s2
935 USING (subject, object)
936 WHERE s2.subject IS NULL AND
941 SELECT subject, object,
943 s1.access AS access_del
944 FROM history_smack_rule_modified s1
945 LEFT JOIN current_smack_rule_modified AS s2
946 USING (subject, object)
947 WHERE s2.subject IS NULL AND
950 ORDER BY subject, object ASC;