MiDBA: config HTTPS for APEX 5.1 running on SE2

In my case, self-signed certification is used.

High level steps are;

1. Relink SE Oracle binary in order to support TCPS. Skip this step for EE

2. Create wallet to store self-signed cert

3. Create self-signed cert

4. Verify TCPS connection over SQL*NET

5. Configure http2 for APEX Embedded web server

Below are details.

Step 1 - Enable SE TCPS

Follow Oracle doc - How To Enable TCPS Support For Oracle Standard Edition (Doc ID 1457854.1)

-- current lib file

/software/oracle/product/12.2.0/lib> ls -l libntcps*

-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a

--installer jar files

:/software/oracle/admin/INSTALLER/database/stage/Components/oracle.network.rsf/12.2.0.1.0/1/DataFiles> ls -l

total 4540

-rwxr-xr-x 1 oracle oracle 722400 Jan 26 2017 filegroup1.jar

-rwxr-xr-x 1 oracle oracle 3708288 Jan 26 2017 filegroup2.jar

-rwxr-xr-x 1 oracle oracle 212871 Jan 26 2017 filegroup3.jar

> pwd

/software/oracle/admin/INSTALLER/database/stage/Components/oracle.network.rsf/12.2.0.1.0/1/DataFiles

> $ORACLE_HOME/jdk/bin/jar tvf filegroup2.jar |grep libntcps

356034 Thu Dec 15 07:31:24 SGT 2016 lib/libntcps12_ee.a.dbl

> $ORACLE_HOME/jdk/bin/jar xvf ~/filegroup2.jar lib/libntcps12_ee.a.dbl

inflated: lib/libntcps12_ee.a.dbl

> ls -la libntcps*

-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a

-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl

> cp -p libntcps12.a libntcps12.a.orig

-- relink

> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 24 14:39:17 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

> ps -f |grep tns

oracle 20494 20272 0 14:39 pts/0 00:00:00 grep --color=auto tns

> ps -fu oracle

UID PID PPID C STIME TTY TIME CMD

oracle 14048 14046 0 11:20 ? 00:00:01 sshd: oracle@pts/1

oracle 14049 14048 0 11:20 pts/1 00:00:00 -ksh

oracle 19604 1 0 14:04 ? 00:00:00 /software/oracle/product/12.2.0/bin/tnslsnr LISTENER_ORCL -inherit

oracle 20271 20269 0 14:28 ? 00:00:00 sshd: oracle@pts/0

oracle 20272 20271 0 14:28 pts/0 00:00:00 -ksh

oracle 20500 20272 0 14:40 pts/0 00:00:00 ps -fu oracle

> lsnrctl stop LISTENER_ORCL

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 24-JAN-2018 14:40:10

Copyright (c) 1991, 2016, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1588)))

The command completed successfully

> relink all

writing relink log to: /software/oracle/product/12.2.0/install/relink.log

> ls -la libntcps*

-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a

-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a.orig

-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl

> ls -l /software/oracle/product/12.2.0/install/relink.log

-rw-r--r-- 1 oracle oracle 169637 Jan 24 14:41 /software/oracle/product/12.2.0/install/relink.log

#highlighted are updated files

ls -lrt

...

-rw-r--r-- 1 oracle oracle 22215298 Jan 24 12:01 libcommon12.a

-rw-r--r-- 1 oracle oracle 36700774 Jan 24 12:01 libgeneric12.a

-rwxr-xr-x 1 oracle oracle 2032800 Jan 24 12:01 libasmclntsh12.so

-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl

-rw-r--r-- 1 oracle oracle 14683 Jan 24 14:40 libskgxn2.so

-rw------- 1 oracle oracle 137 Jan 24 14:40 ldflagsO

-rw-r--r-- 1 oracle oracle 137 Jan 24 14:40 ldflags

-rw-r--r-- 1 oracle oracle 2872 Jan 24 14:40 ntcontab.o

-rw-r--r-- 1 oracle oracle 1968 Jan 24 14:40 nnfgt.o

-rw-r--r-- 1 oracle oracle 9619442 Jan 24 14:40 libn12.a

-rwxr-xr-x 1 oracle oracle 8033736 Jan 24 14:40 libclntshcore.so.12.1

-rw-r--r-- 1 oracle oracle 1687689 Jan 24 14:40 clntshcore.map

lrwxrwxrwx 1 oracle oracle 21 Jan 24 14:40 libclntshcore.so -> libclntshcore.so.12.1

-rwxr-xr-x 1 oracle oracle 71653096 Jan 24 14:40 libclntsh.so.12.1

-rw-r--r-- 1 oracle oracle 5080271 Jan 24 14:40 clntsh.map

lrwxrwxrwx 1 oracle oracle 17 Jan 24 14:40 libclntsh.so -> libclntsh.so.12.1

