X.509를 이용하여 SAP HANA Cloud 인가하기

10 May 2024

hdbsql 버전 확인
```
hdbsql -v
```
웹에서 콘솔을 이용하여 basic authentication을 계정을 만들고 로컬에서 접속 테스트를 해보자.
```
SQL CREATE USER TESTUSER PASSWORD Password1 NO FORCE_FIRST_PASSWORD_CHANGE SET USERGROUP DEFAULT;
```

hdbsql -j -A -sslprovider mscrypto -u TESTUSER -p Password1 -Z traceFile=stdout -Z traceOptions=debug=warning,flush=on -n ENDPOINT주소 "SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY;"

openssl을 이용하여 개인 키를 만들어주자.

개인 키를 저장할 경로를 만들고

openssl를 통해 key를 만들어주자

openssl genrsa -out demorootca.key 4096
openssl req -x509 -new -nodes -key demorootca.key -sha256 -days 365 -subj "/CN=DEMO_ROOT_CERT_AUTH/ST=ON" -out demorootca.crt

이번엔 공개키를 만들어주자

openssl genrsa -out test_x509_user.key 2048
openssl req -new -nodes -key test_x509_user.key -out test_x509_user.csr -subj "/CN=TESTX509"

서명이 된 client certificate를 만든다.

openssl x509 -req -in test_x509_user.csr -CA demorootca.crt -CAkey demorootca.key -CAcreateserial -days 31 -out test_x509_user.crt

서명이 된건지 확인한다.

openssl verify -CAfile demorootca.crt test_x509_user.crt

client certificate 표시

openssl x509 -text -noout -in test_x509_user.crt

만들어진 crt를 이용하여 HANA Cloud web console서 쿼리를 쳐보자.

위 내용을 복사하고 아래 쿼리로 변경하여 실행해준다.

CREATE CERTIFICATE X509_USER_CERT FROM '-----BEGIN CERTIFICATE-----
MIIEDjCCAfagAwIBAgIUJ9J5L3FXp244Iw8cgIVc4UnVWMUwDQYJKoZIhvcNAQEL
BQAwKzEcMBoGA1UEAwwTREVNT19ST09UX0NFUlRfQVVUSDELMAkGA1UECAwCT04w
HhcNMjQwNTEwMDcyMDIyWhcNMjQwNjEwMDcyMDIyWjATMREwDwYDVQQDDAhURVNU
WDUwOTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMPERzPZOnlqFyLt
xcuKWGqcdmYTF9qDqG9bVevski2muZ+c90+6K/WtG1vlXE5b6TrUjmiU26tpiBgZ
H3hr0S1EzJT+TCFQqckJyU+JyqFP2iCLYExB6nyvEb2yJHz952Pr5x4TGAsjKI0W
82i2SrQ7aPD7fDESoaewcL8U1AQXCm44J2FmaujKUjPibTup6z7yqGpRA4EwrY/U
1NNIqW65zy9Wq2OKM1xHTX2kE+bhr+LyNs/mnjSND79AhEi4QBiFsWkdkyFhAwA3
Dyq0aQqoGyKGpoiSJTVy3LTmvRMTpNTyUomvqaNyDQL1yscLecHb6Z32ZaVXOy2A
/rw7G2MCAwEAAaNCMEAwHQYDVR0OBBYEFKJnhvvFRZEks/Z2TYGzzhtfdkWxMB8G
A1UdIwQYMBaAFKB+1v05TlJza3zI4HmCKKtzSzS8MA0GCSqGSIb3DQEBCwUAA4IC
AQBhjwfc0dWBhabFyi094sumKw/+iAXxhXW5+YbHVJCOthUA93Ta1YZdeWly5Yj1
CSNhKEcqbH1pkvScDDGZOy9X3Y59ivzUN0KNROhYHuPaFCsxm56SJj5SrX4gHoTx
J2VgGDFOL/zd9ZycHWHMN6UBC4XuyZ6SXGhwvzwUUV/P/OeST1nTHsr76+6QO7T7
CzPdSBwO5xnpHRRTmb/HO1vLXIpdKoMftB6AHC3BwtwVv1Panh5WuUyiG9h+CoBT
EaXCX7zLfweoeMxDm9lXWYErrDIsX6dltwbDKEWnFJ0tcwFYJner0JVvLaIlHb8a
cPIl3yYBhGRtTu8ZqfcZT6fQNGRR92Xe1A5l/nh4kk4Z3JIO11h26ZjiE0QXNCDX
5sToZmJbFAl+zdDA9hklg/yAfV+RMen4IHtQp4L4iLTcKBRxAMokQLxVfhpk+U01
1LWNxHMs6/K8CCxKcLdmHOWwV4nFll4uOx3E+N4WoeaCDEpBdPG0D8ZVSjhnoqF8
1DXyThqWl0Sg2lYWOWUuaoFpj/ZbtxrXDsoZCJaUG8574Q+7Fko21yyoS78PO4dE
9UXCNH7WQbbCkMEI4cQN5l8Uf95s0GswgmvbFJlgtdzK/hVY2B1tKT5JKdN41QhN
iDlVxWQfoFJJjEgfofmyJNgng80JwwrmbgXJ40V0nzx74Q==
-----END CERTIFICATE-----';

