May 13, 2008
Don’t grant execute on DBMS_SYSTEM!
If you support an Oracle database that is used as a resource manager for BEA WebLogic or IBM’s WebSphere products, chances are someone has come to you with a document that says you need to grant them execute on SYS.DBMS_SYSTEM. When this happens, be a Diva and tell them no. This usually puts customers into shock. It’s fun to see the looks on their faces.
The reason they are asking is because of a feature of the xarecover function. If there is a system crash during a distributed transaction, the xarecover function will attempt to clean up. Part of the clean up is a call to DIST_TXN_SYNC, a procedure that is included in the SYS.DBMS_SYSTEM package. The problem is that there are many procedures in this package, and granting the execute privilege gives access to all of them. Oddly enough, the xarecover function comes from the Oracle JDBC, so if you are looking for someone to blame for this, the culprit is Oracle, not IBM or BEA.
The good news is that Oracle has redeemed themselves by moving the DIST_TXN_SYNC procedure to a new package called DBMS_XA that is available to the public. The even better news is that they have made a patch available so you can install it in 10.2.0.2 or 10.2.0.3. It will be included in 10.2.0.4. Because the call comes from the JDBC libraries on the client machine, you have to patch both the DBMS and the client to make it work. The patch numbers are 5892995 (client) and 5945463 (DBMS).
For more information about XA, read the following white papers. You will find the information quite valuable.
http://www.techworld.com/whitepapers/index.cfm?whitepaperid=3139
http://www.oracle.com/technology/products/database/clustering/pdf/bestpracticesforxaandrac.pdf
Here is a link to the DBMS_XA documentation in Oracle 11.1 from the PSOUG web site
