客户有个需求，查询数据库中所有包含内部、内部资料、秘密、机密、保密、密、等等。。
等字段的表和字段。
创建如下存储过程，查询当前用户（查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;
