细粒度审计的案例

botang · 发表于 2020-11-1 20:10:42

强制性审计/SYS强制性审计/标准审计（对象/权限/语句）/细粒度/基于值的审计

统一审计（为了取代标准审计（对象/权限/语句））

1. 作handler:
/etc/postfix/main.cf:

myorigin = $myhostname
inet_interfaces = all
mydestination = $myhostname, localhost.$mydomain, localhost, classroom.example.com
mynetworks = 192.168.0.0/24

复制代码

[root@classroom postfix]# hostname
classroom.example.com
[root@classroom postfix]# vim main.cf
[root@classroom postfix]# systemctl restart postfix
[root@classroom postfix]# ping www.botangdb.com
PING www.botangdb.com (45.113.121.152) 56(84) bytes of data.
64 bytes from 45.113.121.152 (45.113.121.152): icmp_seq=1 ttl=48 time=53.7 ms
64 bytes from 45.113.121.152 (45.113.121.152): icmp_seq=2 ttl=48 time=57.2 ms
64 bytes from 45.113.121.152 (45.113.121.152): icmp_seq=3 ttl=48 time=65.5 ms
64 bytes from 45.113.121.152 (45.113.121.152): icmp_seq=4 ttl=48 time=36.4 ms
64 bytes from 45.113.121.152 (45.113.121.152): icmp_seq=5 ttl=48 time=62.6 ms
64 bytes from 45.113.121.152 (45.113.121.152): icmp_seq=6 ttl=48 time=62.10 ms
64 bytes from 45.113.121.152 (45.113.121.152): icmp_seq=7 ttl=48 time=66.7 ms
^C
--- www.botangdb.com ping statistics ---
8 packets transmitted, 7 received, 12.5% packet loss, time 16ms
rtt min/avg/max/mdev = 36.445/57.873/66.714/9.722 ms
[root@classroom postfix]# mail -s "test1" 18950350376@189.cn
bash: mail: 未找到命令...
^C
[root@classroom postfix]# yum -y install mailx
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：0:06:13 前，执行于 2020年11月01日星期日 20时05分06秒。
依赖关系解决。
=================================================================================================================================
软件包架构版本仓库大小
=================================================================================================================================
Installing:
mailx x86_64 12.5-29.el8 BaseOS 257 k
事务概要
=================================================================================================================================
安装 1 软件包
总计：257 k
安装大小：491 k
下载软件包：
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
准备中 : 1/1
Installing : mailx-12.5-29.el8.x86_64 1/1
运行脚本 : mailx-12.5-29.el8.x86_64 1/1
验证 : mailx-12.5-29.el8.x86_64 1/1
Installed products updated.
已安装:
mailx-12.5-29.el8.x86_64
完毕！
[root@classroom postfix]#

复制代码

测试外发邮件服务器：

[root@classroom postfix]# mail -s "test1" 18950350376@189.cn
This is a test 2020-11-1 20:13
.
EOT
[root@classroom postfix]# mailq
Mail queue is empty
[root@classroom postfix]#

复制代码

如果外网被挡住：

[root@classroom postfix]# yum -y install dovecot*
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：0:16:42 前，执行于 2020年11月01日星期日 20时05分06秒。
依赖关系解决。
=================================================================================================================================
软件包架构版本仓库大小
=================================================================================================================================
Installing:
dovecot x86_64 1:2.2.36-5.el8 AppStream 4.6 M
dovecot-mysql x86_64 1:2.2.36-5.el8 AppStream 98 k
dovecot-pgsql x86_64 1:2.2.36-5.el8 AppStream 101 k
安装依赖关系:
clucene-core x86_64 2.3.3.4-31.20130812.e8e3d20git.el8 AppStream 590 k
libpq x86_64 10.5-1.el8 AppStream 188 k
事务概要
=================================================================================================================================
安装 5 软件包
总计：5.5 M
安装大小：18 M
下载软件包：
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
准备中 : 1/1
Installing : libpq-10.5-1.el8.x86_64 1/5
Installing : clucene-core-2.3.3.4-31.20130812.e8e3d20git.el8.x86_64 2/5
运行脚本 : dovecot-1:2.2.36-5.el8.x86_64 3/5
Installing : dovecot-1:2.2.36-5.el8.x86_64 3/5
运行脚本 : dovecot-1:2.2.36-5.el8.x86_64 3/5
Installing : dovecot-mysql-1:2.2.36-5.el8.x86_64 4/5
Installing : dovecot-pgsql-1:2.2.36-5.el8.x86_64 5/5
运行脚本 : dovecot-1:2.2.36-5.el8.x86_64 5/5
运行脚本 : dovecot-pgsql-1:2.2.36-5.el8.x86_64 5/5
验证 : clucene-core-2.3.3.4-31.20130812.e8e3d20git.el8.x86_64 1/5
验证 : dovecot-1:2.2.36-5.el8.x86_64 2/5
验证 : dovecot-mysql-1:2.2.36-5.el8.x86_64 3/5
验证 : dovecot-pgsql-1:2.2.36-5.el8.x86_64 4/5
验证 : libpq-10.5-1.el8.x86_64 5/5
Installed products updated.
已安装:
dovecot-1:2.2.36-5.el8.x86_64 dovecot-mysql-1:2.2.36-5.el8.x86_64 dovecot-pgsql-1:2.2.36-5.el8.x86_64
clucene-core-2.3.3.4-31.20130812.e8e3d20git.el8.x86_64 libpq-10.5-1.el8.x86_64
完毕！
[root@classroom postfix]#

