客户有个需求，查询数据库中所有包含内部、内部资料、秘密、机密、保密、密、等等。。

等字段的表和字段。





创建如下存储过程，查询当前用户（查open状态的生产用户即可sys和system之类的系统用户没有必要，

查询语句
select username,account_status  from dba_users where account_status=
'OPEN';）

下有哪些表，遍历这些表的所有字符类型的列，查出每个列的值对比有没有和我们输入的敏感字段相符的，有的话记录表明和列名到 tmptable_zyr。





1 创建结果存储表


create table tmptable_zyr (tab_name varchar2(
300) , col_name varchar2(
300));





2 创建存储过程


CREATE OR REPLACE procedure QUERY_KEY

(

 keyword in varchar2

 )

 as

  v_SQLStatement varchar2(
4000);

  
v_startRecord Number;


begin

  FOR reInfo IN (
select table_name from user_tables) LOOP 

    FOR colInfo IN (SELECT column_name, DATA_TYPE

                      FROM user_tab_cols

                     WHERE table_name = reInfo.table_name) LOOP

      IF (colInfo.DATA_TYPE like  
'%CHAR%'  ) THEN     

        v_SQLStatement := 
'select count(*) from ' || reInfo.table_name ||

                          
' where ' || colInfo.column_name || 
' like '
'%'||

                          keyword||
'%'
''; 

        
execute immediate v_SQLStatement

          into v_startRecord;      

        
IF (v_startRecord > 
0) THEN        

          insert into tmptable_zyr values (reInfo.table_name,colInfo.column_name);        

          commit;        

        
END IF;      

      
END IF;

    
END LOOP; 

  
END LOOP;


END QUERY_KEY;





3 执行存储过程


call QUERY_KEY(
'保密');





4 查询是否有敏感数据


select * from tmptable_zyr;





 
col TAB_NAME format a30

 col COL_NAME format a30


select * from tmptable_zyr;





























**************************以下是本机测试情况**********************************************





测试样例：





创建测试表


create table ttt (name varchar2(
30));





插入数据


insert into ttt values(
'保密');

commit;





确认数据


select * from ttt;





进行敏感查询






call QUERY_KEY(
'保密');

查询是否查出敏感数据


select * from tmptable_zyr;





1  
TTT  NAME

可以看到查出了TTT表的name 列。

































有问题可以用下面的调试 加上了         DBMS_OUTPUT.PUT_LINE(v_SQLStatement);                                                    


create table tmptable_zyr (tab_name varchar2(
300) , col_name varchar2(
300));






CREATE OR REPLACE procedure QUERY_KEY

(

 keyword in varchar2

 )

 as

  v_SQLStatement varchar2(
3000);

  
v_startRecord Number;


begin

  FOR reInfo IN (
select table_name from user_tables) LOOP 

    FOR colInfo IN (SELECT column_name, DATA_TYPE

                      FROM user_tab_cols

                     WHERE table_name = reInfo.table_name) LOOP

      IF (colInfo.DATA_TYPE like  
'%CHAR%'  ) THEN     

        v_SQLStatement := 
'select count(*) from ' || reInfo.table_name ||

                          
' where ' || colInfo.column_name || 
' like '
'%'||

                          keyword||
'%'
''; 

        DBMS_OUTPUT.PUT_LINE(v_SQLStatement);                                                    

        
execute immediate v_SQLStatement

          into v_startRecord;      

        
IF (v_startRecord > 
0) THEN        

          insert into tmptable_zyr values (reInfo.table_name,colInfo.column_name);        

          commit;        

        
END IF;      

      
END IF;

    
END LOOP; 

  
END LOOP;


END QUERY_KEY;