syspolicy.sql
1 -- 2 -- Copyright (c) 2011-2012 Apple Inc. All Rights Reserved. 3 -- 4 -- @APPLE_LICENSE_HEADER_START@ 5 -- 6 -- This file contains Original Code and/or Modifications of Original Code 7 -- as defined in and that are subject to the Apple Public Source License 8 -- Version 2.0 (the 'License'). You may not use this file except in 9 -- compliance with the License. Please obtain a copy of the License at 10 -- http://www.opensource.apple.com/apsl/ and read it before using this 11 -- file. 12 -- 13 -- The Original Code and all software distributed under the License are 14 -- distributed on an 'AS IS' basis, WITHOUT WARRANTY OF ANY KIND, EITHER 15 -- EXPRESS OR IMPLIED, AND APPLE HEREBY DISCLAIMS ALL SUCH WARRANTIES, 16 -- INCLUDING WITHOUT LIMITATION, ANY WARRANTIES OF MERCHANTABILITY, 17 -- FITNESS FOR A PARTICULAR PURPOSE, QUIET ENJOYMENT OR NON-INFRINGEMENT. 18 -- Please see the License for the specific language governing rights and 19 -- limitations under the License. 20 -- 21 -- @APPLE_LICENSE_HEADER_END@ 22 -- 23 -- 24 -- System Policy master database - file format and initial contents 25 -- 26 -- This is currently for sqlite3 27 -- 28 -- NOTES: 29 -- Dates are uniformly in julian form. We use 5000000 as the canonical "never" expiration 30 -- value; that's a day in the year 8977. 31 -- 32 PRAGMA user_version = 1; 33 PRAGMA foreign_keys = true; 34 PRAGMA legacy_file_format = false; 35 PRAGMA recursive_triggers = true; 36 37 38 -- 39 -- The feature table hold configuration features and options 40 -- 41 CREATE TABLE feature ( 42 id INTEGER PRIMARY KEY, -- canononical 43 name TEXT NOT NULL UNIQUE, -- name of option 44 value TEXT NULL, -- value of option, if any 45 remarks TEXT NULL -- optional remarks string 46 ); 47 48 49 -- 50 -- The primary authority. This table is conceptually scanned 51 -- in priority order, with the highest-priority matching enabled record 52 -- determining the outcome. 53 -- 54 CREATE TABLE authority ( 55 id INTEGER PRIMARY KEY AUTOINCREMENT, -- canonical 56 version INTEGER NOT NULL DEFAULT (1) -- semantic version of this rule 57 CHECK (version > 0), 58 type INTEGER NOT NULL, -- operation type 59 requirement TEXT NULL -- code requirement 60 CHECK ((requirement IS NULL) = ((flags & 1) != 0)), 61 allow INTEGER NOT NULL DEFAULT (1) -- allow (1) or deny (0) 62 CHECK (allow = 0 OR allow = 1), 63 disabled INTEGER NOT NULL DEFAULT (0) -- disable count (stacks; enabled if zero) 64 CHECK (disabled >= 0), 65 expires FLOAT NOT NULL DEFAULT (5000000), -- expiration of rule authority (Julian date) 66 priority REAL NOT NULL DEFAULT (0), -- rule priority (full float) 67 label TEXT NULL, -- text label for authority rule 68 filter_unsigned TEXT NULL, -- prescreen for handling unsigned code 69 flags INTEGER NOT NULL DEFAULT (0), -- amalgamated binary flags 70 -- following fields are for documentation only 71 ctime FLOAT NOT NULL DEFAULT (JULIANDAY('now')), -- rule creation time (Julian) 72 mtime FLOAT NOT NULL DEFAULT (JULIANDAY('now')), -- time rule was last changed (Julian) 73 user TEXT NULL, -- user requesting this rule (NULL if unknown) 74 remarks TEXT NULL -- optional remarks string 75 ); 76 77 -- index 78 CREATE INDEX authority_type ON authority (type); 79 CREATE INDEX authority_priority ON authority (priority); 80 CREATE INDEX authority_expires ON authority (expires); 81 82 -- update mtime if a record is changed 83 CREATE TRIGGER authority_update AFTER UPDATE ON authority 84 BEGIN 85 UPDATE authority SET mtime = JULIANDAY('now') WHERE id = old.id; 86 END; 87 88 -- rules that are actively considered 89 CREATE VIEW active_authority AS 90 SELECT * from authority 91 WHERE disabled = 0 AND JULIANDAY('now') < expires AND (flags & 1) = 0; 92 93 -- rules subject to priority scan: active_authority but including disabled rules 94 CREATE VIEW scan_authority AS 95 SELECT * from authority 96 WHERE JULIANDAY('now') < expires AND (flags & 1) = 0; 97 98 99 -- 100 -- A table to carry (potentially large-ish) filesystem data stored as a bookmark blob. 101 -- 102 CREATE TABLE bookmarkhints ( 103 id INTEGER PRIMARY KEY AUTOINCREMENT, 104 bookmark BLOB NOT NULL, 105 authority INTEGER NOT NULL 106 REFERENCES authority(id) ON DELETE CASCADE 107 ); 108 109 110 -- 111 -- Upgradable features already contained in this baseline. 112 -- See policydatabase.cpp for upgrade code. 113 -- 114 INSERT INTO feature (name, value, remarks) 115 VALUES ('bookmarkhints', 'present', 'builtin'); 116 INSERT INTO feature (name, value, remarks) 117 VALUES ('codesignedpackages', 'present', 'builtin'); 118 INSERT INTO feature (name, value, remarks) 119 VALUES ('filter_unsigned', 'present', 'builtin'); 120 INSERT INTO feature (name, value, remarks) 121 VALUES ('document rules', 'present', 'builtin'); 122 INSERT INTO feature (name, value, remarks) 123 VALUES ('root_only', 'present', 'builtin'); 124 125 126 -- 127 -- Initial canonical contents of a fresh database 128 -- 129 130 -- virtual rule anchoring negative cache entries (no rule found) 131 insert into authority (type, allow, priority, flags, label) 132 values (1, 0, -1.0E100, 1, 'No Matching Rule'); 133 134 -- any "genuine Apple-signed" installers 135 insert into authority (type, allow, priority, flags, label, requirement) 136 values (2, 1, -1, 2, 'Apple Installer', 'anchor apple generic and certificate 1[subject.CN] = "Apple Software Update Certification Authority"'); 137 138 -- Apple code signing 139 insert into authority (type, allow, flags, label, requirement) 140 values (1, 1, 2, 'Apple System', 'anchor apple'); 141 142 -- Mac App Store code signing 143 insert into authority (type, allow, flags, label, requirement) 144 values (1, 1, 2, 'Mac App Store', 'anchor apple generic and certificate leaf[field.1.2.840.113635.100.6.1.9] exists'); 145 146 -- Mac App Store installer signing 147 insert into authority (type, allow, flags, label, requirement) 148 values (2, 1, 2, 'Mac App Store', 'anchor apple generic and certificate leaf[field.1.2.840.113635.100.6.1.10] exists'); 149 150 -- Caspian code and archive signing 151 insert into authority (type, allow, flags, label, requirement) 152 values (1, 1, 2, 'Developer ID', 'anchor apple generic and certificate 1[field.1.2.840.113635.100.6.2.6] exists and certificate leaf[field.1.2.840.113635.100.6.1.13] exists'); 153 insert into authority (type, allow, flags, label, requirement) 154 values (2, 1, 2, 'Developer ID', 'anchor apple generic and certificate 1[field.1.2.840.113635.100.6.2.6] exists and (certificate leaf[field.1.2.840.113635.100.6.1.14] or certificate leaf[field.1.2.840.113635.100.6.1.13])'); 155 156 -- Document signing 157 insert into authority (type, allow, flags, label, requirement) 158 values (3, 1, 2, 'Apple System', 'anchor apple'); 159 insert into authority (type, allow, flags, label, requirement) 160 values (3, 1, 2, 'Developer ID', 'anchor apple generic and certificate 1[field.1.2.840.113635.100.6.2.6] exists and certificate leaf[field.1.2.840.113635.100.6.1.13] exists'); 161 162 163 -- 164 -- The cache table lists previously determined outcomes 165 -- for individual objects (by object hash). Entries come from 166 -- full evaluations of authority records, or by explicitly inserting 167 -- override rules that preempt the normal authority. 168 -- EACH object record must have a parent authority record from which it is derived; 169 -- this may be a normal authority rule or an override rule. If the parent rule is deleted, 170 -- all objects created from it are automatically removed (by sqlite itself). 171 -- 172 CREATE TABLE object ( 173 id INTEGER PRIMARY KEY, -- canonical 174 type INTEGER NOT NULL, -- operation type 175 hash CDHASH NOT NULL, -- canonical hash of object 176 allow INTEGER NOT NULL, -- allow (1) or deny (0) 177 expires FLOAT NOT NULL DEFAULT (5000000), -- expiration of object entry 178 authority INTEGER NOT NULL -- governing authority rule 179 REFERENCES authority(id) ON DELETE CASCADE, 180 -- following fields are for documentation only 181 path TEXT NULL, -- path of object at record creation time 182 ctime FLOAT NOT NULL DEFAULT (JULIANDAY('now')), -- record creation time 183 mtime FLOAT NOT NULL DEFAULT (JULIANDAY('now')), -- record modification time 184 remarks TEXT NULL -- optional remarks string 185 ); 186 187 -- index 188 CREATE INDEX object_type ON object (type); 189 CREATE INDEX object_expires ON object (expires); 190 CREATE UNIQUE INDEX object_hash ON object (hash); 191 192 -- update mtime if a record is changed 193 CREATE TRIGGER object_update AFTER UPDATE ON object 194 BEGIN 195 UPDATE object SET mtime = JULIANDAY('now') WHERE id = old.id; 196 END; 197 198 199 -- 200 -- Some useful views on objects. These are for administration; they are not used by the assessor. 201 -- 202 CREATE VIEW object_state AS 203 SELECT object.id, object.type, object.allow, 204 CASE object.expires WHEN 5000000 THEN NULL ELSE STRFTIME('%Y-%m-%d %H:%M:%f', object.expires, 'localtime') END AS expiration, 205 (object.expires - JULIANDAY('now')) * 86400 as remaining, 206 authority.label, 207 object.authority, 208 object.path, 209 object.ctime, 210 authority.requirement, 211 authority.disabled, 212 object.remarks 213 FROM object, authority 214 WHERE object.authority = authority.id;