kingbase openssl身份鉴别使用指导

数字证书制作：
使用说明：文中黑色粗体字为用户输入命令

1. 查看openssl 版本
   test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl verison
   openssl: /home/test/pkg/8.0.0025/db/lib/libssl.so.1.0.0: version `OPENSSL_1.0.2’ not found (required by openssl)
   openssl: /home/test/pkg/8.0.0025/db/lib/libcrypto.so.1.0.0: version `OPENSSL_1.0.2’ not found (required by openssl)
   openssl: /home/test/pkg/8.0.0025/db/lib/libcrypto.so.1.0.0: version `OPENSSL_1.0.2g’ not found (required by openssl)

出现上述缺少链接库问题，是因为LD_LIBRARY_PATH用的是数据库的lib，置空后正常，
test@Kylin:~/pkg/8.0.0025/db/bin/ca$ export LD_LIBRARY_PATH=
test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl version
OpenSSL 1.0.2g 1 Mar 2016

2. CA证书制作：
   test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl req -newkey rsa:2048 -keyout ca.key -keyform PEM -out ca.csr -outform PEM
   Generating a 2048 bit RSA private key
   …+++
   …+++
   writing new private key to ‘ca.key’
   Enter PEM pass phrase:123456
   Verifying - Enter PEM pass phrase: 123456

---

You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.

Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:BJ
Locality Name (eg, city) []:BJ
Organization Name (eg, company) [Internet Widgits Pty Ltd]:KB
Organizational Unit Name (eg, section) []:KB
Common Name (e.g. server FQDN or YOUR name) []:CA
Email Address []:
Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl x509 -req -in ca.csr -out ca.crt -signkey ca.key -days 3650
Signature ok
subject=/C=CN/ST=BJ/L=BJ/O=KB/OU=KB/CN=CA
Getting Private key
Enter pass phrase for ca.key:123456
3. Root证书制作：
test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl req -newkey rsa:2048 -keyout root.key -keyform PEM -out root.csr -outform PEM
Generating a 2048 bit RSA private key
…+++
…+++
writing new private key to ‘root.key’
Enter PEM pass phrase:123456
Verifying - Enter PEM pass phrase:123456

You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.

Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:BJ
Locality Name (eg, city) []:BJ
Organization Name (eg, company) [Internet Widgits Pty Ltd]:KB
Organizational Unit Name (eg, section) []:KB
Common Name (e.g. server FQDN or YOUR name) []:ROOT
Email Address []:
Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl x509 -req -in root.csr -out root.crt -signkey root.key -days 3650
Signature ok
subject=/C=CN/ST=BJ/L=BJ/O=KB/OU=KB/CN=ROOT
Getting Private key
Enter pass phrase for root.key:123456

4. 生成服务器证书：
   test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl genrsa -des3 -out server.key 1024
   Generating RSA private key, 1024 bit long modulus
   …++++++
   .++++++
   e is 65537 (0x10001)
   Enter pass phrase for server.key:123456
   Verifying - Enter pass phrase for server.key:123456

test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl req -new -key server.key -out server.csr
Enter pass phrase for server.key:123456
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.

Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:BJ
Locality Name (eg, city) []:BJ
Organization Name (eg, company) [Internet Widgits Pty Ltd]:KB
Organizational Unit Name (eg, section) []:KB
Common Name (e.g. server FQDN or YOUR name) []:SERVER
Email Address []:
Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

***拷贝openssl配置文件到当前目录：
cp /etc/ssl/openssl.cnf ./
test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl x509 -sha1 -req -in server.csr -CA root.crt -CAkey root.key -CAcreateserial -out server.crt -outform PEM -days 3650 -passin pass:123456 -extfile ./openssl.cnf -extensions v3_req
Signature ok
subject=/C=CN/ST=BJ/L=BJ/O=KB/OU=KB/CN=SERVER
Getting CA Private Key

5. 生成用户usystem证书：

test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl genrsa -des3 -out usystem.key 1024
Generating RSA private key, 1024 bit long modulus
…++++++
…++++++
e is 65537 (0x10001)
Enter pass phrase for usystem.key:123456
Verifying - Enter pass phrase for usystem.key:123456

test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl req -new -key usystem.key -out usystem.csr
Enter pass phrase for usystem.key:123456
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.

Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:BJ
Locality Name (eg, city) []:BJ
Organization Name (eg, company) [Internet Widgits Pty Ltd]:KB
Organizational Unit Name (eg, section) []:KB
Common Name (e.g. server FQDN or YOUR name) []:SYSTEM
Email Address []:
Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl x509 -sha1 -req -in usystem.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out usystem.crt -outform PEM -days 3650 -passin pass:123456 -extfile ./openssl.cnf -extensions v3_req
Signature ok
subject=/C=CN/ST=BJ/L=BJ/O=KB/OU=KB/CN=SYSTEM
Getting CA Private Key
6. 生成kingbase证书：
test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl genrsa -des3 -out kingbase.key 1024
Generating RSA private key, 1024 bit long modulus
…++++++
…++++++
e is 65537 (0x10001)
Enter pass phrase for kingbase.key:123456
Verifying - Enter pass phrase for kingbase.key: 123456
test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl req -new -key kingbase.key -out kingbase.csr
Enter pass phrase for kingbase.key:123456
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.

Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:BJ
Locality Name (eg, city) []:BJ
Organization Name (eg, company) [Internet Widgits Pty Ltd]:KB
Organizational Unit Name (eg, section) []:KB
Common Name (e.g. server FQDN or YOUR name) []:KINGBASE
Email Address []:
Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl x509 -sha1 -req -in kingbase.csr -CA root.crt -CAkey root.key -CAcreateserial -out kingbase.crt -outform PEM -days 3650 -passin pass:123456 -extfile ./openssl.cnf -extensions v3_req
Signature ok
subject=/C=CN/ST=BJ/L=BJ/O=KB/OU=KB/CN=KINGBASE
Getting CA Private Key

7. 生成userver证书：
   test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl genrsa -des3 -out userver.key 1024
   Generating RSA private key, 1024 bit long modulus
   …++++++
   …++++++
   e is 65537 (0x10001)
   Enter pass phrase for userver.key:123456
   Verifying - Enter pass phrase for userver.key:123456
   test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl req -new -key userver.key -out userver.csr
   Enter pass phrase for userver.key:123456
   You are about to be asked to enter information that will be incorporated
   into your certificate request.
   What you are about to enter is what is called a Distinguished Name or a DN.
   There are quite a few fields but you can leave some blank
   For some fields there will be a default value,
   If you enter ‘.’, the field will be left blank.

---

Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:BJ
Locality Name (eg, city) []:BJ
Organization Name (eg, company) [Internet Widgits Pty Ltd]:KB
Organizational Unit Name (eg, section) []:KB
Common Name (e.g. server FQDN or YOUR name) []:SERVER
Email Address []:
Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
test@Kylin:~/pkg/8.0.0025/db/bin/ca$ openssl x509 -sha1 -req -in userver.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out userver.crt -outform PEM -days 3650 -passin pass:123456 -extfile ./openssl.cnf -extensions v3_req
Signature ok
subject=/C=CN/ST=BJ/L=BJ/O=KB/OU=KB/CN=SERVER
Getting CA Private Key
连接测试

1. 数据准备：
   a) 在kingbase.conf中增加 ssl=on；
   b) 在sys_hba.conf中，host修改为 hostssl，MD5修改为ukpwd ；
   c) 把 所有的 crt和key 文件权限改为 600；
   d) 把 ca.crt、root.crt、server.crt、userver.crt、server.key、userver.key 放到 数据库目录下；
   e) 把 ca.crt、root.crt、kingbase.crt、usyssao.crt、usyssso.crt、usystem.crt 、kingbase.key、usyssao.key、usyssso.key、usystem.key 放到bin目录下；

2. 启动数据库：
   test@Kylin:~/pkg/8.0.0025/db/bin$ ./kingbase -D ca_data/
   LOG: 转存许可证信息…
   LOG: 许可证版本：2.0
   LOG: 产品线：ES
   LOG: 产品版本：8.0.0
   LOG: 版本类型：ME
   LOG: 操作系统：LNX
   LOG: 体系结构：X64
   LOG: 浮动日期模式：是
   LOG: 基准日期：2019-09-26
   LOG: 许可证有效时间：180天
   LOG: 最大连接数：无限制
   LOG: 用户信息：V8
   LOG: 试用模式：否
   LOG: 网卡物理地址检查模式：否
   LOG: 网卡物理地址：
   LOG: IPV4地址检查模式：否
   LOG: IPV4地址：
   LOG: 序列号：1e763e91-cb914c9a-dfe158e5-f462527b
   sh: /home/test/pkg/8.0.0025/db/lib/libtinfo.so.5: no version information available (required by sh)
   please enter the server key password:123456
   LOG: SSL 的证书吊销列表文件"/home/test/pkg/8.0.0025/db/bin/ca_data/root.crl"未找到，忽略：没有那个文件或目录
   DETAIL: 证书将不予核对吊销列表
   LOG: the encrypt device is opened
   LOG: 数据库系统已关闭在 2019-09-27 16:39:46 CST
   LOG: checkpoint record is at 1/1DB9050
   LOG: redo record is at 1/1DB9050; undo record is at 0/0; shutdown TRUE
   LOG: 下一个事务ID: 0/269; 下一个OID: 31827
   LOG: 下一个 MultiXactId: 1; 下一个 MultiXactOffset: 0
   LOG: 数据库系统准备接受连接

注：上面缺失root.crl警告信息不影响功能正常使用。
3. 连接测试：
a) 正常连接测试：
test@Kylin:~/pkg/8.0.0025/db/bin$ ./isql -p19940 -USYSTEM -d TEST -b ./kingbase.crt -r ./root.crt -k ./kingbase.key -g usystem.crt -G usystem.key -p19970
用户密码 SYSTEM:123456
Enter the password of client key:123456
please input the usb token pin:123456
欢迎使用 isql 8.0.0.0025 release 64 bit, Kingbase 交互式终端.

本次登录信息:
用户名: SYSTEM
主机: [local]
登录时间: 2019-09-27 16:42:57.052739+08

这是你第一次登录.

本次登录和最后一次登录之间的失败次数: 0

密码的过期时间没有设置.

类型: \h SQL帮助命令
? isql帮助命令
\g 将之前的SQL语句发往服务器执行
\q 退出

TEST=#

b) 异常连接测试（用户密码错误）：
test@Kylin:~/pkg/8.0.0025/db/bin$ ./isql -p19940 -USYSTEM -d TEST -b ./kingbase.crt -r ./root.crt -k ./kingbase.key -g usystem.crt -G usystem.key -p19970
用户密码 SYSTEM:123
Enter the password of client key:123456
please input the usb token pin:123456
isql: FATAL: 用户"SYSTEM"的口令认证失败

c) 异常连接测试（pin码错误）：
test@Kylin:~/pkg/8.0.0025/db/bin$ ./isql -p19940 -USYSTEM -d TEST -b ./kingbase.crt -r ./root.crt -k ./kingbase.key -g usystem.crt -G usystem.key -p19970
用户密码 SYSTEM:123456
Enter the password of client key:123456
please input the usb token pin:1234
isql: could not read usbkey key file, maybe wrong pin code

注：-b kingbase用户证书，-r root用户证书，-k kingbase用户的key -g 指定的是用户证书，-G 指定对应的key，