この広告は30日以上更新がないブログに表示されております。
新規記事の投稿を行うことで、非表示にすることが可能です。
広告
posted by fanblog
2016年02月07日
(DataStage)OracleDBアクセス
CREATE ROLE DSXE;
GRANT SELECT on sys.dba_extents to DSXE;
GRANT SELECT on sys.dba_data_files to DSXE;
GRANT SELECT on sys.dba_tab_partitions to DSXE;
GRANT SELECT on sys.dba_tab_subpartitions to DSXE;
GRANT SELECT on sys.dba_objects to DSXE;
GRANT SELECT on sys.all_part_indexes to DSXE;
GRANT SELECT on sys.all_part_tables to DSXE;
GRANT SELECT on sys.all_indexes to DSXE;
GRANT DSXE to;
参考URL:
http://www-01.ibm.com/support/knowledgecenter/?lang=ja#!/SSZJPZ_11.3.0/com.ibm.swg.im.iis.conn.oradb.help.doc/topics/t_coracon_Accessing_Oracle_Databases.html
GRANT SELECT on sys.dba_extents to DSXE;
GRANT SELECT on sys.dba_data_files to DSXE;
GRANT SELECT on sys.dba_tab_partitions to DSXE;
GRANT SELECT on sys.dba_tab_subpartitions to DSXE;
GRANT SELECT on sys.dba_objects to DSXE;
GRANT SELECT on sys.all_part_indexes to DSXE;
GRANT SELECT on sys.all_part_tables to DSXE;
GRANT SELECT on sys.all_indexes to DSXE;
GRANT DSXE to
参考URL:
http://www-01.ibm.com/support/knowledgecenter/?lang=ja#!/SSZJPZ_11.3.0/com.ibm.swg.im.iis.conn.oradb.help.doc/topics/t_coracon_Accessing_Oracle_Databases.html
ENCRYPT/DECRYPT(暗号化/復号化)
−− 実行権限付与
grant execute on dbms_crypto to USER_NAME;
−− 暗号化
CREATE OR REPLACE FUNCTION TEXT_ENCRYPT(pTXT VARCHAR2, pPwd VARCHAR2)
RETURN VARCHAR2
IS
vEncrypted RAW(1000);
BEGIN
vEncrypted := DBMS_CRYPTO.ENCRYPT(
src => UTL_I18N.STRING_TO_RAW (pTXT, 'AL32UTF8'),
typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5,
key => UTL_I18N.STRING_TO_RAW(RPAD(pPwd, 32, CHR(0)), 'AL32UTF8'));
RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(vEncrypted));
END;
/
−− 復号化
CREATE OR REPLACE FUNCTION TEXT_DECRYPT(pEncTxt VARCHAR2, pPwd VARCHAR2)
RETURN VARCHAR2
IS
vRaw RAW(1000);
BEGIN
vRaw := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(pEncTxt));
vRaw := DBMS_CRYPTO.DECRYPT(
src => vRaw,
typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5,
key => UTL_I18N.STRING_TO_RAW(RPAD(pPwd, 32, CHR(0)), 'AL32UTF8'));
RETURN UTL_I18N.RAW_TO_CHAR (vRaw, 'AL32UTF8');
END;
/
grant execute on dbms_crypto to USER_NAME;
−− 暗号化
CREATE OR REPLACE FUNCTION TEXT_ENCRYPT(pTXT VARCHAR2, pPwd VARCHAR2)
RETURN VARCHAR2
IS
vEncrypted RAW(1000);
BEGIN
vEncrypted := DBMS_CRYPTO.ENCRYPT(
src => UTL_I18N.STRING_TO_RAW (pTXT, 'AL32UTF8'),
typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5,
key => UTL_I18N.STRING_TO_RAW(RPAD(pPwd, 32, CHR(0)), 'AL32UTF8'));
RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(vEncrypted));
END;
/
−− 復号化
CREATE OR REPLACE FUNCTION TEXT_DECRYPT(pEncTxt VARCHAR2, pPwd VARCHAR2)
RETURN VARCHAR2
IS
vRaw RAW(1000);
BEGIN
vRaw := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(pEncTxt));
vRaw := DBMS_CRYPTO.DECRYPT(
src => vRaw,
typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5,
key => UTL_I18N.STRING_TO_RAW(RPAD(pPwd, 32, CHR(0)), 'AL32UTF8'));
RETURN UTL_I18N.RAW_TO_CHAR (vRaw, 'AL32UTF8');
END;
/
(PL/SQL)INSERTでRETURNING句
CREATE OR REPLACE PROCEDURE TEST.TBL_INSERT
IS
vUser TABLE_NAME%ROWTYPE;
BEGIN
INSERT INTO USER_MASTER (USER_ID, USER_NAME) VALUES ('0001', '鈴木 一')
RETURNING USER_ID, USER_NAME INTO vUser;
DBMS_OUTPUT.PUT_LINE('インサートした件数は' || SQL%ROWCOUNT || '件です。');
DBMS_OUTPUT.PUT_LINE('インサートしたUSER_IDは' || vUser.USER_ID || 'です。');
END;
/
IS
vUser TABLE_NAME%ROWTYPE;
BEGIN
INSERT INTO USER_MASTER (USER_ID, USER_NAME) VALUES ('0001', '鈴木 一')
RETURNING USER_ID, USER_NAME INTO vUser;
DBMS_OUTPUT.PUT_LINE('インサートした件数は' || SQL%ROWCOUNT || '件です。');
DBMS_OUTPUT.PUT_LINE('インサートしたUSER_IDは' || vUser.USER_ID || 'です。');
END;
/
