Friday, July 10, 2015

More Java grants on Oracle. ORA-29532


I got a simple file system writer/reader, it starts with Oracle Directory alias and continues generating folders using organization number and some bits of date. Alias part is static, the rest.. suppose to be generated infinitely. Not including mount, ownership and permission details, basically your main folder and subfolders have to be fully available to user running Oracle.

Short spec
Oracle Directory: /attachments/ (alias ATTACHMENTS)
Organization id: 301
Todays date monthly token: 0715
Schema in use: AWS


Lets start with stack trace:

<...> the Permission ( /attachments/301/0715/19871_head.txt write) has not been granted to AWS. The PL/SQL to grant this is dbms_java.grant_permission( 'AWS', '', '/attachments/301/0715/19871_head.txt', 'write' )
oracle.jdbc.driver.OracleSQLException: ORA-29532: Java call terminated by uncaught Java exception: the Permission ( /attachments/301/0715/19871_head.txt write) has not been granted to AWS. The PL/S
QL to grant this is dbms_java.grant_permission( 'AWS', '', '/attachments/301/0715/19871_head.txt', 'write' )

Possible fixes

Thing is you need write permissions in your Oracle dir, but in this case its recursive and never ending. I start with this:

  dbms_java.grant_permission( 'AWS', '', '/attachments/*', 'write' );

Small bit that made me spend couple of hours was recursive Java grant, just use dash "-" instead of "*" and grant will be valid for all your subdirectories:

  dbms_java.grant_permission( 'AWS', '', '/attachments/-', 'write' );

Just in case you need more then write - use full fleet of file permission types:

  dbms_java.grant_permission( 'AWS', '', '/attachments/-', 'read,write,delete' );

No comments: