40.实施数据库审计
1.DBA负责的安全和审计工作及标准
1.1.标准审计
1.2.启用审计，并查找结果
1.3.指定审计选项
1.4默认审计（Oracle12c）
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 4 17:26:12 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
sys@ORCL11G 2023-03-04 17:26:12> audit create table by scott;
Audit succeeded.
Elapsed: 00:00:00.02
sys@ORCL11G 2023-03-04 17:26:21> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ scott
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 4 17:26:24 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
scott@ORCL11G 2023-03-04 17:26:24> create table test as select *from emp;
Table created.
Elapsed: 00:00:00.03
scott@ORCL11G 2023-03-04 17:26:36> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 4 17:26:41 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
sys@ORCL11G 2023-03-04 17:26:41> select count(*) from dba_common_audit_trail;
COUNT(*)
----------
6
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-04 17:26:46> select * from dba_common_audit_trail;
AUDIT_TYPE SESSION_ID PROXY_SESSIONID STATEMENTID ENTRYID EXTENDED_TIMESTAMP GLOBAL_UID DB_USER CLIENT_ID ECONTEXT_ID
---------------------- ---------- --------------- ----------- ---------- --------------------------------------------------------------------------- -------------------------------- ------------------------------ ---------------------------------------------------------------- ----------------------------------------------------------------
EXT_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OS_USER USERHOST OS_PROCESS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------
TERMINAL INSTANCE_NUMBER OBJECT_SCHEMA OBJECT_NAME POLICY_NAME NEW_OWNER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
NEW_NAME ACTION STATEMENT_TYPE AUDIT_OPTION TRANSACTIONID RETURNCODE SCN
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------------------------- ---------------------------------------- ---------------- ---------- ----------
COMMENT_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_BIND
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OBJ_PRIVILEGE SYS_PRIVILEGE ADMI OS_PRIV GRANTEE PRIV_USED SES_ACTIONS LOGOFF_TIME LOGOFF_LREAD LOGOFF_PREAD LOGOFF_LWRITE LOGOFF_DLOCK SESSION_CPU OBJ_EDITION_NAME DBID
---------------- ---------------------------------------- ---- ------- ------------------------------ ---------------------------------------- ------------------- ------------------- ------------ ------------ ------------- ---------------------------------------- ----------- ------------------------------ ----------
Standard Audit 220029 1 1 04-MAR-23 05.25.58.676288 PM +08:00 SCOTT
oracle yuanzj.com 7717
pts/2 0
100 LOGON 0000000000000000 0
Authenticated by: DATABASE
NONE CREATE SESSION 1235547844
Standard Audit 220029 1 2 04-MAR-23 05.26.09.235439 PM +08:00 SCOTT
oracle yuanzj.com 7717
pts/2 0
101 LOGOFF 0
NONE 2023-03-04 17:26:09 1718 39 61 0 6 1235547844
Standard Audit 220031 1 1 04-MAR-23 05.26.24.705616 PM +08:00 SCOTT
oracle yuanzj.com 7770
pts/2 0
100 LOGON 0000000000000000 0
Authenticated by: DATABASE
NONE CREATE SESSION 1235547844
Standard Audit 220031 19 2 04-MAR-23 05.26.36.572505 PM +08:00 SCOTT
oracle yuanzj.com 7770
pts/2 0 SCOTT TEST
1 CREATE TABLE 020015008F030000 0 1446509
NONE CREATE TABLE 1235547844
Standard Audit 220031 19 3 04-MAR-23 05.26.36.572591 PM +08:00 SCOTT
oracle yuanzj.com 7770
pts/2 0 SCOTT EMP
3 SELECT 020015008F030000 0 1446509
NONE 1235547844
Standard Audit 220031 1 4 04-MAR-23 05.26.38.630869 PM +08:00 SCOTT
oracle yuanzj.com 7770
pts/2 0
101 LOGOFF 0
NONE 2023-03-04 17:26:38 310 1 137 0 2 1235547844
6 rows selected.
Elapsed: 00:00:00.01
sys@ORCL11G 2023-03-04 17:26:53> truncate table aud$;
Table truncated.
Elapsed: 00:00:00.01
sys@ORCL11G 2023-03-04 17:27:52> select * from dba_common_audit_trail;
no rows selected
Elapsed: 00:00:00.011.5.查询审计视图
sys@ORCL11G 2023-03-04 17:27:55> select * from tab where tname like '%AUDIT%';
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ALL_AUDIT_POLICIES VIEW
ALL_AUDIT_POLICY_COLUMNS VIEW
ALL_DEF_AUDIT_OPTS VIEW
ALL_REPAUDIT_ATTRIBUTE VIEW
ALL_REPAUDIT_COLUMN VIEW
AUDIT$ TABLE
AUDIT_ACTIONS TABLE
DBA_AUDIT_EXISTS VIEW
DBA_AUDIT_MGMT_CLEANUP_JOBS VIEW
DBA_AUDIT_MGMT_CLEAN_EVENTS VIEW
DBA_AUDIT_MGMT_CONFIG_PARAMS VIEW
DBA_AUDIT_MGMT_LAST_ARCH_TS VIEW
DBA_AUDIT_OBJECT VIEW
DBA_AUDIT_POLICIES VIEW
DBA_AUDIT_POLICY_COLUMNS VIEW
DBA_AUDIT_SESSION VIEW
DBA_AUDIT_STATEMENT VIEW
DBA_AUDIT_TRAIL VIEW
DBA_COMMON_AUDIT_TRAIL VIEW
DBA_FGA_AUDIT_TRAIL VIEW
DBA_OBJ_AUDIT_OPTS VIEW
DBA_PRIV_AUDIT_OPTS VIEW
DBA_REPAUDIT_ATTRIBUTE VIEW
DBA_REPAUDIT_COLUMN VIEW
DBA_STMT_AUDIT_OPTS VIEW
GV_$XML_AUDIT_TRAIL VIEW
KU$_10_1_AUDIT_VIEW VIEW
KU$_AUDIT_DEFAULT_VIEW VIEW
KU$_AUDIT_OBJ_BASE_VIEW VIEW
KU$_AUDIT_OBJ_VIEW VIEW
KU$_AUDIT_VIEW VIEW
KU$_PROCDEPOBJ_AUDIT_VIEW VIEW
KU$_PROCOBJ_AUDIT_VIEW VIEW
KU$_PROC_AUDIT_VIEW VIEW
SM$AUDIT_CONFIG VIEW
STMT_AUDIT_OPTION_MAP TABLE
USER_AUDIT_OBJECT VIEW
USER_AUDIT_POLICIES VIEW
USER_AUDIT_POLICY_COLUMNS VIEW
USER_AUDIT_SESSION VIEW
USER_AUDIT_STATEMENT VIEW
USER_AUDIT_TRAIL VIEW
USER_OBJ_AUDIT_OPTS VIEW
USER_REPAUDIT_ATTRIBUTE VIEW
USER_REPAUDIT_COLUMN VIEW
V_$XML_AUDIT_TRAIL VIEW
--取消审计
noaudit create table by scott;
--如果审计没有加用户，取消时候也不要加用户
noaudit create table2.FGA策略的SYSDBA审计
--关闭审计
alter system set audit_trail=none scope=spfile;
--重启数据库
shu immmediate
startup
2.1细粒度审计
2.2细粒度审计配置过程
--查看dbms_fga包
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 22:24:05 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-04 22:24:05> desc dbms_fga
PROCEDURE ADD_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
AUDIT_CONDITION VARCHAR2 IN DEFAULT
AUDIT_COLUMN VARCHAR2 IN DEFAULT
HANDLER_SCHEMA VARCHAR2 IN DEFAULT
HANDLER_MODULE VARCHAR2 IN DEFAULT
ENABLE BOOLEAN IN DEFAULT
STATEMENT_TYPES VARCHAR2 IN DEFAULT
AUDIT_TRAIL BINARY_INTEGER IN DEFAULT
AUDIT_COLUMN_OPTS BINARY_INTEGER IN DEFAULT
PROCEDURE DISABLE_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE DROP_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE ENABLE_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
ENABLE BOOLEAN IN DEFAULT
--配置细粒度审计
sys@ORCL10G 2023-03-04 22:26:05>
sys@ORCL10G 2023-03-04 22:26:05> exec dbms_fga.add_policy('SCOTT','EMP','FGA1','deptno=10','SAL',enable=>true)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
sys@ORCL10G 2023-03-04 22:28:34> select * from tab where tname like '%POLIC%';
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DIR$VICTIM_POLICY TABLE
GV_$VPD_POLICY VIEW
V_$VPD_POLICY VIEW
DBA_POLICIES VIEW
ALL_POLICIES VIEW
USER_POLICIES VIEW
DBA_POLICY_GROUPS VIEW
ALL_POLICY_GROUPS VIEW
USER_POLICY_GROUPS VIEW
DBA_POLICY_CONTEXTS VIEW
ALL_POLICY_CONTEXTS VIEW
USER_POLICY_CONTEXTS VIEW
DBA_AUDIT_POLICIES VIEW
DBA_AUDIT_POLICY_COLUMNS VIEW
ALL_AUDIT_POLICIES VIEW
ALL_AUDIT_POLICY_COLUMNS VIEW
USER_AUDIT_POLICIES VIEW
USER_AUDIT_POLICY_COLUMNS VIEW
KU$_FGA_POLICY_VIEW VIEW
KU$_RLS_POLICY_VIEW VIEW
JAVA$POLICY$ TABLE
DBA_JAVA_POLICY VIEW
USER_JAVA_POLICY VIEW
JAVA$POLICY$SHARED$TABLE TABLE
24 rows selected.
Elapsed: 00:00:00.01
--查看配置结果
sys@ORCL10G 2023-03-04 22:44:17> select object_schema,object_name, policy_name,enabled from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENA
------------------------------ ------------------------------ ------------------------------ ---
SCOTT EMP FGA1 YES
--测试审计
scott@ORCL10G 2023-03-04 22:36:18> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
Elapsed: 00:00:00.01
scott@ORCL10G 2023-03-04 22:36:56> select * from emp where deptno = 10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
Elapsed: 00:00:00.00
--查看审计结果
sys@ORCL10G 2023-03-04 22:36:21> select count(*) from dba_fga_audit_trail;
COUNT(*)
----------
1
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-04 22:36:59> select count(*) from dba_fga_audit_trail;
COUNT(*)
----------
2
Elapsed: 00:00:00.00
--清空审计结果
sys@ORCL10G 2023-03-04 22:35:24> truncate table fga_log$;
Table truncated.
Elapsed: 00:00:00.06
sys@ORCL10G 2023-03-04 22:44:17> select object_schema,object_name, policy_name,enabled from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENA
------------------------------ ------------------------------ ------------------------------ ---
SCOTT EMP FGA1 YES
Elapsed: 00:00:00.00
--禁用审计
sys@ORCL10G 2023-03-04 22:44:43> exec dbms_fga.disable_policy('SCOTT','EMP','FGA1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
--删除审计
sys@ORCL10G 2023-03-04 22:44:52> exec dbms_fga.drop_policy('SCOTT','EMP','FGA1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01 
NAME_COL_PLUS_SHOW_PARAM TYPE
-------------------------------------------------------------------------------- -----------
VALUE_COL_PLUS_SHOW_PARAM
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
audit_file_dest string
/u01/app/ora10g/admin/orcl10g/adump
audit_sys_operations boolean
FALSE
audit_syslog_level string
audit_trail
--打开系统审计(不要打开系统审计)
alter system set audit_sys_operations= true scope=spfile
