有个很难搞的问题

Question

有个数据库，里面有个资源表某名奇妙的丢失数据，查看了v$sql，用select 语句（其他的管理工具没用过，如logminer，审计看了 也一知半解，如果有好的查看系统日志的方法 和我说说，谢谢了），言归正传吧，看了 里面 有每隔几分钟 有一条delete 语句我用 select * from dba_jobs_running  查看正在运行的job 找不到一job，然后排除了业务系统上的误操作，请问有什么方法 可以 不让他delete ，我想回收他的权限， REVOKE DELETE  ON  （TABLE） FROM  PUBLIC ；用sysdba 登录，却提示没有权限，而且revoke 权限 不利于 以后的管理 ，希望大家帮帮忙 提出一个好的解决方法 谢谢了！

Answer 1

好乱？我简单的说说吧，我希望哪位高手可以帮帮我分析一下 这个表随机 delete 语句产生的原因，最好可以帮我拿出一个解决方案 真的 不胜感谢了，为了 这个问题 最近我都头疼死了 唉！

Answer 2

要不先加个触发器试试，不允许delete

Answer 3

察看一下是哪个用户在执行这个操作!
追到用户大概就能确定原因了!
v$logmnr_logs
v$logmnr_dictionary
v$logmnr_parameters
v$logmnr_contents

Answer 4

对这张表的删除操作暂时开启一下fga吧（虽然牺牲了点代价），然后根据时间和操作来找到底谁做了吧
举个简单例子，楼主自己改改，改成自己需要的样子吧SQL> show user                                                                          
USER is "SYS"                                                                           
SQL> begin                                                                              
  2    dbms_fga.add_policy                                                              
  3                 ( object_schema => 'HR',                                            
  4                   object_name => 'TEST1',                                           
  5                   policy_name => 'Example',                                         
  6                   audit_condition => '1=1',                                         
  7                   STATEMENT_TYPES => 'DELETE' );                                    
  8  end;                                                                               
  9  /                                                                                  PL/SQL procedure successfully completed.SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options                                     
SQL> conn hr/hr                                                                         
Connected.                                                                              
SQL> select * from test1;                                                               ID         NAME
---------- --------------------
1.2.1                          
1.1                            
1.2                            
1.10                           
1.12                           
1.1.2                          
1.1.1                          
1.1.12                         
222                            9 rows selected.SQL> delete from test1 where id = '222';1 row deleted.SQL> commit;Commit complete.SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options                                     
SQL> conn / as sysdba                                                                   
Connected.                      
SQL> ed                                                                   
Wrote file afiedt.buf                                                       1  create or replace procedure show_aud
  2    as                                
  3    begin                             
  4      for rec in ( select db_user,    
  5                          client_id,  
  6                          object_schema,
  7                          object_name,  
  8                          extended_timestamp,
  9                          sql_text,          
 10                          statementid        
 11                     from dba_common_audit_trail
 12                    group by db_user,           
 13                             statementid,       
 14                             sql_text,          
 15                             object_schema,     
 16                             object_name,       
 17                             client_id,         
 18                             extended_timestamp 
 19                    order by extended_timestamp asc)
 20      loop                                          
 21        dbms_output.put_line ( 'Who: ' || rec.db_user );
 22        dbms_output.put_line ( 'What: '                 
 23                               || rec.object_schema     
 24                               || '.'                   
 25                               || rec.object_name );    
 26        dbms_output.put_line ( 'Where: ' || rec.client_id );
 27        dbms_output.put_line ( 'When: '                     
 28                               || to_char                   
 29                                       (rec.extended_timestamp,
 30                                        'Mon-DD HH24:MI') );   
 31        dbms_output.put_line ( 'How: '                         
 32                               || rec.sql_text );              
 33        dbms_output.put_line                                   
 34          ( '--------------------- End of Record ----------------------');
 35      end loop;
 36* end;
SQL> /Procedure created.SQL> exec show_audPL/SQL procedure successfully completed.SQL> set serveroutput on
SQL> exec show_aud
Who: HR
What: HR.TEST1
Where:
When: Mar-12 11:12
How: delete from test1 where id = '222'
--------------------- End of Record ----------------------PL/SQL procedure successfully completed.SQL>

Answer 5

简单来说，你可以这样：
1. select * from dba_jobs，查出哪些jobs含有delete监控表的语句，如没有的话，转2
2. 建立一个测试表TA，对要监控的表建立delete触发器，利用sys_context函数获得客户端信息，如：
create or replace trigger D_T
  before delete on 监控表  
  for each row
declare
  -- local variables here
  v_ip varchar2(100);
begin
  select sys_context('userenv','ip_address') into v_ip from dual;
  insert into TA(A,B) values(v_ip,sysdate);
end D_T;

Answer 6

楼主想要回收的是对象权限,如果没有授此权限就不用回收,其实更主要的是系统权限,如DBA具有任务数据库的操作权限，另外DELETE ANY TABLE权限也会删除数据库的任何数据，楼主试试回收这两个权限REVOKE DBA FROM 某用户 或 PUBLIC;REVOKE DELETE ANY TABLE FROM 某用户 或 PUBLIC;