Oracle DBMS_REPUTIL

Oracle 11.2.0.4 and DBMS_REPUTIL

Had to implement a workaround for something a bit odd today. For some reason Oracle 11.2.0.4 does not grant public access to the DBMS_REPUTIL package. This meant all the triggers in my application stopped working!

Replication

The DBMS_REPUTIL package is the database interface to functions used to make replication work. Lots of the insert triggers in this application I work on (during my day job) use the DBMS_REPUTIL.from_remote to determine if the insertion was as a result of a replication from another DB. If so then the trigger does nothing as the record that would have been created by the trigger will also be replicated. So often you will see this:

IF dbms_reputil.from_remote = true THEN
    return;
END IF;

The problem is that in 11.2.0.4 and not in 11.2.0.1 or prior) this package is no longer publicly executable. I think perhaps if you call the replication_on function it might fix itself up but in our case the triggers are setup for replication but it is up to our customer if they choose to enable replication or not.

What we saw was that when an insert occured, an error would be returned like this:
ORA-04098: trigger "schema.trigger" is invalid and failed re-validation 

You can see what the problem is by doing:
show error trigger <triggername>

LINE/COL ERROR
--------------  -----------------------------------------------
1/7             PL/SQL: Statement ignored
1/10            PLS-00201: identifier 'DBMS_REPUTIL' must be declared 

Granting Access

The configuration application which creates the DB schema will also create database users that own the schema. The configuration application connects to the database as system or at least as some privileged DB account. The obvious thing is to have the application grant execute rights to the DB users created but even that wasn't possible as even the system account did not have rights over the package and therefore could not grant them to other users.

The obvious thing to do is
grant execute on DBMS_REPUTIL to system;

Then modify the configuration utility to do the same for accounts it creates. This isn't quite enough though as the system user also has to have the right to grant this right to other users so you have to say:
grant execute on DBMS_REPUTIL to system with grant option;

All our application users have a common role so initially I granted the role access to this package but I found this wasn't sufficient. I'm not really sure why but I found granting the actual user execute rights did work.

When testing this out I found the trigger would still fail after granting access. In fact the show error trigger XXX still showed the same fault. It turned out you have to re-validate the trigger (recompile it) and you can do this as follows:
alter trigger <triggername> compile;

Then it should work.

Detecting the Condition

The thing is that the configuration application would succeed in creating the schema and it wasn't until later when we tried to run the system that it would fail. We wanted this condition to be evident before the user even ran the tool to create the application schema so we needed a way to detect it in the tool.

There is a table called  ALL_TAB_PRIVS that you can query to determine what users have what rights on a package or table. So it then just became a matter of saying:
select count(*) from ALL_TAB_PRIVS where lower(table_name)='dbms_reputil' and privilege='EXECUTE' and lower(grantee)='public' or lower(grantee)='username';

If this returns a value greater than 1 it means either public access is available or 'username' has access to the table. If it is zero then the tool flags an error to the user running the tool to tell them to fix it.

Java Certificate Path Validation Weirdness

I had to create some code that would validate a PKCS#7 signedData structure using a set of pre-configured trust points. We also need to verify the signer certificate has not been revoked and we wanted to support both OCSP based revocation checking (based on embedded AIA extension) and CRL Distribution Points for retrieving CRLs from HTTP locations.

I wrote some code in Java to do this based on sample code on the Web. Essentially I used a Certficate Path Builder which would both locate a path based on a bucket of certificates and set of trust anchors (trusted certificates) and validate the path (including checking the revocation status).

            Set<TrustAnchor> trustAnchors = buildAnchors();

            X509CertSelector selector = new X509CertSelector();
            selector.setCertificate(certificate);
            
            PKIXBuilderParameters params = new                                   PKIXBuilderParameters(
                   trustAnchors, selector ); 

            params.addCertStore(
                makeAdditionlCertStore(
                     certificate,additionalCerts));

            params.setDate(messageSigningDate);

            //
            //  Enable OCSP lookup
            //
            Security.setProperty("ocsp.enable","true");
            
            //
            //  Enable Certificate Revocation List Distribution 
            //  Points (CRLDP) support
            //
            System.setProperty(
                 "com.sun.security.enableCRLDP","true");
            
            CertPathBuilder builder = 
                CertPathBuilder.getInstance("PKIX");
        
            return builder.build(params);