复制代码

/etc/dovecot/dovecot.conf:

login_trusted_networks = 192.168.0.0/24

复制代码

/etc/dovecot/conf.d:
10-mail.conf:
mail_location = mbox:~/mail:INBOX=/var/mail/%u

[oracle@classroom ~]$ mkdir mail

复制代码

2. oracle数据内部对外发邮件测试：

DECLARE
c utl_smtp.connection;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
END;
BEGIN
c := utl_smtp.open_connection('classroom.example.com');
utl_smtp.helo(c, 'classroom.example.com');
utl_smtp.mail(c, 'oracle@classroom.example.com');
utl_smtp.rcpt(c, 'oracle@classroom.example.com');
utl_smtp.open_data(c);
send_header('From', '"Sender" <oracle@classroom.example.com>');
send_header('To', '"orcl dba" <oracle@classroom.example.com>');
send_header('Subject', 'Hello');
utl_smtp.write_data(c, utl_tcp.CRLF || 'FGA!');
utl_smtp.close_data(c);
utl_smtp.quit(c);
END;
/

复制代码

create or replace procedure procsmtp ( object_schema VARCHAR2, object_name VARCHAR2, policy_name
VARCHAR2 )
is
c utl_smtp.connection;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
END;
BEGIN
c := utl_smtp.open_connection('classroom.example.com');
utl_smtp.helo(c, 'classroom.example.com');
utl_smtp.mail(c, 'oracle@classroom.example.com');
utl_smtp.rcpt(c, 'oracle@classroom.example.com');
utl_smtp.open_data(c);
send_header('From', '"Sender" <oracle@classroom.example.com>');
send_header('To', '"orcl dba" <oracle@classroom.example.com>');
send_header('Subject', 'FGA');
utl_smtp.write_data(c, utl_tcp.CRLF ||sys_context('userenv','os_user')||' '||
to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' '||
sys_context('userenv','ip_address') ||' '||object_schema||' '||object_name||' '||policy_name
);
utl_smtp.close_data(c);
utl_smtp.quit(c);
END;
/
begin
procsmtp('HR','T062AD11_B','POLICY1');
end;
/

复制代码

3. 定FGA策略:

begin
dbms_fga.ADD_POLICY (
OBJECT_SCHEMA=>'HR',
OBJECT_NAME=>'t062ad11_b',
POLICY_NAME=>'policy1',
AUDIT_CONDITION=>'empno=100',
AUDIT_COLUMN=>'salary',
HANDLER_SCHEMA=>'SYS',
HANDLER_MODULE=>'PROCSMTP',
ENABLE=>true,
STATEMENT_TYPES=>'SELECT,UPDATE',
AUDIT_TRAIL=>DBMS_FGA.XML+DBMS_FGA.EXTENDED);
end;
/

复制代码

select * from dba_audit_policies;

复制代码

HR T062AD11_B SYS POLICY1 empno=100 SALARY SYS PROCSMTP YES YES NO YES NO XML+EXTENDED ANY_COLUMNS

select * from dba_common_audit_trail order by 6 desc ;select * from dba_common_audit_trail order by 6 desc ;

复制代码