SUBLIME 스니펫

<snippet>
	<content><![CDATA[

-- 시노님 생성 
CREATE OR REPLACE  SYNONYM RTIS_DEV.${1:} FOR RTIS.${1:};
CREATE OR REPLACE  SYNONYM RTIS_APP.${1:} FOR RTIS.${1:};

-- TABLE 실행권한 부여
GRANT SELECT ON RTIS.${1:} TO RTIS_APP ;  
-- GRANT SELECT,DELETE,UPDATE ON RTIS.${2:} TO RTIS_DEV ;


-- 1.시노님,권한 RTIS -> RTIS_DEV,RTIS_APP
WITH TB_DUMMY AS (
          SELECT TRIM('${1:}') AS TBL FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
) 
SELECT 'CREATE SYNONYM RTIS_DEV.'||TBL||' FOR RTIS.'||TBL||';' SYN1
     , 'CREATE SYNONYM RTIS_APP.'||TBL||' FOR RTIS.'||TBL||';' SYN2
     , 'GRANT SELECT ON RTIS.'||TBL||' TO RTIS_APP;' GRT2     
     , 'EXEC SP_ADD_GRANT_TABLE;'  ADD_GRANT
     , 'INSERT INTO -- GRANT SELECT ON RTIS.'||TBL||' TO RTIS_DEV;' GRT1     
  FROM DUAL A
  FULL OUTER JOIN TB_DUMMY B      
               ON 1=1
 WHERE EXISTS ( SELECT 1 FROM DBA_TABLES C -- 실제 테이블이 존재 하는지 체크 
                 WHERE C.TABLE_NAME = TBL
              )            
;      

-- 1.시노님,권한 RTIS -> WEB_DEV,WEB_APP
WITH TB_DUMMY AS (
          SELECT TRIM('${1:}') AS TBL FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
) 
SELECT 'CREATE SYNONYM WEB_DEV.'||TBL||' FOR RTIS.'||TBL||';' SYN1
     , 'CREATE SYNONYM WEB_APP.'||TBL||' FOR RTIS.'||TBL||';' SYN2
     , 'GRANT SELECT ON RTIS.'||TBL||' TO WEB_APP,WEB_DEV;' GRT2     
     , 'EXEC SP_ADD_GRANT_TABLE;'  ADD_GRANT     
  FROM DUAL A
  FULL OUTER JOIN TB_DUMMY B      
               ON 1=1
 WHERE EXISTS ( SELECT 1 FROM DBA_TABLES C -- 실제 테이블이 존재 하는지 체크 
                 WHERE C.TABLE_NAME = TBL
              )            
;      

-- 1.시노님,권한 OBT -> RTIS_DEV,RTIS_APP
WITH TB_DUMMY AS (
          SELECT TRIM('${1:}') AS TBL FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
) 
SELECT 'CREATE SYNONYM RTIS_DEV.'||TBL||' FOR OBT.'||TBL||';' SYN1
     , 'CREATE SYNONYM RTIS_APP.'||TBL||' FOR OBT.'||TBL||';' SYN2
     , 'GRANT SELECT ON OBT.'||TBL||' TO RTIS_APP;' GRT2     
     , 'EXEC SP_ADD_GRANT_TABLE;'  ADD_GRANT     
     , '-- GRANT SELECT ON OBT.'||TBL||' TO RTIS_DEV;' GRT1     
  FROM DUAL A
  FULL OUTER JOIN TB_DUMMY B      
               ON 1=1
 WHERE EXISTS ( SELECT 1 FROM DBA_TABLES C -- 실제 테이블이 존재 하는지 체크 
                 WHERE C.TABLE_NAME = TBL
              )            
;     

-- 2.OBT 시노님,권한 RTIS -> OBT_DEV,OBT_APP

-- 1.시노님,권한 
WITH TB_DUMMY AS (
          SELECT TRIM('${1:}') AS TBL FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
)
SELECT 'CREATE SYNONYM OBT_DEV.'||TBL||' FOR RTIS.'||TBL||';' SYN1
     , 'CREATE SYNONYM OBT_APP.'||TBL||' FOR RTIS.'||TBL||';' SYN2
     , 'CREATE SYNONYM OBT.'||TBL||' FOR RTIS.'||TBL||';' SYN2     
     , 'GRANT SELECT ON RTIS.'||TBL||' TO OBT_APP;' GRT2     
     , 'GRANT SELECT ON RTIS.'||TBL||' TO OBT;' GRT1     
     , 'EXEC SP_ADD_GRANT_TABLE;'  ADD_GRANT     
  FROM DUAL A
  FULL OUTER JOIN TB_DUMMY B      
               ON 1=1
 WHERE EXISTS ( SELECT 1 FROM DBA_TABLES C -- 실제 테이블이 존재 하는지 체크 
                 WHERE C.TABLE_NAME = TBL
              )            
;      

-- 3.WEB 시노님,권한  WEB -> WEB_DEV,WEB_APP
-- 1.시노님,권한 
WITH TB_DUMMY AS (
          SELECT TRIM('${1:}') AS TBL FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
)
SELECT 'CREATE SYNONYM WEB_DEV.'||TBL||' FOR WEB.'||TBL||';' SYN1
     , 'CREATE SYNONYM WEB_APP.'||TBL||' FOR WEB.'||TBL||';' SYN2
     , 'GRANT SELECT ON WEB.'||TBL||' TO WEB_APP;' GRT2     
     , 'EXEC SP_ADD_GRANT_TABLE;'  ADD_GRANT     

  FROM DUAL A
  FULL OUTER JOIN TB_DUMMY B      
               ON 1=1
 WHERE EXISTS ( SELECT 1 FROM DBA_TABLES C -- 실제 테이블이 존재 하는지 체크 
                 WHERE C.TABLE_NAME = TBL
              )            
;   

-- 4.ERPAPP 시노님,권한  ERPAPP -> RTIS_DEV,RTIS_APP
-- 1.시노님,권한 
WITH TB_DUMMY AS (
          SELECT TRIM('${1:}') AS TBL FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
UNION ALL SELECT TRIM('${1:}') FROM DUAL
)
SELECT 'CREATE SYNONYM RTIS_DEV.'||TBL||' FOR ERPAPP.'||TBL||';' SYN1
     , 'CREATE SYNONYM RTIS_APP.'||TBL||' FOR ERPAPP.'||TBL||';' SYN2
     , 'GRANT SELECT ON ERPAPP.'||TBL||' TO RTIS_APP;' GRT2     
     , 'EXEC SP_ADD_GRANT_TABLE;'  ADD_GRANT     

  FROM DUAL A
  FULL OUTER JOIN TB_DUMMY B      
               ON 1=1
 WHERE EXISTS ( SELECT 1 FROM DBA_TABLES C -- 실제 테이블이 존재 하는지 체크 
                 WHERE C.TABLE_NAME = TBL
              )            
;   

]]></content>
	<tabTrigger>:1</tabTrigger>
	<!-- scope>source.sql</scope -->
</snippet>
SUBLIME 스니펫

DB CAFE