This seemed to be working fine. I tested this with a SSL path issued by a public CA and it was able to get the revocation status and all was well. I disabled OCSP and it still fetched the revocation status using a CRLDP extension in the certificate.

What I didn't initially check was that if I disabled CRLDP it failed even though the path I was testing had an AIA extension pointing at an OCSP server which was accessible. I did some Googling on this and found this which seems to indicate OCSP is not supported via the path builder before JDK 1.8!

So then I re-wrote the code to use the certificate path validator and again this didn't work but this time I got a wrong key usage exception. Well according to this there is a bug in 1.7 update 6 through 10 where OCSP fails with this error!

I updated to update 25 and sure enough the problem goes away.

The problem with the path validator is it literally does what the name implies and so unless you have a path already built for you then you either have to use the path builder or do it yourself. With PKCS#7 structures the ordering of the additional certificates is arbitrary (well they are ordered lexicographically on their encoding according to DER as they are sent as part of a SET) so you can't assume they will be in path order.

My solution in the end was to use the path builder to build the path but turn off revocation checking and then once I have a path to validate it with the path validator. This is going to be pretty inefficient but then what choice have I got until I can upgrade to 1.8?

        public void validateCertificate( 

            X509Certificate       certificate,

            List<X509Certificate> additionalCerts )

                    {

            Set<TrustAnchor> trustAnchors = 

                buildAnchors();

            CertPath cp = 

                 makeCertificatePath(

                     trustAnchors,

                     certificate, 

                     additionalCerts,

                     messageSigningDate);

            

            PKIXParameters params = 

                 new PKIXParameters(trustAnchors);

            params.setDate(messageSigningDate);

            params.setRevocationEnabled(true);

            params.setSigProvider("BC");

            

            //

            //  Enable OCSP lookup

            //

            Security.setProperty("ocsp.enable","true");

            

            //

            //  Enable Certificate Revocation List Distribution 

            //  Points (CRLDP) support

            //

            System.setProperty(

                "com.sun.security.enableCRLDP","true");

            

            CertPathValidator cpv = 

                CertPathValidator.getInstance("PKIX");

            return cpv.validate(cp, params);

        }

    private CertPath makeCertificatePath(

            Set<TrustAnchor>        trustAnchors,

            X509Certificate         certificate,

            List<X509Certificate>   additionalCerts, 

            Date                    messageSigningDate) 

                    throws  CertificateException, 

                            InvalidAlgorithmParameterException, 

                            NoSuchAlgorithmException, 

                            CertPathBuilderException

    {

        X509CertSelector selector = new X509CertSelector();

        selector.setCertificate(certificate);

        

        PKIXBuilderParameters params = new 

            PKIXBuilderParameters(trustAnchors, selector );

        params.addCertStore(

            makeAdditionlCertStore(certificate,additionalCerts));

        params.setDate(messageSigningDate);

        params.setRevocationEnabled(false);

        params.setSigProvider("BC");

        //

        //  Throws if path building/validation fails

        //

        CertPathBuilder builder = 
            CertPathBuilder.getInstance("PKIX");
        
        CertPathBuilderResult result = 

           builder.build(params);

        return result.getCertPath();

    }

Repainting a Boat

Sabot Respray

My son sails a sabot which is a small (8 foot) sailboat for kids under 16. The nationals are on in Sydney this year and his old boat was getting too hard to maintain so we decided an upgrade was in order.

We bought an older (2006 we think) Van Munster Sabot which are reputed to be very fast. What makes them fast is a combination of tweaks to the hull design (while staying in the class rules) and the fact that they are constructed out of epoxy/glass composite which makes them quite stiff. I also like that they have no thwart so you can get right up the front in light breeze. Also there is no internal lip on the gunwale so when you right them after a capsize, less water is trapped in the boat.

The early Van Munster boats just had one little problem - they were coated in gelcoat which doesn't stick to epoxy... Apparently some chemical barrier was used to make it work except it didn't (work that is). What happened was that after a year or so the gelcoat started cracking and looked a lot like crazed pottery. As time went on it would start to fall off in big bits and expose the resin underneath. The only solution was to get all the gelcoat off and re-spray the boat which is pretty expensive.

The sabot we got for my son had been partially re-sprayed. The bottom had been done but the deck and buoyancy tanks had not. Consequently it was a bit cheaper than other boats of this type.

