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.