Início / dba / Perguntas / 225041
Accepted
Gandolf989
Asked: 2018-12-15 14:14:16 +0800 CST

Preciso de ajuda para criar um ouvinte criptografado para meu banco de dados 11gR2 usando uma carteira e criptografia SHA1

  • 772

Estamos usando 11.2.0.4. Como as conexões Oracle não são criptografadas por padrão e acessam dados de identificação pessoal (PII) em nosso aplicativo, somos obrigados a ir a um ouvinte criptografado. Estou tendo problemas para fazer funcionar. Também estamos usando Transparent Data Encryption (TDE). Alguma sugestão sobre o que estou fazendo de errado? Eu tenho a saída da minha conexão, arquivo de log do ouvinte e arquivo de rastreamento abaixo.

@ > connect connect system/pwd@MYAPP
ERROR:
ORA-29080: Message 29080 not found;  product=RDBMS; facility=ORA

<msg time='2018-12-14T11:10:03.640-05:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='MYORACLEVM101.corp.com'
 host_addr='10.1.3.209'>
 <txt>14-DEC-2018 11:10:03 * &lt;unknown connect data&gt; * 12561
 </txt>
</msg>
<msg time='2018-12-14T11:10:03.641-05:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='MYORACLEVM101.corp.com'
 host_addr='10.1.3.209'>
 <txt>TNS-12561: TNS:unknown error
 </txt>
</msg>
2018-12-14 17:11:54.058558 : nstoSetupTimeout:ATO enabled for ctx=0x0x17848c0, val=60000(millisecs)
2018-12-14 17:11:54.059097 : nstoUpdateActive:Active timeout is 0 (see nstotyp)
2018-12-14 17:11:54.059407 : nsopen:opening transport...
2018-12-14 17:11:54.059718 : nttcnp:getting sockname
2018-12-14 17:11:54.060053 : nttcnp:getting peername
2018-12-14 17:11:54.060355 : nttcnr:waiting to accept a connection.
2018-12-14 17:11:54.060645 : nttcnr:getting sockname
2018-12-14 17:11:54.060965 : nttcnr:connected on ipaddr 10.1.3.209
2018-12-14 17:11:54.061271 : nttvlser:valid node check on incoming node 10.1.3.209
2018-12-14 17:11:54.061570 : nttvlser:Accepted Entry: 10.1.3.209
2018-12-14 17:11:54.061885 : nttcon:set TCP_NODELAY on 14
2018-12-14 17:11:54.062184 : ntzAllocate:allocating 304 bytes of memory.
2018-12-14 17:11:54.062511 : nsopen:transport is open
2018-12-14 17:11:54.062818 : ntzcontrol:Command = 1125
2018-12-14 17:11:54.063107 : ntzcontrol:negotiated cipher retrieval failed with error 29031
2018-12-14 17:11:54.063459 : nsnainit:inf->nsinfflg[0]: 0xd inf->nsinfflg[1]: 0xd
2018-12-14 17:11:54.063765 : nsopen:global context check-in (to slot 4) complete
2018-12-14 17:11:54.064066 : nsanswer:deferring connect attempt; at stage 3
2018-12-14 17:11:54.064403 : ntzcontrol:Command = 1123
2018-12-14 17:11:54.064800 : ntzdosecneg:SSL handshake returned "in progress" status
2018-12-14 17:11:54.065124 : ntzcontrol:Command = 1124
2018-12-14 17:11:54.065439 : nsevdansw:exit
2018-12-14 17:11:54.066212 : ntzcontrol:Command = 1123
2018-12-14 17:11:54.068626 : ntzdosecneg:SSL handshake done
2018-12-14 17:11:54.068925 : nsevdansw:exit
2018-12-14 17:11:54.069517 : nscon:doing connect handshake...
2018-12-14 17:11:54.069861 : ntznzosread:read in 238 bytes
2018-12-14 17:11:54.070152 : ntznzosread:no data remaining to be read from SSL buffer.
2018-12-14 17:11:54.070450 : nscon:got NSPTCN packet
2018-12-14 17:11:54.070746 : nsevdansw:exit
2018-12-14 17:11:54.071044 : ntzcontrol:Command = 3
2018-12-14 17:11:54.071367 : ntzcontrol:Command = 7
2018-12-14 17:11:54.071664 : ntzcontrol:unknown command 7 - calling underlying protocol adapter
2018-12-14 17:11:54.071961 : nscon:sending NSPTRD packet
2018-12-14 17:11:54.072299 : nstimarmed:no timer allocated
2018-12-14 17:11:54.072591 : nstoClearTimeout:ATO disabled for ctx=0x0x17848c0
2018-12-14 17:11:54.072874 : nstoClearTimeout:STO disabled for ctx=0x0x17848c0
2018-12-14 17:11:54.073156 : nstoClearTimeout:RTO disabled for ctx=0x0x17848c0
2018-12-14 17:11:54.073450 : nstoClearTimeout:PITO disabled for ctx=0x0x17848c0
2018-12-14 17:11:54.073733 : nstoUpdateActive:Active timeout is -1 (see nstotyp)
2018-12-14 17:11:54.074015 : ntzcontrol:Command = 14
2018-12-14 17:11:54.074307 : ntzcontrol:Command = 15
2018-12-14 17:11:54.074615 : nsclose:closing transport
2018-12-14 17:11:54.074929 : nsclose:global context check-out (from slot 4) complete
2018-12-14 17:11:54.075237 : nsgldissolve:Deallocating cxd 0x1784220.
2018-12-14 17:11:54.075793 : nstoSetupTimeout:ATO enabled for ctx=0x0x17848c0, val=60000(millisecs)
2018-12-14 17:11:54.076090 : nstoUpdateActive:Active timeout is 0 (see nstotyp)
2018-12-14 17:11:54.076394 : nsopen:opening transport...
2018-12-14 17:11:54.076709 : nsopen:transport is open
2018-12-14 17:11:54.077031 : nsnainit:inf->nsinfflg[0]: 0xd inf->nsinfflg[1]: 0xd
2018-12-14 17:11:54.077348 : nsopen:global context check-in (to slot 4) complete
2018-12-14 17:11:54.077647 : nsanswer:deferring connect attempt; at stage 5
2018-12-14 17:11:54.077951 : nscon:doing connect handshake...
2018-12-14 17:11:54.078255 : nscon:got NSPTCN packet
2018-12-14 17:11:54.078547 : nsevdansw:exit
2018-12-14 17:11:54.078865 : nscon:sending NSPTAC packet
2018-12-14 17:11:54.079158 : nscon:connect handshake is complete
2018-12-14 17:11:54.079463 : nscon:nsctxinf[0]=0xd, [1]=0xc
2018-12-14 17:11:54.079823 : nsevdansw:exit
2018-12-14 17:11:54.080151 : nsrdr:got NSPTMK packet
2018-12-14 17:11:54.080460 : nsglauthorized:Authenticated user: 504
2018-12-14 17:11:54.080749 : nstoClearTimeout:ATO disabled for ctx=0x0x17848c0
2018-12-14 17:11:54.081033 : nstoUpdateActive:Active timeout is -1 (see nstotyp)
2018-12-14 17:11:54.081326 : nstoControlATO:ATO disabled for ctx=0x0x17848c0
2018-12-14 17:11:54.081644 : nsgcsss:ons_subscriber_status=1
2018-12-14 17:11:54.083110 : nsdo:632 bytes to NS buffer
2018-12-14 17:11:54.083437 : nsdo:466 bytes to NS buffer
2018-12-14 17:11:54.083735 : nstimarmed:no timer allocated
2018-12-14 17:11:54.084031 : nsclose:closing transport
2018-12-14 17:11:54.084342 : nsclose:global context check-out (from slot 4) complete
2018-12-14 17:11:54.084648 : nsgldissolve:Deallocating cxd 0x1784220.