Being the masochist that I am I decided to fix it myself. Initially I was just going to remove the gelcoat and have someone else re-spray it. Then I was thinking I could prime it with a roller and let someone else do the topcoat. I looked at compressors, and gear and decided it was too expensive to spray myself.

After lots of research and thought I bought a compressor and did the whole job myself in the garage!

Removing the gelcoat

I talked to a friend and he told me to get a random orbital sander with those round velcro pads so I bought one of these. The dust catcher is nice (and effective) but I had a lot of problems with mine as the screw holding the pad to the motor would come undone. I pulled it apart a couple of times and put the screw in with thread-lock but it still didn't help.

Gelcoat is very hard and it takes *forever* to sand. Also you can't get into the curved parts even with a mouse sander and trying to sand it by hand (even with the toughest 80 grit paper) is impossible. I talked to a boat builder at a sailing training day for my son and he said you can use a chisel to scrap it off safely.

The chisel made it much easier and didn't seem to damage the resin underneath. I did poke the resin in one or too spots but it wasn't hard to repair.

Surface Filling

When the gelcoat came off it became apparent the surface is quite pock-marked from what appeared to be bubbles in the resin between the gelcoat and the glass. The bubbles seem line up with the weave pattern of the glass. Interestingly the areas where the worst delamination occurred seemed to have the most bubbles.

I decided rather than use a high-build primer I would fill the surface with an epoxy filler. My thinking was that a filler made with micro-spheres or similar will be lighter weight than primer. I used Epifill (as I had some) and filled the surface holes and then sanded. This took quite a few goes as there were areas like the back gunwale where the glass seemed to be corrugated as it was wrapped around the mold.

This process took quite some time. I never did quite find a comfortable way to lay the boat so I could work on it without bending right over. For some of it I could sit but not all.

Compressors and Spraying

In between removing gelcoat, bogging and sanding I was also looking at what it would take to spray the thing myself. The quotes for even just spraying the top coat were in the hundreds. 

The advice I was given was that if I am to do it myself I will need a compressor that has 13cfm of free air delivery. This is a big 3 HP compressor that needs to run on a 15A (240v in Australia) socket. It is the biggest compressor you can run short of a 3 phase unit. The cost runs around AUD $1000 just for the compressor. After a lot of ebay and gumtree searching I decided this wasn't an option (not worth it if I am only going to use the unit a couple of times a year).

In the process I learned a lot about compressors such as:

• Some places advertise in cubic feet per minute (CFM) and others in liters and the conversion is 1 CFM is 28.3 liters/minute
• Some places advertise the capacity in Free Air Delivery (FAD) and some in displacement. FAD is the litres of air pushed out of the compressor per minute at a given pressure (usually 120psi). Displacement is the air pumping capacity calculated by multiplying the cylinder area by the stroke length and number of strokes per minute. Usually this is about 1/3 higher than the FAD volume depending on the efficiency of the pump.
• Big compressors are usually V-twin or triple cylinder (like a car). Smaller ones are single cylinder.
• Better compressors are belt driven and have a cooling fan. Belt drive transfers less vibration from the pump to the motor and means they last longer. Direct drive are cheaper.
• Alloy pumps are cheaper, quieter, capable of higher pressure but don't last as long. Cast iron is preferable if you have the cash.
• Compressors use oil but oil is bad for painting. Also moisture from the air is bad for painting (in particular 2 pack). Use an inline moisture filter at the gun to avoid problems. Also some air tools require injection of oil into the line to lubricate the tool. I don't need air tools so I don't want oil injection. 

Part of the reason I was thinking about doing it myself (apart from the cost) was that otherwise I can't repair the boat at home. In the past I could repair my son's boat by fixing the glass and then applying gelcoat and sanding with wet and dry paper but this isn't an option as there will be no gelcoat!

Also I have some foils (centreboard and rudder) that need repairing too so it would be good to be able to do this.

So I kept looking and found this place called VG Auto Paints. They sell spray equipment including compressors. They specialize in DIY car painting and have a load of videos on how to do it. (see here). There are loads of videos that I found useful such as how to setup a spray gun how to clean your spray gun but there are lots more.

The other thing I learned from talking with these guys is that you don't need a massive compressor to do small jobs. In the worst case it just means you have to wait while the compressor catches up occasionally.

The final straw was that the boat builder I spoke to about doing the job was backlogged for a couple of weeks.

