文档课题：删除审计表aud$，用户无法连接数据库的测试.

数据库：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