lrwxrwxrwx 1 oracle oracle 12 Jan 24 14:40 libclntsh.so.11.1 -> libclntsh.so

lrwxrwxrwx 1 oracle oracle 12 Jan 24 14:40 libclntsh.so.10.1 -> libclntsh.so

-rwxr-xr-x 1 oracle oracle 2222528 Jan 24 14:40 libocci.so.12.1

lrwxrwxrwx 1 oracle oracle 15 Jan 24 14:40 libocci.so -> libocci.so.12.1

-rwxr-xr-x 1 oracle oracle 2553264 Jan 24 14:40 libagtsh.so.1.0

lrwxrwxrwx 1 oracle oracle 15 Jan 24 14:40 libagtsh.so -> libagtsh.so.1.0

-rwxr-xr-x 1 oracle oracle 194936 Jan 24 14:40 libsrvmm2.so

-rw-r--r-- 1 oracle oracle 1192447 Jan 24 14:40 libskgxp12.so

Step 2 - create wallet

Use oracle wallet manager - owm to create an empty wallet.

Step 3 - create self-signed cert

ORCL:localhost:/software/oracle/admin/ORCL/wallet> orapki wallet add -wallet ./ -pwd Oracle123$ \
> -dn "CN=`hostname`, OU=DBA, O=mm, L=SG, ST=SG, C=SG" \
> -keysize 1024 -self_signed -validity 3650
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.
ORCL:localhost:/software/oracle/admin/ORCL/wallet> orapki wallet display -wallet ./ -pwd Oracle123$
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
Subject: C=SG,ST=SG,L=SG,O=mm,OU=dba,CN=dbmate-cert
User Certificates:
Subject: CN=localhost,OU=DBA,O=mm,L=SG,ST=SG,C=SG
Trusted Certificates:
Subject: CN=localhost,OU=DBA,O=mm,L=SG,ST=SG,C=SG

Step 4 - verify TCPS

-- config sqlnet

ORCL:localhost:/software/oracle/product/12.2.0/network/admin> cat sqlnet.ora

# sqlnet.ora Network Configuration File: /software/oracle/product/12.2.0/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /software/oracle/admin/ORCL/wallet )

)

)

ORCL:localhost:/software/oracle/product/12.2.0/network/admin> cat listener.ora

# listener.ora Network Configuration File: /software/oracle/product/12.2.0/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER_ORCL =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1588))

(ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /software/oracle/admin/ORCL/wallet )

)

)

--tnsnames.ora

ORCL_SSL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL)

)

)

ORCL:localhost:/software/oracle/product/12.2.0/network/admin> sqlplus perfstat@ORCL_SSL

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 26 11:00:54 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter password:

Last Successful login time: Fri Jan 26 2018 11:00:14 +08:00

Connected to:

Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

SQL> select sys_context('userenv','network_protocol') from dual;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')

--------------------------------------------------------------------------------

tcps

SQL> exit

Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

ORCL:localhost:/software/oracle/product/12.2.0/network/admin> tnsping ORCL_SSL

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 26-JAN-2018 11:01:17

Copyright (c) 1997, 2016, Oracle. All rights reserved.

Used parameter files:

/software/oracle/product/12.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))

OK (10 msec)

Step 5. Configure http2 for APEX Embedded web server

Set http2-port and http2-protocol in the XDB configuration:
See Note 942945.1

5.1 Set dispatcher for TCPS in database parameter file

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)','(PROTOCOL=TCPS) (SERVICE=ORCLXDB)'

5.2 set apex /Manage Instance/Requires HTTPS = always.

My database is 12.2c , the APEX http port was 8888, I am using same port for https , as no more HTTP will be supported.

SQL> select dbms_xdb_config.gethttpport() from dual;

DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
8888

SQL> exec dbms_xdb_config.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL> select dbms_xdb_config.gethttpport() from dual;

DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
8080

SQL> call DBMS_XDB_CONFIG.SETLISTENERENDPOINT(2, null, 8888,2);

Call completed.

SQL> select dbms_xdb_config.gethttpport() from dual;

DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
8080

select extractValue(value(x),'/httpconfig/http2-protocol', 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') "Protocol"
2 , extractValue(value(x),'/httpconfig/http2-port', 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') "Port#"
3 from table(xmlsequence(extract(xdburitype('/xdbconfig.xml').getXML(),'/xdbconfig/sysconfig/protocolconfig/httpconfig'))) x
4 /

Protocol
--------------------------------------------------------------------------------
Port#
--------------------------------------------------------------------------------
tcps
8888

Cheers!

MiDBA

Sunday, January 28, 2018

config HTTPS for APEX 5.1 running on SE2

About Me

Save your time!

Translate

Popular Posts

Labels

Blog Archive

Structured Data