# 1. 创建证书目录
$ mkdir -p /ob/app/oceanbase/etc/ssl
$ cd /ob/app/oceanbase/etc/ssl

# 2. 生成CA私钥
$ openssl genrsa 2048 > ca-key.pem

Generating RSA private key, 2048 bit long modulus
……………………..+++
……………………..+++
e is 65537 (0x10001)

# 3. 生成CA证书
$ openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem

You are about to be asked to enter information that will be incorporated
into your certificate request.
Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:Beijing
Locality Name (eg, city) []:Beijing
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Fgedu
Organizational Unit Name (eg, section) []:DBA
Common Name (e.g. server FQDN or YOUR name) []:Fgedu CA
Email Address []:dba@fgedu.net.cn

# 4. 生成服务端私钥，更多学习教程公众号风哥教程itpux_com。
$ openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem

# 5. 生成服务端证书
$ openssl rsa -in server-key.pem -out server-key.pem
$ openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Signature ok
subject=/C=CN/ST=Beijing/L=Beijing/O=Fgedu/OU=DBA/CN=fgedu.net.cn
Getting CA Private Key

# 6. 验证证书
$ openssl verify -CAfile ca-cert.pem server-cert.pem

server-cert.pem: OK

# 7. 设置证书权限
$ chmod 600 *.pem
$ chown admin:admin *.pem

# 8. 查看证书信息
$ openssl x509 -in server-cert.pem -text -noout

Certificate:
Data:
Version: 3 (0x2)
Serial Number: 1 (0x1)
Signature Algorithm: sha256WithRSAEncryption
Issuer: C=CN, ST=Beijing, L=Beijing, O=Fgedu, OU=DBA, CN=Fgedu CA
Validity
Not Before: Jan 20 00:00:00 2024 GMT，from DB视频:www.itpux.com。
Not After : Jan 17 00:00:00 2034 GMT
Subject: C=CN, ST=Beijing, L=Beijing, O=Fgedu, OU=DBA, CN=fgedu.net.cn
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
Public-Key: (2048 bit)

# 1. 配置OBServer SSL参数
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
ALTER SYSTEM SET ssl_client_authentication=’TRUE’;
ALTER SYSTEM SET ssl_ca=’/ob/app/oceanbase/etc/ssl/ca-cert.pem’;
ALTER SYSTEM SET ssl_cert=’/ob/app/oceanbase/etc/ssl/server-cert.pem’;
ALTER SYSTEM SET ssl_key=’/ob/app/oceanbase/etc/ssl/server-key.pem’;
”

# 2. 重启OBServer使配置生效
$ ssh admin@192.168.1.101
$ cd /ob/app/oceanbase
$ ./bin/observer -stop
$ ./bin/observer -start

# 3. 验证SSL配置
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
SHOW VARIABLES LIKE ‘%ssl%’;
”

+—————+————————–+
| Variable_name | Value |
+—————+————————–+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /ob/app/oceanbase/… |
| ssl_cert | /ob/app/oceanbase/… |
| ssl_key | /ob/app/oceanbase/… |
+—————+————————–+

# 4. 使用SSL连接
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p \
–ssl-ca=/ob/app/oceanbase/etc/ssl/ca-cert.pem \
–ssl-cert=/ob/app/oceanbase/etc/ssl/client-cert.pem \
–ssl-key=/ob/app/oceanbase/etc/ssl/client-key.pem

Welcome to the OceanBase monitor. Commands end with ; or \g.
Your OceanBase connection id is 12345
Server version: 4.2.1 OceanBase

# 5. 强制SSL连接
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
ALTER USER ‘fgedu_app’@’192.168.1.%’ REQUIRE SSL;
”

Query OK, 0 rows affected

# 1. 创建加密表空间（Oracle模式）
$ obclient -h192.168.1.101 -P2881 -usys@fgedu_tenant -p -e ”
— 创建钱包
ALTER SYSTEM SET TDE_KEYSTORE=KEYSTORE IDENTIFIED BY wallet_password;

— 打开钱包
ALTER SYSTEM SET TDE_KEYSTORE=OPEN IDENTIFIED BY wallet_password;

— 创建加密表空间
CREATE TABLESPACE fgedu_encrypted_ts
DATAFILE ‘/ob/fgdata/fgedu_encrypted_ts.dbf’ SIZE 100M
ENCRYPTION USING ‘AES256’
DEFAULT STORAGE(ENCRYPT);
”

# 2. 创建加密表
$ obclient -h192.168.1.101 -P2881 -usys@fgedu_tenant -p -e ”
CREATE TABLE fgedu_sensitive_data (
id NUMBER PRIMARY KEY,
card_no VARCHAR2(20),
phone VARCHAR2(20),
address VARCHAR2(200)
) TABLESPACE fgedu_encrypted_ts;
”

# 3. 查看加密状态
$ obclient -h192.168.1.101 -P2881 -usys@fgedu_tenant -p -e ”
SELECT tablespace_name, encrypted
FROM dba_tablespaces
WHERE tablespace_name = ‘FGEDU_ENCRYPTED_TS’;
”

+———————+———–+
| TABLESPACE_NAME | ENCRYPTED |
+———————+———–+
| FGEDU_ENCRYPTED_TS | YES |
+———————+———–+

# 4. 查看钱包状态
$ obclient -h192.168.1.101 -P2881 -usys@fgedu_tenant -p -e ”
SELECT wrl_type, status, wallet_type
FROM v$encryption_wallet;
”

+———–+——–+————-+
| WRL_TYPE | STATUS | WALLET_TYPE |
+———–+——–+————-+
| FILE | OPEN | PASSWORD |
+———–+——–+————-+

# 1. 开启标准审计
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
— 开启标准审计
ALTER SYSTEM SET audit_log_policy=’ALL’;
”

# 2. 配置审计策略
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
— 审计所有DDL操作
AUDIT DDL;

— 审计特定表的DML操作
AUDIT SELECT, INSERT, UPDATE, DELETE ON fgedudb.fgedu_user;

— 审计用户登录
AUDIT SESSION;

— 审计权限变更
AUDIT GRANT, REVOKE;
”

# 3. 查看审计配置
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
SELECT * FROM information_schema.INNODB_AUDIT_LOG;
”

# 4. 查看审计日志
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
SELECT
event_time,
user_host,
command_type,
argument
FROM mysql.audit_log
ORDER BY event_time DESC
LIMIT 10;
”

+———————+——————+————–+——————+
| event_time | user_host | command_type | argument |
+———————+——————+————–+——————+
| 2024-01-20 10:00:00 | fgedu_app[app] | SELECT | SELECT * FROM… |
| 2024-01-20 09:59:00 | root[sys] | ALTER | ALTER USER… |
| 2024-01-20 09:58:00 | fgedu_dba[sys] | CREATE | CREATE TABLE… |
+———————+——————+————–+——————+

# 5. 关闭特定审计
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
— 关闭表审计
NOAUDIT SELECT ON fgedudb.fgedu_user;

— 关闭DDL审计
NOAUDIT DDL;
”