今天ronon在etl帐号下创建下述过程,查询分区表的分区键数据类型,发现执行过程时出错, 而etl帐号是有dba角色的.

关键点：在PL/SQL里面select 表的权限需要单独赋予的。即使你是DBA用户 (不能依赖于role-based privileges), 更深一层的原因以后给出.

所以，语句中如果引用了其它帐号的对像，一定要注意这一点.

[@more@]

SQL> col GRANTEE for a10
SQL> col GRANTED_ROLE for a15
SQL>
SQL> select grantee,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE from DBA_ROLE_PRIVS where grantee in('ETL') order by grantee;

GRANTEE GRANTED_ROLE ADMIN_ DEFAUL
---------- --------------- ------ ------
ETL DBA NO YES
ETL DEVELOPERS NO YES

create or replace procedure tf_get_par_clo_type(p_table_name in varchar2) as
v_par_clo_type varchar2(64);
v_str_owner varchar2(1024) := '';
v_str_tabname varchar2(1024) := '';
v_str varchar2(1024);
begin

v_str_owner := upper(substr(p_table_name,1,instr(p_table_name, '.') -1));
v_str_tabname := upper(substr(p_table_name,instr(p_table_name, '.') + 1));
v_str := ' select data_type ' ||
' from sys.dba_tab_columns c, sys.dba_part_key_columns p, sys.dba_part_tables t ' ||
' where p.name = t.table_name ' ||
' and c.owner = t.owner ' ||
' and t.owner = '''||v_str_owner||''''||
' and c.table_name = t.table_name ' ||
' and c.column_name = p.column_name ' ||
' and t.table_name = '''||v_str_tabname|| '''';
dbms_output.put_line(v_str);
execute immediate v_str into v_par_clo_type;
--using v_str_tabname;
--return v_par_clo_type;
dbms_output.put_line(v_par_clo_type);

/* exception
when others then
--return 'error';*/
null;
end tf_get_par_clo_type;

SQL> exec etl.tf_get_par_clo_type('HLW.TEST_PARTITION');
select data_type from sys.dba_tab_columns c, sys.dba_part_key_columns p,
sys.dba_part_tables t where p.name = t.table_name and c.owner = t.owner and
t.owner = 'HLW' and c.table_name = t.table_name and c.column_name =
p.column_name and t.table_name = 'TEST_PARTITION'
BEGIN etl.tf_get_par_clo_type('HLW.TEST_PARTITION'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "ETL.TF_GET_PAR_CLO_TYPE", line 19
ORA-06512: at line 1

procedure中的语句单独拿出来执行正常

SQL> select data_type from sys.dba_tab_columns c, sys.dba_part_key_columns p,
2 sys.dba_part_tables t where p.name = t.table_name and c.owner = t.owner and
3 t.owner = 'HLW' and c.table_name = t.table_name and c.column_name =
4 p.column_name and t.table_name = 'TEST_PARTITION';

DATA_TYPE
--------------------------------------------------------------------------------
DATE

注意错误信息ORA-00942: table or view does not exist, 是不是etl帐号在pl/sql引擎下执行时,不具备select权限呢?

我们试一下显式授权

grant select on sys.dba_tab_columns to etl;
grant select on sys.dba_part_key_columns to etl;
grant select on sys.dba_part_tables to etl;

这次可以正常执行了

SQL> exec etl.tf_get_par_clo_type('HLW.TEST_PARTITION');
select data_type from sys.dba_tab_columns c, sys.dba_part_key_columns p,
sys.dba_part_tables t where p.name = t.table_name and c.owner = t.owner and
t.owner = 'HLW' and c.table_name = t.table_name and c.column_name =
p.column_name and t.table_name = 'TEST_PARTITION'
DATE

PL/SQL procedure successfully completed.