Oracle database permissions

[Goaty Bill 2]

There are a few of you out there, I don't expect to get any help from colleagues on this one, it has me slightly foxed.
RDBSM 12c (Enterprise Edition Release 12.1.0.2.0)
E-Business Suite 12.1.3 (The question is not EBS related.)

Two schemas :
APPS
(For the non-EBS folks, this is the Oracle Applications defined super-user. Just shy of similar abilities as SYSTEM)
and
XX
A custom database user / schema as required by Oracle application customisation / extension standards.

Why two schemas?
Implementation hiding. Other developers are unusually nervous in the face of Java and OO PL/SQL.

The problem:
I have created a java file read write etc utility in the XX schema.
For the record this is considerably more powerful and feature rich than the Oracle 'built-in' UTL_FILE which does not provide anywhere near the requisite file handling functionality.

In the XX schema I have created a PL/SQL package to provide standard PL/SQL wrappers to the Java methods.
I have also created (in XX) a PL/SQL object type that uses the package based wrappers to 'restore' the object nature of dealing with files.

So
Java public class xxFile
PL/SQL Package xx_file
PL/SQL Object Type xx_file_i abstract interface class.
PL/SQL Object Type xx_file_o concrete implementation class - subtype of xx.xx_file_i
PL/SQL Object Type xx_file_factory_o factory object for creating / returning type xx_file_i

This tests successfully when executed from XX in this simplified test;
DECLARE
l_file xx_file_i := xx_file_o('/s01/somepath/filename.csv');
BEGIN
If l_file.exist() Then
dbms_output.put_line('File Exists');
End if;
END;


Now this is required for use by the APPS user to be useful to developers so;
GRANT execute on xx.xx_file_i TO apps
GRANT execute on xx.xx_file_factory_o TO apps


In the APPS schema I have created a similar arrangement;
PL/SQL Object Type xx_file_i abstract interface class.
PL/SQL Object Type xx_file_o concrete implementation class, subtype of apps.xx_file_i composed of (having as an attribute) xx.xx_file_i
Calls to apps.xx_file_o are deferred to the attribute class xx.xx_file_i
The attribute class is instantiated by a static function of the xx.xx_file_factory_o object.


Both the XX and APPS schemas have full java permissions on the file system via DBMS_JAVA
And permissions granted to APPS on the XX Java :

FROM dba_java_policy

SEQ	GRANTEE	NAME	TYPE_NAME	USER	ACTION	KIND	ENABLED	TYPE_SCHEMA
61	APPS	*	java.io.FilePermission	APPS	null	GRANT	ENABLED	SYS
186	XX	*	java.io.FilePermission	APPS	null	GRANT	ENABLED	SYS
188	APPS	*	xxFile	APPS	null	GRANT	ENABLED	XX
188	APPS	*	xxList	APPS	null	GRANT	ENABLED	XX

When executing in the APPS Schema;

declare
l_file xx_file_i := xx_file_o('/s01/somepath/filename.csv');
begin
If l_file.exist() Then
dbms_output.put_line('File Exists');
End if;
end;

Results in;

ORA-01031: insufficient privileges
ORA-06512: at "XX.XX_FILE", line 79
ORA-06512: at "XX.XX_FILE_O", line 76
ORA-06512: at "XX.XX_FILE_FACTORY_O", line 80
ORA-06512: at "APPS.XX_FILE_O", line 76
ORA-06512: at line 2
01031. 00000 - "insufficient privileges"

• Cause: An attempt was made to perform a database operation without

the necessary privileges.

• Action: Ask your database administrator or designated security

administrator to grant you the necessary privileges

I cannot determine what privilege is missing, or if this is a shortfall in Oracle's security model.

While it is unwise I think to dismiss any possible solution, I should point out that I have used precisely the same model for a number of PL/SQL objects spanning the two schemas where the XX schema is not making use of Java.

I can also attest that simply creating the Java and PL/SQL in the APPS schema also succeeded, but this does not fully suit my purpose.

[Goaty Bill 2]

Just like me to over complicate things.

To the bare bones of it;
All permissions described as above with the addition of directly granting execute on the PL/SQL package that contains the Java wrapper methods;
GRANT execute on xx.xx_file TO apps

From APPS

declare
l_result number;
begin
l_file xx_file_i := xx.xx_file.get_fileid('/s01/somepath/filename.csv');
end;

and...

ORA-01031: insufficient privileges
ORA-06512: at "XX.XX_FILE", line 79
ORA-06512: at line 4
01031. 00000 - "insufficient privileges"

• Cause: An attempt was made to perform a database operation without

the necessary privileges.

• Action: Ask your database administrator or designated security

administrator to grant you the necessary privileges

The result remains the same.

Because it is stating "a database operation" and there are no queries (dml or ddl) being executed by xx.xx_file.get_fileid, I can only presume this refers to the attempt to execute the Java.

[Crasher242]

How did you create your custom XX schema?

Does it have the necessary cascaded privileges onto the inbuilt oracle components and object that are being referenced within your java objects?

For reference - i come from Oracle/EBS background and can attest to the fact that i've always used a custom schema to hold custom data-related objects (tables/indexes etc) to keep them separate from core EBS schema objects (in much the same way that EBS itself does). However nearly always stick code objects in the APPS schema.
Only time i've not done that, and needed to stick custom code in a custom schema is, as you say, to keep some prying eyes away from security stuff. In these cases, you need to manually go through and make individual and specific grants to all the downstream objects you possibly think you will need, whether it be SELECT, EXECUTE, INSERT privileges...
It only needs on missing privilege/grant to through the errors you are getting.

[Goaty Bill 2]

I 'inherited' the XX schema. It's been in existence for 10-12 years with this client.

My ultimate intention is to move such utilities on to a more appropriately named schema with controllable access, likely via sudo

I won't bore you with further details as, rather embarrassingly, I have discovered the problem.

I made the mistake of copying a file header for re-use in the Java wrapping PL/SQL package.
I far prefer the AUTHID DEFINER (historical default) but cross schema type inheritance demands 'CURRENT USER'.
In copying the header text, I took a file with the 'CURRENT USER' text with it, and of course APPS had no access to XX Java.

Changed the package AUTHID to 'DEFINER' and of course APPS gained access to the Java classes via the wrapper package, and all is working. fine now.
No amount of privileges granted via DBMS_JAVA was going to solve that one.

Thanks for responding .


The shame of it.