1.当前状况 系统正式用户:ecp 开发用户需求:按工号新增用户,开发以后不使用ecp,而使用工号登录,工号用户需要可以查看ecp所有的对象,但是无法进行删除,修改的动作

2.工号用户:BAC7000370 3.权限授予说明 table----select view ----select trigger ---- debug table(trigger 没有单独的权限,因为trigger是依赖在表上面) package ---- execute (execute/debug 权限都可以看到但是无法修改,下同,只有execute权限,无法查看 package body 的内容,需要 debug权限) package body ---- debug package procedure ---- execute(execute执行权限,可以查看,debug权限无法查看) type -----execute type body ---- debug type function ---- execute sequence ---- select (可以调用) 以上所有对象权限都可以在 dba_tab_privs 视图里面查找到(trigger除外,检查trigger是否能被看到,只要检查是否有debug table权限即可)

3.执行

select * from dba_tab_privs a where a.owner='ECP';

--如果量少可以手动拼接语句,手动执行
select 'grant select,delete,insert,update,debug on '||a.owner||'.'||a.table_name||' to BAC7000370;' from dba_tables a where a.owner='ECP';
select 'grant execute,debug on '||a.owner||'.'|| a.object_name||' to BAC7000370;' 
from dba_objects a where a.owner='ECP' and a.object_type='PACKAGE';
....
--如果还需要进一步方便,可以创建同义词
select 'CREATE OR REPLACE SYNONYM BAC7000370.'||a.table_name||' for ecp.'||a.table_name||' ;' from dba_tables a where a.owner='ECP';

4.使用存储过程 前提 有部分ecp的对象调用了其他用户的对象,这部分需要提前连带授权 否则报错:ORA-04042: procedure, function, package, or package body does not exist

select * from dba_tab_privs a where a.grantee='ECP';
select 'grant '||a.privilege||' on '||a.owner||'.'||a.table_name||' to '||a.grantee||' with grant option;' from dba_tab_privs a where a.grantee='ECP';
--grant select on pcp.VIEW_SCM_AMOUNT to ecp with grant option;
--grant EXECUTE on pcp.DS_FUNC_DECRYPT_DES to ecp with grant option;
create or replace noneditionable procedure dba_grant_user(var_username in varchar2,var_gonghao in varchar2)
as
num number;
v_table_sql varchar2(4000) ;
--v_table_debug_sql varchar2(4000) ;
v_view_sql varchar2(4000) ;
v_procedure_sql varchar2(4000) ;
v_type_sql varchar2(4000) ;
--v_type_body_sql varchar2(4000) ;
v_function_sql varchar2(4000) ;
v_package_sql varchar2(4000) ;
--v_package_body_sql varchar2(4000) ;

---------------select
--view 
--table

--------------debug table
---trigger 

-------------- execute
--package  procedure FUNCTION TYPE 

------------  debug  
--package body  TYPE BODY

---前提 有部分ecp的对象调用了其他用户的对象,这部分需要提前连带授权

cursor v_table_name is select OBJECT_NAME from dba_objects a where owner =upper(var_username)  and a.OBJECT_TYPE in ('TABLE') ;
cursor v_view_name is select OBJECT_NAME from dba_objects a where owner =upper(var_username)  and a.OBJECT_TYPE in ('VIEW') ;
cursor v_procedure_name is select OBJECT_NAME from dba_objects a where owner =upper(var_username)  and a.OBJECT_TYPE in ('PROCEDURE') ;
cursor v_type_name is select OBJECT_NAME from dba_objects a where owner =upper(var_username)  and a.OBJECT_TYPE in ('TYPE') ;
--cursor v_type_body_name is select OBJECT_NAME from dba_objects a where owner =upper(var_username)  and a.OBJECT_TYPE in ('TYPE BODY') ;
cursor v_function_name is select OBJECT_NAME from dba_objects a where owner =upper(var_username)  and a.OBJECT_TYPE in ('FUNCTION') ;
cursor v_package_name is select OBJECT_NAME from dba_objects a where owner =upper(var_username)  and a.OBJECT_TYPE in ('PACKAGE') ;
--cursor v_objects_name is select OBJECT_NAME from dba_objects a where owner ='ECP'  and a.OBJECT_TYPE in ('JAVA SOURCE') ;



