Repository: guacamole-client Updated Branches: refs/heads/master e7cb654c2 -> 00d464b51
GUACAMOLE-394: Add guacamole_user_history table to database schema. Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/5aa2172e Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/5aa2172e Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/5aa2172e Branch: refs/heads/master Commit: 5aa2172eacfc066ddd925b959ca693e66eb1f35b Parents: c991ea4 Author: Michael Jumper <[email protected]> Authored: Tue Sep 12 13:18:00 2017 -0700 Committer: Michael Jumper <[email protected]> Committed: Mon Dec 11 23:51:56 2017 -0800 ---------------------------------------------------------------------- .../schema/001-create-schema.sql | 24 +++++++++++++++ .../schema/upgrade/upgrade-pre-0.9.14.sql | 24 +++++++++++++++ .../schema/001-create-schema.sql | 30 +++++++++++++++++++ .../schema/upgrade/upgrade-pre-0.9.14.sql | 30 +++++++++++++++++++ .../schema/001-create-schema.sql | 31 ++++++++++++++++++++ 5 files changed, 139 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/5aa2172e/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql index 1873d1c..d65bd33 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql @@ -352,6 +352,30 @@ CREATE TABLE `guacamole_connection_history` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- User login/logout history +-- + +CREATE TABLE guacamole_user_history ( + + `history_id` int(11) NOT NULL AUTO_INCREMENT, + `user_id` int(11) DEFAULT NULL, + `username` varchar(128) NOT NULL, + `remote_host` varchar(256) DEFAULT NULL, + `start_date` datetime NOT NULL, + `end_date` datetime DEFAULT NULL, + + PRIMARY KEY (history_id), + KEY `user_id` (`user_id`), + KEY `start_date` (`start_date`), + KEY `end_date` (`end_date`), + + CONSTRAINT guacamole_user_history_ibfk_1 + FOREIGN KEY (user_id) + REFERENCES guacamole_user (user_id) ON DELETE SET NULL + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -- User password history -- http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/5aa2172e/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.14.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.14.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.14.sql index 01be93a..5a0a0b2 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.14.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.14.sql @@ -37,3 +37,27 @@ ALTER TABLE guacamole_connection ALTER TABLE guacamole_connection_history ADD COLUMN remote_host VARCHAR(256) DEFAULT NULL; + +-- +-- User login/logout history +-- + +CREATE TABLE guacamole_user_history ( + + `history_id` int(11) NOT NULL AUTO_INCREMENT, + `user_id` int(11) DEFAULT NULL, + `username` varchar(128) NOT NULL, + `remote_host` varchar(256) DEFAULT NULL, + `start_date` datetime NOT NULL, + `end_date` datetime DEFAULT NULL, + + PRIMARY KEY (history_id), + KEY `user_id` (`user_id`), + KEY `start_date` (`start_date`), + KEY `end_date` (`end_date`), + + CONSTRAINT guacamole_user_history_ibfk_1 + FOREIGN KEY (user_id) + REFERENCES guacamole_user (user_id) ON DELETE SET NULL + +) ENGINE=InnoDB DEFAULT CHARSET=utf8; http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/5aa2172e/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql index e4015d3..4840c91 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql @@ -439,6 +439,36 @@ CREATE INDEX guacamole_connection_history_end_date ON guacamole_connection_history(end_date); -- +-- User login/logout history +-- + +CREATE TABLE guacamole_user_history ( + + history_id serial NOT NULL, + user_id integer DEFAULT NULL, + username varchar(128) NOT NULL, + remote_host varchar(256) DEFAULT NULL, + start_date timestamptz NOT NULL, + end_date timestamptz DEFAULT NULL, + + PRIMARY KEY (history_id), + + CONSTRAINT guacamole_user_history_ibfk_1 + FOREIGN KEY (user_id) + REFERENCES guacamole_user (user_id) ON DELETE SET NULL + +); + +CREATE INDEX guacamole_user_history_user_id + ON guacamole_user_history(user_id); + +CREATE INDEX guacamole_user_history_start_date + ON guacamole_user_history(start_date); + +CREATE INDEX guacamole_user_history_end_date + ON guacamole_user_history(end_date); + +-- -- User password history -- http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/5aa2172e/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.14.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.14.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.14.sql index 157e896..39e8e59 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.14.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.14.sql @@ -37,3 +37,33 @@ ALTER TABLE guacamole_connection ALTER TABLE guacamole_connection_history ADD COLUMN remote_host VARCHAR(256) DEFAULT NULL; + +-- +-- User login/logout history +-- + +CREATE TABLE guacamole_user_history ( + + history_id serial NOT NULL, + user_id integer DEFAULT NULL, + username varchar(128) NOT NULL, + remote_host varchar(256) DEFAULT NULL, + start_date timestamptz NOT NULL, + end_date timestamptz DEFAULT NULL, + + PRIMARY KEY (history_id), + + CONSTRAINT guacamole_user_history_ibfk_1 + FOREIGN KEY (user_id) + REFERENCES guacamole_user (user_id) ON DELETE SET NULL + +); + +CREATE INDEX guacamole_user_history_user_id + ON guacamole_user_history(user_id); + +CREATE INDEX guacamole_user_history_start_date + ON guacamole_user_history(start_date); + +CREATE INDEX guacamole_user_history_end_date + ON guacamole_user_history(end_date); http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/5aa2172e/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 91ba377..19d48e4 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 @@ -505,6 +505,37 @@ CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_end_date] GO -- +-- User login/logout history +-- + +CREATE TABLE [guacamole_user_history] ( + + [history_id] [int] IDENTITY(1,1) NOT NULL, + [user_id] [int] DEFAULT NULL, + [username] [nvarchar](128) NOT NULL, + [remote_host] [nvarchar](256) DEFAULT NULL, + [start_date] [datetime] NOT NULL, + [end_date] [datetime] DEFAULT NULL, + + PRIMARY KEY (history_id), + + CONSTRAINT FK_guacamole_user_history_user_id + FOREIGN KEY (user_id) + REFERENCES guacamole_user (user_id) ON DELETE SET NULL + +); + +CREATE NONCLUSTERED INDEX [IX_guacamole_user_history_user_id] + ON [guacamole_user_history] ([user_id]); + +CREATE NONCLUSTERED INDEX [IX_guacamole_user_history_start_date] + ON [guacamole_user_history] ([start_date]); + +CREATE NONCLUSTERED INDEX [IX_guacamole_user_history_end_date] + ON [guacamole_user_history] ([end_date]); +GO + +-- -- The user_password_history table stores password history -- for users, allowing for enforcing rules associated with -- reuse of passwords.
