文档课题:删除审计表aud$,用户无法连接数据库的测试.标签:13,aud,删除,数据库,NUMBER,leo,sys,VARCHAR2 From: https://blog.51cto.com/u_12991611/5754584
数据库:oracle 11.2.0.4 64位
系统:centos 7.9 64位
环境:单实例
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 rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
leo@ORCL 2022-10-13 16:04:51>
说明:用户leo正常连接.
sys@ORCL 13-OCT-22> select owner,segment_name,segment_type,tablespace_name from dba_segments where segment_name='AUD$'
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
---------- --------------- ------------------ ------------------------------
SYS AUD$ TABLE AUD_TBS
sys@ORCL 13-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@ORCL 13-OCT-22> drop table aud$;
Table dropped.
leo@ORCL 2022-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
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options (with complications)
[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 rights reserved.
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@ORCL 13-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@ORCL 13-OCT-22> grant delete on aud$ to DELETE_CATALOG_ROLE;
Grant succeeded.
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 rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
leo@ORCL 2022-10-13 16:09:45>
结论:在开启审计的场景中,aud$审计表被删除后非sys用户登陆会出现异常,重建后恢复正常.
参考网址:http://blog.itpub.net/31392094/viewspace-2127107