You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@guacamole.apache.org by vn...@apache.org on 2018/10/01 18:08:26 UTC
[25/38] guacamole-client git commit: GUACAMOLE-220: Update SQL Server
mapping with respect to user group support.
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/UserRecordMapper.xml
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml
index 22a0cc7..4d4a3cc 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/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'),
#{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'),
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'),
)
<if test="term.startDate != null and term.endDate != null">
@@ -144,7 +154,11 @@
<!-- Restrict to readable users -->
JOIN [guacamole_user_permission] ON
[guacamole_user_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 -->
@@ -155,7 +169,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'
)
<if test="term.startDate != null and term.endDate != null">
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/usergroup/UserGroupMapper.xml
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/usergroup/UserGroupMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/usergroup/UserGroupMapper.xml
new file mode 100644
index 0000000..aed0247
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/usergroup/UserGroupMapper.xml
@@ -0,0 +1,229 @@
+<?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.usergroup.UserGroupMapper" >
+
+ <!-- Result mapper for user group objects -->
+ <resultMap id="UserGroupResultMap" type="org.apache.guacamole.auth.jdbc.usergroup.UserGroupModel" >
+
+ <!-- User group properties -->
+ <id column="user_group_id" property="objectID" jdbcType="INTEGER"/>
+ <result column="entity_id" property="entityID" jdbcType="INTEGER"/>
+ <result column="name" property="identifier" jdbcType="VARCHAR"/>
+ <result column="disabled" property="disabled" jdbcType="BOOLEAN"/>
+
+ <!-- Arbitrary attributes -->
+ <collection property="arbitraryAttributes" resultSet="arbitraryAttributes"
+ ofType="org.apache.guacamole.auth.jdbc.base.ArbitraryAttributeModel"
+ column="user_group_id" foreignColumn="user_group_id">
+ <result property="name" column="attribute_name" jdbcType="VARCHAR"/>
+ <result property="value" column="attribute_value" jdbcType="VARCHAR"/>
+ </collection>
+
+ </resultMap>
+
+ <!-- Select all group names -->
+ <select id="selectIdentifiers" resultType="string">
+ SELECT name
+ FROM [guacamole_entity]
+ WHERE [guacamole_entity].type = 'USER_GROUP'
+ </select>
+
+ <!-- Select names of all readable groups -->
+ <select id="selectReadableIdentifiers" resultType="string">
+ SELECT [guacamole_entity].name
+ FROM [guacamole_user_group]
+ JOIN [guacamole_entity] ON [guacamole_user_group].entity_id = [guacamole_entity].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_entity].type = 'USER_GROUP'
+ AND permission = 'READ'
+ </select>
+
+ <!-- Select multiple groups by name -->
+ <select id="select" resultMap="UserGroupResultMap"
+ resultSets="users,arbitraryAttributes">
+
+ SELECT
+ [guacamole_user_group].user_group_id,
+ [guacamole_entity].entity_id,
+ [guacamole_entity].name,
+ disabled
+ FROM [guacamole_user_group]
+ JOIN [guacamole_entity] ON [guacamole_user_group].entity_id = [guacamole_entity].entity_id
+ WHERE [guacamole_entity].name IN
+ <foreach collection="identifiers" item="identifier"
+ open="(" separator="," close=")">
+ #{identifier,jdbcType=VARCHAR}
+ </foreach>
+ AND [guacamole_entity].type = 'USER_GROUP';
+
+ SELECT
+ [guacamole_user_group_attribute].user_group_id,
+ [guacamole_user_group_attribute].attribute_name,
+ [guacamole_user_group_attribute].attribute_value
+ FROM [guacamole_user_group_attribute]
+ JOIN [guacamole_user_group] ON [guacamole_user_group].user_group_id = [guacamole_user_group_attribute].user_group_id
+ JOIN [guacamole_entity] ON [guacamole_user_group].entity_id = [guacamole_entity].entity_id
+ WHERE [guacamole_entity].name IN
+ <foreach collection="identifiers" item="identifier"
+ open="(" separator="," close=")">
+ #{identifier,jdbcType=VARCHAR}
+ </foreach>
+ AND [guacamole_entity].type = 'USER_GROUP';
+
+ </select>
+
+ <!-- Select multiple groups by name only if readable -->
+ <select id="selectReadable" resultMap="UserGroupResultMap"
+ resultSets="users,arbitraryAttributes">
+
+ SELECT
+ [guacamole_user_group].user_group_id,
+ [guacamole_entity].entity_id,
+ [guacamole_entity].name,
+ disabled
+ FROM [guacamole_user_group]
+ JOIN [guacamole_entity] ON [guacamole_user_group].entity_id = [guacamole_entity].entity_id
+ JOIN [guacamole_user_group_permission] ON affected_user_group_id = [guacamole_user_group].user_group_id
+ WHERE [guacamole_entity].name IN
+ <foreach collection="identifiers" item="identifier"
+ open="(" separator="," close=")">
+ #{identifier,jdbcType=VARCHAR}
+ </foreach>
+ AND [guacamole_entity].type = 'USER_GROUP'
+ AND <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 permission = 'READ';
+
+ SELECT
+ [guacamole_user_group_attribute].user_group_id,
+ [guacamole_user_group_attribute].attribute_name,
+ [guacamole_user_group_attribute].attribute_value
+ FROM [guacamole_user_group_attribute]
+ JOIN [guacamole_user_group] ON [guacamole_user_group].user_group_id = [guacamole_user_group_attribute].user_group_id
+ JOIN [guacamole_entity] ON [guacamole_user_group].entity_id = [guacamole_entity].entity_id
+ JOIN [guacamole_user_group_permission] ON affected_user_group_id = [guacamole_user_group].user_group_id
+ WHERE [guacamole_entity].name IN
+ <foreach collection="identifiers" item="identifier"
+ open="(" separator="," close=")">
+ #{identifier,jdbcType=VARCHAR}
+ </foreach>
+ AND [guacamole_entity].type = 'USER_GROUP'
+ AND <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 permission = 'READ';
+
+ </select>
+
+ <!-- Select single group by name -->
+ <select id="selectOne" resultMap="UserGroupResultMap"
+ resultSets="users,arbitraryAttributes">
+
+ SELECT
+ [guacamole_user_group].user_group_id,
+ [guacamole_entity].entity_id,
+ [guacamole_entity].name,
+ disabled
+ FROM [guacamole_user_group]
+ JOIN [guacamole_entity] ON [guacamole_user_group].entity_id = [guacamole_entity].entity_id
+ WHERE
+ [guacamole_entity].name = #{name,jdbcType=VARCHAR}
+ AND [guacamole_entity].type = 'USER_GROUP';
+
+ SELECT
+ [guacamole_user_group_attribute].user_group_id,
+ [guacamole_user_group_attribute].attribute_name,
+ [guacamole_user_group_attribute].attribute_value
+ FROM [guacamole_user_group_attribute]
+ JOIN [guacamole_user_group] ON [guacamole_user_group].user_group_id = [guacamole_user_group_attribute].user_group_id
+ JOIN [guacamole_entity] ON [guacamole_user_group].entity_id = [guacamole_entity].entity_id
+ WHERE
+ [guacamole_entity].name = #{name,jdbcType=VARCHAR}
+ AND [guacamole_entity].type = 'USER_GROUP'
+
+ </select>
+
+ <!-- Delete single group by name -->
+ <delete id="delete">
+ DELETE FROM [guacamole_entity]
+ WHERE
+ name = #{identifier,jdbcType=VARCHAR}
+ AND type = 'USER_GROUP'
+ </delete>
+
+ <!-- Insert single group -->
+ <insert id="insert" useGeneratedKeys="true" keyProperty="object.objectID"
+ parameterType="org.apache.guacamole.auth.jdbc.usergroup.UserGroupModel">
+
+ INSERT INTO [guacamole_user_group] (
+ entity_id,
+ disabled
+ )
+ VALUES (
+ #{object.entityID,jdbcType=VARCHAR},
+ #{object.disabled,jdbcType=BOOLEAN}
+ )
+
+ </insert>
+
+ <!-- Update single group -->
+ <update id="update" parameterType="org.apache.guacamole.auth.jdbc.usergroup.UserGroupModel">
+ UPDATE [guacamole_user_group]
+ SET disabled = #{object.disabled,jdbcType=BOOLEAN}
+ WHERE user_group_id = #{object.objectID,jdbcType=VARCHAR}
+ </update>
+
+ <!-- Delete attributes associated with group -->
+ <delete id="deleteAttributes">
+ DELETE FROM [guacamole_user_group_attribute]
+ WHERE user_group_id = #{object.objectID,jdbcType=INTEGER}
+ </delete>
+
+ <!-- Insert attributes for group -->
+ <insert id="insertAttributes" parameterType="org.apache.guacamole.auth.jdbc.base.ArbitraryAttributeModel">
+ INSERT INTO [guacamole_user_group_attribute] (
+ user_group_id,
+ attribute_name,
+ attribute_value
+ )
+ VALUES
+ <foreach collection="object.arbitraryAttributes" item="attribute" separator=",">
+ (#{object.objectID,jdbcType=INTEGER},
+ #{attribute.name,jdbcType=VARCHAR},
+ #{attribute.value,jdbcType=VARCHAR})
+ </foreach>
+ </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/usergroup/UserGroupMemberUserGroupMapper.xml
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/usergroup/UserGroupMemberUserGroupMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/usergroup/UserGroupMemberUserGroupMapper.xml
new file mode 100644
index 0000000..2092f24
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/usergroup/UserGroupMemberUserGroupMapper.xml
@@ -0,0 +1,93 @@
+<?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.usergroup.UserGroupMemberUserGroupMapper" >
+
+ <!-- Select the names of all member user groups -->
+ <select id="selectChildIdentifiers" resultType="string">
+ SELECT name
+ FROM [guacamole_user_group_member]
+ JOIN [guacamole_entity] ON [guacamole_entity].entity_id = [guacamole_user_group_member].member_entity_id
+ WHERE
+ [guacamole_user_group_member].user_group_id = #{parent.objectID,jdbcType=INTEGER}
+ AND [guacamole_entity].type = 'USER_GROUP'
+ </select>
+
+ <!-- Select the names of all readable member user groups -->
+ <select id="selectReadableChildIdentifiers" resultType="string">
+ SELECT [guacamole_entity].name
+ FROM [guacamole_user_group_member]
+ JOIN [guacamole_entity] ON [guacamole_entity].entity_id = [guacamole_user_group_member].member_entity_id
+ JOIN [guacamole_user_group] ON [guacamole_user_group].entity_id = [guacamole_entity].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].user_group_id = #{parent.objectID,jdbcType=INTEGER}
+ AND [guacamole_entity].type = 'USER_GROUP'
+ AND permission = 'READ'
+ </select>
+
+ <!-- Delete member groups by name -->
+ <delete id="delete">
+ DELETE [guacamole_user_group_member]
+ FROM [guacamole_user_group_member]
+ JOIN [guacamole_entity] ON [guacamole_entity].entity_id = member_entity_id
+ WHERE
+ user_group_id = #{parent.objectID,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 member groups by name -->
+ <insert id="insert">
+ INSERT INTO [guacamole_user_group_member] (
+ user_group_id,
+ member_entity_id
+ )
+ SELECT DISTINCT
+ #{parent.objectID,jdbcType=INTEGER},
+ [guacamole_entity].entity_id
+ FROM [guacamole_entity]
+ WHERE
+ [guacamole_entity].name IN
+ <foreach collection="children" item="identifier"
+ open="(" separator="," close=")">
+ #{identifier}
+ </foreach>
+ AND [guacamole_entity].type = 'USER_GROUP'
+ AND [guacamole_entity].entity_id NOT IN (
+ SELECT [guacamole_user_group_member].member_entity_id
+ FROM [guacamole_user_group_member]
+ WHERE [guacamole_user_group_member].user_group_id = #{parent.objectID,jdbcType=INTEGER}
+ )
+ </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/usergroup/UserGroupMemberUserMapper.xml
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/usergroup/UserGroupMemberUserMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/usergroup/UserGroupMemberUserMapper.xml
new file mode 100644
index 0000000..2c91c92
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/usergroup/UserGroupMemberUserMapper.xml
@@ -0,0 +1,93 @@
+<?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.usergroup.UserGroupMemberUserMapper" >
+
+ <!-- Select the username of all member users -->
+ <select id="selectChildIdentifiers" resultType="string">
+ SELECT name
+ FROM [guacamole_user_group_member]
+ JOIN [guacamole_entity] ON [guacamole_entity].entity_id = [guacamole_user_group_member].member_entity_id
+ WHERE
+ [guacamole_user_group_member].user_group_id = #{parent.objectID,jdbcType=INTEGER}
+ AND [guacamole_entity].type = 'USER'
+ </select>
+
+ <!-- Select the usernames of all readable member users -->
+ <select id="selectReadableChildIdentifiers" resultType="string">
+ SELECT [guacamole_entity].name
+ FROM [guacamole_user_group_member]
+ JOIN [guacamole_entity] ON [guacamole_entity].entity_id = [guacamole_user_group_member].member_entity_id
+ JOIN [guacamole_user] ON [guacamole_user].entity_id = [guacamole_entity].entity_id
+ JOIN [guacamole_user_permission] ON affected_user_id = [guacamole_user].user_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="#{user.entityID,jdbcType=INTEGER}"/>
+ <property name="groups" value="effectiveGroups"/>
+ </include>
+ AND [guacamole_user_group_member].user_group_id = #{parent.objectID,jdbcType=INTEGER}
+ AND [guacamole_entity].type = 'USER'
+ AND permission = 'READ'
+ </select>
+
+ <!-- Delete member users by name -->
+ <delete id="delete">
+ DELETE [guacamole_user_group_member]
+ FROM [guacamole_user_group_member]
+ JOIN [guacamole_entity] ON [guacamole_entity].entity_id = member_entity_id
+ WHERE
+ user_group_id = #{parent.objectID,jdbcType=INTEGER}
+ AND [guacamole_entity].type = 'USER'
+ AND [guacamole_entity].name IN
+ <foreach collection="children" item="identifier"
+ open="(" separator="," close=")">
+ #{identifier,jdbcType=VARCHAR}
+ </foreach>
+ </delete>
+
+ <!-- Insert member users by name -->
+ <insert id="insert">
+ INSERT INTO [guacamole_user_group_member] (
+ user_group_id,
+ member_entity_id
+ )
+ SELECT DISTINCT
+ #{parent.objectID,jdbcType=INTEGER},
+ [guacamole_entity].entity_id
+ FROM [guacamole_entity]
+ WHERE
+ [guacamole_entity].name IN
+ <foreach collection="children" item="identifier"
+ open="(" separator="," close=")">
+ #{identifier}
+ </foreach>
+ AND [guacamole_entity].type = 'USER'
+ AND [guacamole_entity].entity_id NOT IN (
+ SELECT [guacamole_user_group_member].member_entity_id
+ FROM [guacamole_user_group_member]
+ WHERE [guacamole_user_group_member].user_group_id = #{parent.objectID,jdbcType=INTEGER}
+ )
+ </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/usergroup/UserGroupParentUserGroupMapper.xml
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/usergroup/UserGroupParentUserGroupMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/usergroup/UserGroupParentUserGroupMapper.xml
new file mode 100644
index 0000000..0ea9252
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/usergroup/UserGroupParentUserGroupMapper.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.usergroup.UserGroupParentUserGroupMapper" >
+
+ <!-- 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>