Oracle Database 18c Password Authentication with Microsoft Active Directory

Oracle database 18c introduces a new feature 'Integration of Active Directory Services with Oracle Database'. It gives us a way to authenticate and authorize users directly with Microsoft Active Directory. Oracle database users and roles can map directly to Active Directory users and groups without using Oracle Enterprise User Security (EUS) or another intermediate directory service. Here, I am going to demonstrate how to make it work step by step.
 
Environment:

  Database Server - Oracle Database 18c Enterprise Edition on Oracle Linux Server 7.5
  Microsoft Active Directory Server - Windows Server 2012 R2

Restrict OEM agent 13.2 to use TLS protocol TLSv1.2

Oracle Enterprise Manager 13.2 agent, by default, is configured to use TLS protocol TLSv1.0, TLSv1.1 and TLSv1.2,all versions of TLS when OEM 13.2 released. To restrict agent to use only TLSv1.2 as following,

ORA-01017 invalid username/password after 11g database upgraded to 12.2 pluggable database

Oracle database 11.2.0.3 was upgraded to 12.2.0.1 and plugged into CDB as pluggable database with 'create pluggable database ... using ...' command. The container database (CDB) was creted with 12.2 dbca and all built-in users' passwords (sys,system,dbsnmp, etc) were set to same ones as in original 11g database.

Birthday Attacks against TLS ciphers Used by OEM 13.2 Agent

Oracle Enterprise Manager 13.2 uses following cipher suites as default value for SSL communication,
 SSL_RSA_WITH_RC4_128_MD5, SSL_RSA_WITH_RC4_128_SHA and SSL_RSA_WITH_3DES_EDE_CBC_SHA
 
Which support ciphers as listed by script CipherScan.bsh (script details can be found at the end of this post),

[oracle@host01]$ ./CipherScan.bsh host01.dbaplus.ca:3872
 Following Cipher(s) is/are supported on server host01.dbaplus.ca:3872
     ECDHE-RSA-AES128-SHA256
     ECDHE-RSA-AES128-SHA
     DHE-RSA-AES128-SHA256
     DHE-RSA-AES128-SHA
     AES128-SHA256
     AES128-SHA
     DES-CBC3-SHA

Here, cipher DES-CBC3-SHA could introduce a security threat of 'Birthday Attack against TLS ciphers with 64bit block size vulnerability'. Remote attackers can obtain cleartext data via a birthday attack against a long-duration encrypted session of all versions of SSL/TLS protocol supporting cipher suites which use DES, 3DES, IDEA or RC2 as the symmetric encryption cipher in CBC mode.

It can be disabled/removed by configuring agent's properties. Before changing the agent property, one of following method can be used to check current setting,
* Run command: <AGENT_INST_HOME>/bin/emctl getproperty agent -name SSLCipherSuites or
* Check agent property file by running:  grep -i <AGENT_INST_HOME>/sysman/config/emd.properties
 

[oracle@host01]$ cd /u01/software/em/agent/agent_inst
[oracle@host01]$ ./bin/emctl getproperty agent -name SSLCipherSuites
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
SSLCipherSuites is unset; default value is SSL_RSA_WITH_RC4_128_MD5:SSL_RSA_WITH_RC4_128_SHA:SSL_RSA_WITH_3DES_EDE_CBC_SHA
[oracle@host01]$
[oracle@host01]$ grep -i 'SSLCipherSuites' ./sysman/config/emd.properties
[oracle@host01]$

The property is using default value and not set yet.
Remove weak cipher suite by setting SSLCipherSuites property of agent,

[oracle@host01]$ cd /u01/software/em/agent/agent_inst                      
[oracle@host01]$ ./bin/emctl setproperty agent -name SSLCipherSuites -value TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
EMD setproperty succeeded

If the Agent is running on AIX platform, use the cipher SSL_RSA_WITH_AES_128_CBC_SHA only.

[oracle@host01]$ ./bin/emctl setproperty agent -name SSLCipherSuites -value SSL_RSA_WITH_AES_128_CBC_SHA

Check current value,

[oracle@host01]$ cd /u01/software/em/agent/agent_inst
[oracle@host01]$ ./bin/emctl getproperty agent -name SSLCipherSuites
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
SSLCipherSuites=TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256
[oracle@host01]$
[oracle@host01]$ grep -i ./sysman/config/'SSLCipherSuites' emd.properties
SSLCipherSuites=TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256
[oracle@host01]$

Restart agent,

[oracle@host01]$ /u01/software/em/agent/agent_13.2.0.0.0/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.
[oracle@host01]$
[oracle@host01]$ /u01/software/em/agent/agent_13.2.0.0.0/bin/emctl start agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Starting agent ............... started.

Verify the supported ciphers,

[oracle@host01]$ ./CipherScan.bsh host01.dbaplus.ca:3872
 Following Cipher(s) is/are supported on server host01.dbaplus.ca:3872
     AES128-SHA

There is no threat (weak cipher) any more.
 
Appendex.  Script file CipherScan.bsh

