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);
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)
142 -- Not aggregated rules
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 -- Index used for grouping and sorting by (subject, object)
151 -- and used for filtering by subject
152 CREATE INDEX IF NOT EXISTS all_smack_binary_rules_subject_object_index
153 ON all_smack_binary_rules(subject, object);
155 -- Index used for filtering by object
156 CREATE INDEX IF NOT EXISTS all_smack_binary_rules_object_index
157 ON all_smack_binary_rules(object);
159 -- TEMPORARY TABLES ------------------------------------------------------------
160 -- Definitions are repeated in code.
162 CREATE TEMPORARY TABLE modified_label(
163 name TEXT NOT NULL PRIMARY KEY
166 -- Not aggregated subset of modified rules
167 CREATE TEMPORARY TABLE all_smack_binary_rules_modified(
168 subject TEXT NOT NULL,
169 object TEXT NOT NULL,
170 access INTEGER NOT NULL,
171 is_volatile INTEGER NOT NULL
174 -- Aggregated subset of rules after changes
175 CREATE TEMPORARY TABLE current_smack_rule_modified(
176 subject TEXT NOT NULL,
177 object TEXT NOT NULL,
178 access INTEGER NOT NULL
181 -- Aggregated subset of rules before changes
182 CREATE TEMPORARY TABLE history_smack_rule_modified(
183 subject TEXT NOT NULL,
184 object TEXT NOT NULL,
185 access INTEGER NOT NULL
189 -- PERMISSION VIEW -------------------------------------------------------------
190 DROP VIEW IF EXISTS permission_view;
191 CREATE VIEW permission_view AS
192 SELECT permission.permission_id, permission.name, permission_type.type_name
194 INNER JOIN permission_type USING(permission_type_id);
196 DROP TRIGGER IF EXISTS permission_view_insert_trigger;
197 CREATE TRIGGER permission_view_insert_trigger
198 INSTEAD OF INSERT ON permission_view
200 -- Add the permission
201 INSERT OR IGNORE INTO permission(name,permission_type_id)
202 SELECT NEW.name, permission_type.permission_type_id
204 WHERE permission_type.type_name = NEW.type_name;
207 -- Delete the previous definition of the permission
208 DELETE FROM permission_label_rule_view
209 WHERE permission_name = NEW.name AND
210 permission_type_name = NEW.type_name;
212 DELETE FROM permission_permission_rule_view
213 WHERE permission_name = NEW.name AND
214 permission_type_name = NEW.type_name;
216 DELETE FROM permission_app_path_type_rule_view
217 WHERE permission_name = NEW.name AND
218 permission_type_name = NEW.type_name;
222 -- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
223 DROP VIEW IF EXISTS permission_label_rule_view;
224 CREATE VIEW permission_label_rule_view AS
226 permission_view.permission_id AS permission_id,
227 permission_view.name AS permission_name,
228 permission_view.type_name AS permission_type_name,
229 label.name AS label_name,
230 permission_label_rule.access AS access,
231 permission_label_rule.is_reverse AS is_reverse
232 FROM permission_label_rule
233 LEFT JOIN permission_view USING(permission_id)
234 LEFT JOIN label USING(label_id);
237 -- Preferred way of adding permission rules would be to use these ONE, multi-row
238 -- insert statement, with one check of a condition
239 -- that there is such permission id. It's impossible to make those inserts in C,
240 -- so the programmer has to secure, that there is a permission with a given id.
241 -- (Check it and insert in the same transaction)
242 -- In runtime we accept ONLY inserts with label.
243 -- All other kinds of permissions are filled during the database creation.
244 DROP TRIGGER IF EXISTS permission_label_rule_view_insert_trigger;
245 CREATE TRIGGER permission_label_rule_view_insert_trigger
246 INSTEAD OF INSERT ON permission_label_rule_view
248 -- Adding api features adds a label it it's not present.
249 INSERT OR IGNORE INTO label(name) VALUES (NEW.label_name);
251 INSERT OR REPLACE INTO permission_label_rule(permission_id,
255 SELECT NEW.permission_id,
257 str_to_access(NEW.access),
260 WHERE label.name = NEW.label_name;
264 -- TODO: Potential problem - undeleted labels.
265 DROP TRIGGER IF EXISTS permission_label_rule_view_delete_trigger;
266 CREATE TRIGGER permission_label_rule_view_delete_trigger
267 INSTEAD OF DELETE ON permission_label_rule_view
269 DELETE FROM permission_label_rule
270 WHERE permission_label_rule.permission_id
271 IN (SELECT permission_view.permission_id
273 WHERE permission_view.name = OLD.permission_name AND
274 permission_view.type_name = OLD.permission_type_name);
278 -- PERMISSION TO APP PATH TYPE RULE VIEW ---------------------------------------
279 DROP VIEW IF EXISTS permission_app_path_type_rule_view;
280 CREATE VIEW permission_app_path_type_rule_view AS
282 permission_view.permission_id AS permission_id,
283 permission_view.name AS permission_name,
284 permission_view.type_name AS permission_type_name,
285 app_path_type.name AS app_path_type_name,
286 permission_app_path_type_rule.access AS access,
287 permission_app_path_type_rule.is_reverse AS is_reverse
288 FROM permission_app_path_type_rule
289 LEFT JOIN permission_view USING(permission_id)
290 LEFT JOIN app_path_type USING(app_path_type_id);
293 DROP TRIGGER IF EXISTS permission_app_path_type_rule_view_insert_trigger;
294 CREATE TRIGGER permission_app_path_type_rule_view_insert_trigger
296 ON permission_app_path_type_rule_view
297 WHEN NEW.permission_id IS NULL
299 INSERT INTO permission_app_path_type_rule(permission_id,
303 SELECT permission_view.permission_id,
304 app_path_type.app_path_type_id,
305 str_to_access(NEW.access),
307 FROM permission_view, app_path_type
308 WHERE permission_view.name = NEW.permission_name AND
309 permission_view.type_name = NEW.permission_type_name AND
310 app_path_type.name = NEW.app_path_type_name;
313 DROP TRIGGER IF EXISTS permission_app_path_type_rule_view_delete_trigger;
314 CREATE TRIGGER permission_app_path_type_rule_view_delete_trigger
316 ON permission_app_path_type_rule_view
319 DELETE FROM permission_app_path_type_rule
320 WHERE permission_app_path_type_rule.permission_id
321 IN (SELECT permission_view.permission_id
323 WHERE permission_view.name = OLD.permission_name AND
324 permission_view.type_name = OLD.permission_type_name);
328 CREATE TRIGGER permission_app_path_type_id_rule_view_insert_trigger
330 ON permission_app_path_type_rule_view
331 WHEN NEW.permission_id IS NOT NULL
333 INSERT OR REPLACE INTO permission_app_path_type_rule(permission_id,
337 SELECT NEW.permission_id,
338 app_path_type.app_path_type_id,
339 str_to_access(NEW.access),
342 WHERE app_path_type.name = NEW.app_path_type_name;
346 -- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
347 DROP VIEW IF EXISTS permission_permission_rule_view;
348 CREATE VIEW permission_permission_rule_view AS
350 tmp_permission_view.permission_id AS permission_id,
351 tmp_permission_view.name AS permission_name,
352 tmp_permission_view.type_name AS permission_type_name,
353 tmp_target_permission_view.name AS target_permission_name,
354 tmp_target_permission_view.type_name AS target_permission_type_name,
355 permission_permission_rule.access AS access,
356 permission_permission_rule.is_reverse AS is_reverse
357 FROM permission_permission_rule
358 LEFT JOIN permission_view AS tmp_permission_view USING(permission_id)
359 LEFT JOIN permission_view AS tmp_target_permission_view
360 ON permission_permission_rule.target_permission_id = tmp_target_permission_view.permission_id;
363 -- Trigger for manual addition of rules.
364 DROP TRIGGER IF EXISTS permission_permission_rule_view_insert_trigger;
365 CREATE TRIGGER permission_permission_rule_view_insert_trigger
366 INSTEAD OF INSERT ON permission_permission_rule_view
369 INSERT OR REPLACE INTO permission_permission_rule(permission_id,
370 target_permission_id,
373 SELECT tmp_permission_view.permission_id,
374 tmp_target_permission_view.permission_id,
375 str_to_access(NEW.access),
377 FROM permission_view AS tmp_permission_view,
378 permission_view AS tmp_target_permission_view
379 WHERE tmp_permission_view.name = NEW.permission_name AND
380 tmp_permission_view.type_name = NEW.permission_type_name AND
381 tmp_target_permission_view.name = NEW.target_permission_name AND
382 tmp_target_permission_view.type_name = NEW.target_permission_type_name;
386 DROP TRIGGER IF EXISTS permission_permission_rule_view_delete_trigger;
387 CREATE TRIGGER permission_permission_rule_view_delete_trigger
388 INSTEAD OF DELETE ON permission_permission_rule_view
391 DELETE FROM permission_permission_rule
392 WHERE permission_permission_rule.permission_id
393 IN (SELECT permission_view.permission_id
395 WHERE permission_view.name = OLD.permission_name AND
396 permission_view.type_name = OLD.permission_type_name);
401 -- LABEL VIEW ------------------------------------------------------------------
402 -- There are no INSTEAD OF triggers on regular tables.
403 -- We use a view to delete unreferenced labels:
404 DROP VIEW IF EXISTS label_view;
405 CREATE VIEW label_view AS SELECT * FROM label;
407 DROP TRIGGER IF EXISTS label_view_delete_trigger;
408 CREATE TRIGGER label_view_delete_trigger
409 INSTEAD OF DELETE ON label_view
410 WHEN OLD.label_id NOT IN (SELECT app.label_id
412 OLD.label_id NOT IN (SELECT permission_label_rule.label_id
413 FROM permission_label_rule) AND
414 OLD.label_id NOT IN (SELECT app_path.label_id
417 DELETE FROM label WHERE label.name = OLD.name;
421 -- APPLICATION VIEW ------------------------------------------------------------
422 DROP VIEW IF EXISTS application_view;
423 CREATE VIEW application_view AS
424 SELECT app.app_id, label.name
426 INNER JOIN app USING(label_id);
428 DROP TRIGGER IF EXISTS application_view_insert_trigger;
429 CREATE TRIGGER application_view_insert_trigger
430 INSTEAD OF INSERT ON application_view
432 -- The app's label could have been added by the permission.
433 INSERT OR IGNORE INTO label(name) VALUES (NEW.name);
436 INSERT INTO app(label_id)
439 WHERE label.name = NEW.name;
441 -- Add the permission granted to all applications
442 INSERT INTO app_permission_view(app_id, name, type_name, is_volatile, is_enabled)
443 VALUES (last_insert_rowid(), "ALL_APPS", "ALL_APPS", 0, 1);
448 DROP TRIGGER IF EXISTS application_view_delete_trigger;
449 CREATE TRIGGER application_view_delete_trigger
450 INSTEAD OF DELETE ON application_view
452 -- Delete rules that correspond to app's paths:
453 DELETE FROM permission_label_rule
454 WHERE permission_label_rule.label_id IN
455 (SELECT app_path.label_id
457 INNER JOIN application_view USING(app_id)
458 WHERE application_view.name = OLD.name);
461 DELETE FROM path_view
462 WHERE path_view.owner_app_label_name=OLD.name;
464 -- Delete apps permissions:
465 DELETE FROM app_permission
466 WHERE app_permission.app_id
467 IN (SELECT application_view.app_id
468 FROM application_view
469 WHERE application_view.name = OLD.name
472 -- Delete application
474 WHERE app.app_id IN (SELECT application_view.app_id
475 FROM application_view
476 WHERE application_view.name = OLD.name
480 DELETE FROM label_view
481 WHERE label_view.name = OLD.name;
485 -- PATH VIEW -------------------------------------------------------------------
486 DROP VIEW IF EXISTS path_view;
487 CREATE VIEW path_view AS
488 SELECT application_view.name AS owner_app_label_name,
489 app_path.path AS path,
490 label.name AS path_label_name,
491 app_path.access AS access,
492 app_path.access_reverse AS access_reverse,
493 app_path_type.name AS path_type_name
496 LEFT JOIN app_path_type USING (app_path_type_id)
497 LEFT JOIN application_view USING (app_id)
498 LEFT JOIN label USING (label_id);
501 -- For an existing application we add a path.
502 DROP TRIGGER IF EXISTS path_view_insert_trigger;
503 CREATE TRIGGER path_view_insert_trigger
504 INSTEAD OF INSERT ON path_view
505 WHEN NEW.owner_app_label_name IN (SELECT application_view.name
506 FROM application_view)
508 -- The path's label could have been added by the permission.
509 INSERT OR IGNORE INTO label(name) VALUES (NEW.path_label_name);
512 INSERT OR IGNORE INTO app_path(app_id, path, label_id, access, access_reverse, app_path_type_id)
513 SELECT application_view.app_id,
516 str_to_access(NEW.access),
517 str_to_access(NEW.access_reverse),
518 app_path_type.app_path_type_id
519 FROM application_view, app_path_type, label
520 WHERE application_view.name = NEW.owner_app_label_name AND
521 app_path_type.name = NEW.path_type_name AND
522 label.name = NEW.path_label_name;
525 DROP TRIGGER IF EXISTS path_view_delete_trigger;
526 CREATE TRIGGER path_view_delete_trigger
527 INSTEAD OF DELETE ON path_view
531 WHERE app_path.app_id IN (SELECT app.app_id
533 WHERE label.name = OLD.owner_app_label_name AND
534 app.label_id = label.label_id);
536 -- Delete the path's label if it's not used any more
537 DELETE FROM label_view WHERE label_view.name = OLD.path_label_name;
541 -- PATH_REMOVAL VIEW -------------------------------------------------------------------
542 DROP VIEW IF EXISTS path_removal_view;
543 CREATE VIEW path_removal_view AS
544 SELECT application_view.app_id AS owner_app_id,
545 application_view.name AS owner_app_label_name,
546 app_path.path AS path,
547 label.label_id AS path_label_id
549 LEFT JOIN application_view USING (app_id)
550 LEFT JOIN label USING (label_id);
552 DROP TRIGGER IF EXISTS path_removal_delete_trigger;
553 CREATE TRIGGER path_removal_delete_trigger
554 INSTEAD OF DELETE ON path_removal_view
558 WHERE app_path.app_id = OLD.owner_app_id AND
559 app_path.path = OLD.path;
561 -- Delete the path's label if it's not used anymore.
562 DELETE FROM label_view WHERE label_view.label_id = OLD.path_label_id;
566 -- APP PERMISSION LIST VIEW ----------------------------------------------------
567 -- Used in check_app_permission_internal to check if permissions are present
568 -- TODO: Check if SQLite optimizer doesn't change app_permission_view to the same code.
569 DROP VIEW IF EXISTS app_permission_list_view;
570 CREATE VIEW app_permission_list_view AS
571 SELECT app_permission.app_id AS app_id,
572 app_permission.permission_id AS permission_id,
573 permission_view.name AS permission_name,
574 permission_view.type_name AS permission_type_name,
575 app_permission.is_volatile AS is_volatile,
576 app_permission.is_enabled AS is_enabled
578 INNER JOIN permission_view USING(permission_id);
583 -- APP PERMISSION VIEW ---------------------------------------------------------
584 DROP VIEW IF EXISTS app_permission_view;
585 CREATE VIEW app_permission_view AS
586 SELECT application_view.app_id,
587 application_view.name AS app_name,
588 permission_view.permission_id,
589 permission_view.name,
590 permission_view.type_name,
591 app_permission.is_volatile,
592 app_permission.is_enabled
594 INNER JOIN application_view USING(app_id)
595 INNER JOIN permission_view USING(permission_id);
598 DROP TRIGGER IF EXISTS app_permission_view_insert_trigger;
599 CREATE TRIGGER app_permission_view_insert_trigger
600 INSTEAD OF INSERT ON app_permission_view
602 INSERT OR IGNORE INTO app_permission(app_id, permission_id, is_volatile, is_enabled)
604 permission_view.permission_id,
608 WHERE permission_view.name = NEW.name AND
609 permission_view.type_name = NEW.type_name;
615 -- It's forbidden do change permission from not volatile to volatile.
616 -- We have to check it before inserting anything.
617 -- Used in updating permissions
618 DROP TRIGGER IF EXISTS app_permission_view_update_trigger;
619 CREATE TRIGGER app_permission_view_update_trigger
620 INSTEAD OF UPDATE ON app_permission_view
622 UPDATE OR IGNORE app_permission
623 SET is_enabled = NEW.is_enabled
624 WHERE app_permission.app_id = OLD.app_id AND
625 app_permission.permission_id
626 IN (SELECT permission_view.permission_id
628 WHERE permission_view.name = OLD.name AND
629 permission_view.type_name = OLD.type_name
634 DROP TRIGGER IF EXISTS app_permission_view_delete_trigger;
635 CREATE TRIGGER app_permission_view_delete_trigger
636 INSTEAD OF DELETE ON app_permission_view
638 DELETE FROM app_permission
639 WHERE app_permission.app_id
640 IN (SELECT application_view.app_id
641 FROM application_view
642 WHERE application_view.name = OLD.app_name
645 app_permission.permission_id NOT IN (SELECT permission_view.permission_id
647 WHERE permission_view.name = "ALL_APPS" AND
648 permission_view.type_name = "ALL_APPS");
650 DELETE FROM path_view
651 WHERE path_view.owner_app_label_name=OLD.app_name;
655 -- APP PERMISSION VOLATILE VIEW ------------------------------------------------
656 DROP VIEW IF EXISTS app_permission_volatile_view;
657 CREATE VIEW app_permission_volatile_view AS
659 FROM app_permission_view
660 WHERE app_permission_view.is_volatile = 1;
663 DROP TRIGGER IF EXISTS app_permission_volatile_view_delete_trigger;
664 CREATE TRIGGER app_permission_volatile_view_delete_trigger
665 INSTEAD OF DELETE ON app_permission_volatile_view
667 DELETE FROM app_permission
668 WHERE app_permission.is_volatile = 1 AND
669 app_permission.app_id
670 IN (SELECT application_view.app_id
671 FROM application_view
672 WHERE application_view.name = OLD.app_name
677 -- APPLICATIONS PERMISSIONS ID -------------------------------------------------
678 -- All applications and their permissions
679 DROP VIEW IF EXISTS app_label_with_permission_view;
680 CREATE VIEW app_label_with_permission_view AS
681 SELECT app_permission.permission_id,
682 application_view.name,
683 application_view.app_id,
684 app_permission.is_volatile
686 INNER JOIN application_view USING(app_id)
687 WHERE app_permission.is_enabled = 1;
691 -- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
692 -- ltl = label to label
693 DROP VIEW IF EXISTS ltl_permission_permission_rule_view;
694 CREATE VIEW ltl_permission_permission_rule_view AS
695 SELECT app1.name AS subject,
698 app1.is_volatile OR app2.is_volatile AS is_volatile
699 FROM permission_permission_rule AS p
700 INNER JOIN app_label_with_permission_view AS app1 USING(permission_id)
701 INNER JOIN app_label_with_permission_view AS app2
702 ON app2.permission_id = p.target_permission_id
703 WHERE is_reverse = 0 AND app1.app_id != app2.app_id
705 SELECT app2.name AS subject,
708 app1.is_volatile OR app2.is_volatile AS is_volatile
709 FROM permission_permission_rule AS p
710 INNER JOIN app_label_with_permission_view AS app1 USING(permission_id)
711 INNER JOIN app_label_with_permission_view AS app2
712 ON app2.permission_id = p.target_permission_id
713 WHERE is_reverse = 1 AND app1.app_id != app2.app_id;
715 -- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
716 -- ltl = label to label
717 DROP VIEW IF EXISTS ltl_permission_label_rule_view;
718 CREATE VIEW ltl_permission_label_rule_view AS
719 SELECT app.name AS subject,
720 label.name AS object,
723 FROM permission_label_rule AS p
724 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
725 INNER JOIN label USING(label_id)
726 WHERE is_reverse = 0 AND app.name != label.name
728 SELECT label.name AS subject,
732 FROM permission_label_rule AS p
733 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
734 INNER JOIN label USING(label_id)
735 WHERE is_reverse = 1 AND app.name != label.name;
740 -- PERMISSION TO PATH TYPE RULE VIEW -------------------------------------------
741 -- ltl = label to label
742 DROP VIEW IF EXISTS ltl_permission_app_path_type_rule_view;
743 CREATE VIEW ltl_permission_app_path_type_rule_view AS
744 SELECT app.name AS subject,
745 label.name AS object,
748 FROM permission_app_path_type_rule AS p
749 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
750 INNER JOIN app_path USING(app_path_type_id)
751 INNER JOIN label USING(label_id)
752 WHERE is_reverse = 0 AND app.name != label.name
754 SELECT label.name AS subject,
758 FROM permission_app_path_type_rule AS p
759 INNER JOIN app_label_with_permission_view AS app USING(permission_id)
760 INNER JOIN app_path USING(app_path_type_id)
761 INNER JOIN label USING(label_id)
762 WHERE is_reverse = 1 AND app.name != label.name;
765 -- PERMISSION TO APPLICATION'S OWN PATHS ---------------------------------------
766 -- ltl = label to label
767 DROP VIEW IF EXISTS ltl_app_path_view;
768 CREATE VIEW ltl_app_path_view AS
769 SELECT application_view.name AS subject,
770 label.name AS object,
771 app_path.access AS access
773 INNER JOIN application_view USING(app_id)
774 INNER JOIN label USING(label_id);
777 -- PERMISSION FROM PATHS TO APPLICATIONS ---------------------------------------
778 -- ltl = label to label
779 DROP VIEW IF EXISTS ltl_app_path_reverse_view;
780 CREATE VIEW ltl_app_path_reverse_view AS
781 SELECT label.name AS subject,
782 application_view.name AS object,
783 app_path.access_reverse AS access
785 INNER JOIN application_view USING(app_id)
786 INNER JOIN label USING(label_id)
787 WHERE app_path.access_reverse != 0 ;
790 -- SMACK RULES VIEWS -----------------------------------------------------------
791 DROP VIEW IF EXISTS all_smack_binary_rules_view;
792 CREATE VIEW all_smack_binary_rules_view AS
797 FROM (SELECT subject, object, access, is_volatile
798 FROM ltl_permission_permission_rule_view
800 SELECT subject, object, access, is_volatile
801 FROM ltl_permission_label_rule_view
803 SELECT subject, object, access, is_volatile
804 FROM ltl_permission_app_path_type_rule_view
806 SELECT subject, object, access, 0
807 FROM ltl_app_path_view
809 SELECT subject, object, access, 0
810 FROM ltl_app_path_reverse_view
813 -- ALL INSERTED DATA VIEW ------------------------------------------------------
814 -- This view is used to clear the database from inserted rules.
815 -- We loose all information about installed applications
817 DROP VIEW IF EXISTS all_inserted_data;
818 CREATE VIEW all_inserted_data AS
822 DROP TRIGGER IF EXISTS all_inserted_data_delete_trigger;
823 CREATE TRIGGER all_inserted_data_delete_trigger INSTEAD OF
824 DELETE ON all_inserted_data
826 DELETE FROM permission_label_rule;
827 DELETE FROM permission_permission_rule;
828 DELETE FROM permission_app_path_type_rule;
830 DELETE FROM app_permission;
832 DELETE FROM permission;
833 DELETE FROM permission_type;
835 DELETE FROM app_path;
836 DELETE FROM app_path_type;
844 -- SMACK RULES MODIFICATIONS VIEW ----------------------------------------------
845 -- This definition is repeated during opening a connection with the database.
846 -- Used to get all smack rules, even volatile.
847 -- Ensure it's the same!
848 CREATE TEMPORARY VIEW modified_smack_rules AS
849 SELECT subject, object,
850 access_to_str(access_add) AS access_add,
851 access_to_str(access_del) AS access_del
853 SELECT subject, object,
854 s1.access & ~s2.access AS access_add,
855 s2.access & ~s1.access AS access_del
856 FROM current_smack_rule_modified AS s1
857 INNER JOIN history_smack_rule_modified AS s2
858 USING (subject, object)
859 WHERE s1.access != s2.access
863 SELECT subject, object,
864 s1.access AS access_add,
866 FROM current_smack_rule_modified AS s1
867 LEFT JOIN history_smack_rule_modified s2
868 USING (subject, object)
869 WHERE s2.subject IS NULL AND
874 SELECT subject, object,
876 s1.access AS access_del
877 FROM history_smack_rule_modified s1
878 LEFT JOIN current_smack_rule_modified AS s2
879 USING (subject, object)
880 WHERE s2.subject IS NULL AND
883 ORDER BY subject, object ASC;