[OCI - ATP to Oracle DBLink]

OCI의 ATP 서비스와 일반 VM 또는 on-prem의 Oracle DB와 DBlink를 생성하였습니다.

ATP 특성상 OS 접근이 안되기 때문에 일반적으로 생성하는 DBlink와는 방법이 조금 다릅니다.

 

 

■ Wallet용 디렉토리 생성 (target  DB)

[root@target-oracle ~]# mkdir -p /u01/server/wallet
[root@target-oracle ~]# mkdir -p /u01/client/wallet
[root@target-oracle ~]# mkdir /u01/certificate

[root@target-oracle ~]# chown -R oracle.oinstall /u01/server
[root@target-oracle ~]# chown -R oracle.oinstall /u01/client/
[root@target-oracle ~]# chown -R oracle.oinstall /u01/certificate/

 

 

■ TCP인증(SSL인증) 활성화 (target  DB)

## 서버 wallet 생성

[root@target-oracle ~]# su - oracle

[oracle@target-oracle ~]$ cd /u01/server/wallet/


## 서버 wallet 생성
[oracle@target-oracle wallet]$ orapki wallet create -wallet ./ -pwd Kim123456 -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.


[oracle@target-oracle wallet]$ orapki wallet add -wallet ./ -pwd Kim123456 -dn "CN=dbcs" -keysize 1024 -self_signed -validity 3650 -sign_alg sha256
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

 

## 클라이언트 wallet 생성

[oracle@target-oracle wallet]$ cd /u01/client/wallet/

[oracle@target-oracle wallet]$ orapki wallet create -wallet ./ -pwd Kim123456 -auto_login


[oracle@target-oracle wallet]$ orapki wallet add -wallet ./ -pwd  Kim123456 -dn "CN=client" -keysize 1024 -self_signed -validity 3650 -sign_alg sha256
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

 

 

■ 인증서 교환 (target  DB)

1. 서버 및 클라이언트 인증서 export

## 서버
[oracle@target-oracle ~]$ cd /u01/server/wallet/

[oracle@target-oracle wallet]$ orapki wallet export -wallet ./ -pwd Kim123456 -dn "CN=dbcs" -cert /tmp/server.crt
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.



## 클라이언트
[oracle@target-oracle wallet]$ cd /u01/client/wallet/

[oracle@target-oracle wallet]$ orapki wallet export -wallet ./ -pwd Kim123456 -dn "CN=client" -cert /tmp/client.crt
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

 

2.서버의 wallet에 클라이언트 인증서 & 클라이언트 wallet에 서버 인증서 각 추가

[oracle@target-oracle wallet]$ cd /u01/server/wallet/

[oracle@target-oracle wallet]$  orapki wallet add -wallet ./ -pwd Kim123456 -trusted_cert -cert /tmp/client.crt
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.


[oracle@target-oracle wallet]$ cd /u01/client/wallet/

[oracle@target-oracle wallet]$ orapki wallet add -wallet ./ -pwd Kim123456 -trusted_cert -cert /tmp/server.crt
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

 

3.인증서 확인 및 서버 wallet 권한 변경

[oracle@target-oracle wallet]$ orapki wallet display -wallet .
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=client
Trusted Certificates:
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        CN=client
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Subject:        CN=dbcs
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US


[oracle@target-oracle wallet]$  cd /u01/server/wallet
[oracle@target-oracle wallet]$  chmod 640 cwallet.sso

 

 

■ wallet directory 추가 설정 (target  DB)

1.Listener.ora에 wallet directory 추가

[root@target-oracle ~]# su - grid

[grid@target-oracle admin]$ vi $ORACLE_HOME/network/admin/listener.ora

[grid@target-oracle admin]$ vi listener.ora

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/server/wallet)
    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = asis-oracle)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = asis-oracle)(PORT = 2484))
    )
  )

 

2.sqlnet.ora에 wallet directory 추가

[root@target-oracle ~]# su - oracle

[root@target-oracle ~]# vi $ORACLE_HOME/network/admin/sqlnet.ora
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/server/wallet)
     )
   )

 

 

■ TCPS접속 사용 2484번 포트 해제 (target  DB)

[root@target-oracle sysconfig]# yum -y install iptables-services


[root@target-oracle ~]# vi /etc/sysconfig/iptables

