AlderPump logo

Version 2.5

Full support for 32 and 64-bit clients.

Covers all features of Oracle 10.1 to 11.2

Usability improvements and bugfixes

CONFIGURING SERVER-SIDE DIRECTORY BROWSING

Quick links:

  1. Preface

    Unlike old utilities exp and imp DataPump operates on files located on database server. This is not always convenient, there should be ways to transfer files to and from database server machine to client. Package UTL_FILE provides methods to read and write files as well as delete and rename them; however there is no method to list files in server directories.

    AlderPump versions 2.2 and newer offer two methods for server-side directory browsing: via an internal Oracle package, or a Java stored procedure.

  2. Picking method right for your situation

    Both PL/SQL and Java approaches are supported equally; choice depends on your situation. Once either (or both) method is configured, AlderPump detects this automatically upon connecting to database; PL/SQL is preferred over Java if both are available.

    As a side note, both methods allow browsing of arbitrary folder, but AlderPump restricts this functionality by only allowing access to folders exposed via DIRECTORY objects - for security reasons.

    • PL/SQL method employs undocumented package SYS.DBMS_BACKUP_RESTORE normally used by RMAN. Procedure searchFiles() populates in-memory table with files matching the mask. The table then queried by AlderPump.

      For further details please refer to Christopher Poole's page:

    • Java method creates a stored procedure and PL/SQL wrapper around it. Procedure stores names and sizes of files (filtering out directories) in a temporary CLOB which is returned to AlderPump. The procedure can be created in any schema; in fact it can be also created in several schemas. AlderPump is using:

      1. procedure located in current schema, if present
      2. available procedure belonging to schema coming before others in alphabetical order - if current user is allowed to run it.

    Comparison of PL/SQL and Java methods:

      PL/SQL Java
    Compatibility Works on any 10g+ database Requires Java support on database server
    Uses documented features Employs undocumented package SYS.DBMS_BACKUP_RESTORE Yes
    Extra objects created Single view in SYS schema, grants on SYS package Java procedure and PL/SQL wrapper in one or more schemas; JAVAUSERPRIV grant to each AlderPump user
    Requires SYSDBA privilege on installation Yes No
    Requires SYSDBA privilege on use No No
    Runtime overhead Memory consumtion depends on number of files in the requested directory and all its subfolders. File lengths fetched separately. Memory consumtion depends on number of files in the requested directory (but JVM footprint is substantial, on some platforms it consumes 1G of RAM even while idle.).
    Ease of removal Easy and straightforward Review needed before revoking grants.

    As rule of thumb, we recommend Java method if Java already installed and used on the database. If Java not used, consider PL/SQL approach.

  3. Installing PL/SQL

    Installation script is ConfigureDirBrowsingPLSQL.sql located in AlderPump folder.

    1. Run installation script from SqlPlus as SYS:
      sqlplus -s sys/password@tns as sysdba @ConfigureDirBrowsingPLSQL.sql

      the script creates view around built-in table and grants public access to it. Public access is safe because view contents is private.

    2. Grant execute privilege to users allowed to browse database folders:
      GRANT EXECUTE ON SYS.DBMS_BACKUP_RESTORE TO user_name;

    Uninstallation:

    1. To revoke execute privileges from users, run output of the below query:
      SELECT 'revoke execute on sys.dbms_backup_restore from '||
              grantee||';' AS SqlText
        FROM dba_tab_privs
       WHERE owner = 'SYS'
         AND table_name = 'DBMS_BACKUP_RESTORE'
      ;
    2. Drop view created around built-in table:
      DROP VIEW sys.x_$krbmsft;
  4. Installing Java

    Installation script is ConfigureDirBrowsingJAVA.sql located in AlderPump folder.

    1. Run installation script from SqlPlus:
      sqlplus user/password@tns @ConfigureDirBrowsingJAVA.sql

      the script creates java source named "ap$_Dir" and PL/SQL wrapper function "ap$_DirLs" (both names are case sensitive).

    2. Grant privileges to users:
      GRANT EXECUTE ON "ap$_DirLs" TO user_name;
      GRANT JAVAUSERPRIV TO user_name;

      For convenience, you may combine these two privileges into a role. Please note that roles take effect on re-connection.

    Uninstallation:

    1. Drop java source and the wrapper:
      DROP FUNCTION    "ap$_DirLs";
      DROP JAVA SOURCE "ap$_Dir";
    2. Revoke role JavaUserPriv from unwanted users. Statement below lists users granted the role, but be careful: some of them are built-in accounts.
      SELECT grantee
        FROM dba_role_privs
       WHERE granted_role = 'JAVAUSERPRIV'
         AND grantee NOT IN (SELECT name FROM sys.ku_noexp_tab WHERE obj_type='SCHEMA')
      ;
      Revoke statement might look like:
      REVOKE JAVAUSERPRIV FROM user_name;
  5. Restart AlderPump

    AlderPump checks for available browsing methods upon connecting to database; it should be restarted to recognize the changes.