PL/SQL专家指南4——调用者权限和定义者权限 下篇

-------------------------------------------------------------调用者权限和定义者权限学习---------------------------------------------------

转自群友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-------------------------------------
--------------------------------全文完---------------------------------------