GUACAMOLE-220: Update SQL Server mapping with respect to user group support.
Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/ee356201 Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/ee356201 Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/ee356201 Branch: refs/heads/master Commit: ee356201948c7d566b37733dcbe1fb0098d95a6d Parents: dec7b3c Author: Michael Jumper <mjum...@apache.org> Authored: Sat Sep 8 18:52:10 2018 -0700 Committer: Michael Jumper <mjum...@apache.org> Committed: Wed Sep 19 23:56:53 2018 -0700 ---------------------------------------------------------------------- .../guacamole/auth/jdbc/base/EntityMapper.xml | 123 ++++++++++ .../auth/jdbc/connection/ConnectionMapper.xml | 30 ++- .../jdbc/connection/ConnectionRecordMapper.xml | 22 +- .../connectiongroup/ConnectionGroupMapper.xml | 36 ++- .../ConnectionGroupPermissionMapper.xml | 52 +++-- .../permission/ConnectionPermissionMapper.xml | 44 ++-- .../SharingProfilePermissionMapper.xml | 44 ++-- .../jdbc/permission/SystemPermissionMapper.xml | 40 ++-- .../permission/UserGroupPermissionMapper.xml | 153 +++++++++++++ .../jdbc/permission/UserPermissionMapper.xml | 100 ++++---- .../sharingprofile/SharingProfileMapper.xml | 18 +- .../auth/jdbc/user/PasswordRecordMapper.xml | 3 +- .../guacamole/auth/jdbc/user/UserMapper.xml | 84 +++++-- .../jdbc/user/UserParentUserGroupMapper.xml | 96 ++++++++ .../auth/jdbc/user/UserRecordMapper.xml | 29 ++- .../auth/jdbc/usergroup/UserGroupMapper.xml | 229 +++++++++++++++++++ .../UserGroupMemberUserGroupMapper.xml | 93 ++++++++ .../usergroup/UserGroupMemberUserMapper.xml | 93 ++++++++ .../UserGroupParentUserGroupMapper.xml | 96 ++++++++ 19 files changed, 1218 insertions(+), 167 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/ee356201/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/base/EntityMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/base/EntityMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/base/EntityMapper.xml new file mode 100644 index 0000000..f61463a --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/base/EntityMapper.xml @@ -0,0 +1,123 @@ +<?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.base.EntityMapper" > + + <!-- + * SQL fragment which tests whether the value of the given column matches + * the given entity ID. If group identifiers are provided, the IDs of the + * entities for all groups having those identifiers are tested, as well. + * Disabled groups are ignored. + * + * @param column + * The name of the column to test. This column MUST contain an entity + * ID (a foreign key into the [guacamole_entity] table). + * + * @param entityID + * The ID of the specific entity to test the column against. + * + * @param groups + * A collection of group identifiers to additionally test the column + * against. Though this functionality is optional, a collection must + * always be given, even if that collection is empty. + --> + <sql id="isRelatedEntity"> + ( + ${column} = ${entityID} + <if test="!${groups}.isEmpty()"> + OR ${column} IN ( + SELECT [guacamole_entity].entity_id + FROM [guacamole_entity] + JOIN [guacamole_user_group] ON [guacamole_user_group].entity_id = [guacamole_entity].entity_id + WHERE + type = 'USER_GROUP' + AND name IN + <foreach collection="${groups}" item="effectiveGroup" + open="(" separator="," close=")"> + #{effectiveGroup,jdbcType=VARCHAR} + </foreach> + AND disabled = 0 + ) + </if> + ) + </sql> + + <!-- Select names of all effective groups (including inherited) --> + <select id="selectEffectiveGroupIdentifiers" resultType="string"> + + WITH [related_entity] ([entity_id]) AS ( + SELECT + [guacamole_user_group].entity_id + FROM [guacamole_user_group] + JOIN [guacamole_user_group_member] ON [guacamole_user_group].user_group_id = [guacamole_user_group_member].user_group_id + WHERE + [guacamole_user_group_member].member_entity_id = #{entity.entityID} + AND [guacamole_user_group].disabled = 0 + <if test="!effectiveGroups.isEmpty()"> + UNION ALL + SELECT + [guacamole_entity].entity_id + FROM [guacamole_entity] + JOIN [guacamole_user_group] ON [guacamole_user_group].entity_id = [guacamole_entity].entity_id + WHERE + type = 'USER_GROUP' + AND name IN + <foreach collection="effectiveGroups" item="effectiveGroup" + open="(" separator="," close=")"> + #{effectiveGroup,jdbcType=VARCHAR} + </foreach> + AND [guacamole_user_group].disabled = 0 + </if> + UNION ALL + SELECT + [guacamole_user_group].entity_id + FROM [related_entity] + JOIN [guacamole_user_group_member] ON [related_entity].entity_id = [guacamole_user_group_member].member_entity_id + JOIN [guacamole_user_group] ON [guacamole_user_group].user_group_id = [guacamole_user_group_member].user_group_id + WHERE + [guacamole_user_group].disabled = 0 + ) + SELECT DISTINCT name + FROM [related_entity] + JOIN [guacamole_entity] ON [related_entity].entity_id = [guacamole_entity].entity_id + WHERE + [guacamole_entity].type = 'USER_GROUP'; + + </select> + + <!-- Insert single entity --> + <insert id="insert" useGeneratedKeys="true" keyProperty="entity.entityID" + parameterType="org.apache.guacamole.auth.jdbc.base.EntityModel"> + + INSERT INTO [guacamole_entity] ( + name, + type + ) + VALUES ( + #{entity.identifier,jdbcType=VARCHAR}, + #{entity.entityType,jdbcType=VARCHAR} + ) + + </insert> + +</mapper> http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/ee356201/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml index fb61757..54cb575 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml @@ -68,7 +68,11 @@ SELECT connection_id FROM [guacamole_connection_permission] WHERE - user_id = #{user.objectID,jdbcType=INTEGER} + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ' </select> @@ -89,7 +93,11 @@ WHERE <if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}</if> <if test="parentIdentifier == null">parent_id IS NULL</if> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ' </select> @@ -170,7 +178,11 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER} </foreach> - AND [guacamole_connection_permission].user_id = #{user.objectID,jdbcType=INTEGER} + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="[guacamole_connection_permission].entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ'; SELECT primary_connection_id, [guacamole_sharing_profile].sharing_profile_id @@ -181,7 +193,11 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER} </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ'; SELECT @@ -195,7 +211,11 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER} </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ'; </select> http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/ee356201/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml index d7ae41c..2abf1ae 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.xml @@ -79,7 +79,10 @@ #{record.sharingProfileIdentifier,jdbcType=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'), #{record.username,jdbcType=VARCHAR}, #{record.startDate,jdbcType=TIMESTAMP}, #{record.endDate,jdbcType=TIMESTAMP} @@ -161,13 +164,21 @@ <!-- Restrict to readable connections --> JOIN [guacamole_connection_permission] ON [guacamole_connection_history].connection_id = [guacamole_connection_permission].connection_id - AND [guacamole_connection_permission].user_id = #{user.objectID,jdbcType=INTEGER} + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="[guacamole_connection_permission].entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND [guacamole_connection_permission].permission = 'READ' <!-- Restrict to readable users --> JOIN [guacamole_user_permission] ON [guacamole_connection_history].user_id = [guacamole_user_permission].affected_user_id - AND [guacamole_user_permission].user_id = #{user.objectID,jdbcType=INTEGER} + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="[guacamole_user_permission].entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND [guacamole_user_permission].permission = 'READ' <!-- Search terms --> @@ -178,7 +189,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' ) OR [guacamole_connection_history].connection_id IN ( http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/ee356201/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml index f75943e..32c1d13 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml @@ -69,7 +69,11 @@ SELECT connection_group_id FROM [guacamole_connection_group_permission] WHERE - user_id = #{user.objectID,jdbcType=INTEGER} + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ' </select> @@ -90,7 +94,11 @@ WHERE <if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}</if> <if test="parentIdentifier == null">parent_id IS NULL</if> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ' </select> @@ -161,7 +169,11 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER} </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ'; SELECT parent_id, [guacamole_connection_group].connection_group_id @@ -172,7 +184,11 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER} </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ'; SELECT parent_id, [guacamole_connection].connection_id @@ -183,7 +199,11 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER} </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ'; SELECT @@ -197,7 +217,11 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER} </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ'; </select> http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/ee356201/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml index 3cc0988..b891868 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml @@ -25,24 +25,26 @@ <!-- 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.entityID,jdbcType=INTEGER} AS 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 + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> </select> @@ -50,26 +52,32 @@ <select id="selectOne" resultMap="ConnectionGroupPermissionResultMap"> SELECT - [guacamole_connection_group_permission].user_id, - username, + #{entity.entityID,jdbcType=INTEGER} AS 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} + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = #{type,jdbcType=VARCHAR} AND connection_group_id = #{identifier,jdbcType=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} + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND connection_group_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -87,12 +95,12 @@ <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 <foreach collection="permissions" item="permission" - open="(" separator="," close=")"> - (#{permission.userID,jdbcType=INTEGER}, - #{permission.type,jdbcType=VARCHAR}, - #{permission.objectIdentifier,jdbcType=INTEGER}) + open="(" separator=" OR " close=")"> + (entity_id = #{permission.entityID,jdbcType=INTEGER} AND + permission = #{permission.type,jdbcType=VARCHAR} AND + connection_group_id = #{permission.objectIdentifier,jdbcType=INTEGER}) </foreach> </delete> @@ -101,24 +109,24 @@ <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} AS permission, #{permission.objectIdentifier,jdbcType=INTEGER} AS connection_group_id </foreach> AS permissions WHERE NOT EXISTS (SELECT 1 FROM [guacamole_connection_group_permission] - WHERE [guacamole_connection_group_permission].user_id = permissions.user_id AND + WHERE [guacamole_connection_group_permission].entity_id = permissions.entity_id AND [guacamole_connection_group_permission].permission = permissions.permission AND [guacamole_connection_group_permission].connection_group_id = permissions.connection_group_id ); http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/ee356201/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml index aaa555a..acd02ab 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml @@ -25,24 +25,26 @@ <!-- 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.entityID,jdbcType=INTEGER} AS 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 + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> </select> @@ -50,26 +52,32 @@ <select id="selectOne" resultMap="ConnectionPermissionResultMap"> SELECT - [guacamole_connection_permission].user_id, - username, + #{entity.entityID,jdbcType=INTEGER} AS 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} + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = #{type,jdbcType=VARCHAR} AND connection_id = #{identifier,jdbcType=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} + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND connection_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -90,7 +98,7 @@ WHERE <foreach collection="permissions" item="permission" open="(" separator=" OR " close=")"> - (user_id = #{permission.userID,jdbcType=INTEGER} AND + (entity_id = #{permission.entityID,jdbcType=INTEGER} AND permission = #{permission.type,jdbcType=VARCHAR} AND connection_id = #{permission.objectIdentifier,jdbcType=INTEGER}) </foreach> @@ -101,24 +109,24 @@ <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} AS permission, #{permission.objectIdentifier,jdbcType=INTEGER} AS connection_id </foreach> AS permissions WHERE NOT EXISTS ( SELECT 1 FROM [guacamole_connection_permission] - WHERE [guacamole_connection_permission].user_id = permissions.user_id AND + WHERE [guacamole_connection_permission].entity_id = permissions.entity_id AND [guacamole_connection_permission].permission = permissions.permission AND [guacamole_connection_permission].connection_id = permissions.connection_id ); http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/ee356201/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml index ab40d2a..7acc290 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml @@ -25,24 +25,26 @@ <!-- 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.entityID,jdbcType=INTEGER} AS 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 + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> </select> @@ -50,26 +52,32 @@ <select id="selectOne" resultMap="SharingProfilePermissionResultMap"> SELECT - [guacamole_sharing_profile_permission].user_id, - username, + #{entity.entityID,jdbcType=INTEGER} AS 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} + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = #{type,jdbcType=VARCHAR} AND sharing_profile_id = #{identifier,jdbcType=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} + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND sharing_profile_id IN <foreach collection="identifiers" item="identifier" open="(" separator="," close=")"> @@ -90,7 +98,7 @@ WHERE <foreach collection="permissions" item="permission" open="(" separator=" OR " close=")"> - (user_id = #{permission.userID,jdbcType=INTEGER} AND + (entity_id = #{permission.entityID,jdbcType=INTEGER} AND permission = #{permission.type,jdbcType=VARCHAR} AND sharing_profile_id = #{permission.objectIdentifier,jdbcType=INTEGER}) </foreach> @@ -101,24 +109,24 @@ <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} AS permission, #{permission.objectIdentifier,jdbcType=INTEGER} AS sharing_profile_id </foreach> AS permissions WHERE NOT EXISTS (SELECT 1 FROM [guacamole_sharing_profile_permission] - WHERE [guacamole_sharing_profile_permission].user_id = permissions.user_id + WHERE [guacamole_sharing_profile_permission].entity_id = permissions.entity_id AND [guacamole_sharing_profile_permission].permission = permissions.permission AND [guacamole_sharing_profile_permission].sharing_profile_id = permissions.sharing_profile_id ); http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/ee356201/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml index 663b94e..2b069fe 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml @@ -25,36 +25,40 @@ <!-- 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, + SELECT DISTINCT + #{entity.entityID} AS 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 + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> </select> <!-- Select the single permission matching the given criteria --> <select id="selectOne" resultMap="SystemPermissionResultMap"> - SELECT - [guacamole_system_permission].user_id, - username, + SELECT DISTINCT + #{entity.entityID} AS 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} + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = #{type,jdbcType=VARCHAR} </select> @@ -66,7 +70,7 @@ WHERE <foreach collection="permissions" item="permission" open="(" separator=" OR " close=")"> - (user_id = #{permission.userID,jdbcType=INTEGER} + (entity_id = #{permission.entityID,jdbcType=INTEGER} AND permission = #{permission.type,jdbcType=VARCHAR}) </foreach> @@ -76,21 +80,21 @@ <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} AS permission </foreach> AS permissions WHERE NOT EXISTS (SELECT 1 FROM [guacamole_system_permission] - WHERE [guacamole_system_permission].user_id = permissions.user_id + WHERE [guacamole_system_permission].entity_id = permissions.entity_id AND [guacamole_system_permission].permission = permissions.permission ); http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/ee356201/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserGroupPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserGroupPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserGroupPermissionMapper.xml new file mode 100644 index 0000000..331a3a3 --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserGroupPermissionMapper.xml @@ -0,0 +1,153 @@ +<?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.permission.UserGroupPermissionMapper" > + + <!-- Result mapper for user group permissions --> + <resultMap id="UserGroupPermissionResultMap" type="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> + <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_name" property="objectIdentifier" jdbcType="INTEGER"/> + </resultMap> + + <!-- Select all permissions for a given entity --> + <select id="select" resultMap="UserGroupPermissionResultMap"> + + SELECT + #{entity.entityID,jdbcType=INTEGER} AS entity_id, + permission, + affected_entity.name AS affected_name + FROM [guacamole_user_group_permission] + JOIN [guacamole_user_group] affected_group ON [guacamole_user_group_permission].affected_user_group_id = affected_group.user_group_id + JOIN [guacamole_entity] affected_entity ON affected_group.entity_id = affected_entity.entity_id + WHERE + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="[guacamole_user_group_permission].entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> + AND affected_entity.type = 'USER_GROUP' + + </select> + + <!-- Select the single permission matching the given criteria --> + <select id="selectOne" resultMap="UserGroupPermissionResultMap"> + + SELECT + #{entity.entityID,jdbcType=INTEGER} AS entity_id, + permission, + affected_entity.name AS affected_name + FROM [guacamole_user_group_permission] + JOIN [guacamole_user_group] affected_group ON [guacamole_user_group_permission].affected_user_group_id = affected_group.user_group_id + JOIN [guacamole_entity] affected_entity ON affected_group.entity_id = affected_entity.entity_id + WHERE + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="[guacamole_user_group_permission].entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> + AND permission = #{type,jdbcType=VARCHAR} + AND affected_entity.name = #{identifier,jdbcType=VARCHAR} + AND affected_entity.type = 'USER_GROUP' + + </select> + + <!-- Select identifiers accessible by the given entity for the given permissions --> + <select id="selectAccessibleIdentifiers" resultType="string"> + + SELECT DISTINCT affected_entity.name + FROM [guacamole_user_group_permission] + JOIN [guacamole_user_group] affected_group ON [guacamole_user_group_permission].affected_user_group_id = affected_group.user_group_id + JOIN [guacamole_entity] affected_entity ON affected_group.entity_id = affected_entity.entity_id + WHERE + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="[guacamole_user_group_permission].entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> + AND affected_entity.name IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=VARCHAR} + </foreach> + AND permission IN + <foreach collection="permissions" item="permission" + open="(" separator="," close=")"> + #{permission,jdbcType=VARCHAR} + </foreach> + AND affected_entity.type = 'USER_GROUP' + + </select> + + <!-- Delete all given permissions --> + <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> + + DELETE [guacamole_user_group_permission] + FROM [guacamole_user_group_permission] + JOIN [guacamole_user_group] affected_group ON [guacamole_user_group_permission].affected_user_group_id = affected_group.user_group_id + JOIN [guacamole_entity] affected_entity ON affected_group.entity_id = affected_entity.entity_id + WHERE + <foreach collection="permissions" item="permission" + open="(" separator=" OR " close=")"> + ([guacamole_user_group_permission].entity_id = #{permission.entityID,jdbcType=INTEGER} AND + permission = #{permission.type,jdbcType=VARCHAR} AND + affected_entity.name = #{permission.objectIdentifier,jdbcType=VARCHAR} AND + affected_entity.type = 'USER_GROUP') + </foreach> + + </delete> + + <!-- Insert all given permissions --> + <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> + + INSERT INTO [guacamole_user_group_permission] ( + entity_id, + permission, + affected_user_group_id + ) + SELECT DISTINCT + permissions.entity_id, + permissions.permission, + affected_group.user_group_id + FROM + <foreach collection="permissions" item="permission" + open="(" separator="UNION ALL" close=")"> + SELECT #{permission.entityID,jdbcType=INTEGER} AS entity_id, + #{permission.type,jdbcType=VARCHAR} AS permission, + #{permission.objectIdentifier,jdbcType=VARCHAR} AS affected_name + </foreach> + AS permissions + JOIN [guacamole_entity] affected_entity ON + affected_entity.name = permissions.affected_name + AND affected_entity.type = 'USER_GROUP' + JOIN [guacamole_user_group] affected_group ON affected_group.entity_id = affected_entity.entity_id + WHERE NOT EXISTS (SELECT 1 FROM [guacamole_user_group_permission] + WHERE [guacamole_user_group_permission].entity_id = permissions.entity_id + AND [guacamole_user_group_permission].permission = permissions.permission + AND [guacamole_user_group_permission].affected_user_group_id = affected_group.user_group_id + ); + + </insert> + +</mapper> \ No newline at end of file http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/ee356201/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml index 453777d..53ed027 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml @@ -25,25 +25,29 @@ <!-- 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, + #{entity.entityID,jdbcType=INTEGER} AS 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 + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="[guacamole_user_permission].entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> + AND affected_entity.type = 'USER' </select> @@ -51,55 +55,66 @@ <select id="selectOne" resultMap="UserPermissionResultMap"> SELECT - [guacamole_user_permission].user_id, - [guacamole_user].username, + #{entity.entityID,jdbcType=INTEGER} AS 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} + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="[guacamole_user_permission].entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = #{type,jdbcType=VARCHAR} - AND affected.username = #{identifier,jdbcType=INTEGER} + AND affected_entity.name = #{identifier,jdbcType=VARCHAR} + AND affected_entity.type = 'USER' </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 + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="[guacamole_user_permission].entity_id"/> + <property name="entityID" value="#{entity.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> + 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} </foreach> + AND affected_entity.type = 'USER' </select> <!-- Delete all given permissions --> <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> - DELETE FROM [guacamole_user_permission] - USING [guacamole_user] affected + DELETE [guacamole_user_permission] + FROM [guacamole_user_permission] + 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].affected_user_id = affected.user_id - AND ([guacamole_user_permission].user_id, permission, affected.username) IN - <foreach collection="permissions" item="permission" - open="(" separator="," close=")"> - (#{permission.userID,jdbcType=INTEGER}, - #{permission.type,jdbcType=VARCHAR}, - #{permission.objectIdentifier,jdbcType=INTEGER}) - </foreach> + <foreach collection="permissions" item="permission" + open="(" separator=" OR " close=")"> + ([guacamole_user_permission].entity_id = #{permission.entityID,jdbcType=INTEGER} AND + permission = #{permission.type,jdbcType=VARCHAR} AND + affected_entity.name = #{permission.objectIdentifier,jdbcType=VARCHAR} AND + affected_entity.type = 'USER') + </foreach> </delete> @@ -107,27 +122,30 @@ <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} AS permission, - #{permission.objectIdentifier,jdbcType=INTEGER} AS username + #{permission.objectIdentifier,jdbcType=INTEGER} AS affected_name </foreach> AS permissions - JOIN [guacamole_user] ON [guacamole_user].username = permissions.username + JOIN [guacamole_entity] affected_entity ON + affected_entity.name = permissions.affected_name + AND affected_entity.type = 'USER' + JOIN [guacamole_user] affected_user ON affected_user.entity_id = affected_entity.entity_id WHERE NOT EXISTS (SELECT 1 FROM [guacamole_user_permission] - WHERE [guacamole_user_permission].user_id = permissions.user_id + WHERE [guacamole_user_permission].entity_id = permissions.entity_id AND [guacamole_user_permission].permission = permissions.permission - AND [guacamole_user_permission].affected_user_id = [guacamole_user].user_id + AND [guacamole_user_permission].affected_user_id = affected_user.user_id ); </insert> http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/ee356201/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml index 0b3212f..dc87f53 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml @@ -52,7 +52,11 @@ SELECT sharing_profile_id FROM [guacamole_sharing_profile_permission] WHERE - user_id = #{user.objectID,jdbcType=INTEGER} + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ' </select> @@ -99,7 +103,11 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER} </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ'; SELECT @@ -113,7 +121,11 @@ open="(" separator="," close=")"> #{identifier,jdbcType=INTEGER} </foreach> - AND user_id = #{user.objectID,jdbcType=INTEGER} + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ'; </select> http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/ee356201/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml index 20d2cfb..21fd986 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/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 http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/ee356201/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml index 177ab93..7d70950 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/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,24 @@ <!-- Select all usernames --> <select id="selectIdentifiers" resultType="string"> - SELECT username - FROM [guacamole_user] + SELECT name + FROM [guacamole_entity] + WHERE [guacamole_entity].type = 'USER' </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} + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="[guacamole_user_permission].entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> + AND [guacamole_entity].type = 'USER' AND permission = 'READ' </select> @@ -77,7 +85,8 @@ SELECT [guacamole_user].user_id, - [guacamole_user].username, + [guacamole_entity].entity_id, + [guacamole_entity].name, password_hash, password_salt, password_date, @@ -98,11 +107,13 @@ WHERE [guacamole_user_history].user_id = [guacamole_user].user_id ) AS last_active FROM [guacamole_user] - WHERE [guacamole_user].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'; SELECT [guacamole_user_attribute].user_id, @@ -110,11 +121,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=INTEGER} - </foreach>; + #{identifier,jdbcType=VARCHAR} + </foreach> + AND [guacamole_entity].type = 'USER'; </select> @@ -124,7 +137,8 @@ SELECT [guacamole_user].user_id, - [guacamole_user].username, + [guacamole_entity].entity_id, + [guacamole_entity].name, password_hash, password_salt, password_date, @@ -145,13 +159,19 @@ WHERE [guacamole_user_history].user_id = [guacamole_user].user_id ) 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 - 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' + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="[guacamole_user_permission].entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ'; SELECT @@ -160,13 +180,19 @@ [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=INTEGER} + #{identifier,jdbcType=VARCHAR} </foreach> - AND [guacamole_user_permission].user_id = #{user.objectID,jdbcType=INTEGER} + AND [guacamole_entity].type = 'USER' + AND <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="[guacamole_user_permission].entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> AND permission = 'READ'; </select> @@ -176,8 +202,9 @@ resultSets="users,arbitraryAttributes"> SELECT - user_id, - username, + [guacamole_user].user_id, + [guacamole_entity].entity_id, + [guacamole_entity].name, password_hash, password_salt, password_date, @@ -198,8 +225,10 @@ WHERE [guacamole_user_history].user_id = [guacamole_user].user_id ) AS last_active FROM [guacamole_user] + 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} + AND [guacamole_entity].type = 'USER'; SELECT [guacamole_user_attribute].user_id, @@ -207,14 +236,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' </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' </delete> <!-- Insert single user --> @@ -222,7 +256,7 @@ parameterType="org.apache.guacamole.auth.jdbc.user.UserModel"> INSERT INTO [guacamole_user] ( - username, + entity_id, password_hash, password_salt, password_date, @@ -239,7 +273,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/ee356201/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserParentUserGroupMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserParentUserGroupMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserParentUserGroupMapper.xml new file mode 100644 index 0000000..e6eccba --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserParentUserGroupMapper.xml @@ -0,0 +1,96 @@ +<?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.UserParentUserGroupMapper" > + + <!-- Select the names of all parent user groups --> + <select id="selectChildIdentifiers" resultType="string"> + SELECT name + FROM [guacamole_user_group_member] + JOIN [guacamole_user_group] ON [guacamole_user_group_member].user_group_id = [guacamole_user_group].user_group_id + JOIN [guacamole_entity] ON [guacamole_entity].entity_id = [guacamole_user_group].entity_id + WHERE + [guacamole_user_group_member].member_entity_id = #{parent.entityID,jdbcType=INTEGER} + AND [guacamole_entity].type = 'USER_GROUP' + </select> + + <!-- Select the names of all readable parent user groups --> + <select id="selectReadableChildIdentifiers" resultType="string"> + SELECT [guacamole_entity].name + FROM [guacamole_user_group_member] + JOIN [guacamole_user_group] ON [guacamole_user_group_member].user_group_id = [guacamole_user_group].user_group_id + JOIN [guacamole_entity] ON [guacamole_entity].entity_id = [guacamole_user_group].entity_id + JOIN [guacamole_user_group_permission] ON affected_user_group_id = [guacamole_user_group].user_group_id + WHERE + <include refid="org.apache.guacamole.auth.jdbc.base.EntityMapper.isRelatedEntity"> + <property name="column" value="[guacamole_user_group_permission].entity_id"/> + <property name="entityID" value="#{user.entityID,jdbcType=INTEGER}"/> + <property name="groups" value="effectiveGroups"/> + </include> + AND [guacamole_user_group_member].member_entity_id = #{parent.entityID,jdbcType=INTEGER} + AND [guacamole_entity].type = 'USER_GROUP' + AND permission = 'READ' + </select> + + <!-- Delete parent groups by name --> + <delete id="delete"> + DELETE [guacamole_user_group_member] + FROM [guacamole_user_group_member] + JOIN [guacamole_user_group] ON [guacamole_user_group].user_group_id = [guacamole_user_group_member].user_group_id + JOIN [guacamole_entity] ON [guacamole_entity].entity_id = [guacamole_user_group].entity_id + WHERE + member_entity_id = #{parent.entityID,jdbcType=INTEGER} + AND [guacamole_entity].type = 'USER_GROUP' + AND [guacamole_entity].name IN + <foreach collection="children" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=VARCHAR} + </foreach> + </delete> + + <!-- Insert parent groups by name --> + <insert id="insert"> + INSERT INTO [guacamole_user_group_member] ( + user_group_id, + member_entity_id + ) + SELECT DISTINCT + [guacamole_user_group].user_group_id, + #{parent.entityID,jdbcType=INTEGER} + FROM [guacamole_user_group] + JOIN [guacamole_entity] ON [guacamole_user_group].entity_id = [guacamole_entity].entity_id + WHERE + [guacamole_entity].name IN + <foreach collection="children" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=VARCHAR} + </foreach> + AND [guacamole_entity].type = 'USER_GROUP' + AND [guacamole_user_group].user_group_id NOT IN ( + SELECT [guacamole_user_group_member].user_group_id + FROM [guacamole_user_group_member] + WHERE [guacamole_user_group_member].member_entity_id = #{parent.entityID,jdbcType=INTEGER} + ) + </insert> + +</mapper>
