GUACAMOLE-220: Update SQL queries to use guacamole_entity table where applicable.
Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/b499092d Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/b499092d Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/b499092d Branch: refs/heads/master Commit: b499092d06b7cd578fb349f41d228d472de39278 Parents: d95e059 Author: Michael Jumper <mjum...@apache.org> Authored: Tue Apr 3 14:23:56 2018 -0700 Committer: Michael Jumper <mjum...@apache.org> Committed: Wed Sep 19 23:56:51 2018 -0700 ---------------------------------------------------------------------- .../auth/jdbc/connection/ConnectionMapper.xml | 10 +-- .../jdbc/connection/ConnectionRecordMapper.xml | 10 ++- .../connectiongroup/ConnectionGroupMapper.xml | 12 +-- .../ConnectionGroupPermissionMapper.xml | 35 ++++----- .../permission/ConnectionPermissionMapper.xml | 35 ++++----- .../SharingProfilePermissionMapper.xml | 35 ++++----- .../jdbc/permission/SystemPermissionMapper.xml | 31 ++++---- .../jdbc/permission/UserPermissionMapper.xml | 77 +++++++++++--------- .../sharingprofile/SharingProfileMapper.xml | 6 +- .../auth/jdbc/user/PasswordRecordMapper.xml | 3 +- .../guacamole/auth/jdbc/user/UserMapper.xml | 70 ++++++++++++------ .../auth/jdbc/user/UserRecordMapper.xml | 23 ++++-- 12 files changed, 188 insertions(+), 159 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/b499092d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml index 0b109f6..c238c78 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml @@ -68,7 +68,7 @@ SELECT connection_id FROM guacamole_connection_permission WHERE - user_id = #{user.objectID,jdbcType=INTEGER} + entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ' </select> @@ -89,7 +89,7 @@ WHERE <if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}::integer</if> <if test="parentIdentifier == null">parent_id IS NULL</if> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ' </select> @@ -165,7 +165,7 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER}::integer </foreach> - AND guacamole_connection_permission.user_id = #{user.objectID,jdbcType=INTEGER} + AND guacamole_connection_permission.entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ' GROUP BY guacamole_connection.connection_id; @@ -177,7 +177,7 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER}::integer </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ'; SELECT @@ -191,7 +191,7 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER}::integer </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ'; </select> http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/b499092d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml index 4545332..b4407bd 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml @@ -79,7 +79,10 @@ #{record.sharingProfileIdentifier,jdbcType=INTEGER}::integer, #{record.sharingProfileName,jdbcType=VARCHAR}, (SELECT user_id FROM guacamole_user - WHERE username = #{record.username,jdbcType=VARCHAR}), + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id + WHERE + guacamole_entity.name = #{record.username,jdbcType=VARCHAR} + AND guacamole_entity.type = 'USER'::guacamole_entity_type), #{record.username,jdbcType=VARCHAR}, #{record.startDate,jdbcType=TIMESTAMP}, #{record.endDate,jdbcType=TIMESTAMP} @@ -180,7 +183,10 @@ guacamole_connection_history.user_id IN ( SELECT user_id FROM guacamole_user - WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0 + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id + WHERE + POSITION(#{term.term,jdbcType=VARCHAR} IN guacamole_entity.name) > 0 + AND guacamole_entity.type = 'USER'::guacamole_entity_type ) OR guacamole_connection_history.connection_id IN ( http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/b499092d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml index 7cc4ac7..7e0b188 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml @@ -69,7 +69,7 @@ SELECT connection_group_id FROM guacamole_connection_group_permission WHERE - user_id = #{user.objectID,jdbcType=INTEGER} + entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ' </select> @@ -90,7 +90,7 @@ WHERE <if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}::integer</if> <if test="parentIdentifier == null">parent_id IS NULL</if> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ' </select> @@ -161,7 +161,7 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER}::integer </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ'; SELECT parent_id, guacamole_connection_group.connection_group_id @@ -172,7 +172,7 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER}::integer </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ'; SELECT parent_id, guacamole_connection.connection_id @@ -183,7 +183,7 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER}::integer </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ'; SELECT @@ -197,7 +197,7 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER}::integer </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ'; </select> http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/b499092d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml index 5ab114a..c8ec936 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml @@ -25,24 +25,21 @@ <!-- Result mapper for connection permissions --> <resultMap id="ConnectionGroupPermissionResultMap" type="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> - <result column="user_id" property="userID" jdbcType="INTEGER"/> - <result column="username" property="username" jdbcType="VARCHAR"/> + <result column="entity_id" property="entityID" jdbcType="INTEGER"/> <result column="permission" property="type" jdbcType="VARCHAR" javaType="org.apache.guacamole.net.auth.permission.ObjectPermission$Type"/> <result column="connection_group_id" property="objectIdentifier" jdbcType="INTEGER"/> </resultMap> - <!-- Select all permissions for a given user --> + <!-- Select all permissions for a given entity --> <select id="select" resultMap="ConnectionGroupPermissionResultMap"> SELECT - guacamole_connection_group_permission.user_id, - username, + entity_id, permission, connection_group_id FROM guacamole_connection_group_permission - JOIN guacamole_user ON guacamole_connection_group_permission.user_id = guacamole_user.user_id - WHERE guacamole_connection_group_permission.user_id = #{user.objectID,jdbcType=INTEGER} + WHERE entity_id = #{entity.entityID,jdbcType=INTEGER} </select> @@ -50,26 +47,24 @@ <select id="selectOne" resultMap="ConnectionGroupPermissionResultMap"> SELECT - guacamole_connection_group_permission.user_id, - username, + entity_id, permission, connection_group_id FROM guacamole_connection_group_permission - JOIN guacamole_user ON guacamole_connection_group_permission.user_id = guacamole_user.user_id WHERE - guacamole_connection_group_permission.user_id = #{user.objectID,jdbcType=INTEGER} + entity_id = #{entity.entityID,jdbcType=INTEGER} AND permission = #{type,jdbcType=VARCHAR}::guacamole_object_permission_type AND connection_group_id = #{identifier,jdbcType=INTEGER}::integer </select> - <!-- Select identifiers accessible by the given user for the given permissions --> + <!-- Select identifiers accessible by the given entity for the given permissions --> <select id="selectAccessibleIdentifiers" resultType="string"> SELECT DISTINCT connection_group_id FROM guacamole_connection_group_permission WHERE - user_id = #{user.objectID,jdbcType=INTEGER} + entity_id = #{entity.entityID,jdbcType=INTEGER} AND connection_group_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -87,10 +82,10 @@ <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> DELETE FROM guacamole_connection_group_permission - WHERE (user_id, permission, connection_group_id) IN + WHERE (entity_id, permission, connection_group_id) IN <foreach collection="permissions" item="permission" open="(" separator="," close=")"> - (#{permission.userID,jdbcType=INTEGER}, + (#{permission.entityID,jdbcType=INTEGER}, #{permission.type,jdbcType=VARCHAR}::guacamole_object_permission_type, #{permission.objectIdentifier,jdbcType=INTEGER}::integer) </foreach> @@ -101,25 +96,25 @@ <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> INSERT INTO guacamole_connection_group_permission ( - user_id, + entity_id, permission, connection_group_id ) SELECT DISTINCT - permissions.user_id, + permissions.entity_id, permissions.permission, permissions.connection_group_id FROM <foreach collection="permissions" item="permission" open="(" separator="UNION ALL" close=")"> - SELECT #{permission.userID,jdbcType=INTEGER} AS user_id, + SELECT #{permission.entityID,jdbcType=INTEGER} AS entity_id, #{permission.type,jdbcType=VARCHAR}::guacamole_object_permission_type AS permission, #{permission.objectIdentifier,jdbcType=INTEGER}::integer AS connection_group_id </foreach> AS permissions - WHERE (user_id, permission, connection_group_id) NOT IN ( + WHERE (entity_id, permission, connection_group_id) NOT IN ( SELECT - guacamole_connection_group_permission.user_id, + guacamole_connection_group_permission.entity_id, guacamole_connection_group_permission.permission, guacamole_connection_group_permission.connection_group_id FROM guacamole_connection_group_permission http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/b499092d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml index eed00aa..99eed28 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml @@ -25,24 +25,21 @@ <!-- Result mapper for connection permissions --> <resultMap id="ConnectionPermissionResultMap" type="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> - <result column="user_id" property="userID" jdbcType="INTEGER"/> - <result column="username" property="username" jdbcType="VARCHAR"/> + <result column="entity_id" property="entityID" jdbcType="INTEGER"/> <result column="permission" property="type" jdbcType="VARCHAR" javaType="org.apache.guacamole.net.auth.permission.ObjectPermission$Type"/> <result column="connection_id" property="objectIdentifier" jdbcType="INTEGER"/> </resultMap> - <!-- Select all permissions for a given user --> + <!-- Select all permissions for a given entity --> <select id="select" resultMap="ConnectionPermissionResultMap"> SELECT - guacamole_connection_permission.user_id, - username, + entity_id, permission, connection_id FROM guacamole_connection_permission - JOIN guacamole_user ON guacamole_connection_permission.user_id = guacamole_user.user_id - WHERE guacamole_connection_permission.user_id = #{user.objectID,jdbcType=INTEGER} + WHERE entity_id = #{entity.entityID,jdbcType=INTEGER} </select> @@ -50,26 +47,24 @@ <select id="selectOne" resultMap="ConnectionPermissionResultMap"> SELECT - guacamole_connection_permission.user_id, - username, + entity_id, permission, connection_id FROM guacamole_connection_permission - JOIN guacamole_user ON guacamole_connection_permission.user_id = guacamole_user.user_id WHERE - guacamole_connection_permission.user_id = #{user.objectID,jdbcType=INTEGER} + entity_id = #{entity.entityID,jdbcType=INTEGER} AND permission = #{type,jdbcType=VARCHAR}::guacamole_object_permission_type AND connection_id = #{identifier,jdbcType=INTEGER}::integer </select> - <!-- Select identifiers accessible by the given user for the given permissions --> + <!-- Select identifiers accessible by the given entity for the given permissions --> <select id="selectAccessibleIdentifiers" resultType="string"> SELECT DISTINCT connection_id FROM guacamole_connection_permission WHERE - user_id = #{user.objectID,jdbcType=INTEGER} + entity_id = #{entity.entityID,jdbcType=INTEGER} AND connection_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -87,10 +82,10 @@ <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> DELETE FROM guacamole_connection_permission - WHERE (user_id, permission, connection_id) IN + WHERE (entity_id, permission, connection_id) IN <foreach collection="permissions" item="permission" open="(" separator="," close=")"> - (#{permission.userID,jdbcType=INTEGER}, + (#{permission.entityID,jdbcType=INTEGER}, #{permission.type,jdbcType=VARCHAR}::guacamole_object_permission_type, #{permission.objectIdentifier,jdbcType=INTEGER}::integer) </foreach> @@ -101,25 +96,25 @@ <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> INSERT INTO guacamole_connection_permission ( - user_id, + entity_id, permission, connection_id ) SELECT DISTINCT - permissions.user_id, + permissions.entity_id, permissions.permission, permissions.connection_id FROM <foreach collection="permissions" item="permission" open="(" separator="UNION ALL" close=")"> - SELECT #{permission.userID,jdbcType=INTEGER} AS user_id, + SELECT #{permission.entityID,jdbcType=INTEGER} AS entity_id, #{permission.type,jdbcType=VARCHAR}::guacamole_object_permission_type AS permission, #{permission.objectIdentifier,jdbcType=INTEGER}::integer AS connection_id </foreach> AS permissions - WHERE (user_id, permission, connection_id) NOT IN ( + WHERE (entity_id, permission, connection_id) NOT IN ( SELECT - guacamole_connection_permission.user_id, + guacamole_connection_permission.entity_id, guacamole_connection_permission.permission, guacamole_connection_permission.connection_id FROM guacamole_connection_permission http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/b499092d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml index faddcdb..73d0ad4 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml @@ -25,24 +25,21 @@ <!-- Result mapper for sharing profile permissions --> <resultMap id="SharingProfilePermissionResultMap" type="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> - <result column="user_id" property="userID" jdbcType="INTEGER"/> - <result column="username" property="username" jdbcType="VARCHAR"/> + <result column="entity_id" property="entityID" jdbcType="INTEGER"/> <result column="permission" property="type" jdbcType="VARCHAR" javaType="org.apache.guacamole.net.auth.permission.ObjectPermission$Type"/> <result column="sharing_profile_id" property="objectIdentifier" jdbcType="INTEGER"/> </resultMap> - <!-- Select all permissions for a given user --> + <!-- Select all permissions for a given entity --> <select id="select" resultMap="SharingProfilePermissionResultMap"> SELECT - guacamole_sharing_profile_permission.user_id, - username, + entity_id, permission, sharing_profile_id FROM guacamole_sharing_profile_permission - JOIN guacamole_user ON guacamole_sharing_profile_permission.user_id = guacamole_user.user_id - WHERE guacamole_sharing_profile_permission.user_id = #{user.objectID,jdbcType=INTEGER} + WHERE entity_id = #{entity.entityID,jdbcType=INTEGER} </select> @@ -50,26 +47,24 @@ <select id="selectOne" resultMap="SharingProfilePermissionResultMap"> SELECT - guacamole_sharing_profile_permission.user_id, - username, + entity_id, permission, sharing_profile_id FROM guacamole_sharing_profile_permission - JOIN guacamole_user ON guacamole_sharing_profile_permission.user_id = guacamole_user.user_id WHERE - guacamole_sharing_profile_permission.user_id = #{user.objectID,jdbcType=INTEGER} + entity_id = #{entity.entityID,jdbcType=INTEGER} AND permission = #{type,jdbcType=VARCHAR}::guacamole_object_permission_type AND sharing_profile_id = #{identifier,jdbcType=INTEGER}::integer </select> - <!-- Select identifiers accessible by the given user for the given permissions --> + <!-- Select identifiers accessible by the given entity for the given permissions --> <select id="selectAccessibleIdentifiers" resultType="string"> SELECT DISTINCT sharing_profile_id FROM guacamole_sharing_profile_permission WHERE - user_id = #{user.objectID,jdbcType=INTEGER} + entity_id = #{entity.entityID,jdbcType=INTEGER} AND sharing_profile_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -87,10 +82,10 @@ <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> DELETE FROM guacamole_sharing_profile_permission - WHERE (user_id, permission, sharing_profile_id) IN + WHERE (entity_id, permission, sharing_profile_id) IN <foreach collection="permissions" item="permission" open="(" separator="," close=")"> - (#{permission.userID,jdbcType=INTEGER}, + (#{permission.entityID,jdbcType=INTEGER}, #{permission.type,jdbcType=VARCHAR}::guacamole_object_permission_type, #{permission.objectIdentifier,jdbcType=INTEGER}::integer) </foreach> @@ -101,25 +96,25 @@ <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> INSERT INTO guacamole_sharing_profile_permission ( - user_id, + entity_id, permission, sharing_profile_id ) SELECT DISTINCT - permissions.user_id, + permissions.entity_id, permissions.permission, permissions.sharing_profile_id FROM <foreach collection="permissions" item="permission" open="(" separator="UNION ALL" close=")"> - SELECT #{permission.userID,jdbcType=INTEGER} AS user_id, + SELECT #{permission.entityID,jdbcType=INTEGER} AS entity_id, #{permission.type,jdbcType=VARCHAR}::guacamole_object_permission_type AS permission, #{permission.objectIdentifier,jdbcType=INTEGER}::integer AS sharing_profile_id </foreach> AS permissions - WHERE (user_id, permission, sharing_profile_id) NOT IN ( + WHERE (entity_id, permission, sharing_profile_id) NOT IN ( SELECT - guacamole_sharing_profile_permission.user_id, + guacamole_sharing_profile_permission.entity_id, guacamole_sharing_profile_permission.permission, guacamole_sharing_profile_permission.sharing_profile_id FROM guacamole_sharing_profile_permission http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/b499092d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml index 96ffb21..5e75891 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml @@ -25,22 +25,19 @@ <!-- Result mapper for system permissions --> <resultMap id="SystemPermissionResultMap" type="org.apache.guacamole.auth.jdbc.permission.SystemPermissionModel"> - <result column="user_id" property="userID" jdbcType="INTEGER"/> - <result column="username" property="username" jdbcType="VARCHAR"/> + <result column="entity_id" property="entityID" jdbcType="INTEGER"/> <result column="permission" property="type" jdbcType="VARCHAR" javaType="org.apache.guacamole.net.auth.permission.SystemPermission$Type"/> </resultMap> - <!-- Select all permissions for a given user --> + <!-- Select all permissions for a given entity --> <select id="select" resultMap="SystemPermissionResultMap"> SELECT - guacamole_system_permission.user_id, - username, + entity_id, permission FROM guacamole_system_permission - JOIN guacamole_user ON guacamole_system_permission.user_id = guacamole_user.user_id - WHERE guacamole_system_permission.user_id = #{user.objectID,jdbcType=INTEGER} + WHERE entity_id = #{entity.entityID,jdbcType=INTEGER} </select> @@ -48,13 +45,11 @@ <select id="selectOne" resultMap="SystemPermissionResultMap"> SELECT - guacamole_system_permission.user_id, - username, + entity_id, permission FROM guacamole_system_permission - JOIN guacamole_user ON guacamole_system_permission.user_id = guacamole_user.user_id WHERE - guacamole_system_permission.user_id = #{user.objectID,jdbcType=INTEGER} + entity_id = #{entity.entityID,jdbcType=INTEGER} AND permission = #{type,jdbcType=VARCHAR}::guacamole_system_permission_type </select> @@ -63,10 +58,10 @@ <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.SystemPermissionModel"> DELETE FROM guacamole_system_permission - WHERE (user_id, permission) IN + WHERE (entity_id, permission) IN <foreach collection="permissions" item="permission" open="(" separator="," close=")"> - (#{permission.userID,jdbcType=INTEGER}, + (#{permission.entityID,jdbcType=INTEGER}, #{permission.type,jdbcType=VARCHAR}::guacamole_system_permission_type) </foreach> @@ -76,22 +71,22 @@ <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.SystemPermissionModel"> INSERT INTO guacamole_system_permission ( - user_id, + entity_id, permission ) SELECT DISTINCT - permissions.user_id, + permissions.entity_id, permissions.permission FROM <foreach collection="permissions" item="permission" open="(" separator="UNION ALL" close=")"> - SELECT #{permission.userID,jdbcType=INTEGER} AS user_id, + SELECT #{permission.entityID,jdbcType=INTEGER} AS entity_id, #{permission.type,jdbcType=VARCHAR}::guacamole_system_permission_type AS permission </foreach> AS permissions - WHERE (user_id, permission) NOT IN ( + WHERE (entity_id, permission) NOT IN ( SELECT - guacamole_system_permission.user_id, + guacamole_system_permission.entity_id, guacamole_system_permission.permission FROM guacamole_system_permission ); http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/b499092d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml index 0126ae5..d6680ea 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml @@ -25,25 +25,25 @@ <!-- Result mapper for user permissions --> <resultMap id="UserPermissionResultMap" type="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> - <result column="user_id" property="userID" jdbcType="INTEGER"/> - <result column="username" property="username" jdbcType="VARCHAR"/> + <result column="entity_id" property="entityID" jdbcType="INTEGER"/> <result column="permission" property="type" jdbcType="VARCHAR" javaType="org.apache.guacamole.net.auth.permission.ObjectPermission$Type"/> - <result column="affected_username" property="objectIdentifier" jdbcType="INTEGER"/> + <result column="affected_name" property="objectIdentifier" jdbcType="INTEGER"/> </resultMap> - <!-- Select all permissions for a given user --> + <!-- Select all permissions for a given entity --> <select id="select" resultMap="UserPermissionResultMap"> SELECT - guacamole_user_permission.user_id, - guacamole_user.username, + guacamole_user_permission.entity_id, permission, - affected.username AS affected_username + affected_entity.name AS affected_name FROM guacamole_user_permission - JOIN guacamole_user ON guacamole_user_permission.user_id = guacamole_user.user_id - JOIN guacamole_user affected ON guacamole_user_permission.affected_user_id = affected.user_id - WHERE guacamole_user_permission.user_id = #{user.objectID,jdbcType=INTEGER} + JOIN guacamole_user affected_user ON guacamole_user_permission.affected_user_id = affected_user.user_id + JOIN guacamole_entity affected_entity ON affected_user.entity_id = affected_entity.entity_id + WHERE + guacamole_user_permission.entity_id = #{entity.entityID,jdbcType=INTEGER} + AND affected_entity.type = 'USER'::guacamole_entity_type </select> @@ -51,38 +51,40 @@ <select id="selectOne" resultMap="UserPermissionResultMap"> SELECT - guacamole_user_permission.user_id, - guacamole_user.username, + guacamole_user_permission.entity_id, permission, - affected.username AS affected_username + affected_entity.name AS affected_name FROM guacamole_user_permission - JOIN guacamole_user ON guacamole_user_permission.user_id = guacamole_user.user_id - JOIN guacamole_user affected ON guacamole_user_permission.affected_user_id = affected.user_id + JOIN guacamole_user affected_user ON guacamole_user_permission.affected_user_id = affected_user.user_id + JOIN guacamole_entity affected_entity ON affected_user.entity_id = affected_entity.entity_id WHERE - guacamole_user_permission.user_id = #{user.objectID,jdbcType=INTEGER} + guacamole_user_permission.entity_id = #{entity.entityID,jdbcType=INTEGER} AND permission = #{type,jdbcType=VARCHAR}::guacamole_object_permission_type - AND affected.username = #{identifier,jdbcType=INTEGER} + AND affected_entity.name = #{identifier,jdbcType=VARCHAR} + AND affected_entity.type = 'USER'::guacamole_entity_type </select> - <!-- Select identifiers accessible by the given user for the given permissions --> + <!-- Select identifiers accessible by the given entity for the given permissions --> <select id="selectAccessibleIdentifiers" resultType="string"> - SELECT DISTINCT username + SELECT DISTINCT affected_entity.name FROM guacamole_user_permission - JOIN guacamole_user ON guacamole_user_permission.affected_user_id = guacamole_user.user_id + JOIN guacamole_user affected_user ON guacamole_user_permission.affected_user_id = affected_user.user_id + JOIN guacamole_entity affected_entity ON affected_user.entity_id = affected_entity.entity_id WHERE - guacamole_user_permission.user_id = #{user.objectID,jdbcType=INTEGER} - AND username IN + guacamole_user_permission.entity_id = #{entity.entityID,jdbcType=INTEGER} + AND affected_entity.name IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> - #{identifier,jdbcType=INTEGER} + #{identifier,jdbcType=VARCHAR} </foreach> AND permission IN <foreach collection="permissions" item="permission" open="(" separator="," close=")"> #{permission,jdbcType=VARCHAR}::guacamole_object_permission_type </foreach> + AND affected_entity.type = 'USER'::guacamole_entity_type </select> @@ -90,16 +92,18 @@ <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> DELETE FROM guacamole_user_permission - USING guacamole_user affected + USING guacamole_user affected_user, guacamole_entity affected_entity WHERE - guacamole_user_permission.affected_user_id = affected.user_id - AND (guacamole_user_permission.user_id, permission, affected.username) IN + guacamole_user_permission.affected_user_id = affected_user.user_id + AND affected_user.entity_id = affected_entity.entity_id + AND (guacamole_user_permission.entity_id, permission, affected_entity.name) IN <foreach collection="permissions" item="permission" open="(" separator="," close=")"> - (#{permission.userID,jdbcType=INTEGER}, + (#{permission.entityID,jdbcType=INTEGER}, #{permission.type,jdbcType=VARCHAR}::guacamole_object_permission_type, #{permission.objectIdentifier,jdbcType=INTEGER}) </foreach> + AND affected_entity.type = 'USER'::guacamole_entity_type </delete> @@ -107,26 +111,29 @@ <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> INSERT INTO guacamole_user_permission ( - user_id, + entity_id, permission, affected_user_id ) SELECT DISTINCT - permissions.user_id, + permissions.entity_id, permissions.permission, - guacamole_user.user_id + affected_user.user_id FROM <foreach collection="permissions" item="permission" open="(" separator="UNION ALL" close=")"> - SELECT #{permission.userID,jdbcType=INTEGER} AS user_id, + SELECT #{permission.entityID,jdbcType=INTEGER} AS entity_id, #{permission.type,jdbcType=VARCHAR}::guacamole_object_permission_type AS permission, - #{permission.objectIdentifier,jdbcType=INTEGER} AS username + #{permission.objectIdentifier,jdbcType=VARCHAR}::text AS affected_name </foreach> AS permissions - JOIN guacamole_user ON guacamole_user.username = permissions.username - WHERE (permissions.user_id, permissions.permission, guacamole_user.user_id) NOT IN ( + JOIN guacamole_entity affected_entity ON + affected_entity.name = permissions.affected_name + AND affected_entity.type = 'USER'::guacamole_entity_type + JOIN guacamole_user affected_user ON affected_user.entity_id = affected_entity.entity_id + WHERE (permissions.entity_id, permissions.permission, affected_user.user_id) NOT IN ( SELECT - guacamole_user_permission.user_id, + guacamole_user_permission.entity_id, guacamole_user_permission.permission, guacamole_user_permission.affected_user_id FROM guacamole_user_permission http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/b499092d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml index 801d6e3..66bd701 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml @@ -52,7 +52,7 @@ SELECT sharing_profile_id FROM guacamole_sharing_profile_permission WHERE - user_id = #{user.objectID,jdbcType=INTEGER} + entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ' </select> @@ -99,7 +99,7 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER}::integer </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ'; SELECT @@ -113,7 +113,7 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER}::integer </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ'; </select> http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/b499092d/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 index 41591fa..e9c857a 100644 --- 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 @@ -41,8 +41,9 @@ 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 + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id WHERE - guacamole_user.username = #{username,jdbcType=VARCHAR} + guacamole_entity.name = #{username,jdbcType=VARCHAR} ORDER BY guacamole_user_password_history.password_date DESC LIMIT #{maxHistorySize} http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/b499092d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml index e183fe2..796962d 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml @@ -28,7 +28,8 @@ <!-- User properties --> <id column="user_id" property="objectID" jdbcType="INTEGER"/> - <result column="username" property="identifier" jdbcType="VARCHAR"/> + <result column="entity_id" property="entityID" jdbcType="INTEGER"/> + <result column="name" property="identifier" jdbcType="VARCHAR"/> <result column="password_hash" property="passwordHash" jdbcType="BINARY"/> <result column="password_salt" property="passwordSalt" jdbcType="BINARY"/> <result column="password_date" property="passwordDate" jdbcType="TIMESTAMP"/> @@ -57,17 +58,20 @@ <!-- Select all usernames --> <select id="selectIdentifiers" resultType="string"> - SELECT username - FROM guacamole_user + SELECT name + FROM guacamole_entity + WHERE guacamole_entity.type = 'USER'::guacamole_entity_type </select> <!-- Select usernames of all readable users --> <select id="selectReadableIdentifiers" resultType="string"> - SELECT username + SELECT guacamole_entity.name FROM guacamole_user + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id JOIN guacamole_user_permission ON affected_user_id = guacamole_user.user_id WHERE - guacamole_user_permission.user_id = #{user.objectID,jdbcType=INTEGER} + guacamole_user_permission.entity_id = #{user.entityID,jdbcType=INTEGER} + AND guacamole_entity.type = 'USER'::guacamole_entity_type AND permission = 'READ' </select> @@ -77,7 +81,8 @@ SELECT guacamole_user.user_id, - guacamole_user.username, + guacamole_entity.entity_id, + guacamole_entity.name, password_hash, password_salt, password_date, @@ -94,13 +99,15 @@ organizational_role, MAX(start_date) AS last_active FROM guacamole_user + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id LEFT JOIN guacamole_user_history ON guacamole_user_history.user_id = guacamole_user.user_id - WHERE guacamole_user.username IN + WHERE guacamole_entity.name IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> #{identifier,jdbcType=VARCHAR} </foreach> - GROUP BY guacamole_user.user_id; + AND guacamole_entity.type = 'USER'::guacamole_entity_type + GROUP BY guacamole_user.user_id, guacamole_entity.entity_id; SELECT guacamole_user_attribute.user_id, @@ -108,11 +115,13 @@ guacamole_user_attribute.attribute_value FROM guacamole_user_attribute JOIN guacamole_user ON guacamole_user.user_id = guacamole_user_attribute.user_id - WHERE username IN + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id + WHERE guacamole_entity.name IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> #{identifier,jdbcType=VARCHAR} - </foreach>; + </foreach> + AND guacamole_entity.type = 'USER'::guacamole_entity_type; </select> @@ -122,7 +131,8 @@ SELECT guacamole_user.user_id, - guacamole_user.username, + guacamole_entity.entity_id, + guacamole_entity.name, password_hash, password_salt, password_date, @@ -139,16 +149,18 @@ organizational_role, MAX(start_date) AS last_active FROM guacamole_user + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id JOIN guacamole_user_permission ON affected_user_id = guacamole_user.user_id LEFT JOIN guacamole_user_history ON guacamole_user_history.user_id = guacamole_user.user_id - WHERE guacamole_user.username IN + WHERE guacamole_entity.name IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> #{identifier,jdbcType=VARCHAR} </foreach> - AND guacamole_user_permission.user_id = #{user.objectID,jdbcType=INTEGER} + AND guacamole_entity.type = 'USER'::guacamole_entity_type + AND guacamole_user_permission.entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ' - GROUP BY guacamole_user.user_id; + GROUP BY guacamole_user.user_id, guacamole_entity.entity_id; SELECT guacamole_user_attribute.user_id, @@ -156,13 +168,15 @@ guacamole_user_attribute.attribute_value FROM guacamole_user_attribute JOIN guacamole_user ON guacamole_user.user_id = guacamole_user_attribute.user_id + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id JOIN guacamole_user_permission ON affected_user_id = guacamole_user.user_id - WHERE username IN + WHERE guacamole_entity.name IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> #{identifier,jdbcType=VARCHAR} </foreach> - AND guacamole_user_permission.user_id = #{user.objectID,jdbcType=INTEGER} + AND guacamole_entity.type = 'USER'::guacamole_entity_type + AND guacamole_user_permission.entity_id = #{user.entityID,jdbcType=INTEGER} AND permission = 'READ'; </select> @@ -173,7 +187,8 @@ SELECT guacamole_user.user_id, - guacamole_user.username, + guacamole_entity.entity_id, + guacamole_entity.name, password_hash, password_salt, password_date, @@ -190,10 +205,12 @@ organizational_role, MAX(start_date) AS last_active FROM guacamole_user + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id LEFT JOIN guacamole_user_history ON guacamole_user_history.user_id = guacamole_user.user_id WHERE - guacamole_user.username = #{username,jdbcType=VARCHAR} - GROUP BY guacamole_user.user_id; + guacamole_entity.name = #{username,jdbcType=VARCHAR} + AND guacamole_entity.type = 'USER'::guacamole_entity_type + GROUP BY guacamole_user.user_id, guacamole_entity.entity_id; SELECT guacamole_user_attribute.user_id, @@ -201,14 +218,19 @@ guacamole_user_attribute.attribute_value FROM guacamole_user_attribute JOIN guacamole_user ON guacamole_user.user_id = guacamole_user_attribute.user_id - WHERE username = #{username,jdbcType=VARCHAR}; + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id + WHERE + guacamole_entity.name = #{username,jdbcType=VARCHAR} + AND guacamole_entity.type = 'USER'::guacamole_entity_type </select> <!-- Delete single user by username --> <delete id="delete"> - DELETE FROM guacamole_user - WHERE username = #{identifier,jdbcType=VARCHAR} + DELETE FROM guacamole_entity + WHERE + name = #{identifier,jdbcType=VARCHAR} + AND type = 'USER'::guacamole_entity_type </delete> <!-- Insert single user --> @@ -216,7 +238,7 @@ parameterType="org.apache.guacamole.auth.jdbc.user.UserModel"> INSERT INTO guacamole_user ( - username, + entity_id, password_hash, password_salt, password_date, @@ -233,7 +255,7 @@ organizational_role ) VALUES ( - #{object.identifier,jdbcType=VARCHAR}, + #{object.entityID,jdbcType=VARCHAR}, #{object.passwordHash,jdbcType=BINARY}, #{object.passwordSalt,jdbcType=BINARY}, #{object.passwordDate,jdbcType=TIMESTAMP}, http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/b499092d/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml index 014b38a..20cb2a8 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml @@ -44,8 +44,9 @@ guacamole_user_history.end_date FROM guacamole_user_history JOIN guacamole_user ON guacamole_user_history.user_id = guacamole_user.user_id + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id WHERE - guacamole_user.username = #{username,jdbcType=VARCHAR} + guacamole_entity.name = #{username,jdbcType=VARCHAR} ORDER BY guacamole_user_history.start_date DESC, guacamole_user_history.end_date DESC @@ -66,7 +67,10 @@ VALUES ( #{record.remoteHost,jdbcType=VARCHAR}, (SELECT user_id FROM guacamole_user - WHERE username = #{record.username,jdbcType=VARCHAR}), + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id + WHERE + guacamole_entity.name = #{record.username,jdbcType=VARCHAR} + AND guacamole_entity.type = 'USER'::guacamole_entity_type), #{record.username,jdbcType=VARCHAR}, #{record.startDate,jdbcType=TIMESTAMP}, #{record.endDate,jdbcType=TIMESTAMP} @@ -79,7 +83,10 @@ UPDATE guacamole_user_history SET remote_host = #{record.remoteHost,jdbcType=VARCHAR}, user_id = (SELECT user_id FROM guacamole_user - WHERE username = #{record.username,jdbcType=VARCHAR}), + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id + WHERE + guacamole_entity.name = #{record.username,jdbcType=VARCHAR} + AND guacamole_entity.type = 'USER'::guacamole_entity_type), username = #{record.username,jdbcType=VARCHAR}, start_date = #{record.startDate,jdbcType=TIMESTAMP}, end_date = #{record.endDate,jdbcType=TIMESTAMP} @@ -105,7 +112,10 @@ guacamole_user_history.user_id IN ( SELECT user_id FROM guacamole_user - WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0 + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id + WHERE + POSITION(#{term.term,jdbcType=VARCHAR} IN guacamole_entity.name) > 0 + AND guacamole_entity.type = 'USER'::guacamole_entity_type), ) <if test="term.startDate != null and term.endDate != null"> @@ -157,7 +167,10 @@ guacamole_user_history.user_id IN ( SELECT user_id FROM guacamole_user - WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0 + JOIN guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id + WHERE + POSITION(#{term.term,jdbcType=VARCHAR} IN guacamole_entity.name) > 0 + AND guacamole_entity.type = 'USER'::guacamole_entity_type ) <if test="term.startDate != null and term.endDate != null">
