diff options
| -rw-r--r-- | schema.sql | 63 |
1 files changed, 63 insertions, 0 deletions
diff --git a/schema.sql b/schema.sql new file mode 100644 index 0000000..d586c35 --- /dev/null +++ b/schema.sql @@ -0,0 +1,63 @@ +/* SCROTT - Core System Data Structures + * ------------------------------------ + * SQL Database Schema for backend + */ + +CREATE DATABASE IF NOT EXISTS `db_scrott`; +USE `db_scrott`; + +/* Description of SCROTT object permissions system + * ----------------------------------------------- + * Every object has an owner, zero or more additional members, and then there is the general + * public. + * + * OWNER = user OR group + * MEMBER = user + * OWNER MEMBERS OTHERS + * Permissions are: ------------------------------------------------ + * 1. Access object GRANTED GRANTED MB #6 + * 2. Modify object GRANTED MB #0 DENIED + * 3. Modify members GRANTED MB #1 DENIED + * 4. Modify permissions GRANTED DENIED DENIED + * 5. Access sub-objects GRANTED MB #2 MB #7 + * 6. Create sub-objects GRANTED MB #3 MB #8 + * 7. Modify sub-objects GRANTED MB #4 DENIED + * 8. Modify sub-objects' members GRANTED MB #5 DENIED + * 9. Modify sub-objects' permissions GRANTED DENIED DENIED + * + * 012345678 + * EG: 001111000 = 120 --implies--> that members may access,create,and + * fully modify sub-objects. All other + * options are DENIED. This mask value + * of 120 is a recommended starting point. + * + * Permissions can be overridden down-stream, but permissions will cascade otherwise. + */ + +DROP TABLE IF EXISTS `user`; +CREATE TABLE `user` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `guid` varchar(10) NOT NULL, + `owner` varchar(10) NOT NULL, + `perms` int(10) unsigned NOT NULL, + `name` varchar(50) NOT NULL, + `alias` varchar(50) DEFAULT NULL, + `key` varchar(64) NOT NULL, + `salt` varchar(64) NOT NULL, + + PRIMARY KEY (`id`) +); + + + + + + +DROP TABLE IF EXISTS `group`; +CREATE TABLE `group` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `guid` varchar(10) NOT NULL, + `owner` varchar(10) NOT NULL, + `name` varchar(50) NOT NULL, + /* TODO -- wip */ + /* TODO -- remember members table */ |