Oracle FGA审核

最新推荐文章于 2024-01-21 10:04:47 发布

cldh1492

最新推荐文章于 2024-01-21 10:04:47 发布

阅读量319

点赞数

文章标签：数据库

FGA审核（fine-grained auditing）即细粒度审核。标准的数据库审核可以捕获对某个表的所有访问，但是可能只有某些行包含了用户关心的敏感信息，为了查找少量的重要信息，我们可能需要筛选大量的审核记录。而细粒度审核可以配置只在访问特定行或特定行的特定列时生成审核记录，并可配置审核条件满足时运行一个PL/SQL代码，以完成更加复杂的监控操作。

一、FGA审核的基本方法

程序包dbms_fga用来创建、使能、删除FGA审核策略

SQL> desc dbms_fga

Element Type

-------------- ---------

EXTENDED CONSTANT

DB CONSTANT

DB_EXTENDED CONSTANT

XML CONSTANT

ALL_COLUMNS CONSTANT

ANY_COLUMNS CONSTANT

ADD_POLICY PROCEDURE

DROP_POLICY PROCEDURE

ENABLE_POLICY PROCEDURE

DISABLE_POLICY PROCEDURE

程序包中的过程add_policy用来创建一个审核策略

SQL> desc dbms_fga.add_policy

Parameter Type Mode Default?

----------------- -------------- ---- --------

OBJECT_SCHEMA VARCHAR2 IN Y 待审核对象的用户名，默认为创建FGA审核策略的用户

OBJECT_NAME VARCHAR2 IN 待审核表的名称

POLICY_NAME VARCHAR2 IN 每一个审核策略都应有一个策略名

AUDIT_CONDITION VARCHAR2 IN Y 确定审核行的条件表达式，为NULL时对任何行的访问都纳入审核

AUDIT_COLUMN VARCHAR2 IN Y 待审核列的列表，为NULL时对任何列的访问都纳入审核

HANDLER_SCHEMA VARCHAR2 IN Y 达到审核条件时所运行存储过程的用户名，默认为创建策略的用户

HANDLER_MODULE VARCHAR2 IN Y 达到审核条件时所运行的PL/SQL存储过程

ENABLE BOOLEAN IN Y 默认为true，策略自动激活，用disable_policy可禁用策略，若为false，则需使用enable_policy激活策略

STATEMENT_TYPES VARCHAR2 IN Y 定义要审核的语句类型，如select、insert、update或delete中的一个或多个，默认只审核select

AUDIT_TRAIL BINARY_INTEGER IN Y 是否将执行的SQL语句及其绑定变量写入FGA审核跟踪，默认为写入

AUDIT_COLUMN_OPTS BINARY_INTEGER IN Y 当执行语句涉及到audit_column参数中指定的任何列或所有列时，是否进行审核，

包括dbms_fga.any_columns（默认）或dbms_fga.all_columns

POLICY_OWNER VARCHAR2 IN Y 策略所有者

过程drop_policy用来删除一个审核策略

SQL> desc dbms_fga.drop_policy

Parameter Type Mode Default?

------------- -------- ---- --------

OBJECT_SCHEMA VARCHAR2 IN Y

OBJECT_NAME VARCHAR2 IN

POLICY_NAME VARCHAR2 IN

审核结果通过视图dba_fga_audit_trail可以查看，审核完毕后也可以直接将视图记录删除。

这里需要特别说明的是，FGA审核是独立于标准数据库审核的，也就是说，即便标准数据库审核未打开，audit_trail参数设置为none，FGA审核也是有效可行的。这给我们带来了一个方便，不用重启实例更改参数，就可以实施FGA审核。

以下是对用户的某个表添加查询和DML操作的审核，且暂时不启用

begin

dbms_fga.add_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT',

audit_condition => null,

audit_column => null,

enable => false,

statement_types => 'select,insert,update,delete');

end;

以下还可以在审核条件满足时执行一个存储过程，以实现更复杂的监控

begin

dbms_fga.add_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT',

audit_condition => null,

