Ler arquivo de certificado digital usando PLSQL

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
elton.souza
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 38
Registrado em: Qua, 30 Jan 2013 6:22 pm

Boa tarde pessoal, tudo bem?

Preciso ler um arquivo de certificação digital cuja extensão é PFX usando PLSQL. Já pesquisei na internet e não encontrei muita coisa em PLSQL. Alguém ai tem algum ideia de como posso realizar esse procedimento de leitura?

Fico aguardando amigos uma possível ajuda de vocês.

Obrigado
Tinho
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 317
Registrado em: Seg, 16 Nov 2009 4:50 pm
Localização: São Paulo - SP

Amigo,

Dei uma breve pesquisada. No entanto não achei nenhuma resposta específica que pudesse responder diretamente a sua dúvida.

Mas na minha opinião pessoal acredito que você não consiga abrir um arquivo de um certificado digital sem ter um software específico para isso ou alguma chave decodificadora, mesmo porque ele contém o conteúdo que compõe a assinatura, garantia, integridade da comunicação envolvida entre as pontas. Mesmo assim acredito que caso você consiga fazer isso, teria de utilizar alguns recursos java.

No entanto, achei alguns artigos dos quais estou postando alguns trechos, que talvez sirva como diretriz para o que você está pesquisando e te dar maior clareza sobre o assunto, não sei você já os tinhas visto antes, de qualquer maneira segue abaixo:

Este aqui diz a respeito de arquivos PKX:

http://www.oracle.com/technetwork/artic ... 92744.html

Using PFX and PEM Certificate Formats with Keystores

by Arvind Tiwari
09/27/2006

Abstract

Public Key Cryptography Standards #12 (PKCS#12) specifies a portable format for storing and transporting user or server private keys, public keys, and certificates. It is a binary format, and these files are also known as PFX files. Developers often need to transform PFX files to some different format, such as PEM or JKS, so that they can be used by standalone Java clients using SSL communication, or WebLogic Server. This article describes how to export the private key, public key, and certificate from a PFX file and create JKS or PEM files from these artifacts.

Introduction

PFX certificates are usually created for two purposes:
1.
Client certificate: This is usually installed on a client's browser and provides user identity to the server at the other end. It also provides a public key and certificate to the server. This public key will be used by the server to encrypt any data exchange between these two parties.

2.
Server certificate: This will be installed on the server side and provides server identity, certificate, and public key information to clients that try to establish a connection. The certificate and public key are used for handshaking and further data encryption between client and server.

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

http://www.oracle.com/technetwork/artic ... 96724.html

Then, we need to combine the private and public keys into a single file. The .cer file contains the public key, the .pvk contains the private key and the .pfx file is the exchange file that contains both keys. Keep in mind that certificates generated in this way should not be used in production environment, so you should request a certificate to a trusted third-party certificate authority.
Os demais tratam de uma maneira generalizada como o Oracle trabalha com certificados digitais:
http://docs.oracle.com/cd/A97630_01/app ... _http2.htm

SET_WALLET Procedure
This procedure sets the Oracle wallet used for all HTTP requests over Secured Socket Layer (SSL), namely HTTPS. When the UTL_HTTP package communicates with an HTTP server over SSL, the HTTP server presents its digital certificate, which is signed by a certificate authority, to the UTL_HTTP package for identification purpose. The Oracle wallet contains the list of certificate authorities that are trusted by the user of the UTL_HTTP package. An Oracle wallet is required to make an HTTPS request.

To set up an Oracle wallet, use the Oracle Wallet Manager to create a wallet. In order for the HTTPS request to succeed, the certificate authority that signs the certificate of the remote HTTPS Web server must be one trust point set in the wallet. When a wallet is created, it is populated with a set of well-known certificate authorities as trust points. If the certificate authority that signs the certificate of the remote HTTPS Web server is not among the trust points, or the certificate authority has new root certificates, you should obtain the root certificate of that certificate authority and install it as a trust point in the wallet using Oracle Wallet Manager.

See Also:
Oracle Advanced Security Administrator's Guide for more information on Wallet Manager


Syntax
UTL_HTTP.set_wallet (
path IN VARCHAR2,
password IN VARCHAR2 DEFAULT NULL);

https://www.stanford.edu/dept/itss/docs ... thmeth.htm

Public Key Infrastructure-Based Authentication

Authentication systems based on public key infrastructure issue digital certificates to user clients, which use them to authenticate directly to servers in the enterprise without directly involving an authentication server. Oracle provides a public key infrastructure (PKI) for using public keys and certificates, consisting of the following components:
•Authentication and secure session key management using Secure Sockets Layer (SSL).
•Oracle Call Interface (OCI) and PL/SQL functions to sign user-specified data using a private key and certificate, and verify the signature on data using a trusted certificate.
•Trusted certificates, identifying third-party entities that are trusted as signers of user certificates when an identity is being validated as the entity it claims to be. When the user's certificate is being validated, the signer is one of the factors checked, using trust points or a trusted certificate chain of certificate authorities stored in the validating system. If there are several levels of trusted certificates in that chain, a trusted certificate at a lower level is simply trusted without needing to have all its higher level certificates reverified.
•Oracle wallets, which are data structures that contain a user private key, a user certificate, and the user's set of trust points (trusted certificate authorities).
•OracleAS Certificate Authority, a component of the Oracle Identity Management infrastructure, which provides an integrated solution for provisioning X.509v3 certificates for use by individuals, applications, and servers, which require certificates for PKI-based operations such as authentication, SSL, S/MIME, and so on.
•Oracle Wallet Manager, a standalone Java application used to manage and edit the security credentials in Oracle wallets, providing the following operations: •Protects user keys
•Manages X.509 Version 3 certificates on Oracle clients and servers
•Generates a public-private key pair and creates a certificate request for submission to a certificate authority
•Installs a certificate for the entity
•Configures trusted certificates for the entity
•Creates wallets
•Opens a wallet to enable access to PKI-based services

