擁有dba角色的普通帳號在pl sql中查sys的對像時仍需顯式授權

今天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.