2 -- 1. Beware of updating schema!
3 -- We can drop views and triggers,
4 -- but we should copy data from tables
5 -- according to the schema version!
6 -- 2. If you change definition of tables
7 -- update the schema counter at the bottom!!
9 -- TODO: Use "USING" in joins whenever possible
11 .load librules-db-sql-udf.so
12 PRAGMA foreign_keys = ON;
14 BEGIN EXCLUSIVE TRANSACTION;
16 -- PRAGMA cache_size = 2000;
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 app_path_type_id INTEGER NOT NULL ,
48 -- Desired behavior should be:
49 -- allow one app to register a path only once (already implemented by the primary key)
50 -- prohibit two apps registering the same path with different labels (probably cannot be done by SQL constraints)
51 -- allow two apps to register the same path if label is also same
53 PRIMARY KEY (app_id, path),
55 FOREIGN KEY(app_id) REFERENCES app(app_id),
56 FOREIGN KEY(label_id) REFERENCES label(label_id),
57 FOREIGN KEY(app_path_type_id) REFERENCES app_path_type(app_path_type_id)
60 CREATE TABLE IF NOT EXISTS app_path_type (
61 app_path_type_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
66 -- CREATE INDEX IF NOT EXISTS app_path_type_index ON app_path_type(app_path_type_id, name);
69 CREATE TABLE IF NOT EXISTS permission_permission_rule (
70 permission_id INTEGER NOT NULL,
71 target_permission_id INTEGER NOT NULL,
72 access INTEGER NOT NULL DEFAULT 0,
73 is_reverse INTEGER NOT NULL DEFAULT 0,
75 PRIMARY KEY (permission_id, target_permission_id, is_reverse),
77 FOREIGN KEY(permission_id) REFERENCES permission(permission_id),
78 FOREIGN KEY(target_permission_id) REFERENCES permission(permission_id)
81 CREATE TABLE IF NOT EXISTS permission_label_rule (
82 permission_id INTEGER NOT NULL,
83 label_id INTEGER NOT NULL,
84 access INTEGER NOT NULL DEFAULT 0,
85 is_reverse INTEGER NOT NULL DEFAULT 0,
87 PRIMARY KEY (permission_id,label_id, is_reverse),
89 FOREIGN KEY(permission_id) REFERENCES permission(permission_id),
90 FOREIGN KEY(label_id) REFERENCES label(label_id)
93 CREATE TABLE IF NOT EXISTS permission_app_path_type_rule (
94 permission_id INTEGER NOT NULL,
95 app_path_type_id INTEGER NOT NULL,
96 access INTEGER NOT NULL DEFAULT 0,
97 is_reverse INTEGER NOT NULL DEFAULT 0,
99 PRIMARY KEY (permission_id, app_path_type_id, is_reverse),
101 FOREIGN KEY(permission_id) REFERENCES permission(permission_id),
102 FOREIGN KEY(app_path_type_id) REFERENCES app_path_type(app_path_type_id)
106 CREATE TABLE IF NOT EXISTS label (
107 label_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
113 CREATE TABLE IF NOT EXISTS permission_type (
114 permission_type_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
115 type_name TEXT NOT NULL,
120 CREATE TABLE IF NOT EXISTS permission (
121 permission_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
122 permission_type_id INTEGER NOT NULL,
125 UNIQUE (name, permission_type_id),
127 FOREIGN KEY(permission_type_id) REFERENCES permission_type(permission_type_id)
131 CREATE TABLE IF NOT EXISTS all_smack_binary_rules(
132 subject TEXT NOT NULL,
133 object TEXT NOT NULL,
134 access INTEGER NOT NULL,
135 is_volatile INTEGER NOT NULL
138 -- TEMPORARY TABLES ------------------------------------------------------------
139 -- Definitions are repeated in code.
141 CREATE TEMPORARY TABLE history_smack_rule(
142 subject TEXT NOT NULL,
143 object TEXT NOT NULL,
144 access INTEGER NOT NULL
148 CREATE TEMPORARY TABLE modified_label(
154 CREATE TEMPORARY TABLE all_smack_binary_rules_modified(
155 subject TEXT NOT NULL,
156 object TEXT NOT NULL,
157 access INTEGER NOT NULL
161 CREATE TEMPORARY TABLE history_smack_rule_modified(
162 subject TEXT NOT NULL,
163 object TEXT NOT NULL,
164 access INTEGER NOT NULL
168 -- PERMISSION VIEW -------------------------------------------------------------
169 DROP VIEW IF EXISTS permission_view;
170 CREATE VIEW permission_view AS
171 SELECT permission.permission_id, permission.name, permission_type.type_name
173 INNER JOIN permission_type
174 ON permission.permission_type_id = permission_type.permission_type_id;
176 DROP TRIGGER IF EXISTS permission_view_insert_trigger;
177 CREATE TRIGGER permission_view_insert_trigger
178 INSTEAD OF INSERT ON permission_view
180 INSERT OR IGNORE INTO permission(name,permission_type_id)
181 SELECT NEW.name, permission_type.permission_type_id
183 WHERE permission_type.type_name = NEW.type_name;
186 -- Once the files provide other types of rules,
187 -- this trigger will have to delete also from permission_path_type_rule
188 -- and maybe permission_permission_rule
189 DELETE FROM permission_label_rule_view
190 WHERE permission_label_rule_view.permission_name = NEW.name AND
191 permission_label_rule_view.permission_type_name = NEW.type_name;
194 -- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
195 DROP VIEW IF EXISTS permission_label_rule_view;
196 CREATE VIEW permission_label_rule_view AS
198 permission_view.permission_id AS permission_id,
199 permission_view.name AS permission_name,
200 permission_view.type_name AS permission_type_name,
201 label.name AS label_name,
202 permission_label_rule.access AS access,
203 permission_label_rule.is_reverse AS is_reverse
204 FROM permission_label_rule
205 LEFT JOIN permission_view
206 ON permission_label_rule.permission_id = permission_view.permission_id
209 ON permission_label_rule.label_id = label.label_id;
212 -- Preferred way of adding permission rules would be to use these ONE, multi-row
213 -- insert statement, with one check of a condition
214 -- that there is such permission id. It's impossible to make those inserts in C,
215 -- so the programmer has to secure, that there is a permission with a given id.
216 -- (Check it and insert in the same transaction)
217 -- In runtime we accept ONLY inserts with label.
218 -- All other kinds of permissions are filled during the database creation.
219 DROP TRIGGER IF EXISTS permission_label_rule_view_insert_trigger;
220 CREATE TRIGGER permission_label_rule_view_insert_trigger
221 INSTEAD OF INSERT ON permission_label_rule_view
223 -- Adding api features adds a label it it's not present.
224 INSERT OR IGNORE INTO label(name) VALUES (NEW.label_name);
226 INSERT INTO permission_label_rule(permission_id, label_id, access, is_reverse)
227 SELECT NEW.permission_id,
229 str_to_access(NEW.access),
232 WHERE label.name = NEW.label_name;
236 -- TODO: Potential problem - undeleted labels.
237 DROP TRIGGER IF EXISTS permission_label_rule_view_delete_trigger;
238 CREATE TRIGGER permission_label_rule_view_delete_trigger
239 INSTEAD OF DELETE ON permission_label_rule_view
241 DELETE FROM permission_label_rule
242 WHERE permission_label_rule.permission_id IN (SELECT permission_view.permission_id
244 WHERE permission_view.name = OLD.permission_name AND
245 permission_view.type_name = OLD.permission_type_name);
249 -- PERMISSION TO APP PATH TYPE RULE VIEW ---------------------------------------
250 DROP VIEW IF EXISTS permission_app_path_type_rule_view;
251 CREATE VIEW permission_app_path_type_rule_view AS
253 permission_view.permission_id AS permission_id,
254 permission_view.name AS permission_name,
255 permission_view.type_name AS permission_type_name,
256 app_path_type.name AS app_path_type_name,
257 permission_app_path_type_rule.access AS access,
258 permission_app_path_type_rule.is_reverse AS is_reverse
259 FROM permission_app_path_type_rule
260 LEFT JOIN permission_view
261 ON permission_app_path_type_rule.permission_id = permission_view.permission_id
262 LEFT JOIN app_path_type
263 ON permission_app_path_type_rule.app_path_type_id = app_path_type.app_path_type_id;
265 DROP TRIGGER IF EXISTS permission_app_path_type_rule_view_insert_trigger;
266 CREATE TRIGGER permission_app_path_type_rule_view_insert_trigger
268 ON permission_app_path_type_rule_view
270 INSERT INTO permission_app_path_type_rule(permission_id,
274 SELECT permission_view.permission_id,
275 app_path_type.app_path_type_id,
276 str_to_access(NEW.access),
278 FROM permission_view, app_path_type
279 WHERE permission_view.name = NEW.permission_name AND
280 permission_view.type_name = NEW.permission_type_name AND
281 app_path_type.name = NEW.app_path_type_name;
285 -- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
286 DROP VIEW IF EXISTS permission_permission_rule_view;
287 CREATE VIEW permission_permission_rule_view AS
289 tmp_permission_view.permission_id AS permission_id,
290 tmp_permission_view.name AS permission_name,
291 tmp_permission_view.type_name AS permission_type_name,
292 tmp_target_permission_view.name AS target_permission_name,
293 tmp_target_permission_view.type_name AS target_permission_type_name,
294 permission_permission_rule.access AS access,
295 permission_permission_rule.is_reverse AS is_reverse
296 FROM permission_permission_rule
297 LEFT JOIN permission_view AS tmp_permission_view
298 ON permission_permission_rule.permission_id = tmp_permission_view.permission_id
299 LEFT JOIN permission_view AS tmp_target_permission_view
300 ON permission_permission_rule.target_permission_id = tmp_target_permission_view.permission_id;
303 -- Trigger for manual addition of rules.
304 DROP TRIGGER IF EXISTS permission_permission_rule_view_insert_trigger;
305 CREATE TRIGGER permission_permission_rule_view_insert_trigger
306 INSTEAD OF INSERT ON permission_permission_rule_view
308 INSERT INTO permission_permission_rule(permission_id,
309 target_permission_id,
312 SELECT tmp_permission_view.permission_id,
313 tmp_target_permission_view.permission_id,
314 str_to_access(NEW.access),
316 FROM permission_view AS tmp_permission_view,
317 permission_view AS tmp_target_permission_view
318 WHERE tmp_permission_view.name = NEW.permission_name AND
319 tmp_permission_view.type_name = NEW.permission_type_name AND
320 tmp_target_permission_view.name = NEW.target_permission_name AND
321 tmp_target_permission_view.type_name = NEW.target_permission_type_name;
326 -- LABEL VIEW ------------------------------------------------------------------
327 -- There are no INSTEAD OF triggers on regular tables.
328 -- We use a view to delete unreferenced labels:
329 DROP VIEW IF EXISTS label_view;
330 CREATE VIEW label_view AS SELECT * FROM label;
332 DROP TRIGGER IF EXISTS label_view_delete_trigger;
333 CREATE TRIGGER label_view_delete_trigger
334 INSTEAD OF DELETE ON label_view
335 WHEN OLD.label_id NOT IN (SELECT app.label_id
337 OLD.label_id NOT IN (SELECT permission_label_rule.label_id
338 FROM permission_label_rule) AND
339 OLD.label_id NOT IN (SELECT app_path.label_id
342 DELETE FROM label WHERE label.name = OLD.name;
346 -- APPLICATION VIEW ------------------------------------------------------------
347 DROP VIEW IF EXISTS application_view;
348 CREATE VIEW application_view AS
349 SELECT app.app_id, label.name
352 ON label.label_id = app.label_id ;
354 DROP TRIGGER IF EXISTS application_view_insert_trigger;
355 CREATE TRIGGER application_view_insert_trigger
356 INSTEAD OF INSERT ON application_view
358 INSERT OR IGNORE INTO label(name) VALUES (NEW.name);
359 INSERT INTO app(label_id) SELECT label_id FROM label WHERE label.name = NEW.name;
360 INSERT INTO app_permission_view(app_id,
365 VALUES(last_insert_rowid(),
372 DROP TRIGGER IF EXISTS application_view_delete_trigger;
373 CREATE TRIGGER application_view_delete_trigger
374 INSTEAD OF DELETE ON application_view
376 -- Delete rules that correspond to app's paths:
377 DELETE FROM permission_label_rule
378 WHERE permission_label_rule.label_id IN
379 (SELECT app_path.label_id
381 INNER JOIN application_view
382 ON app_path.app_id = application_view.app_id
383 WHERE application_view.name = OLD.name);
386 DELETE FROM path_view
387 WHERE path_view.owner_app_label_name=OLD.name;
389 -- Delete apps permissions:
390 DELETE FROM app_permission
391 WHERE app_permission.app_id
392 IN (SELECT application_view.app_id
393 FROM application_view
394 WHERE application_view.name = OLD.name
397 -- Delete application
399 WHERE app.app_id IN (SELECT application_view.app_id
400 FROM application_view
401 WHERE application_view.name = OLD.name
405 DELETE FROM label_view
406 WHERE label_view.name = OLD.name;
410 -- PATH VIEW -------------------------------------------------------------------
411 DROP VIEW IF EXISTS path_view;
412 CREATE VIEW path_view AS
413 SELECT application_view.name AS owner_app_label_name,
414 app_path.path AS path,
415 label.name AS path_label_name,
416 app_path.access AS access,
417 app_path_type.name AS path_type_name
420 LEFT JOIN app_path_type USING (app_path_type_id)
421 LEFT JOIN application_view USING (app_id)
422 LEFT JOIN label USING (label_id);
425 -- For an existing application we add a path.
426 DROP TRIGGER IF EXISTS path_view_insert_trigger;
427 CREATE TRIGGER path_view_insert_trigger
428 INSTEAD OF INSERT ON path_view
429 WHEN NEW.owner_app_label_name IN (SELECT application_view.name
430 FROM application_view)
432 INSERT OR IGNORE INTO label(name) VALUES (NEW.path_label_name);
434 INSERT INTO app_path(app_id, path, label_id, access, app_path_type_id)
435 SELECT application_view.app_id,
438 str_to_access(NEW.access),
439 app_path_type.app_path_type_id
441 FROM application_view, app_path_type, label
442 WHERE application_view.name = NEW.owner_app_label_name AND
443 app_path_type.name = NEW.path_type_name AND
444 label.name = NEW.path_label_name;
448 DROP TRIGGER IF EXISTS path_view_delete_trigger;
449 CREATE TRIGGER path_view_delete_trigger
450 INSTEAD OF DELETE ON path_view
453 WHERE app_path.app_id IN (SELECT app.app_id
455 WHERE label.name = OLD.owner_app_label_name AND
456 app.label_id = label.label_id);
458 DELETE FROM label_view WHERE label_view.name = OLD.path_label_name;
461 -- APP PERMISSION LIST VIEW ----------------------------------------------------
462 -- Used in check_app_permission_internal to check if permissions are present
463 -- TODO: Check if SQLite optimizer doesn't change app_permission_view to the same code.
464 DROP VIEW IF EXISTS app_permission_list_view;
465 CREATE VIEW app_permission_list_view AS
466 SELECT app_permission.app_id AS app_id,
467 app_permission.permission_id AS permission_id,
468 permission_view.name AS permission_name,
469 permission_view.type_name AS permission_type_name,
470 app_permission.is_volatile AS is_volatile,
471 app_permission.is_enabled AS is_enabled
473 INNER JOIN permission_view
474 ON permission_view.permission_id = app_permission.permission_id;
478 -- APP PERMISSION VIEW ---------------------------------------------------------
479 DROP VIEW IF EXISTS app_permission_view;
480 CREATE VIEW app_permission_view AS
481 SELECT application_view.app_id,
482 application_view.name AS app_name,
483 permission_view.permission_id,
484 permission_view.name,
485 permission_view.type_name,
486 app_permission.is_volatile,
487 app_permission.is_enabled
489 INNER JOIN application_view
490 ON application_view.app_id = app_permission.app_id
491 INNER JOIN permission_view
492 ON permission_view.permission_id = app_permission.permission_id;
495 DROP TRIGGER IF EXISTS app_permission_view_insert_trigger;
496 CREATE TRIGGER app_permission_view_insert_trigger
497 INSTEAD OF INSERT ON app_permission_view
499 INSERT INTO app_permission(app_id, permission_id, is_volatile, is_enabled)
501 permission_view.permission_id,
505 WHERE permission_view.name = NEW.name AND
506 permission_view.type_name = NEW.type_name;
512 -- It's forbidden do change permission from not volatile to volatile.
513 -- We have to check it before inserting anything.
514 -- Used in updating permissions
515 DROP TRIGGER IF EXISTS app_permission_view_update_trigger;
516 CREATE TRIGGER app_permission_view_update_trigger
517 INSTEAD OF UPDATE ON app_permission_view
519 UPDATE OR IGNORE app_permission
520 SET is_enabled = NEW.is_enabled
521 WHERE app_permission.app_id = OLD.app_id AND
522 app_permission.permission_id
523 IN (SELECT permission_view.permission_id
525 WHERE permission_view.name = OLD.name AND
526 permission_view.type_name = OLD.type_name
531 DROP TRIGGER IF EXISTS app_permission_view_delete_trigger;
532 CREATE TRIGGER app_permission_view_delete_trigger
533 INSTEAD OF DELETE ON app_permission_view
535 DELETE FROM app_permission
536 WHERE app_permission.app_id
537 IN (SELECT application_view.app_id
538 FROM application_view
539 WHERE application_view.name = OLD.app_name
542 app_permission.permission_id NOT IN (SELECT permission_view.permission_id
544 WHERE permission_view.name = "ALL_APPS" AND
545 permission_view.type_name = "ALL_APPS");
548 -- APP PERMISSION VOLATILE VIEW ------------------------------------------------
549 DROP VIEW IF EXISTS app_permission_volatile_view;
550 CREATE VIEW app_permission_volatile_view AS
552 FROM app_permission_view
553 WHERE app_permission_view.is_volatile = 1;
556 DROP TRIGGER IF EXISTS app_permission_volatile_view_delete_trigger;
557 CREATE TRIGGER app_permission_volatile_view_delete_trigger
558 INSTEAD OF DELETE ON app_permission_volatile_view
560 DELETE FROM app_permission
561 WHERE app_permission.is_volatile = 1 AND
562 app_permission.app_id
563 IN (SELECT application_view.app_id
564 FROM application_view
565 WHERE application_view.name = OLD.app_name
570 -- APPLICATIONS PERMISSIONS ID -------------------------------------------------
571 -- All applications and their permissions
572 DROP VIEW IF EXISTS app_label_with_permission_view;
573 CREATE VIEW app_label_with_permission_view AS
574 SELECT app_permission.permission_id,
575 application_view.name,
576 application_view.app_id,
577 app_permission.is_volatile
579 INNER JOIN application_view
580 ON application_view.app_id = app_permission.app_id AND
581 app_permission.is_enabled = 1;
585 -- PERMISSION TO PERMISSION RULE VIEW ------------------------------------------
586 -- ltl = label to label
587 DROP VIEW IF EXISTS ltl_permission_permission_rule_view;
588 CREATE VIEW ltl_permission_permission_rule_view AS
589 SELECT (CASE WHEN is_reverse = 0 THEN app1.name ELSE app2.name END) AS subject,
590 (CASE WHEN is_reverse = 1 THEN app1.name ELSE app2.name END) AS object,
592 app1.is_volatile OR app2.is_volatile AS is_volatile
593 FROM permission_permission_rule AS p
594 INNER JOIN app_label_with_permission_view AS app1
595 ON app1.permission_id = p.permission_id
596 INNER JOIN app_label_with_permission_view AS app2
597 ON app2.permission_id = p.target_permission_id AND
598 app1.app_id != app2.app_id;
600 -- PERMISSION TO LABEL RULE VIEW -----------------------------------------------
601 -- ltl = label to label
602 DROP VIEW IF EXISTS ltl_permission_label_rule_view;
603 CREATE VIEW ltl_permission_label_rule_view AS
604 SELECT (CASE WHEN is_reverse = 0 THEN app.name ELSE label.name END) AS subject,
605 (CASE WHEN is_reverse = 1 THEN app.name ELSE label.name END) AS object,
608 FROM permission_label_rule AS p
609 INNER JOIN app_label_with_permission_view AS app
610 ON app.permission_id = p.permission_id
612 ON label.label_id = p.label_id AND
613 app.name != label.name;
617 -- PERMISSION TO PATH TYPE RULE VIEW -------------------------------------------
618 -- ltl = label to label
619 DROP VIEW IF EXISTS ltl_permission_app_path_type_rule_view;
620 CREATE VIEW ltl_permission_app_path_type_rule_view AS
621 SELECT (CASE WHEN is_reverse = 0 THEN app.name ELSE label.name END) AS subject,
622 (CASE WHEN is_reverse = 1 THEN app.name ELSE label.name END) AS object,
625 FROM permission_app_path_type_rule AS p
626 INNER JOIN app_label_with_permission_view AS app
627 ON app.permission_id = p.permission_id
629 ON app_path.app_path_type_id = p.app_path_type_id
631 ON label.label_id = app_path.label_id AND
632 app.name != label.name;
635 -- PERMISSION TO APPLICATION'S OWN PATHS ---------------------------------------
636 -- ltl = label to label
637 DROP VIEW IF EXISTS ltl_app_path_view;
638 CREATE VIEW ltl_app_path_view AS
639 SELECT application_view.name AS subject,
640 label.name AS object,
641 app_path.access AS access
643 INNER JOIN application_view ON application_view.app_id = app_path.app_id
644 INNER JOIN label ON label.label_id = app_path.label_id;
647 -- SMACK RULES VIEWS -----------------------------------------------------------
648 DROP VIEW IF EXISTS all_smack_binary_rules_view;
649 CREATE VIEW all_smack_binary_rules_view AS
652 bitwise_or(access) AS access,
653 MIN(is_volatile) AS is_volatile
654 FROM (SELECT subject, object, access, is_volatile
655 FROM ltl_permission_permission_rule_view
657 SELECT subject, object, access, is_volatile
658 FROM ltl_permission_label_rule_view
660 SELECT subject, object, access, is_volatile
661 FROM ltl_permission_app_path_type_rule_view
663 SELECT subject, object, access, 0
664 FROM ltl_app_path_view
666 GROUP BY subject, object
667 ORDER BY subject, object ASC;
669 -- ALL INSERTED DATA VIEW ------------------------------------------------------
670 -- This view is used to clear the database from inserted rules.
671 -- We loose all information about installed applications
673 DROP VIEW IF EXISTS all_inserted_data;
674 CREATE VIEW all_inserted_data AS
678 DROP TRIGGER IF EXISTS all_inserted_data_delete_trigger;
679 CREATE TRIGGER all_inserted_data_delete_trigger INSTEAD OF
680 DELETE ON all_inserted_data
682 DELETE FROM permission_label_rule;
683 DELETE FROM permission_permission_rule;
684 DELETE FROM permission_app_path_type_rule;
686 DELETE FROM app_permission;
688 DELETE FROM permission;
689 DELETE FROM permission_type;
691 DELETE FROM app_path;
692 DELETE FROM app_path_type;
700 -- SMACK RULES MODIFICATIONS VIEW ----------------------------------------------
701 -- This definition is repeated during opening a connection with the database.
702 -- Used to get all smack rules, even volatile.
703 -- Ensure it's the same!
704 CREATE TEMPORARY VIEW modified_smack_rules AS
705 SELECT subject, object,
706 access_to_str(access_add) AS access_add,
707 access_to_str(access_del) AS access_del
709 SELECT subject, object,
710 s1.access & ~s2.access AS access_add,
711 s2.access & ~s1.access AS access_del
712 FROM all_smack_binary_rules AS s1
713 INNER JOIN history_smack_rule AS s2
714 USING (subject, object)
715 WHERE s1.access != s2.access
719 SELECT subject, object,
720 s1.access AS access_add,
722 FROM all_smack_binary_rules AS s1
723 LEFT JOIN history_smack_rule s2
724 USING (subject, object)
725 WHERE s2.subject IS NULL AND
730 SELECT subject, object,
732 s1.access AS access_del
733 FROM history_smack_rule s1
734 LEFT JOIN all_smack_binary_rules AS s2
735 USING (subject, object)
736 WHERE s2.subject IS NULL AND
739 ORDER BY subject, object ASC;
743 PRAGMA schema_version = 1;