GUACAMOLE-220: Add MySQL and SQL Server versions of user group schema.
Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/1d0fcc17 Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/1d0fcc17 Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/1d0fcc17 Branch: refs/heads/staging/1.0.0 Commit: 1d0fcc1732fded614707b0e425af89eca0526e6a Parents: 78d5e3b Author: Michael Jumper <mjum...@apache.org> Authored: Fri Sep 7 20:49:13 2018 -0700 Committer: Michael Jumper <mjum...@apache.org> Committed: Wed Sep 19 23:56:52 2018 -0700 ---------------------------------------------------------------------- .../schema/001-create-schema.sql | 196 +++++-- .../schema/002-create-admin-user.sql | 26 +- .../schema/upgrade/upgrade-pre-1.0.0.sql | 335 ++++++++++++ .../schema/001-create-schema.sql | 303 ++++++++--- .../schema/002-create-admin-user.sql | 21 +- .../schema/upgrade/upgrade-pre-1.0.0.sql | 532 +++++++++++++++++++ 6 files changed, 1294 insertions(+), 119 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/1d0fcc17/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql index 76711f1..1606af1 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql @@ -79,6 +79,25 @@ CREATE TABLE `guacamole_connection` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Table of base entities which may each be either a user or user group. Other +-- tables which represent qualities shared by both users and groups will point +-- to guacamole_entity, while tables which represent qualities specific to +-- users or groups will point to guacamole_user or guacamole_user_group. +-- + +CREATE TABLE `guacamole_entity` ( + + `entity_id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(128) NOT NULL, + `type` enum('USER', + 'USER_GROUP') NOT NULL, + + PRIMARY KEY (`entity_id`), + UNIQUE KEY `guacamole_entity_name_scope` (`type`, `name`) + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -- Table of users. Each user has a unique username and a hashed password -- with corresponding salt. Although the authentication system will always set -- salted passwords, other systems may set unsalted passwords by simply not @@ -88,9 +107,9 @@ CREATE TABLE `guacamole_connection` ( CREATE TABLE `guacamole_user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, + `entity_id` int(11) NOT NULL, - -- Username and optionally-salted password - `username` varchar(128) NOT NULL, + -- Optionally-salted password `password_hash` binary(32) NOT NULL, `password_salt` binary(32), `password_date` datetime NOT NULL, @@ -117,7 +136,61 @@ CREATE TABLE `guacamole_user` ( `organizational_role` VARCHAR(256), PRIMARY KEY (`user_id`), - UNIQUE KEY `username` (`username`) + + UNIQUE KEY `guacamole_user_single_entity` (`entity_id`), + + CONSTRAINT `guacamole_user_entity` + FOREIGN KEY (`entity_id`) + REFERENCES `guacamole_entity` (`entity_id`) + ON DELETE CASCADE + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table of user groups. Each user group may have an arbitrary set of member +-- users and member groups, with those members inheriting the permissions +-- granted to that group. +-- + +CREATE TABLE `guacamole_user_group` ( + + `user_group_id` int(11) NOT NULL AUTO_INCREMENT, + `entity_id` int(11) NOT NULL, + + -- Group disabled status + `disabled` boolean NOT NULL DEFAULT 0, + + PRIMARY KEY (`user_group_id`), + + UNIQUE KEY `guacamole_user_group_single_entity` (`entity_id`), + + CONSTRAINT `guacamole_user_group_entity` + FOREIGN KEY (`entity_id`) + REFERENCES `guacamole_entity` (`entity_id`) + ON DELETE CASCADE + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table of users which are members of given user groups. +-- + +CREATE TABLE `guacamole_user_group_member` ( + + `user_group_id` int(11) NOT NULL, + `member_entity_id` int(11) NOT NULL, + + PRIMARY KEY (`user_group_id`, `member_entity_id`), + + -- Parent must be a user group + CONSTRAINT `guacamole_user_group_member_parent_id` + FOREIGN KEY (`user_group_id`) + REFERENCES `guacamole_user_group` (`user_group_id`) ON DELETE CASCADE, + + -- Member may be either a user or a user group (any entity) + CONSTRAINT `guacamole_user_group_member_entity_id` + FOREIGN KEY (`member_entity_id`) + REFERENCES `guacamole_entity` (`entity_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -208,6 +281,28 @@ CREATE TABLE guacamole_user_attribute ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Table of arbitrary user group attributes. Each attribute is simply a +-- name/value pair associated with a user group. Arbitrary attributes are +-- defined by other extensions. Attributes defined by this extension will be +-- mapped to properly-typed columns of a specific table. +-- + +CREATE TABLE guacamole_user_group_attribute ( + + `user_group_id` int(11) NOT NULL, + `attribute_name` varchar(128) NOT NULL, + `attribute_value` varchar(4096) NOT NULL, + + PRIMARY KEY (`user_group_id`, `attribute_name`), + KEY `user_group_id` (`user_group_id`), + + CONSTRAINT `guacamole_user_group_attribute_ibfk_1` + FOREIGN KEY (`user_group_id`) + REFERENCES `guacamole_user_group` (`user_group_id`) ON DELETE CASCADE + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -- Table of arbitrary connection attributes. Each attribute is simply a -- name/value pair associated with a connection. Arbitrary attributes are -- defined by other extensions. Attributes defined by this extension will be @@ -274,128 +369,157 @@ CREATE TABLE guacamole_sharing_profile_attribute ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- Table of connection permissions. Each connection permission grants a user --- specific access to a connection. +-- Table of connection permissions. Each connection permission grants a user or +-- user group specific access to a connection. -- CREATE TABLE `guacamole_connection_permission` ( - `user_id` int(11) NOT NULL, + `entity_id` int(11) NOT NULL, `connection_id` int(11) NOT NULL, `permission` enum('READ', 'UPDATE', 'DELETE', 'ADMINISTER') NOT NULL, - PRIMARY KEY (`user_id`,`connection_id`,`permission`), + PRIMARY KEY (`entity_id`,`connection_id`,`permission`), CONSTRAINT `guacamole_connection_permission_ibfk_1` FOREIGN KEY (`connection_id`) REFERENCES `guacamole_connection` (`connection_id`) ON DELETE CASCADE, - CONSTRAINT `guacamole_connection_permission_ibfk_2` - FOREIGN KEY (`user_id`) - REFERENCES `guacamole_user` (`user_id`) ON DELETE CASCADE + CONSTRAINT `guacamole_connection_permission_entity` + FOREIGN KEY (`entity_id`) + REFERENCES `guacamole_entity` (`entity_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Table of connection group permissions. Each group permission grants a user --- specific access to a connection group. +-- or user group specific access to a connection group. -- CREATE TABLE `guacamole_connection_group_permission` ( - `user_id` int(11) NOT NULL, + `entity_id` int(11) NOT NULL, `connection_group_id` int(11) NOT NULL, `permission` enum('READ', 'UPDATE', 'DELETE', 'ADMINISTER') NOT NULL, - PRIMARY KEY (`user_id`,`connection_group_id`,`permission`), + PRIMARY KEY (`entity_id`,`connection_group_id`,`permission`), CONSTRAINT `guacamole_connection_group_permission_ibfk_1` FOREIGN KEY (`connection_group_id`) REFERENCES `guacamole_connection_group` (`connection_group_id`) ON DELETE CASCADE, - CONSTRAINT `guacamole_connection_group_permission_ibfk_2` - FOREIGN KEY (`user_id`) - REFERENCES `guacamole_user` (`user_id`) ON DELETE CASCADE + CONSTRAINT `guacamole_connection_group_permission_entity` + FOREIGN KEY (`entity_id`) + REFERENCES `guacamole_entity` (`entity_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Table of sharing profile permissions. Each sharing profile permission grants --- a user specific access to a sharing profile. +-- a user or user group specific access to a sharing profile. -- CREATE TABLE guacamole_sharing_profile_permission ( - `user_id` integer NOT NULL, + `entity_id` integer NOT NULL, `sharing_profile_id` integer NOT NULL, `permission` enum('READ', 'UPDATE', 'DELETE', 'ADMINISTER') NOT NULL, - PRIMARY KEY (`user_id`, `sharing_profile_id`, `permission`), + PRIMARY KEY (`entity_id`, `sharing_profile_id`, `permission`), CONSTRAINT `guacamole_sharing_profile_permission_ibfk_1` FOREIGN KEY (`sharing_profile_id`) REFERENCES `guacamole_sharing_profile` (`sharing_profile_id`) ON DELETE CASCADE, - CONSTRAINT `guacamole_sharing_profile_permission_ibfk_2` - FOREIGN KEY (`user_id`) - REFERENCES `guacamole_user` (`user_id`) ON DELETE CASCADE + CONSTRAINT `guacamole_sharing_profile_permission_entity` + FOREIGN KEY (`entity_id`) + REFERENCES `guacamole_entity` (`entity_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- Table of system permissions. Each system permission grants a user a --- system-level privilege of some kind. +-- Table of system permissions. Each system permission grants a user or user +-- group a system-level privilege of some kind. -- CREATE TABLE `guacamole_system_permission` ( - `user_id` int(11) NOT NULL, + `entity_id` int(11) NOT NULL, `permission` enum('CREATE_CONNECTION', 'CREATE_CONNECTION_GROUP', 'CREATE_SHARING_PROFILE', 'CREATE_USER', + 'CREATE_USER_GROUP', 'ADMINISTER') NOT NULL, - PRIMARY KEY (`user_id`,`permission`), + PRIMARY KEY (`entity_id`,`permission`), - CONSTRAINT `guacamole_system_permission_ibfk_1` - FOREIGN KEY (`user_id`) - REFERENCES `guacamole_user` (`user_id`) ON DELETE CASCADE + CONSTRAINT `guacamole_system_permission_entity` + FOREIGN KEY (`entity_id`) + REFERENCES `guacamole_entity` (`entity_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- Table of user permissions. Each user permission grants a user access to --- another user (the "affected" user) for a specific type of operation. +-- Table of user permissions. Each user permission grants a user or user group +-- access to another user (the "affected" user) for a specific type of +-- operation. -- CREATE TABLE `guacamole_user_permission` ( - `user_id` int(11) NOT NULL, + `entity_id` int(11) NOT NULL, `affected_user_id` int(11) NOT NULL, `permission` enum('READ', 'UPDATE', 'DELETE', 'ADMINISTER') NOT NULL, - PRIMARY KEY (`user_id`,`affected_user_id`,`permission`), + PRIMARY KEY (`entity_id`,`affected_user_id`,`permission`), CONSTRAINT `guacamole_user_permission_ibfk_1` FOREIGN KEY (`affected_user_id`) REFERENCES `guacamole_user` (`user_id`) ON DELETE CASCADE, - CONSTRAINT `guacamole_user_permission_ibfk_2` - FOREIGN KEY (`user_id`) - REFERENCES `guacamole_user` (`user_id`) ON DELETE CASCADE + CONSTRAINT `guacamole_user_permission_entity` + FOREIGN KEY (`entity_id`) + REFERENCES `guacamole_entity` (`entity_id`) ON DELETE CASCADE + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table of user group permissions. Each user group permission grants a user +-- or user group access to a another user group (the "affected" user group) for +-- a specific type of operation. +-- + +CREATE TABLE `guacamole_user_group_permission` ( + + `entity_id` int(11) NOT NULL, + `affected_user_group_id` int(11) NOT NULL, + `permission` enum('READ', + 'UPDATE', + 'DELETE', + 'ADMINISTER') NOT NULL, + + PRIMARY KEY (`entity_id`, `affected_user_group_id`, `permission`), + + CONSTRAINT `guacamole_user_group_permission_affected_user_group` + FOREIGN KEY (`affected_user_group_id`) + REFERENCES `guacamole_user_group` (`user_group_id`) ON DELETE CASCADE, + + CONSTRAINT `guacamole_user_group_permission_entity` + FOREIGN KEY (`entity_id`) + REFERENCES `guacamole_entity` (`entity_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/1d0fcc17/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/002-create-admin-user.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/002-create-admin-user.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/002-create-admin-user.sql index cfc8313..f62d6d1 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/002-create-admin-user.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/002-create-admin-user.sql @@ -18,32 +18,36 @@ -- -- Create default user "guacadmin" with password "guacadmin" -INSERT INTO guacamole_user (username, password_hash, password_salt, password_date) -VALUES ('guacadmin', +INSERT INTO guacamole_entity (name, type) VALUES ('guacadmin', 'USER'); +INSERT INTO guacamole_user (entity_id, password_hash, password_salt, password_date) +SELECT + entity_id, x'CA458A7D494E3BE824F5E1E175A1556C0F8EEF2C2D7DF3633BEC4A29C4411960', -- 'guacadmin' x'FE24ADC5E11E2B25288D1704ABE67A79E342ECC26064CE69C5B3177795A82264', - NOW()); + NOW() +FROM guacamole_entity WHERE name = 'guacadmin'; -- Grant this user all system permissions -INSERT INTO guacamole_system_permission -SELECT user_id, permission +INSERT INTO guacamole_system_permission (entity_id, permission) +SELECT entity_id, permission FROM ( SELECT 'guacadmin' AS username, 'CREATE_CONNECTION' AS permission UNION SELECT 'guacadmin' AS username, 'CREATE_CONNECTION_GROUP' AS permission UNION SELECT 'guacadmin' AS username, 'CREATE_SHARING_PROFILE' AS permission UNION SELECT 'guacadmin' AS username, 'CREATE_USER' AS permission + UNION SELECT 'guacadmin' AS username, 'CREATE_USER_GROUP' AS permission UNION SELECT 'guacadmin' AS username, 'ADMINISTER' AS permission ) permissions -JOIN guacamole_user ON permissions.username = guacamole_user.username; +JOIN guacamole_entity ON permissions.username = guacamole_entity.name AND guacamole_entity.type = 'USER'; -- Grant admin permission to read/update/administer self -INSERT INTO guacamole_user_permission -SELECT guacamole_user.user_id, affected.user_id, permission +INSERT INTO guacamole_user_permission (entity_id, affected_user_id, permission) +SELECT guacamole_entity.entity_id, guacamole_user.user_id, permission FROM ( SELECT 'guacadmin' AS username, 'guacadmin' AS affected_username, 'READ' AS permission UNION SELECT 'guacadmin' AS username, 'guacadmin' AS affected_username, 'UPDATE' AS permission UNION SELECT 'guacadmin' AS username, 'guacadmin' AS affected_username, 'ADMINISTER' AS permission ) permissions -JOIN guacamole_user ON permissions.username = guacamole_user.username -JOIN guacamole_user affected ON permissions.affected_username = affected.username; - +JOIN guacamole_entity ON permissions.username = guacamole_entity.name AND guacamole_entity.type = 'USER' +JOIN guacamole_entity affected ON permissions.affected_username = affected.name AND guacamole_entity.type = 'USER' +JOIN guacamole_user ON guacamole_user.entity_id = affected.entity_id; http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/1d0fcc17/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-1.0.0.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-1.0.0.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-1.0.0.sql index 2979f53..8332d7f 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-1.0.0.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-1.0.0.sql @@ -18,6 +18,319 @@ -- -- +-- Add new system-level permission +-- + +ALTER TABLE `guacamole_system_permission` + MODIFY `permission` enum('CREATE_CONNECTION', + 'CREATE_CONNECTION_GROUP', + 'CREATE_SHARING_PROFILE', + 'CREATE_USER', + 'CREATE_USER_GROUP', + 'ADMINISTER') NOT NULL; + +-- +-- Table of base entities which may each be either a user or user group. Other +-- tables which represent qualities shared by both users and groups will point +-- to guacamole_entity, while tables which represent qualities specific to +-- users or groups will point to guacamole_user or guacamole_user_group. +-- + +CREATE TABLE `guacamole_entity` ( + + `entity_id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(128) NOT NULL, + `type` enum('USER', + 'USER_GROUP') NOT NULL, + + PRIMARY KEY (`entity_id`), + UNIQUE KEY `guacamole_entity_name_scope` (`type`, `name`) + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table of user groups. Each user group may have an arbitrary set of member +-- users and member groups, with those members inheriting the permissions +-- granted to that group. +-- + +CREATE TABLE `guacamole_user_group` ( + + `user_group_id` int(11) NOT NULL AUTO_INCREMENT, + `entity_id` int(11) NOT NULL, + + -- Group disabled status + `disabled` boolean NOT NULL DEFAULT 0, + + PRIMARY KEY (`user_group_id`), + + UNIQUE KEY `guacamole_user_group_single_entity` (`entity_id`), + + CONSTRAINT `guacamole_user_group_entity` + FOREIGN KEY (`entity_id`) + REFERENCES `guacamole_entity` (`entity_id`) + ON DELETE CASCADE + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table of users which are members of given user groups. +-- + +CREATE TABLE `guacamole_user_group_member` ( + + `user_group_id` int(11) NOT NULL, + `member_entity_id` int(11) NOT NULL, + + PRIMARY KEY (`user_group_id`, `member_entity_id`), + + -- Parent must be a user group + CONSTRAINT `guacamole_user_group_member_parent_id` + FOREIGN KEY (`user_group_id`) + REFERENCES `guacamole_user_group` (`user_group_id`) ON DELETE CASCADE, + + -- Member may be either a user or a user group (any entity) + CONSTRAINT `guacamole_user_group_member_entity_id` + FOREIGN KEY (`member_entity_id`) + REFERENCES `guacamole_entity` (`entity_id`) ON DELETE CASCADE + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table of user group permissions. Each user group permission grants a user +-- or user group access to a another user group (the "affected" user group) for +-- a specific type of operation. +-- + +CREATE TABLE `guacamole_user_group_permission` ( + + `entity_id` int(11) NOT NULL, + `affected_user_group_id` int(11) NOT NULL, + `permission` enum('READ', + 'UPDATE', + 'DELETE', + 'ADMINISTER') NOT NULL, + + PRIMARY KEY (`entity_id`, `affected_user_group_id`, `permission`), + + CONSTRAINT `guacamole_user_group_permission_affected_user_group` + FOREIGN KEY (`affected_user_group_id`) + REFERENCES `guacamole_user_group` (`user_group_id`) ON DELETE CASCADE, + + CONSTRAINT `guacamole_user_group_permission_entity` + FOREIGN KEY (`entity_id`) + REFERENCES `guacamole_entity` (`entity_id`) ON DELETE CASCADE + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Modify guacamole_user table to use guacamole_entity as a base +-- + +-- Add new entity_id column +ALTER TABLE guacamole_user ADD COLUMN entity_id int(11); + +-- Create user entities for each guacamole_user entry +INSERT INTO guacamole_entity (name, type) +SELECT username, 'USER' FROM guacamole_user; + +-- Update guacamole_user to point to corresponding guacamole_entity +UPDATE guacamole_user SET entity_id = ( + SELECT entity_id FROM guacamole_entity + WHERE + username = guacamole_entity.name + AND type = 'USER' +); + +-- The entity_id column should now be safely non-NULL +ALTER TABLE guacamole_user MODIFY entity_id int(11) NOT NULL; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE guacamole_user + ADD CONSTRAINT guacamole_user_single_entity + UNIQUE (entity_id); + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE guacamole_user + ADD CONSTRAINT guacamole_user_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE; + +-- The username column can now safely be removed +ALTER TABLE guacamole_user DROP COLUMN username; + +-- +-- Modify guacamole_connection_permission to use guacamole_entity instead of +-- guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE guacamole_connection_permission ADD COLUMN entity_id int(11); + +-- Update guacamole_connection_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE guacamole_connection_permission SET entity_id = ( + SELECT entity_id FROM guacamole_user + WHERE guacamole_user.user_id = guacamole_connection_permission.user_id +); + +-- The entity_id column should now be safely non-NULL +ALTER TABLE guacamole_connection_permission MODIFY entity_id int(11) NOT NULL; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE guacamole_connection_permission + ADD CONSTRAINT guacamole_connection_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE; + +-- Remove user_id column +ALTER TABLE guacamole_connection_permission DROP FOREIGN KEY guacamole_connection_permission_ibfk_2; +ALTER TABLE guacamole_connection_permission DROP PRIMARY KEY; +ALTER TABLE guacamole_connection_permission DROP COLUMN user_id; + +-- Add new primary key which uses entity_id +ALTER TABLE guacamole_connection_permission + ADD PRIMARY KEY (entity_id, connection_id, permission); + +-- +-- Modify guacamole_connection_group_permission to use guacamole_entity instead +-- of guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE guacamole_connection_group_permission ADD COLUMN entity_id int(11); + +-- Update guacamole_connection_group_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE guacamole_connection_group_permission SET entity_id = ( + SELECT entity_id FROM guacamole_user + WHERE guacamole_user.user_id = guacamole_connection_group_permission.user_id +); + +-- The entity_id column should now be safely non-NULL +ALTER TABLE guacamole_connection_group_permission MODIFY entity_id int(11) NOT NULL; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE guacamole_connection_group_permission + ADD CONSTRAINT guacamole_connection_group_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE; + +-- Remove user_id column +ALTER TABLE guacamole_connection_group_permission DROP FOREIGN KEY guacamole_connection_group_permission_ibfk_2; +ALTER TABLE guacamole_connection_group_permission DROP PRIMARY KEY; +ALTER TABLE guacamole_connection_group_permission DROP COLUMN user_id; + +-- Add new primary key which uses entity_id +ALTER TABLE guacamole_connection_group_permission + ADD PRIMARY KEY (entity_id, connection_group_id, permission); + +-- +-- Modify guacamole_sharing_profile_permission to use guacamole_entity instead +-- of guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE guacamole_sharing_profile_permission ADD COLUMN entity_id int(11); + +-- Update guacamole_sharing_profile_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE guacamole_sharing_profile_permission SET entity_id = ( + SELECT entity_id FROM guacamole_user + WHERE guacamole_user.user_id = guacamole_sharing_profile_permission.user_id +); + +-- The entity_id column should now be safely non-NULL +ALTER TABLE guacamole_sharing_profile_permission MODIFY entity_id int(11) NOT NULL; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE guacamole_sharing_profile_permission + ADD CONSTRAINT guacamole_sharing_profile_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE; + +-- Remove user_id column +ALTER TABLE guacamole_sharing_profile_permission DROP FOREIGN KEY guacamole_sharing_profile_permission_ibfk_2; +ALTER TABLE guacamole_sharing_profile_permission DROP PRIMARY KEY; +ALTER TABLE guacamole_sharing_profile_permission DROP COLUMN user_id; + +-- Add new primary key which uses entity_id +ALTER TABLE guacamole_sharing_profile_permission + ADD PRIMARY KEY (entity_id, sharing_profile_id, permission); + +-- +-- Modify guacamole_user_permission to use guacamole_entity instead of +-- guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE guacamole_user_permission ADD COLUMN entity_id int(11); + +-- Update guacamole_user_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE guacamole_user_permission SET entity_id = ( + SELECT entity_id FROM guacamole_user + WHERE guacamole_user.user_id = guacamole_user_permission.user_id +); + +-- The entity_id column should now be safely non-NULL +ALTER TABLE guacamole_user_permission MODIFY entity_id int(11) NOT NULL; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE guacamole_user_permission + ADD CONSTRAINT guacamole_user_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE; + +-- Remove user_id column +ALTER TABLE guacamole_user_permission DROP FOREIGN KEY guacamole_user_permission_ibfk_2; +ALTER TABLE guacamole_user_permission DROP PRIMARY KEY; +ALTER TABLE guacamole_user_permission DROP COLUMN user_id; + +-- Add new primary key which uses entity_id +ALTER TABLE guacamole_user_permission + ADD PRIMARY KEY (entity_id, affected_user_id, permission); + +-- +-- Modify guacamole_system_permission to use guacamole_entity instead of +-- guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE guacamole_system_permission ADD COLUMN entity_id int(11); + +-- Update guacamole_system_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE guacamole_system_permission SET entity_id = ( + SELECT entity_id FROM guacamole_user + WHERE guacamole_user.user_id = guacamole_system_permission.user_id +); + +-- The entity_id column should now be safely non-NULL +ALTER TABLE guacamole_system_permission MODIFY entity_id int(11) NOT NULL; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE guacamole_system_permission + ADD CONSTRAINT guacamole_system_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE; + +-- Remove user_id column +ALTER TABLE guacamole_system_permission DROP FOREIGN KEY guacamole_system_permission_ibfk_1; +ALTER TABLE guacamole_system_permission DROP PRIMARY KEY; +ALTER TABLE guacamole_system_permission DROP COLUMN user_id; + +-- Add new primary key which uses entity_id +ALTER TABLE guacamole_system_permission + ADD PRIMARY KEY (entity_id, permission); + +-- -- Table of arbitrary user attributes. Each attribute is simply a name/value -- pair associated with a user. Arbitrary attributes are defined by other -- extensions. Attributes defined by this extension will be mapped to @@ -40,6 +353,28 @@ CREATE TABLE guacamole_user_attribute ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Table of arbitrary user group attributes. Each attribute is simply a +-- name/value pair associated with a user group. Arbitrary attributes are +-- defined by other extensions. Attributes defined by this extension will be +-- mapped to properly-typed columns of a specific table. +-- + +CREATE TABLE guacamole_user_group_attribute ( + + `user_group_id` int(11) NOT NULL, + `attribute_name` varchar(128) NOT NULL, + `attribute_value` varchar(4096) NOT NULL, + + PRIMARY KEY (`user_group_id`, `attribute_name`), + KEY `user_group_id` (`user_group_id`), + + CONSTRAINT `guacamole_user_group_attribute_ibfk_1` + FOREIGN KEY (`user_group_id`) + REFERENCES `guacamole_user_group` (`user_group_id`) ON DELETE CASCADE + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -- Table of arbitrary connection attributes. Each attribute is simply a -- name/value pair associated with a connection. Arbitrary attributes are -- defined by other extensions. Attributes defined by this extension will be http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/1d0fcc17/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql index ee10dda..54be792 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql @@ -34,6 +34,22 @@ EXEC sp_bindrule GO -- +-- Entity types +-- + +CREATE RULE [guacamole_entity_type_list] AS @list IN ( + 'USER', + 'USER_GROUP' +); +GO + +CREATE TYPE [guacamole_entity_type] FROM [nvarchar](16); +EXEC sp_bindrule + 'guacamole_entity_type_list', + 'guacamole_entity_type'; +GO + +-- -- Object permission types -- @@ -60,6 +76,7 @@ CREATE RULE [guacamole_system_permission_list] AS @list IN ( 'CREATE_CONNECTION_GROUP', 'CREATE_SHARING_PROFILE', 'CREATE_USER', + 'CREATE_USER_GROUP', 'ADMINISTER' ); GO @@ -164,6 +181,28 @@ CREATE NONCLUSTERED INDEX [IX_guacamole_connection_parent_id] GO -- +-- Table of base entities which may each be either a user or user group. Other +-- tables which represent qualities shared by both users and groups will point +-- to guacamole_entity, while tables which represent qualities specific to +-- users or groups will point to guacamole_user or guacamole_user_group. +-- + +CREATE TABLE [guacamole_entity] ( + + [entity_id] [int] IDENTITY(1,1) NOT NULL, + [name] [nvarchar](128) NOT NULL, + [type] [guacamole_entity_type] NOT NULL, + + CONSTRAINT [PK_guacamole_entity] + PRIMARY KEY CLUSTERED ([entity_id]), + + CONSTRAINT [AK_guacamole_entity_name_scope] + UNIQUE ([type], [name]) + +); +GO + +-- -- Table of users. Each user has a unique username and a hashed password -- with corresponding salt. Although the authentication system will always set -- salted passwords, other systems may set unsalted passwords by simply not @@ -172,10 +211,10 @@ GO CREATE TABLE [guacamole_user] ( - [user_id] [int] IDENTITY(1,1) NOT NULL, + [user_id] [int] IDENTITY(1,1) NOT NULL, + [entity_id] [int] NOT NULL, - -- Username and optionally-salted password - [username] [nvarchar](128) NOT NULL, + -- Optionally-salted password [password_hash] [binary](32) NOT NULL, [password_salt] [binary](32), [password_date] [datetime] NOT NULL, @@ -204,8 +243,68 @@ CREATE TABLE [guacamole_user] ( CONSTRAINT [PK_guacamole_user] PRIMARY KEY CLUSTERED ([user_id]), - CONSTRAINT [AK_guacamole_user_username] - UNIQUE ([username]) + CONSTRAINT [AK_guacamole_user_single_entity] + UNIQUE ([entity_id]), + + CONSTRAINT [FK_guacamole_user_entity] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) + ON DELETE CASCADE + +); +GO + +-- +-- Table of user groups. Each user group may have an arbitrary set of member +-- users and member groups, with those members inheriting the permissions +-- granted to that group. +-- + +CREATE TABLE [guacamole_user_group] ( + + [user_group_id] [int] IDENTITY(1,1) NOT NULL, + [entity_id] [int] NOT NULL, + + -- Group disabled status + [disabled] [bit] NOT NULL DEFAULT 0, + + CONSTRAINT [PK_guacamole_user_group] + PRIMARY KEY CLUSTERED ([user_group_id]), + + CONSTRAINT [guacamole_user_group_single_entity] + UNIQUE ([entity_id]), + + CONSTRAINT [guacamole_user_group_entity] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) + ON DELETE CASCADE + +); +GO + +-- +-- Table of users which are members of given user groups. +-- + +CREATE TABLE [guacamole_user_group_member] ( + + [user_group_id] [int] NOT NULL, + [member_entity_id] [int] NOT NULL, + + CONSTRAINT [PK_guacamole_user_group_member] + PRIMARY KEY CLUSTERED ([user_group_id], [member_entity_id]), + + -- Parent must be a user group + CONSTRAINT [guacamole_user_group_member_parent_id] + FOREIGN KEY ([user_group_id]) + REFERENCES [guacamole_user_group] ([user_group_id]) + ON DELETE CASCADE, + + -- Member may be either a user or a user group (any entity) + CONSTRAINT [guacamole_user_group_member_entity_id] + FOREIGN KEY ([member_entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) + -- ON DELETE CASCADE handled by guacamole_delete_entity trigger ); GO @@ -270,6 +369,34 @@ CREATE NONCLUSTERED INDEX [IX_guacamole_user_attribute_user_id] GO -- +-- Table of arbitrary user group attributes. Each attribute is simply a +-- name/value pair associated with a user group. Arbitrary attributes are +-- defined by other extensions. Attributes defined by this extension will be +-- mapped to properly-typed columns of a specific table. +-- + +CREATE TABLE [guacamole_user_group_attribute] ( + + [user_group_id] [int] NOT NULL, + [attribute_name] [nvarchar](128) NOT NULL, + [attribute_value] [nvarchar](4000) NOT NULL, + + CONSTRAINT [PK_guacamole_user_group_attribute] + PRIMARY KEY CLUSTERED ([user_group_id], [attribute_name]), + + CONSTRAINT [FK_guacamole_user_attribute_user_group_id] + FOREIGN KEY ([user_group_id]) + REFERENCES [guacamole_user_group] ([user_group_id]) + ON DELETE CASCADE + +); + +CREATE NONCLUSTERED INDEX [IX_guacamole_user_group_attribute_user_id] + ON [guacamole_user_group_attribute] ([user_group_id]) + INCLUDE ([attribute_name], [attribute_value]); +GO + +-- -- Table of arbitrary connection attributes. Each attribute is simply a -- name/value pair associated with a connection. Arbitrary attributes are -- defined by other extensions. Attributes defined by this extension will be @@ -403,27 +530,27 @@ CREATE NONCLUSTERED INDEX [IX_guacamole_sharing_profile_parameter_sharing_profil GO -- --- Table of connection permissions. Each connection permission grants a user --- specific access to a connection. +-- Table of connection permissions. Each connection permission grants a user or +-- user group specific access to a connection. -- CREATE TABLE [guacamole_connection_permission] ( - [user_id] [int] NOT NULL, + [entity_id] [int] NOT NULL, [connection_id] [int] NOT NULL, [permission] [guacamole_object_permission] NOT NULL, CONSTRAINT [PK_guacamole_connection_permission] - PRIMARY KEY CLUSTERED ([user_id], [connection_id], [permission]), + PRIMARY KEY CLUSTERED ([entity_id], [connection_id], [permission]), CONSTRAINT [FK_guacamole_connection_permission_connection_id] FOREIGN KEY ([connection_id]) REFERENCES [guacamole_connection] ([connection_id]) ON DELETE CASCADE, - CONSTRAINT [FK_guacamole_connection_permission_user_id] - FOREIGN KEY ([user_id]) - REFERENCES [guacamole_user] ([user_id]) + CONSTRAINT [FK_guacamole_connection_permission_entity_id] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) ON DELETE CASCADE ); @@ -431,32 +558,32 @@ CREATE TABLE [guacamole_connection_permission] ( CREATE NONCLUSTERED INDEX [IX_guacamole_connection_permission_connection_id] ON [guacamole_connection_permission] ([connection_id]); -CREATE NONCLUSTERED INDEX [IX_guacamole_connection_permission_user_id] - ON [guacamole_connection_permission] ([user_id]); +CREATE NONCLUSTERED INDEX [IX_guacamole_connection_permission_entity_id] + ON [guacamole_connection_permission] ([entity_id]); GO -- -- Table of connection group permissions. Each group permission grants a user --- specific access to a connection group. +-- or user group specific access to a connection group. -- CREATE TABLE [guacamole_connection_group_permission] ( - [user_id] [int] NOT NULL, + [entity_id] [int] NOT NULL, [connection_group_id] [int] NOT NULL, [permission] [guacamole_object_permission] NOT NULL, CONSTRAINT [PK_guacamole_connection_group_permission] - PRIMARY KEY CLUSTERED ([user_id], [connection_group_id], [permission]), + PRIMARY KEY CLUSTERED ([entity_id], [connection_group_id], [permission]), CONSTRAINT [FK_guacamole_connection_group_permission_connection_group_id] FOREIGN KEY ([connection_group_id]) REFERENCES [guacamole_connection_group] ([connection_group_id]) ON DELETE CASCADE, - CONSTRAINT [FK_guacamole_connection_group_permission_user_id] - FOREIGN KEY ([user_id]) - REFERENCES [guacamole_user] ([user_id]) + CONSTRAINT [FK_guacamole_connection_group_permission_entity_id] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) ON DELETE CASCADE ); @@ -464,32 +591,32 @@ CREATE TABLE [guacamole_connection_group_permission] ( CREATE NONCLUSTERED INDEX [IX_guacamole_connection_group_permission_connection_group_id] ON [guacamole_connection_group_permission] ([connection_group_id]); -CREATE NONCLUSTERED INDEX [IX_guacamole_connection_group_permission_user_id] - ON [guacamole_connection_group_permission] ([user_id]); +CREATE NONCLUSTERED INDEX [IX_guacamole_connection_group_permission_entity_id] + ON [guacamole_connection_group_permission] ([entity_id]); GO -- -- Table of sharing profile permissions. Each sharing profile permission grants --- a user specific access to a sharing profile. +-- a user or user group specific access to a sharing profile. -- CREATE TABLE [guacamole_sharing_profile_permission] ( - [user_id] [int] NOT NULL, + [entity_id] [int] NOT NULL, [sharing_profile_id] [int] NOT NULL, [permission] [guacamole_object_permission] NOT NULL, CONSTRAINT [PK_guacamole_sharing_profile_permission] - PRIMARY KEY CLUSTERED ([user_id], [sharing_profile_id], [permission]), + PRIMARY KEY CLUSTERED ([entity_id], [sharing_profile_id], [permission]), CONSTRAINT [FK_guacamole_sharing_profile_permission_sharing_profile_id] FOREIGN KEY ([sharing_profile_id]) REFERENCES [guacamole_sharing_profile] ([sharing_profile_id]) ON DELETE CASCADE, - CONSTRAINT [FK_guacamole_sharing_profile_permission_user_id] - FOREIGN KEY ([user_id]) - REFERENCES [guacamole_user] ([user_id]) + CONSTRAINT [FK_guacamole_sharing_profile_permission_entity_id] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) ON DELETE CASCADE ); @@ -497,68 +624,103 @@ CREATE TABLE [guacamole_sharing_profile_permission] ( CREATE NONCLUSTERED INDEX [IX_guacamole_sharing_profile_permission_sharing_profile_id] ON [guacamole_sharing_profile_permission] ([sharing_profile_id]); -CREATE NONCLUSTERED INDEX [IX_guacamole_sharing_profile_permission_user_id] - ON [guacamole_sharing_profile_permission] ([user_id]); +CREATE NONCLUSTERED INDEX [IX_guacamole_sharing_profile_permission_entity_id] + ON [guacamole_sharing_profile_permission] ([entity_id]); GO -- --- Table of system permissions. Each system permission grants a user a --- system-level privilege of some kind. +-- Table of system permissions. Each system permission grants a user or user +-- group a system-level privilege of some kind. -- CREATE TABLE [guacamole_system_permission] ( - [user_id] [int] NOT NULL, + [entity_id] [int] NOT NULL, [permission] [guacamole_system_permission] NOT NULL, CONSTRAINT [PK_guacamole_system_permission] - PRIMARY KEY CLUSTERED ([user_id], [permission]), + PRIMARY KEY CLUSTERED ([entity_id], [permission]), - CONSTRAINT [FK_guacamole_system_permission_user_id] - FOREIGN KEY ([user_id]) - REFERENCES [guacamole_user] ([user_id]) + CONSTRAINT [FK_guacamole_system_permission_entity_id] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) ON DELETE CASCADE ); -CREATE NONCLUSTERED INDEX [IX_guacamole_system_permission_user_id] - ON [guacamole_system_permission] ([user_id]); +CREATE NONCLUSTERED INDEX [IX_guacamole_system_permission_entity_id] + ON [guacamole_system_permission] ([entity_id]); GO -- --- Table of user permissions. Each user permission grants a user access to --- another user (the "affected" user) for a specific type of operation. +-- Table of user permissions. Each user permission grants a user or user group +-- access to another user (the "affected" user) for a specific type of +-- operation. -- CREATE TABLE [guacamole_user_permission] ( - [user_id] [int] NOT NULL, + [entity_id] [int] NOT NULL, [affected_user_id] [int] NOT NULL, [permission] [guacamole_object_permission] NOT NULL, CONSTRAINT [PK_guacamole_user_permission] - PRIMARY KEY CLUSTERED ([user_id], [affected_user_id], [permission]), + PRIMARY KEY CLUSTERED ([entity_id], [affected_user_id], [permission]), CONSTRAINT [FK_guacamole_user_permission_affected_user_id] FOREIGN KEY ([affected_user_id]) - REFERENCES [guacamole_user] ([user_id]), - -- ON DELETE CASCADE handled by guacamole_delete_user trigger - - CONSTRAINT [FK_guacamole_user_permission_user_id] - FOREIGN KEY ([user_id]) REFERENCES [guacamole_user] ([user_id]) - -- ON DELETE CASCADE handled by guacamole_delete_user trigger + ON DELETE CASCADE, + + CONSTRAINT [FK_guacamole_user_permission_entity_id] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) + -- ON DELETE CASCADE handled by guacamole_delete_entity trigger ); -CREATE NONCLUSTERED INDEX [IX_guacamole_user_permission_user_id] - ON [guacamole_user_permission] ([user_id]); +CREATE NONCLUSTERED INDEX [IX_guacamole_user_permission_entity_id] + ON [guacamole_user_permission] ([entity_id]); CREATE NONCLUSTERED INDEX [IX_guacamole_user_permission_affected_user_id] ON [guacamole_user_permission] ([affected_user_id]); GO -- +-- Table of user group permissions. Each user group permission grants a user +-- or user group access to a another user group (the "affected" user group) for +-- a specific type of operation. +-- + +CREATE TABLE [guacamole_user_group_permission] ( + + [entity_id] [int] NOT NULL, + [affected_user_group_id] [int] NOT NULL, + [permission] [guacamole_object_permission] NOT NULL, + + CONSTRAINT [PK_guacamole_user_group_permission] + PRIMARY KEY CLUSTERED ([entity_id], [affected_user_group_id], [permission]), + + CONSTRAINT [FK_guacamole_user_group_permission_affected_user_group_id] + FOREIGN KEY ([affected_user_group_id]) + REFERENCES [guacamole_user_group] ([user_group_id]) + ON DELETE CASCADE, + + CONSTRAINT [FK_guacamole_user_group_permission_entity_id] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) + -- ON DELETE CASCADE handled by guacamole_delete_entity trigger + +); + +CREATE NONCLUSTERED INDEX [IX_guacamole_user_group_permission_entity_id] + ON [guacamole_user_group_permission] ([entity_id]); + +CREATE NONCLUSTERED INDEX [IX_guacamole_user_group_permission_affected_user_group_id] + ON [guacamole_user_group_permission] ([affected_user_group_id]); +GO + +-- -- Table of connection history records. Each record defines a specific user's -- session, including the connection used, the start time, and the end time -- (if any). @@ -682,12 +844,12 @@ GO -- -- Handle cascading deletion/updates of records in response to deletion of --- guacamole_user records, where such deletion is not already covered by +-- guacamole_entity records, where such deletion is not already covered by -- ON DELETE CASCADE or ON DELETE SET NULL. -- -CREATE TRIGGER [guacamole_delete_user] - ON [guacamole_user] +CREATE TRIGGER [guacamole_delete_entity] + ON [guacamole_entity] INSTEAD OF DELETE AS BEGIN @@ -696,13 +858,18 @@ AS BEGIN -- Delete all associated permissions not covered by ON DELETE CASCADE DELETE FROM [guacamole_user_permission] - WHERE - [user_id] IN (SELECT [user_id] FROM DELETED) - OR [user_id] IN (SELECT [user_id] FROM DELETED); + WHERE [entity_id] IN (SELECT [entity_id] FROM DELETED); + + DELETE FROM [guacamole_user_group_permission] + WHERE [entity_id] IN (SELECT [entity_id] FROM DELETED); + + -- Delete all associated group memberships not covered by ON DELETE CASCADE + DELETE FROM [guacamole_user_group_member] + WHERE [member_entity_id] IN (SELECT [entity_id] FROM DELETED); -- Perform original deletion - DELETE FROM [guacamole_user] - WHERE [user_id] IN (SELECT [user_id] FROM DELETED); + DELETE FROM [guacamole_entity] + WHERE [entity_id] IN (SELECT [entity_id] FROM DELETED); END GO @@ -746,6 +913,20 @@ AS BEGIN -- Do not take trigger into account when producing row counts for the DELETE SET NOCOUNT ON; + -- Delete all descendant connections + WITH [connection_groups] ([connection_group_id]) AS ( + SELECT [connection_group_id] FROM DELETED + UNION ALL + SELECT [guacamole_connection_group].[connection_group_id] + FROM [guacamole_connection_group] + JOIN [connection_groups] ON [connection_groups].[connection_group_id] = [guacamole_connection_group].[parent_id] + ) + DELETE FROM [guacamole_connection] + WHERE [parent_id] IN ( + SELECT [connection_group_id] + FROM [connection_groups] + ); + -- Delete all requested connection groups, including descendants WITH [connection_groups] ([connection_group_id]) AS ( SELECT [connection_group_id] FROM DELETED @@ -760,10 +941,6 @@ AS BEGIN FROM [connection_groups] ); - -- Delete all child connections - DELETE FROM [guacamole_connection] - WHERE [parent_id] IN (SELECT [connection_group_id] FROM DELETED); - END GO http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/1d0fcc17/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/002-create-admin-user.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/002-create-admin-user.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/002-create-admin-user.sql index 5b14651..dcb4257 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/002-create-admin-user.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/002-create-admin-user.sql @@ -18,43 +18,46 @@ -- -- Create default user "guacadmin" with password "guacadmin" +INSERT INTO [guacamole_entity] ([name], [type]) VALUES ('guacadmin', 'USER'); INSERT INTO [guacamole_user] ( - [username], + [entity_id], [password_hash], [password_salt], [password_date] ) -VALUES ( - 'guacadmin', +SELECT + [entity_id], 0xCA458A7D494E3BE824F5E1E175A1556C0F8EEF2C2D7DF3633BEC4A29C4411960, 0xFE24ADC5E11E2B25288D1704ABE67A79E342ECC26064CE69C5B3177795A82264, getdate() -); +FROM [guacamole_entity] WHERE [name] = 'guacadmin'; -- Grant this user all system permissions INSERT INTO [guacamole_system_permission] SELECT - [user_id], + [entity_id], [permission] FROM ( SELECT 'guacadmin', 'CREATE_CONNECTION' UNION SELECT 'guacadmin', 'CREATE_CONNECTION_GROUP' UNION SELECT 'guacadmin', 'CREATE_SHARING_PROFILE' UNION SELECT 'guacadmin', 'CREATE_USER' + UNION SELECT 'guacadmin', 'CREATE_USER_GROUP' UNION SELECT 'guacadmin', 'ADMINISTER' ) [permissions] ([username], [permission]) -JOIN [guacamole_user] ON [permissions].[username] = [guacamole_user].[username]; +JOIN [guacamole_entity] ON [permissions].[username] = [guacamole_entity].[name] AND [guacamole_entity].[type] = 'USER'; INSERT INTO [guacamole_user_permission] SELECT + [guacamole_entity].[entity_id], [guacamole_user].[user_id], - [affected].[user_id], [permission] FROM ( SELECT 'guacadmin', 'guacadmin', 'READ' UNION SELECT 'guacadmin', 'guacadmin', 'UPDATE' UNION SELECT 'guacadmin', 'guacadmin', 'ADMINISTER' ) [permissions] ([username], [affected_username], [permission]) -JOIN [guacamole_user] ON permissions.username = [guacamole_user].[username] -JOIN [guacamole_user] [affected] ON permissions.affected_username = affected.username; +JOIN [guacamole_entity] ON [permissions].[username] = [guacamole_entity].[name] AND [guacamole_entity].[type] = 'USER' +JOIN [guacamole_entity] [affected] ON [permissions].[affected_username] = [affected].[name] AND [guacamole_entity].[type] = 'USER' +JOIN [guacamole_user] ON [guacamole_user].[entity_id] = [affected].[entity_id]; GO http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/1d0fcc17/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/upgrade/upgrade-pre-1.0.0.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/upgrade/upgrade-pre-1.0.0.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/upgrade/upgrade-pre-1.0.0.sql index cb02dd5..6e9133a 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/upgrade/upgrade-pre-1.0.0.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/upgrade/upgrade-pre-1.0.0.sql @@ -18,6 +18,510 @@ -- -- +-- Add new system-level permission +-- + +EXEC sp_unbindrule 'guacamole_system_permission'; +DROP RULE [guacamole_system_permission_list]; +GO + +CREATE RULE [guacamole_system_permission_list] AS @list IN ( + 'CREATE_CONNECTION', + 'CREATE_CONNECTION_GROUP', + 'CREATE_SHARING_PROFILE', + 'CREATE_USER', + 'CREATE_USER_GROUP', + 'ADMINISTER' +); +GO + +EXEC sp_bindrule + 'guacamole_system_permission_list', + 'guacamole_system_permission'; +GO + +-- +-- Entity types +-- + +CREATE RULE [guacamole_entity_type_list] AS @list IN ( + 'USER', + 'USER_GROUP' +); +GO + +CREATE TYPE [guacamole_entity_type] FROM [nvarchar](16); +EXEC sp_bindrule + 'guacamole_entity_type_list', + 'guacamole_entity_type'; +GO + +-- +-- Table of base entities which may each be either a user or user group. Other +-- tables which represent qualities shared by both users and groups will point +-- to guacamole_entity, while tables which represent qualities specific to +-- users or groups will point to guacamole_user or guacamole_user_group. +-- + +CREATE TABLE [guacamole_entity] ( + + [entity_id] [int] IDENTITY(1,1) NOT NULL, + [name] [nvarchar](128) NOT NULL, + [type] [guacamole_entity_type] NOT NULL, + + CONSTRAINT [PK_guacamole_entity] + PRIMARY KEY CLUSTERED ([entity_id]), + + CONSTRAINT [AK_guacamole_entity_name_scope] + UNIQUE ([type], [name]) + +); +GO + +-- +-- Table of user groups. Each user group may have an arbitrary set of member +-- users and member groups, with those members inheriting the permissions +-- granted to that group. +-- + +CREATE TABLE [guacamole_user_group] ( + + [user_group_id] [int] IDENTITY(1,1) NOT NULL, + [entity_id] [int] NOT NULL, + + -- Group disabled status + [disabled] [bit] NOT NULL DEFAULT 0, + + CONSTRAINT [PK_guacamole_user_group] + PRIMARY KEY CLUSTERED ([user_group_id]), + + CONSTRAINT [guacamole_user_group_single_entity] + UNIQUE ([entity_id]), + + CONSTRAINT [guacamole_user_group_entity] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) + ON DELETE CASCADE + +); +GO + +-- +-- Table of users which are members of given user groups. +-- + +CREATE TABLE [guacamole_user_group_member] ( + + [user_group_id] [int] NOT NULL, + [member_entity_id] [int] NOT NULL, + + CONSTRAINT [PK_guacamole_user_group_member] + PRIMARY KEY CLUSTERED ([user_group_id], [member_entity_id]), + + -- Parent must be a user group + CONSTRAINT [guacamole_user_group_member_parent_id] + FOREIGN KEY ([user_group_id]) + REFERENCES [guacamole_user_group] ([user_group_id]) + ON DELETE CASCADE, + + -- Member may be either a user or a user group (any entity) + CONSTRAINT [guacamole_user_group_member_entity_id] + FOREIGN KEY ([member_entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) + -- ON DELETE CASCADE handled by guacamole_delete_entity trigger + +); +GO + +-- +-- Table of user group permissions. Each user group permission grants a user +-- or user group access to a another user group (the "affected" user group) for +-- a specific type of operation. +-- + +CREATE TABLE [guacamole_user_group_permission] ( + + [entity_id] [int] NOT NULL, + [affected_user_group_id] [int] NOT NULL, + [permission] [guacamole_object_permission] NOT NULL, + + CONSTRAINT [PK_guacamole_user_group_permission] + PRIMARY KEY CLUSTERED ([entity_id], [affected_user_group_id], [permission]), + + CONSTRAINT [FK_guacamole_user_group_permission_affected_user_group_id] + FOREIGN KEY ([affected_user_group_id]) + REFERENCES [guacamole_user_group] ([user_group_id]) + ON DELETE CASCADE, + + CONSTRAINT [FK_guacamole_user_group_permission_entity_id] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) + -- ON DELETE CASCADE handled by guacamole_delete_entity trigger + +); + +CREATE NONCLUSTERED INDEX [IX_guacamole_user_group_permission_entity_id] + ON [guacamole_user_group_permission] ([entity_id]); + +CREATE NONCLUSTERED INDEX [IX_guacamole_user_group_permission_affected_user_group_id] + ON [guacamole_user_group_permission] ([affected_user_group_id]); +GO + +-- +-- The guacamole_delete_entity trigger effectively replaces the +-- guacamole_delete_user trigger, which is no longer necessary and will cease +-- being correct after the columns of existing tables are updated. +-- + +DROP TRIGGER [guacamole_delete_user]; +GO + +-- +-- Modify guacamole_user table to use guacamole_entity as a base +-- + +-- Add new entity_id column +ALTER TABLE [guacamole_user] ADD [entity_id] [int]; +GO + +-- Create user entities for each guacamole_user entry +INSERT INTO [guacamole_entity] ([name], [type]) +SELECT [username], 'USER' FROM [guacamole_user]; +GO + +-- Update guacamole_user to point to corresponding guacamole_entity +UPDATE [guacamole_user] SET [entity_id] = ( + SELECT [entity_id] FROM [guacamole_entity] + WHERE + [username] = [guacamole_entity].[name] + AND type = 'USER' +); +GO + +-- The entity_id column should now be safely non-NULL +ALTER TABLE [guacamole_user] + ALTER COLUMN [entity_id] [int] NOT NULL; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE [guacamole_user] + ADD CONSTRAINT [AK_guacamole_user_single_entity] + UNIQUE ([entity_id]); + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE [guacamole_user] + ADD CONSTRAINT [FK_guacamole_user_entity] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) + ON DELETE CASCADE; + +-- The username column can now safely be removed +ALTER TABLE [guacamole_user] DROP [AK_guacamole_user_username]; +ALTER TABLE [guacamole_user] DROP COLUMN [username]; +GO + +-- +-- Modify guacamole_connection_permission to use guacamole_entity instead of +-- guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE [guacamole_connection_permission] ADD [entity_id] [int]; +GO + +-- Update guacamole_connection_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE [guacamole_connection_permission] SET [entity_id] = ( + SELECT [entity_id] FROM [guacamole_user] + WHERE [guacamole_user].[user_id] = [guacamole_connection_permission].[user_id] +); +GO + +-- The entity_id column should now be safely non-NULL +ALTER TABLE [guacamole_connection_permission] + ALTER COLUMN [entity_id] [int] NOT NULL; + +-- Remove user_id column +DROP INDEX [IX_guacamole_connection_permission_user_id] ON [guacamole_connection_permission]; +ALTER TABLE [guacamole_connection_permission] DROP [PK_guacamole_connection_permission]; +ALTER TABLE [guacamole_connection_permission] DROP [FK_guacamole_connection_permission_user_id]; +ALTER TABLE [guacamole_connection_permission] DROP COLUMN [user_id]; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE [guacamole_connection_permission] + ADD CONSTRAINT [FK_guacamole_connection_permission_entity_id] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) + ON DELETE CASCADE; + +CREATE NONCLUSTERED INDEX [IX_guacamole_connection_permission_entity_id] + ON [guacamole_connection_permission] ([entity_id]); + +-- Add new primary key which uses entity_id +ALTER TABLE [guacamole_connection_permission] + ADD CONSTRAINT [PK_guacamole_connection_permission] + PRIMARY KEY CLUSTERED ([entity_id], [connection_id], [permission]); +GO + +-- +-- Modify guacamole_connection_group_permission to use guacamole_entity instead +-- of guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE [guacamole_connection_group_permission] ADD [entity_id] [int]; +GO + +-- Update guacamole_connection_group_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE guacamole_connection_group_permission SET entity_id = ( + SELECT entity_id FROM guacamole_user + WHERE guacamole_user.user_id = guacamole_connection_group_permission.user_id +); +GO + +-- The entity_id column should now be safely non-NULL +ALTER TABLE [guacamole_connection_group_permission] + ALTER COLUMN [entity_id] [int] NOT NULL; + +-- Remove user_id column +DROP INDEX [IX_guacamole_connection_group_permission_user_id] ON [guacamole_connection_group_permission]; +ALTER TABLE [guacamole_connection_group_permission] DROP [PK_guacamole_connection_group_permission]; +ALTER TABLE [guacamole_connection_group_permission] DROP [FK_guacamole_connection_group_permission_user_id]; +ALTER TABLE [guacamole_connection_group_permission] DROP COLUMN user_id; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE [guacamole_connection_group_permission] + ADD CONSTRAINT [FK_guacamole_connection_group_permission_entity_id] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) + ON DELETE CASCADE; + +CREATE NONCLUSTERED INDEX [IX_guacamole_connection_group_permission_entity_id] + ON [guacamole_connection_group_permission] ([entity_id]); + +-- Add new primary key which uses entity_id +ALTER TABLE [guacamole_connection_group_permission] + ADD CONSTRAINT [PK_guacamole_connection_group_permission] + PRIMARY KEY CLUSTERED ([entity_id], [connection_group_id], [permission]); +GO + +-- +-- Modify guacamole_sharing_profile_permission to use guacamole_entity instead +-- of guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE [guacamole_sharing_profile_permission] ADD [entity_id] [int]; +GO + +-- Update guacamole_sharing_profile_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE guacamole_sharing_profile_permission SET entity_id = ( + SELECT entity_id FROM guacamole_user + WHERE guacamole_user.user_id = guacamole_sharing_profile_permission.user_id +); +GO + +-- The entity_id column should now be safely non-NULL +ALTER TABLE [guacamole_sharing_profile_permission] + ALTER COLUMN [entity_id] [int] NOT NULL; + +-- Remove user_id column +DROP INDEX [IX_guacamole_sharing_profile_permission_user_id] ON [guacamole_sharing_profile_permission]; +ALTER TABLE [guacamole_sharing_profile_permission] DROP [PK_guacamole_sharing_profile_permission]; +ALTER TABLE [guacamole_sharing_profile_permission] DROP [FK_guacamole_sharing_profile_permission_user_id]; +ALTER TABLE [guacamole_sharing_profile_permission] DROP COLUMN user_id; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE [guacamole_sharing_profile_permission] + ADD CONSTRAINT [FK_guacamole_sharing_profile_permission_entity_id] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) + ON DELETE CASCADE; + +CREATE NONCLUSTERED INDEX [IX_guacamole_sharing_profile_permission_entity_id] + ON [guacamole_sharing_profile_permission] ([entity_id]); + +-- Add new primary key which uses entity_id +ALTER TABLE [guacamole_sharing_profile_permission] + ADD CONSTRAINT [PK_guacamole_sharing_profile_permission] + PRIMARY KEY CLUSTERED ([entity_id], [sharing_profile_id], [permission]); +GO + +-- +-- Modify guacamole_user_permission to use guacamole_entity instead of +-- guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE [guacamole_user_permission] ADD [entity_id] [int]; +GO + +-- Update guacamole_user_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE guacamole_user_permission SET entity_id = ( + SELECT entity_id FROM guacamole_user + WHERE guacamole_user.user_id = guacamole_user_permission.user_id +); +GO + +-- The entity_id column should now be safely non-NULL +ALTER TABLE [guacamole_user_permission] + ALTER COLUMN [entity_id] [int] NOT NULL; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE [guacamole_user_permission] + ADD CONSTRAINT [FK_guacamole_user_permission_entity_id] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]); + -- ON DELETE CASCADE handled by guacamole_delete_entity trigger + +-- The affected_user_id column now has ON DELETE CASCADE +ALTER TABLE [guacamole_user_permission] DROP [FK_guacamole_user_permission_affected_user_id]; +ALTER TABLE [guacamole_user_permission] + ADD CONSTRAINT [FK_guacamole_user_permission_affected_user_id] + FOREIGN KEY ([affected_user_id]) + REFERENCES [guacamole_user] ([user_id]) + ON DELETE CASCADE; + +CREATE NONCLUSTERED INDEX [IX_guacamole_user_permission_entity_id] + ON [guacamole_user_permission] ([entity_id]); + +-- Remove user_id column +DROP INDEX [IX_guacamole_user_permission_user_id] ON [guacamole_user_permission]; +ALTER TABLE [guacamole_user_permission] DROP [PK_guacamole_user_permission]; +ALTER TABLE [guacamole_user_permission] DROP [FK_guacamole_user_permission_user_id]; +ALTER TABLE [guacamole_user_permission] DROP COLUMN user_id; + +-- Add new primary key which uses entity_id +ALTER TABLE [guacamole_user_permission] + ADD CONSTRAINT [PK_guacamole_user_permission] + PRIMARY KEY CLUSTERED ([entity_id], [affected_user_id], [permission]); +GO + +-- +-- Modify guacamole_system_permission to use guacamole_entity instead of +-- guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE [guacamole_system_permission] ADD [entity_id] [int]; +GO + +-- Update guacamole_system_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE [guacamole_system_permission] SET [entity_id] = ( + SELECT [entity_id] FROM [guacamole_user] + WHERE [guacamole_user].[user_id] = [guacamole_system_permission].[user_id] +); +GO + +-- The entity_id column should now be safely non-NULL +ALTER TABLE [guacamole_system_permission] + ALTER COLUMN [entity_id] [int] NOT NULL; + +-- Remove user_id column +DROP INDEX [IX_guacamole_system_permission_user_id] ON [guacamole_system_permission]; +ALTER TABLE [guacamole_system_permission] DROP [PK_guacamole_system_permission]; +ALTER TABLE [guacamole_system_permission] DROP [FK_guacamole_system_permission_user_id]; +ALTER TABLE [guacamole_system_permission] DROP COLUMN [user_id]; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE [guacamole_system_permission] + ADD CONSTRAINT [FK_guacamole_system_permission_entity_id] + FOREIGN KEY ([entity_id]) + REFERENCES [guacamole_entity] ([entity_id]) + ON DELETE CASCADE; + +CREATE NONCLUSTERED INDEX [IX_guacamole_system_permission_entity_id] + ON [guacamole_system_permission] ([entity_id]); + +-- Add new primary key which uses entity_id +ALTER TABLE [guacamole_system_permission] + ADD CONSTRAINT [PK_guacamole_system_permission] + PRIMARY KEY CLUSTERED ([entity_id], [permission]); +GO + +-- +-- Handle cascading deletion/updates of records in response to deletion of +-- guacamole_entity records, where such deletion is not already covered by +-- ON DELETE CASCADE or ON DELETE SET NULL. +-- + +CREATE TRIGGER [guacamole_delete_entity] + ON [guacamole_entity] + INSTEAD OF DELETE +AS BEGIN + + -- Do not take trigger into account when producing row counts for the DELETE + SET NOCOUNT ON; + + -- Delete all associated permissions not covered by ON DELETE CASCADE + DELETE FROM [guacamole_user_permission] + WHERE [entity_id] IN (SELECT [entity_id] FROM DELETED); + + DELETE FROM [guacamole_user_group_permission] + WHERE [entity_id] IN (SELECT [entity_id] FROM DELETED); + + -- Delete all associated group memberships not covered by ON DELETE CASCADE + DELETE FROM [guacamole_user_group_member] + WHERE [member_entity_id] IN (SELECT [entity_id] FROM DELETED); + + -- Perform original deletion + DELETE FROM [guacamole_entity] + WHERE [entity_id] IN (SELECT [entity_id] FROM DELETED); + +END +GO + +-- +-- Update guacamole_delete_connection_group trigger to remove descendant +-- connections first. +-- + +DROP TRIGGER [guacamole_delete_connection_group]; +GO + +CREATE TRIGGER [guacamole_delete_connection_group] + ON [guacamole_connection_group] + INSTEAD OF DELETE +AS BEGIN + + -- Do not take trigger into account when producing row counts for the DELETE + SET NOCOUNT ON; + + -- Delete all descendant connections + WITH [connection_groups] ([connection_group_id]) AS ( + SELECT [connection_group_id] FROM DELETED + UNION ALL + SELECT [guacamole_connection_group].[connection_group_id] + FROM [guacamole_connection_group] + JOIN [connection_groups] ON [connection_groups].[connection_group_id] = [guacamole_connection_group].[parent_id] + ) + DELETE FROM [guacamole_connection] + WHERE [parent_id] IN ( + SELECT [connection_group_id] + FROM [connection_groups] + ); + + -- Delete all requested connection groups, including descendants + WITH [connection_groups] ([connection_group_id]) AS ( + SELECT [connection_group_id] FROM DELETED + UNION ALL + SELECT [guacamole_connection_group].[connection_group_id] + FROM [guacamole_connection_group] + JOIN [connection_groups] ON [connection_groups].[connection_group_id] = [guacamole_connection_group].[parent_id] + ) + DELETE FROM [guacamole_connection_group] + WHERE [connection_group_id] IN ( + SELECT [connection_group_id] + FROM [connection_groups] + ); + +END +GO + +-- -- Table of arbitrary user attributes. Each attribute is simply a name/value -- pair associated with a user. Arbitrary attributes are defined by other -- extensions. Attributes defined by this extension will be mapped to @@ -46,6 +550,34 @@ CREATE NONCLUSTERED INDEX [IX_guacamole_user_attribute_user_id] GO -- +-- Table of arbitrary user group attributes. Each attribute is simply a +-- name/value pair associated with a user group. Arbitrary attributes are +-- defined by other extensions. Attributes defined by this extension will be +-- mapped to properly-typed columns of a specific table. +-- + +CREATE TABLE [guacamole_user_group_attribute] ( + + [user_group_id] [int] NOT NULL, + [attribute_name] [nvarchar](128) NOT NULL, + [attribute_value] [nvarchar](4000) NOT NULL, + + CONSTRAINT [PK_guacamole_user_group_attribute] + PRIMARY KEY CLUSTERED ([user_group_id], [attribute_name]), + + CONSTRAINT [FK_guacamole_user_attribute_user_group_id] + FOREIGN KEY ([user_group_id]) + REFERENCES [guacamole_user_group] ([user_group_id]) + ON DELETE CASCADE + +); + +CREATE NONCLUSTERED INDEX [IX_guacamole_user_group_attribute_user_id] + ON [guacamole_user_group_attribute] ([user_group_id]) + INCLUDE ([attribute_name], [attribute_value]); +GO + +-- -- Table of arbitrary connection attributes. Each attribute is simply a -- name/value pair associated with a connection. Arbitrary attributes are -- defined by other extensions. Attributes defined by this extension will be
