행위

시노님 권한 관리 프로시져

DB CAFE

thumb_up 추천메뉴 바로가기


1 권한/시노님 추가 프로시져[편집]

CREATE OR REPLACE PROCEDURE SP_ADD_SYN_GRANT
/*
-- 2019/10/01
-- 시노님 추가 , 권한 추가   
-- 원본테이블에 대한 시노님 생성 
-- 권한관리테이블에 USE_YN = 'Y' 인 대상에 권한 부여
-- BY CYKIM

-- ----------------------------------------------------------------
-- 권한관리테이블(TB_MGR_GRANT)
CREATE TABLE TB_MGR_GRANT
(
  GRANTEE      VARCHAR2(128 BYTE),
  OWNER        VARCHAR2(128 BYTE),
  TABLE_NAME   VARCHAR2(128 BYTE),
  GRANTOR      VARCHAR2(128 BYTE),
  PRIVILEGE    VARCHAR2(40 BYTE),
  GRANTABLE    VARCHAR2(3 BYTE),
  OBJECT_TYPE  VARCHAR2(24 BYTE),
  USE_YN       CHAR(1 BYTE)                     DEFAULT 'Y'                   NOT NULL,
  CREATED      DATE                             DEFAULT SYSDATE               NOT NULL,
  OWNER_TYPE   VARCHAR2(2 BYTE)                 DEFAULT 'R'  -- R:scott,O:tiger,AR:AMEX_scott,AO:AMEX_tiger
)

CREATE UNIQUE INDEX IX_MGR_GRANT_01 ON TB_MGR_GRANT
(GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, 
OBJECT_TYPE)
;
-- 권한 추가 로그 
CREATE TABLE TB_MGR_GRANT_LOG
(
  GRANTEE           VARCHAR2(128 BYTE),
  OWNER             VARCHAR2(128 BYTE),
  TABLE_NAME        VARCHAR2(128 BYTE),
  GRANTOR           VARCHAR2(128 BYTE),
  PRIVILEGE         VARCHAR2(40 BYTE),
  OBJECT_TYPE       VARCHAR2(24 BYTE),
  CREATED           DATE                        DEFAULT SYSDATE               NOT NULL,
  GRANT_REVOKE_GBN  VARCHAR2(30 BYTE),
  GRANT_REVOKE_SQL  VARCHAR2(4000 BYTE),
  GRANTABLE         VARCHAR2(3 BYTE),
  OWNER_TYPE        VARCHAR2(2 BYTE)   -- R:scott,O:tiger,AR:AMEX_scott,AO:AMEX_tiger
)
*/
(
    p_towner            in varchar2 default '', -- 테이블 오너(원본)
    p_tname             in varchar2 default '', -- 테이블 명(원본)
    p_syn_owner         in varchar2 default '', -- 생성할 시노님 오너(scott,tiger,WEB,..)
    p_amex_user         in varchar2 default '',   -- amex_유저 AMEX_scott,AMEX_tiger   
    p_privs             in varchar2 default '',   -- SELECT,INSERT,UPDATE,DELETE
    p_option            in varchar2 default 'C',  -- Create,New            
    p_exec              in number default 0     -- 0 :SQL만 출력 , 1:실행
)
IS

/*  1.권한 부여 프로그램 */                           
    CURSOR INVALID_OBJECT IS                          
        -- 2.TB_MGR_GRANT에서 관리하는 권한에 빠진 권한 부여
            SELECT A.GRANTEE
                 , A.TABLE_NAME
                 , LISTAGG(A.PRIVILEGE,',') WITHIN GROUP(ORDER BY 1) PRIVILEGE
              FROM TB_MGR_GRANT@DL_scott_DEV_scott_DBA A         
             WHERE A.GRANTEE   = p_syn_owner||'_APP' -- APP계정 기준
               AND A.TABLE_NAME= p_tname
               AND A.USE_YN ='Y'                   
             GROUP BY A.GRANTEE , A.TABLE_NAME
                 ;     
                                                   
    V_S1_SQL      VARCHAR2(1000);
    V_S1_APP_SQL  VARCHAR2(1000);
    V_S1_DEV_SQL  VARCHAR2(1000);    
    V_S2_SQL      VARCHAR2(1000);
    V_S2_APP_SQL  VARCHAR2(1000);
    V_S2_DEV_SQL  VARCHAR2(1000);        
    V_G1_SQL      VARCHAR2(1000);
    V_G2_SQL      VARCHAR2(1000);
    V_P1_SQL      VARCHAR2(1000);
    V_P2_SQL      VARCHAR2(1000);    

    V_GRANTEE  VARCHAR2(100);