#!/usr/bin/bash
# -----------------------------------------------
# Scan available Cipher on given server with port
# Usage:
#    CipherScan.bsh  <SERVER_IP>:<PORT>
#
# For example,
#    ./CipherScan.bsh  host01.dbaplus.ca:3872
# -----------------------------------------------
SERVER=$1
CIPHER_SUPPORTED=$(openssl ciphers 'ALL:eNULL' | sed -e 's/:/ /g')
echo -e "\n Following Cipher(s) is/are supported on server $SERVER \n"
for cipher in ${CIPHER_SUPPORTED[@]}
do
    result=$(echo -n | openssl s_client -cipher "$cipher" -connect $SERVER 2>&1)
    if [[ "$result" =~ "Connection refused" ]] ; then
        echo  $result
        break
    fi
    if ! [[ "$result" =~ ":error:" ]] ; then
          echo "     "$cipher
    fi
done

Using UTL_HTTP to access SSL (https) web sites

Demonstration in this article is given on Oracle Database - Enterprise Edition - version 11.2.0.3

Not like non-SSL (http) web site, accessing SSL (https) web sites with UTL_HTTP  needs additional configuration steps outside of PL/SQL programming as following.

1. Create wallet on database server where UTL_HTTP is called from

$ $ORACLE_HOME/bin/orapki wallet create -wallet /u01/app/oracle/admin/DB01/wallet -auto_login -pwd Pasword11203
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

If -auto_login option is not used, UTL_HTTP.SET_WALLET has to be called with the wallet password (here given Password11203). Check wallet built-in certificates:

$ $ORACLE_HOME/bin/orapki wallet display -wallet /u01/app/oracle/admin/DB01/wallet
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
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

2. Import certificate of the web sites

Certificate file GoogleAccount.cer is exported from FireFox while https://account.google.com is open. If you do not know how to export certificate of your SSL web sites, try to GOOGLE with  "export ssl certificate from Internet Explorer Firefox".

$ $ORACLE_HOME/bin/orapki wallet add -wallet /u01/app/oracle/admin/DB01/wallet -trusted_cert -cert  /tmp/GoogleAccount.cer -pwd Pasword11203
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

$ $ORACLE_HOME/bin/orapki wallet display -wallet /u01/app/oracle/admin/DB01/wallet
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=GeoTrust Global CA,O=GeoTrust Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
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
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

The root certificate (CN=GeoTrust Global CA,O=GeoTrust Inc.,C=US) of https://account.google.com is imported successfully.

3. Test UTL_HTTP

system@DB01> DECLARE
  2    v_http_request    UTL_HTTP.REQ;
  3    v_http_response   UTL_HTTP.RESP;
  4    v_text            VARCHAR2(255);
  5  BEGIN
  6    --UTL_HTTP.SET_PROXY ('username:password@proxy-server:port');  -- if proxy is needed
  7    UTL_HTTP.SET_WALLET('file:/u01/app/oracle/admin/DB01/wallet');
  8    v_http_request := UTL_HTTP.BEGIN_REQUEST(url => 'https://accounts.google.com', method => 'GET', http_version => 'HTTP/1.1');
  9    v_http_response := UTL_HTTP.GET_RESPONSE(v_http_request);
 10    UTL_HTTP.READ_TEXT(v_http_response, v_text, 100);
 11    DBMS_OUTPUT.PUT_LINE (v_text);
 12    UTL_HTTP.END_RESPONSE(v_http_response);
 13  END;
 14  /

<!DOCTYPE html>
<html lang="en">
  <head>
  <meta charset="utf-8">
  <meta content="width=300, init

PL/SQL procedure successfully completed.

If the wallet is created without '-auto_login' option, UTL_HTTP.SET_WALLET has to be called as UTL_HTTP.SET_WALLET('file:/u01/app/oracle/admin/DB01/wallet','Pasword11203').Here, 'Pasword11203' is password of the wallet.If the wallet is not configured properly or required certificate is not imported into wallet, UTL_HTTP will return 'ORA-29024: Certificate validation failure',

system@DB01> DECLARE
  2    v_http_request    UTL_HTTP.REQ;
  3    v_http_response   UTL_HTTP.RESP;
  4    v_text            VARCHAR2(255);
  5  BEGIN
  6    --UTL_HTTP.SET_PROXY ('username:password@proxy-server:port');  -- if proxy is needed
  7    --UTL_HTTP.SET_WALLET('file:/u01/app/oracle/admin/DB01/wallet');
  8    v_http_request := UTL_HTTP.BEGIN_REQUEST(url => 'https://accounts.google.com', method => 'GET', http_version => 'HTTP/1.1');
  9    v_http_response := UTL_HTTP.GET_RESPONSE(v_http_request);
 10    UTL_HTTP.READ_TEXT(v_http_response, v_text, 100);
 11    DBMS_OUTPUT.PUT_LINE (v_text);
 12    UTL_HTTP.END_RESPONSE(v_http_response);
 13  END;
 14  /
declare
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure
ORA-06512: at line 8