MariaDB “JDBC/Connector J” Connection with SSL enabled.
[root@dbversity-com rh-mariadb101]# cat /etc/opt/rh/rh-mariadb101/my.cnf
# This group is read both both by the client and the server
# use it for options that affect everything
[client-server]
#################### <– Client-Server SSL Options–> #######################################
ssl
ssl-cipher = AES128+EECDH:AES128+EDH
ssl-ca = /etc/opt/rh/rh-mariadb101/pki/ca.pem
ssl-cert = /etc/opt/rh/rh-mariadb101/pki/mariadb_cert.pem
ssl-key = /etc/opt/rh/rh-mariadb101/pki/mariadb_private.key
[client]
[mysqld]
port = 4306
performance_schema = on
skip-show-database
symbolic-links = 0
local-infile = 0
bind-address = dbversity-com
wait_timeout = 1800
interactive_timeout = 1800
#################### <– MariaDB Plugins –> ###############################################
pam_use_cleartext_plugin
plugin-dir = /opt/rh/rh-mariadb101/root/usr/lib64/mysql/plugin/
plugin-load = auth_gssapi.so
plugin-load = auth_pam.so
plugin-load = simple_password_check.so
plugin-load = server_audit.so
plugin-load = pam_unix.so
##################### <– Kerberos configuration –> #######################################
#gssapi-keytab-path = /etc/opt/rh/rh-mariadb101/mariadb.keytab
##################### <– Logging switches –> #######################################
general-log
general-log-file = /var/opt/rh/rh-mariadb101/lib/mysql/general_query.log
log-output = file
log_warnings = 3
slow-query-log-file = /var/opt/rh/rh-mariadb101/lib/mysql/slow-query.log
# include all files from the config directory
#
!includedir /etc/opt/rh/rh-mariadb101/my.cnf.d
[root@dbversity-com rh-mariadb101]#
[root@dbversity-com security]# grep ‘ssl’ /etc/opt/rh/rh-mariadb101/my.cnf
ssl
ssl-cipher = AES128+EECDH:AES128+EDH
ssl-ca = /etc/opt/rh/rh-mariadb101/pki/ca.pem
ssl-cert = /etc/opt/rh/rh-mariadb101/pki/mariadb_cert.pem
ssl-key = /etc/opt/rh/rh-mariadb101/pki/mariadb_private.key
[root@dbversity-com security]#
[root@dbversity-com security]#
[root@dbversity-com security]#
[root@dbversity-com security]#
[root@dbversity-com security]#
[root@dbversity-com security]# cd /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.101-3.b13.el6_8.x86_64/jre/lib/security/
[root@dbversity-com security]#
shell> keytool -import -alias mysqlServerCACert -file cacert.pem -keystore truststore
[root@dbversity-com security]#
[root@dbversity-com security]# keytool -importcert -trustcacerts -file /etc/opt/rh/rh-mariadb101/pki/mariadb_cert.pem -alias dbversity -keystore /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.101-3.b13.el6_8.x86_64/jre/lib/security/cacerts -storepass changeit
Owner: EMAILADDRESS=dbversity@dbversity-com, CN=dbversity-com.dbversity-com, OU=, O=group ., L=, ST=, C=IN
Issuer: CN= Internal DeviceCA Untrusted, DC=nsroot, DC=net
Trust this certificate? [no]: yes
Certificate was added to keystore
[root@dbversity-com security]#
[root@dbversity-com security]#
[root@dbversity-com security]#
[root@dbversity-com security]#
[root@dbversity-com rh-mariadb101]# cat ~/.bashrc
# .bashrc
# User specific aliases and functions
alias rm=’rm -i’
alias cp=’cp -i’
alias mv=’mv -i’
export MYSQL_HISTFILE=$MYSQL_HISTFILE:/dev/null
ln -s /dev/null $MYSQL_HISTFILE
export MARIADB_ROOT=/opt/rh/rh-mariadb101
. $MARIADB_ROOT/enable
export MONYOG_HOME=/opt/mariadb/MONyog
export PATH=$PATH:/opt/mariadb/MONyog/bin
# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
#export PS1=”[ \u @ \h : \w ] ”
export JAVACONFDIRS=/etc/opt/rh/rh-mariadb101/java:/etc/java:/usr/bin/java:/opt/rh/rh-mariadb101/mariadb-java-client-1.5.2.jar
export CLASSPATH=$CLASSPATH:/opt/rh/rh-mariadb101/mariadb-java-client-1.5.2.jar:/usr/bin/java
[root@dbversity-com rh-mariadb101]#
[root@dbversity-com rh-mariadb101]#
[root@dbversity-com rh-mariadb101]# ll -lhtr
total 452K
-rwxr-x— 1 mysql mysql 1.1K Apr 19 09:59 enable
-rwxr-x— 1 mysql mysql 466 Jul 26 17:56 service-environment
drwxr-x— 19 mysql mysql 4.0K Sep 16 05:56 root
-rwxr-x— 1 mysql mysql 5.1K Oct 3 13:52 java
-rwxr-x— 1 root root 415K Oct 4 02:49 mariadb-java-client-1.5.2.jar
-rwxr-x— 1 mysql mysql 2.2K Oct 4 03:00 ConnExample.java
-rwxr-x— 1 mysql mysql 3.1K Oct 4 03:00 ConnExample.class
-rw-r–r– 1 root root 2.6K Oct 4 04:18 JdbcSsl.java
-rw-r–r– 1 root root 3.5K Oct 4 04:18 JdbcSsl.class
[root@dbversity-com rh-mariadb101]#
[root@dbversity-com rh-mariadb101]# cat JdbcSsl.java
import java.sql.*;
import javax.net.ssl.SSLSocketFactory;
import java.net.UnknownHostException;
import java.util.Arrays;
import java.util.List;
import java.security.Security;
import java.util.ArrayList;
public class JdbcSsl {
public static void main(String[] args) throws UnknownHostException {
System.setProperty(“javax.net.ssl.trustStore”,”/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.101-3.b13.el6_8.x86_64/jre/lib/security/cacerts”);
System.setProperty(“javax.net.ssl.trustStorePassword”,”changeit”);
System.setProperty(“javax.net.ssl.trustStoreType”,”jks”);
Connection conn = null;
Statement stmt = null;
try{
Class.forName(“org.mariadb.jdbc.Driver”);
System.out.println(“Connecting to database …”);
conn = DriverManager.getConnection(“jdbc:mysql://dbversity:4306/mydb?useSSL=true”, “admin”, “XXXXXXXXXXXX”);
System.out.println(“Creating a statements …”);
stmt = conn.createStatement();
stmt.executeUpdate(“DROP TABLE IF EXISTS mydb.jdbc_tbl_new”);
stmt.executeUpdate(“CREATE TABLE jdbc_tbl_new (id int not null primary key, name varchar(20))”);
stmt.executeUpdate(“INSERT INTO jdbc_tbl_new (id,name) VALUES (1,’MariaDB JDBC Testing’)”);
stmt.executeUpdate(“INSERT INTO jdbc_tbl_new (id,name) VALUES (2,’MariaDB JDBC Testing’)”);
stmt.executeUpdate(“INSERT INTO jdbc_tbl_new (id,name) VALUES (3,’MariaDB JDBC Testing’)”);
stmt.executeUpdate(“INSERT INTO jdbc_tbl_new (id,name) VALUES (4,’MariaDB JDBC Testing’)”);
stmt.executeUpdate(“INSERT INTO jdbc_tbl_new (id,name) VALUES (5,’MariaDB JDBC Testing’)”);
stmt.executeUpdate(“show databases”);
String sql = “SELECT id,name FROM jdbc_tbl_new”;
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
//Retrieve by column name
int id = rs.getInt(“id”);
String name = rs.getString(“name”);
//Display names
System.out.print(“ID: ” + id);
System.out.println(“, Value: ” + name);
}
System.out.println(“Table has been created & data inserted …”);
stmt.close();
System.out.println(“Closing the connection …”);
conn.close();
}
catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println(“Goodbye!”);
}//end main
}//end FirstExample
[root@dbversity-com rh-mariadb101]#
[root@dbversity-com rh-mariadb101]#
[root@dbversity-com rh-mariadb101]#
[root@dbversity-com rh-mariadb101]# javac JdbcSsl.java
[root@dbversity-com rh-mariadb101]#
[root@dbversity-com rh-mariadb101]#
[root@dbversity-com rh-mariadb101]# java JdbcSsl
Connecting to database …
Creating a statements …
ID: 1, Value: MariaDB JDBC Testing
ID: 2, Value: MariaDB JDBC Testing
ID: 3, Value: MariaDB JDBC Testing
ID: 4, Value: MariaDB JDBC Testing
ID: 5, Value: MariaDB JDBC Testing
Table has been created & data inserted …
Closing the connection …
Goodbye!
[root@dbversity-com rh-mariadb101]#
[root@dbversity-com rh-mariadb101]#
[root@dbversity-com rh-mariadb101]#
[root@dbversity-com rh-mariadb101]# mysql -h dbversity-com -P 4306 -D mydb -u admin -p’XXXXXXXXXXXX’
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1573
Server version: 10.1.16-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [mydb]> \s
————–
mysql Ver 15.1 Distrib 10.1.16-MariaDB, for Linux (x86_64) using EditLine wrapper
Connection id: 1573
Current database: mydb
Current user: admin@dbversity-com.dbversity-com
SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server: MariaDB
Server version: 10.1.16-MariaDB MariaDB Server
Protocol version: 10
Connection: dbversity-com via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 4306
Uptime: 5 days 3 hours 52 min 40 sec
Threads: 3 Questions: 55837 Slow queries: 16142 Opens: 97 Flush tables: 1 Open tables: 86 Queries per second avg: 0.125
————–
MariaDB [mydb]>
MariaDB [mydb]> show grants for admin@’%’;
+————————————————————————————————————————————————+
| Grants for admin@% |
+————————————————————————————————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘admin’@’%’ IDENTIFIED BY PASSWORD ‘*9F1E8C39D634182D145B3D26DE12D09F7C66D903’ REQUIRE SSL WITH GRANT OPTION |
+————————————————————————————————————————————————+
1 row in set (0.00 sec)
MariaDB [mydb]>
MariaDB [mydb]> show tables;
+—————-+
| Tables_in_mydb |
+—————-+
| jdbc_tbl_new |
+—————-+
1 row in set (0.00 sec)
MariaDB [mydb]>
MariaDB [mydb]> select * from jdbc_tbl_new;
+—-+———————-+
| id | name |
+—-+———————-+
| 1 | MariaDB JDBC Testing |
| 2 | MariaDB JDBC Testing |
| 3 | MariaDB JDBC Testing |
| 4 | MariaDB JDBC Testing |
| 5 | MariaDB JDBC Testing |
+—-+———————-+
5 rows in set (0.00 sec)
MariaDB [mydb]>
MariaDB [mydb]> exit
Bye
[root@dbversity-com rh-mariadb101]#
Awesome article which will help me enable to work on JDBC/ODBC connections hassle-free