•X.509 Version 3 certificates obtained from (and signed by) a trusted entity, a certificate authority. Such a certificate certifies, because the certificate authority is trusted, that the requesting entity's information is correct and that the public key on the certificate belongs to the identified entity. The certificate is loaded into an Oracle wallet to enable future authentication.

http://docs.oracle.com/cd/B14117_01/ser ... ossary.htm

certificate
Also called a digital certificate. An ITU x.509 v3 standard data structure that securely binds an identity to a public key.

A certificate is created when an entity's public key is signed by a trusted identity, a certificate authority The certificate ensures that the entity's information is correct and that the public key actually belongs to that entity.

A certificate contains the entity's name, identifying information, and public key. It is also likely to contain a serial number, expiration date, and information about the rights, uses, and privileges associated with the certificate. Finally, it contains information about the certificate authority that issued it.

certificate authority
A trusted third party that certifies that other entities--users, databases, administrators, clients, servers--are who they say they are. When it certifies a user, the certificate authority first seeks verification that the user is not on the certificate revocation list (CRL), then verifies the user's identity and grants a certificate, signing it with the certificate authority's private key. The certificate authority has its own certificate and public key which it publishes. Servers and clients use these to verify signatures the certificate authority has made. A certificate authority might be an external company that offers certificate services, or an internal organization such as a corporate MIS department.

http://ebstechnical.blogspot.com.br/201 ... plsql.html

Above sample code will post the data taken from the cursor and construct name-value string to the website mentioned in p_url_in variable by POST method.
In the above I used UTL_HTTP.SET_WALLET, you have to create certificate and load it in the Oracle Wallet Manager. This procedure sets the Oracle wallet used for all HTTP requests over Secured Socket Layer(SSL). When the UTL_HTTP package communicates with an HTTP server over SSL, the HTTP server presents its digital certificate, which is signed by a certificate authority, to the UTL_HTTP package for identification purpose.
To create wallet,
1) open the URL in IE and goto FILE menu, click on Properties.Then click on Certificates button. You will get certificate window, on that goto "details" tab and click on "copy to file". Follow the prompts and choose "Base-64 encoded x.509 (.cer)" radio button. Save that file in the local directory.

2)Goto Oracle Wallet Manager, goto wallet menu and then New. It will prompt you password. This password is used in the code when we call the SET_WALLET procedure. In the Operations menu click on "Import Trusted Certificate" and upload the certificate file from step1 and save.
3) FTP this file(from step2) in binary mode to /etc/Oracle/Wallet

http://oracle.itags.org/wap_q_oracle_275180

2. The wallet can be created by Oracle Wallet Manager (OWM). The wallet must contain the digital certificate of the certificate authority (CA) that signs the certificate of the remote HTTPS Web server. When a wallet is created, OWM populates your wallet with the certs. of some popular CAs. If the CA that signs the cert. of the HTTP server is not one of them, you need to install the cert. of that CA to your wallet via OWM.

http://web.deu.edu.tr/doc/oracle/B14099 ... allets.htm

In the preceding command, module can be wallet (Oracle wallet), crl (certificate revocation list), or cert (PKI digital certificate). The available commands depend on the module you are using. For example, if you are working with a wallet, then you can add a certificate or a key to the wallet with the add command. The following example adds the user certificate located at /private/lhale/cert.txt to the wallet located at ORACLE_HOME/wallet/ewallet.p12:

CRLs must be managed with orapki. This utility creates a hashed value of the CRL issuer's name to identify the CRLs location in your system. If you do not use orapki, your Oracle server cannot locate CRLs to validate PKI digital certificates. The following sections describe CRLS, how you use them, and how to use orapki to manage them:

Section 15.2.5.1, "About Certificate Validation with Certificate Revocation Lists"


Section 15.2.5.2, "Certificate Revocation List Management"



15.2.5.1 About Certificate Validation with Certificate Revocation Lists

The process of determining whether a given certificate can be used in a given context is referred to as certificate validation. Certificate validation includes determining that

A trusted certificate authority (CA) has digitally signed the certificate


The certificate's digital signature corresponds to the independently-calculated hash value of the certificate itself and the certificate signer's (CA's) public key


The certificate has not expired


The certificate has not been revoked

The SSL network layer automatically performs the first three validation checks, but you must configure certificate revocation list (CRL) checking to ensure that certificates have not been revoked. CRLs are signed data structures that contain a list of revoked certificates. They are usually issued and signed by the same entity who issued the original certificate.
Espero ter ajudado de alguma forma.

Qualquer coisa posta mais detalhes.

Att.,
Responder
  • Informação