This chapter discusses the setup process of DidiSoft OraPGP package providing OpenPGP cryptography in Oracle(c) PL/SQL. It assumes that you are familiar with Oracle(c) Database 11 and above and how to execute commands on the command shell of your operating system.
This product is a Java stored procedure package, and as such it resides in a database schema. You can use any schema (referred in the example calls below as ‘smithj’) which has the SYSDBA role, but if you want to have a dedicated schema for this package, the minimum privileges needed are shown below:
CREATE USER smithj IDENTIFIED BY pass; GRANT CREATE session TO smithj; GRANT CREATE TABLE TO smithj; GRANT CREATE PROCEDURE TO smithj; GRANT CREATE TYPE TO smithj; -- this is needed to use loadjava grant JAVA_ADMIN TO smithj; grant JAVAUSERPRIV TO smithj; -- this is needed in order to be able to store the JAR file contents ALTER USER smithj quota unlimited ON USERS;
Step 1) Extract the distribution ZIP archive to a folder, referred below as [extraction folder]
Step 2) Load the JAR files
Load the JAR files using the loadjava.sh/.bat utility located in the Oracle Database instance /BIN/ folder.
For example on Windows C:\app\User\product\11.1.0\db_1\BIN\loadjava.bat
Replace below smithj/pass with a real database user schema and password applicable for your Oracle Database and host details if needed. Read below for more details regarding the user/pass parameter.
loadjava.bat -resolve -verbose -u smithj/pass [extraction folder]\SetupFiles\jce-jdk13-155.jar
loadjava.bat -resolve -verbose -u smithj/pass [extraction folder]\SetupFiles\ora-pgp-1.3.jar
loadjava.sh -resolve -verbose -u smithj/pass [extraction folder]/SetupFiles/jce-jdk13-155.jar
loadjava.sh -resolve -verbose -u smithj/pass [extraction folder]/SetupFiles/ora-pgp-1.3.jar
Step 3) Register the ORA_PGP PL/SQL package
using your favorite PL/SQL development environment (SQL*Plus, Oracle(c) SQLDeveloper(c), etc.) execute the PL/SQL scripts located at:
Step 4) Check that everything is working:
Execute in your PL/SQL command environment:
SELECT ORA_PGP.VERSION FROM dual
From trial to production
To switch from an evaluation (trial) version of the software to a licensed production version:
The uninstall process is similar to the Upgrade and requires first to unload the old version JAR files from the Oracle© database:
dropjava.bat -resolve -verbose –user smithj/pass [extraction folder]\SetupFiles\jce-ext-jdk13-155.jar dropjava.bat -resolve -verbose –u smithj/pass [extraction folder]\SetupFiles\ora-pgp-<version>.jar
dropjava.sh -r -v -u smithj/pass [extraction folder]/SetupFiles/jce-ext-jdk13-155.jar dropjava.sh -r -v -u smithj/pass [extraction folder]/SetupFiles/ora-pgp-<version>.jar
and afterwards to drop the ORA_PGP package using your favorite PL/SQL execution environment (SQL*Plus, Oracle(c) SQLDeveloper(c), etc.
SQL> drop package ORA_PGP;
Note: Replace above smithj/password with those used in the initial Setup.
The user/password parameter
The -user parameter of the loadjava and dropjava commands can be simply:
username/password – where username is a user(schema) name in the Oracle Database and password is its password.
username/password@database – in this case database can be a TNS name or Net8 name-value list
If we want to install the script into a remote machine specified with @host:port:SID then the additional -thin parameter must also be used:
loadjava -resolve -verbose -thin -user username/password@host:port:SID …