Oracle从A库向B库赋予权限,建立同义词

浅浅的花香味﹌ 2021-09-21 04:26 294阅读 0赞

步骤一:

declare 
      i number;
      s varchar(50);
      s_YXRAW varchar(50);
    begin
      select user into s_YXRAW from dual;
      if s_YXRAW <> 'YXRAW' then return; end if;
      s:= 'PBP_QX'; -- 要导入的用户
      
      -- 赋权限-----------赋权限必须在YXRAW用户下执行
      --dbms_output.put_line('grant select, references, index on REC_TRADE to '|| s || ';'); -- 交易流水表
      --dbms_output.put_line( 'grant EXECUTE on YXRAW.YXRAW_COMMON to '|| s ||';'); -- YXRAW通用包
      for i in (select table_name from user_tables where table_name not in ('BAS_SYSTEM_INFO','BAS_PAGE','USR_USER','BAS_SYSTEM_INFO','BAS_REPORT') order by table_name)
      loop
        dbms_output.put_line('grant select, insert, update, delete, references, alter, index on '|| i.table_name ||' to '|| s || ';');
      end loop;
      
    
    
    -- 其他
    /* dbms_output.put_line( 'declare'); dbms_output.put_line( 's_YXRAW varchar(50);'); dbms_output.put_line( 'begin'); dbms_output.put_line( 'select user into s_YXRAW from dual;'); dbms_output.put_line( 'if s_YXRAW = ''YXRAW'' then return; end if;'); */
      -- 删除表、创建同义词，切不可在YXRAW用户下执行
      -- 删除同名表
      /*for i in (select table_name from user_tables) loop dbms_output.put_line('DROP TABLE '|| i.table_name ||' CASCADE CONSTRAINTS; '); end loop;*/
    
      -- 创建同义词
      --dbms_output.put_line( 'CREATE OR REPLACE EDITIONABLE SYNONYM "'|| s ||'"."YXRAW_COMMON" FOR "'|| s_YXRAW ||'"."YXRAW_COMMON";');
      --dbms_output.put_line( 'CREATE OR REPLACE EDITIONABLE SYNONYM "'|| s ||'"."REC_TRADE" FOR "'|| s_YXRAW ||'"."REC_TRADE";'); 
      for i in (select table_name from user_tables where table_name not in ('BAS_SYSTEM_INFO','BAS_PAGE','USR_USER','BAS_SYSTEM_INFO','BAS_REPORT') order by table_name)
      loop
        dbms_output.put_line('CREATE OR REPLACE EDITIONABLE SYNONYM "'|| s ||'"."'|| i.table_name ||'" FOR "'|| s_YXRAW ||'"."'|| i.table_name ||'";');
      end loop;
      
      -- dbms_output.put_line( 'end;');
      
      -- grant select, insert, update, delete, references, alter, index on RPT_ACCOUNT_CATEGORY to PAM
      
      
    end;

步骤二:  
在YXRAW库(赋值库)中执行上半部分语句,在PBP\_QX(被赋同义词库)执行下半部分语句:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzQ5NTM5MA_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzQ5NTM5MA_size_16_color_FFFFFF_t_70]: /images/20210920/18401705dcc84ea291dd0b68d1fb4ab3.png