audit_column => null,

handler_schema => 'SYS',

handler_module => 'LLN_AUDIT_PROC',

enable => false,

statement_types => 'select,insert,update,delete');

end;

启用审核

begin

dbms_fga.enable_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT',

enable => true);

end;

禁用审核

begin

dbms_fga.enable_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT',

enable => false);

end;

删除审核

begin

dbms_fga.drop_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT');

end;

查看审核结果

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a60

col sql_bind for a60

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, sql_bind, statement_type from dba_fga_audit_trail order by timestamp desc;

结果中的sesssion_id就是视图v$session中的audsid，由此可以对应出具体的会话信息。

清除FGA审核的历史记录

delete from dba_fga_audit_trail;

commit;

二、FGA审核实验

1、没有附加存储过程的审核实验：

1）用户hr有一张员工表employees，其中字段salary记录了每位员工的工资信息

2）创建一个FGA审核策略，捕获包含对employees表salary列的所有DML语句，并加入条件只对行的所属部门ID包含80部门的纳入审核

begin

dbms_fga.add_policy(object_schema => 'HR',

object_name => 'EMPLOYEES',

policy_name => 'MY_POLICY',

audit_condition => 'department_id = 80',

audit_column => 'SALARY',

statement_types => 'select,insert,update,delete');

end;

3）测试对表的审核操作

测试之前可以先清除FGA审核的历史记录

delete from dba_fga_audit_trail;

commit;

非sys用户登录，查看员工表的所有信息

select * from hr.employees;

查看审核结果

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a50

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, statement_type from dba_fga_audit_trail;

SESSION_ID TIMESTAMP DB_USER OS_USER USERHOST OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT STATEME

---------- ------------------- ---------- -------------------- -------------------- --------------- -------------------- ------------------------------ -------------------------------------------------- -------

441434 2017-09-20 11:54:53 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees SELECT

由于查询包含了满足审核条件的记录，因此该语句会被纳入审核。

查看30部门的员工信息

select * from hr.employees where department_id=30;

查看审核结果

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a50

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, statement_type from dba_fga_audit_trail;

SESSION_ID TIMESTAMP DB_USER OS_USER USERHOST OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT STATEMENT_TYPE

---------- -------------------- ---------- ------------------------------ -------------------- --------------- --------------- --------------- ------------------------------ --------------

452718 2015/9/3 18:28:39 HR VM-2008-090\Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees SELECT

由于查询非针对80部门，因此不会纳入审核。

查看80部门员工的所有信息

select * from hr.employees where department_id=80;

查看审核结果

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a50

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, statement_type from dba_fga_audit_trail;

SESSION_ID TIMESTAMP DB_USER OS_USER USERHOST OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT STATEME

441434 2017-09-20 11:54:53 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees SELECT

441434 2017-09-20 12:00:51 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees where department_id=80 SELECT

由于查询包含了SALARY列和指定的80部门条件，故该语句加入审核。

查看80部门员工的姓名和入职日期等信息

select employee_id, first_name, last_name, hire_date from hr.employees where department_id=80;

查看审核结果

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a50

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, statement_type from dba_fga_audit_trail;

SESSION_ID TIMESTAMP DB_USER OS_USER USERHOST OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT STATEMENT_TYPE

---------- -------------------- ---------- ------------------------------ -------------------- --------------- --------------- --------------- -------------------------------------------------- --------------

452718 2015/9/3 18:28:39 HR VM-2008-090\Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees SELECT

452718 2015/9/3 20:48:20 HR VM-2008-090\Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees where department_id=80 SELECT

由于该查询未包含SALARY列，故不会纳入审核。

更新操作

update hr.employees set salary=15000 where employee_id=145;

该更新满足审核条件，该ID员工属于80部门，且针对的是SALARY列的更新，因此被纳入审核

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a60

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, statement_type from dba_fga_audit_trail;

SESSION_ID TIMESTAMP DB_USER OS_USER USERHOST OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT STATEME

441434 2017-09-20 11:54:53 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees SELECT

