'1. IT Story'에 해당되는 글 397건

GRANT EXECUTE ON DBMS_OBFUSCATION_TOOLKIT TO system;

GRANT EXECUTE ON DBMS_CRYPTO TO system;

create or replace function TYS_Encrypt (input_string VARCHAR2) RETURN RAW

IS

   -- select DBMS_CRYPTO.RANDOMBYTES (num_key_bytes) from dual; --> key_bytes_raw

   encrypted_raw      RAW (2000);             -- stores encrypted binary text

   key_bytes_raw      RAW (32) := HEXTORAW('6E266456572A3E4C79D677BAC5144A645DF911CE07F18AEFF57C3E8560AE1672');  --암호화 키

   encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;  --암호화 알고리즘

BEGIN

     encrypted_raw := DBMS_CRYPTO.ENCRYPT

      (src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),

       typ => encryption_type,

       key => key_bytes_raw);

   return encrypted_raw;

END;

/

[PACKAGE]

CREATE OR REPLACE PACKAGE SCOTT.CRYPTO_AES256

IS

/******************************************************************************

  암호화

 ******************************************************************************/

 FUNCTION ENC_AES ( input_string IN VARCHAR2

 ) RETURN VARCHAR2;

/******************************************************************************

  복호화

 ******************************************************************************/

 FUNCTION DEC_AES (  encrypted_raw IN VARCHAR2

 ) RETURN VARCHAR2;                       

END CRYPTO_AES256;

/

CREATE OR REPLACE PACKAGE BODY SCOTT.CRYPTO_AES256

IS

/******************************************************************************

  암호화

 ******************************************************************************/

 FUNCTION ENC_AES ( input_string IN VARCHAR2

 ) RETURN VARCHAR2

 IS

   encrypted_raw      RAW (2000);             -- 암호화된 RAW타입 데이터

   key_bytes_raw      RAW (32);               -- 암호화 KEY (32RAW => 32Byte => 256bit)

   encryption_type    PLS_INTEGER :=          -- 암호화 알고리즘 선언

                            DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

    BEGIN

        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');

        encrypted_raw := DBMS_CRYPTO.ENCRYPT

            (

                src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),

                typ => encryption_type,

                key => key_bytes_raw

            );

        -- 에러 방지를 위해 base64_encode로 인코딩 처리..

        -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

        RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(encrypted_raw));

    END ENC_AES;

/******************************************************************************

  복호화

 ******************************************************************************/

 FUNCTION DEC_AES (  encrypted_raw IN VARCHAR2

 ) RETURN VARCHAR2

IS

   output_string      VARCHAR2 (200);         -- 복호화된 문자열

   decrypted_raw      RAW (2000);             -- 복호화된 raw타입 데이터

   key_bytes_raw      RAW (32);               -- 256bit 암호화 key

   encryption_type    PLS_INTEGER :=          -- 복호화 알고리즘 선언

                            DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

    BEGIN

        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');

        decrypted_raw := DBMS_CRYPTO.DECRYPT

            (

                -- 에러 방지를 위해 base64_decode로 인코딩 처리..

                -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

                src => UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(encrypted_raw)),

                typ => encryption_type,

                key => key_bytes_raw

            );

       output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');

       RETURN output_string;

    END DEC_AES;                       

END CRYPTO_AES256;

/

[Funtion]

create FUNCTION ENC_AES ( input_string IN VARCHAR2

 ) RETURN VARCHAR2

 IS

 /******************************************************************************

  암호화

 ******************************************************************************/

   encrypted_raw      RAW (2000);             -- 암호화된 RAW타입 데이터

   key_bytes_raw      RAW (32);               -- 암호화 KEY (32RAW => 32Byte => 256bit)

   encryption_type    PLS_INTEGER :=          -- 암호화 알고리즘 선언

                            DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

    BEGIN

        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');

        encrypted_raw := DBMS_CRYPTO.ENCRYPT

            (

                src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),

                typ => encryption_type,

                key => key_bytes_raw

            );

        -- 에러 방지를 위해 base64_encode로 인코딩 처리..

        -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

        RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(encrypted_raw));

    END ENC_AES;

 /

 create FUNCTION DEC_AES (  encrypted_raw IN VARCHAR2

 ) RETURN VARCHAR2

IS

/******************************************************************************

  복호화

 ******************************************************************************/

   output_string      VARCHAR2 (200);         -- 복호화된 문자열

   decrypted_raw      RAW (2000);             -- 복호화된 raw타입 데이터

   key_bytes_raw      RAW (32);               -- 256bit 암호화 key

   encryption_type    PLS_INTEGER :=          -- 복호화 알고리즘 선언

                            DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

    BEGIN

        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');

        decrypted_raw := DBMS_CRYPTO.DECRYPT

            (

                -- 에러 방지를 위해 base64_decode로 인코딩 처리..

                -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

                src => UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(encrypted_raw)),

                typ => encryption_type,

                key => key_bytes_raw

            );

       output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');

       RETURN output_string;

    END DEC_AES;                       