The one I bought in the end was a small 2.5HP Ross direct drive unit that puts out 120 liters/minute. It is all the things I said are bad above but as I am not using it professionally I don't care. Cheap is good. Also, while the gun says it needs a higher FAD than the compressor quotes, the gun only uses 50psi while the compressor is quoting FAD at 120psi so the actual delivery is much more at 50psi.

Spray Gear and Paint

The compressor came with a pretty useless suction gun that in fact requires too much air to really run off the compressor. It came with a bunch of bits and pieces for pumping up tyres and for blowing off dust plus a collection of fittings, quick connectors and a hose. The fittings and hose were useful although no matter how much I tighten them on lots of them seem to leak air.

First off I needed a spray gun and bought one of these which are quite cheap but offer both 1.4mm and 2.0mm nozzles so I can do both primer and topcoat with the same gun.

Then the list expanded to the following:

• Inline pressure regulator. This is so I can adjust the pressure at the gun and get the spray pattern right.
• Inline moisture filter - also fitted at the gun.
• Mixing cups. These are neat cups with measurements for 4-1, 3-1 and 2-1 mixing ratios. They also have marks for 10%,20% and 30% thinning which makes mixing the paint really easy. They cost a dollar each however.
• Filter cones (for filtering lumps from mixed paint)
• Gun cleaning kit (see video)
• Blue chemical resistant gloves.
• Disposable paper overall
• Chemically resistant squeeze bottles for thinners.
• Spray mask (Sundstrom spray respirator kit)
• Buffing compound
• A power buffer with lambswool pad.
• Lots of masking tape
• Cheap plastic drop sheets from Bunnings. These are used to mask the bits of boat I am not painting.
• Face mask to protect my eyes (although I already had that).

The paint came from BC Coatings. I used EP 100 epoxy primer then UT102 2 pack topcoat (which they colour matched to the hull colour). I bought epoxy thinners and thinners for the topcoat also. Unfortunately the primer and thinners only come in 4L tins so I have a lot left over. Having said that I used much more primer than I expected.

Safety

In short, this stuff is toxic. 2 pack more so than epoxy but the thinners from both will kill you or at least intoxicate you and damage your organs. 2-pack is cured with isocyanates which are particularly evil. This datasheet however from Dulux makes the case that the thinner is far worse for you based on vapor pressure and the relative amounts of each substance. 

As I said I bought a respirator with gas filter, wore overalls, gloves and a mask.

In addition, I did the painting inside the garage to limit the travel of the dust/fumes as well as limit dust exposure to what I was painting.

My wife decided I needed a tent to work in so she made me one out of bed sheets. The idea was to limit where the over-spray goes and contain the fumes as well as limit ingress of dust. Also she wanted it to be quick to setup for future jobs. This didn't work out however - see below.

I found an old empty paint tin and used this for cleaning. The way it works is you hold the thing you are cleaning over the tin and squirt thinners on it from a squeeze bottle. The thinners all go into the tin for later disposal. It's really important this stuff doesn't go in the garden or drains.

Painting

So now all my bog is sanded and the boat is smooth. I am ready to paint. I don my gear, mix my paint, load my gun. I used a bit of cardboard as a test target to adjust the gun and sprayed the boat. This worked out Ok except that once I put white primer on the boat I could then can see all the little imperfections like pin-holes in the bog and sagging bits etc. So I get my 200 grit out again, sand it smooth and bog the bits that look bad.

I ended up thinning the primer to 20% and this produced a reasonable finish with the 2mm nozzle.

Here it is after primer:

Then I sand again, redid the plastic covers and sprayed again. The finish wasn't fantastic (a bit sandy) but it was smooth enough (see below for what went wrong). I sanded again with wet and dry, fixed any drips, re-did the covering and was ready for top-coat.

Top coat went on and looked pretty shiny. For the 2 pack I thinned it to around 25%. I had a few drips and I missed one bit and touched one part of the boat with the hose. I gave it a day to dry, used Wet and dry on the drips and bits touched by the hose, re-sprayed the bit I missed and waited for it to harden.

Other things I learned along the way were that you really need to see a surface reflection in the bit you painted to tell if you have it right. If you don't have enough pressure, or were not close enough you end up with a sandy finish. If you are too close or too slow you will get a run. Really good light is important especially when the air is filled with over-spray dust!

