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 = 2;
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)
25 CREATE INDEX IF NOT EXISTS app_index ON app(app_id, label_id);
28 CREATE TABLE IF NOT EXISTS app_permission (
29 app_id INTEGER NOT NULL,
30 permission_id INTEGER NOT NULL,
31 is_volatile INTEGER NOT NULL DEFAULT 0,
32 is_enabled INTEGER NOT NULL DEFAULT 1,
34 PRIMARY KEY(app_id, permission_id),
36 FOREIGN KEY(app_id) REFERENCES app(app_id),
37 FOREIGN KEY(permission_id) REFERENCES permission(permission_id)
40 CREATE TABLE IF NOT EXISTS app_path (
41 app_id INTEGER NOT NULL,
43 label_id INTEGER NOT NULL,
44 access INTEGER NOT NULL,
45 access_reverse 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.
152 CREATE TEMPORARY TABLE history_smack_rule(
153 subject TEXT NOT NULL,
154 object TEXT NOT NULL,
155 access INTEGER NOT NULL
159 CREATE TEMPORARY TABLE modified_label(
165 CREATE TEMPORARY TABLE all_smack_binary_rules_modified(
166 subject TEXT NOT NULL,
167 object TEXT NOT NULL,
168 access INTEGER NOT NULL
172 CREATE TEMPORARY TABLE history_smack_rule_modified(
173 subject TEXT NOT NULL,
174 object TEXT NOT NULL,
175 access INTEGER NOT NULL
179 -- PERMISSION VIEW -------------------------------------------------------------
180 DROP VIEW IF EXISTS permission_view;
181 CREATE VIEW permission_view AS
182 SELECT permission.permission_id, permission.name, permission_type.type_name
184 INNER JOIN permission_type USING(permission_type_id);
186 DROP TRIGGER IF EXISTS permission_view_insert_trigger;
187 CREATE TRIGGER permission_view_insert_trigger
188 INSTEAD OF INSERT ON permission_view
190 -- Add the permission
191 INSERT OR IGNORE INTO permission(name,permission_type_id)
192 SELECT NEW.name, permission_type.permission_type_id
194 WHERE permission_type.type_name = NEW.type_name;
197 -- Delete the previous definition of the permission
198 DELETE FROM permission_label_rule_view
199 WHERE permission_label_rule_view.permission_name = NEW.name AND
200 permission_label_rule_view.permission_type_name = NEW.type_name;
203 -- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
204 DROP VIEW IF EXISTS permission_label_rule_view;
205 CREATE VIEW permission_label_rule_view AS
207 permission_view.permission_id AS permission_id,
208 permission_view.name AS permission_name,
209 permission_view.type_name AS permission_type_name,
210 label.name AS label_name,
211 permission_label_rule.access AS access,
212 permission_label_rule.is_reverse AS is_reverse
213 FROM permission_label_rule
214 LEFT JOIN permission_view USING(permission_id)
215 LEFT JOIN label USING(label_id);
218 -- Preferred way of adding permission rules would be to use these ONE, multi-row
219 -- insert statement, with one check of a condition
220 -- that there is such permission id. It's impossible to make those inserts in C,
221 -- so the programmer has to secure, that there is a permission with a given id.
222 -- (Check it and insert in the same transaction)
223 -- In runtime we accept ONLY inserts with label.
224 -- All other kinds of permissions are filled during the database creation.
225 DROP TRIGGER IF EXISTS permission_label_rule_view_insert_trigger;
226 CREATE TRIGGER permission_label_rule_view_insert_trigger
227 INSTEAD OF INSERT ON permission_label_rule_view
229 -- Adding api features adds a label it it's not present.
230 INSERT OR IGNORE INTO label(name) VALUES (NEW.label_name);
232 INSERT OR REPLACE INTO permission_label_rule(permission_id,
236 SELECT NEW.permission_id,
238 str_to_access(NEW.access),
241 WHERE label.name = NEW.label_name;
245 -- TODO: Potential problem - undeleted labels.
246 DROP TRIGGER IF EXISTS permission_label_rule_view_delete_trigger;
247 CREATE TRIGGER permission_label_rule_view_delete_trigger
248 INSTEAD OF DELETE ON permission_label_rule_view
250 DELETE FROM permission_label_rule
251 WHERE permission_label_rule.permission_id
252 IN (SELECT permission_view.permission_id
254 WHERE permission_view.name = OLD.permission_name AND
255 permission_view.type_name = OLD.permission_type_name);
259 -- PERMISSION TO APP PATH TYPE RULE VIEW ---------------------------------------
260 DROP VIEW IF EXISTS permission_app_path_type_rule_view;
261 CREATE VIEW permission_app_path_type_rule_view AS
263 permission_view.permission_id AS permission_id,
264 permission_view.name AS permission_name,
265 permission_view.type_name AS permission_type_name,
266 app_path_type.name AS app_path_type_name,
267 permission_app_path_type_rule.access AS access,
268 permission_app_path_type_rule.is_reverse AS is_reverse
269 FROM permission_app_path_type_rule
270 LEFT JOIN permission_view USING(permission_id)
271 LEFT JOIN app_path_type USING(app_path_type_id);
274 DROP TRIGGER IF EXISTS permission_app_path_type_rule_view_insert_trigger;
275 CREATE TRIGGER permission_app_path_type_rule_view_insert_trigger
277 ON permission_app_path_type_rule_view
278 WHEN NEW.permission_id IS NULL
280 INSERT INTO permission_app_path_type_rule(permission_id,
284 SELECT permission_view.permission_id,
285 app_path_type.app_path_type_id,
286 str_to_access(NEW.access),
288 FROM permission_view, app_path_type
289 WHERE permission_view.name = NEW.permission_name AND
290 permission_view.type_name = NEW.permission_type_name AND
291 app_path_type.name = NEW.app_path_type_name;
294 DROP TRIGGER IF EXISTS permission_app_path_type_rule_view_delete_trigger;
295 CREATE TRIGGER permission_app_path_type_rule_view_delete_trigger
297 ON permission_app_path_type_rule_view
300 DELETE FROM permission_app_path_type_rule
301 WHERE permission_app_path_type_rule.permission_id
302 IN (SELECT permission_view.permission_id
304 WHERE permission_view.name = OLD.permission_name AND
305 permission_view.type_name = OLD.permission_type_name);
309 CREATE TRIGGER permission_app_path_type_id_rule_view_insert_trigger
311 ON permission_app_path_type_rule_view
312 WHEN NEW.permission_id IS NOT NULL
314 INSERT OR REPLACE INTO permission_app_path_type_rule(permission_id,
318 SELECT NEW.permission_id,
319 app_path_type.app_path_type_id,
320 str_to_access(NEW.access),
323 WHERE app_path_type.name = NEW.app_path_type_name;
327 -- LABEL TO APP PATH TYPE RULE VIEW --------------------------------------------
328 DROP VIEW IF EXISTS label_app_path_type_rule_view;
329 CREATE VIEW label_app_path_type_rule_view AS
331 label_app_path_type_rule.label_id AS label_id,
332 label.name AS label_name,
333 app_path_type.name AS app_path_type_name,
334 label_app_path_type_rule.access AS access,
335 label_app_path_type_rule.is_reverse AS is_reverse
336 FROM label_app_path_type_rule
337 LEFT JOIN label USING(label_id)
338 LEFT JOIN app_path_type USING(app_path_type_id);
341 DROP TRIGGER IF EXISTS label_app_path_type_rule_view_insert_trigger;
342 CREATE TRIGGER label_app_path_type_rule_view_insert_trigger
344 ON label_app_path_type_rule_view
346 INSERT OR IGNORE INTO label(name) VALUES (NEW.label_name);
348 INSERT INTO label_app_path_type_rule(label_id,
352 SELECT label.label_id,
353 app_path_type.app_path_type_id,
354 str_to_access(NEW.access),
356 FROM label, app_path_type
357 WHERE label.name = NEW.label_name AND
358 app_path_type.name = NEW.app_path_type_name;
362 DROP TRIGGER IF EXISTS label_app_path_type_rule_view_delete_trigger;
363 CREATE TRIGGER label_app_path_type_rule_view_delete_trigger
365 ON label_app_path_type_rule_view
367 -- Delete the rules with this label
368 DELETE FROM label_app_path_type_rule
369 WHERE label_app_path_type_rule.label_id
370 IN (SELECT label.label_id
372 WHERE label.name = OLD.label_name);
374 -- Delete the label if it's not referenced
375 DELETE FROM label_view
376 WHERE label_view.name = OLD.label_name;
379 -- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
380 DROP VIEW IF EXISTS permission_permission_rule_view;
381 CREATE VIEW permission_permission_rule_view AS
383 tmp_permission_view.permission_id AS permission_id,
384 tmp_permission_view.name AS permission_name,
385 tmp_permission_view.type_name AS permission_type_name,
386 tmp_target_permission_view.name AS target_permission_name,
387 tmp_target_permission_view.type_name AS target_permission_type_name,
388 permission_permission_rule.access AS access,
389 permission_permission_rule.is_reverse AS is_reverse
390 FROM permission_permission_rule
391 LEFT JOIN permission_view AS tmp_permission_view USING(permission_id)
392 LEFT JOIN permission_view AS tmp_target_permission_view
393 ON permission_permission_rule.target_permission_id = tmp_target_permission_view.permission_id;
396 -- Trigger for manual addition of rules.
397 DROP TRIGGER IF EXISTS permission_permission_rule_view_insert_trigger;
398 CREATE TRIGGER permission_permission_rule_view_insert_trigger
399 INSTEAD OF INSERT ON permission_permission_rule_view
402 INSERT OR REPLACE INTO permission_permission_rule(permission_id,
403 target_permission_id,
406 SELECT tmp_permission_view.permission_id,
407 tmp_target_permission_view.permission_id,
408 str_to_access(NEW.access),
410 FROM permission_view AS tmp_permission_view,
411 permission_view AS tmp_target_permission_view
412 WHERE tmp_permission_view.name = NEW.permission_name AND
413 tmp_permission_view.type_name = NEW.permission_type_name AND
414 tmp_target_permission_view.name = NEW.target_permission_name AND
415 tmp_target_permission_view.type_name = NEW.target_permission_type_name;
419 DROP TRIGGER IF EXISTS permission_permission_rule_view_delete_trigger;
420 CREATE TRIGGER permission_permission_rule_view_delete_trigger
421 INSTEAD OF DELETE ON permission_permission_rule_view
424 DELETE FROM permission_permission_rule_view
425 WHERE permission_permission_rule_view.permission_id
426 IN (SELECT permission_view.permission_id
428 WHERE permission_view.name = OLD.permission_name AND
429 permission_view.type_name = OLD.permission_type_name);
434 -- LABEL VIEW ------------------------------------------------------------------
435 -- There are no INSTEAD OF triggers on regular tables.
436 -- We use a view to delete unreferenced labels:
437 DROP VIEW IF EXISTS label_view;
438 CREATE VIEW label_view AS SELECT * FROM label;
440 DROP TRIGGER IF EXISTS label_view_delete_trigger;
441 CREATE TRIGGER label_view_delete_trigger
442 INSTEAD OF DELETE ON label_view
443 WHEN OLD.label_id NOT IN (SELECT app.label_id
445 OLD.label_id NOT IN (SELECT permission_label_rule.label_id
446 FROM permission_label_rule) AND
447 OLD.label_id NOT IN (SELECT app_path.label_id
449 OLD.label_id NOT IN (SELECT label_app_path_type_rule.label_id
450 FROM label_app_path_type_rule)
452 DELETE FROM label WHERE label.name = OLD.name;
456 -- APPLICATION VIEW ------------------------------------------------------------
457 DROP VIEW IF EXISTS application_view;
458 CREATE VIEW application_view AS
459 SELECT app.app_id, label.name
461 INNER JOIN app USING(label_id);
463 DROP TRIGGER IF EXISTS application_view_insert_trigger;
464 CREATE TRIGGER application_view_insert_trigger
465 INSTEAD OF INSERT ON application_view
467 -- The app's label could have been added by the permission.
468 INSERT OR IGNORE INTO label(name) VALUES (NEW.name);
471 INSERT INTO app(label_id)
474 WHERE label.name = NEW.name;
476 -- Add the permission granted to all applications
477 INSERT INTO app_permission_view(app_id, name, type_name, is_volatile, is_enabled)
478 VALUES (last_insert_rowid(), "ALL_APPS", "ALL_APPS", 0, 1);
483 DROP TRIGGER IF EXISTS application_view_delete_trigger;
484 CREATE TRIGGER application_view_delete_trigger
485 INSTEAD OF DELETE ON application_view
487 -- Delete rules that correspond to app's paths:
488 DELETE FROM permission_label_rule
489 WHERE permission_label_rule.label_id IN
490 (SELECT app_path.label_id
492 INNER JOIN application_view USING(app_id)
493 WHERE application_view.name = OLD.name);
496 DELETE FROM path_view
497 WHERE path_view.owner_app_label_name=OLD.name;
499 -- Delete apps permissions:
500 DELETE FROM app_permission
501 WHERE app_permission.app_id
502 IN (SELECT application_view.app_id
503 FROM application_view
504 WHERE application_view.name = OLD.name
507 -- Delete application
509 WHERE app.app_id IN (SELECT application_view.app_id
510 FROM application_view
511 WHERE application_view.name = OLD.name
515 DELETE FROM label_view
516 WHERE label_view.name = OLD.name;
520 -- PATH VIEW -------------------------------------------------------------------
521 DROP VIEW IF EXISTS path_view;
522 CREATE VIEW path_view AS
523 SELECT application_view.name AS owner_app_label_name,
524 app_path.path AS path,
525 label.name AS path_label_name,
526 app_path.access AS access,
527 app_path.access_reverse AS access_reverse,
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 OR IGNORE INTO app_path(app_id, path, label_id, access, access_reverse, app_path_type_id)
548 SELECT application_view.app_id,
551 str_to_access(NEW.access),
552 str_to_access(NEW.access_reverse),
553 app_path_type.app_path_type_id
554 FROM application_view, app_path_type, label
555 WHERE application_view.name = NEW.owner_app_label_name AND
556 app_path_type.name = NEW.path_type_name AND
557 label.name = NEW.path_label_name;
560 DROP TRIGGER IF EXISTS path_view_delete_trigger;
561 CREATE TRIGGER path_view_delete_trigger
562 INSTEAD OF DELETE ON path_view
566 WHERE app_path.app_id IN (SELECT app.app_id
568 WHERE label.name = OLD.owner_app_label_name AND
569 app.label_id = label.label_id);
571 -- Delete the path's label if it's not used any more
572 DELETE FROM label_view WHERE label_view.name = OLD.path_label_name;
575 -- APP PERMISSION LIST VIEW ----------------------------------------------------
576 -- Used in check_app_permission_internal to check if permissions are present
577 -- TODO: Check if SQLite optimizer doesn't change app_permission_view to the same code.
578 DROP VIEW IF EXISTS app_permission_list_view;
579 CREATE VIEW app_permission_list_view AS
580 SELECT app_permission.app_id AS app_id,
581 app_permission.permission_id AS permission_id,
582 permission_view.name AS permission_name,
583 permission_view.type_name AS permission_type_name,
584 app_permission.is_volatile AS is_volatile,
585 app_permission.is_enabled AS is_enabled
587 INNER JOIN permission_view USING(permission_id);
592 -- APP PERMISSION VIEW ---------------------------------------------------------
593 DROP VIEW IF EXISTS app_permission_view;
594 CREATE VIEW app_permission_view AS
595 SELECT application_view.app_id,
596 application_view.name AS app_name,
597 permission_view.permission_id,
598 permission_view.name,
599 permission_view.type_name,
600 app_permission.is_volatile,
601 app_permission.is_enabled
603 INNER JOIN application_view USING(app_id)
604 INNER JOIN permission_view USING(permission_id);
607 DROP TRIGGER IF EXISTS app_permission_view_insert_trigger;
608 CREATE TRIGGER app_permission_view_insert_trigger
609 INSTEAD OF INSERT ON app_permission_view
611 INSERT INTO app_permission(app_id, permission_id, is_volatile, is_enabled)
613 permission_view.permission_id,
617 WHERE permission_view.name = NEW.name AND
618 permission_view.type_name = NEW.type_name;
624 -- It's forbidden do change permission from not volatile to volatile.
625 -- We have to check it before inserting anything.
626 -- Used in updating permissions
627 DROP TRIGGER IF EXISTS app_permission_view_update_trigger;
628 CREATE TRIGGER app_permission_view_update_trigger
629 INSTEAD OF UPDATE ON app_permission_view
631 UPDATE OR IGNORE app_permission
632 SET is_enabled = NEW.is_enabled
633 WHERE app_permission.app_id = OLD.app_id AND
634 app_permission.permission_id
635 IN (SELECT permission_view.permission_id
637 WHERE permission_view.name = OLD.name AND
638 permission_view.type_name = OLD.type_name
643 DROP TRIGGER IF EXISTS app_permission_view_delete_trigger;
644 CREATE TRIGGER app_permission_view_delete_trigger
645 INSTEAD OF DELETE ON app_permission_view
647 DELETE FROM app_permission
648 WHERE app_permission.app_id
649 IN (SELECT application_view.app_id
650 FROM application_view
651 WHERE application_view.name = OLD.app_name
654 app_permission.permission_id NOT IN (SELECT permission_view.permission_id
656 WHERE permission_view.name = "ALL_APPS" AND
657 permission_view.type_name = "ALL_APPS");
659 DELETE FROM path_view
660 WHERE path_view.owner_app_label_name=OLD.app_name;
664 -- APP PERMISSION VOLATILE VIEW ------------------------------------------------
665 DROP VIEW IF EXISTS app_permission_volatile_view;
666 CREATE VIEW app_permission_volatile_view AS
668 FROM app_permission_view
669 WHERE app_permission_view.is_volatile = 1;
672 DROP TRIGGER IF EXISTS app_permission_volatile_view_delete_trigger;
673 CREATE TRIGGER app_permission_volatile_view_delete_trigger
674 INSTEAD OF DELETE ON app_permission_volatile_view
676 DELETE FROM app_permission
677 WHERE app_permission.is_volatile = 1 AND
678 app_permission.app_id
679 IN (SELECT application_view.app_id
680 FROM application_view
681 WHERE application_view.name = OLD.app_name
686 -- APPLICATIONS PERMISSIONS ID -------------------------------------------------
687 -- All applications and their permissions
688 DROP VIEW IF EXISTS app_label_with_permission_view;
689 CREATE VIEW app_label_with_permission_view AS
690 SELECT app_permission.permission_id,
691 application_view.name,
692 application_view.app_id,
693 app_permission.is_volatile
695 INNER JOIN application_view USING(app_id)
696 WHERE app_permission.is_enabled = 1;
700 -- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
701 -- ltl = label to label
702 DROP VIEW IF EXISTS ltl_permission_permission_rule_view;
703 CREATE VIEW ltl_permission_permission_rule_view AS
704 SELECT (CASE WHEN is_reverse = 0 THEN app1.name ELSE app2.name END) AS subject,
705 (CASE WHEN is_reverse = 1 THEN app1.name ELSE app2.name END) AS object,
707 app1.is_volatile OR app2.is_volatile AS is_volatile
708 FROM permission_permission_rule AS p
709 INNER JOIN app_label_with_permission_view AS app1 USING(permission_id)
710 INNER JOIN app_label_with_permission_view AS app2
711 ON app2.permission_id = p.target_permission_id
712 WHERE app1.app_id != app2.app_id;
714 -- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
715 -- ltl = label to label
716 DROP VIEW IF EXISTS ltl_permission_label_rule_view;
717 CREATE VIEW ltl_permission_label_rule_view AS
718 SELECT (CASE WHEN is_reverse = 0 THEN app.name ELSE label.name END) AS subject,
719 (CASE WHEN is_reverse = 1 THEN app.name ELSE label.name END) AS object,
722 FROM permission_label_rule AS p
723 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
724 INNER JOIN label USING(label_id)
725 WHERE app.name != label.name;
729 -- PERMISSION TO PATH TYPE RULE VIEW -------------------------------------------
730 -- ltl = label to label
731 DROP VIEW IF EXISTS ltl_permission_app_path_type_rule_view;
732 CREATE VIEW ltl_permission_app_path_type_rule_view AS
733 SELECT (CASE WHEN is_reverse = 0 THEN app.name ELSE label.name END) AS subject,
734 (CASE WHEN is_reverse = 1 THEN app.name ELSE label.name END) AS object,
737 FROM permission_app_path_type_rule AS p
738 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
739 INNER JOIN app_path USING(app_path_type_id)
740 INNER JOIN label USING(label_id)
741 WHERE app.name != label.name;
744 -- LABEL TO PATH TYPE RULE VIEW -------------------------------------------
745 -- ltl = label to label
746 DROP VIEW IF EXISTS ltl_label_app_path_type_rule_view;
747 CREATE VIEW ltl_label_app_path_type_rule_view AS
748 SELECT (CASE WHEN is_reverse = 0 THEN label.name ELSE path_label.name END) AS subject,
749 (CASE WHEN is_reverse = 1 THEN label.name ELSE path_label.name END) AS object,
752 FROM label_app_path_type_rule AS l
753 INNER JOIN label USING(label_id)
754 INNER JOIN app_path USING(app_path_type_id)
755 INNER JOIN label AS path_label ON app_path.label_id = path_label.label_id
756 WHERE path_label.name != label.name;
759 -- PERMISSION TO APPLICATION'S OWN PATHS ---------------------------------------
760 -- ltl = label to label
761 DROP VIEW IF EXISTS ltl_app_path_view;
762 CREATE VIEW ltl_app_path_view AS
763 SELECT application_view.name AS subject,
764 label.name AS object,
765 app_path.access AS access
767 INNER JOIN application_view USING(app_id)
768 INNER JOIN label USING(label_id);
771 -- PERMISSION FROM PATHS TO APPLICATIONS ---------------------------------------
772 -- ltl = label to label
773 DROP VIEW IF EXISTS ltl_app_path_reverse_view;
774 CREATE VIEW ltl_app_path_reverse_view AS
775 SELECT label.name AS subject,
776 application_view.name AS object,
777 app_path.access_reverse AS access
779 INNER JOIN application_view USING(app_id)
780 INNER JOIN label USING(label_id)
781 WHERE app_path.access_reverse != 0 ;
784 -- SMACK RULES VIEWS -----------------------------------------------------------
785 DROP VIEW IF EXISTS all_smack_binary_rules_view;
786 CREATE VIEW all_smack_binary_rules_view AS
789 bitwise_or(access) AS access,
790 MIN(is_volatile) AS is_volatile
791 FROM (SELECT subject, object, access, is_volatile
792 FROM ltl_permission_permission_rule_view
794 SELECT subject, object, access, is_volatile
795 FROM ltl_permission_label_rule_view
797 SELECT subject, object, access, is_volatile
798 FROM ltl_permission_app_path_type_rule_view
800 SELECT subject, object, access, is_volatile
801 FROM ltl_label_app_path_type_rule_view
803 SELECT subject, object, access, 0
804 FROM ltl_app_path_view
806 SELECT subject, object, access, 0
807 FROM ltl_app_path_reverse_view
809 GROUP BY subject, object
810 ORDER BY subject, object ASC;
812 -- ALL INSERTED DATA VIEW ------------------------------------------------------
813 -- This view is used to clear the database from inserted rules.
814 -- We loose all information about installed applications
816 DROP VIEW IF EXISTS all_inserted_data;
817 CREATE VIEW all_inserted_data AS
821 DROP TRIGGER IF EXISTS all_inserted_data_delete_trigger;
822 CREATE TRIGGER all_inserted_data_delete_trigger INSTEAD OF
823 DELETE ON all_inserted_data
825 DELETE FROM permission_label_rule;
826 DELETE FROM permission_permission_rule;
827 DELETE FROM permission_app_path_type_rule;
829 DELETE FROM app_permission;
831 DELETE FROM permission;
832 DELETE FROM permission_type;
834 DELETE FROM app_path;
835 DELETE FROM app_path_type;
843 -- SMACK RULES MODIFICATIONS VIEW ----------------------------------------------
844 -- This definition is repeated during opening a connection with the database.
845 -- Used to get all smack rules, even volatile.
846 -- Ensure it's the same!
847 CREATE TEMPORARY VIEW modified_smack_rules AS
848 SELECT subject, object,
849 access_to_str(access_add) AS access_add,
850 access_to_str(access_del) AS access_del
852 SELECT subject, object,
853 s1.access & ~s2.access AS access_add,
854 s2.access & ~s1.access AS access_del
855 FROM all_smack_binary_rules AS s1
856 INNER JOIN history_smack_rule AS s2
857 USING (subject, object)
858 WHERE s1.access != s2.access
862 SELECT subject, object,
863 s1.access AS access_add,
865 FROM all_smack_binary_rules AS s1
866 LEFT JOIN history_smack_rule s2
867 USING (subject, object)
868 WHERE s2.subject IS NULL AND
873 SELECT subject, object,
875 s1.access AS access_del
876 FROM history_smack_rule s1
877 LEFT JOIN all_smack_binary_rules AS s2
878 USING (subject, object)
879 WHERE s2.subject IS NULL AND
882 ORDER BY subject, object ASC;