oracle 创建中间用户，并授权

原创

西柚墨遥 2023-11-04 09:17:24 ©著作权

文章标签 sql 存储过程 oracle授权中间用户 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者西柚墨遥的原创作品，请联系作者获取转载授权，否则将追究法律责任

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