As part of our headlong rush into the world of xmldb we've had to do a lot of java related stuff inside the database on the way - we recently had an issue where the java code was reporting it couldn't write to a file. This led us partially down a blind alley as the error being thrown was 'correct' but the user having the problem mentioned in the error text was not the correct one (something specific about the way xmldb was working it seems). Anyway i'm ignoring the xmldb part just do do a simple demo of what happens when you create a very basic piece of java code inside the database that tries to write to an external file.
Personally I've never really ued java very much (and even less inside the database) - only for very specific cases - the functionality is huge and i'm sure there are places where it's really good....but for me it's 'proper' coding - and it's hard (well a lot harder than simple plsql) so i try and avoid it if i can.....
The demo is in 12c but it's just as relevant in earlier versions.
This demo assumes you've already installed all the java scripts (initjvm etc) and you have the facilty to create java code inside the database alongside the more familiar world of plsql.
So here we go - lets create a user and give it DBA (which is everything right....?) note the 12c common user syntax to create it in all 'plugs' and the special addition to the grant command to also give it DBA in all the 'plugs' too.
SQL> create user c##demo identified by c##demo;
User created.
SQL> grant dba to c##demo container=all;
Grant succeeded.
So now we have a DBA account lets log on to it and create some basic java code
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "writer" AS
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.Writer;
public class writer {
public void writing() {
try {
//What ever the file path is.
File statText = new File("/tmp/test.txt");
FileOutputStream is = new FileOutputStream(statText);
OutputStreamWriter osw = new OutputStreamWriter(is);
Writer w = new BufferedWriter(osw);
w.write("hello world!");
w.close();
} catch (IOException e) {
System.out.println("Something went wrong");
}
}
public static void main(String[]args) {
writer write = new writer();
write.writing();
}
}
;
/
So there I've created a java clas called writer - all it does when called is create a text file '/tmp/test.txt' and puts one line in it saying 'hello world' - I'm sure this code could be simpler but i borrowed it from a code snippet i found and then expanded on it - my lack of java skills meant i just left it as soon as it started working......
Now we've got that basic java code we need to create a plsql wrapper around that to be able to call it - thats a much shorter piece of code - see below
CREATE OR REPLACE PROCEDURE rich
AS LANGUAGE JAVA
NAME 'writer.main(java.lang.String[])';
/
So all I'm saying there is i'm creating something called rich which calls the java class 'writer' (which got create along with the source in the earlier step).
So now with my DBA rights lets run the procedure rich....
execute rich;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
/tmp/test.txt write) has not been granted to C##DEMO. The PL/SQL to grant this
is dbms_java.grant_permission( 'C##DEMO', 'SYS:java.io.FilePermission',
'/tmp/test.txt', 'write' )
ORA-06512: at "C##DEMO.RICH", line 1
ORA-06512: at line 1
Hold on - i'm a DBA - whats going on here....?
It seems that java rights do not recognise this fact - so what's going on here?
Helpfully the error message actually tells us what to do to fix the problem which is quite a novelty in itself!
So lets give it a quick test
SQL> execute dbms_java.grant_permission( 'C##DEMO', 'SYS:java.io.FilePermission', '/tmp/test.txt', 'read,write,delete' ) ;
PL/SQL procedure successfully completed.
SQL> execute rich;
PL/SQL procedure successfully completed.
And it works fine (and creates the file as i want)
I then undo what i just did and the script fails again.
SQL> execute dbms_java.revoke_permission( 'C##DEMO', 'SYS:java.io.FilePermission', '/tmp/test.txt', 'read,write,delete' ) ;
PL/SQL procedure successfully completed.
SQL> exec rich;
BEGIN rich; END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
/tmp/test.txt write) has not been granted to C##DEMO. The PL/SQL to grant this
is dbms_java.grant_permission( 'C##DEMO', 'SYS:java.io.FilePermission',
'/tmp/test.txt', 'write' )
ORA-06512: at "C##DEMO.RICH", line 1
ORA-06512: at line 1
The permissions actually granted to me (related to java) are shown in this select
SELECT TYPE_NAME, NAME, ACTION,ENABLED FROM DBA_JAVA_POLICY WHERE GRANTEE = 'C##DEMO';
java.io.FilePermission
/tmp/test.txt
read,write,delete
DISABLED
So here you can see that after the last command the permissions is explicitly 'DISABLED'
Surely i hear you ask though there must be some kind of java super user that can just do everything - indeed there is - but the DBA role does not have that privilege.
The role in question is javasyspriv - this lets you do anything.
Lets try that way instead - i can grant it to myself as i am a DBA!
SQL> grant javasyspriv to c##demo;
Grant succeeded.
Now lets try
SQL> exec rich;
BEGIN rich; END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
/tmp/test.txt write) has not been granted to C##DEMO. The PL/SQL to grant this
is dbms_java.grant_permission( 'C##DEMO', 'SYS:java.io.FilePermission',
'/tmp/test.txt', 'write' )
ORA-06512: at "C##DEMO.RICH", line 1
ORA-06512: at line 1
And it didn't work....
I got confused for a few mins here but then discovered you have to get a fresh session to pick up the change - if i logoff and back on and try
sqlplus c##demo/c##demo
SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 19 22:47:56 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-00942: table or view does not exist
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Last Successful login time: Thu Jun 19 2014 22:47:15 +01:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> exec rich;
PL/SQL procedure successfully completed.
And now it works
So there is a quick primer on some of the basics of java in the database - as already mentioned it's a huge area and to be honest if you are unlikely to use any of this stuff then don't install the option in the first place - it just massively increases the number of ways the database can be exploited for security holes.... (see some worrying examples here examples )
You don't need the database java option installed to be able to run java against the database - the two are totally unrelated.
Comments
Post a Comment