[incubator-kyuubi] branch branch-1.6 updated: [KYUUBI #3705] Add docs for JDBC authentication usage with in-memory database

[ch...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

chengpan pushed a commit to branch branch-1.6
in repository https://gitbox.apache.org/repos/asf/incubator-kyuubi.git


The following commit(s) were added to refs/heads/branch-1.6 by this push:
     new cbea330d7 [KYUUBI #3705] Add docs for JDBC authentication usage with in-memory database
cbea330d7 is described below

commit cbea330d79637049afe0cf939fbabc2356740ec0
Author: Bowen Liang <li...@gf.com.cn>
AuthorDate: Thu Oct 27 11:06:36 2022 +0800

    [KYUUBI #3705] Add docs for JDBC authentication usage with in-memory database
    
    ### _Why are the changes needed?_
    
    to close #3705 .
    
    Add docs for JDBC authentication usage with in-memory database with config example for token validation example.
    
    ### _How was this patch tested?_
    - [ ] Add some test cases that check the changes thoroughly including negative and positive cases if possible
    
    - [ ] Add screenshots for manual tests if appropriate
    
    - [x] [Run test](https://kyuubi.apache.org/docs/latest/develop_tools/testing.html#running-tests) locally before make a pull request
    
    Closes #3706 from bowenliang123/jdbc-inmem.
    
    Closes #3705
    
    3de9bceb [Bowen Liang] use backslash for multi-line config value
    26a4d1b7 [Bowen Liang] nit
    e078e985 [Bowen Liang] add JDBC auth usage with in-memory db for token validation
    67624faf [liangbowen] init jdbc inmem doc
    
    Lead-authored-by: Bowen Liang <li...@gf.com.cn>
    Co-authored-by: liangbowen <li...@gf.com.cn>
    Signed-off-by: Cheng Pan <ch...@apache.org>
    (cherry picked from commit 738e35100a4b31a62008de88ee466e3675cdac72)
    Signed-off-by: Cheng Pan <ch...@apache.org>
---
 docs/security/jdbc.md | 21 ++++++++++++++++++++-
 1 file changed, 20 insertions(+), 1 deletion(-)

diff --git a/docs/security/jdbc.md b/docs/security/jdbc.md
index 1f5158301..0da6634f7 100644
--- a/docs/security/jdbc.md
+++ b/docs/security/jdbc.md
@@ -46,4 +46,23 @@ kyuubi.authentication.jdbc.url = jdbc:mysql://127.0.0.1:3306/auth_db
 kyuubi.authentication.jdbc.user = bowenliang123
 kyuubi.authentication.jdbc.password = bowenliang123@kyuubi
 kyuubi.authentication.jdbc.query = SELECT 1 FROM auth_table WHERE user=${user} AND passwd=MD5(CONCAT(salt,${password}))
-```
\ No newline at end of file
+```
+
+## Authentication with In-memory Database
+
+Used with auto created in-memory database, JDBC authentication could be applied for token validation without starting up a dedicated database service or setting up a custom plugin. 
+
+Consider authentication for a pair of a username and a token which contacted with an `expire_time` in 'yyyyMMddHHmm' format and a MD5 signature generated with sequence of `expire_time`, `username` and a secret key. With the following example, an H2 in-memory database will be auto crated with Kyuubi Server and used for authentication with its system function `HASH` and checking token expire time with `NOW()`.
+
+```properties
+kyuubi.authentication=JDBC
+kyuubi.authentication.jdbc.driver.class = org.h2.Driver
+kyuubi.authentication.jdbc.url = jdbc:h2:mem:
+kyuubi.authentication.jdbc.user = no_user
+kyuubi.authentication.jdbc.query = SELECT 1 FROM ( \
+  SELECT ${user} as username, 'secret_key' as secret_key, \
+  SUBSTRING(${password}, 0, 12) as expire_time, \
+  SUBSTRING(${password}, 13) as signed \
+  ) WHERE signed = RAWTOHEX(HASH('MD5', CONCAT(secret_key, username, expire_time))) \
+  AND PARSEDATETIME(expire_time,'yyyyMMddHHmm') > NOW()
+```