-------------------------------------------------------------调用者权限和定义者权限学习---------------------------------------------------
转自群友shehasgone：
--调用者权限的存储过程 print_table
create or replace procedure print_table(p_query in varchar2)
authid current_user
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTb1 dbms_sql.desc_tab;
l_colCnt number;
begin
dbms_sql.parse(l_theCursor,p_query,dbms_sql.native);
dbms_sql.describe_columns(l_theCursor,l_colCnt,l_descTb1);
for i in 1..l_colCnt loop
dbms_sql.define_column(l_theCursor,i,l_columnValue,4000);
end loop;
l_status:=dbms_sql.execute(l_theCursor);
while(dbms_sql.fetch_rows(l_theCursor)>0) loop
for i in 1..l_colCnt loop
dbms_sql.column_value(l_theCursor,i,l_columnValue);
dbms_output.put_line(rpad(l_descTb1(i).col_name,30)
||':'||
l_columnValue);
end loop;
dbms_output.put_line('--------------------------');
end loop;
exception
when others then
dbms_sql.close_cursor(l_theCursor);
raise;
end;
---把这个存储过程的执行权限授予 public
SQL> grant execute on print_table to public;
Grant succeeded
--调用者权限的存储过程 desc_table
create or replace procedure desc_table(p_tname in varchar2)
authid current_user
as
begin
dbms_output.put_line('Datatypes for Table '||p_tname);
dbms_output.new_line;
dbms_output.put_line(rpad('Column Name',31)||
rpad('Datatype',20) ||
rpad('Length',11) ||
'Nullable');
dbms_output.put_line(rpad('-',30,'-')||''||
rpad('-',19,'-')||''||
rpad('-',10,'-')||''||
'-----------');
for x in
( select column_name,
data_type,
substr(decode(data_type,
'NUMBER',decode(data_precision,NULL,NULL,'('||data_precision||','||data_scale||')'),
data_length),1,11) data_length,
decode(nullable,'Y','null','not null') nullable
from user_tab_columns
where table_name = upper(trim(p_tname))
order by column_id)
loop
dbms_output.put_line(rpad(x.column_name,31)||
rpad(x.data_type,20)||
rpad(x.data_length,11)||
x.nullable);
end loop;
dbms_output.put_line(chr(10)||chr(10)||'Indexes on '||p_tname);
for z in
( select a.index_name,a.uniqueness from user_indexes a
where a.table_name = upper(trim(p_tname))
and index_type='NORMAL')
loop
dbms_output.put(rpad(z.index_name,31)||
z.uniqueness);
for y in
( select decode(column_position,1,'(',',')||column_name column_name
from user_ind_columns b
where b.index_name = z.index_name
order by column_position)
loop
dbms_output.put(y.column_name);
end loop;
dbms_output.put_line(')'||chr(10));
end loop;
end;
---把这个存储过程的执行权限授予 public
SQL> grant execute on desc_table to public;
--定义一个调用者权限函数
--先建立一个测试表
create table t ( x varchar2(255));
--建立调用者权限函数
create or replace function Invoker_rights_function return varchar2
authid current_user
as
l_data varchar2(4000);
begin
dbms_output.put_line('I am an IR PROC owned by A');
select 'current_user='||sys_context('userenv','currnet_user')||
'current_schema='||sys_context('userenv','current_schema')||
'active roles='||cnt||
'data from T='||t.x
into l_data
from (select count(*) cnt from session_roles), t;
return l_data;
end;
--把这个调用者权限函数授权到public
SQL> grant execute on she.invoker_rights_function to public;
Grant succeeded
--定义一个定义者权限函数
create or replace function Definer_rights_function return varchar2
as
l_data varchar2(4000);
begin
dbms_output.put_line('I am an IR PROC owned by A');
select 'current_user='||sys_context('userenv','currnet_user')||
'current_schema='||sys_context('userenv','current_schema')||
'active roles='||cnt||
'data from T='||t.x
into l_data
from (select count(*) cnt from session_roles), t;
return l_data;
end;
--把这个调用者权限函数授权到public
SQL> grant execute on she.Definer_rights_function to public;
Grant succeeded
--如果是视图则以定义者权限存储
create view V
as
select invoker_rights_function from dual;
--把视图的查询权限授予public
SQL> GRANT SELECT ON V TO PUBLIC;
Grant succeeded
当切换用户执行时如果如下：
SELECT * FROM SHE.V
结果
current_user=current_schema=SHEactive roles=0data from T=SHE'table
这说明，查的不是当前用户，而是she用户下的。
此时当前用户下无T表也不会报错。
--对于查询
SELECT SHE.INVOKER_RIGHTS_FUNCTION FROM DUAL;
结果：
current_user=current_schema=SCOTTactive roles=2data from T=SCOTT'table
这个是当前用户下的信息。
此时当前用户下无T表也会报错。
--最后说明， 视图是以定义者权限进行编译存储的。
--健壮的程序
--建立测试表。
create table t (pk number);
create table y ( c number,d number);
-建立测试存储过程
create or replace procedure P
authid current_user
as
no_such_table exception;
pragma exception_init(no_such_table,-942);
insufficient_privs exception;
pragma exception_init(insufficient_privs,-1031);
invalid_column_name exception;
pragma exception_init(invalid_column_name,-904);
inconsistent_datatypes exception;
pragma exception_init(inconsistent_datatypes,-932);
begin
for x in (select pk from t ) loop
update y set c=c+0.5 where d=x.pk;
end loop;
commit;
exception
when no_such_table then
dbms_output.put_line('Error Caught:'||sqlerrm);
when insufficient_privs then
dbms_output.put_line('Error Caught:'||sqlerrm);
when invalid_column_name then
dbms_output.put_line('Error Caught:'||sqlerrm);
when inconsistent_datatypes then
dbms_output.put_line('Error Caught:'||sqlerrm);
rollback;
end;
---
--换其它用户执行
SQL> conn scott/tiger;
已连接。
SQL> set serveroutput on;
SQL> exec she.p;
Error Caught:ORA-00904: "PK": 无效的标识符
PL/SQL 过程已成功完成。
SQL> exec she.p;
PL/SQL 过程已成功完成。
SQL> exec she.p;
Error Caught:ORA-00904: "D": 无效的标识符
PL/SQL 过程已成功完成。
SQL> exec she.p;
Error Caught:ORA-00932: 不一致的数据类型: 要求 NUMBER 得到的却是 DATE
PL/SQL 过程已成功完成。
SQL> exec she.p;
BEGIN she.p; END;
*
ERROR 位于第 1 行:
ORA-00932: 不一致的数据类型: 要求 NUMBER 得到的却是 DATE
ORA-06512: 在"SHE.P", line 17
ORA-06512: 在line 1
SQL> exec she.p;
Error Caught:ORA-00932: 不一致的数据类型: 要求 NUMBER 得到的却是 DATE
PL/SQL 过程已成功完成。
SQL>
--注：如何获取关联的ora错误编号。
当我把
no_such_table exception;
pragma exception_init(no_such_table,-942);
中的-943后，重新编译这个存储过程
执行时。
SQL> exec she.p;
BEGIN she.p; END;
*
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在
ORA-06512: 在"SHE.P", line 17
ORA-06512: 在line 1
这里是会报错的，真正的错误是ORA-00942
这时就已经提示我们，这个异常编号是和-942关联的
此时，把-943 再换成-942编译后，达到捕捉异常后，过程能运行的目的。
--select * 的副作用
建立测试表及数据。
create table t (msg varchar2(25),c1 int,c2 int);
insert into t values('c1=1,c2=2',1,2);
commit;
--建立调用者权限存储过程
create or replace procedure p
authid current_user
as
begin
for x in (select * from t) loop
dbms_output.put_line('msg='||x.msg);
dbms_output.put_line('c1='||x.c1);
dbms_output.put_line('c2='||x.c2);
end loop;
end;
--执行
exec p;
--结果
msg=c1=1,c2=2
c1=1
c2=2
--把执行权限授予public
SQL> grant execute on p to public;
Grant succeeded
--建立测试用户
SQL> create user u1 identified by u1 default tablespace users;
用户已创建
SQL> grant dba,resource,connect to u1;
授权成功。
--切换到u1用户
--建立测试表及数据
create table t (msg varchar2(25),c2 int ,c1 int);
insert into t values('c1=2,c2=1',1,2);
commit;
--调用上面的存储过程
SQL> set serveroutput on;
SQL> exec she.p;
msg=c1=2,c2=1
c1=1
c2=2
PL/SQL procedure successfully completed
注意此时的结果 c1=1,c2=2当前用户表中的c1=2,c2=1
分析：它根本不是所要求的－－但认真思考后会发现，这确实是按程序实现的。
在编译时，PL/SQL为设置了隐含记录X。记录X只是一个数据结构，具有3个元素：
MSG VARCHAR2,C1 NUMBER,C2 NUMBER。在用户SHE的查询分析阶段，SELECT *
按次序展开为MSG,C1,C2。然而作为U1,它们被展开为MSG,C2,C1。因为数据类型
完全与隐含的记录X匹配，您不会收到INCONSISTENT DATATYPE 的错误提示（如果
数据类型不匹配，这一错误还是会发生）。数据读取成功，便把记录属性C1列放入
了C2列。这是预期的行为，也是在产品程序中不使用SELECT * 的另一个充分的理由。
---隐藏的伪列
--以she用户建立表和测试数据
create table t (c1 int);
insert into t values(1);
commit;
--建立调用者权限存储过程
create or replace procedure p
authid current_user
as
c2 number default 5;
begin
update t set c1=c2;
commit;
end;
--在当前用户下执行并查询表T
SQL> exec p;
PL/SQL procedure successfully completed
SQL> select * from t;
C1
---------------------------------------
5
注意这里是5.
--如果表t 有字段c2，同时c2的值为8，那此时看到的值应该是8.
--接着切换到用户u1
--建立测试表及数据
create table t (c1 int ,c2 int);
insert into t values(1,2);
commit;
--执行用户she下的存储过程P并查看结果。
SQL> exec she.p;
PL/SQL procedure successfully completed
SQL> select * from t;
C1 C2
--------------------------------------- ---------------------------------------
5 2
注意，此时的结果 c1的值为5，但c2的值没有变化。
分析：它的对错在于您如何看待。对于UPDATE SET C1=C2语句，如果在SQL*PLUS提示符状态
运行，结果是C1被设置为2，而不是5.然而，因为编译时PL/SQL重写此查询没有任何对于C2
的引用，所以它对T数据表复本进行操作，与它对其化T数据表复本所做的完全相同－－将
C1设置为5。此PL/SQL程序不能“看到”C2列，因为C2不存在于它编译的对象中。
起先，这似乎有些混乱，因为您不有正常地看到改写后的更新内容，但一旦您了解了它，
它是非常有意义的。
------------------------------2011-10-13-------------------------------------
--------------------------------全文完---------------------------------------