CONFIGURING SERVER-SIDE DIRECTORY BROWSING

Quick links:

• PL/SQL installation instructions
• Java installation instructions

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 up to 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.