# sample configuration for iptables service
# you can edit this manually or use system-config-firewall
# please do not ask us to add additional ports/services to this default configuration
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
-A INPUT -p tcp -m state --state NEW -m tcp --dport 2484 -j ACCEPT
COMMIT
~                                                                    


[root@target-oracle ~]# systemctl restart iptables

 

 

■ TCP endpoint 추가 (target DB)

## 리스너 구성 변경
$ sudo su - grid
$ srvctl modify listener -p "TCPS:1522/TCP:1521"
$ srvctl stop listener
$ srvctl start listener


## DB 재기동
$ sudo su - oracle
$ srvctl stop database -database dbcs01_xxxxxx
$ srvctl start database -database dbcs01_xxxxxx


## 리스너 상태 확인
$ sudo su - grid
$ lsnrctl status

 

 

■ target DB wallet을 ATP에 전달(object storage 업로드)

[root@target-oracle ~]# su - oracle

[oracle@target-oracle ~]$ cp /u01/client/wallet/cwallet.sso  /tmp/.
[oracle@target-oracle ~]$ chmod 604 /tmp/cwallet.sso

 

 

■ 오브젝트 스토리지에 cwallet.sso 업로드 (ATP)

 

 

■ 사용자 공개 키 생성 (ATP)

 

 

■ API 서명 키 생성 (target DB)

[root@target-oracle ~]# mkdir -p /home/opc/TESTKEY

[root@target-oracle ~]# openssl genrsa -out /home/opc/TESTKEY/oci_api_key.pem 2048
Generating RSA private key, 2048 bit long modulus
..............+++
..........................+++
e is 65537 (0x10001)


[root@target-oracle ~]# chmod go-rwx /home/opc/TESTKEY/oci_api_key.pem


[root@target-oracle TESTKEY]# mkdir -p /root/oci


[root@target-oracle TESTKEY]# openssl rsa -pubout -in /home/opc/TESTKEY/oci_api_key.pem -out /root/oci/oci_api_key_public.pem
writing RSA key




[root@target-oracle ~]# openssl rsa -pubout -outform DER -in /home/opc/TESTKEY/oci_api_key.pem | openssl md5 -c
writing RSA key
(stdin)= 3c:89:00:fd:d5:e5:e0:dc:0e:1a:4e:c0:f9:d5:1b:6e




[root@target-oracle TESTKEY]# cd /root/oci/


[root@target-oracle oci]# ll
total 4
-rw-r--r-- 1 root root 451 Oct 11 11:29 oci_api_key_public.pem


[root@target-oracle oci]# vi oci_api_key_public.pem

-----BEGIN PUBLIC KEY-----
************************************
************************************
-----END PUBLIC KEY-----

 

 

■ 사용자 생성 (ATP)

메뉴 -> ID -> 사용자 -> 사용자 생성

 

 

■ 암호 키 생성

 

 

■ 데이터베이스 연결 후 자격 증명 생성

private_key : 고객 암호 키

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name => 'OCI_KEY_CRED',
user_ocid => '***',
tenancy_ocid => '***',
private_key => '***',
fingerprint => '3***');
END;
/

 

 

 

■ oracleidentitycloudservice/kim 유저의 토큰(비밀번호) 생성

 

 

■ wallet 설정 (ATP)

## wallet 설정을 위한 디렉토리 생성
CREATE DIRECTORY dblink_wallet_dir_dbcs AS 'walletdir';


## 자격증명 등록 (target db에 생성한 kim유저로 지정)
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'kim',
username => 'oracleidentitycloudservice/kim',
password => '');
END;
/



## 오브젝트 스토리지에 업로드된 wallet을 dierctory object에 배치
BEGIN
  DBMS_CLOUD.GET_OBJECT(
    credential_name => 'kkk',
    object_uri => '/cwallet.sso',
    directory_name => 'dblink_wallet_dir_dbcs');
END;
/

 

## 배치된 wallet 확인

 

■ DB Link 생성 (ATP)

BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'TODAY',
          hostname => '',
          port => '',
          service_name => 'orcl',
          ssl_server_cert_dn => 'CN=asis-oracle',
          credential_name => 'DBCS_DB_LINK_CRED',
          directory_name => 'dblink_wallet_dir_dbcs');
END;
/

 

 

■ DB link 생성 확인

※ DB Link 삭제 방법

###DROP DATABASE LINK
BEGIN
DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(
  db_link_name => ' '
);
END;
/

 

 

■ DBlink test

select * from dba_users@TESTDBLINK;