Repository: guacamole-client Updated Branches: refs/heads/master 4a9560358 -> 9a0df8e33
GUACAMOLE-363: Correct DOS-style newlines within SQL Server schema before it's too late. Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/303bc49d Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/303bc49d Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/303bc49d Branch: refs/heads/master Commit: 303bc49d7a39cd6b1dec1048e51afb9aa055b8fc Parents: 9df20f2 Author: Michael Jumper <mjum...@apache.org> Authored: Mon Dec 11 22:53:19 2017 -0800 Committer: Michael Jumper <mjum...@apache.org> Committed: Mon Dec 11 22:53:19 2017 -0800 ---------------------------------------------------------------------- .../schema/001-create-schema.sql | 1296 +++++++++--------- .../schema/002-create-admin-user.sql | 120 +- 2 files changed, 708 insertions(+), 708 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/303bc49d/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 9a8bec8..16525c0 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,648 +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. --- - --- --- 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) NOT NULL; -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) NOT NULL; -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) NOT NULL; -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) NOT NULL; -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 +-- +-- 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) NOT NULL; +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) NOT NULL; +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) NOT NULL; +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) NOT NULL; +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 http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/303bc49d/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 a5ecac0..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,60 +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 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 +-- +-- 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
