PL/SQL : Securely Connect to HTTPS URL using oracle wallet

Assumption :

You can execute the PL/SQL with the http connection successfully.


Step1 :

Get the Site Certificate :

Open the site in the browser, click on the Lock icon and then certificate information, under the certification path click on the root certificate, and do a copy to file with Base-64 encoded format

name this as root.cer

Do the same thing for intermediate certificate and name it as intermediate.cer


Step 2 :

Create a directory for wallet :

 mkdir -p /u01/app/oracle/admin/DBNAME/wallet  

Step 3:

create new wallet

 $ orapki wallet create -wallet /u01/app/oracle/admin/11gr2/wallet -pwd WalletPasswd123 -auto_login   

The password should follow the password policy, else you might get an error saying wallet creation failed

Now import the root and intermediate certificate that we have copied

 $ orapki wallet add -wallet /u01/app/oracle/admin/11gr2/wallet -trusted_cert -cert "/location_of_the_cert/root.cer" -pwd WalletPasswd123  
 $ orapki wallet add -wallet /u01/app/oracle/admin/11gr2/wallet -trusted_cert -cert "/location_of_the_cert/intermediate.cer" -pwd WalletPasswd123  

Step 4:

execute the SQL :

 EXEC utl_http.set_wallet ( 'file:/u01/app/oracle/admin/11gr2/wallet', 'WalletPasswd123');  
 EXEC show_html_from_url(https://www.google.com)  
 ..............  
 ..............  
 PL/SQL procedure successfully completed  
 SQL>  

CentOS Directory Server (LDAP) : Useful commands.

How to add a new ldap user?

create a ldif (newuser.ldif) file with the content :

dn: uid=first.last,ou=People,dc=example,dc=com          uid: first.last          cn: First Last          sn: Last          objectClass: account          objectClass: posixAccount          objectClass: person          objectClass: top          userPassword: XXXXXXXX          loginShell: /bin/bash          uidNumber: XXXX          gidNumber: XXX          homeDirectory: /home/first.last          gecos: first last

Add the ldif to LDAP

ldapadd -Wxc -D "cn=Directory Manager" -H ldap://localhost:389 -f newuser.ldif

This will prompt you for the Directory Manager's password.

How does an Administrator change a password from the command-line?

ldappasswd -Z -h hostname -p 389 -D "cn=Directory Manager" -w admin_password -s new_password "uid=first.last,ou=People,dc=example,dc=com"

How to Remove a User from an Existing group ?

ldapmodify -x -h hostname -p 389 -D "cn=Directory Manager" -w password dn: cn=group_name,ou=Group,dc=example,dc=com changetype: modify delete: memberUid memberUid: first.last ^d Response : modifying entry "cn=group_name,ou=Group,dc=example,dc=com

How to Add a User to a Existing group ?

ldapmodify -x -h hostname -p 389 -D "cn=Directory Manager" -w password dn: cn=group_name,ou=Group,dc=example,dc=com changetype:modify add: memberUid memberUid: first.last ^d Response: modifying entry "cn=group_name,ou=Group,dc=example,dc=com"

How to Configure a mirror subversion repository

Setup a Subversion Mirror repository :

Assumption :

You already have a source subversion repository configured
CentOS/RedHat

Steps :

Step 1 : Create an empty subversion repository on the destination.

yum install subversion httpd mod_dav_svn

note :

you might want to add a WanDisco repository to your yum configuration as below :

create  wandisco-svn.repo file with below content at /etc/yum.repos.d/:

 [WandiscoSVN]  
 name=Wandisco SVN Repo  
 baseurl=http://opensource.wandisco.com/centos/6/svn-1.8/RPMS/$basearch/  
 enabled=1  
 gpgcheck=0  

once the yum install is complete to create empty repository cd to the path :

cd /svn
svnadmin create repos                        .......repos is the repository path

Step 2: set the apache configuration for miror repository and create auth file :

 <Location />  
  DAV svn  
  SVNPath /svn/repos  
  AuthType Basic  
  AuthName "SVN Repo"  
  AuthUserFile /etc/httpd/conf/svn-auth-conf  
  Require valid-user  
 </Location>  

as the auth type is basic, we will have to create the svn-auth-conf file : 

htpasswd -cm /etc/httpd/conf/svn-auth-conf <username>

this will generate the file : svn-auth-conf which will be used for http authentication of the repository.

Step 3 : setup local file protocol authentication 

You can  set the password and authorization information for the mirror under the conf directory for repository /svn/repos/ : 

Step 4 : Make the mirror repository only writable by sync user : 

[/]
* = r
username = rw

in the authz file

Step 5 : Make Mirror Repository Revision Properties Modifiable by Synchronizing User

To do this, we need to create a pre-revprop-change hook with something similar to the following example, as a shell script:

 #!/bin/sh  
 USER="$3"  
 if [ "$USER" = "syncuser" ]; then exit 0; fi  
 echo "Only the syncuser user may change revision properties as this is a read-only, mirror repository." >&2  
 exit 1  

Step 6 : Initialize the synchronization : 

svnsync init mirror_repo source_repo --source-username=kiodex_integration --source-password=Tea4two\! 

Copied properties for revision 0.

Step 7: Perform Initial Synchronization

To make sure everything is ready and to perform the initial synchronization, on any system, perform the following:

 svnsync synchronize URL_TO_MIRROR_REPO --sync-username=svnsync --sync-password=svnsyncpassword --source-username=sourceusername --source-password=sourcepassword 

If everything synchronized property, you should see some output similar to this:

Committed revision 1.
Copied properties for revision 1.
Committed revision 2.
Copied properties for revision 2.
Committed revision 3.
Copied properties for revision 3.

 Step 8: Automate Synchronization with post-commit Hook

After taking care of initial synchronization all that needs to happen now is to write a script to be ran either as a scheduled process or as a post-commit hook to synchronize your mirror repository with the master repository.  the post-commit hook at the source gives the best option 

add below to your existing post-commit hook : 

svnsync synchronize URL_TO_MIRROR_REPO --sync-username=svnsync --sync-password=svnsyncpassword --source-username=sourceusername --source-password=sourcepassword