--    V_OWNER  VARCHAR2(100);
    V_TABLE_NAME  VARCHAR2(100);
--    V_GRANTOR  VARCHAR2(100);
    V_PRIVILEGE  VARCHAR2(100);
--    V_OBJECT_TYPE  VARCHAR2(100);    
--    V_GRANT_REVOKE_GBN VARCHAR2(100);
    
    V_MSG       long;    
    
BEGIN

    DBMS_OUTPUT.ENABLE;
        
    if p_option = 'C' THEN 
        FOR V_ROW IN INVALID_OBJECT
        LOOP    
            -- 블록 에러 발생시에도 계속 실행토록 
            BEGIN 
                V_GRANTEE:= V_ROW.GRANTEE;            
                V_TABLE_NAME:= V_ROW.TABLE_NAME;
    --            V_GRANTOR:= V_ROW.GRANTOR;
                V_PRIVILEGE:= NVL(p_privs,V_ROW.PRIVILEGE);
                
                -- 대상 유저 시노님 생성  
                V_S1_SQL     := 'CREATE OR REPLACE SYNONYM  '||p_syn_owner||'.'||p_tname||' FOR '||p_towner||'.'||p_tname;     
                V_S1_APP_SQL := 'CREATE OR REPLACE SYNONYM  '||p_syn_owner||'_APP.'||p_tname||' FOR '||p_syn_owner||'.'||p_tname;                   
                V_S1_DEV_SQL := 'CREATE OR REPLACE SYNONYM  '||p_syn_owner||'_DEV.'||p_tname||' FOR '||p_syn_owner||'.'||p_tname;
                -- 대상 유저 권한 추가             
                V_G1_SQL     := 'GRANT '||V_PRIVILEGE||' ON '||p_towner   ||'.'||p_tname||' TO  '||p_syn_owner||' WITH GRANT OPTION';
                V_P1_SQL     := 'BEGIN '||p_syn_owner||'.SP_ADD_'||p_syn_owner||'_DEVS_GRANT('''||p_tname||''','''||V_PRIVILEGE||'''); END;';
                                    
                -- amex_user 시노님 생성     
                V_S2_SQL     := 'CREATE OR REPLACE SYNONYM  '||p_amex_user||'.'||p_tname||' FOR '||p_syn_owner||'.'||p_tname;            
                V_S2_APP_SQL := 'CREATE OR REPLACE SYNONYM  '||p_amex_user||'_APP.'||p_tname||' FOR '||p_amex_user||'.'||p_tname;
                V_S2_DEV_SQL := 'CREATE OR REPLACE SYNONYM  '||p_amex_user||'_DEV.'||p_tname||' FOR '||p_amex_user||'.'||p_tname;
                -- amex_user 권한 부여 
                V_G2_SQL     := 'GRANT '||V_PRIVILEGE||' ON '||p_syn_owner   ||'.'||p_tname||' TO   '||p_amex_user||' WITH GRANT OPTION';
                V_P2_SQL     := 'BEGIN '||p_amex_user||'.SP_ADD_'||p_amex_user||'_DEVS_GRANT('''||p_tname||''','''||V_PRIVILEGE||'''); END;';         
                       
                IF p_exec <> 0 THEN        
                    IF p_towner <> p_syn_owner THEN -- 테이블<>시노님 오너가 다를 경우에는 시노님을 생성
                        EXECUTE IMMEDIATE V_S1_SQL;
                    END IF ;
                    -- _APP,_DEV 시노님 생성                 
                    EXECUTE IMMEDIATE V_S1_APP_SQL;
                    EXECUTE IMMEDIATE V_S1_DEV_SQL;                
                    -- WITH GRANT 권한 부여  
                    EXECUTE IMMEDIATE V_G1_SQL;
                    -- _APP,_DEV 권한 부여 
                    EXECUTE IMMEDIATE V_P1_SQL;
                    IF p_amex_user IS NOT NULL THEN 
                        EXECUTE IMMEDIATE V_S2_SQL;
                        -- AMEX_scott/tiger_APP,AMEX_scott/tiger_DEV 시노님 생성                 
                        EXECUTE IMMEDIATE V_S2_APP_SQL;
                        EXECUTE IMMEDIATE V_S2_APP_SQL;                      
                        EXECUTE IMMEDIATE V_G2_SQL;
                        EXECUTE IMMEDIATE V_P2_SQL;
                    END IF;
                            
                    -- LOG 기록 
    --                INSERT INTO TB_MGR_GRANT_LOG 
    --                          (GRANTEE      , OWNER     , TABLE_NAME     , GRANTOR, PRIVILEGE,OBJECT_TYPE,GRANT_REVOKE_GBN) 
    --                    VALUES (V_ROW.GRANTEE,V_ROW.OWNER,V_ROW.TABLE_NAME,V_ROW.GRANTOR,V_ROW.PRIVILEGE,V_ROW.OBJECT_TYPE,V_GRANT_REVOKE_GBN);
    --                COMMIT;
                        
                ELSE
                
                    dbms_output.enable(4000);
           
                    IF p_towner <> p_syn_owner THEN -- 테이블<>시노님 오너가 다를 경우에는 시노님을 생성
                        dbms_output.put_line('[V_S1_SQL]     '||V_S1_SQL);      
                    END IF;
                    dbms_output.put_line('[V_S1_APP_SQL]     '||V_S1_APP_SQL);
                    dbms_output.put_line('[V_S1_DEV_SQL]     '||V_S1_DEV_SQL);                
                    dbms_output.put_line('[V_G1_SQL]         '||V_G1_SQL);                
                    dbms_output.put_line('[V_P1_SQL]         '||V_P1_SQL);
                    
                    IF p_amex_user IS NOT NULL THEN                
                        dbms_output.put_line('[V_S2_SQL]     '||V_S2_SQL);      
                        dbms_output.put_line('[V_S2_APP_SQL] '||V_S2_APP_SQL);
                        dbms_output.put_line('[V_S2_DEV_SQL] '||V_S2_DEV_SQL);                    
                        dbms_output.put_line('[V_G2_SQL]     '||V_G2_SQL);                
                        dbms_output.put_line('[V_P2_SQL]     '||V_P2_SQL);                                
                    END IF;
                    
                END IF;                      
              
            EXCEPTION
                WHEN OTHERS THEN
                    V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); 
    --                INSERT INTO TB_MGR_GRANT_LOG 
    --                           (GRANTEE  , OWNER , TABLE_NAME , GRANTOR , PRIVILEGE ,OBJECT_TYPE  ,GRANT_REVOKE_GBN,GRANT_REVOKE_SQL)
    --                    VALUES (V_GRANTEE,V_OWNER,V_TABLE_NAME,V_GRANTOR,V_PRIVILEGE,V_OBJECT_TYPE,V_GRANT_REVOKE_GBN,V_MSG);
    --                COMMIT;          
            END;
        END LOOP;
        
    ELSIF p_option = 'N' THEN
    
                V_S1_SQL     := 'CREATE OR REPLACE SYNONYM  '||p_syn_owner||'.'||p_tname||' FOR '||p_towner||'.'||p_tname;     
                V_S1_APP_SQL := 'CREATE OR REPLACE SYNONYM  '||p_syn_owner||'_APP.'||p_tname||' FOR '||p_syn_owner||'.'||p_tname;                   
                V_S1_DEV_SQL := 'CREATE OR REPLACE SYNONYM  '||p_syn_owner||'_DEV.'||p_tname||' FOR '||p_syn_owner||'.'||p_tname;            
                V_G1_SQL     := 'GRANT '||p_privs||' ON '||p_towner   ||'.'||p_tname||' TO  '||p_syn_owner||' WITH GRANT OPTION';
                V_P1_SQL     := 'BEGIN '||p_syn_owner||'.SP_ADD_'||p_syn_owner||'_DEVS_GRANT('''||p_tname||''','''||p_privs||'''); END;';
                            
                V_S2_SQL     := 'CREATE OR REPLACE SYNONYM  '||p_amex_user||'.'||p_tname||' FOR '||p_syn_owner||'.'||p_tname;            
                V_S2_APP_SQL := 'CREATE OR REPLACE SYNONYM  '||p_amex_user||'_APP.'||p_tname||' FOR '||p_amex_user||'.'||p_tname;
                V_S2_DEV_SQL := 'CREATE OR REPLACE SYNONYM  '||p_amex_user||'_DEV.'||p_tname||' FOR '||p_amex_user||'.'||p_tname;
                V_G2_SQL     := 'GRANT '||p_privs||' ON '||p_syn_owner   ||'.'||p_tname||' TO   '||p_amex_user||' WITH GRANT OPTION';
                V_P2_SQL     := 'BEGIN '||p_amex_user||'.SP_ADD_'||p_amex_user||'_DEVS_GRANT('''||p_tname||''','''||p_privs||'''); END;';
                
                IF p_exec <> 0 THEN        
                    IF p_towner <> p_syn_owner THEN -- 테이블<>시노님 오너가 다를 경우에는 시노님을 생성
                        EXECUTE IMMEDIATE V_S1_SQL;
                    END IF ;
                    -- _APP,_DEV 시노님 생성                 
                    EXECUTE IMMEDIATE V_S1_APP_SQL;
                    EXECUTE IMMEDIATE V_S1_DEV_SQL;                
                    -- WITH GRANT 권한 부여  
                    EXECUTE IMMEDIATE V_G1_SQL;
                    -- _APP,_DEV 권한 부여 
                    EXECUTE IMMEDIATE V_P1_SQL;
                    IF p_amex_user IS NOT NULL THEN 
                        EXECUTE IMMEDIATE V_S2_SQL;
                        -- AMEX_scott/tiger_APP,AMEX_scott/tiger_DEV 시노님 생성                 
                        EXECUTE IMMEDIATE V_S2_APP_SQL;
                        EXECUTE IMMEDIATE V_S2_APP_SQL;                      
                        EXECUTE IMMEDIATE V_G2_SQL;
                        EXECUTE IMMEDIATE V_P2_SQL;
                    END IF;
                            
                    -- 권한테이블에 추가 
                    BEGIN SP_ADD_GRANT_TABLE(p_towner,p_tname,1);END;    
                    
                    -- LOG 기록 
    --                INSERT INTO TB_MGR_GRANT_LOG 
    --                          (GRANTEE      , OWNER     , TABLE_NAME     , GRANTOR, PRIVILEGE,OBJECT_TYPE,GRANT_REVOKE_GBN) 
    --                    VALUES (V_ROW.GRANTEE,V_ROW.OWNER,V_ROW.TABLE_NAME,V_ROW.GRANTOR,V_ROW.PRIVILEGE,V_ROW.OBJECT_TYPE,V_GRANT_REVOKE_GBN);
    --                COMMIT;
                        
                ELSE
                
                    dbms_output.enable(4000);
           
                    IF p_towner <> p_syn_owner THEN -- 테이블<>시노님 오너가 다를 경우에는 시노님을 생성
                        dbms_output.put_line('[V_S1_SQL]     '||V_S1_SQL);      
                    END IF;
                    dbms_output.put_line('[V_S1_APP_SQL]     '||V_S1_APP_SQL);
                    dbms_output.put_line('[V_S1_DEV_SQL]     '||V_S1_DEV_SQL);                
                    dbms_output.put_line('[V_G1_SQL]         '||V_G1_SQL);                
                    dbms_output.put_line('[V_P1_SQL]         '||V_P1_SQL);
                    
                    IF p_amex_user IS NOT NULL THEN                
                        dbms_output.put_line('[V_S2_SQL]     '||V_S2_SQL);      
                        dbms_output.put_line('[V_S2_APP_SQL] '||V_S2_APP_SQL);
                        dbms_output.put_line('[V_S2_DEV_SQL] '||V_S2_DEV_SQL);                    
                        dbms_output.put_line('[V_G2_SQL]     '||V_G2_SQL);                
                        dbms_output.put_line('[V_P2_SQL]     '||V_P2_SQL);                                
                    END IF;                                    
                END IF;                                  
    END IF;            

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);

END;
/

1.1 권한 제거[편집]

CREATE OR REPLACE PROCEDURE SP_DEL_GRANT_USER_REVOKE
    (  
      p_tname    in varchar2 default ''             -- 테이블 명 
    , p_grantor  in varchar2 default ''             -- AMEX_scott,AMEX_tiger
    , p_grantee  in varchar2 default ''             -- _DEV,_APP
    --, p_gnropt   in varchar2 default 'R'             -- With Grant Option    
    , p_option   in varchar2 default 'RU'           -- RU:revoke USER,RA:revoke All
    , p_delmgr   in varchar2 default 'Y'            -- 권한 관리 테이블에서 삭제 할것인지?    
    , p_exec     in number default 0                -- 0 :SQL만 출력 , 1:실행 
    ) -- authid current_user
is 
/* 1.권한관리테이블에 추가 대상  */
    CURSOR ADD_OBJECT IS
        -- 1.신규 추가된  권한 TB_MGR_INDEX 입력         
        SELECT A.OWNER
             , A.TABLE_NAME
             , A.GRANTEE
             , A.GRANTOR
             , A.GRANTABLE
             , A.PRIVILEGE
             , A.OBJECT_TYPE             
          FROM TB_MGR_GRANT@DL_scott_DEV_scott_DBA A
         WHERE A.TABLE_NAME = p_tname   -- 'TABLE_NAME'
           AND A.GRANTOR   = p_grantor  -- 'AMEX_scott'
--           AND A.GRANTEE IN (p_grantee)
           AND A.USE_YN     = 'Y'          
         ;
         
V_G_SQL VARCHAR2(200);
V_MSG       long;
begin
-- []------------------------------------------------------------------------------------
    IF p_option = 'RU' THEN         -- 해당 GRANTOR의 해당 GRANTEE 제거 
        FOR V_ROW IN ADD_OBJECT
        LOOP 
            -- 명시된 GRANTEE만 제거  
            IF V_ROW.GRANTOR IN (p_grantee) THEN 
                
                V_G_SQL :=  'REVOKE '||V_ROW.PRIVILEGE||' ON '||V_ROW.GRANTOR||'.'|| V_ROW.TABLE_NAME ||' FROM '||V_ROW.GRANTEE;    

            
                IF p_exec <> 0 THEN
                    EXECUTE IMMEDIATE V_G_SQL;
                    
                    -- 권한 테이블에서 제거 하기
                    IF p_delmgr = 'Y' THEN
                        DELETE FROM TB_MGR_GRANT@DL_scott_DEV_scott_DBA
                         WHERE OWNER      = V_ROW.OWNER
                           AND TABLE_NAME = V_ROW.TABLE_NAME
                           AND GRANTEE    = V_ROW.GRANTEE
                           AND GRANTOR    = V_ROW.GRANTOR
                           AND PRIVILEGE  = V_ROW.PRIVILEGE                            
                        ;
                        COMMIT;
                    END IF;
                                
                    -- LOG 기록 
                    INSERT INTO TB_MGR_GRANT_LOG                        
                              (       OWNER ,      TABLE_NAME ,      GRANTEE ,      GRANTOR ,      GRANTABLE 
                              ,      PRIVILEGE ,      OBJECT_TYPE,      GRANT_REVOKE_GBN,GRANT_REVOKE_SQL) 
                       VALUES ( V_ROW.OWNER ,V_ROW.TABLE_NAME ,V_ROW.GRANTEE ,V_ROW.GRANTOR ,V_ROW.GRANTABLE 
                              ,V_ROW.PRIVILEGE ,V_ROW.OBJECT_TYPE,              'R',V_G_SQL);
                    COMMIT;
                END IF;                       
            ELSE
                dbms_output.enable(100); 
                dbms_output.put_line('>>> '||V_G_SQL);      
            END IF;
    --     COMMIT;
        END LOOP;
-- []------------------------------------------------------------------------------------        
     ELSIF p_option = 'RA' THEN -- GRANTOR가 준 전체 권한
      
        FOR V_ROW IN ADD_OBJECT
        LOOP        
                
            V_G_SQL :=  'REVOKE '||V_ROW.PRIVILEGE||' ON '||V_ROW.GRANTOR||'.'|| V_ROW.TABLE_NAME ||' FROM '||V_ROW.GRANTEE;    

        
            IF p_exec <> 0 THEN
                EXECUTE IMMEDIATE V_G_SQL;
                
                -- 권한 테이블에서 제거 하기
                IF p_delmgr = 'Y' THEN
                    DELETE FROM TB_MGR_GRANT@DL_scott_DEV_scott_DBA
                     WHERE OWNER      = V_ROW.OWNER
                       AND TABLE_NAME = V_ROW.TABLE_NAME
                       AND GRANTEE    = V_ROW.GRANTEE
                       AND GRANTOR    = V_ROW.GRANTOR
                       AND PRIVILEGE  = V_ROW.PRIVILEGE                            
                    ;
                    COMMIT;
                END IF;
                            
                -- LOG 기록 
                INSERT INTO TB_MGR_GRANT_LOG                        
                          (       OWNER ,      TABLE_NAME ,      GRANTEE ,      GRANTOR ,      GRANTABLE 
                          ,      PRIVILEGE ,      OBJECT_TYPE,      GRANT_REVOKE_GBN,GRANT_REVOKE_SQL) 
                   VALUES ( V_ROW.OWNER ,V_ROW.TABLE_NAME ,V_ROW.GRANTEE ,V_ROW.GRANTOR ,V_ROW.GRANTABLE 
                          ,V_ROW.PRIVILEGE ,V_ROW.OBJECT_TYPE,              'R',V_G_SQL);
                COMMIT;
                         
            ELSE
                dbms_output.enable(100); 
                dbms_output.put_line('>>> '||V_G_SQL);      
            END IF;
    --     COMMIT;
        END LOOP;
     END IF;
exception 
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' ||V_G_SQL||'-'|| SQLERRM); 
end;
/

2 시노님 관리 프로시져[편집]

CREATE OR REPLACE PROCEDURE DBADM.sp_dbm_synonym(p_grantee varchar2 default '%', p_exec boolean default false)   
--authid current_user  
--grant create any synonym to dbadm 
as 
  v_dbname    varchar2(100) ; --:= 'DBDIBS';  
  v_grantee   varchar2(100) ;  
  v_sqlcmd    varchar2(4000);  
begin   
  v_grantee := upper(p_grantee);  
  select sys_context('USERENV','DB_NAME') into v_dbname from dual;
  if    v_dbname = 'DBPIBS' then v_dbname := 'DBDIBS';
  elsif v_dbname = 'DBPUBS' then v_dbname := 'DBDUBS';
  end if;
  
  dbms_output.put_line('-- dbadm.sp_dbm_synonym(p_grantee => ''%'', p_exec => false)');
  for ic in (  
      select /*+ rule */p.grantee, o.owner, o.object_name   
      from dba_objects o,   
       (select distinct r.grantee, p.owner, p.obj_type, p.obj_nm  
        from dbadm.dbm_privs_obj p, dbadm.dbm_privs_role r, dba_users u 
        where r.dbname = v_dbname  
          and r.grantee like v_grantee  
          and r.dbname=p.dbname  
          and p.grantee=r.granted_role  
          and p.grant_type='ROLE'  
          and p.syn='Y'  
          and r.grantee=u.username 
        union   
        select distinct p.grantee, p.owner, p.obj_type, p.obj_nm  
        from dbadm.dbm_privs_obj p, dba_users u 
        where p.dbname = v_dbname  
          and p.grantee like v_grantee  
          and p.grant_type <> 'ROLE'  
          and p.syn='Y'   
          and p.grantee=u.username 
       ) p  
      where o.owner=p.owner  
        and o.object_type=p.obj_type  
        and o.object_name like p.obj_nm  
        --and o.object_name like decode(o.object_type, 'FUNCTION','FN%', 'PROCEDURE','SP%','SEQUENCE','%SEQ','%') -- Naming 표준이 있는 경우
        and o.object_name not like 'BIN$%' 
      minus 
      select owner, table_owner, table_name from dba_synonyms where owner like '%' 
  )loop  
  begin 
    v_sqlcmd := 'create or replace synonym '|| ic.grantee ||'.'||ic.object_name||' for '||ic.owner||'.'||ic.object_name;  
    --insert into dbadm.tb_dev_msg values('synonym','1', v_sqlcmd, sysdate);  
    if p_exec then 
        execute immediate v_sqlcmd;
        v_sqlcmd := v_sqlcmd||'; -- done';
    else
        v_sqlcmd := v_sqlcmd||';';
    end if;
    dbms_output.enable(1000);  
    dbms_output.put_line(v_sqlcmd);  
  exception when others then  
    dbms_output.enable(1000);  
    dbms_output.put_line(sqlerrm);  
    dbms_output.put_line(v_sqlcmd);  
  end; 
  end loop;  
end;