http://www.devx.com/gethelpon/10MinuteSolution/16608/0/page/5
select * from
emp a
where 3 = (select count(distinct(sal)) from emp b
where b.sal >a.sal)
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank =2;
Tuesday, February 14, 2006
oracle top n query
select * from
emp a
where 3 = (select count(distinct(sal)) from emp b
where b.sal >a.sal)
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank =2;
emp a
where 3 = (select count(distinct(sal)) from emp b
where b.sal >a.sal)
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank =2;
Friday, February 10, 2006
VB and oracle
Links i Referred
http://www.orafaq.com/faqmsvb.htm
http://support.microsoft.com/default.aspx?scid=kb;en-us;300596&sd=tech
Create a System DSN in Windows XP
1. Click Start, point to Control Panel, double-click Administrative Tools, and then double-click Data Sources(ODBC).
2. Click the System DSN tab, and then click Add.
3. Click the database driver that corresponds with the database type to which you are connecting, and then click Finish.
4. Type the data source name. Make sure that you choose a name that you can remember. You will need to use this name later.
5. Click Select.
6. Click the correct database, and then click OK.
7. Click OK, and then click OK.
o Create a System DSN in Windows 2000
1. Click Start, point to Programs, point to Administrative Tools, and then double-click Data Sources (ODBC).
NOTE: In Windows 2000 Professional, click Start, point to Settings, click Control Panel, double-click Administrative Tools, and then double-click Data Sources (ODBC).
2. Click the System DSN tab.
3. Click Add.
4. Click the database driver that corresponds with the database type to which you are connecting, and then click Finish.
5. Type the data source name. Make sure to you a name that you will remember. You will need to use this name later.
6. Click Select.
7. Click the correct database, and then click OK.
8. Click OK in the next two dialog boxes.
ow does one connect to Oracle from VB?
Connectivity to Oracle is provided via ODBC or OO4O (Oracle Objects for OLE). For more information about ODBC, read the ODBC FAQ. For information about OO4O, read the OO4O FAQ. Look at this examples:
' DAO Example (Data Access Objects)
Dim wstemp As Workspace
Dim dbtemp As Database
Dim rstemp As Recordset
Set wstemp = DBEngine.Workspaces(0)
Set dbtemp = wstemp.OpenDatabase("", False, False, "ODBC;DSN=Oracle;USR=scott;PWD=tiger")
Set rstemp = dbtemp.OpenRecordset(myquery.Text, dbOpenDynaset, dbSQLPassThrough)
howmany = 0
Combo1.Clear
Do Until rstemp.EOF
msgbox rstemp(0)
rstemp.MoveNext
howmany = howmany + 1
Loop
' RDO Example (Remote Data Objects)
Dim contemp As New rdoConnection
Dim rstemp As rdoResultset
Dim envtemp As rdoEnvironment
Set envtemp = rdoEngine.rdoEnvironments(0)
envtemp.CursorDriver = rdUseServer
' or rdUseOdbc, rdUseNone, rdUseIfNeeded, rdUseClientBatch
With contemp
.Connect = "ODBC;DSN=Oracle;USR=scott;PWD=tiger"
.EstablishConnection rdDriverNoPrompt, false, rdoForwardOnly
' or rdoStatic, rdoKeyset, rdoDynamic
End With
Set rstemp = contemp.OpenResultset("select ...") ' Your SQL here
howmany = 0
With rstemp
Do Until .EOF Or howmany > 2000
msgbox .rdoColumns(0) ' Popup a message box showing the 1st column
.MoveNext
howmany = howmany + 1
Loop
ADO Example
Option Explicit
Public m_adoCnn As New ADODB.Connection
Public m_adoRst As New ADODB.Recordset
Public Sub Command1_Click()
m_adoCnn.ConnectionString = "Provider=MSDAORA;Password=tiger;User ID=scott;Data Source=database"
m_adoCnn.Open
End Sub
Private Sub Form_Unload(Cancel As Integer)
m_adoCnn.Close
End Sub
OO4O Example
Option Explicit
Dim m_oraSession As Object
Dim m_oraDatabase As Object
Private Sub Command1_Click()
Set m_oraSession = CreateObject("OracleInProcServer.XOraSession")
Set m_oraDatabase = m_oraSession.DBOpenDatabase("Database", "user/password", 0&)
MsgBox "Connected to " & m_oraDatabase.Connect & "@" & m_oraDatabase.DatabaseName
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set m_oraDatabase = Nothing
Set m_oraSession = Nothing
End Sub
# Back to top of file
Why is there only one record in my recordset?
When you do a recordcount and it return only one record in the recordset, while you know there are more records, you need to move to the last record before doing the count. Look at this example.
Dim rs As Recordset
rs.MoveLast
TxtNumRows.Text = rs.RecordCount
NOTE: Don't forget to do a rs.MoveFirst to get back to the first record again.
http://www.orafaq.com/faqmsvb.htm
http://support.microsoft.com/default.aspx?scid=kb;en-us;300596&sd=tech
Create a System DSN in Windows XP
1. Click Start, point to Control Panel, double-click Administrative Tools, and then double-click Data Sources(ODBC).
2. Click the System DSN tab, and then click Add.
3. Click the database driver that corresponds with the database type to which you are connecting, and then click Finish.
4. Type the data source name. Make sure that you choose a name that you can remember. You will need to use this name later.
5. Click Select.
6. Click the correct database, and then click OK.
7. Click OK, and then click OK.
o Create a System DSN in Windows 2000
1. Click Start, point to Programs, point to Administrative Tools, and then double-click Data Sources (ODBC).
NOTE: In Windows 2000 Professional, click Start, point to Settings, click Control Panel, double-click Administrative Tools, and then double-click Data Sources (ODBC).
2. Click the System DSN tab.
3. Click Add.
4. Click the database driver that corresponds with the database type to which you are connecting, and then click Finish.
5. Type the data source name. Make sure to you a name that you will remember. You will need to use this name later.
6. Click Select.
7. Click the correct database, and then click OK.
8. Click OK in the next two dialog boxes.
ow does one connect to Oracle from VB?
Connectivity to Oracle is provided via ODBC or OO4O (Oracle Objects for OLE). For more information about ODBC, read the ODBC FAQ. For information about OO4O, read the OO4O FAQ. Look at this examples:
' DAO Example (Data Access Objects)
Dim wstemp As Workspace
Dim dbtemp As Database
Dim rstemp As Recordset
Set wstemp = DBEngine.Workspaces(0)
Set dbtemp = wstemp.OpenDatabase("", False, False, "ODBC;DSN=Oracle;USR=scott;PWD=tiger")
Set rstemp = dbtemp.OpenRecordset(myquery.Text, dbOpenDynaset, dbSQLPassThrough)
howmany = 0
Combo1.Clear
Do Until rstemp.EOF
msgbox rstemp(0)
rstemp.MoveNext
howmany = howmany + 1
Loop
' RDO Example (Remote Data Objects)
Dim contemp As New rdoConnection
Dim rstemp As rdoResultset
Dim envtemp As rdoEnvironment
Set envtemp = rdoEngine.rdoEnvironments(0)
envtemp.CursorDriver = rdUseServer
' or rdUseOdbc, rdUseNone, rdUseIfNeeded, rdUseClientBatch
With contemp
.Connect = "ODBC;DSN=Oracle;USR=scott;PWD=tiger"
.EstablishConnection rdDriverNoPrompt, false, rdoForwardOnly
' or rdoStatic, rdoKeyset, rdoDynamic
End With
Set rstemp = contemp.OpenResultset("select ...") ' Your SQL here
howmany = 0
With rstemp
Do Until .EOF Or howmany > 2000
msgbox .rdoColumns(0) ' Popup a message box showing the 1st column
.MoveNext
howmany = howmany + 1
Loop
ADO Example
Option Explicit
Public m_adoCnn As New ADODB.Connection
Public m_adoRst As New ADODB.Recordset
Public Sub Command1_Click()
m_adoCnn.ConnectionString = "Provider=MSDAORA;Password=tiger;User ID=scott;Data Source=database"
m_adoCnn.Open
End Sub
Private Sub Form_Unload(Cancel As Integer)
m_adoCnn.Close
End Sub
OO4O Example
Option Explicit
Dim m_oraSession As Object
Dim m_oraDatabase As Object
Private Sub Command1_Click()
Set m_oraSession = CreateObject("OracleInProcServer.XOraSession")
Set m_oraDatabase = m_oraSession.DBOpenDatabase("Database", "user/password", 0&)
MsgBox "Connected to " & m_oraDatabase.Connect & "@" & m_oraDatabase.DatabaseName
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set m_oraDatabase = Nothing
Set m_oraSession = Nothing
End Sub
# Back to top of file
Why is there only one record in my recordset?
When you do a recordcount and it return only one record in the recordset, while you know there are more records, you need to move to the last record before doing the count. Look at this example.
Dim rs As Recordset
rs.MoveLast
TxtNumRows.Text = rs.RecordCount
NOTE: Don't forget to do a rs.MoveFirst to get back to the first record again.
Wednesday, February 08, 2006
P2P SSL authentication and JAVA with OpenSSL
This post contains some of my work about java and openssl.
Pre-requisites
install apache, mod_ssl(if it is not available with apache), openssl
configure openssl configuration file openssl.cnf
The Certificate Stuff:
there are 3 important options with openssl.
i.e, genrsa, req,ca
check the openssl help for all the detail input/output description.
creating key and self certified certificate
first create the key..
openssl genrsa -des3 -out ca.key 1024
then use the following command to create the CA certificate
openssl req -new -x509 -days 365 -key ca.key -out ca.crt
now we generate key and certificate request key..
openssl genrsa -des3 -out server.key 1024
certificate request (CSR)
openssl req -new -key server.key -out server.csr
(generating key, request are done at client side and sent across to the CA for
certification)
Command to certify using our own CA.
openssl ca -in -out -keyfile -cert
example: openssl ca -in server.csr -out server.crt -keyfile ca.key -cert ca.crt
this way we can sign any reguest use openssl.cnf file to put certifying
criteria like city name should be or country name should
Now generating keystores so that our application can use SSL certificates for
secured communication.
use pkcs12 facility to export certificates to a pkcs12 store with a command
similar to the following..
for CA..
openssl pkcs12 -export -in cacert.pem -inkey cakey.pem -out ca.p12
for client
penssl pkcs12 -export -in client.crt -inkey client.key -out client.p12
NOTE:
keytool -list -keystore test.p12 -storetype PKCS12
keytool error: java.io.IOException: Set tag error
You could try to clean up your keystore. Execute commands:
openssl pkcs12 -in test.p12 -clcerts -out file.pem
openssl pkcs12 -export -in file.pem -out clean.p12
Using keystores
we need to put the ca keystore in trust store and the client certificate in keystore
in java it is just setting the system properties as shown in the following program and the magic works..
import java.io.IOException;
import java.io.PrintStream;
import java.io.InputStreamReader;
import java.io.BufferedReader;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.io.*;
import java.net.*;
import java.util.*;
public class TestClient {
public TestClient(){
try{
java.security.Security.addProvider(new com.sun.net.ssl.internal.ssl.Provider());
//System.setProperty("javax.net.debug", "all");
System.setProperty"java.protocol.handler.pkgs","com.sun.net.ssl.internal.www.protocol");
//System.setProperty("javax.net.ssl.trustStore", "truststore");
System.setProperty("javax.net.ssl.trustStore", "ca.p12");
System.setProperty("javax.net.ssl.trustStoreType", "pkcs12");
System.setProperty("javax.net.ssl.trustStorePassword","password");
System.out.println("after truststore setp");
System.setProperty("javax.net.ssl.keyStoreType", "pkcs12");
System.setProperty("javax.net.ssl.keyStore", "client.p12");
System.setProperty("javax.net.ssl.keyStorePassword", "password");
System.out.println("after keystore setting");
System.out.println("Sending XML with http ");
URL url = new URL("https://SERVER_USRL");
System.out.println("after url created");
//URLConnection uc = (URLConnection)url.openConnection();
//System.out.println("after connection establisht");
//uc.setDoOutput(true);
//uc.setDoInput(true);
//uc.setAllowUserInteraction(false);
//BufferedReader in = new BufferedReader(new InputStreamReader(url.openStream()));
//System.out.println("The instance of url.openConnection |" +url.openConnection()"|");
com.sun.net.ssl.HttpsURLConnection connection = (com.sun.net.ssl.HttpsURLConnection)url.openConnection();
BufferedReader in = new BufferedReader(new InputStreamReader(connection.getInputStream()));
PrintWriter pw = new PrintWriter(new FileWriter("output.xml"));
String inputLine;
while ((inputLine = in.readLine()) != null)
pw.println(inputLine);
in.close();
pw.close();
}catch(Exception e){
e.printStackTrace();
}
}
public static void main(String arg[]){
System.out.println("before");
TestClient obj= new TestClient();
System.out.println("after");
}
}
General notes about OPENSSL
1)openssl uses openssl.cnf while creating certificate request as well as when we are certifying request with our own CA.
so this is a crucial one, we can change the default options like country name,
city, province etc., and while certifying also this configuration file is used to validate the client CSR file
openssl uses 2 text files while certifying requests.
serial
index.txt
serial will contain the certifiate serial number
intially it contains 01 and after that it will be incremented automatically for each request.
index.txt contains the created certificate information ( i think so) initially
it will contain nothing..
see that this file will have no content in the beginning
may be size should be 0 kb
this 2 things will create lot of errors if there is something wrong.
use online help for setting up openssl and to resolve errors generated during
creating .csr or .crt files..
http://tirian.magd.ox.ac.uk/~nick/openssl-certs/index.shtml
APACHE configuration:
apache needs mod_ssl module for dealing with SSL. Load this module and ssl.conf will be used to create virtualhosts with SSL configurations.
read apache documentation for various attribues in ssl.conf
sample virtualhost configuration in ssl.conf
ServerName testing.cybernetsoft.com
SSLEngine on
ErrorLog logs/ssl_error_log
TransferLog logs/ssl_access_log
SSLCipherSuite ALL:!ADH:!EXPORT56:RC4+RSA:+HIGH:+MEDIUM:+LOW:+SSLv2:+EXP
SSLCertificateFile /etc/httpd/conf/ssl.crt/server.crt
SSLCertificateKeyFile /etc/httpd/conf/ssl.key/server.key
SSLCertificateChainFile /etc/httpd/conf/ssl.crt/ca.crt
SSLCACertificateFile /etc/httpd/conf/ssl.crt/ca.crt
SSLCACertificatePath /etc/httpd/conf/ssl.crt
SSLVerifyDepth 1
HostnameLookups double
#SSLVerifyClient require
#SSLRequire (%(SSL_CLIENT_I_DN_CN) eq %(HTTP_HOST))
#SSLRequire (%(SSL_CLIENT_I_DN_O) eq "Cybernet Software Systems Inc")
# and %{SSL_CLIENT_S_DN_OU} in {"Staff", "CA", "Dev"}
# and %{TIME_WDAY} >= 1 and %{TIME_WDAY} <= 5
# and %{TIME_HOUR} >= 8 and %{TIME_HOUR} <= 20 )
#SLRequire ( %{SSL_CIPHER} !~ m/^(EXP|NULL)/
# and %{SSL_CLIENT_S_DN_O} eq "Snake Oil, Ltd."
# and %{SSL_CLIENT_S_DN_OU} in {"Staff", "CA", "Dev"}
# and %{TIME_WDAY} >= 1 and %{TIME_WDAY} <= 5
# and %{TIME_HOUR} >= 8 and %{TIME_HOUR} <= 20 )
# or %{REMOTE_ADDR} =~ m/^11\.1\.14\.[0-9]+$/
CustomLog logs/ssl_request_log
"%t %h cn %{SSL_CLIENT_S_DN_CN}x remoteaddr %{REMOTE_ADDR}x remotehost %{REMOTE_HOST}x remoteident %{REMOTE_IDENT}x \"%r\" %b"
WebLogicHost 11.1.14.13
WebLogicPort 7001
StatPath true
HungServerRecoverSecs 300
ConnectTimeoutSecs 10
#SSLRequire (%{SSL_CLIENT_S_DN_CN} eq %{REMOTE_HOST})
#SSLRequire (%{SSL_CLIENT_VERIFY} eq "SUCCESS")
WebLogicHost 11.1.14.13
WebLogicPort 7001
SetHandler weblogic-handler
SETTING UP tomcat
Setting up Apache Tomcat and a Simple Apache SOAP Client for SSL Communication.
http://ws.apache.org/soap/docs/install/FAQ_Tomcat_SOAP_SSL.html
create key and sertificate from keytool for server.
keytool -genkey -alias tomcat-sv -dname "CN=harinath.cybernetsoft.com, OU=Java services team, O=Cybernet software systems, L=Chennai,
S=TN, C=IN" -keyalg RSA -keypass changeit -storepass changeit -keystore server.keystore
keytool -export -alias tomcat-cv -storepass changeit -file server.cer -keystore server.keystore
do the same thing for client the following steps worked fine for tomcat
CREATING KEYSTORE FOR TOMCAT:
1) keytool -genkey -alias tomcat-cl -dname "CN=harinath.cybernetsoft.com,OU=J2EE TEAM, O=CSS, L=CHENNAI, S=TN, C=IN" -keyalg RSA -keypass changeit -storepass changeit -keystore client.keystore
2)keytool -export -alias tomcat-cl -storepass changeit -file client.cer -keystore client.keystore
keytool -export -alias tomcat -storepass password -file c.crt -keystore client.keystore
3) and now change ur server.xml for ssl
clientAuth="false" keystorePass="changeit" sslProtocol="TLS"/>
Tomcat by default uses keystore named ".keystore" inside the home directory of
the user.
I think we can provide keystore and truststore and their passwords in connector itself.
Read the tomcat documentation of the version you are using.
LINKS:
http://www.flatmtn.com/computer/Linux-SSLCertificatesApache.html
installing CA Into IE
http://wasd.vsm.com.au/ht_root/other/faq/msie_ca_cert/
http://httpd.apache.org/docs-2.0/
http://www.impetus.us/~rjmooney/projects/misc/clientcertauth.html
http://www.samspublishing.com/articles/article.asp?p=30115&seqNum=11
http://tirian.magd.ox.ac.uk/~nick/openssl-certs/index.shtml
http://www.modssl.org/docs/2.8/ssl_reference.html#table3
http://httpd.apache.org/docs-2.0/mod/mod_ssl.html#sslrequiressl
http://www.samspublishing.com/articles/article.asp?p=30115&seqNum=5
http://www.impetus.us/~rjmooney/projects/misc/clientcertauth.html
sample config file openssl.cnf
http://paf.se/openssl/openssl.cnf.html
A very useful stuff.. on how to create certificates etc.. just a text file but
really useful.
http://rei1.m-plify.net/howtoon-certificates.txt
http://www.openssl.org/docs/apps/ca.html
http://www.drh-consultancy.demon.co.uk/pkcs12faq.html
http://search.netscape.com/ns/boomframe.jsp?query=installing+client+certificate+in+browser&page=1&offset=0&result_url=redir%3Fsrc%3Dwebsearch%26requestId%3Deab426fbf64c7db%26clickedItemRank%3D1%26userQuery%3Dinstalling%2Bclient%2Bcertificate%2Bin%2Bbrowser%26clickedItemURN%3Dhttp%253A%252F%252Fzeus.phys.uconn.edu%252Fprod%252Fdocs%252FcertSetup.html%26invocationType%3D-%26fromPage%3Ddshnnp%26amp%3BampTest%3D1&remove_url=http%3A%2F%2Fzeus.phys.uconn.edu%2Fprod%2Fdocs%2FcertSetup.html
http://www.cs.sunysb.edu/documentation/jsse/api_users_guide.html
http://java.sun.com/j2se/1.4.2/docs/api/javax/net/ssl/SSLSocketFactory.html
http://forum.java.sun.com/thread.jsp?forum=60&thread=204299
http://forum.java.sun.com/thread.jsp?forum=60&thread=525858
http://forum.java.sun.com/thread.jsp?forum=2&thread=258908
http://java.sun.com/j2se/1.4.2/docs/guide/security/jsse/samples/sockets/client/SSLSocketClientWithClientAuth.java
http://java.sun.com/products/jsse/doc/apidoc/com/sun/net/ssl/TrustManagerFactory.html
http://www.javaworld.com/javaworld/javatips/jw-javatip96.html
http://www.javaolympus.com/J2SE/NETWORKING/JavaSockets/JavaSockets.jsp
http://www-106.ibm.com/developerworks/java/library/ws-secmap/
http://java.sun.com/products/jsse/
The following helps in customization of JSSE like keymanager, trustmanger etc. and includes a sample server/clients application with SSL.
http://www-106.ibm.com/developerworks/java/library/j-customssl/
file:///usr/weblogic81/samples/server/examples/src/examples/security/sslclient/SSLClient.html
http://www-106.ibm.com/developerworks/java/library/j-customssl/
http://archives.java.sun.com/cgi-bin/wa?A2=ind0201&L=java-security&D=0&P=736
permissions in java
http://java.sun.com/j2se/1.4.2/docs/guide/security/permissions.html
http://mindprod.com/jgloss/certificate.html
Pre-requisites
install apache, mod_ssl(if it is not available with apache), openssl
configure openssl configuration file openssl.cnf
The Certificate Stuff:
there are 3 important options with openssl.
i.e, genrsa, req,ca
check the openssl help for all the detail input/output description.
creating key and self certified certificate
first create the key..
openssl genrsa -des3 -out ca.key 1024
then use the following command to create the CA certificate
openssl req -new -x509 -days 365 -key ca.key -out ca.crt
now we generate key and certificate request key..
openssl genrsa -des3 -out server.key 1024
certificate request (CSR)
openssl req -new -key server.key -out server.csr
(generating key, request are done at client side and sent across to the CA for
certification)
Command to certify using our own CA.
openssl ca -in
example: openssl ca -in server.csr -out server.crt -keyfile ca.key -cert ca.crt
this way we can sign any reguest use openssl.cnf file to put certifying
criteria like city name should be
Now generating keystores so that our application can use SSL certificates for
secured communication.
use pkcs12 facility to export certificates to a pkcs12 store with a command
similar to the following..
for CA..
openssl pkcs12 -export -in cacert.pem -inkey cakey.pem -out ca.p12
for client
penssl pkcs12 -export -in client.crt -inkey client.key -out client.p12
NOTE:
keytool -list -keystore test.p12 -storetype PKCS12
keytool error: java.io.IOException: Set tag error
You could try to clean up your keystore. Execute commands:
openssl pkcs12 -in test.p12 -clcerts -out file.pem
openssl pkcs12 -export -in file.pem -out clean.p12
Using keystores
we need to put the ca keystore in trust store and the client certificate in keystore
in java it is just setting the system properties as shown in the following program and the magic works..
import java.io.IOException;
import java.io.PrintStream;
import java.io.InputStreamReader;
import java.io.BufferedReader;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.io.*;
import java.net.*;
import java.util.*;
public class TestClient {
public TestClient(){
try{
java.security.Security.addProvider(new com.sun.net.ssl.internal.ssl.Provider());
//System.setProperty("javax.net.debug", "all");
System.setProperty"java.protocol.handler.pkgs","com.sun.net.ssl.internal.www.protocol");
//System.setProperty("javax.net.ssl.trustStore", "truststore");
System.setProperty("javax.net.ssl.trustStore", "ca.p12");
System.setProperty("javax.net.ssl.trustStoreType", "pkcs12");
System.setProperty("javax.net.ssl.trustStorePassword","password");
System.out.println("after truststore setp");
System.setProperty("javax.net.ssl.keyStoreType", "pkcs12");
System.setProperty("javax.net.ssl.keyStore", "client.p12");
System.setProperty("javax.net.ssl.keyStorePassword", "password");
System.out.println("after keystore setting");
System.out.println("Sending XML with http ");
URL url = new URL("https://SERVER_USRL");
System.out.println("after url created");
//URLConnection uc = (URLConnection)url.openConnection();
//System.out.println("after connection establisht");
//uc.setDoOutput(true);
//uc.setDoInput(true);
//uc.setAllowUserInteraction(false);
//BufferedReader in = new BufferedReader(new InputStreamReader(url.openStream()));
//System.out.println("The instance of url.openConnection |" +url.openConnection()"|");
com.sun.net.ssl.HttpsURLConnection connection = (com.sun.net.ssl.HttpsURLConnection)url.openConnection();
BufferedReader in = new BufferedReader(new InputStreamReader(connection.getInputStream()));
PrintWriter pw = new PrintWriter(new FileWriter("output.xml"));
String inputLine;
while ((inputLine = in.readLine()) != null)
pw.println(inputLine);
in.close();
pw.close();
}catch(Exception e){
e.printStackTrace();
}
}
public static void main(String arg[]){
System.out.println("before");
TestClient obj= new TestClient();
System.out.println("after");
}
}
General notes about OPENSSL
1)openssl uses openssl.cnf while creating certificate request as well as when we are certifying request with our own CA.
so this is a crucial one, we can change the default options like country name,
city, province etc., and while certifying also this configuration file is used to validate the client CSR file
openssl uses 2 text files while certifying requests.
serial
index.txt
serial will contain the certifiate serial number
intially it contains 01 and after that it will be incremented automatically for each request.
index.txt contains the created certificate information ( i think so) initially
it will contain nothing..
see that this file will have no content in the beginning
may be size should be 0 kb
this 2 things will create lot of errors if there is something wrong.
use online help for setting up openssl and to resolve errors generated during
creating .csr or .crt files..
http://tirian.magd.ox.ac.uk/~nick/openssl-certs/index.shtml
APACHE configuration:
apache needs mod_ssl module for dealing with SSL. Load this module and ssl.conf will be used to create virtualhosts with SSL configurations.
read apache documentation for various attribues in ssl.conf
sample virtualhost configuration in ssl.conf
ServerName testing.cybernetsoft.com
SSLEngine on
ErrorLog logs/ssl_error_log
TransferLog logs/ssl_access_log
SSLCipherSuite ALL:!ADH:!EXPORT56:RC4+RSA:+HIGH:+MEDIUM:+LOW:+SSLv2:+EXP
SSLCertificateFile /etc/httpd/conf/ssl.crt/server.crt
SSLCertificateKeyFile /etc/httpd/conf/ssl.key/server.key
SSLCertificateChainFile /etc/httpd/conf/ssl.crt/ca.crt
SSLCACertificateFile /etc/httpd/conf/ssl.crt/ca.crt
SSLCACertificatePath /etc/httpd/conf/ssl.crt
SSLVerifyDepth 1
HostnameLookups double
#SSLVerifyClient require
#SSLRequire (%(SSL_CLIENT_I_DN_CN) eq %(HTTP_HOST))
#SSLRequire (%(SSL_CLIENT_I_DN_O) eq "Cybernet Software Systems Inc")
# and %{SSL_CLIENT_S_DN_OU} in {"Staff", "CA", "Dev"}
# and %{TIME_WDAY} >= 1 and %{TIME_WDAY} <= 5
# and %{TIME_HOUR} >= 8 and %{TIME_HOUR} <= 20 )
#SLRequire ( %{SSL_CIPHER} !~ m/^(EXP|NULL)/
# and %{SSL_CLIENT_S_DN_O} eq "Snake Oil, Ltd."
# and %{SSL_CLIENT_S_DN_OU} in {"Staff", "CA", "Dev"}
# and %{TIME_WDAY} >= 1 and %{TIME_WDAY} <= 5
# and %{TIME_HOUR} >= 8 and %{TIME_HOUR} <= 20 )
# or %{REMOTE_ADDR} =~ m/^11\.1\.14\.[0-9]+$/
CustomLog logs/ssl_request_log
"%t %h cn %{SSL_CLIENT_S_DN_CN}x remoteaddr %{REMOTE_ADDR}x remotehost %{REMOTE_HOST}x remoteident %{REMOTE_IDENT}x \"%r\" %b"
WebLogicHost 11.1.14.13
WebLogicPort 7001
StatPath true
HungServerRecoverSecs 300
ConnectTimeoutSecs 10
#SSLRequire (%{SSL_CLIENT_S_DN_CN} eq %{REMOTE_HOST})
#SSLRequire (%{SSL_CLIENT_VERIFY} eq "SUCCESS")
WebLogicHost 11.1.14.13
WebLogicPort 7001
SetHandler weblogic-handler
SETTING UP tomcat
Setting up Apache Tomcat and a Simple Apache SOAP Client for SSL Communication.
http://ws.apache.org/soap/docs/install/FAQ_Tomcat_SOAP_SSL.html
create key and sertificate from keytool for server.
keytool -genkey -alias tomcat-sv -dname "CN=harinath.cybernetsoft.com, OU=Java services team, O=Cybernet software systems, L=Chennai,
S=TN, C=IN" -keyalg RSA -keypass changeit -storepass changeit -keystore server.keystore
keytool -export -alias tomcat-cv -storepass changeit -file server.cer -keystore server.keystore
do the same thing for client the following steps worked fine for tomcat
CREATING KEYSTORE FOR TOMCAT:
1) keytool -genkey -alias tomcat-cl -dname "CN=harinath.cybernetsoft.com,OU=J2EE TEAM, O=CSS, L=CHENNAI, S=TN, C=IN" -keyalg RSA -keypass changeit -storepass changeit -keystore client.keystore
2)keytool -export -alias tomcat-cl -storepass changeit -file client.cer -keystore client.keystore
keytool -export -alias tomcat -storepass password -file c.crt -keystore client.keystore
3) and now change ur server.xml for ssl
clientAuth="false" keystorePass="changeit" sslProtocol="TLS"/>
Tomcat by default uses keystore named ".keystore" inside the home directory of
the user.
I think we can provide keystore and truststore and their passwords in connector itself.
Read the tomcat documentation of the version you are using.
LINKS:
http://www.flatmtn.com/computer/Linux-SSLCertificatesApache.html
installing CA Into IE
http://wasd.vsm.com.au/ht_root/other/faq/msie_ca_cert/
http://httpd.apache.org/docs-2.0/
http://www.impetus.us/~rjmooney/projects/misc/clientcertauth.html
http://www.samspublishing.com/articles/article.asp?p=30115&seqNum=11
http://tirian.magd.ox.ac.uk/~nick/openssl-certs/index.shtml
http://www.modssl.org/docs/2.8/ssl_reference.html#table3
http://httpd.apache.org/docs-2.0/mod/mod_ssl.html#sslrequiressl
http://www.samspublishing.com/articles/article.asp?p=30115&seqNum=5
http://www.impetus.us/~rjmooney/projects/misc/clientcertauth.html
sample config file openssl.cnf
http://paf.se/openssl/openssl.cnf.html
A very useful stuff.. on how to create certificates etc.. just a text file but
really useful.
http://rei1.m-plify.net/howtoon-certificates.txt
http://www.openssl.org/docs/apps/ca.html
http://www.drh-consultancy.demon.co.uk/pkcs12faq.html
http://search.netscape.com/ns/boomframe.jsp?query=installing+client+certificate+in+browser&page=1&offset=0&result_url=redir%3Fsrc%3Dwebsearch%26requestId%3Deab426fbf64c7db%26clickedItemRank%3D1%26userQuery%3Dinstalling%2Bclient%2Bcertificate%2Bin%2Bbrowser%26clickedItemURN%3Dhttp%253A%252F%252Fzeus.phys.uconn.edu%252Fprod%252Fdocs%252FcertSetup.html%26invocationType%3D-%26fromPage%3Ddshnnp%26amp%3BampTest%3D1&remove_url=http%3A%2F%2Fzeus.phys.uconn.edu%2Fprod%2Fdocs%2FcertSetup.html
http://www.cs.sunysb.edu/documentation/jsse/api_users_guide.html
http://java.sun.com/j2se/1.4.2/docs/api/javax/net/ssl/SSLSocketFactory.html
http://forum.java.sun.com/thread.jsp?forum=60&thread=204299
http://forum.java.sun.com/thread.jsp?forum=60&thread=525858
http://forum.java.sun.com/thread.jsp?forum=2&thread=258908
http://java.sun.com/j2se/1.4.2/docs/guide/security/jsse/samples/sockets/client/SSLSocketClientWithClientAuth.java
http://java.sun.com/products/jsse/doc/apidoc/com/sun/net/ssl/TrustManagerFactory.html
http://www.javaworld.com/javaworld/javatips/jw-javatip96.html
http://www.javaolympus.com/J2SE/NETWORKING/JavaSockets/JavaSockets.jsp
http://www-106.ibm.com/developerworks/java/library/ws-secmap/
http://java.sun.com/products/jsse/
The following helps in customization of JSSE like keymanager, trustmanger etc. and includes a sample server/clients application with SSL.
http://www-106.ibm.com/developerworks/java/library/j-customssl/
file:///usr/weblogic81/samples/server/examples/src/examples/security/sslclient/SSLClient.html
http://www-106.ibm.com/developerworks/java/library/j-customssl/
http://archives.java.sun.com/cgi-bin/wa?A2=ind0201&L=java-security&D=0&P=736
permissions in java
http://java.sun.com/j2se/1.4.2/docs/guide/security/permissions.html
http://mindprod.com/jgloss/certificate.html
Subscribe to:
Posts (Atom)