+ Defined schema for backend users table

! Devised an initial concept for Scrott object permissions system

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 */