441434 2017-09-20 12:00:51 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees where department_id=80 SELECT

441434 2017-09-20 12:04:29 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY update hr.employees set salary=15000 where employee_id=145 UPDATE

无论DML操作最终是否被提交，审核记录都将存在而不会被回滚掉。

更新另一位员工的SALARY

update hr.employees set salary=3000 where employee_id=198;

查看审核结果

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a60

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, statement_type from dba_fga_audit_trail;

SESSION_ID TIMESTAMP DB_USER OS_USER USERHOST OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT STATEME

441434 2017-09-20 11:54:53 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees SELECT

441434 2017-09-20 12:00:51 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees where department_id=80 SELECT

441434 2017-09-20 12:04:29 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY update hr.employees set salary=15000 where employee_id=145 UPDATE

由于该员工不属于80部门，因此不会纳入审核。

4）审核策略的删除

删除审核策略，需要指定审核对象和策略名

begin

dbms_fga.drop_policy(object_schema => 'HR',

object_name => 'EMPLOYEES',

policy_name => 'MY_POLICY');

end;

清除FGA审核的历史记录

delete from dba_fga_audit_trail;

commit;

2、附加存储过程的审核实验：

1）创建自己的会话审核跟踪表

create table sys.lln_audit_trail(audsid number,

username varchar2(30),

machine varchar2(64),

ip varchar2(15),

osuser varchar2(30),

program varchar2(64),

logon_time date,

sid number,

serial# number,

pid number,

spid varchar2(12),

action varchar2(32),

sql_id varchar2(13),

object_schema varchar2(30),

object_name varchar2(30),

policy_name varchar2(30))

tablespace rmes;

2）创建存储过程，配合FGA审核执行会话跟踪

create or replace procedure sys.lln_audit_proc(object_schema varchar2,

object_name varchar2,

policy_name varchar2) is

-- 配合FGA审核执行会话跟踪

my_audsid number;

my_username varchar2(30);

my_machine varchar2(64);

my_osuser varchar2(30);

my_program varchar2(64);

my_logon_time date;

my_sid number;

my_serial# number;

my_pid number;

my_spid varchar2(12);

my_action varchar2(32);

my_sql_id varchar2(13);

begin

-- 查找会话信息

select s.audsid,

s.username,

s.machine,

s.osuser,

s.program,

s.logon_time,

s.sid,

s.serial#,

p.pid,

p.spid,

s.action,

s.sql_id

into my_audsid,

my_username,

my_machine,

my_osuser,

my_program,

my_logon_time,

my_sid,

my_serial#,

my_pid,

my_spid,

my_action,

my_sql_id

from v$session s, v$process p

where s.paddr = p.addr

and s.audsid = sys_context('userenv', 'sessionid');

-- 记录会话信息，这里是自动提交的，不可以加显示提交命令，否则审核的操作将报内部错误

insert into lln_audit_trail

(audsid,

username,

machine,

ip,

osuser,

program,

logon_time,

sid,

serial#,

pid,

spid,

action,

sql_id,

object_schema,

object_name,

policy_name)

values

(my_audsid,

my_username, --ora_login_user

my_machine,

sys_context('userenv', 'ip_address'),

my_osuser,

my_program,

my_logon_time, --sysdate

my_sid,

my_serial#,

my_pid,

my_spid,

my_action,

my_sql_id,

object_schema,

object_name,

policy_name);

end;

3）创建和使能FGA审核策略

begin

dbms_fga.add_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT',

audit_condition => null,

audit_column => null,

handler_schema => 'SYS',

handler_module => 'LLN_AUDIT_PROC',

enable => false,

statement_types => 'select,insert,update,delete');

end;

begin

dbms_fga.enable_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT',

enable => true);

end;

4）开启一个会话，对审核的表执行查询和DML测试操作

5）查询审核和跟踪结果

审核结果包含了在指定表上执行的SQL和绑定变量等信息

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a60

col sql_bind for a60

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, sql_bind, statement_type from dba_fga_audit_trail order by timestamp desc;