이후 제대로 생성되었는지 확인

SELECT CERTIFICATE_NAME, SUBJECT_DISTINGUISHED_NAME, ISSUER_DISTINGUISHED_NAME, ISSUER_COMMON_NAME
  FROM CERTIFICATES
 WHERE CERTIFICATE_NAME='X509_USER_CERT';

이번에 demorootca.crt를 이용하여 CERTIFICATE를 만들어보자. demorootca.crt 파일 확인 후에 아래 쿼리 수정

CREATE CERTIFICATE DEMOROOTCA_CERT FROM '-----BEGIN CERTIFICATE-----
MIIFNzCCAx+gAwIBAgIUY5tZOtu2nv1EoB9FZU34OqMBHhYwDQYJKoZIhvcNAQEL
BQAwKzEcMBoGA1UEAwwTREVNT19ST09UX0NFUlRfQVVUSDELMAkGA1UECAwCT04w
HhcNMjQwNTEwMDcxOTM5WhcNMjUwNTEwMDcxOTM5WjArMRwwGgYDVQQDDBNERU1P
X1JPT1RfQ0VSVF9BVVRIMQswCQYDVQQIDAJPTjCCAiIwDQYJKoZIhvcNAQEBBQAD
ggIPADCCAgoCggIBAJFoSaYwMioGUwKSRmPIP5SzFwq+rg4znaLM+yD6eTZX8lP9
OO5BOBofP0Ju8BluekSkR9faEfohsrhpzOmziLXkI0einlvBtKhebjWBUnj9LOlv
Po0ZjrxduSZQiAbtO89nRjUqyGTbsanObDUghjyzV3BBeswZaElqcwhhV7QXSrgG
oMAhc5IwgVAWSJW9EpPjf+KuxE3KKaUzVUq9SkRA0V3FlDke+CSypnqJpcEo1xOA
NXnkPw2HmX19skCzLnIA3eksabYfZPG2WJJ6DmvUWpL6I8WSU8f23URBoCoxsWiW
rPYfER2WMU5aIt2fbjbwAN2le9J9uD4x5y5my/J+iXTKHfdIC9GxlemVKPeVCM4O
ufIzVY3Fq87SXZGU0mwyzCDBN/779VcliyjmLT7mdpLKVopcREl8GAv4Bj1rUE7T
HMDb0qvfgCw2nAleTYQua9+PxGomfr0wRijFE6/B0NV806F++OJynLwzbUOFlmwD
merdcejD4BKaz62vjC83lRb6fBa6aNXLziHq3fcv4mDPrq2RJgwsO/FqYTcV//bK
JUcJ9nDS8745TPn2ERq6UT6Vr0rNgmDB9X2ai+Wnpc7V2Qi6XXv5dueW4jTEUxar
4gyqCvrEbK4cmDALApCk6WHjU88qis8sKl+OwMUIO+EEeB3zdsvnRqjxyuWNAgMB
AAGjUzBRMB0GA1UdDgQWBBSgftb9OU5Sc2t8yOB5giirc0s0vDAfBgNVHSMEGDAW
gBSgftb9OU5Sc2t8yOB5giirc0s0vDAPBgNVHRMBAf8EBTADAQH/MA0GCSqGSIb3
DQEBCwUAA4ICAQBpIEkypx6OPuewE3Rtq/J95SfMDpm5mJ3eG0OHROOHE09EGYmF
4QjiH0ZF/Vd2DzTbNN9xwD7+YDgl+GsUfEmJ2ldWyO5q5i4UmdWynkPmOigenx0X
lxFEZ3dlTOIazXSQ/oHdurrj8+0ZY3nIyXhbRJfNO00wUlKa/GIT+ORt8TQLVB0H
56AEffR9/pD+ifmtMnm5/zBoxPsQ+ohlSRZF06ERgBd9TrJKjf5RNAkbvdx5CXf+
vuFCNUEFgaa9I6OuT0GWwqKQ5gBPmDw/hqJduwOLbhWzSetrAO6ndPrp0avFnU4m
GR27OXVfkhrNeeZfjdMWdGxORpYe4Vcg6VCrUn9F4NAKn+WIeG4rmW+k+90Wjl/7
HboZbfdFJ2szjh050rZtyt/h7fc2+TmUD0LXZdpht8PL+tFTFfiuYnJVrz3sLgGR
lMi27hcG5rJpVmTMsr/2915BofI4aDwBpm7ikkwbCoEZbN/G3K4vWD2THxaogfz1
KMGVg1Nd0MSH+bHWhwqVufe9N2ueoXyYaGhoOuZxj/Kp6foCtrd+C7xZwM8UOxin
84apClKdGepIts36nKd8vVdb7hiiG4NvUFjpcQvNrIx2aMpST6KSp68NEoU77UMr
2Dv0VqAO1gICcg+EquN1K0+KHWffazbCoOLEyZH0B5VUgV5NAz1ytcU9kQ==
-----END CERTIFICATE-----';

