From 46a49f0b5ef205f5632b44dc07221eed79ec803d Mon Sep 17 00:00:00 2001
From: Julien Lancelot <julien.lancelot@sonarsource.com>
Date: Thu, 11 Jun 2020 10:15:31 +0200
Subject: SONAR-13472 Fix SSF-113

* SONAR-13472 Create 'SESSION_TOKENS' table
* SONAR-13472 Remove 'SESSION_TOKENS' from user when disabling an user
* SONAR-13472 Replace JwtSession expiration duration by a time
* SONAR-13472 Create, update and delete SessionToken during authentication lifecycle
* SONAR-13472 Purge expired session tokens at start-up and every day
* SONAR-13472 Improve log during session tokens cleaning
* Add example to start a Keycloak server already configured
---
 .../org/sonar/db/user/SessionTokenMapper.xml       | 59 ++++++++++++++++++++++
 1 file changed, 59 insertions(+)
 create mode 100644 server/sonar-db-dao/src/main/resources/org/sonar/db/user/SessionTokenMapper.xml

(limited to 'server/sonar-db-dao/src/main/resources')

diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/user/SessionTokenMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/user/SessionTokenMapper.xml
new file mode 100644
index 00000000000..4a655eac015
--- /dev/null
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/user/SessionTokenMapper.xml
@@ -0,0 +1,59 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd">
+
+<mapper namespace="org.sonar.db.user.SessionTokenMapper">
+
+  <sql id="columns">
+    st.uuid as uuid,
+    st.user_uuid as "userUuid",
+    st.expiration_date as "expirationDate",
+    st.created_at as "createdAt",
+    st.updated_at as "updatedAt"
+  </sql>
+
+  <select id="selectByUuid" parameterType="String" resultType="org.sonar.db.user.SessionTokenDto">
+    select
+    <include refid="columns"/>
+    from session_tokens st
+    where st.uuid=#{uuid, jdbcType=VARCHAR}
+  </select>
+
+  <insert id="insert" parameterType="Map" useGeneratedKeys="false">
+    insert into session_tokens
+    (
+      uuid,
+      user_uuid,
+      expiration_date,
+      created_at,
+      updated_at
+    )
+    values (
+      #{dto.uuid, jdbcType=VARCHAR},
+      #{dto.userUuid, jdbcType=VARCHAR},
+      #{dto.expirationDate, jdbcType=BIGINT},
+      #{dto.createdAt, jdbcType=BIGINT},
+      #{dto.updatedAt, jdbcType=BIGINT}
+    )
+  </insert>
+
+  <update id="update" parameterType="Map">
+    update session_tokens set
+      expiration_date = #{dto.expirationDate, jdbcType=BIGINT},
+      updated_at = #{dto.updatedAt, jdbcType=BIGINT}
+    where
+      uuid = #{dto.uuid, jdbcType=VARCHAR}
+  </update>
+
+    <delete id="deleteByUuid" parameterType="String">
+    delete from session_tokens where uuid = #{uuid, jdbcType=VARCHAR}
+  </delete>
+
+  <delete id="deleteByUserUuid" parameterType="String">
+    delete from session_tokens where user_uuid = #{userUuid, jdbcType=VARCHAR}
+  </delete>
+
+  <delete id="deleteExpired" parameterType="Long" >
+    delete from session_tokens where expiration_date &lt; #{now, jdbcType=BIGINT}
+  </delete>
+
+</mapper>
-- 
cgit v1.2.3