# 1. 配置YashanDB审计功能
$ /yashandb/app/bin/yasql -U sys -P sys123 -D fgedudb

# 启用审计日志
SQL> ALTER SYSTEM SET audit_logging = ‘on’;
SQL> ALTER SYSTEM SET audit_level = ‘all’;
SQL> ALTER SYSTEM SET audit_directory = ‘/yashandb/audit’;

# 配置审计规则
SQL> CREATE AUDIT POLICY login_policy FOR LOGIN, LOGOUT;
SQL> CREATE AUDIT POLICY ddl_policy FOR DDL;
SQL> CREATE AUDIT POLICY dml_policy FOR DML;
SQL> CREATE AUDIT POLICY privilege_policy FOR GRANT, REVOKE;

# 2. 部署ELK Stack
# 安装Elasticsearch
$ sudo yum install elasticsearch
$ sudo systemctl start elasticsearch

# 安装Logstash
$ sudo yum install logstash
$ sudo systemctl start logstash

# 安装Kibana
$ sudo yum install kibana
$ sudo systemctl start kibana

# 3. 配置Logstash收集审计日志
$ vi /etc/logstash/conf.d/yashandb-audit.conf
input {
file {
path => “/yashandb/audit/yashandb_audit.log”
start_position => “beginning”
sincedb_path => “/dev/null”
}
}

filter {
grok {
match => {
“message” => “%{TIMESTAMP_ISO8601:timestamp} %{LOGLEVEL:level} %{DATA:user} %{DATA:action} %{GREEDYDATA:details}”
}
}
date {
match => [“timestamp”, “yyyy-MM-dd HH:mm:ss”]
target => “@timestamp”
}
}

output {
elasticsearch {
hosts => [“localhost:9200”]
index => “yashandb-audit-%{+YYYY.MM.dd}”
}
}

# 4. 配置Kibana仪表盘
# 访问Kibana界面：http://localhost:5601
# 创建索引模式：yashandb-audit-*
# 创建仪表盘，添加可视化

# 5. 验证审计系统
# 执行一些数据库操作
$ /yashandb/app/bin/yasql -U fgedu -P fgedu123 -D fgedudb
SQL> CREATE TABLE fgedu.test (id INT PRIMARY KEY, name VARCHAR(100));
SQL> INSERT INTO fgedu.test VALUES (1, ‘test’);
SQL> GRANT SELECT ON fgedu.test TO read_user;

# 查看审计日志
$ tail -f /yashandb/audit/yashandb_audit.log

# 查看Kibana仪表盘，确认数据已收集

# 1. 分析登录失败
# 在Kibana中执行查询
GET yashandb-audit-*/_search
{
“query”: {
“match”: {
“action”: “LOGIN FAILURE”
}
},
“aggs”: {
“by_user”: {
“terms”: {
“field”: “user.keyword”
}
},
“by_ip”: {
“terms”: {
“field”: “ip.keyword”
}
}
}
}

# 2. 分析DDL操作
GET yashandb-audit-*/_search
{
“query”: {
“match”: {
“action”: “DDL”
}
},
“aggs”: {
“by_user”: {
“terms”: {
“field”: “user.keyword”
}
},
“by_object”: {
“terms”: {
“field”: “object.keyword”
}
}
}
}

# 3. 分析权限变更
GET yashandb-audit-*/_search
{
“query”: {
“bool”: {
“should”: [
{ “match”: { “action”: “GRANT” } },
{ “match”: { “action”: “REVOKE” } }
]
}
},
“aggs”: {
“by_user”: {
“terms”: {
“field”: “user.keyword”
}
},
“by_privilege”: {
“terms”: {
“field”: “privilege.keyword”
}
}
}
}

# 4. 检测异常操作
GET yashandb-audit-*/_search
{
“query”: {
“bool”: {
“must”: [
{ “match”: { “level”: “ERROR” } }
],
“filter”: {
“range”: {
“@timestamp”: {
“gte”: “now-7d”
}
}
}
}
}
}

# 5. 生成审计分析报告
$ ./audit_analysis.sh
=== YashanDB安全审计分析报告 ===

1. 登录失败统计:
5

2. DDL操作统计:
10

3. DML操作统计:
100

4. 权限变更统计:
5

5. 异常操作检测:
2

6. 最近24小时审计日志摘要:
2024-01-01 10:00:00 ERROR admin LOGIN FAILURE: Invalid password
2024-01-01 10:00:01 ERROR admin LOGIN FAILURE: Invalid password
2024-01-01 10:00:02 ERROR admin LOGIN FAILURE: Invalid password
2024-01-01 11:00:00 INFO fgedu DDL: CREATE TABLE fgedu.test
2024-01-01 11:00:01 INFO fgedu DML: INSERT INTO fgedu.test

# 1. 查看审计日志
$ tail -f /yashandb/audit/yashandb_audit.log

2024-01-01 10:00:00 ERROR admin LOGIN FAILURE: Invalid password
2024-01-01 10:00:01 ERROR admin LOGIN FAILURE: Invalid password
2024-01-01 10:00:02 ERROR admin LOGIN FAILURE: Invalid password
2024-01-01 10:00:03 ERROR admin LOGIN FAILURE: Invalid password
2024-01-01 10:00:04 ERROR admin LOGIN FAILURE: Invalid password

# 2. 分析原因：可能是暴力破解攻击
# 3. 处理措施：

# 临时锁定用户
$ /yashandb/app/bin/yasql -U sys -P sys123 -D fgedudb
SQL> ALTER USER admin ACCOUNT LOCK;

# 检查登录IP
# 假设从审计日志中提取IP地址为192.168.1.100

# 配置防火墙，阻止该IP
$ sudo firewall-cmd –permanent –add-rich-rule=’rule family=”ipv4″ source address=”192.168.1.100″ reject’
$ sudo firewall-cmd –reload

# 4. 预防措施：

# 配置登录失败次数限制
SQL> ALTER SYSTEM SET password_max_failed_login_attempts = 3;

# 配置登录延迟
SQL> ALTER SYSTEM SET login_delay = ‘2s’;

# 启用双因素认证
# 配置邮件告警
# 在Elasticsearch Watcher中配置告警规则

# 5. 恢复用户
# 确认安全后解锁用户
SQL> ALTER USER admin ACCOUNT UNLOCK;
SQL> ALTER USER admin IDENTIFIED BY new_password;

# 6. 总结报告
# 记录安全事件处理过程
# 分析安全漏洞
# 提出改进建议