GUACAMOLE-423: Merge bump version numbers for all modified components to 0.9.14.
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 f7a34b1..91ba377 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
@@ -1,468 +1,648 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-/**
- * Turn on ANSI_NULLS for the entire DB to make it ISO-compliant.
- */
-ALTER DATABASE CURRENT SET ANSI_NULLS ON;
-GO
-
-/**
- * Turn on QUOTED_IDENTIFIER for the entire DB.
- */
-ALTER DATABASE CURRENT SET QUOTED_IDENTIFIER ON;
-GO
-
-/**
- * List for permission data type.
- */
-CREATE RULE [guacamole_permission_list]
- AS
- @list IN ('READ','UPDATE','DELETE','ADMINISTER');
-GO
-
-/**
- * List for system permission data type.
- */
-CREATE RULE [guacamole_system_permission_list]
- AS
- @list IN ('CREATE_CONNECTION',
- 'CREATE_CONNECTION_GROUP',
- 'CREATE_SHARING_PROFILE',
- 'CREATE_USER',
- 'ADMINISTER');
-GO
-
-/**
- * The permission data type.
- */
-CREATE TYPE [guacamole_permission] FROM [nvarchar](10) NOT NULL;
-EXEC sp_bindrule 'guacamole_permission_list','guacamole_permission';
-
-/**
- * The system permission data type.
- */
-CREATE TYPE [guacamole_system_permission] FROM [nvarchar](32) NOT NULL;
-EXEC sp_bindrule 'guacamole_system_permission_list','guacamole_system_permission';
-GO
-
-/**
- * The connection_group table stores organizational and balancing groups.
- */
-CREATE TABLE [guacamole_connection_group](
- [connection_group_id] [int] IDENTITY(1,1) NOT NULL,
- [parent_id] [int] NULL,
- [connection_group_name] [nvarchar](128) NOT NULL,
- [type] [nvarchar](32) NOT NULL,
- [max_connections] [int] NULL,
- [max_connections_per_user] [int] NULL,
- [enable_session_affinity] [bit] NOT NULL,
-
- CONSTRAINT [PK_guacmaole_connection_group] PRIMARY KEY CLUSTERED
- ([connection_group_id] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for connection_group table.
- */
-ALTER TABLE [guacamole_connection_group]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_group_connection_group_id] FOREIGN KEY([parent_id])
- REFERENCES [guacamole_connection_group] ([connection_group_id]);
-ALTER TABLE [guacamole_connection_group]
- CHECK CONSTRAINT [FK_guacamole_connection_group_connection_group_id];
-ALTER TABLE [guacamole_connection_group]
- WITH CHECK ADD CONSTRAINT [CK_guacamole_connection_group_type]
- CHECK (([type]='BALANCING' OR [type]='ORGANIZATIONAL'));
-ALTER TABLE [guacamole_connection_group]
- CHECK CONSTRAINT [CK_guacamole_connection_group_type];
-
-/**
- * Default values for connection_group table.
- */
-ALTER TABLE [guacamole_connection_group]
- ADD CONSTRAINT [DF_guacamole_connection_group_type] DEFAULT (N'ORGANIZATIONAL') FOR [type];
-ALTER TABLE [guacamole_connection_group]
- ADD CONSTRAINT [DF_guacamole_connection_group_enable_session_affinity] DEFAULT ((0)) FOR [enable_session_affinity];
-GO
-
-/**
- * The connection table, for storing connections and attributes.
- */
-CREATE TABLE [guacamole_connection](
- [connection_id] [int] IDENTITY(1,1) NOT NULL,
- [connection_name] [nvarchar](128) NOT NULL,
- [parent_id] [int] NULL,
- [protocol] [nvarchar](32) NOT NULL,
- [proxy_port] [int] NULL,
- [proxy_hostname] [nvarchar](512) NULL,
- [proxy_encryption_method] [nvarchar](4) NULL,
- [max_connections] [int] NULL,
- [max_connections_per_user] [int] NULL,
- [connection_weight] [int] NULL,
- [failover_only] [bit] NOT NULL,
-
- CONSTRAINT [PK_guacamole_connection] PRIMARY KEY CLUSTERED
- ([connection_id] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-ALTER TABLE [guacamole_connection]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_connection_group] FOREIGN KEY([parent_id])
- REFERENCES [guacamole_connection_group] ([connection_group_id]);
-ALTER TABLE [guacamole_connection]
- CHECK CONSTRAINT [FK_guacamole_connection_connection_group];
-ALTER TABLE [guacamole_connection]
- WITH CHECK ADD CONSTRAINT [CK_proxy_encryption_method]
- CHECK (([proxy_encryption_method]='SSL' OR [proxy_encryption_method]='NONE'));
-ALTER TABLE [guacamole_connection]
- CHECK CONSTRAINT [CK_proxy_encryption_method];
-ALTER TABLE [guacamole_connection]
- ADD CONSTRAINT [DF_guacamole_connection_failover_only] DEFAULT ((0)) FOR [failover_only];
-GO
-
-/**
- * The user table stores user accounts, passwords, and properties.
- */
-CREATE TABLE [guacamole_user](
- [user_id] [int] IDENTITY(1,1) NOT NULL,
- [username] [nvarchar](128) NOT NULL,
- [password_hash] [binary](32) NOT NULL,
- [password_salt] [binary](32) NULL,
- [password_date] [datetime] NOT NULL,
- [disabled] [bit] NOT NULL,
- [expired] [bit] NOT NULL,
- [access_window_start] [time](7) NULL,
- [access_window_end] [time](7) NULL,
- [valid_from] [date] NULL,
- [valid_until] [date] NULL,
- [timezone] [nvarchar](64) NULL,
- [full_name] [nvarchar](256) NULL,
- [email_address] [nvarchar](256) NULL,
- [organization] [nvarchar](256) NULL,
- [organizational_role] [nvarchar](256) NULL,
-
- CONSTRAINT [PK_guacamole_user] PRIMARY KEY CLUSTERED
- ([user_id] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Defaults for user table
- */
-ALTER TABLE [guacamole_user]
- ADD CONSTRAINT [DF_guacamole_user_disabled] DEFAULT ((0)) FOR [disabled];
-ALTER TABLE [guacamole_user]
- ADD CONSTRAINT [DF_guacamole_user_expired] DEFAULT ((0)) FOR [expired];
-GO
-
-/**
- * The sharing_profile table stores profiles that allow
- * connections to be shared amongst multiple users.
- */
-CREATE TABLE [guacamole_sharing_profile](
- [sharing_profile_id] [int] IDENTITY(1,1) NOT NULL,
- [sharing_profile_name] [nvarchar](128) NOT NULL,
- [primary_connection_id] [int] NOT NULL,
-
- CONSTRAINT [PK_guacamole_sharing_profile] PRIMARY KEY CLUSTERED
- ([sharing_profile_id] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for sharing_profile table.
- */
-ALTER TABLE [guacamole_sharing_profile]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_sharing_profile_connection] FOREIGN KEY([primary_connection_id])
- REFERENCES [guacamole_connection] ([connection_id])
- ON UPDATE CASCADE
- ON DELETE CASCADE;
-ALTER TABLE [guacamole_sharing_profile]
- CHECK CONSTRAINT [FK_guacamole_sharing_profile_connection];
-GO
-
-/**
- * The connection_parameter table stores parameters for
- * connection objects.
- */
-CREATE TABLE [guacamole_connection_parameter](
- [connection_id] [int] NOT NULL,
- [parameter_name] [nvarchar](128) NOT NULL,
- [parameter_value] [nvarchar](4000) NOT NULL,
-
- CONSTRAINT [PK_guacamole_connection_parameter] PRIMARY KEY CLUSTERED
- ([connection_id] ASC, [parameter_name] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for the connection_parameter table.
- */
-ALTER TABLE [guacamole_connection_parameter]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_parameter_connection] FOREIGN KEY([connection_id])
- REFERENCES [guacamole_connection] ([connection_id])
- ON UPDATE CASCADE
- ON DELETE CASCADE;
-ALTER TABLE [guacamole_connection_parameter]
- CHECK CONSTRAINT [FK_guacamole_connection_parameter_connection];
-GO
-
-/**
- * The sharing_profile_parameter table stores parameters
- * for sharing_profile objects.
- */
-CREATE TABLE [guacamole_sharing_profile_parameter](
- [sharing_profile_id] [int] NOT NULL,
- [parameter_name] [nvarchar](128) NOT NULL,
- [parameter_value] [nvarchar](4000) NOT NULL,
-
- CONSTRAINT [PK_guacamole_sharing_profile_parameter] PRIMARY KEY CLUSTERED
- ([sharing_profile_id] ASC, [parameter_name] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for the sharing_profile_parameter
- * table.
- */
-ALTER TABLE [guacamole_sharing_profile_parameter]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_sharing_profile_parameter_sharing_profile] FOREIGN KEY([sharing_profile_id])
- REFERENCES [guacamole_sharing_profile] ([sharing_profile_id])
- ON UPDATE CASCADE
- ON DELETE CASCADE;
-ALTER TABLE [guacamole_sharing_profile_parameter]
- CHECK CONSTRAINT [FK_guacamole_sharing_profile_parameter_sharing_profile];
-GO
-
-/**
- * The connection_permission table stores permission
- * mappings for connection objects.
- */
-CREATE TABLE [guacamole_connection_permission](
- [user_id] [int] NOT NULL,
- [connection_id] [int] NOT NULL,
- [permission] [guacamole_permission] NOT NULL,
-
- CONSTRAINT [PK_guacamole_connection_permission] PRIMARY KEY CLUSTERED
- ([user_id] ASC, [connection_id] ASC, [permission] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for the connection_permission table.
- */
-ALTER TABLE [guacamole_connection_permission]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_permission_connection1] FOREIGN KEY([connection_id])
- REFERENCES [guacamole_connection] ([connection_id])
- ON UPDATE CASCADE
- ON DELETE CASCADE;
-ALTER TABLE [guacamole_connection_permission]
- CHECK CONSTRAINT [FK_guacamole_connection_permission_connection1];
-ALTER TABLE [guacamole_connection_permission]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_permission_user1] FOREIGN KEY([user_id])
- REFERENCES [guacamole_user] ([user_id])
- ON UPDATE CASCADE
- ON DELETE CASCADE;
-ALTER TABLE [guacamole_connection_permission]
- CHECK CONSTRAINT [FK_guacamole_connection_permission_user1];
-GO
-
-/**
- * The connection_group_permission table stores permission mappings for
- * connection_group objects.
- */
-CREATE TABLE [guacamole_connection_group_permission](
- [user_id] [int] NOT NULL,
- [connection_group_id] [int] NOT NULL,
- [permission] [guacamole_permission] NOT NULL,
-
- CONSTRAINT [PK_guacamole_connection_group_permission] PRIMARY KEY CLUSTERED
- ([user_id] ASC, [connection_group_id] ASC, [permission] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for the connection_group_permission table.
- */
-ALTER TABLE [guacamole_connection_group_permission]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_group_permission_connection_group] FOREIGN KEY([connection_group_id])
- REFERENCES [guacamole_connection_group] ([connection_group_id])
- ON UPDATE CASCADE
- ON DELETE CASCADE;
-ALTER TABLE [guacamole_connection_group_permission]
- CHECK CONSTRAINT [FK_guacamole_connection_group_permission_connection_group];
-ALTER TABLE [guacamole_connection_group_permission]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_group_permission_user] FOREIGN KEY([user_id])
- REFERENCES [guacamole_user] ([user_id])
- ON UPDATE CASCADE
- ON DELETE CASCADE;
-ALTER TABLE [guacamole_connection_group_permission]
- CHECK CONSTRAINT [FK_guacamole_connection_group_permission_user];
-GO
-
-/**
- * The sharing_profile_permission table stores permission
- * mappings for sharing_profile objects.
- */
-CREATE TABLE [guacamole_sharing_profile_permission](
- [user_id] [int] NOT NULL,
- [sharing_profile_id] [int] NOT NULL,
- [permission] [guacamole_permission] NOT NULL,
-
- CONSTRAINT [PK_guacamole_sharing_profile_permission] PRIMARY KEY CLUSTERED
- ([user_id] ASC, [sharing_profile_id] ASC, [permission] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for the sharing_profile_permission table.
- */
-ALTER TABLE [guacamole_sharing_profile_permission]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_sharing_profile_permission_sharing_profile] FOREIGN KEY([sharing_profile_id])
- REFERENCES [guacamole_sharing_profile] ([sharing_profile_id])
- ON UPDATE CASCADE
- ON DELETE CASCADE;
-ALTER TABLE [guacamole_sharing_profile_permission]
- CHECK CONSTRAINT [FK_guacamole_sharing_profile_permission_sharing_profile];
-ALTER TABLE [guacamole_sharing_profile_permission]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_sharing_profile_permission_user] FOREIGN KEY([user_id])
- REFERENCES [guacamole_user] ([user_id])
- ON UPDATE CASCADE
- ON DELETE CASCADE;
-ALTER TABLE [guacamole_sharing_profile_permission]
- CHECK CONSTRAINT [FK_guacamole_sharing_profile_permission_user];
-GO
-
-/**
- * The system_permission table stores permission mappings
- * for system-level operations.
- */
-CREATE TABLE [guacamole_system_permission](
- [user_id] [int] NOT NULL,
- [permission] [guacamole_system_permission] NOT NULL,
-
- CONSTRAINT [PK_guacamole_system_permission] PRIMARY KEY CLUSTERED
- ([user_id] ASC, [permission] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for system_permission table.
- */
-ALTER TABLE [guacamole_system_permission]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_system_permission_user] FOREIGN KEY([user_id])
- REFERENCES [guacamole_user] ([user_id])
- ON UPDATE CASCADE
- ON DELETE CASCADE;
-ALTER TABLE [guacamole_system_permission]
- CHECK CONSTRAINT [FK_guacamole_system_permission_user];
-GO
-
-/**
- * The user_permission table stores permission mappings
- * for users to other users.
- */
-CREATE TABLE [guacamole_user_permission](
- [user_id] [int] NOT NULL,
- [affected_user_id] [int] NOT NULL,
- [permission] [guacamole_permission] NOT NULL,
-
- CONSTRAINT [PK_guacamole_user_permission] PRIMARY KEY CLUSTERED
- ([user_id] ASC, [affected_user_id] ASC, [permission] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for user_permission table.
- */
-ALTER TABLE [guacamole_user_permission]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_user_permission_user] FOREIGN KEY([user_id])
- REFERENCES [guacamole_user] ([user_id])
- ON UPDATE CASCADE
- ON DELETE CASCADE;
-ALTER TABLE [guacamole_user_permission]
- CHECK CONSTRAINT [FK_guacamole_user_permission_user];
-ALTER TABLE [guacamole_user_permission]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_user_permission_user1] FOREIGN KEY([affected_user_id])
- REFERENCES [guacamole_user] ([user_id]);
-ALTER TABLE [guacamole_user_permission]
- CHECK CONSTRAINT [FK_guacamole_user_permission_user1];
-GO
-
-/**
- * The connection_history table stores records for historical
- * connections.
- */
-CREATE TABLE [guacamole_connection_history](
- [history_id] [int] IDENTITY(1,1) NOT NULL,
- [user_id] [int] NULL,
- [username] [nvarchar](128) NOT NULL,
- [remote_host] [nvarchar](256) NULL,
- [connection_id] [int] NULL,
- [connection_name] [nvarchar](128) NOT NULL,
- [sharing_profile_id] [int] NULL,
- [sharing_profile_name] [nvarchar](128) NULL,
- [start_date] [datetime] NOT NULL,
- [end_date] [datetime] NULL,
-
- CONSTRAINT [PK_guacamole_connection_history] PRIMARY KEY CLUSTERED
- ([history_id] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for connection_history table
- */
-ALTER TABLE [guacamole_connection_history]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_history_connection] FOREIGN KEY([connection_id])
- REFERENCES [guacamole_connection] ([connection_id])
- ON UPDATE CASCADE
- ON DELETE SET NULL;
-ALTER TABLE [guacamole_connection_history]
- CHECK CONSTRAINT [FK_guacamole_connection_history_connection];
-ALTER TABLE [guacamole_connection_history]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_history_sharing_profile] FOREIGN KEY([sharing_profile_id])
- REFERENCES [guacamole_sharing_profile] ([sharing_profile_id]);
-ALTER TABLE [guacamole_connection_history]
- CHECK CONSTRAINT [FK_guacamole_connection_history_sharing_profile];
-ALTER TABLE [guacamole_connection_history]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_connection_history_user] FOREIGN KEY([user_id])
- REFERENCES [guacamole_user] ([user_id])
- ON UPDATE CASCADE
- ON DELETE SET NULL;
-ALTER TABLE [guacamole_connection_history]
- CHECK CONSTRAINT [FK_guacamole_connection_history_user];
-GO
-
-/**
- * The user_password_history table stores password history
- * for users, allowing for enforcing rules associated with
- * reuse of passwords.
- */
-CREATE TABLE [guacamole_user_password_history](
- [password_history_id] [int] IDENTITY(1,1) NOT NULL,
- [user_id] [int] NOT NULL,
- [password_hash] [binary](32) NOT NULL,
- [password_salt] [binary](32) NULL,
- [password_date] [datetime] NOT NULL,
-
- CONSTRAINT [PK_guacamole_user_password_history] PRIMARY KEY CLUSTERED
- ([password_history_id] ASC) ON [PRIMARY]
-) ON [PRIMARY];
-
-/**
- * Foreign keys for user_password_history table
- */
-ALTER TABLE [guacamole_user_password_history]
- WITH CHECK ADD CONSTRAINT [FK_guacamole_user_password_history_user] FOREIGN KEY([user_id])
- REFERENCES [guacamole_user] ([user_id])
- ON UPDATE CASCADE
- ON DELETE CASCADE;
-ALTER TABLE [guacamole_user_password_history]
- CHECK CONSTRAINT [FK_guacamole_user_password_history_user];
-GO
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+
+--
+-- Connection group types
+--
+
+CREATE RULE [guacamole_connection_group_type_list] AS @list IN (
+ 'ORGANIZATIONAL',
+ 'BALANCING'
+);
+GO
+
+CREATE TYPE [guacamole_connection_group_type] FROM [nvarchar](16);
+EXEC sp_bindrule
+ 'guacamole_connection_group_type_list',
+ 'guacamole_connection_group_type';
+GO
+
+--
+-- Object permission types
+--
+
+CREATE RULE [guacamole_object_permission_list] AS @list IN (
+ 'READ',
+ 'UPDATE',
+ 'DELETE',
+ 'ADMINISTER'
+);
+GO
+
+CREATE TYPE [guacamole_object_permission] FROM [nvarchar](16);
+EXEC sp_bindrule
+ 'guacamole_object_permission_list',
+ 'guacamole_object_permission';
+GO
+
+--
+-- System permission types
+--
+
+CREATE RULE [guacamole_system_permission_list] AS @list IN (
+ 'CREATE_CONNECTION',
+ 'CREATE_CONNECTION_GROUP',
+ 'CREATE_SHARING_PROFILE',
+ 'CREATE_USER',
+ 'ADMINISTER'
+);
+GO
+
+CREATE TYPE [guacamole_system_permission] FROM [nvarchar](32);
+EXEC sp_bindrule
+ 'guacamole_system_permission_list',
+ 'guacamole_system_permission';
+GO
+
+--
+-- Guacamole proxy (guacd) encryption methods.
+--
+
+CREATE RULE [guacamole_proxy_encryption_method_list] AS @list IN (
+ 'NONE',
+ 'SSL'
+);
+GO
+
+CREATE TYPE [guacamole_proxy_encryption_method] FROM [nvarchar](8);
+EXEC sp_bindrule
+ 'guacamole_proxy_encryption_method_list',
+ 'guacamole_proxy_encryption_method';
+GO
+
+--
+-- Table of connection groups. Each connection group has a name, type, and
+-- optional parent connection group.
+--
+
+CREATE TABLE [guacamole_connection_group] (
+
+ [connection_group_id] [int] IDENTITY(1,1) NOT NULL,
+ [parent_id] [int],
+ [connection_group_name] [nvarchar](128) NOT NULL,
+ [type] [guacamole_connection_group_type]
+ NOT NULL DEFAULT 'ORGANIZATIONAL',
+
+ -- Concurrency limits
+ [max_connections] [int],
+ [max_connections_per_user] [int],
+ [enable_session_affinity] [bit] NOT NULL DEFAULT 0,
+
+ CONSTRAINT [PK_guacamole_connection_group]
+ PRIMARY KEY CLUSTERED ([connection_group_id]),
+
+ CONSTRAINT [AK_guacamole_connection_group_name_parent]
+ UNIQUE ([connection_group_name], [parent_id]),
+
+ CONSTRAINT [FK_guacamole_connection_group_parent_id]
+ FOREIGN KEY ([parent_id])
+ REFERENCES [guacamole_connection_group] ([connection_group_id])
+ -- ON DELETE CASCADE handled by guacamole_delete_connection_group trigger
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_group_parent_id]
+ ON [guacamole_connection_group] ([parent_id]);
+GO
+
+--
+-- Table of connections. Each connection has a name, protocol, and
+-- associated set of parameters. A connection may belong to a connection group.
+--
+
+CREATE TABLE [guacamole_connection] (
+
+ [connection_id] [int] IDENTITY(1,1) NOT NULL,
+ [connection_name] [nvarchar](128) NOT NULL,
+ [parent_id] [int],
+ [protocol] [nvarchar](32) NOT NULL,
+
+ -- Concurrency limits
+ [max_connections] [int],
+ [max_connections_per_user] [int],
+
+ -- Connection Weight
+ [connection_weight] [int],
+ [failover_only] [bit] NOT NULL DEFAULT 0,
+
+ -- Guacamole proxy (guacd) overrides
+ [proxy_port] [int],
+ [proxy_hostname] [nvarchar](512),
+ [proxy_encryption_method] [guacamole_proxy_encryption_method],
+
+ CONSTRAINT [PK_guacamole_connection]
+ PRIMARY KEY CLUSTERED ([connection_id]),
+
+ CONSTRAINT [AK_guacamole_connection_name_parent]
+ UNIQUE ([connection_name], [parent_id]),
+
+ CONSTRAINT [FK_guacamole_connection_parent_id]
+ FOREIGN KEY ([parent_id])
+ REFERENCES [guacamole_connection_group] ([connection_group_id])
+ -- ON DELETE CASCADE handled by guacamole_delete_connection_group trigger
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_parent_id]
+ ON [guacamole_connection] ([parent_id]);
+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
+-- providing the salt.
+--
+
+CREATE TABLE [guacamole_user] (
+
+ [user_id] [int] IDENTITY(1,1) NOT NULL,
+
+ -- Username and optionally-salted password
+ [username] [nvarchar](128) NOT NULL,
+ [password_hash] [binary](32) NOT NULL,
+ [password_salt] [binary](32),
+ [password_date] [datetime] NOT NULL,
+
+ -- Account disabled/expired status
+ [disabled] [bit] NOT NULL DEFAULT 0,
+ [expired] [bit] NOT NULL DEFAULT 0,
+
+ -- Time-based access restriction
+ [access_window_start] [time](7),
+ [access_window_end] [time](7),
+
+ -- Date-based access restriction
+ [valid_from] [date],
+ [valid_until] [date],
+
+ -- Timezone used for all date/time comparisons and interpretation
+ [timezone] [nvarchar](64),
+
+ -- Profile information
+ [full_name] [nvarchar](256),
+ [email_address] [nvarchar](256),
+ [organization] [nvarchar](256),
+ [organizational_role] [nvarchar](256),
+
+ CONSTRAINT [PK_guacamole_user]
+ PRIMARY KEY CLUSTERED ([user_id]),
+
+ CONSTRAINT [AK_guacamole_user_username]
+ UNIQUE ([username])
+
+);
+GO
+
+--
+-- Table of sharing profiles. Each sharing profile has a name, associated set
+-- of parameters, and a primary connection. The primary connection is the
+-- connection that the sharing profile shares, and the parameters dictate the
+-- restrictions/features which apply to the user joining the connection via the
+-- sharing profile.
+--
+
+CREATE TABLE [guacamole_sharing_profile] (
+
+ [sharing_profile_id] [int] IDENTITY(1,1) NOT NULL,
+ [sharing_profile_name] [nvarchar](128) NOT NULL,
+ [primary_connection_id] [int] NOT NULL,
+
+ CONSTRAINT [PK_guacamole_sharing_profile]
+ PRIMARY KEY CLUSTERED ([sharing_profile_id]),
+
+ CONSTRAINT [AK_guacamole_sharing_profile_name_primary_connection]
+ UNIQUE ([sharing_profile_name], [primary_connection_id]),
+
+ CONSTRAINT [FK_guacamole_sharing_profile_primary_connection_id]
+ FOREIGN KEY ([primary_connection_id])
+ REFERENCES [guacamole_connection] ([connection_id])
+ -- ON DELETE CASCADE handled by guacamole_delete_connection trigger
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_sharing_profile_primary_connection_id]
+ ON [guacamole_sharing_profile] ([primary_connection_id]);
+GO
+
+--
+-- Table of connection parameters. Each parameter is simply a name/value pair
+-- associated with a connection.
+--
+
+CREATE TABLE [guacamole_connection_parameter] (
+
+ [connection_id] [int] NOT NULL,
+ [parameter_name] [nvarchar](128) NOT NULL,
+ [parameter_value] [nvarchar](4000) NOT NULL,
+
+ CONSTRAINT [PK_guacamole_connection_parameter]
+ PRIMARY KEY CLUSTERED ([connection_id], [parameter_name]),
+
+ CONSTRAINT [FK_guacamole_connection_parameter_connection_id]
+ FOREIGN KEY ([connection_id])
+ REFERENCES [guacamole_connection] ([connection_id])
+ ON DELETE CASCADE
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_parameter_connection_id]
+ ON [guacamole_connection_parameter] ([connection_id]);
+GO
+
+--
+-- Table of sharing profile parameters. Each parameter is simply
+-- name/value pair associated with a sharing profile. These parameters dictate
+-- the restrictions/features which apply to the user joining the associated
+-- connection via the sharing profile.
+--
+
+CREATE TABLE [guacamole_sharing_profile_parameter] (
+
+ [sharing_profile_id] [int] NOT NULL,
+ [parameter_name] [nvarchar](128) NOT NULL,
+ [parameter_value] [nvarchar](4000) NOT NULL,
+
+ CONSTRAINT [PK_guacamole_sharing_profile_parameter]
+ PRIMARY KEY CLUSTERED ([sharing_profile_id], [parameter_name]),
+
+ CONSTRAINT [FK_guacamole_sharing_profile_parameter_connection_id]
+ FOREIGN KEY ([sharing_profile_id])
+ REFERENCES [guacamole_sharing_profile] ([sharing_profile_id])
+ ON DELETE CASCADE
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_sharing_profile_parameter_sharing_profile_id]
+ ON [guacamole_sharing_profile_parameter] ([sharing_profile_id]);
+GO
+
+--
+-- Table of connection permissions. Each connection permission grants a user
+-- specific access to a connection.
+--
+
+CREATE TABLE [guacamole_connection_permission] (
+
+ [user_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]),
+
+ 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])
+ ON DELETE CASCADE
+
+);
+
+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]);
+GO
+
+--
+-- Table of connection group permissions. Each group permission grants a user
+-- specific access to a connection group.
+--
+
+CREATE TABLE [guacamole_connection_group_permission] (
+
+ [user_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]),
+
+ 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])
+ ON DELETE CASCADE
+
+);
+
+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]);
+GO
+
+--
+-- Table of sharing profile permissions. Each sharing profile permission grants
+-- a user specific access to a sharing profile.
+--
+
+CREATE TABLE [guacamole_sharing_profile_permission] (
+
+ [user_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]),
+
+ 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])
+ ON DELETE CASCADE
+
+);
+
+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]);
+GO
+
+--
+-- Table of system permissions. Each system permission grants a user a
+-- system-level privilege of some kind.
+--
+
+CREATE TABLE [guacamole_system_permission] (
+
+ [user_id] [int] NOT NULL,
+ [permission] [guacamole_system_permission] NOT NULL,
+
+ CONSTRAINT [PK_guacamole_system_permission]
+ PRIMARY KEY CLUSTERED ([user_id], [permission]),
+
+ CONSTRAINT [FK_guacamole_system_permission_user_id]
+ FOREIGN KEY ([user_id])
+ REFERENCES [guacamole_user] ([user_id])
+ ON DELETE CASCADE
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_system_permission_user_id]
+ ON [guacamole_system_permission] ([user_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.
+--
+
+CREATE TABLE [guacamole_user_permission] (
+
+ [user_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]),
+
+ 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
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_user_permission_user_id]
+ ON [guacamole_user_permission] ([user_id]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_user_permission_affected_user_id]
+ ON [guacamole_user_permission] ([affected_user_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).
+--
+
+CREATE TABLE [guacamole_connection_history] (
+
+ [history_id] [int] IDENTITY(1,1) NOT NULL,
+ [user_id] [int],
+ [username] [nvarchar](128) NOT NULL,
+ [remote_host] [nvarchar](256),
+ [connection_id] [int],
+ [connection_name] [nvarchar](128) NOT NULL,
+ [sharing_profile_id] [int],
+ [sharing_profile_name] [nvarchar](128),
+ [start_date] [datetime] NOT NULL,
+ [end_date] [datetime],
+
+ CONSTRAINT [PK_guacamole_connection_history]
+ PRIMARY KEY CLUSTERED ([history_id]),
+
+ CONSTRAINT [FK_guacamole_connection_history_user_id]
+ FOREIGN KEY ([user_id])
+ REFERENCES [guacamole_user] ([user_id])
+ ON DELETE SET NULL,
+
+ CONSTRAINT [FK_guacamole_connection_history_connection_id]
+ FOREIGN KEY ([connection_id])
+ REFERENCES [guacamole_connection] ([connection_id])
+ ON DELETE SET NULL,
+
+ CONSTRAINT [FK_guacamole_connection_history_sharing_profile_id]
+ FOREIGN KEY ([sharing_profile_id])
+ REFERENCES [guacamole_sharing_profile] ([sharing_profile_id])
+ -- ON DELETE SET NULL handled by guacamole_delete_sharing profile trigger
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_user_id]
+ ON [guacamole_connection_history] ([user_id]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_connection_id]
+ ON [guacamole_connection_history] ([connection_id]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_sharing_profile_id]
+ ON [guacamole_connection_history] ([sharing_profile_id]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_start_date]
+ ON [guacamole_connection_history] ([start_date]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_end_date]
+ ON [guacamole_connection_history] ([end_date]);
+GO
+
+--
+-- The user_password_history table stores password history
+-- for users, allowing for enforcing rules associated with
+-- reuse of passwords.
+--
+
+CREATE TABLE [guacamole_user_password_history] (
+
+ [password_history_id] [int] IDENTITY(1,1) NOT NULL,
+ [user_id] [int] NOT NULL,
+
+ -- Salted password
+ [password_hash] [binary](32) NOT NULL,
+ [password_salt] [binary](32),
+ [password_date] [datetime] NOT NULL,
+
+ CONSTRAINT [PK_guacamole_user_password_history]
+ PRIMARY KEY CLUSTERED ([password_history_id]),
+
+ CONSTRAINT [FK_guacamole_user_password_history_user_id]
+ FOREIGN KEY ([user_id])
+ REFERENCES [guacamole_user] ([user_id])
+ ON DELETE CASCADE
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_user_password_history_user_id]
+ ON [guacamole_user_password_history] ([user_id]);
+GO
+
+--
+-- Handle cascading deletion/updates of records in response to deletion of
+-- guacamole_user 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]
+ 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
+ [user_id] IN (SELECT [user_id] FROM DELETED)
+ OR [user_id] IN (SELECT [user_id] FROM DELETED);
+
+ -- Perform original deletion
+ DELETE FROM [guacamole_user]
+ WHERE [user_id] IN (SELECT [user_id] FROM DELETED);
+
+END
+GO
+
+--
+-- Handle cascading deletion/updates of records in response to deletion of
+-- guacamole_connection records, where such deletion is not already covered by
+-- ON DELETE CASCADE or ON DELETE SET NULL.
+--
+
+CREATE TRIGGER [guacamole_delete_connection]
+ ON [guacamole_connection]
+ INSTEAD OF DELETE
+AS BEGIN
+
+ -- Do not take trigger into account when producing row counts for the DELETE
+ SET NOCOUNT ON;
+
+ -- Delete associated sharing profiles
+ DELETE FROM [guacamole_sharing_profile]
+ WHERE [primary_connection_id] IN (SELECT [connection_id] FROM DELETED);
+
+ -- Perform original deletion
+ DELETE FROM [guacamole_connection]
+ WHERE [connection_id] IN (SELECT [connection_id] FROM DELETED);
+
+END
+GO
+
+--
+-- Handle cascading deletion/updates of records in response to deletion of
+-- guacamole_connection_group records, where such deletion is not already
+-- covered by ON DELETE CASCADE or ON DELETE SET NULL.
+--
+
+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 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]
+ );
+
+ -- Delete all child connections
+ DELETE FROM [guacamole_connection]
+ WHERE [parent_id] IN (SELECT [connection_group_id] FROM DELETED);
+
+END
+GO
+
+--
+-- Handle cascading deletion/updates of records in response to deletion of
+-- guacamole_sharing_profile records, where such deletion is not already
+-- covered by ON DELETE CASCADE or ON DELETE SET NULL.
+--
+
+CREATE TRIGGER [guacamole_delete_sharing_profile]
+ ON [guacamole_sharing_profile]
+ 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
+ UPDATE [guacamole_connection_history]
+ SET [sharing_profile_id] = NULL
+ WHERE [sharing_profile_id] IN (SELECT [sharing_profile_id] FROM DELETED);
+
+ -- Perform original deletion
+ DELETE FROM [guacamole_sharing_profile]
+ WHERE [sharing_profile_id] IN (SELECT [sharing_profile_id] FROM DELETED);
+
+END
+GO
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 4898e57..5b14651 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
@@ -1,49 +1,60 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-/**
- * Create the default admin user account and set up full privileges.
- */
-INSERT INTO [guacamole_user] (username, password_hash, password_salt, password_date)
-VALUES ('guacadmin',
- 0xCA458A7D494E3BE824F5E1E175A1556C0F8EEF2C2D7DF3633BEC4A29C4411960,
- 0xFE24ADC5E11E2B25288D1704ABE67A79E342ECC26064CE69C5B3177795A82264,
- getdate());
-
-INSERT INTO [guacamole_user_permission]
-SELECT [guacamole_user].[user_id], [affected].[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;
-
-INSERT INTO [guacamole_system_permission]
-SELECT user_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, 'ADMINISTER' AS permission)
- permissions
- JOIN [guacamole_user] ON permissions.username = [guacamole_user].[username];
-GO
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+
+-- Create default user "guacadmin" with password "guacadmin"
+INSERT INTO [guacamole_user] (
+ [username],
+ [password_hash],
+ [password_salt],
+ [password_date]
+)
+VALUES (
+ 'guacadmin',
+ 0xCA458A7D494E3BE824F5E1E175A1556C0F8EEF2C2D7DF3633BEC4A29C4411960,
+ 0xFE24ADC5E11E2B25288D1704ABE67A79E342ECC26064CE69C5B3177795A82264,
+ getdate()
+);
+
+-- Grant this user all system permissions
+INSERT INTO [guacamole_system_permission]
+SELECT
+ [user_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', 'ADMINISTER'
+) [permissions] ([username], [permission])
+JOIN [guacamole_user] ON [permissions].[username] = [guacamole_user].[username];
+
+INSERT INTO [guacamole_user_permission]
+SELECT
+ [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;
+GO