생성완료

이후 아래 쿼리 실행

CREATE X509 PROVIDER DEMO_X509_PROVIDER WITH ISSUER 'SP=ON, CN=DEMO_ROOT_CERT_AUTH';
SELECT * FROM X509_PROVIDERS;
CREATE PSE X509_PSE;
SELECT * FROM PSES;
ALTER PSE X509_PSE ADD CERTIFICATE DEMOROOTCA_CERT;
SELECT * FROM PSE_CERTIFICATES;
SET PSE X509_PSE PURPOSE X509 FOR PROVIDER DEMO_X509_PROVIDER;
SELECT * FROM PSES;

Database 유저 만들기

CREATE USER TESTX509_TECHNICAL WITH IDENTITY 'CN=TESTX509' FOR X509 PROVIDER DEMO_X509_PROVIDER SET USERGROUP DEFAULT;
SELECT * FROM USERS WHERE USER_NAME = 'TESTX509_TECHNICAL';

이후 웹에서 Database Exploerer Open

좌측 상단의 플러스 버튼을 누르고 정보 기입후에 OK 클릭

새로 추가한 Database 연결 정보로 접속하여 쿼리를 날려보자.

SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY;

정상 동작

마지막으로 총 복습하는 느낌으로 Node.js App을 만들고 X.509를 이용하여 연결해보자.

새로운 경로 생성
node project를 구성하기 위해 init
```
npm init -y
```
nodeQuery.js 작성 ``` ‘use strict’; var util = require(‘util’); var hana = require(‘@sap/hana-client’);

var connOptions = { //Option 1 serverNode: ‘@X509UserKey’,

//Option 2
// serverNode: '00e63a27-dda7-4603-9b32-8f894fc5c2f1.hana.trial-us10.hanacloud.ondemand.com:443',
// authenticationX509: '~/certs/test_x509_user.pem',

sslCryptoProvider: 'openssl',
authenticationMethods: 'x509' };

var connection = hana.createConnection();

connection.connect(connOptions);

var sql = ‘SELECT CURRENT_USER FROM DUMMY;’; var result = connection.exec(sql); console.log(util.inspect(result, {colors: true})); connection.disconnect();

4. hdbuserstore에 key 생성 (이건 또 cmd에서 안되고 git bash에선 된다.)

hdbuserstore SetX509 X509UserKey 1e48300b-3b35-4003-8340-aa8f2ffb3cc1.hana.trial-us10.hanacloud.ondemand.com:443 C:/sap/hana_tutorial/nodeCF/cert/test_x509_user.pem ``` 정상적으로 만들어짐

이후 접속 요청을 했는데 refuse가 됨

if(재능이 없으면 시간으로 극복하라){return 성공;}

Profile

Post

Project

X.509를 이용하여 SAP HANA Cloud 인가하기