You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@guacamole.apache.org by vn...@apache.org on 2017/12/06 15:11:59 UTC
[1/5] guacamole-client git commit: GUACAMOLE-363: Do not take
behind-the-scenes trigger operations into account when calculating the row
counts for a query.
Repository: guacamole-client
Updated Branches:
refs/heads/staging/0.9.14 f75786a68 -> 9df20f29b
GUACAMOLE-363: Do not take behind-the-scenes trigger operations into account when calculating the row counts for a query.
Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo
Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/23084625
Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/23084625
Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/23084625
Branch: refs/heads/staging/0.9.14
Commit: 23084625ab7d82ecd73197ff9078f4c822af62e3
Parents: 56da0b0
Author: Michael Jumper <mj...@apache.org>
Authored: Sat Nov 25 21:54:41 2017 -0800
Committer: Michael Jumper <mj...@apache.org>
Committed: Sun Nov 26 13:01:48 2017 -0800
----------------------------------------------------------------------
.../schema/001-create-schema.sql | 12 ++++++++++++
1 file changed, 12 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/23084625/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 be955ac..da63f60 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
@@ -559,6 +559,9 @@ CREATE TRIGGER [guacamole_delete_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
@@ -583,6 +586,9 @@ CREATE TRIGGER [guacamole_delete_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);
@@ -605,6 +611,9 @@ CREATE TRIGGER [guacamole_delete_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
@@ -637,6 +646,9 @@ CREATE TRIGGER [guacamole_delete_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
[3/5] guacamole-client git commit: GUACAMOLE-363: Clean up and
refactor SQL Server schema to closely match the MySQL and PostgreSQL schemas.
Add missing types and indexes.
Posted by vn...@apache.org.
GUACAMOLE-363: Clean up and refactor SQL Server schema to closely match the MySQL and PostgreSQL schemas. Add missing types and indexes.
Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo
Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/317c76b5
Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/317c76b5
Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/317c76b5
Branch: refs/heads/staging/0.9.14
Commit: 317c76b5c5c38fe2deed02c7681f43f797b85922
Parents: 442fd3d
Author: Michael Jumper <mj...@apache.org>
Authored: Sat Nov 25 17:19:27 2017 -0800
Committer: Michael Jumper <mj...@apache.org>
Committed: Sun Nov 26 13:01:48 2017 -0800
----------------------------------------------------------------------
.../schema/001-create-schema.sql | 930 ++++++++++---------
.../schema/002-create-admin-user.sql | 93 +-
2 files changed, 561 insertions(+), 462 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/317c76b5/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 f7a34b1..fd576e5 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,556 @@
-/*
- * 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.
- */
+--
+-- 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.
- */
+--
+-- 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');
+--
+-- 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
-/**
- * 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');
+CREATE TYPE [guacamole_object_permission] FROM [nvarchar](16) NOT NULL;
+EXEC sp_bindrule
+ 'guacamole_object_permission_list',
+ 'guacamole_object_permission';
GO
-/**
- * The permission data type.
- */
-CREATE TYPE [guacamole_permission] FROM [nvarchar](10) NOT NULL;
-EXEC sp_bindrule 'guacamole_permission_list','guacamole_permission';
+--
+-- System permission types
+--
+
+CREATE RULE [guacamole_system_permission_list] AS @list IN (
+ 'CREATE_CONNECTION',
+ 'CREATE_CONNECTION_GROUP',
+ 'CREATE_SHARING_PROFILE',
+ 'CREATE_USER',
+ 'ADMINISTER'
+);
+GO
-/**
- * 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';
+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];
+--
+-- Guacamole proxy (guacd) encryption methods.
+--
+
+CREATE RULE [guacamole_proxy_encryption_method_list] AS @list IN (
+ 'NONE',
+ 'SSL'
+);
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];
+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]),
+
+ -- NOTE: SQL Server does not allow ON DELETE CASCADE for this column
+ -- due to the potential for cycles. Lacking this, deletion of connection
+ -- groups will fail if they contain other connection groups.
+ CONSTRAINT [FK_guacamole_connection_group_parent_id]
+ FOREIGN KEY ([parent_id])
+ REFERENCES [guacamole_connection_group] ([connection_group_id])
+
+);
+
+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
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_parent_id]
+ ON [guacamole_connection] ([parent_id]);
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];
+--
+-- 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
-/**
- * The sharing_profile table stores profiles that allow
- * connections to be shared amongst multiple users.
- */
-CREATE TABLE [guacamole_sharing_profile](
+--
+-- 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] 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];
+ [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
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_sharing_profile_primary_connection_id]
+ ON [guacamole_sharing_profile] ([primary_connection_id]);
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,
+--
+-- 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] 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];
+ 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
-/**
- * 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,
+--
+-- 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] 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];
+ 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
-/**
- * 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];
+--
+-- 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
-/**
- * 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];
+--
+-- 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
-/**
- * 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];
+--
+-- 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
-/**
- * The system_permission table stores permission mappings
- * for system-level operations.
- */
-CREATE TABLE [guacamole_system_permission](
- [user_id] [int] NOT NULL,
+--
+-- 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] 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];
+ 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
-/**
- * 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];
+--
+-- 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]),
+
+ -- NOTE: SQL Server does not allow ON DELETE CASCADE for this column
+ -- due to the potential for multiple deletion paths. Lacking this, deletion
+ -- of users associated with permissions granted to other users will fail.
+ CONSTRAINT [FK_guacamole_user_permission_affected_user_id]
+ FOREIGN KEY ([affected_user_id])
+ REFERENCES [guacamole_user] ([user_id]),
+
+ CONSTRAINT [FK_guacamole_user_permission_user_id]
+ FOREIGN KEY ([user_id])
+ REFERENCES [guacamole_user] ([user_id])
+ ON DELETE CASCADE
+
+);
+
+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
-/**
- * The connection_history table stores records for historical
- * connections.
- */
-CREATE TABLE [guacamole_connection_history](
+--
+-- 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] 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];
+ [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,
+
+ -- NOTE: SQL Server does not allow ON DELETE SET NULL for this column
+ -- due to the potential for multiple deletion paths. Lacking this, deletion
+ -- of sharing profiles which have previously been used (and thus have
+ -- existing history records) will fail.
+ CONSTRAINT [FK_guacamole_connection_history_sharing_profile_id]
+ FOREIGN KEY ([sharing_profile_id])
+ REFERENCES [guacamole_sharing_profile] ([sharing_profile_id])
+
+);
+
+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](
+--
+-- 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
+ [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
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/317c76b5/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 4898e57..a5ecac0 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.
- */
+--
+-- 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',
+-- Create default user "guacadmin" with password "guacadmin"
+INSERT INTO [guacamole_user] (
+ [username],
+ [password_hash],
+ [password_salt],
+ [password_date]
+)
+VALUES (
+ 'guacadmin',
0xCA458A7D494E3BE824F5E1E175A1556C0F8EEF2C2D7DF3633BEC4A29C4411960,
0xFE24ADC5E11E2B25288D1704ABE67A79E342ECC26064CE69C5B3177795A82264,
- getdate());
+ getdate()
+);
-INSERT INTO [guacamole_user_permission]
-SELECT [guacamole_user].[user_id], [affected].[user_id], permission
+-- Grant this user all system permissions
+INSERT INTO [guacamole_system_permission]
+SELECT
+ [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;
+ 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_system_permission]
-SELECT user_id, permission
+INSERT INTO [guacamole_user_permission]
+SELECT
+ [guacamole_user].[user_id],
+ [affected].[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];
+ 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
[5/5] guacamole-client git commit: GUACAMOLE-363: Merge bring SQL
Server schema to parity with MySQL/PostgreSQL.
Posted by vn...@apache.org.
GUACAMOLE-363: Merge bring SQL Server schema to parity with MySQL/PostgreSQL.
Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo
Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/9df20f29
Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/9df20f29
Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/9df20f29
Branch: refs/heads/staging/0.9.14
Commit: 9df20f29bde058e8bbc5c98557f37b6e42e92a4e
Parents: f75786a 1ec76b4
Author: Nick Couchman <vn...@apache.org>
Authored: Wed Dec 6 10:11:02 2017 -0500
Committer: Nick Couchman <vn...@apache.org>
Committed: Wed Dec 6 10:11:02 2017 -0500
----------------------------------------------------------------------
.../schema/001-create-schema.sql | 1024 ++++++++++--------
.../schema/002-create-admin-user.sql | 93 +-
2 files changed, 654 insertions(+), 463 deletions(-)
----------------------------------------------------------------------
[2/5] guacamole-client git commit: GUACAMOLE-363: Add triggers to
handle cascading deletes/updates where SQL Server does not support doing so
automatically via ON DELETE actions.
Posted by vn...@apache.org.
GUACAMOLE-363: Add triggers to handle cascading deletes/updates where SQL Server does not support doing so automatically via ON DELETE actions.
Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo
Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/56da0b0c
Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/56da0b0c
Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/56da0b0c
Branch: refs/heads/staging/0.9.14
Commit: 56da0b0c756f59b06f6647653388a5448fadb9bd
Parents: 317c76b
Author: Michael Jumper <mj...@apache.org>
Authored: Sat Nov 25 19:57:31 2017 -0800
Committer: Michael Jumper <mj...@apache.org>
Committed: Sun Nov 26 13:01:48 2017 -0800
----------------------------------------------------------------------
.../schema/001-create-schema.sql | 122 ++++++++++++++++---
1 file changed, 108 insertions(+), 14 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/56da0b0c/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 fd576e5..be955ac 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
@@ -124,12 +124,10 @@ CREATE TABLE [guacamole_connection_group] (
CONSTRAINT [AK_guacamole_connection_group_name_parent]
UNIQUE ([connection_group_name], [parent_id]),
- -- NOTE: SQL Server does not allow ON DELETE CASCADE for this column
- -- due to the potential for cycles. Lacking this, deletion of connection
- -- groups will fail if they contain other connection groups.
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
);
@@ -171,7 +169,7 @@ CREATE TABLE [guacamole_connection] (
CONSTRAINT [FK_guacamole_connection_parent_id]
FOREIGN KEY ([parent_id])
REFERENCES [guacamole_connection_group] ([connection_group_id])
- ON DELETE CASCADE
+ -- ON DELETE CASCADE handled by guacamole_delete_connection_group trigger
);
@@ -249,7 +247,7 @@ CREATE TABLE [guacamole_sharing_profile] (
CONSTRAINT [FK_guacamole_sharing_profile_primary_connection_id]
FOREIGN KEY ([primary_connection_id])
REFERENCES [guacamole_connection] ([connection_id])
- ON DELETE CASCADE
+ -- ON DELETE CASCADE handled by guacamole_delete_connection trigger
);
@@ -446,17 +444,15 @@ CREATE TABLE [guacamole_user_permission] (
CONSTRAINT [PK_guacamole_user_permission]
PRIMARY KEY CLUSTERED ([user_id], [affected_user_id], [permission]),
- -- NOTE: SQL Server does not allow ON DELETE CASCADE for this column
- -- due to the potential for multiple deletion paths. Lacking this, deletion
- -- of users associated with permissions granted to other users will fail.
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
+ -- ON DELETE CASCADE handled by guacamole_delete_user trigger
);
@@ -499,13 +495,10 @@ CREATE TABLE [guacamole_connection_history] (
REFERENCES [guacamole_connection] ([connection_id])
ON DELETE SET NULL,
- -- NOTE: SQL Server does not allow ON DELETE SET NULL for this column
- -- due to the potential for multiple deletion paths. Lacking this, deletion
- -- of sharing profiles which have previously been used (and thus have
- -- existing history records) will fail.
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
);
@@ -553,4 +546,105 @@ CREATE TABLE [guacamole_user_password_history] (
CREATE NONCLUSTERED INDEX [IX_guacamole_user_password_history_user_id]
ON [guacamole_user_password_history] ([user_id]);
-GO
\ No newline at end of file
+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
+
+ -- 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
+
+ -- 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
+
+ -- 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
+
+ -- 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
[4/5] guacamole-client git commit: GUACAMOLE-363: Remove ALTER
DATABASE CURRENT statements (not compatible with SQL Server 2008).
Posted by vn...@apache.org.
GUACAMOLE-363: Remove ALTER DATABASE CURRENT statements (not compatible with SQL Server 2008).
Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo
Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/1ec76b48
Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/1ec76b48
Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/1ec76b48
Branch: refs/heads/staging/0.9.14
Commit: 1ec76b486aa0835c6ad088c379f2066cac52fb2c
Parents: 2308462
Author: Michael Jumper <mj...@apache.org>
Authored: Sun Nov 26 13:45:45 2017 -0800
Committer: Michael Jumper <mj...@apache.org>
Committed: Sun Nov 26 13:45:45 2017 -0800
----------------------------------------------------------------------
.../schema/001-create-schema.sql | 14 --------------
1 file changed, 14 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/1ec76b48/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 da63f60..9a8bec8 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
@@ -18,20 +18,6 @@
--
--
--- 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
-
---
-- Connection group types
--