begin
  num :=1;
  loop
  if num=1 then  
    for i in v_table_name loop
     v_table_sql := 'grant select,delete,update,insert,debug on "'||var_username||'"."' || i.OBJECT_NAME|| '" to "'||var_gonghao||'"';
     --v_table_debug_sql := 'grant debug on '||var_username||'.' || i.OBJECT_NAME|| ' to "'||var_gonghao||'"';
     dbms_output.put_line(v_table_sql);
     execute immediate v_table_sql;
     --dbms_output.put_line(v_table_debug_sql);
     --execute immediate v_table_debug_sql;
     end loop;
     
  elsif num=2 then
      for i in v_view_name loop
      v_view_sql := 'grant select on '||var_username||'.' || i.OBJECT_NAME|| ' to "'||var_gonghao||'"';
      dbms_output.put_line(v_view_sql);
      execute immediate v_view_sql;
      end loop;
      
  elsif num=3 then
      for i in v_procedure_name loop
      v_procedure_sql := 'grant execute on '||var_username||'.' || i.OBJECT_NAME|| ' to "'||var_gonghao||'"';
      dbms_output.put_line(v_procedure_sql);
      execute immediate v_procedure_sql;
      end loop;      
      
  elsif num=4 then
     for i in v_type_name loop
     v_type_sql := 'grant execute,debug on '||var_username||'.' || i.OBJECT_NAME|| ' to "'||var_gonghao||'"';
     --v_type_body_sql := 'grant debug on '||var_username||'.' || i.OBJECT_NAME|| ' to "'||var_gonghao||'"';
     dbms_output.put_line(v_type_sql);
     execute immediate v_type_sql;
     --dbms_output.put_line(v_type_body_sql);
     --execute immediate v_type_body_sql;
      end loop;      
  
  elsif num=5 then
     for i in v_function_name loop
     v_function_sql := 'grant execute on '||var_username||'.' || i.OBJECT_NAME|| ' to "'||var_gonghao||'"';
     dbms_output.put_line(v_function_sql);
     execute immediate v_function_sql;
      end loop;   
              
  elsif num=6 then
     for i in v_package_name loop
     v_package_sql := 'grant execute,debug on '||var_username||'.' || i.OBJECT_NAME|| ' to "'||var_gonghao||'"';
     --v_package_body_sql := 'grant debug on '||var_username||'.' || i.OBJECT_NAME|| ' to "'||var_gonghao||'"';
     dbms_output.put_line(v_package_sql);
     execute immediate v_package_sql;
     --dbms_output.put_line(v_package_body_sql);
     --execute immediate v_package_body_sql;
      end loop;     
        
  end if;
    
    num:=num+1;
    exit when num=7;
  end loop;  
end;

说明: 1.因为很多对象都是sys用户的,用system建procedure不方便,需要授权一堆,因此,如果想要system执行,可用sys建立,授权给system执行权限即可,不是system能查到这些视图,就能建立存储过程 grant execute ,debug on sys.dba_grant_user to system;

2.因为有些用户的对象特别的,所以调用时加上dbms_output.enable(buffer_size => null),不然会报错,PLSQL放不下, ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes begin dbms_output.enable(buffer_size => null);

3.v_table_sql := 'grant select,delete,update,insert,debug on "'||var_username||'"."' || i.OBJECT_NAME|| '" to "'||var_gonghao||'"'; 最后是故意没有分号,有分号执行报错ORA-00933,没有分号才行,和拼接语句再执行不太一样,原因不太了解 ORA-00933参考文章:https://www.modb.pro/db/21036