/

export EDITOR=vi

echo "======================================="

echo "===Please Select the Instance Number==="

echo "======================================="

echo "1) TESTDB"

echo "2) STARTDB"

echo "3) ABCDB"

echo "What number do you want?"

read number

case $number in

        "1")

                export ORACLE_SID=TESTDB

                echo "Thank you for selecting TESTDB"

                ;;

        "2")

                export ORACLE_SID=STARTDB

                echo "Thank you for selecting STARTDB"

                ;;

        "3")

                export ORACLE_SID=ABCDB

                echo "Thank you for selecting ABCDB"

                ;;

        *)

                exit;;

esac

define LOWTHRESHOLD=10

define MIDTHRESHOLD=62

define VERBOSE=FALSE

set veri off;

set feedback off;

set serverout on

DECLARE

 verbose boolean:=&&VERBOSE;

BEGIN

 For C in (

  select 

   version, 

   date_time,

   dbms_flashback.get_system_change_number current_scn,

   indicator

  from

  (

   select

   version,

   to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') DATE_TIME,

   ((((

    ((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60) +

    ((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60) +

    (((to_number(to_char(sysdate,'DD'))-1))*24*60*60) +

    (to_number(to_char(sysdate,'HH24'))*60*60) +

    (to_number(to_char(sysdate,'MI'))*60) +

    (to_number(to_char(sysdate,'SS')))

    ) * (16*1024)) - dbms_flashback.get_system_change_number)

   / (16*1024*60*60*24)

   ) indicator

   from v$instance

  ) 

 ) LOOP

  dbms_output.put_line( '-----------------------------------------------------'

                        || '---------' );

  dbms_output.put_line( 'ScnHealthCheck' );

  dbms_output.put_line( '-----------------------------------------------------'

                        || '---------' );

  dbms_output.put_line( 'Current Date: '||C.date_time );

  dbms_output.put_line( 'Current SCN:  '||C.current_scn );

  if (verbose) then

    dbms_output.put_line( 'SCN Headroom: '||round(C.indicator,2) );

  end if;

  dbms_output.put_line( 'Version:      '||C.version );

  dbms_output.put_line( '-----------------------------------------------------'

                        || '---------' );

  IF C.version > '10.2.0.5.0' and 

     C.version NOT LIKE '9.2%' THEN

    IF C.indicator>&MIDTHRESHOLD THEN 

      dbms_output.put_line('Result: A - SCN Headroom is good');

      dbms_output.put_line('Apply the latest recommended patches');

      dbms_output.put_line('based on your maintenance schedule');

      IF (C.version < '11.2.0.2') THEN

        dbms_output.put_line('AND set _external_scn_rejection_threshold_hours='

                             || '24 after apply.');

      END IF;

    ELSIF C.indicator<=&LOWTHRESHOLD THEN

      dbms_output.put_line('Result: C - SCN Headroom is low');

      dbms_output.put_line('If you have not already done so apply' );

      dbms_output.put_line('the latest recommended patches right now' );

      IF (C.version < '11.2.0.2') THEN

        dbms_output.put_line('set _external_scn_rejection_threshold_hours=24 '

                             || 'after apply');

      END IF;

      dbms_output.put_line('AND contact Oracle support immediately.' );

    ELSE

      dbms_output.put_line('Result: B - SCN Headroom is low');

      dbms_output.put_line('If you have not already done so apply' );

      dbms_output.put_line('the latest recommended patches right now');

      IF (C.version < '11.2.0.2') THEN

        dbms_output.put_line('AND set _external_scn_rejection_threshold_hours='

                             ||'24 after apply.');

      END IF;

    END IF;

  ELSE

    IF C.indicator<=&MIDTHRESHOLD THEN

      dbms_output.put_line('Result: C - SCN Headroom is low');

      dbms_output.put_line('If you have not already done so apply' );

      dbms_output.put_line('the latest recommended patches right now' );

      IF (C.version >= '10.1.0.5.0' and 

          C.version <= '10.2.0.5.0' and 

          C.version NOT LIKE '9.2%') THEN

        dbms_output.put_line(', set _external_scn_rejection_threshold_hours=24'

                             || ' after apply');

      END IF;

      dbms_output.put_line('AND contact Oracle support immediately.' );

    ELSE

      dbms_output.put_line('Result: A - SCN Headroom is good');

      dbms_output.put_line('Apply the latest recommended patches');

      dbms_output.put_line('based on your maintenance schedule ');

      IF (C.version >= '10.1.0.5.0' and

          C.version <= '10.2.0.5.0' and

          C.version NOT LIKE '9.2%') THEN

       dbms_output.put_line('AND set _external_scn_rejection_threshold_hours=24'

                             || ' after apply.');

      END IF;

    END IF;

  END IF;

  dbms_output.put_line(

    'For further information review MOS document id 1393363.1');

  dbms_output.put_line( '-----------------------------------------------------'

                        || '---------' );

 END LOOP;

end;

/