利用外部表,实现对批量表自动授权功能。由于要授权的表名是由普通文件(.txt)的形式下发,所以此时利用了外部表,减轻了工作量。
read.txt中是一些表名,在此不再列出。
1.创建目录
SQL>create directory testdir as 'd:\oracle\temp\';
2.创建外部表
SQL>create table readtab
(table_name varchar2(50))
organization external
(type oracle_loader
default directory testdir
access parameters ( fields terminated by ',' )
location ('read.txt')
)
/
3应用1
select 'grant select on '||trim(table_name)|| ' to QH_YQCDTJC;' from readtab
/
4.创建外部表2
SQL>create table writetab
(table_name varchar2(300))
organization external
(type oracle_loader
default directory testdir
access parameters ( fields terminated by ',' )
location ('write.txt')
)
5.应用2.
select 'grant select on '||trim(table_name)|| ' to QH_YQCDTJC;' from readtab
union
select 'grant insert on '||trim(table_name)|| ' to QH_YQCDTJC;' from writetab
union
select 'grant delete on '||trim(table_name)|| ' to QH_YQCDTJC;' from writetab
union
select 'grant update on '||trim(table_name)|| ' to QH_YQCDTJC;' from writetab
union
select 'grant insert on '||trim(table_name)|| ' to QH_YQSJYCJL;' from writetab
union
select 'grant delete on '||trim(table_name)|| ' to QH_YQSJYCJL;' from writetab
union
select 'grant update on '||trim(table_name)|| ' to QH_YQSJYCJL;' from writetab
union
select 'grant select on '||trim(table_name)|| ' to QH_YQSJYCJL;' from readtab