You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@guacamole.apache.org by jm...@apache.org on 2017/01/05 21:50:25 UTC
[3/7] incubator-guacamole-client git commit: GUACAMOLE-36: Define and
map historical password record table.
GUACAMOLE-36: Define and map historical password record table.
Project: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/commit/ae695ef1
Tree: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/tree/ae695ef1
Diff: http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/diff/ae695ef1
Branch: refs/heads/master
Commit: ae695ef17b2272fefa4941c1a69b1816ffd9b8e9
Parents: 3c718f2
Author: Michael Jumper <mj...@apache.org>
Authored: Mon Aug 22 17:24:38 2016 -0700
Committer: Michael Jumper <mj...@apache.org>
Committed: Thu Jan 5 13:37:13 2017 -0800
----------------------------------------------------------------------
.../jdbc/JDBCAuthenticationProviderModule.java | 2 +
.../auth/jdbc/user/PasswordRecordMapper.java | 68 +++++++++++++++++++
.../schema/001-create-schema.sql | 22 ++++++
.../schema/upgrade/upgrade-pre-0.9.11.sql | 23 +++++++
.../auth/jdbc/user/PasswordRecordMapper.xml | 70 ++++++++++++++++++++
.../schema/001-create-schema.sql | 24 +++++++
.../schema/upgrade/upgrade-pre-0.9.11.sql | 24 +++++++
.../auth/jdbc/user/PasswordRecordMapper.xml | 70 ++++++++++++++++++++
8 files changed, 303 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/ae695ef1/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/JDBCAuthenticationProviderModule.java
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/JDBCAuthenticationProviderModule.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/JDBCAuthenticationProviderModule.java
index e52ca5a..475282f 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/JDBCAuthenticationProviderModule.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/JDBCAuthenticationProviderModule.java
@@ -75,6 +75,7 @@ import org.apache.guacamole.auth.jdbc.sharingprofile.SharingProfileMapper;
import org.apache.guacamole.auth.jdbc.sharingprofile.SharingProfileParameterMapper;
import org.apache.guacamole.auth.jdbc.sharingprofile.SharingProfileService;
import org.apache.guacamole.auth.jdbc.tunnel.RestrictedGuacamoleTunnelService;
+import org.apache.guacamole.auth.jdbc.user.PasswordRecordMapper;
import org.mybatis.guice.MyBatisModule;
import org.mybatis.guice.datasource.builtin.PooledDataSourceProvider;
@@ -121,6 +122,7 @@ public class JDBCAuthenticationProviderModule extends MyBatisModule {
addMapperClass(ConnectionPermissionMapper.class);
addMapperClass(ConnectionRecordMapper.class);
addMapperClass(ConnectionParameterMapper.class);
+ addMapperClass(PasswordRecordMapper.class);
addMapperClass(SystemPermissionMapper.class);
addMapperClass(SharingProfileMapper.class);
addMapperClass(SharingProfileParameterMapper.class);
http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/ae695ef1/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.java
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.java
new file mode 100644
index 0000000..21d36a8
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.java
@@ -0,0 +1,68 @@
+/*
+ * 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.
+ */
+
+package org.apache.guacamole.auth.jdbc.user;
+
+import java.util.List;
+import org.apache.guacamole.auth.jdbc.base.ModeledDirectoryObjectMapper;
+import org.apache.ibatis.annotations.Param;
+
+/**
+ * Mapper for historical password records (users' prior passwords, along with
+ * the dates they were set).
+ *
+ * @author Michael Jumper
+ */
+public interface PasswordRecordMapper extends ModeledDirectoryObjectMapper<UserModel> {
+
+ /**
+ * Returns a collection of all password records associated with the user
+ * having the given username.
+ *
+ * @param username
+ * The username of the user whose password records are to be retrieved.
+ *
+ * @param maxHistorySize
+ * The maximum number of records to maintain for each user.
+ *
+ * @return
+ * A collection of all password records associated with the user having
+ * the given username. This collection will be empty if no such user
+ * exists.
+ */
+ List<PasswordRecordModel> select(@Param("username") String username,
+ @Param("maxHistorySize") int maxHistorySize);
+
+ /**
+ * Inserts the given password record. Old records exceeding the maximum
+ * history size will be automatically deleted.
+ *
+ * @param record
+ * The password record to insert.
+ *
+ * @param maxHistorySize
+ * The maximum number of records to maintain for each user.
+ *
+ * @return
+ * The number of rows inserted.
+ */
+ int insert(@Param("record") PasswordRecordModel record,
+ @Param("maxHistorySize") int maxHistorySize);
+
+}
http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/ae695ef1/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 cb56047..ebf5b7d 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
@@ -336,3 +336,25 @@ CREATE TABLE `guacamole_connection_history` (
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- User password history
+--
+
+CREATE TABLE guacamole_user_password_history (
+
+ `password_history_id` int(11) NOT NULL AUTO_INCREMENT,
+ `user_id` int(11) NOT NULL,
+
+ -- Salted password
+ `password_hash` binary(32) NOT NULL,
+ `password_salt` binary(32),
+ `password_date` datetime NOT NULL,
+
+ PRIMARY KEY (`password_history_id`),
+ KEY `user_id` (`user_id`),
+
+ CONSTRAINT `guacamole_user_password_history_ibfk_1`
+ FOREIGN KEY (`user_id`)
+ REFERENCES `guacamole_user` (`user_id`) ON DELETE CASCADE
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/ae695ef1/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.11.sql
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.11.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.11.sql
index 3acc2a4..ecfde88 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.11.sql
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.11.sql
@@ -23,3 +23,26 @@
ALTER TABLE guacamole_user
ADD COLUMN password_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
+
+--
+-- User password history
+--
+
+CREATE TABLE guacamole_user_password_history (
+
+ `password_history_id` int(11) NOT NULL AUTO_INCREMENT,
+ `user_id` int(11) NOT NULL,
+
+ -- Salted password
+ `password_hash` binary(32) NOT NULL,
+ `password_salt` binary(32),
+ `password_date` datetime NOT NULL,
+
+ PRIMARY KEY (`password_history_id`),
+ KEY `user_id` (`user_id`),
+
+ CONSTRAINT `guacamole_user_password_history_ibfk_1`
+ FOREIGN KEY (`user_id`)
+ REFERENCES `guacamole_user` (`user_id`) ON DELETE CASCADE
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/ae695ef1/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml
new file mode 100644
index 0000000..2b5ff23
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml
@@ -0,0 +1,70 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
+ "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
+
+<!--
+ 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.
+-->
+
+<mapper namespace="org.apache.guacamole.auth.jdbc.user.PasswordRecordMapper" >
+
+ <!-- Result mapper for system permissions -->
+ <resultMap id="PasswordRecordResultMap" type="org.apache.guacamole.auth.jdbc.user.PasswordRecordModel">
+ <result column="user_id" property="userID" jdbcType="INTEGER"/>
+ <result column="password_hash" property="passwordHash" jdbcType="BINARY"/>
+ <result column="password_salt" property="passwordSalt" jdbcType="BINARY"/>
+ <result column="password_date" property="passwordDate" jdbcType="TIMESTAMP"/>
+ </resultMap>
+
+ <!-- Select all password records for a given user -->
+ <select id="select" resultMap="PasswordRecordResultMap">
+
+ SELECT
+ guacamole_user_password_history.user_id,
+ guacamole_user_password_history.password_hash,
+ guacamole_user_password_history.password_salt,
+ guacamole_user_password_history.password_date
+ FROM guacamole_user_password_history
+ JOIN guacamole_user ON guacamole_user_password_history.user_id = guacamole_user.user_id
+ WHERE
+ guacamole_user.username = #{username,jdbcType=VARCHAR}
+ ORDER BY
+ guacamole_user_password_history.password_date DESC
+ LIMIT #{maxHistorySize}
+
+ </select>
+
+ <!-- Insert the given password record -->
+ <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.user.PasswordRecordModel">
+
+ INSERT INTO guacamole_user_password_history (
+ user_id,
+ password_hash,
+ password_salt,
+ password_date
+ )
+ VALUES (
+ #{record.userID,jdbcType=INTEGER},
+ #{record.passwordHash,jdbcType=BINARY},
+ #{record.passwordSalt,jdbcType=BINARY},
+ #{record.passwordDate,jdbcType=TIMESTAMP}
+ )
+
+ </insert>
+
+</mapper>
http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/ae695ef1/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 e308dce..f2ad2c4 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
@@ -385,3 +385,27 @@ CREATE INDEX ON guacamole_connection_history(connection_id);
CREATE INDEX ON guacamole_connection_history(sharing_profile_id);
CREATE INDEX ON guacamole_connection_history(start_date);
CREATE INDEX ON guacamole_connection_history(end_date);
+
+--
+-- User password history
+--
+
+CREATE TABLE guacamole_user_password_history (
+
+ password_history_id serial NOT NULL,
+ user_id integer NOT NULL,
+
+ -- Salted password
+ password_hash bytea NOT NULL,
+ password_salt bytea,
+ password_date timestamptz NOT NULL,
+
+ PRIMARY KEY (password_history_id),
+
+ CONSTRAINT guacamole_user_password_history_ibfk_1
+ FOREIGN KEY (user_id)
+ REFERENCES guacamole_user (user_id) ON DELETE CASCADE
+
+);
+
+CREATE INDEX ON guacamole_user_password_history(user_id);
http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/ae695ef1/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.11.sql
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.11.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.11.sql
index d2f430c..c047a8f 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.11.sql
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.11.sql
@@ -23,3 +23,27 @@
ALTER TABLE guacamole_user
ADD COLUMN password_date timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP;
+
+--
+-- User password history
+--
+
+CREATE TABLE guacamole_user_password_history (
+
+ password_history_id serial NOT NULL,
+ user_id integer NOT NULL,
+
+ -- Salted password
+ password_hash bytea NOT NULL,
+ password_salt bytea,
+ password_date timestamptz NOT NULL,
+
+ PRIMARY KEY (password_history_id),
+
+ CONSTRAINT guacamole_user_password_history_ibfk_1
+ FOREIGN KEY (user_id)
+ REFERENCES guacamole_user (user_id) ON DELETE CASCADE
+
+);
+
+CREATE INDEX ON guacamole_user_password_history(user_id);
http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/ae695ef1/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml
new file mode 100644
index 0000000..a119f4c
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml
@@ -0,0 +1,70 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
+ "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
+
+<!--
+ 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.
+-->
+
+<mapper namespace="org.apache.guacamole.auth.jdbc.user.PasswordRecordMapper" >
+
+ <!-- Result mapper for historical passwords -->
+ <resultMap id="PasswordRecordResultMap" type="org.apache.guacamole.auth.jdbc.user.PasswordRecordModel">
+ <result column="user_id" property="userID" jdbcType="INTEGER"/>
+ <result column="password_hash" property="passwordHash" jdbcType="BINARY"/>
+ <result column="password_salt" property="passwordSalt" jdbcType="BINARY"/>
+ <result column="password_date" property="passwordDate" jdbcType="TIMESTAMP"/>
+ </resultMap>
+
+ <!-- Select all password records for a given user -->
+ <select id="select" resultMap="PasswordRecordResultMap">
+
+ SELECT
+ guacamole_user_password_history.user_id,
+ guacamole_user_password_history.password_hash,
+ guacamole_user_password_history.password_salt,
+ guacamole_user_password_history.password_date
+ FROM guacamole_user_password_history
+ JOIN guacamole_user ON guacamole_user_password_history.user_id = guacamole_user.user_id
+ WHERE
+ guacamole_user.username = #{username,jdbcType=VARCHAR}
+ ORDER BY
+ guacamole_user_password_history.password_date DESC
+ LIMIT #{maxHistorySize}
+
+ </select>
+
+ <!-- Insert the given password record -->
+ <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.user.PasswordRecordModel">
+
+ INSERT INTO guacamole_user_password_history (
+ user_id,
+ password_hash,
+ password_salt,
+ password_date
+ )
+ VALUES (
+ #{record.userID,jdbcType=INTEGER},
+ #{record.passwordHash,jdbcType=BINARY},
+ #{record.passwordSalt,jdbcType=BINARY},
+ #{record.passwordDate,jdbcType=TIMESTAMP}
+ )
+
+ </insert>
+
+</mapper>