重庆思庄Oracle、Redhat认证学习论坛
标题: 删除审计表aud$，用户无法连接数据库的测试 [打印本页]
作者: denglj 时间: 2022-11-28 19:22
标题: 删除审计表aud$，用户无法连接数据库的测试
文档课题：删除审计表aud$，用户无法连接数据库的测试.
数据库：oracle 11.2.0.464位
系统：centos 7.964位
环境：单实例
1、理论知识数据库开启审计的情况下，aud$会记录非sys用户的登陆登出记录.若aud$表被不小心删除,虽然数据库能正常打开，sys用户也能登录到数据库，并能查询所有数据，但除sys用户外的其它用户均无法正常登陆登出数据库.
2、场景模拟[oracle@liujun~]$ sqlplus leo/leo@orcl
SQL*Plus:Release 11.2.0.4.0 Production on Thu Oct 13 16:04:51 2022
Copyright(c) 1982, 2013, Oracle. All rightsreserved.
Connectedto:
OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With thePartitioning, OLAP, Data Mining and Real Application Testing options
leo@ORCL2022-10-13 16:04:51>
说明：用户leo正常连接.
sys@ORCL13-OCT-22> select owner,segment_name,segment_type,tablespace_name fromdba_segments where segment_name='AUD$'
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------- ------------------ ------------------------------
SYS AUD$ TABLE AUD_TBS
sys@ORCL13-OCT-22> show parameter audi
NAME TYPE VALUE
----------------------------------------------- ------------------------------
audit_file_dest string /u01/app/oracle/app/oracle/adm
in/orcl/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
说明：数据库审计级别为DB.
sys@ORCL13-OCT-22> drop table aud$;
Tabledropped.
leo@ORCL2022-10-13 16:07:03> exit
ERROR:
ORA-00600:internal error code, arguments: [ktcrab: caller passed invalid xcb], [4],[0x0E7971A28], [0x000000000], [2], [1802], [], [], [], [], [], []
ORA-00604:error occurred at recursive SQL level 1
ORA-00942:table or view does not exist
Disconnectedfrom Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction
With thePartitioning, OLAP, Data Mining and Real Application Testing options (withcomplications)
[oracle@liujun~]$ sqlplus leo/leo@orcl
SQL*Plus:Release 11.2.0.4.0 Production on Thu Oct 13 16:07:30 2022
Copyright(c) 1982, 2013, Oracle. All rightsreserved.
ERROR:
ORA-00604:error occurred at recursive SQL level 1
ORA-00942:table or view does not exist
ORA-02002:error while writing to audit trail
ORA-00604:error occurred at recursive SQL level 1
ORA-00942:table or view does not exist
说明：异常场景被成功模拟出来，用户leo在aud$表被删除的情况下，登出登陆均出现异常.
3、解决方案按如下语句，sys用户重建审计表aud$.
sys@ORCL13-OCT-22> create table AUD$
(
sessionid NUMBER not null,
entryid NUMBER not null,
statement NUMBER not null,
timestamp# DATE,
userid VARCHAR2(30),
userhost VARCHAR2(128),
terminal VARCHAR2(255),
action# NUMBER not null,
returncode NUMBER not null,
obj$creator VARCHAR2(30),
obj$name VARCHAR2(128),
auth$privileges VARCHAR2(16),
auth$grantee VARCHAR2(30),
new$owner VARCHAR2(30),
new$name VARCHAR2(128),
ses$actions VARCHAR2(19),
ses$tid NUMBER,
logoff$lread NUMBER,
logoff$pread NUMBER,
logoff$lwrite NUMBER,
logoff$dead NUMBER,
logoff$time DATE,
comment$text VARCHAR2(4000),
clientid VARCHAR2(64),
spare1 VARCHAR2(255),
spare2 NUMBER,
obj$label RAW(255),
ses$label RAW(255),
priv$used NUMBER,
sessioncpu NUMBER,
ntimestamp# TIMESTAMP(6),
proxy$sid NUMBER,
user$guid VARCHAR2(32),
instance# NUMBER,
process# VARCHAR2(16),
xid RAW(8),
auditid VARCHAR2(64),
scn NUMBER,
dbid NUMBER,
sqlbind CLOB,
sqltext CLOB,
obj$edition VARCHAR2(30)
)
tablespace AUD_TBS
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
sys@ORCL13-OCT-22> grant delete on aud$ to DELETE_CATALOG_ROLE;
Grantsucceeded.
4、验证[oracle@liujun~]$ sqlplus leo/leo@orcl
SQL*Plus:Release 11.2.0.4.0 Production on Thu Oct 13 16:09:45 2022
Copyright(c) 1982, 2013, Oracle. All rightsreserved.
Connectedto:
OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With thePartitioning, OLAP, Data Mining and Real Application Testing options
leo@ORCL2022-10-13 16:09:45>
结论：在开启审计的场景中，aud$审计表被删除后非sys用户登陆会出现异常，重建后恢复正常.
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) |
Powered by Discuz! X3.2 |