Repository: incubator-guacamole-client Updated Branches: refs/heads/master 81ffa5c8e -> d808f7fbb
http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/b6e88d33/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 new file mode 100644 index 0000000..47a3e63 --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml @@ -0,0 +1,232 @@ +<?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.connectiongroup.ConnectionGroupMapper" > + + <!-- Result mapper for connection objects --> + <resultMap id="ConnectionGroupResultMap" type="org.apache.guacamole.auth.jdbc.connectiongroup.ConnectionGroupModel" > + + <!-- Connection group properties --> + <id column="connection_group_id" property="objectID" jdbcType="INTEGER"/> + <result column="connection_group_name" property="name" jdbcType="VARCHAR"/> + <result column="parent_id" property="parentIdentifier" jdbcType="INTEGER"/> + <result column="type" property="type" jdbcType="VARCHAR" + javaType="org.apache.guacamole.net.auth.ConnectionGroup$Type"/> + <result column="max_connections" property="maxConnections" jdbcType="INTEGER"/> + <result column="max_connections_per_user" property="maxConnectionsPerUser" jdbcType="INTEGER"/> + <result column="enable_session_affinity" property="sessionAffinityEnabled" jdbcType="INTEGER"/> + + <!-- Child connection groups --> + <collection property="connectionGroupIdentifiers" resultSet="childConnectionGroups" ofType="java.lang.String" + column="connection_group_id" foreignColumn="parent_id"> + <result column="connection_group_id"/> + </collection> + + <!-- Child connections --> + <collection property="connectionIdentifiers" resultSet="childConnections" ofType="java.lang.String" + column="connection_group_id" foreignColumn="parent_id"> + <result column="connection_id"/> + </collection> + + </resultMap> + + <!-- Select all connection group identifiers --> + <select id="selectIdentifiers" resultType="string"> + SELECT connection_group_id + FROM [guacamole].[connection_group] + </select> + + <!-- Select identifiers of all readable connection groups --> + <select id="selectReadableIdentifiers" resultType="string"> + SELECT connection_group_id + FROM [guacamole].[connection_group_permission] + WHERE + user_id = #{user.objectID,jdbcType=INTEGER} + AND permission = 'READ' + </select> + + <!-- Select all connection identifiers within a particular connection group --> + <select id="selectIdentifiersWithin" resultType="string"> + SELECT connection_group_id + FROM [guacamole].[connection_group] + WHERE + <if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}</if> + <if test="parentIdentifier == null">parent_id IS NULL</if> + </select> + + <!-- Select identifiers of all readable connection groups within a particular connection group --> + <select id="selectReadableIdentifiersWithin" resultType="string"> + SELECT [guacamole].[connection_group].connection_group_id + FROM [guacamole].[connection_group] + JOIN [guacamole].[connection_group_permission] ON [guacamole].[connection_group_permission].connection_group_id = [guacamole].[connection_group].connection_group_id + 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 permission = 'READ' + </select> + + <!-- Select multiple connection groups by identifier --> + <select id="select" resultMap="ConnectionGroupResultMap" + resultSets="connectionGroups,childConnectionGroups,childConnections"> + + SELECT + connection_group_id, + connection_group_name, + parent_id, + type, + max_connections, + max_connections_per_user, + enable_session_affinity + FROM [guacamole].[connection_group] + WHERE connection_group_id IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=INTEGER} + </foreach>; + + SELECT parent_id, connection_group_id + FROM [guacamole].[connection_group] + WHERE parent_id IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=INTEGER} + </foreach>; + + SELECT parent_id, connection_id + FROM [guacamole].[connection] + WHERE parent_id IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=INTEGER} + </foreach>; + + </select> + + <!-- Select multiple connection groups by identifier only if readable --> + <select id="selectReadable" resultMap="ConnectionGroupResultMap" + resultSets="connectionGroups,childConnectionGroups,childConnections"> + + SELECT + [guacamole].[connection_group].connection_group_id, + connection_group_name, + parent_id, + type, + max_connections, + max_connections_per_user, + enable_session_affinity + FROM [guacamole].[connection_group] + JOIN [guacamole].[connection_group_permission] ON [guacamole].[connection_group_permission].connection_group_id = [guacamole].[connection_group].connection_group_id + WHERE [guacamole].[connection_group].connection_group_id IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=INTEGER} + </foreach> + AND user_id = #{user.objectID,jdbcType=INTEGER} + AND permission = 'READ'; + + SELECT parent_id, [guacamole].[connection_group].connection_group_id + FROM [guacamole].[connection_group] + JOIN [guacamole].[connection_group_permission] ON [guacamole].[connection_group_permission].connection_group_id = [guacamole].[connection_group].connection_group_id + WHERE parent_id IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=INTEGER} + </foreach> + AND user_id = #{user.objectID,jdbcType=INTEGER} + AND permission = 'READ'; + + SELECT parent_id, [guacamole].[connection].connection_id + FROM [guacamole].[connection] + JOIN [guacamole].[connection_permission] ON [guacamole].[connection_permission].connection_id = [guacamole].[connection].connection_id + WHERE parent_id IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=INTEGER} + </foreach> + AND user_id = #{user.objectID,jdbcType=INTEGER} + AND permission = 'READ'; + + </select> + + <!-- Select single connection group by name --> + <select id="selectOneByName" resultMap="ConnectionGroupResultMap"> + + SELECT + connection_group_id, + connection_group_name, + parent_id, + type, + max_connections, + max_connections_per_user, + enable_session_affinity + FROM [guacamole].[connection_group] + WHERE + <if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}</if> + <if test="parentIdentifier == null">parent_id IS NULL</if> + AND connection_group_name = #{name,jdbcType=VARCHAR} + + </select> + + <!-- Delete single connection group by identifier --> + <delete id="delete"> + DELETE FROM [guacamole].[connection_group] + WHERE connection_group_id = #{identifier,jdbcType=INTEGER} + </delete> + + <!-- Insert single connection --> + <insert id="insert" useGeneratedKeys="true" keyProperty="object.objectID" + parameterType="org.apache.guacamole.auth.jdbc.connectiongroup.ConnectionGroupModel"> + + INSERT INTO [guacamole].[connection_group] ( + connection_group_name, + parent_id, + type, + max_connections, + max_connections_per_user, + enable_session_affinity + ) + VALUES ( + #{object.name,jdbcType=VARCHAR}, + #{object.parentIdentifier,jdbcType=INTEGER}, + #{object.type,jdbcType=VARCHAR}, + #{object.maxConnections,jdbcType=INTEGER}, + #{object.maxConnectionsPerUser,jdbcType=INTEGER}, + #{object.sessionAffinityEnabled,jdbcType=INTEGER} + ) + + </insert> + + <!-- Update single connection group --> + <update id="update" parameterType="org.apache.guacamole.auth.jdbc.connectiongroup.ConnectionGroupModel"> + UPDATE [guacamole].[connection_group] + SET connection_group_name = #{object.name,jdbcType=VARCHAR}, + parent_id = #{object.parentIdentifier,jdbcType=INTEGER}, + type = #{object.type,jdbcType=VARCHAR}, + max_connections = #{object.maxConnections,jdbcType=INTEGER}, + max_connections_per_user = #{object.maxConnectionsPerUser,jdbcType=INTEGER}, + enable_session_affinity = #{object.sessionAffinityEnabled,jdbcType=INTEGER} + WHERE connection_group_id = #{object.objectID,jdbcType=INTEGER} + </update> + +</mapper> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/b6e88d33/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 new file mode 100644 index 0000000..2890ab3 --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionGroupPermissionMapper.xml @@ -0,0 +1,130 @@ +<?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.ConnectionGroupPermissionMapper" > + + <!-- 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="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 id="select" resultMap="ConnectionGroupPermissionResultMap"> + + SELECT + [guacamole].[connection_group_permission].user_id, + username, + 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} + + </select> + + <!-- Select the single permission matching the given criteria --> + <select id="selectOne" resultMap="ConnectionGroupPermissionResultMap"> + + SELECT + [guacamole].[connection_group_permission].user_id, + username, + 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} + 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 id="selectAccessibleIdentifiers" resultType="string"> + + SELECT DISTINCT connection_group_id + FROM [guacamole].[connection_group_permission] + WHERE + user_id = #{user.objectID,jdbcType=INTEGER} + AND connection_group_id IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=INTEGER} + </foreach> + AND permission IN + <foreach collection="permissions" item="permission" + open="(" separator="," close=")"> + #{permission,jdbcType=VARCHAR} + </foreach> + + </select> + + <!-- Delete all given permissions --> + <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 + <foreach collection="permissions" item="permission" + open="(" separator="," close=")"> + (#{permission.userID,jdbcType=INTEGER}, + #{permission.type,jdbcType=VARCHAR}, + #{permission.objectIdentifier,jdbcType=INTEGER}) + </foreach> + + </delete> + + <!-- Insert all given permissions --> + <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> + + INSERT INTO [guacamole].[connection_group_permission] ( + user_id, + permission, + connection_group_id + ) + SELECT DISTINCT + permissions.user_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, + #{permission.type,jdbcType=VARCHAR} AS permission, + #{permission.objectIdentifier,jdbcType=INTEGER} AS connection_group_id + </foreach> + AS permissions + WHERE (user_id, permission, connection_group_id) NOT IN ( + SELECT + [guacamole].[connection_group_permission].user_id, + [guacamole].[connection_group_permission].permission, + [guacamole].[connection_group_permission].connection_group_id + FROM [guacamole].[connection_group_permission] + ); + + </insert> + +</mapper> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/b6e88d33/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 new file mode 100644 index 0000000..8ea85bc --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/ConnectionPermissionMapper.xml @@ -0,0 +1,130 @@ +<?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.ConnectionPermissionMapper" > + + <!-- 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="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 id="select" resultMap="ConnectionPermissionResultMap"> + + SELECT + [guacamole].[connection_permission].user_id, + username, + 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} + + </select> + + <!-- Select the single permission matching the given criteria --> + <select id="selectOne" resultMap="ConnectionPermissionResultMap"> + + SELECT + [guacamole].[connection_permission].user_id, + username, + 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} + AND permission = #{type,jdbcType=VARCHAR} + AND connection_id = #{identifier,jdbcType=INTEGER} + + </select> + + <!-- Select identifiers accessible by the given user for the given permissions --> + <select id="selectAccessibleIdentifiers" resultType="string"> + + SELECT DISTINCT connection_id + FROM [guacamole].[connection_permission] + WHERE + user_id = #{user.objectID,jdbcType=INTEGER} + AND connection_id IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=INTEGER} + </foreach> + AND permission IN + <foreach collection="permissions" item="permission" + open="(" separator="," close=")"> + #{permission,jdbcType=VARCHAR} + </foreach> + + </select> + + <!-- Delete all given permissions --> + <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> + + DELETE FROM [guacamole].[connection_permission] + WHERE (user_id, permission, connection_id) IN + <foreach collection="permissions" item="permission" + open="(" separator="," close=")"> + (#{permission.userID,jdbcType=INTEGER}, + #{permission.type,jdbcType=VARCHAR}, + #{permission.objectIdentifier,jdbcType=INTEGER}) + </foreach> + + </delete> + + <!-- Insert all given permissions --> + <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> + + INSERT INTO [guacamole].[connection_permission] ( + user_id, + permission, + connection_id + ) + SELECT DISTINCT + permissions.user_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, + #{permission.type,jdbcType=VARCHAR} AS permission, + #{permission.objectIdentifier,jdbcType=INTEGER} AS connection_id + </foreach> + AS permissions + WHERE (user_id, permission, connection_id) NOT IN ( + SELECT + [guacamole].[connection_permission].user_id, + [guacamole].[connection_permission].permission, + [guacamole].[connection_permission].connection_id + FROM [guacamole].[connection_permission] + ); + + </insert> + +</mapper> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/b6e88d33/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 new file mode 100644 index 0000000..cb706b8 --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SharingProfilePermissionMapper.xml @@ -0,0 +1,130 @@ +<?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.SharingProfilePermissionMapper"> + + <!-- 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="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 id="select" resultMap="SharingProfilePermissionResultMap"> + + SELECT + [guacamole].[sharing_profile_permission].user_id, + username, + 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} + + </select> + + <!-- Select the single permission matching the given criteria --> + <select id="selectOne" resultMap="SharingProfilePermissionResultMap"> + + SELECT + [guacamole].[sharing_profile_permission].user_id, + username, + 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} + 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 id="selectAccessibleIdentifiers" resultType="string"> + + SELECT DISTINCT sharing_profile_id + FROM [guacamole].[sharing_profile_permission] + WHERE + user_id = #{user.objectID,jdbcType=INTEGER} + AND sharing_profile_id IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=INTEGER} + </foreach> + AND permission IN + <foreach collection="permissions" item="permission" + open="(" separator="," close=")"> + #{permission,jdbcType=VARCHAR} + </foreach> + + </select> + + <!-- Delete all given permissions --> + <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> + + DELETE FROM [guacamole].[sharing_profile_permission] + WHERE (user_id, permission, sharing_profile_id) IN + <foreach collection="permissions" item="permission" + open="(" separator="," close=")"> + (#{permission.userID,jdbcType=INTEGER}, + #{permission.type,jdbcType=VARCHAR}, + #{permission.objectIdentifier,jdbcType=INTEGER}) + </foreach> + + </delete> + + <!-- Insert all given permissions --> + <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> + + INSERT INTO [guacamole].[sharing_profile_permission] ( + user_id, + permission, + sharing_profile_id + ) + SELECT DISTINCT + permissions.user_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, + #{permission.type,jdbcType=VARCHAR} AS permission, + #{permission.objectIdentifier,jdbcType=INTEGER} AS sharing_profile_id + </foreach> + AS permissions + WHERE (user_id, permission, sharing_profile_id) NOT IN ( + SELECT + [guacamole].[sharing_profile_permission].user_id, + [guacamole].[sharing_profile_permission].permission, + [guacamole].[sharing_profile_permission].sharing_profile_id + FROM [guacamole].[sharing_profile_permission] + ); + + </insert> + +</mapper> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/b6e88d33/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 new file mode 100644 index 0000000..d9e622b --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/SystemPermissionMapper.xml @@ -0,0 +1,101 @@ +<?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.SystemPermissionMapper" > + + <!-- 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="permission" property="type" jdbcType="VARCHAR" + javaType="org.apache.guacamole.net.auth.permission.SystemPermission$Type"/> + </resultMap> + + <!-- Select all permissions for a given user --> + <select id="select" resultMap="SystemPermissionResultMap"> + + SELECT + [guacamole].[system_permission].user_id, + username, + 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} + + </select> + + <!-- Select the single permission matching the given criteria --> + <select id="selectOne" resultMap="SystemPermissionResultMap"> + + SELECT + [guacamole].[system_permission].user_id, + username, + 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} + AND permission = #{type,jdbcType=VARCHAR} + + </select> + + <!-- Delete all given permissions --> + <delete id="delete" parameterType="org.apache.guacamole.auth.jdbc.permission.SystemPermissionModel"> + + DELETE FROM [guacamole].[system_permission] + WHERE (user_id, permission) IN + <foreach collection="permissions" item="permission" + open="(" separator="," close=")"> + (#{permission.userID,jdbcType=INTEGER}, + #{permission.type,jdbcType=VARCHAR}) + </foreach> + + </delete> + + <!-- Insert all given permissions --> + <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.SystemPermissionModel"> + + INSERT INTO [guacamole].[system_permission] ( + user_id, + permission + ) + SELECT DISTINCT + permissions.user_id, + permissions.permission + FROM + <foreach collection="permissions" item="permission" + open="(" separator="UNION ALL" close=")"> + SELECT #{permission.userID,jdbcType=INTEGER} AS user_id, + #{permission.type,jdbcType=VARCHAR} AS permission + </foreach> + AS permissions + WHERE (user_id, permission) NOT IN ( + SELECT + [guacamole].[system_permission].user_id, + [guacamole].[system_permission].permission + FROM [guacamole].[system_permission] + ); + + </insert> + +</mapper> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/b6e88d33/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 new file mode 100644 index 0000000..595c326 --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/permission/UserPermissionMapper.xml @@ -0,0 +1,137 @@ +<?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.UserPermissionMapper" > + + <!-- 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="permission" property="type" jdbcType="VARCHAR" + javaType="org.apache.guacamole.net.auth.permission.ObjectPermission$Type"/> + <result column="affected_username" property="objectIdentifier" jdbcType="INTEGER"/> + </resultMap> + + <!-- Select all permissions for a given user --> + <select id="select" resultMap="UserPermissionResultMap"> + + SELECT + [guacamole].[user_permission].user_id, + [guacamole].[user].username, + permission, + affected.username AS affected_username + 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} + + </select> + + <!-- Select the single permission matching the given criteria --> + <select id="selectOne" resultMap="UserPermissionResultMap"> + + SELECT + [guacamole].[user_permission].user_id, + [guacamole].[user].username, + permission, + affected.username AS affected_username + 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} + AND permission = #{type,jdbcType=VARCHAR} + AND affected.username = #{identifier,jdbcType=INTEGER} + + </select> + + <!-- Select identifiers accessible by the given user for the given permissions --> + <select id="selectAccessibleIdentifiers" resultType="string"> + + SELECT DISTINCT username + FROM [guacamole].[user_permission] + JOIN [guacamole].[user] ON [guacamole].[user_permission].affected_user_id = [guacamole].[user].user_id + WHERE + [guacamole].[user_permission].user_id = #{user.objectID,jdbcType=INTEGER} + AND username IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=INTEGER} + </foreach> + AND permission IN + <foreach collection="permissions" item="permission" + open="(" separator="," close=")"> + #{permission,jdbcType=VARCHAR} + </foreach> + + </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 + 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> + + </delete> + + <!-- Insert all given permissions --> + <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel"> + + INSERT INTO [guacamole].[user_permission] ( + user_id, + permission, + affected_user_id + ) + SELECT DISTINCT + permissions.user_id, + permissions.permission, + [guacamole].[user].user_id + FROM + <foreach collection="permissions" item="permission" + open="(" separator="UNION ALL" close=")"> + SELECT #{permission.userID,jdbcType=INTEGER} AS user_id, + #{permission.type,jdbcType=VARCHAR} AS permission, + #{permission.objectIdentifier,jdbcType=INTEGER} AS username + </foreach> + AS permissions + JOIN [guacamole].[user] ON [guacamole].[user].username = permissions.username + WHERE (permissions.user_id, permissions.permission, [guacamole].[user].user_id) NOT IN ( + SELECT + [guacamole].[user_permission].user_id, + [guacamole].[user_permission].permission, + [guacamole].[user_permission].affected_user_id + FROM [guacamole].[user_permission] + ); + + </insert> + +</mapper> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/b6e88d33/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 new file mode 100644 index 0000000..9d7d45a --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileMapper.xml @@ -0,0 +1,126 @@ +<?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.sharingprofile.SharingProfileMapper"> + + <!-- Result mapper for sharing profile objects --> + <resultMap id="SharingProfileResultMap" type="org.apache.guacamole.auth.jdbc.sharingprofile.SharingProfileModel"> + <id column="sharing_profile_id" property="objectID" jdbcType="INTEGER"/> + <result column="sharing_profile_name" property="name" jdbcType="VARCHAR"/> + <result column="primary_connection_id" property="parentIdentifier" jdbcType="INTEGER"/> + </resultMap> + + <!-- Select all sharing profile identifiers --> + <select id="selectIdentifiers" resultType="string"> + SELECT sharing_profile_id + FROM [guacamole].[sharing_profile] + </select> + + <!-- Select identifiers of all readable sharing profiles --> + <select id="selectReadableIdentifiers" resultType="string"> + SELECT sharing_profile_id + FROM [guacamole].[sharing_profile_permission] + WHERE + user_id = #{user.objectID,jdbcType=INTEGER} + AND permission = 'READ' + </select> + + <!-- Select multiple sharing profiles by identifier --> + <select id="select" resultMap="SharingProfileResultMap"> + + SELECT + sharing_profile_id, + sharing_profile_name, + primary_connection_id + FROM [guacamole].[sharing_profile] + WHERE sharing_profile_id IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=INTEGER} + </foreach> + + </select> + + <!-- Select multiple sharing profiles by identifier only if readable --> + <select id="selectReadable" resultMap="SharingProfileResultMap"> + + SELECT + [guacamole].[sharing_profile].sharing_profile_id, + [guacamole].[sharing_profile].sharing_profile_name, + primary_connection_id + FROM [guacamole].[sharing_profile] + JOIN [guacamole].[sharing_profile_permission] ON [guacamole].[sharing_profile_permission].sharing_profile_id = [guacamole].[sharing_profile].sharing_profile_id + WHERE [guacamole].[sharing_profile].sharing_profile_id IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=INTEGER} + </foreach> + AND user_id = #{user.objectID,jdbcType=INTEGER} + AND permission = 'READ' + + </select> + + <!-- Select single sharing profile by name --> + <select id="selectOneByName" resultMap="SharingProfileResultMap"> + + SELECT + sharing_profile_id, + sharing_profile_name, + primary_connection_id + FROM [guacamole].[sharing_profile] + WHERE + primary_connection_id = #{parentIdentifier,jdbcType=INTEGER} + AND sharing_profile_name = #{name,jdbcType=VARCHAR} + + </select> + + <!-- Delete single sharing profile by identifier --> + <delete id="delete"> + DELETE FROM [guacamole].[sharing_profile] + WHERE sharing_profile_id = #{identifier,jdbcType=INTEGER} + </delete> + + <!-- Insert single sharing profile --> + <insert id="insert" useGeneratedKeys="true" keyProperty="object.objectID" + parameterType="org.apache.guacamole.auth.jdbc.sharingprofile.SharingProfileModel"> + + INSERT INTO [guacamole].[sharing_profile] ( + sharing_profile_name, + primary_connection_id + ) + VALUES ( + #{object.name,jdbcType=VARCHAR}, + #{object.parentIdentifier,jdbcType=INTEGER} + ) + + </insert> + + <!-- Update single sharing profile --> + <update id="update" parameterType="org.apache.guacamole.auth.jdbc.sharingprofile.SharingProfileModel"> + UPDATE [guacamole].[sharing_profile] + SET sharing_profile_name = #{object.name,jdbcType=VARCHAR}, + primary_connection_id = #{object.parentIdentifier,jdbcType=INTEGER} + WHERE sharing_profile_id = #{object.objectID,jdbcType=INTEGER} + </update> + +</mapper> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/b6e88d33/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileParameterMapper.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileParameterMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileParameterMapper.xml new file mode 100644 index 0000000..8835350 --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/sharingprofile/SharingProfileParameterMapper.xml @@ -0,0 +1,68 @@ +<?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.sharingprofile.SharingProfileParameterMapper"> + + <!-- Result mapper for sharing profile parameters --> + <resultMap id="ParameterResultMap" type="org.apache.guacamole.auth.jdbc.sharingprofile.SharingProfileParameterModel"> + <result column="sharing_profile_id" property="sharingProfileIdentifier" jdbcType="INTEGER"/> + <result column="parameter_name" property="name" jdbcType="VARCHAR"/> + <result column="parameter_value" property="value" jdbcType="VARCHAR"/> + </resultMap> + + <!-- Select all parameters of a given sharing profile --> + <select id="select" resultMap="ParameterResultMap"> + SELECT + sharing_profile_id, + parameter_name, + parameter_value + FROM [guacamole].[sharing_profile_parameter] + WHERE + sharing_profile_id = #{identifier,jdbcType=INTEGER} + </select> + + <!-- Delete all parameters of a given sharing profile --> + <delete id="delete"> + DELETE FROM [guacamole].[sharing_profile_parameter] + WHERE sharing_profile_id = #{identifier,jdbcType=INTEGER} + </delete> + + <!-- Insert all given parameters --> + <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.sharingprofile.SharingProfileParameterModel"> + + INSERT INTO [guacamole].[sharing_profile_parameter] ( + sharing_profile_id, + parameter_name, + parameter_value + ) + VALUES + <foreach collection="parameters" item="parameter" separator=","> + (#{parameter.sharingProfileIdentifier,jdbcType=INTEGER} + #{parameter.name,jdbcType=VARCHAR}, + #{parameter.value,jdbcType=VARCHAR}) + </foreach> + + </insert> + + +</mapper> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/b6e88d33/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 new file mode 100644 index 0000000..9ad67a6 --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml @@ -0,0 +1,79 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" + "http://mybatis.org/dtd/mybatis-3-mapper.dtd"; > + +<!-- + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. +--> + +<mapper namespace="org.apache.guacamole.auth.jdbc.user.PasswordRecordMapper" > + + <!-- Result mapper for historical passwords --> + <resultMap id="PasswordRecordResultMap" type="org.apache.guacamole.auth.jdbc.user.PasswordRecordModel"> + <result column="user_id" property="userID" jdbcType="INTEGER"/> + <result column="password_hash" property="passwordHash" jdbcType="BINARY"/> + <result column="password_salt" property="passwordSalt" jdbcType="BINARY"/> + <result column="password_date" property="passwordDate" jdbcType="TIMESTAMP"/> + </resultMap> + + <!-- Select all password records for a given user --> + <select id="select" resultMap="PasswordRecordResultMap"> + + SELECT + [guacamole].[user_password_history].user_id, + [guacamole].[user_password_history].password_hash, + [guacamole].[user_password_history].password_salt, + [guacamole].[user_password_history].password_date + FROM [guacamole].[user_password_history] + JOIN [guacamole].[user] ON [guacamole].[user_password_history].user_id = [guacamole].[user].user_id + WHERE + [guacamole].[user].username = #{username,jdbcType=VARCHAR} + ORDER BY + [guacamole].[user_password_history].password_date DESC + LIMIT #{maxHistorySize} + + </select> + + <!-- Insert the given password record --> + <insert id="insert" parameterType="org.apache.guacamole.auth.jdbc.user.PasswordRecordModel"> + + INSERT INTO [guacamole].[user_password_history] ( + user_id, + password_hash, + password_salt, + password_date + ) + VALUES ( + #{record.userID,jdbcType=INTEGER}, + #{record.passwordHash,jdbcType=BINARY}, + #{record.passwordSalt,jdbcType=BINARY}, + #{record.passwordDate,jdbcType=TIMESTAMP} + ); + + DELETE FROM [guacamole].[user_password_history] + WHERE password_history_id IN ( + SELECT password_history_id + FROM [guacamole].[user_password_history] + WHERE user_id = #{record.userID,jdbcType=INTEGER} + ORDER BY password_date DESC + OFFSET #{maxHistorySize} + ); + + </insert> + +</mapper> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/b6e88d33/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 new file mode 100644 index 0000000..a4ceea7 --- /dev/null +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml @@ -0,0 +1,216 @@ +<?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.UserMapper" > + + <!-- Result mapper for user objects --> + <resultMap id="UserResultMap" type="org.apache.guacamole.auth.jdbc.user.UserModel" > + <id column="user_id" property="objectID" jdbcType="INTEGER"/> + <result column="username" 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"/> + <result column="disabled" property="disabled" jdbcType="INTEGER"/> + <result column="expired" property="expired" jdbcType="INTEGER"/> + <result column="access_window_start" property="accessWindowStart" jdbcType="TIME"/> + <result column="access_window_end" property="accessWindowEnd" jdbcType="TIME"/> + <result column="valid_from" property="validFrom" jdbcType="DATE"/> + <result column="valid_until" property="validUntil" jdbcType="DATE"/> + <result column="timezone" property="timeZone" jdbcType="VARCHAR"/> + <result column="full_name" property="fullName" jdbcType="VARCHAR"/> + <result column="email_address" property="emailAddress" jdbcType="VARCHAR"/> + <result column="organization" property="organization" jdbcType="VARCHAR"/> + <result column="organizational_role" property="organizationalRole" jdbcType="VARCHAR"/> + </resultMap> + + <!-- Select all usernames --> + <select id="selectIdentifiers" resultType="string"> + SELECT username + FROM [guacamole].[user] + </select> + + <!-- Select usernames of all readable users --> + <select id="selectReadableIdentifiers" resultType="string"> + SELECT username + FROM [guacamole].[user] + JOIN [guacamole].[user_permission] ON affected_user_id = [guacamole].[user].user_id + WHERE + [guacamole].[user_permission].user_id = #{user.objectID,jdbcType=INTEGER} + AND permission = 'READ' + </select> + + <!-- Select multiple users by username --> + <select id="select" resultMap="UserResultMap"> + + SELECT + user_id, + username, + password_hash, + password_salt, + password_date, + disabled, + expired, + access_window_start, + access_window_end, + valid_from, + valid_until, + timezone, + full_name, + email_address, + organization, + organizational_role + FROM [guacamole].[user] + WHERE username IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=VARCHAR} + </foreach> + + </select> + + <!-- Select multiple users by username only if readable --> + <select id="selectReadable" resultMap="UserResultMap"> + + SELECT + [guacamole].[user].user_id, + username, + password_hash, + password_salt, + password_date, + disabled, + expired, + access_window_start, + access_window_end, + valid_from, + valid_until, + timezone, + full_name, + email_address, + organization, + organizational_role + FROM [guacamole].[user] + JOIN [guacamole].[user_permission] ON affected_user_id = [guacamole].[user].user_id + WHERE username IN + <foreach collection="identifiers" item="identifier" + open="(" separator="," close=")"> + #{identifier,jdbcType=VARCHAR} + </foreach> + AND [guacamole].[user_permission].user_id = #{user.objectID,jdbcType=INTEGER} + AND permission = 'READ' + + </select> + + <!-- Select single user by username --> + <select id="selectOne" resultMap="UserResultMap"> + + SELECT + user_id, + username, + password_hash, + password_salt, + password_date, + disabled, + expired, + access_window_start, + access_window_end, + valid_from, + valid_until, + timezone, + full_name, + email_address, + organization, + organizational_role + FROM [guacamole].[user] + WHERE + username = #{username,jdbcType=VARCHAR} + + </select> + + <!-- Delete single user by username --> + <delete id="delete"> + DELETE FROM [guacamole].[user] + WHERE username = #{identifier,jdbcType=VARCHAR} + </delete> + + <!-- Insert single user --> + <insert id="insert" useGeneratedKeys="true" keyProperty="object.objectID" + parameterType="org.apache.guacamole.auth.jdbc.user.UserModel"> + + INSERT INTO [guacamole].[user] ( + username, + password_hash, + password_salt, + password_date, + disabled, + expired, + access_window_start, + access_window_end, + valid_from, + valid_until, + timezone, + full_name, + email_address, + organization, + organizational_role + ) + VALUES ( + #{object.identifier,jdbcType=VARCHAR}, + #{object.passwordHash,jdbcType=BINARY}, + #{object.passwordSalt,jdbcType=BINARY}, + #{object.passwordDate,jdbcType=TIMESTAMP}, + #{object.disabled,jdbcType=INTEGER}, + #{object.expired,jdbcType=INTEGER}, + #{object.accessWindowStart,jdbcType=TIME}, + #{object.accessWindowEnd,jdbcType=TIME}, + #{object.validFrom,jdbcType=DATE}, + #{object.validUntil,jdbcType=DATE}, + #{object.timeZone,jdbcType=VARCHAR}, + #{object.fullName,jdbcType=VARCHAR}, + #{object.emailAddress,jdbcType=VARCHAR}, + #{object.organization,jdbcType=VARCHAR}, + #{object.organizationalRole,jdbcType=VARCHAR} + ) + + </insert> + + <!-- Update single user --> + <update id="update" parameterType="org.apache.guacamole.auth.jdbc.user.UserModel"> + UPDATE [guacamole].[user] + SET password_hash = #{object.passwordHash,jdbcType=BINARY}, + password_salt = #{object.passwordSalt,jdbcType=BINARY}, + password_date = #{object.passwordDate,jdbcType=TIMESTAMP}, + disabled = #{object.disabled,jdbcType=INTEGER}, + expired = #{object.expired,jdbcType=INTEGER}, + access_window_start = #{object.accessWindowStart,jdbcType=TIME}, + access_window_end = #{object.accessWindowEnd,jdbcType=TIME}, + valid_from = #{object.validFrom,jdbcType=DATE}, + valid_until = #{object.validUntil,jdbcType=DATE}, + timezone = #{object.timeZone,jdbcType=VARCHAR}, + full_name = #{object.fullName,jdbcType=VARCHAR}, + email_address = #{object.emailAddress,jdbcType=VARCHAR}, + organization = #{object.organization,jdbcType=VARCHAR}, + organizational_role = #{object.organizationalRole,jdbcType=VARCHAR} + WHERE user_id = #{object.objectID,jdbcType=VARCHAR} + </update> + +</mapper> http://git-wip-us.apache.org/repos/asf/incubator-guacamole-client/blob/b6e88d33/extensions/guacamole-auth-jdbc/pom.xml ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/pom.xml b/extensions/guacamole-auth-jdbc/pom.xml index 2a5ef5b..7869c86 100644 --- a/extensions/guacamole-auth-jdbc/pom.xml +++ b/extensions/guacamole-auth-jdbc/pom.xml @@ -70,6 +70,7 @@ <!-- Database-specific implementations --> <module>modules/guacamole-auth-jdbc-mysql</module> <module>modules/guacamole-auth-jdbc-postgresql</module> + <module>modules/guacamole-auth-jdbc-sqlserver</module> </modules>