Also, as the gun is running out of paint (or when you are painting something horizontal for too long) it will splatter a bit and again lead to a dusty finish.

Here it is after being sprayed with 2 pack and before being buffed (masking removed)

Buff with electric buffing gadget and buffing compound. The buffing gadget was a bit of a pain - the first one had a broken plug (out of the box). The second one got harder and harder to turn on until it just wouldn't any more (after maybe an hour of operation).

I found a few sandy bits and some orange-peel bits. I think the problem was I touched up a few spots but didn't spray it all. The bits I didn't spray got hit with over-spray that made them sandy. Wet and dry sorted it out mostly but took a lot of the shine off. Buffing these bits brought much of the shine back but it still wasn't as nice as the bits that I didn't have to wet-and-dry.

I replaced all the fittings. I bought some silicon that also acts as a sort of glue. I used this around the tank ports (to stick the rings back on) and filled all the holes used for screwing on fittings before I put the screws back in. I also bought big stainless washers for anything that went through the hull and effectively glued the washer on with silicon (on the inside of the hull) before bolting the fitting back on with a nyloc nut. Still need to put the sailing flags sticker back on and some velcro for the bailer.

Here it is with the name applied and all the fittings back on.

Stuff that went wrong

The worst part of this job is that we started late so the boat ended up a couple of weeks late for the sailing season. This created pressure which meant mistakes and also the errors seemed bigger because of their impact on the schedule.

• As I went along with the removal of the gelcoat my chisel got more and more blunt. By the end it was lifting little bits of resin off with the gelcoat. When I switched to a sharper chisel the problem went away. Nothing you can't fix with bog.
• Need to be very careful with the proportions when mixing epoxy bog. I used disposable spoons initially but it was hard to measure this way. In the end I used disposable knives and squished two separate blobs of filler onto a platter (cut out of juice bottle) and one separate blob of hardener. Then I mixed the three blobs together. This allows you to see that the three blobs are the same size and therefore the proportions are right. One lot of bog didn't go off and wasted half a weekend while I tried hair-drying it (to get it to harden) and then had to scrape it off.
• The spray tent was too small. This meant it was hard doing the edges of the gunwales and a couple of times I marked the paint with the hose. Spraying the inside part of the gunwale was tricky as you have to reach across the boat to do it and not touch anything with the hose. Furthermore it took both me and my daughter to put it up as it was heavy. Not sure I'll use it again and I think simply hanging disposable plastic sheeting is better and easier.
• Lighting is critical - when you start painting your goggles get pasted and the air fills with over-spray. I was using a 500w Bunnings work light because the tent blotted out the lighting in the shed. Twice I nearly knocked it over and more than twice I screwed up the painting because I didn't see a drip developing due to the poor light I had.
• Adjust you gear well. You need to be able to lift the goggles to look at what you pained and get them back on. You don't want to be adjusting your mask/goggles while painting (with paint covered gloves).
• Adjusting the gun was easy enough but the first time I cleaned the gun I missed the part about cleaning the breather on the paint reservoir (this wasn't clear on the video). The next time I painted the surface ended up very sandy as there wasn't enough paint flow to get a wet finish. Then when I started using 2 pack I thought I was in trouble with the compressor (thought it didn't deliver enough air) until I realized it changed when I opened the paint cup. Once I sorted this out the painting went smoothly.
• Need good quality chemical resistant gloves. The ones I used were poor and failed while cleaning so the solvent ended up going on my hands. The solvent stings quite a lot. Worse still I spilled some on the cardboard I was using to protect the bench and then leaned on the bench while cleaning. It soaked right through the disposable overalls and my shorts and stung me... um... where it hurts... If you minimize the amount of solvent that gets on your gloves you can get them to last longer. So clean the air cap and fluid tip last as they are small and cause you to pour solvent over your fingers.
• I was sanding a bit late (probably 9pm) one weekend and the old bat over the way must have complained to the council. Got a curt letter in the post the following week about noise.  Bit rich considering I hear her calling out 'Oscar wee-wee' to her dog at 2am on a regular basis. Grrrr.

Bog Sayings

Essentially take sayings involving God but replace with Bog. I'm sure you'll find it it funny when your back hurts from sanding the stuff for a few hours.

• In Bog we trust
• Blessed be Bog forever.
• May Bog be with you.
• Bog save us.