kyuubi/docs/security/jdbc.md

Configure Kyuubi to Use JDBC Authentication

Kyuubi supports authentication via JDBC query. A query is prepared with user/password value and sent to the database configured in JDBC URL. Authentication passes if the result set is not empty.

The SQL statement must start with the SELECT clause. Placeholders are supported and listed below for substitution:

• ${user}
• ${password}

For example, SELECT 1 FROM auth_db.auth_table WHERE user=${user} AND passwd=MD5(CONCAT(salt,${password})) will be prepared as SELECT 1 FROM auth_db.auth_table WHERE user=? AND passwd=MD5(CONCAT(salt,?)) with value replacement of user and password in string type.

Enable JDBC Authentication

To enable the JDBC authentication method, we need to

• Put the JDBC driver jar file to $KYUUBI_HOME/jars directory to make it visible for the classpath of the kyuubi server.
• Configure the following properties to $KYUUBI_HOME/conf/kyuubi-defaults.conf on each node where kyuubi server is installed.

Configure the authentication properties

Configure the following properties to $KYUUBI_HOME/conf/kyuubi-defaults.conf on each node where kyuubi server is installed.

kyuubi.authentication=JDBC
kyuubi.authentication.jdbc.driver.class = com.mysql.jdbc.Driver
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}))

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().

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()