Eu criei a carteira:

 orapki wallet create  -wallet "${WALLET_DIRECTORY}" -pwd "${LSNRPWD}" -auto_login
 orapki wallet add     -wallet "${WALLET_DIRECTORY}" -pwd "${LSNRPWD}" -dn "CN=`hostname`,OU=EM,O=Organization,L=City,ST=State,C=US" -self_signed -keysize 2048 -sign_alg sha256 -validity 730
 orapki wallet display -wallet "${WALLET_DIRECTORY}" -pwd "${LSNRPWD}"
 orapki wallet export  -wallet "${WALLET_DIRECTORY}" -pwd "${LSNRPWD}" -dn "CN=`hostname`,OU=EM,O=Organization,L=City,ST=State,C=US" -cert ${WALLET_DIRECTORY}/`hostname`-${CURR_TIME}-certificate.crt

meu arquivo listener.ora:

MYAPP_encrypted_listener_11gR2 =
( DESCRIPTION =
 ( address_list =
   ( address = (protocol = tcps)(host = MYORACLEVM101.corp.com)(port = 1520))
 ))

SID_LIST_MYAPP_encrypted_listener_11gR2 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = MYAPP)
      (ORACLE_HOME = /home/oracle/app/product/11.2.0.4)
      (SID_NAME = MYAPP)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = DB12C)
      (ORACLE_HOME = /home/oracle/app/product/12.2.0.1)
      (SID_NAME = DB12C)
    )
  )

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /home/oracle/admin/admin/MYAPP/wallet)
    )
  )

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /home/oracle/admin/wallet/11g_encrypted_listener)
    )
  )

# ADR_BASE_LISTENER = /home/oracle/app
INBOUND_CONNECT_TIMEOUT_LISTENER = 180
# ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON

