F1cddGyU

1/* this is the tool2 admin. Remember to separate the admin user you use
2   for managing the DB from the tool2 admin for handling AC policies
3   Remember also to choose and change the password */
4DROP USER IF EXISTS 'admin'@'%';
5CREATE USER 'admin'@'%' IDENTIFIED BY 'password';
6GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'admin'@'%' WITH GRANT OPTION;
7GRANT REFERENCES ON *.* TO 'admin'@'%';
8GRANT CREATE USER ON *.* TO 'admin'@'%';
9GRANT CREATE VIEW ON *.* TO 'admin'@'%';
10GRANT TRIGGER ON *.* TO 'admin'@'%';
11GRANT CREATE ON *.* TO 'admin'@'%';
12GRANT DROP ON *.* TO 'admin'@'%';
13GRANT DELETE ON *.* TO 'admin'@'%';
14GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'admin'@'%';
15FLUSH PRIVILEGES;
16
17
18/* (drop if exists) create and set new schema */
19DROP SCHEMA IF EXISTS `databaseTool2`;
20CREATE SCHEMA `databaseTool2`;
21USE `databaseTool2`;
22
23/* to allow for triggers */
24SET GLOBAL log_bin_trust_function_creators = 1;
25
26/* remember that MySQL automatically creates indexes for primary keys and unique fields
27   other indexes that may be useful:
28	- RoleTuple table
29		- username
30        - rolename
31        - rolename and role version number
32	- PermissionTuple table
33		- rolename
34        - filename
35        - rolename and filename
36        - filename and file version number
37*/
38
39/* re-create tables */
40CREATE TABLE `users` (
41  `username` varchar(50),
42  `userToken` char(50) NOT NULL UNIQUE,
43  `publicEncryptingKey` varchar(500) NOT NULL,
44  `publicSigningKey` varchar(500) NOT NULL,
45  `statusFlag` char(20) NOT NULL,
46  PRIMARY KEY (`username`)
47) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
48
49CREATE TABLE `roles` (
50  `roleName` varchar(50),
51  `roleToken` char(50) NOT NULL UNIQUE,
52  `publicEncryptingKey` varchar(500) NOT NULL,
53  `publicSigningKey` varchar(500) NOT NULL,
54  `roleVersionNumber` int NOT NULL,
55  `statusFlag` char(20) NOT NULL,
56  PRIMARY KEY (`roleName`)
57) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
58
59CREATE TABLE `files` (
60  `fileName` varchar(50),
61  `fileToken` char(50) NOT NULL UNIQUE,
62  `encryptFileVersionNumber` int NOT NULL,
63  `statusFlag` char(20) NOT NULL,
64  PRIMARY KEY (`fileName`)
65) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
66
67
68
69CREATE TABLE `roleTuples` (
70  `username` varchar(50),
71  `roleName` varchar(50),
72  `roleVersionNumber` int NOT NULL,
73  `encryptedRolePublicEncryptingKey` varchar(3000) NOT NULL,
74  `encryptedRolePrivateEncryptingKey` varchar(3000) NOT NULL,
75  `encryptedRolePublicSigningKey` varchar(3000) NOT NULL,
76  `encryptedRolePrivateSigningKey` varchar(3000) NOT NULL,
77  `signature` char(172) NOT NULL,
78  PRIMARY KEY (`username`,`roleName`),
79  KEY `fk_roleTuples_username_idx` (`username`),
80  KEY `fk_roleTuples_roleName_idx` (`roleName`),
81  CONSTRAINT `fk_roleTuples_username` FOREIGN KEY (`username`) REFERENCES `users` (`username`) ON DELETE RESTRICT ON UPDATE RESTRICT,
82  CONSTRAINT `fk_roleTuples_roleName` FOREIGN KEY (`roleName`) REFERENCES `roles` (`rolename`)
83) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
84
85
86CREATE TABLE `permissionTuples` (
87  `roleName` varchar(50),
88  `fileName` varchar(50),
89  `symmetricFileKeyVersionNumber` int NOT NULL,
90  `roleToken`char(50),
91  `fileToken` char(50),
92  `roleVersionNumber` int NOT NULL,
93  `encryptedSymmetricFileKey` varchar(3000) NOT NULL,
94  `permission` varchar(50) NOT NULL,
95  `userToken` char(50) NOT NULL,
96  `signature` char(172) NOT NULL,
97  PRIMARY KEY (`roleName`,`fileName`, `symmetricFileKeyVersionNumber`),
98  KEY `fk_permissionTuples_roleName_idx` (`roleName`),
99  KEY `fk_permissionTuples_fileName_idx` (`fileName`),
100  KEY `fk_permissionTuples_roleToken_idx` (`roleToken`),
101  KEY `fk_permissionTuples_fileToken_idx` (`fileToken`),
102  KEY `fk_permissionTuples_userToken_idx` (`userToken`),
103  CONSTRAINT `fk_permissionTuples_roleName`  FOREIGN KEY (`roleName`)  REFERENCES `roles` (`rolename`),
104  CONSTRAINT `fk_permissionTuples_fileName`  FOREIGN KEY (`fileName`)  REFERENCES `files` (`filename`),
105  CONSTRAINT `fk_permissionTuples_roleToken` FOREIGN KEY (`roleToken`) REFERENCES `roles` (`roleToken`),
106  CONSTRAINT `fk_permissionTuples_fileToken` FOREIGN KEY (`fileToken`) REFERENCES `files` (`fileToken`),
107  CONSTRAINT `fk_permissionTuples_userToken` FOREIGN KEY (`userToken`) REFERENCES `users` (`userToken`)
108) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
109
110
111CREATE TABLE `fileTuples` (
112  `fileName` varchar(50),
113  `fileToken` char(50),
114  `elementToken` char(50) NOT NULL,
115  `elementSigner` char(50) NOT NULL,
116  `decryptFileVersionNumber` int NOT NULL,
117  `signature` char(172) NOT NULL,
118  PRIMARY KEY (`fileName`),
119  KEY `fk_fileTuples_fileName_idx`  (`fileName`),
120  KEY `fk_fileTuples_fileToken_idx` (`fileToken`),
121  CONSTRAINT `fk_fileTuples_fileName`  FOREIGN KEY (`fileName`)  REFERENCES `files` (`filename`),
122  CONSTRAINT `fk_fileTuples_fileToken` FOREIGN KEY (`fileToken`) REFERENCES `files` (`fileToken`)
123) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
124
125/* note how users can modify ONLY their public key and ONLY once */
126CREATE VIEW `user_specific_users` (`publicEncryptingKey`, `publicSigningKey`,  `userToken`, `statusFlag`) AS
127    SELECT
128        `users`.`publicEncryptingKey` AS `publicEncryptingKey`,
129        `users`.`publicSigningKey` AS `publicSigningKey`,
130        `users`.`userToken` AS `userToken`,
131        `users`.`statusFlag` AS `statusFlag`
132    FROM
133        `users`
134    WHERE
135        `users`.`username` = (CONVERT( SUBSTRING_INDEX(USER(), '@', 1) USING UTF8MB4)) AND
136        `users`.`publicEncryptingKey` = 'mock' AND
137        `users`.`publicSigningKey` = 'mock';
138
139/* users can access only their role tuples */
140CREATE VIEW `user_specific_roleTuples`
141(`username`, `roleName`, `roleVersionNumber`, `encryptedRolePublicEncryptingKey`,
142 `encryptedRolePrivateEncryptingKey`, `encryptedRolePublicSigningKey`,
143 `encryptedRolePrivateSigningKey`, `signature`) AS
144    SELECT
145        `roleTuples`.`username` AS `username`,
146        `roleTuples`.`roleName` AS `roleName`,
147        `roleTuples`.`roleVersionNumber` AS `roleVersionNumber`,
148        `roleTuples`.`encryptedRolePublicEncryptingKey` AS `encryptedRolePublicEncryptingKey`,
149        `roleTuples`.`encryptedRolePrivateEncryptingKey` AS `encryptedRolePrivateEncryptingKey`,
150        `roleTuples`.`encryptedRolePublicSigningKey` AS `encryptedRolePublicSigningKey`,
151        `roleTuples`.`encryptedRolePrivateSigningKey` AS `encryptedRolePrivateSigningKey`,
152        `roleTuples`.`signature` AS `signature`
153    FROM
154        `roleTuples`
155    WHERE
156        `roleTuples`.`username` = (CONVERT( SUBSTRING_INDEX(USER(), '@', 1) USING UTF8MB4));
157
158/* users can access only the permissions they have */
159CREATE VIEW `user_specific_permissionTuples`
160(`roleName`, `fileName`, `symmetricFileKeyVersionNumber`, `roleToken`, `fileToken`,
161 `roleVersionNumber`, `encryptedSymmetricFileKey`, `permission`, `userToken`, `signature`) AS
162    SELECT
163        `permissionTuples`.`roleName` AS `roleName`,
164        `permissionTuples`.`fileName` AS `fileName`,
165        `permissionTuples`.`symmetricFileKeyVersionNumber` AS `symmetricFileKeyVersionNumber`,
166        `permissionTuples`.`roleToken` AS `roleToken`,
167        `permissionTuples`.`fileToken` AS `fileToken`,
168        `permissionTuples`.`roleVersionNumber` AS `roleVersionNumber`,
169        `permissionTuples`.`encryptedSymmetricFileKey` AS `encryptedSymmetricFileKey`,
170        `permissionTuples`.`permission` AS `permission`,
171        `permissionTuples`.`userToken` AS `userToken`,
172        `permissionTuples`.`signature` AS `signature`
173    FROM
174        `permissionTuples`
175    WHERE
176        `permissionTuples`.`roleName` IN (SELECT `user_specific_roleTuples`.`roleName` FROM `user_specific_roleTuples`);
177
178/* users can access only the files they have permission over */
179CREATE VIEW `user_specific_fileTuples`
180(`fileName`, `fileToken`, `elementToken`, `elementSigner`, `decryptFileVersionNumber`,
181 `signature`) AS
182    SELECT
183        `fileTuples`.`fileName` AS `fileName`,
184        `fileTuples`.`fileToken` AS `fileToken`,
185        `fileTuples`.`elementToken` AS `elementToken`,
186        `fileTuples`.`elementSigner` AS `elementSigner`,
187        `fileTuples`.`decryptFileVersionNumber` AS `decryptFileVersionNumber`,
188        `fileTuples`.`signature` AS `signature`
189    FROM
190        `fileTuples`
191    WHERE
192        `fileTuples`.`fileName` IN (SELECT `user_specific_permissionTuples`.`fileName` FROM `user_specific_permissionTuples`);
193
194
195/* Automatically insert the tokens of the role and
196the file when inserting a new permission tuple).
197We do not need to worry about update, as only the admin has this privilege and
198the tokens are not modified by update operations */
199DELIMITER //
200CREATE TRIGGER insertTokensPermissionTuple BEFORE INSERT ON permissionTuples
201FOR EACH ROW BEGIN
202 SET NEW.roleToken = (SELECT roleToken FROM roles WHERE roles.roleName = NEW.roleName LIMIT 1);
203 SET NEW.fileToken = (SELECT fileToken FROM files WHERE files.fileName = NEW.fileName LIMIT 1);
204END //
205
206/* Automatically insert the tokens of the file
207when inserting a new file tuple).
208We do not need to worry about update, as only the admin has this privilege and
209the tokens are not modified by update operations */
210DELIMITER //
211CREATE TRIGGER insertTokenFileTuple BEFORE INSERT ON fileTuples
212FOR EACH ROW BEGIN
213 SET NEW.fileToken = (SELECT fileToken FROM files WHERE files.fileName = NEW.fileName LIMIT 1);
214END //