Sometimes it may be necessary to recreate user java objects within the database, for example following recreation of the JVM or when copying a users schema. These require user and application Java Classes and Java Resources to be reloaded which is not always a simple task if there are a large number of Java objects. The following method explains how to backup all Java Classes, Resources, and their corresponding Java Grants and Policies, avoiding a full database restoration in some cases.
There are two parts that need to be backed up here, the classes and the privileges. For the first part, the simplest method is to export the database with no rows. The second task is accomplished with a SQL query over the data dictionary.
Backup & Restore Java Classes
This can be accomplished by performing a full database export, e.g.:
% exp userid=system/manager full=y ROWS=N file=full_no_rows.dmp
To import this export dump use:
% imp userid=system/manager IGNORE=Y full=y
assuming that the only missing objects are Java Classes and/or PL/SQL Packages. It is possible to reduce the size of the backup here by exporting only certain user schemas or by removing other objects from the backup.
For example:
SQL> create user temp_user identified by X default tablespace users temporary tablespace temp profile default; SQL> alter user temp_user quota 0 on XX; /* for each tablespace*/ SQL> grant dba to temp_user /* needed as the export was done by DBA*/
% imp userid=temp_user/x file=full_no_rows.dmp fromuser=APPS touser=temp_user
Optionally, Views, procedures and other objects can be dropped from the temp_user schema. There is no need to remove tables or indexes, as temp_user has no quota on any tablespace, so none will be recreated. Export the users schema again and don’t forget to specify the FROMUSER=temp_user and TOUSER=APPS on the import command at restore time.
Backup & Restore Privileges
The following SQL script, when run as user SYS, will generate an ordered script to recreate all the java grants, and java policies, assigned to users. It does not generate any SYS Java roles or Public privileges, as these are tightly related to JVM version.
spool setjvmprivs.sql
set echo off
set feedback off
set heading off
set linesize 80
set pagesize 1000
column stmt format a70 word_wrapped
select 'exec '||stmt
from (select seq, 'dbms_java.grant_permission('''||grantee||''','''|| type_schema||':'||type_name||''','''||name||''','''||action|| ''');' stmt
from dba_java_policy
where grantee not in ('JAVADEBUGPRIV', 'JAVASYSPRIV', 'JAVAUSERPRIV', 'JAVA_ADMIN', 'JAVA_DEPLOY', 'SYS', 'PUBLIC') and type_name!='oracle.aurora.rdbms.security.PolicyTablePermission'
union all
select seq,'dbms_java.grant_policy_permission('''||a.grantee||''','''|| u.name||''','''||permition||''','''||action||''');' stmt
from sys.user$ u,
(select seq, grantee,
to_number(substr(name,1,instr(name,':')-1)) userid,
substr(name,instr(name,':')+1,instr(name,'#') -
instr(name,':')-1) permition,
substr(name,instr(name,'#')+1 ) action
from dba_java_policy
where grantee not in ('JAVADEBUGPRIV', 'JAVASYSPRIV',
'JAVAUSERPRIV', 'JAVA_ADMIN', 'JAVA_DEPLOY',
'SYS', 'PUBLIC') and
type_name =
'oracle.aurora.rdbms.security.PolicyTablePermission') a
where u.user#=userid) order by seq;
column stmt clear
set pagesize 24
set heading on
spool off
When executed, this script will generate output like:
exec dbms_java.grant_permission('SCOTT','SYS:java.io.FilePermission','temp\output.txt','read,write);
exec dbms_java.grant_permission('SCOTT','SYS:java.net.SocketPermission','www.oracle.com','resolve');
exec dbms_java.grant_permission('SCOTT','SYS:java.net.SocketPermission','*','connect,resolve');
exec dbms_java.grant_policy_permission('SCOTT','SCOTT','MyPermission','*');
exec dbms_java.grant_permission('OTHER','SCOTT:MyPermission','queseyo.*','');
To restore these java privileges, simply execute the resultant output script (setjvmprivs.sql) as SYS, AFTER you have reloaded all the Java objects as described above.