ACCEPT_SHA1_CERTS=TRUE
ACCEPT_MD5_CERTS=TRUE
# ADD_SSLV3_TO_DEFAULT=TRUE
SSL_VERSION=1.0
DIAG_ADR_ENABLED_MYAPP_encrypted_listener_11gR2=on
TRACE_LEVEL_MYAPP_encrypted_listener_11gR2=ADMIN
TRACE_TIMESTAMP_MYAPP_encrypted_listener_11gR2=true
LOG_DIRECTORY_MYAPP_encrypted_listener_11gR2=/home/oracle/app

#This parameter should be false as listener is not going to authenticate the clients. It is the server process that authenticates the clients.
SSL_CLIENT_AUTHENTICATION=FALSE

Meu arquivo SQLNET.ora:

TCP.VALIDNODE_CHECKING=NO
ADMIN_RESTRICTIONS_LISTENER = ON
REMOTE_OS_AUTHENT = FALSE
ACCEPT_SHA1_CERTS = TRUE
ACCEPT_MD5_CERTS = TRUE
# ADD_SSLV3_TO_DEFAULT = TRUE
SSL_VERSION = 1.0

SQLNET.AUTHENTICATION_SERVICES = (BEQ, TCPS)
# sqlnet.authentication_required = FALSE
# sqlnet.fallback_authentication = TRUE

NAMES.DIRECTORY_PATH = (TNSNAMES)
SSL_CLIENT_AUTHENTICATION = FALSE

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /home/oracle/admin/admin/MYAPP/wallet)
    )
  )

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /home/oracle/admin/wallet/11g_encrypted_listener)
    )
  )

ADR_BASE = /home/oracle/app

# TNSPING.TRACE_LEVEL = ADMIN
# TNSPING.TRACE_DIRECTORY = /home/oracle/app/product/12.2.0.1/network/admin/new_listener/trace_dir

Meu arquivo TNSNAMES.ora:

MYAPP_ENCRYPTED =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = MYORACLEVM101.corp.com)(PORT = 1520))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYAPP)
    )
  )

orapki wallet help
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

wallet:
create [-wallet [wallet]] [[-pwd <pwd>] [-auto_login|-auto_login_local]] | [-auto_login_only]
display [-wallet [wallet]] <-summary> [-pwd <pwd>]
change_pwd [-wallet [wallet]] [-oldpwd <oldpwd>] [-newpwd <newpwd>]
add [-wallet [wallet]] <[-keysize [512|1024|2048|4096]] [-dn [dn]]>
     <-self_signed [-validity [days]] | [-valid_from [mm/dd/yyyy] -valid_until [mm/dd/yyyy]]
                   [-serial_file <file_loc>] | [-serial_num <serial_num>]> <-addext_ski>
     <[-cert [filename]] [-trusted_cert|-user_cert]> [-pwd <pwd>] | [-auto_login_only] [-sign_alg <md5|sha1|sha256|sha384|sha512>]
remove [-wallet [wallet]] [-dn [dn]] [-trusted_cert_all|-trusted_cert|-user_cert|-cert_req]
     [-pwd <pwd>] | [-auto_login_only]
export [-wallet [wallet]] [-dn [dn]] [-cert [filename] | -request [filename]] [-pwd <pwd>]
export_trust_chain [-wallet [wallet]] [-certchain [filename]] [-dn [user_cert_dn]] [-pwd <pwd>]
upload [-wallet [wallet]] [-ldap [host:port]] [-user [user]] [-userpwd [userpwd]] [-pwd <pwd>]
download [-wallet [wallet]] [-ldap [host:nonsslport]] [-user [user]] [-userpwd [userpwd]] [-pwd <pwd>]
jks_to_pkcs12 [-wallet [wallet]] [-pwd <pwd>] [-keystore [keystore]] [-jkspwd [jkspwd]]
     <-aliases [alias:alias..]>
pkcs12_to_jks [-wallet [wallet]] [-pwd <pwd>] [-jksKeyStoreLoc <jksKSloc> -jksKeyStorepwd <jksKS_pwd>]
     [-jksTrustStoreLoc <loc> -jksTrustStorepwd <pwd>]
p11_add [-wallet [wallet]] [-p11_lib <pkcs11Lib>] [-p11_tokenlabel <tokenLabel>]
     [-p11_tokenpw <tokenPassphrase>] [-p11_certlabel <certlabel>] [-pwd <pwd>]
p11_verify [-wallet [wallet]] [-pwd <pwd>]
help
oracle oracle-11g-r2

1 respostas

  1. Best Answer

    Percebi que estava faltando o ssl_client_authentication=FALSE no arquivo listener.ora. Eu tenho isso no arquivo sqlnet.ora também. Eu preciso em ambos os lugares.

    SSL_CLIENT_AUTHENTICATION = FALSE
TRACE_LEVEL_MYAPP_ENCRYPTED_LISTENER_11GR2 = ADMIN
ADR_BASE_MYAPP_ENCRYPTED_LISTENER_11GR2 = /home/oracle/admin
    • 0

relate perguntas