Wednesday, June 20, 2018

R:: Conversion to Numeric

Recently I found an interesting scenario, when I was doing data conversion to numeric in R.

Here is the scenario.

Step#1:: Created a simple data frame with 2 variables as shown below.

 a = as.factor(c(4, 9, 6))  
 b = as.factor(c(2.5, 3, 5.1))  
 df = data.frame(a, b)   
 str(df)  




Step#2:: Converted observations in existing variables to Numeric using as.numeric function, and stored the values in new variables.

 df$a_1 <- as.numeric(df$a)  
 df$b_1 <- as.numeric(df$b)  



What I observed is, the values changed, when applied as.numeric function.

Analysis: It happened because the variable data type is a factor. Internally, factors are stored as integers with a table to give the factor level labels. So using as.numeric will only give the internal integer codes. To avoid this issue, we need to convert variable to character first, then apply as.numeric function.


Step#3:: Converted observations in existing variables to Character using as.character, then applied as.numeric function, and stored the values in new variables.

 df$a_2 <- as.numeric(as.character(df$a))  
 df$b_2 <- as.numeric(as.character(df$b))  




Tuesday, June 19, 2018

DB:: Add SSL Certificate to Wallet

Recently, I tried to access LDAP server over LDAPS protocol from Oracle database. As a part of that, I am supposed to import SSL certificate into database to make SSL handshake worked properly with LDAP server.

Here is the steps, I followed to import SSL certificate into database wallet.

Step#1. Make sure ENCRYPTION_WALLET_LOCATION specified in sqlnet.ora located in $ORACLE_HOME/networl/admin location.

 ENCRYPTION_WALLET_LOCATION =  
  (SOURCE=  
   (METHOD = FILE)  
   (METHOD_DATA = (DIRECTORY=/u01/oracle/admin/ORA12C/wallet)  
  )  
  )  

Step#2. Go to ENCRYPTION_WALLET_LOCATION directory and execute following command to create wallet. If wallet already present, skip this step.

orapki wallet create -wallet /u01/oracle/admin/ORA12C/wallet -auto_login

Note: -auto_login helps you to open wallet automatically, during DB startup. You can also use local auto login option to restrict the wallet access to the host, where it's hosted.


Step#3. Examine the wallet contents with following command.

orapki wallet display -wallet /u01/oracle/admin/ORA12C/wallet

Sample Output:
 Oracle PKI Tool : Version 12.2.0.1.0  
 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.  
 Requested Certificates:  
 Subject:    CN=oracle  
 User Certificates:  
 Oracle Secret Store entries:  
 Trusted Certificates:  

Step#4. Copy SSL certificate to ENCRYPTION_WALLET_LOCATION directory.

SSL certificate content will be in following format.

-----BEGIN CERTIFICATE-----
MIICPDCCAaUCEDyRMcsf9tAbDp...................................................
....................................................................................................xwzoiQ
-----END CERTIFICATE-----


Step#5. Run following command to import the certificate  into wallet as a Trusted certificate.

orapki wallet add -wallet /u01/oracle/admin/ORA12C/wallet -trusted_cert -cert <ssl_cert_file_name> -pwd <wallet_pwd>

execute Step#3 to view the contents of wallet after import.

Sample Output:
 Oracle PKI Tool : Version 12.2.0.1.0  
 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.  
 Requested Certificates:  
 Subject:    CN=oracle  
 User Certificates:  
 Oracle Secret Store entries:  
 Trusted Certificates:  
 Subject:    CN=oracleldap  

Step#6. Test SSL with external system. In this case, I was trying to connect to LDAP over SSL. So I used OS level ldap commands to test the connection via SSL port.

ldapbind -p 1636 -h ldap.lkakarla.com -U 2 -P <wallet_pwd> -W file:/u01/oracle/admin/ORA12C/wallet

Output:
bind successful


Step#7. Remove trusted certificate:

To remove single entry, provide DN of the certificate:
orapki wallet remove -wallet /u01/oracle/admin/ORA12C/wallet -trusted_cert -dn "CN=oracleldap" 

To remove all entries from trust store:
orapki wallet remove -trusted_cert_all -wallet /u01/oracle/admin/ORA12C/wallet